Data Quality & Validation

In the orchestration chapter, you put the shop job in a DAG of five tasks. One of them, check_daily_revenue, checks the data. This chapter shows what that task should check.

A job can finish with every task succeeding and still publish a wrong number. Nothing crashed, so nobody gets an alert.

What you already know

• In a DAG, a failed task makes the tasks after it skip, and a person gets an alert.
• A failed check is not retried. The rows do not change when you look again.
• The Python validation chapter checks every row, collects every problem, and stops when too many rows are bad.

What you will learn

1. How bad rows change a number.
2. Six questions that define good data.
3. A check as a query that finds bad rows.
4. Where the check runs in the job.
5. Write, audit, publish.
6. Warn or block: what happens when a check fails.
7. Freshness and volume checks, and dbt tests.
8. A starter kit of six checks.

We use the shop and its daily job, from raw_orders to daily_revenue. When a lesson needs bad rows, we add them in that section and say so.

Say the load for 2026-01-21 has three problems.

These rows are new for this section. Order 106 appears twice.

Order 107 has an amount of -20.00. Order 108 points to customer 9, who does not exist.

The build adds up the paid orders for the day:

SELECT order_date, SUM(amount) AS revenue
FROM   raw_orders
WHERE  order_date = '2026-01-21'
  AND  status = 'paid'
GROUP  BY order_date;

The number looks normal, so nobody asks questions. 170.00 counts order 106 twice, and it includes a negative amount.

The next lessons find these rows with queries.

Data quality means the data is good enough to trust. "Good" is too vague to test. So we split it into six questions.

Each question has one shop example:

Look at the last row. A query on your own tables cannot tell you if data is accurate. It only sees what is stored.

To check accuracy, you compare with another source that saw the same sales, such as the payment provider. This is called reconciliation. The other five questions you can check with queries on your own tables.

A check is a query that looks for bad rows. If it returns no rows, the data passes. If it returns rows, you have a failure and the ids to look at.

In the Python chapter you checked each row in code and collected every problem. A check in the warehouse does the same job in SQL. The next chapter, on SQL data quality, shows more query patterns.

Take the three bad rows from the first lesson: the copy of order 106, order 107, and order 108. Here are three checks, one for each problem:

-- 1. Unique: no order_id appears twice
SELECT order_id, COUNT(*) AS copies
FROM   raw_orders
WHERE  order_date = '2026-01-21'
GROUP  BY order_id
HAVING COUNT(*) > 1;

-- 2. Valid: no negative amount, and NULL only if pending
SELECT order_id, amount
FROM   raw_orders
WHERE  order_date = '2026-01-21'
  AND (amount < 0 OR (amount IS NULL AND status <> 'pending'));

-- 3. Consistent: every customer exists
SELECT o.order_id, o.customer_id
FROM   raw_orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE  o.order_date = '2026-01-21'
  AND  c.customer_id IS NULL;

Check 3 is the anti-join from the SQL weeks. Customer 9 matches no customer row, so order 108 is returned.

A good check in the wrong place does not help. In the DAG from the orchestration chapter, the check task sits between build and publish.

Say the three checks above fail for 2026-01-21. Look at the five tasks of the shop job in that run:

check_daily_revenue failed, so publish_daily_revenue is skipped. The dashboard keeps the old numbers. The check works like a gate.

The check task looks at two places:

• The raw input (raw_orders): copies, missing keys, wrong counts. You find source problems.
• The built table, before publish: totals and ranges. You find mistakes in your own SQL.

Both groups run inside the one check task, after the build. The rule is simple: nothing is published until they pass.

Write, audit, publish is a pattern with three steps. A staging table is a table that the dashboard does not read.

You write the new data to a staging table. You audit it, which means you run the checks on it.

Only if it passes, you publish it. Here is the pattern for the shop job:

Here is the publish step on a good day. It runs only after the audit passes:

DELETE FROM daily_revenue
WHERE  order_date = '2026-01-21';

INSERT INTO daily_revenue
SELECT * FROM daily_revenue_new
WHERE  order_date = '2026-01-21';

A check that fails must do something. There are three choices.

Warn is the Python warning. Quarantine is a rejection. Block is a fatal error.

In a pipeline they look like this:

• Warn: publish, and tell a person.
• Quarantine: put the bad rows in a quarantine table and publish the rest. The Python chapter called this reject.
• Block: publish nothing. The dashboard keeps the last good numbers.

Block when publishing would show a wrong number. Warn when the number is still right. Here are shop rules with an action for each:

Quarantine has a limit, like the 20% limit in the Python chapter. Here we use 5%: if more than 5% of the rows are set aside, block instead. Choose the limit for each job.

In the bad 2026-01-21 load, 2 of 4 rows would be set aside. That is 50%, so the job blocks.

Now look at what the dashboard shows after the bad 2026-01-21 load, for each choice:

A missing number gets questioned. A wrong number gets used. So old and right is better than new and wrong.

A block also sends an alert, because a failed check is a failed task. Do not retry it. Fix the cause, then run again from the failed task.

Sometimes the problem is not a bad row. The problem is that rows are missing. The extract may write an empty file, and every task still succeeds.

A freshness check asks: is the newest data recent enough? Say the shop now sells every day.

The job runs on 2026-01-23 at 02:00 and loads the day 2026-01-22. Here is the check on the sample orders:

SELECT MAX(order_date) AS newest
FROM   raw_orders;

A volume check asks: is the row count near the usual count? The counts below are made up for a bigger shop. They are not in our six sample orders.

The average of the first five days is 200. The rule allows half to double: 100 to 400 orders. Twelve is outside, so the check fails.

Use the recent average, not one fixed number. A fixed number is right for a month and then wrong, because the shop grows.

A count far below normal means missing data. A count far above normal may mean copies.

dbt is the tool from the orchestration chapter that builds tables from SQL files. It can also run checks, called tests. You write the rule in a YAML file, and dbt writes the query.

YAML is a text file of names and values. Indents show what belongs to what.

raw_orders is loaded, not built by dbt, so you declare it as a source. Each test is a query that returns the bad rows, like the checks above. Here are three built-in tests for raw_orders:

sources:
  - name: warehouse
    tables:
      - name: raw_orders
        columns:
          - name: order_id
            data_tests:
              - not_null
              - unique
          - name: status
            data_tests:
              - accepted_values:
                  values: ['paid', 'pending', 'refunded']

dbt marks a failed test as an error by default. Set severity: warn on a test to make it a warning. That is the same warn or block choice as before.

Before dbt 1.8 the key was tests. Newer versions accept both, and data_tests is preferred. Other tools, such as Great Expectations and Soda, do the same job with their own files.

You get a new table with no checks. Start with these six. Each has a shop example and a failure action:

Add checks 1 and 2 the day you get the table. They can block from day one. Start the other checks in warn mode for a week.

Five points make a complete answer:

1. Place: extract, load, build, check, then publish. Nothing is published before the check passes.
2. Checks: the six from the starter kit, from key not null to fresh and normal row count.
3. Action: warn when the number is still right. Quarantine a small share of bad rows. Block when publishing would be wrong.
4. Failure: publish is skipped, the dashboard keeps the last good day, and a person gets an alert with the rule and the rows. The check is not retried.
5. After: fix the cause, run again from the failed task, and add a new check for every new kind of problem.

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.