Week 137 – Basic

You know, I think the introduction of DATA METRIC FUNCTIONS was a bit too quiet for my liking. With them, you can proactively monitor your tables with data quality tests.

Allow me to provide you a little opportunity to play around. Run the below query:

create or replace stage week137_stage
    url='s3://frostyfridaychallenges/challenge_137/';

create or replace table week137 as
select 
    $1::int AS sale_id,
    $2 AS customer_name,
    $3 AS product,
    $4::int AS quantity,
    $5::float AS unit_price,
    date($6::string, 'DD/MM/YYYY') AS sale_date,
    $7::float AS total_amount
from 
    @week137_stage (file_format=>frosty_csv)
;

THEN

Apply the NULL_COUNT data quality test, and query it, which should give you the following result:

THEN

Create your own custom function that discovers how many rows have bad maths! Where does our total_amount not equal unit_price * quantity?