Skip to main content

Scenario 4: Implementing a Data Quality Gate

Business Context: Your logistics startup relies on a third-party API to ingest delivery package weights. Last week, a bug in the API inserted negative package weights into your raw Snowflake tables. Your dbt models processed this data, and the CEO's dashboard showed that the company delivered "-500 kg" of freight.

Your Objective: You must implement a Data Quality Gate. You need to write a source freshness and data quality test on the raw data. Crucially, you must configure your pipeline to fail and abort before building the downstream models if the raw data is corrupt.


1. Setup the Database Environment​

(Execute this in your Snowflake worksheet to prepare the environment. Find your username by running SELECT CURRENT_USER();)

-- 1. Create a dedicated database for this scenario
CREATE DATABASE IF NOT EXISTS BEEMS_SCENARIO_4_QUALITY;
USE DATABASE BEEMS_SCENARIO_4_QUALITY;
CREATE SCHEMA IF NOT EXISTS RAW;
CREATE SCHEMA IF NOT EXISTS MARTS;

-- 2. Create the raw API table (Source)
CREATE OR REPLACE TABLE RAW.delivery_weights (
package_id VARCHAR,
courier_name VARCHAR,
weight_kg NUMBER(10,2),
scanned_at TIMESTAMP_NTZ
);

-- 3. Insert mock data (including the BAD data!)
INSERT INTO RAW.delivery_weights VALUES
('PKG-101', 'Arjun', 12.50, CURRENT_TIMESTAMP()),
('PKG-102', 'Priya', 5.25, CURRENT_TIMESTAMP()),
('PKG-103', 'Rohan', -8.00, CURRENT_TIMESTAMP()); -- The corrupt data!

2. Defining the Source and Tests​

In your dbt project, define the source and apply standard data quality tests. We also need to write a custom generic test to ensure weights are strictly positive.

Step 1: Write the Custom Test​

Create a new file: macros/test_is_positive.sql

{% test is_positive(model, column_name) %}

SELECT *
FROM {{ model }}
WHERE {{ column_name }} <= 0

{% endtest %}

Step 2: Define the Source YAML​

Create a new file: models/staging/sources.yml

version: 2

sources:
- name: logistics_api
database: BEEMS_SCENARIO_4_QUALITY
schema: RAW
tables:
- name: delivery_weights
columns:
- name: package_id
tests:
- unique
- not_null
- name: weight_kg
tests:
- not_null
- is_positive # Our custom test!

3. Building the Downstream Model​

Create the model that depends on this raw data. Create a new file: models/marts/fct_daily_freight.sql

SELECT
DATE(scanned_at) as delivery_date,
courier_name,
SUM(weight_kg) as total_weight_delivered
FROM {{ source('logistics_api', 'delivery_weights') }}
GROUP BY 1, 2

4. Executing the Data Quality Gate​

If you simply run dbt run, dbt will execute the fct_daily_freight model, ingest the -8.00 kg record, and corrupt the CEO's dashboard.

To implement a Data Quality Gate, you must use the dbt build command. The dbt build command intelligently runs tests on the sources before it runs the downstream models.

dbt build --select source:logistics_api+

What Happens?​

  1. dbt runs the unique, not_null, and is_positive tests on RAW.delivery_weights.
  2. The is_positive test FAILS because it finds PKG-103 with a weight of -8.00.
  3. Because the upstream test failed, dbt SKIPS the execution of fct_daily_freight.sql.

Result: The pipeline halts. The Data Engineering team receives an alert about the corrupt data. The fct_daily_freight table in Snowflake is not updated, meaning the CEO's dashboard continues to show yesterday's clean data instead of today's corrupt data.

Summary​

You have successfully implemented a Data Quality Gate. By defining tests on your sources and executing the pipeline using dbt build, you ensure that corrupt raw data never makes it into your production models.