Data-diff solution for dbt-ers with Snowflake βοΈ π
Who is this for?
- Primarily for people who want to perform Data-diff validation on the Blue-Green deployment π
- Other good considerations π
- UAT validation: data-diff with PROD
- Code-Refactoring validation: data diff between old vs new
- Migration to Snowflake: data diff between old vs new (requires to land the old data to Snowflake)
- CI: future consideration only
β οΈ
dbt-data-diff
package provides the diff results into 3 categories or 3 levels of the diff as follows:
- π₯ Key diff (models): Compare the Primary Key (
pk
) only - π₯ Schema diff (models): Compare the list of column's Names and Data Types
- π₯ Content diff (aka Data diff) (models): Compare all cell values. The columns will be filtered by each table's configuration (
include_columns
andexclude_columns
), and the data can be also filtered by thewhere
config. Behind the scenes, this operation does not require the Primary Key (PK) config, it will perform Bulk Operation (INTERCEPT
orMINUS
) and make an aggregation to make up the column level's match percentage
Behind the scenes, this package leverages the βοΈ Scripting Stored Procedure which provides the 3 ones correspondingly with 3 categories as above. Moreover, it utilizes the DAG of Tasks to optimize the speed with the parallelism once enabled by configuration π
- Add to
packages.yml
file:
packages:
- package: infinitelambda/dbt-data-diff
version: [">=1.0.0", "<1.1.0"]
- (Optional) Configure database & schema in
dbt_project.yml
file:
vars:
# (optional) default to `target.database` if not specified
data_diff__database: COMMON
# (optional) default to `target.schema` if not specified
data_diff__schema: DATA_DIFF
- Create/Migrate the
data-diff
's DDL resources
dbt deps
dbt run -s data_diff --vars '{data_diff__on_migration: true}'
We're going to use the data_diff__configured_tables
variable (Check out the dbt_project.yml/`vars`` section for more details!)
For example, we want to compare table_x
between prod db and dev one:
vars:
data_diff__configured_tables:
- src_db: your_prod
src_schema: your_schema
src_table: table_x
trg_db: your_dev
trg_schema: your_schema
trg_table: table_x
pk: key # multiple columns splitted by comma
include_columns: [] # [] to include all
exclude_columns: ["loaded_at"] # [] to exclude loaded_at field
We can skip this step if you already did it. If not, let's run the below command:
dbt run -s data_diff \
--full-refresh \
--vars '{data_diff__on_migration: true, data_diff__on_migration_data: true, data_diff__full_refresh: true}'
π click me
In the above:
--full-refresh
anddata_diff__full_refresh
: To re-create all data-diff modelsdata_diff__on_migration: true
: To re-create the stored proceduresdata_diff__on_migration_data: true
: To reset the configured data
Now, let's start the diff run:
dbt run-operation data_diff__run # normal mode, run in sequence, wait unitl finished
# OR
dbt run-operation data_diff__run_async # async mode, parallel, no waiting
dbt run-operation data_diff__run_async --args '{is_polling_status: true}'
# async mode, parallel, status polling
NOTE: In async mode, we leverage the DAG of tasks, therefore the dbt's ROLE will need granting the addtional privilege:
use role accountadmin;
grant execute task on account to role {{ target.role }};
π Or via dbt hook by default (it will run an incremental load for all models)
# dbt_project.yml
# normal mode
on-run-end
- > # run data-diff hook
{% if var("data_diff__on_run_hook", false) %}
{{ data_diff.data_diff__run(in_hook=true) }}
{% endif %}
# async mode
on-run-end
- > # run data-diff hook
{% if var("data_diff__on_run_hook", false) %}
{{ data_diff.data_diff__run_async(in_hook=true) }}
{% endif %}
# terminal
dbt run -s data_diff --vars '{data_diff__on_run_hook: true}'
dbt-data-diff
is an open-source dbt package. Whether you are a seasoned open-source contributor or a first-time committer, we welcome and encourage you to contribute code, documentation, ideas, or problem statements to this project.
π See CONTRIBUTING guideline for more details or check out CONTRIBUTING.md
π And then, kudos to our beloved Contributors:
Feature | Supported Package | Notes |
---|---|---|
Key diff |
|
β |
Schema diff |
|
(*): Only available in the paid-version π° |
Content diff |
|
(*): Only available in the paid-version π° |
Yaml Configuration |
|
data_diff will use the toml file, dbt_audit_helper will require to create new models for each comparison |
Query & Execution log |
|
Except for dbt's log, this package to be very transparent on which diff queries executed which are exposed in log_for_validation model |
Snowflake-native Stored Proc |
|
Purely built as Snowflake SQL native stored procedures |
Parallelism |
|
dbt_data_diff leverages Snowflake Task DAG, the others use python threading |
Asynchronous |
|
Trigger run & go away. Decide to continously poll the run status and waiting until finished if needed |
Multi-warehouse supported |
|
(*): Future Consideration π |
Infinite Lambda is a cloud and data consultancy. We build strategies, help organizations implement them, and pass on the expertise to look after the infrastructure.
We are an Elite Snowflake Partner, a Platinum dbt Partner, and a two-time Fivetran Innovation Partner of the Year for EMEA.
Naturally, we love exploring innovative solutions and sharing knowledge, so go ahead and:
π§ Take a look around our Git
βοΈ Browse our tech blog
We are also chatty, so:
π Follow us on LinkedIn
ππΌ Or just get in touch