Skip to content

Data Quality Orchestrator

Image$DATAOPS_DQ_ORCHESTRATOR_IMAGE
Feature StatusPriPrev

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:

  1. profile measures each table in DATAOPS_DQ_TABLES and writes the baseline to dq/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, at dq/baseline.yml in the project root. validate reads the baseline from there, from the project checkout of the branch the pipeline runs on. To keep it elsewhere, set DATAOPS_DQ_BASELINE_PATH. The job keeps the baseline as an artifact so you can download it; see The baseline as a job artifact.
  2. validate builds 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:

  1. DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY, decrypted with DATAOPS_DQ_SNOWFLAKE_PRIVATE_KEY_PASSPHRASE if it's encrypted
  2. DATAOPS_DQ_SNOWFLAKE_PASSWORD
  3. The project's key pair, when it uses key-pair authentication (DATAOPS_SNOWFLAKE_AUTH: KEY_PAIR): DATAOPS_SNOWFLAKE_KEY_PAIR_RAW, decrypted with DATAOPS_SNOWFLAKE_PASSPHRASE
  4. 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.

pipelines/includes/local_includes/dq-jobs.yml
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.