Data Quality Orchestrator
| Image | $DATAOPS_DQ_ORCHESTRATOR_IMAGE |
|---|---|
| Feature Status | PriPrev |
The Data Quality orchestrator catches unexpected changes in your Snowflake tables before they reach the people and systems that use them.
It learns what your data normally looks like, so you don't have to write a rule for every table and column. You profile each table once, and the orchestrator records a baseline: how many rows it has, its columns and their types, how complete and how unique each column is, and the values that columns with few categories take. From then on, every pipeline run checks the tables against that baseline. When a table loses rows, gains a column, starts filling up with nulls or receives a value it has never seen, the pipeline fails and its Tests tab names each check that failed.
Because the baseline is a file in your project's repository, changing what counts as normal is a reviewed change like any other. When the data changes on purpose, profile the table again and commit the new baseline.
Usage¶
The orchestrator has two commands, chosen with DATAOPS_DQ_COMMAND:
profilemeasures each table inDATAOPS_DQ_TABLESand writes the baseline todq/baseline.yml: the row count, the columns and their types, the proportion of non-null and unique values in each column, and the accepted values of columns with few distinct values. Run it from a manually triggered job, review the baseline, and commit it to your DataOps.live project's own repository, atdq/baseline.ymlin the project root.validatereads the baseline from there, from the project checkout of the branch the pipeline runs on. To keep it elsewhere, setDATAOPS_DQ_BASELINE_PATH. The job keeps the baseline as an artifact so you can download it; see The baseline as a job artifact.validatebuilds the checks below from the committed baseline, runs them, and writes a JUnit report that the pipeline shows in its Tests tab. If a check fails, the job fails, and so does the stage.
profile refuses a table with no rows, because there is nothing to measure yet. Profile it once it has data.
Checks¶
| Check | Applied when | Fails when |
|---|---|---|
| Volume | Always | The row count is more than 20% above or below the baseline |
| Schema | Always | A column was added, removed or renamed |
| Types | The baseline recorded the column's type | The column's type changed |
| Not null | The column had no nulls | Any value is null |
| Completeness drift | The column had some nulls | The proportion of non-null values dropped by more than 5 percentage points |
| Unique | The column had no duplicates | Any value is duplicated |
| Cardinality drift | The column had duplicates | The proportion of unique values changed by more than 10 percentage points |
| Accepted values | The column had 10 or fewer distinct values, all text or whole numbers | A value isn't in the baseline's list |
A check that couldn't run, for example because its column was dropped or the query failed, is reported in the Tests tab as an error with the database's message.
Table values in logs and Git¶
A failed not null, unique or accepted values check reports how many rows broke it, but not their values, which could be personal data. For projects whose data is safe to log, set DATAOPS_DQ_SHOW_UNEXPECTED_VALUES to true on a validate job. A failed check then also shows up to 5 of the offending values and the baseline's accepted values, and a database error shows its whole text, which can include the SQL statement and its parameters. Passing checks add nothing.
These values are written to the job log and to the JUnit report (report.xml), which the pipeline uploads to the DataOps.live platform. They are stored there with the pipeline, as well as shown in the Tests tab.
The baseline lists the accepted values of columns with few distinct values, and is committed to the project's repository. Set DATAOPS_DQ_MAX_ACCEPTED_VALUES to 0 if those values shouldn't be in Git.
The baseline as a job artifact¶
The profile job saves dq/baseline.yml as a job artifact, which uploads the file to the DataOps.live platform, where it is stored with the pipeline. Treat it as you would any other data stored with a pipeline run.
The baseline can contain data from your tables: the accepted values of columns with few distinct values. Review it before you commit it, and set DATAOPS_DQ_MAX_ACCEPTED_VALUES to 0 before you run profile on tables whose values shouldn't leave your Snowflake account. The baseline then holds only the row count, column names, types and proportions.
The profile step is manual so that you decide how the baseline is generated and reviewed, and what is stored with it. The orchestrator never commits the file for you.
Snowflake connection¶
The orchestrator connects with the project's existing Snowflake credentials, so a job usually only names its tables and schema. Each setting takes the first value it finds:
| Setting | Job variable | Then the vault key |
|---|---|---|
| Account | DATAOPS_DQ_SNOWFLAKE_ACCOUNT |
SNOWFLAKE.ACCOUNT |
| User | DATAOPS_DQ_SNOWFLAKE_USER |
SNOWFLAKE.TRANSFORM.USERNAME |
| Role | DATAOPS_DQ_SNOWFLAKE_ROLE |
SNOWFLAKE.TRANSFORM.ROLE |
| Warehouse | DATAOPS_DQ_SNOWFLAKE_WAREHOUSE |
SNOWFLAKE.TRANSFORM.WAREHOUSE |
| Database | DATAOPS_DQ_SNOWFLAKE_DATABASE, then DATAOPS_DATABASE |
|
| Schema | DATAOPS_DQ_SNOWFLAKE_SCHEMA |
These are the TRANSFORM credentials that MATE uses. To read the account, user, role, warehouse or password from another vault key, set DATAOPS_DQ_SNOWFLAKE_<SETTING>_VAULT_KEY, for example DATAOPS_DQ_SNOWFLAKE_USER_VAULT_KEY: SNOWFLAKE.DQ.USERNAME.
The job authenticates with the first of:
DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY, decrypted withDATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY_PASSPHRASEif it's encryptedDATAOPS_DQ_SNOWFLAKE_PASSWORD- The project's key pair, when it uses key-pair authentication (
DATAOPS_SNOWFLAKE_AUTH: KEY_PAIR):DATAOPS_SNOWFLAKE_KEY_PAIR_RAW, decrypted withDATAOPS_SNOWFLAKE_PASSPHRASE - The password in the vault key
SNOWFLAKE.TRANSFORM.PASSWORD
A passphrase is only used with the key it was set for. A private key can be in PEM format or base64-encoded DER, and either encrypted or unencrypted.
Snowflake matches unquoted table names in any case, so customer and CUSTOMER name the same table.
Info
The orchestrator doesn't use the project's dataops/ templates, so it skips rendering them (DATAOPS_SKIP_TEMPLATES is set in the image). To render them anyway, set DATAOPS_SKIP_TEMPLATES to an empty string on the job.
Supported parameters¶
| Parameter | Required/Default | Description |
|---|---|---|
DATAOPS_DQ_COMMAND |
REQUIRED | profile to write the baseline, or validate to check the tables against it. When unset, the job logs a message and does nothing |
DATAOPS_DQ_TABLES |
REQUIRED | Comma-separated names of the tables to check |
DATAOPS_DQ_SNOWFLAKE_SCHEMA |
REQUIRED | The schema that holds the tables |
DATAOPS_DQ_SNOWFLAKE_DATABASE |
Optional, defaults to $DATAOPS_DATABASE |
The database that holds the schema |
DATAOPS_DQ_SNOWFLAKE_ACCOUNT |
Optional, defaults to the vault | See Snowflake connection |
DATAOPS_DQ_SNOWFLAKE_USER |
Optional, defaults to the vault | See Snowflake connection |
DATAOPS_DQ_SNOWFLAKE_ROLE |
Optional, defaults to the vault | See Snowflake connection |
DATAOPS_DQ_SNOWFLAKE_WAREHOUSE |
Optional, defaults to the vault | See Snowflake connection |
DATAOPS_DQ_SNOWFLAKE_PASSWORD |
Optional, defaults to the vault | See Snowflake connection |
DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY |
Optional | A private key for key-pair authentication, used instead of the password |
DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY_PASSPHRASE |
Optional | The passphrase of an encrypted DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY |
DATAOPS_DQ_SNOWFLAKE_<SETTING>_VAULT_KEY |
Optional | The vault key to read the account, user, role, warehouse or password from, instead of the default |
DATAOPS_DQ_BASELINE_PATH |
Optional, defaults to dq/baseline.yml |
The baseline file, relative to the project's root |
DATAOPS_DQ_REPORT_PATH |
Optional, defaults to report.xml |
The JUnit report, relative to the project's root |
DATAOPS_DQ_ROW_COUNT_TOLERANCE |
Optional, defaults to 0.2 |
The allowed change in row count, as a fraction of the baseline |
DATAOPS_DQ_NON_NULL_TOLERANCE |
Optional, defaults to 0.05 |
The allowed drop in the proportion of non-null values, for columns that had nulls |
DATAOPS_DQ_UNIQUE_TOLERANCE |
Optional, defaults to 0.1 |
The allowed change in the proportion of unique values, for columns that had duplicates |
DATAOPS_DQ_MAX_ACCEPTED_VALUES |
Optional, defaults to 10 |
profile records the accepted values of columns with at most this many distinct values. 0 turns it off; the maximum is 20 |
DATAOPS_DQ_FAIL_ON_FAILURE |
Optional, defaults to true |
Set to false to report failed checks without failing the job |
DATAOPS_DQ_SHOW_UNEXPECTED_VALUES |
Optional, defaults to false |
Set to true to show up to 5 of the values that failed a check. See Table values in logs and Git |
Example jobs¶
Both jobs extend .data_quality_base, which the DataOps reference project provides. It runs the orchestrator in the Data Quality stage, and keeps the baseline and the test report as job artifacts.
Run DQ Profile Tables by hand and download dq/baseline.yml from its artifacts. Review it, then commit it to the DataOps.live project's own repository, as dq/baseline.yml in the project root, through a merge request into the branch your pipelines run from. From then on, DQ Validate Tables reads it from there in every pipeline. Remember that the artifact is uploaded to the DataOps.live platform; see The baseline as a job artifact.
DQ Profile Tables:
extends:
- .data_quality_base
variables:
DATAOPS_DQ_COMMAND: profile
DATAOPS_DQ_TABLES: CUSTOMER,ORDERS
DATAOPS_DQ_SNOWFLAKE_SCHEMA: SAMPLES
when: manual
DQ Validate Tables:
extends:
- .data_quality_base
variables:
DATAOPS_DQ_COMMAND: validate
DATAOPS_DQ_TABLES: CUSTOMER,ORDERS
DATAOPS_DQ_SNOWFLAKE_SCHEMA: SAMPLES
rules:
- exists:
- dq/baseline.yml
The exists rule runs DQ Validate Tables only once a baseline is committed at that path in the branch. If you set DATAOPS_DQ_BASELINE_PATH, use the same path in the rule.