Asserting Data quality with Expectations
This is the guide page to Fashion Data Expectation stored procedures.
What are expectations? A set of SQL Stored Procedures that ease the writing of tests. Too often, writing tests in SQL requires assertions that are complex to write, that are not factorized. In a complex project, those assertions are spread amongst many projects and scripts and this can be a tedious tasks to maintain them. Fashion Data Expectations are a way to solve these problems:
- a set of stored procedures that manage a large number of common assertions (check for primary key, check for integrity constraints, regular expression, etc.)
- one liners that are still part of the SQL ecosystem, so they live with your SQL code and your Tailer configurations and can benefit from classic SQL syntax.
- fast execution with parallel processing.
- full assertion metrics including number of rejected lines, assertion processing time, timestamping, metrics history, etc.
Let’s say that you imported data into a BigQuery project and you want to check for a primary key constraint with a certain threshold. In SQL you would write something like that:
ASSERT ((
(select count(distinct PK_products) from `dlk-demo.dlk_demo_pda.products`)
- (select count(*) from `dlk-demo.dlk_demo_pda.products`)
) = 0
) as "pk issue with table dlk-demo.dlk_demo_pda.products";
With Fashion Data Expectations, you just write:
CALL `tailer-ai.expect.primarykey_named`('dlk-demo.dlk_demo_pda', 'products', 'PK_products', 0);
You can launch an expectation directly from your BigQuery console and check your test. If the call is properly formed, BigQuery will launch the jobs described in the procedure and you will see the test status and the related metrics in the result of the last job. This eases the developpement of a set of expectations and can also be useful for ensuring adhoc quality of an element.
-- Expectations have usually the following format
-- CALL `tailer-ai.expect.EXPECTATION`('PROJECT_ID.DATASET_ID', 'TABLE_ID', SOME_PARAMETERS);
CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_pda', 'products', 10000, 0);
In your expectation call, always specify the name of the project, otherwise the expectation will search for your table in “tailer-ai” (and will fail) instead of wherever your data is.
You need to request access to the tailer-ai project from your Tailer Platform administrator before beeing able to call these expectations.
To create an expectation, you need two elements:
- a dedicated task in a table-to-table configuration
- a dedicated SQL file
The dedicated task must be of type “expectation”. For example:
{
"id": "expects_tables",
"task_type": "expectation",
"short_description": "Check for data integrity (pk, count, dates,...).",
"doc_md": "000001_load_PDA_products.md",
"sql_file": "000001_load_PDA_products_expects_r7.sql",
"criticality": "warning"
}
In your SQL file, you can add as much expectations as you want:
-- assert count greater than 0
CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_pda', 'products', 100000, 0);
-- assert primary key is ok
CALL `tailer-ai.expect.primarykey`('dlk-demo.dlk_demo_pda', 'products', 0);
-- assert freshness on the final table (we want to have at least 10k products for today iteration)
CALL `tailer-ai.expect.values_to_contain`('dlk-demo.dlk_demo_pda', 'products', 'max_importdate', cast(current_date() as string), 10000, 0);
-- assert freshness on the psa table (we want to have at least 10k product for today psa)
CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_psa', concat('products_', replace(cast(current_date() as string), '-', '')), 100000, 0);
-- assert freshness on the psa table for yesterday(we want to have at least 10k product for yesterday psa)
CALL `tailer-ai.expect.table_count_greater`('dlk-demo.dlk_demo_psa', concat('products_', replace(cast(date_sub(current_date(), interval 1 day) as string), '-', '')), 100000, 0);
Your call to a stored procedure will be treated as a SQL instruction. This allows writing great expectations with powerful features. For example, doing “CONCAT” or using “DATE_SUB” or “CURRENT_DATE” enable counting with a sliding window on a specific table:
-- Check line count for the table products_YYYYMMDD where YYYYMMDD is yesterday's date
CALL `tailer-ai.expect.table_count_greater`(
'dlk-demo.dlk_demo_psa',
concat('products_', replace(cast(date_sub(current_date(), interval 1 day) as string), '-', '')),
100000,
0
);
In your SQL expectation file, only expectations will be executed. Classic SQL commands or comments will be ignored.
Everytime an expectation embedded in a table-to-table data operation is executed, it generates some metrics that are added to the tailer_common.expectation_results table in your project. For example:
SELECT * FROM `dlk-demo.tailer_common.expectation_results` LIMIT 1000
Here are the fields of this:
Field Name | Type | Description |
|---|---|---|
job_id | STRING | Identifier of the Job |
dag_id | STRING | Identifier of the Direct Acyclic Graph |
account | STRING | Account Name |
environment | STRING | Execution Environnement (DEV, PROD,...) |
run_id | STRING | Identifier of the Execution |
configuration_type | STRING | Configuration Type |
configuration_id | STRING | Identifier of the Configuration |
task_id | STRING | Identifier of the Task |
execution_date | STRING | Execution Date |
criticality | STRING | Task Criticality |
expectation_result | STRING | Expectation Result |
The field called “expectation_result” contains additionnal informations about the expectation in JSON format. The generic output is defined here, and some specific results could be added for some expectations (see details in the list of expectations):
{
"dataset": "dlk-demo.dlk_demo_pda",
"tablename": "products",
"column_name": "",
"procedure_name": "table_count_greater",
"date_count": "2021-12-03T14:02:51.284016",
"all_count": 158357,
"target_dataset": "",
"target_table_name": "",
"target_value": [
"100000"
],
"reject_count": 58357,
"reject_threshold": 0.01,
"passed": false,
"start_date": "1642411639",
"end_date": "1642411644"
}
The metrics of a test launched from the BigQuery console won't be inserted into the metrics table. Only the results of the tests embedded into a table-to-table configuration will be stored.
You can find an Expectations Overview in Tailer Studio.
Click in the left pannel on "Expectations Overview" in the "Data Quality" section and see all the expectations that has been recently tested.
A complete documentation of all avaibable expectations and their specific parameters is available on the next page: List of Expectations.
Last modified 2mo ago