Data Quality Checks on Every Data Mart: Prove the Number Before Anyone Asks

Why nobody notices until someone asks

A wrong number looks exactly like a right one. A key that stops being unique, a feed that stopped three days ago, a join that drops the customers who are missing from a lookup table: none of these throws an error. The query runs, the dashboard renders, and the total is off by just enough to change a decision.

The way out is to write down, in advance, what must be true of the data behind each report, check it on a schedule, and have someone told when it breaks. Then “is this number right?” has an answer you can show.

Six checks for the usual ways a total breaks

Six questions cover the breaks that come up again and again. Each is a short query you can write today on any warehouse.

1. Is each row counted once? Pick the column, or set of columns, that should identify a row, and count the groups that appear more than once.
2. Is anything missing that should be there? Count NULLs in the fields a report groups or joins by.
3. Is it up to date? Compare the latest date in the data with the date you expected. “Last 7 days” on a feed that stopped on Friday is a smaller number, not an error.
4. Does every join find its match? For each relationship, count the rows whose key has no partner on the other side.
5. Are the values possible? Negative quantities, a column with one value in every row, dates in the future.
6. Is the latest day complete? Compare yesterday’s row count, or its total, with the same weekday in recent weeks. This is the check that catches a half-loaded day. A freshness check only asks whether yesterday arrived; a partial load passes it.

Write each check so that a row coming back means a problem and no rows means it passed. The NULL check, with a threshold:

SELECT
  SAFE_DIVIDE(
    COUNTIF(traffic_source_id IS NULL),
    COUNT(*)) AS null_share
FROM `owox-demo.ecommerce.sessions`
WHERE date >= DATE_SUB(CURRENT_DATE(),
  INTERVAL 7 DAY)
HAVING null_share > 0.01

The impossible-values check:

SELECT COUNT(*) AS bad_lines
FROM `owox-demo.ecommerce.purchases`
WHERE quantity <= 0
  OR sale_price < 0
HAVING COUNT(*) > 0

The volume check, yesterday’s orders against the average of the same weekday over the last four weeks:

WITH daily AS (
  SELECT order_date AS day,
    COUNT(*) AS orders
  FROM `owox-demo.ecommerce.orders`
  GROUP BY day
),
target AS (
  SELECT DATE_SUB(CURRENT_DATE(),
    INTERVAL 1 DAY) AS day
)
SELECT
  t.day,
  IFNULL(SUM(IF(d.day = t.day,
    d.orders, 0)), 0) AS orders,
  AVG(IF(DATE_DIFF(t.day, d.day, DAY)
    IN (7, 14, 21, 28), d.orders, NULL))
    AS baseline
FROM target AS t
LEFT JOIN daily AS d
  ON d.day BETWEEN
    DATE_SUB(t.day, INTERVAL 28 DAY)
    AND t.day
GROUP BY t.day
HAVING orders < 0.7 * COALESCE(baseline, 1)

All three returned no rows on the demo on 11 October. The thresholds (1% of NULLs, 70% of the usual volume) are starting points, not rules. The next section shows the 70% line both catching a half-loaded day and missing one.

CURRENT_DATE() in BigQuery is UTC unless you pass a time zone, such as CURRENT_DATE('America/New_York') (date functions).

The list itself is standard (the data quality checklist). What follows is what the checks did on real tables.

What the checks found on one shop’s data

We ran these in BigQuery on the [Demo] E-commerce project, an online shop with orders, customers, sessions and ad spend, between 10 and 12 October 2026. The demo is small and clean, and it keeps receiving data, so every figure below carries its run date. Even clean data produces findings, and each one needs a different verdict. The totals:

SELECT
  (SELECT COUNT(*)
   FROM `owox-demo.ecommerce.purchases`)
    AS order_lines,
  (SELECT COUNT(*)
   FROM `owox-demo.ecommerce.orders`)
    AS orders,
  (SELECT COUNT(*)
   FROM `owox-demo.ecommerce.customers`)
    AS customers,
  (SELECT COUNT(*)
   FROM `owox-demo.ecommerce.orders` AS o
   LEFT JOIN `owox-demo.ecommerce.customers`
     AS c USING (customer_id)
   WHERE c.customer_id IS NULL)
    AS orders_without_customer

Run on 12 October: 31,915 order lines, 18,041 orders, 5,000 customers, and no order without a customer. On 10 October, when the first checks below ran, there were 31,789 lines and 17,965 orders.

Uniqueness depends on the key you declare

The Purchases Data Mart holds one row per order line and declares purchase_id as its primary key. Counted on that key, no row appears twice. Counted on the key many people would assume, an order and a product, ten groups do:

SELECT
  order_id,
  product_id,
  COUNT(*) AS lines
FROM `owox-demo.ecommerce.purchases`
GROUP BY order_id, product_id
HAVING COUNT(*) > 1

Ten rows come back: the same product on two lines of one order, each with its own purchase_id. That is not a duplicate. It is how this shop records a basket. But a report that joins on order and product would be off on exactly those ten orders. The check passes or fails depending on what you said a row is, which is why the key belongs in writing next to the data.

A relationship can be complete and still not cover everyone

Every order points at a customer who exists. Run the relationship the other way, from customers to orders:

SELECT
  COUNT(*) AS customers_without_orders
FROM `owox-demo.ecommerce.customers` AS c
LEFT JOIN `owox-demo.ecommerce.orders` AS o
  USING (customer_id)
WHERE o.customer_id IS NULL

It returned 149 of 5,000 customers on 10 October (144 on 12 October). Expected: people register and never buy. It is not a fault, but it decides an answer. “Average orders per customer” is a different number depending on whether those 149 are in the denominator, and the person asking will not know to ask.

Fresh is not the same as covering today

The order data runs to 10 October. The ad spend data stops on 3 October. A check with a threshold returns a row when the newest date is too old:

SELECT
  MAX(date) AS latest,
  COUNT(*) AS row_count
FROM `owox-demo.ecommerce.ad_performance`
HAVING COUNT(*) = 0
  OR DATE_DIFF(CURRENT_DATE(),
    MAX(date), DAY) > 1

Run on 11 October, it returned one row: 3 October, eight days behind (by 12 October the spend ran to 10 October). The COUNT(*) = 0 condition matters: on an empty table MAX(date) is NULL, and a check on the date alone would quietly pass. For a daily load, one day is a sensible starting threshold; a feed that lands weekly needs a wider one.

This is the one that would change a decision, and the demo let us measure it, because by 12 October the missing spend had arrived. This query ran on 12 October; a run on 10 October had counted 106 orders and found no spend rows for the same week:

WITH o AS (
  SELECT
    COUNT(DISTINCT o.order_id) AS orders,
    ROUND(SUM(p.line_revenue))
      AS order_value
  FROM `owox-demo.ecommerce.orders` AS o
  JOIN `owox-demo.ecommerce.purchases` AS p
    USING (order_id)
  WHERE o.order_date
    BETWEEN '2026-10-04' AND '2026-10-10'
),
s AS (
  SELECT ROUND(SUM(spend)) AS spend
  FROM `owox-demo.ecommerce.ad_performance`
  WHERE date
    BETWEEN '2026-10-04' AND '2026-10-10'
)
SELECT
  orders, order_value, spend,
  ROUND(SAFE_DIVIDE(spend, orders), 2)
    AS cost_per_order,
  ROUND(SAFE_DIVIDE(order_value, spend), 1)
    AS value_per_ad_dollar
FROM o CROSS JOIN s

The 10 October column came from an earlier query that joined orders to sessions and traffic sources, so its order count is not directly comparable; the 12 October column is the query above. What both runs measure the same way is the spend: none on 10 October, $4,048 two days later.

The week before, complete on both dates, cost $20.61 per order. So cost per order rose by about a quarter, and the stalled load showed an empty cell, which a dashboard draws as a blank or a zero. These are blended figures on all orders, and order value is not revenue. Had orders been the partial side instead, the ratio would have moved the other way; with both partial, you cannot tell which way. Nothing errors in any of these cases.

A half-loaded day passes freshness and fails volume

The demo has no load timestamps, so this case is constructed: we took the real orders of Sunday 11 October and kept those whose order ID hashes to an even number, as a stand-in for a load that stopped part-way. Then both checks ran on what was left:

WITH loaded AS (
  SELECT order_id, order_date
  FROM `owox-demo.ecommerce.orders`
  WHERE order_date < '2026-10-11'
    OR (order_date = '2026-10-11'
      AND MOD(ABS(FARM_FINGERPRINT(
        order_id)), 2) = 0)
),
daily AS (
  SELECT order_date AS day,
    COUNT(*) AS orders
  FROM loaded
  GROUP BY day
)
SELECT
  MAX(day) AS latest_day,
  DATE_DIFF(DATE '2026-10-12',
    MAX(day), DAY) AS days_behind,
  SUM(IF(day = '2026-10-11',
    orders, 0)) AS orders_that_day,
  AVG(IF(day IN ('2026-10-04', '2026-09-27',
    '2026-09-20', '2026-09-13'),
    orders, NULL)) AS baseline
FROM daily

Run on 12 October: the latest day is 11 October, one day behind, so the freshness check passes. The day holds 4 orders against a baseline of 11.75 for the four Sundays before, which is 34%, so the volume check fires. The full day had 12 orders, 102% of the baseline.

The demo also produced the real thing, and there the threshold failed us. On 11 October the volume check compared 10 October’s 11 orders with a baseline of 15.5: 71%, one point above the line, a pass. On 12 October the same day held 18 orders. The day we passed was at 61% of its final count. We do not know whether those orders arrived late or the demo was reloaded; either way, a 70% line on a table this small is too loose, and a check that runs once, early, sees a day that is still filling.

The sorting into verdicts is the analyst’s job; checks make sure it happens before the number is quoted.

Running the checks on every Data Mart

In OWOX Data Marts, data quality checks are attached to the Data Mart itself: a fixed set on the whole Data Mart, its fields and its relationships, run on demand or by a Data Quality Run trigger (documentation). They cover checks 1 to 5 above, not the volume check, and the documentation does not describe a check written in your own SQL.

What the built-in checks reported on the same data

On 12 October we opened the Data Quality tab of three demo Data Marts, Purchases, Customers and Unified Ad Spend, and read what was there. We started no run and changed nothing.

What passed on Purchases: empty table, primary key uniqueness on purchase_id, null rate on three key fields, and relationship integrity to Products. That agrees with our queries. No check found a violation, so there were no example values to look at.

What did not finish. On each of the three, the seventh check, a relationship integrity check, ended with the status Execution error rather than a pass or a fail. That is one of the four results the documentation lists for a check, next to Passed, Failed and Not applicable, and it means the check tells you nothing about that relationship until it runs. Treat an Execution error as an open question, not a pass: the report shows the SQL behind every check, so you can see what it tried to test.

What the built-in checks did not see. Three things our queries found:

• The 149 customers. Reverse relationship checks are listed, but they were switched off in all three configurations.
• The order-and-product repeats. The declared key is purchase_id, and the check tests the key you declared.
• The stalled ad spend. On Unified Ad Spend the date field is a DATE, and the picker offers four checks for it: column uniqueness, constant column, null rate and type mismatch. Freshness is not on the list.

And the last run was on 23 September, 17 days before we found the stall. A check that is configured but not run sees nothing.

The freshness check needs a timestamp

The documentation explains the missing option: the freshness check works only on a field that marks an instant, such as TIMESTAMP in Google BigQuery, and is not available for Snowflake fields. For a date-only table, expose a load timestamp as a field in the Data Mart, or schedule the threshold query above, as the next section shows.

The separate Data Last Updated indicator tells you when the source tables were last written to. The documentation is explicit that “updated today” does not mean “covers today”: a load can run this morning and still end on 3 October. Read it as “the pipe ran”, and use a check on the dates in the data for “the data is current”.

Getting told when a check finds a problem

For every check that matters, decide who is told, how, and by when.

In OWOX Data Marts, as documented today, nobody is told. Notifications by email or webhook cover Data Mart runs that fail or succeed (notification settings), and a data quality run that completes and finds issues is not a failed run. For an alert, use the warehouse.

In BigQuery: a scheduled query that fails on purpose. The ERROR function stops a query with your own message (Google’s reference). Wrap the freshness check in it:

SELECT IF(
  COUNT(*) = 0
    OR DATE_DIFF(CURRENT_DATE(),
      MAX(date), DAY) > 1,
  ERROR('ad spend is stale or empty'),
  'ok') AS status
FROM `owox-demo.ecommerce.ad_performance`

On the demo it fails with “ad spend is stale or empty”, and it also fails on an empty table, which the date condition alone would let through. Schedule it to run daily, early enough to fix the load before the report is read, and turn on Send email notifications in the scheduled query’s settings (scheduling queries). BigQuery then emails the account that set up the schedule when a run fails; you cannot change that recipient. To reach a shared inbox or a chat channel, add a Pub/Sub topic, which receives a message for every succeeded, failed or cancelled run (run notifications). Any check above that returns rows on a problem can be wrapped the same way.

In Snowflake: an alert. An alert runs a condition on a schedule and acts when it returns rows, for example by sending an email through a notification integration (alerts). Notification integrations can also call a webhook, and Snowflake documents Slack, Microsoft Teams and PagerDuty among the targets (notifications). The “a row means a problem” shape is exactly what an alert condition wants.

In an orchestrator. If Airflow already runs your loads, put the check in a task that fails when it returns rows; Airflow’s on_failure_callback and notifiers send the alert (callbacks).

Whichever route you pick, send it to a second person or a shared channel, not only to yourself.

What a passing check does not prove

• A passing check proves the property you declared, nothing more. This key is unique, this field is under its null threshold, this join finds its matches. It does not prove that revenue is defined the way finance defines it. That is the other half of the question, covered in one source of truth for your numbers.
• It does not stop anyone using the data, and it does not watch for the unexpected. An Error finding informs you; a problem nobody wrote a rule for goes unseen.
• It does not ping you about findings, as documented today. Use the warehouse route in the section above.

If you already test in dbt or the warehouse

Freshness in dbt. dbt source freshness compares a source’s newest load time with warn_after and error_after thresholds, read from a loaded_at_field you name (freshness). That measures the pipe: a load that ran this morning with half a day of rows passes. It is the same point as “updated today” against “covers today”, so pair it with a check on the dates and volume in the data.

If you do not own the dbt project, you can still attach a check to the Data Mart you publish from its tables.

Where to start this week

1. Pick the Data Mart behind the report your most senior reader opens.
2. Write down what a row is. One sentence, plus the column or columns that identify it. That is your primary key check.
3. List the joins the report depends on and check both directions. Decide, for each gap, whether it is a fault or a rule.
4. Check the latest date and yesterday’s volume of every source against a threshold, especially ad spend and anything loaded from a file.
5. Schedule the checks before the report is read, and set up who is told when one fails, plus a second person.

The next section is written to be forwarded. Fill in the brackets before you send it.

For the person who asked: the short version

You asked whether [the number] is right. That is two questions.

Does it mean what you think? Which orders, which dates, before or after refunds. That is a definition, and finance or the team that owns the number sets it.

Is the data under it sound? No doubled rows, no missing or half-loaded days, nothing lost in a join. That part is mine. [Last week, a half-loaded day made cost per order look terrible] or [a stalled spend load left cost per order blank] or [a partly loaded spend made ROAS look too good]; this check now catches that. Each finding comes with the query that produced it, so anyone can rerun it.

Two limits, in plain words. Our tool’s own freshness check cannot read our date-only tables, so I run my own. And the tool does not message anyone when a check finds a problem, so I have set up [an email from the warehouse to me and to the analytics channel], so [a colleague] sees it when I am off.

What you will receive. If a check fails, a note from me by [9:00] naming the number affected and whether to hold it, before the [9:30 Monday] meeting. If nothing fails, a one-line all-clear at the same time. No message at all means the checks did not run, and the numbers should wait. A pass means the checks I set up passed that morning; it does not mean the definition is right, and it does not catch a problem nobody wrote a rule for.

What I need from you. Tell me which report you rely on most, so I start there, and who decides the definition of [the number] if two teams disagree.

If in doubt, ask: “When did the checks on this last run, and did anything fail?”

Data QualityCommon Data Quality Issues and How to Fix ThemOctober 14, 2026→AI in AnalyticsEveryone in the meeting has a different number: how to get one source of truthOctober 10, 2026→Data QualityWho is Responsible for Data Quality? A RACI for Analytics TeamsOctober 14, 2026→

See all articles →