Why tables need tests
The data flow and integrations guide names the checks every flow needs: freshness, volume, completeness and reconciliation. The data quality guide runs a version of them weekly by reading reports. Both assume someone looks. Tests remove that assumption: each rule becomes a query, each query runs after every load, and a failure produces an alert with the failing rows attached rather than waiting for a person to notice a number that looks odd.
The pattern is simple and worth keeping strict. Every test is a query that returns the rows that break its rule. No rows means the test passed. Any rows means it failed, and the rows themselves are the evidence: which orders are duplicated, which campaigns are unmapped, which day is missing. A test that returns a yes or no is harder to act on than one that returns the problem.
Tests are not a replacement for good loading or modelling. They are the safety net under them, and they turn the quiet failures of a marketing warehouse into loud ones while the damage is still one day's data rather than a month's report.
Before you start
- Write access to a schema for tests and results in the warehouse, and permission to schedule queries.
- The SQL patterns in the SQL for marketers guide; the tests use the same
generic tables,
orders,ad_spend_dailyandsessions, plus a campaign mapping table. - The source systems' own totals, such as the commerce platform's daily order and revenue figures and the ad platforms' spend, available as a table or an export the tests can read.
- A list of owners for each table and each source, who will receive the failures.
- The definitions of the metrics the tables feed, from the metrics layer, so that the rules match what reports promise.
- A results table, created once, where every test run writes its name, time, table, number of failing rows and severity.
Pick the tables that feed decisions
List the tables that dashboards, reports and budget decisions read, and start with those. For most marketing warehouses that means the orders or revenue table, the daily spend table, the sessions table built from the analytics export, the leads table from the CRM and the campaign mapping table. Rank them by the decisions that rest on them: a table behind the monthly revenue report and every KPIA metric tied directly to an objective. on the certified dashboards comes before a table used by one analyst. The dashboard governance guide requires certified dashboards to read from defined, reconciled sources, and the tested tables are what makes that requirement checkable.
Write the rules each must meet
For each table, write the rules in plain language before writing any SQL, with the table's owner. Most rules fall into seven kinds: the key is unique; required fields are present; values come from an accepted list; every row maps to a channel and campaign; the latest data is recent enough; the row count is within the usual range for that day; and totals agree with the source system within an agreed tolerance. Give each rule a severity. Blocking rules stop the reporting tables from refreshing or mark them stale, because a report built on the failure would be wrong. Warning rules let the load through and notify the owner. Keep the rules in a table or document with the test name, the table, the rule, the severity and the owner, because that list is the test suite's specification.
Test keys for duplicates
Every fact table has a grain, and the key at that grain must be unique: one row per order, one row per day, account and campaign in the spend table, one row per user and session identifier in the sessions table. A duplicate key doubles whatever is summed over it, so this is usually a blocking test, and the first line of DeduplicationRemoving double counts when one conversion is reported by more than one source. is catching duplicates as they arrive rather than removing them later.
-- Orders: transaction identifier must be unique SELECT order_id, COUNT(*) AS rows_for_key FROM orders WHERE order_date BETWEEN @window_start AND @window_end GROUP BY order_id HAVING COUNT(*) > 1Test the window the load touched rather than the whole history on every run, and the whole history on a slower schedule. For the spend table the key is the combination of date, account and campaign identifier; for sessions, the user identifier with the session identifier, which is how the export defines a session.
Test required fields and accepted values
Required-field tests return rows where a column the reports depend on is empty: an order without a date, revenue or currency; a spend row without a campaign ID; a session without a source. Accepted-value tests return rows whose value is outside the agreed list: a channel name that is not in the channel table, a market code that is not one of the markets, a currency the business does not trade in, a negative quantity.
-- Orders: required fields present and values accepted SELECT order_id, order_date, revenue, currency, market FROM orders WHERE order_date BETWEEN @window_start AND @window_end AND (order_date IS NULL OR revenue IS NULL OR currency NOT IN (SELECT currency FROM accepted_currencies) OR market NOT IN (SELECT market FROM dim_market))Keep accepted lists in tables, not in the test text, so that adding a market is one row rather than an edit to every test that mentions markets.
Test that every row maps to a channel and campaign
Spend, sessions and leads only meet on channel and campaign keys if every row has them. The mapping test returns rows that reached the reporting layer without a channel or campaign key, with the spend or sessions attached so the size of the gap is visible.
-- Spend rows with no campaign key in the mapping table SELECT s.platform, s.campaign_id, s.campaign_name, SUM(s.spend) AS unmapped_spend FROM ad_spend_daily AS s LEFT JOIN map_campaign AS m ON m.source = s.platform AND m.source_id = s.campaign_id WHERE s.spend_date BETWEEN @window_start AND @window_end AND m.campaign_key IS NULL GROUP BY s.platform, s.campaign_id, s.campaign_nameRun the same pattern for session source and medium pairs that map to no channel in your Channel GroupingRules that assign each visit or conversion to a channel, such as organic search or email, from its source, medium and campaign values., and for campaign values on sessions that do not parse under the Naming ConventionAn agreed pattern for naming campaigns, ads, events and tracking parameters so that reports can group them reliably.. These are usually warnings rather than blocking failures, routed to whoever owns the mapping, with a rule that the warning must be cleared before month end.
Test freshness and volume
Freshness returns a row when a table's latest date is older than expected. Set the expectation per source from how it actually arrives. For the GA4Google's event-based analytics platform. export, the basics guide notes that each daily table appears once Google has finished processing the day, usually during the following day but not at a fixed hour, so the freshness test for tables built on it should allow for that and run after the usual arrival time. Where a dashboard has already stopped moving and the stalled layer is not yet known, the diagnosis for a Looker Studio report not updating works back from the reader through the delivery, the cache, any extract and the table to the source.
-- Freshness: latest order date older than expected SELECT latest_date FROM (SELECT MAX(order_date) AS latest_date FROM orders) WHERE latest_date IS NULL OR latest_date < DATE_SUB(CURRENT_DATE(), INTERVAL @expected_lag_days DAY)Volume returns a row when the latest day's row count falls outside the range seen on the same weekday over recent weeks, because marketing volume follows the week. The range is set from your own history, and the tolerance is a parameter you tune, not a fixed figure.
-- Volume: latest day's orders against the same weekday in recent weeks WITH daily AS ( SELECT order_date, COUNT(*) AS orders FROM orders WHERE order_date >= DATE_SUB(@check_date, INTERVAL @history_weeks WEEK) GROUP BY order_date ), history AS ( SELECT MIN(orders) AS low, MAX(orders) AS high FROM daily WHERE EXTRACT(DAYOFWEEK FROM order_date) = EXTRACT(DAYOFWEEK FROM @check_date) AND order_date < @check_date ) SELECT d.order_date, d.orders, h.low, h.high FROM daily AS d CROSS JOIN history AS h WHERE d.order_date = @check_date AND (d.orders < h.low * (1 - @tolerance) OR d.orders > h.high * (1 + @tolerance))Expect volume tests to fire on genuine events such as a sale, a holiday or an outage. That is the point: someone should confirm which it was.
Test totals against the source system
Reconciliation tests compare the warehouse with the system that owns the number: orders and revenue against the commerce platform's own daily totals, spend against each ad platform's totals, leads against the CRM. They return the days where the difference exceeds an agreed tolerance, which the table's owner sets from what normal differences look like, such as orders still being edited on the day of the load.
-- Revenue per day against the commerce platform's own totals WITH wh AS ( SELECT order_date, SUM(revenue) AS revenue FROM orders WHERE order_date BETWEEN @window_start AND @window_end GROUP BY order_date ) SELECT src.order_date, src.revenue AS source_revenue, wh.revenue AS warehouse_revenue FROM source_daily_totals AS src LEFT JOIN wh USING (order_date) WHERE src.order_date BETWEEN @window_start AND @window_end AND (wh.revenue IS NULL OR ABS(wh.revenue - src.revenue) > @tolerance * src.revenue)Keep reconciliation honest about definitions: if the warehouse removes refunds and the source does not, compare like with like or the test will fail every day and be ignored. Where the warehouse holds tables built from the analytics export, the diagnosis of an export that does not match GA4 lists the differences that are expected rather than errors, so the tolerance can allow for them.
Schedule the tests with each load
Run the tests immediately after each load and before the reporting tables refresh, so that a blocking failure can stop bad data reaching dashboards. Each run writes one row per test to the results table: the test name, the table, the run time, the number of failing rows and the severity, with the failing rows themselves saved to a table that keeps them for investigation. A dashboard of the results table, with the latest status per test and a history of failures, shows at a glance whether today's data can be trusted and which tests fail most often. Run the whole-history versions of the key and reconciliation tests on a slower schedule, such as weekly, since they are heavier and catch slower problems.
Route failures to an owner
Every test has an owner, and every failure goes to that owner directly with the test name, the rule it checks, the number of failing rows, a sample of them and the last time the test passed. Shared inboxes and general channels collect alerts nobody reads. Blocking failures also mark the affected dashboards as stale until the owner clears or explains them. When a failure suggests that measurement itself has broken, such as orders recorded in the commerce platform but missing from the analytics-based tables, the owner escalates to the tracking outage response SOP, which covers confirming the outage, protective action in the ad platforms and recovery.
A test suite for an orders table (illustrative)
The example below shows the suite in its first weeks on one table.
Gristwhirl Bakery Supplies catches duplicated orders
Gristwhirl Bakery Supplies, an invented wholesaler of baking ingredients and equipment, writes seven tests for its orders table: unique order ID (blocking), required fields (blocking), accepted currency and market (warning), every order mapped to a channel (warning), freshness (blocking), volume within the same weekday's range over eight weeks with a 20 per cent tolerance (warning) and daily revenue within 0.5 per cent of the commerce platform's totals (blocking). The first week passes except for two volume warnings on a bank holiday, which the owner confirms and closes. On the Tuesday of the second week a checkout release starts sending the confirmation step twice for orders paid by card.
| Test | Result | Failing rows | Detail |
|---|---|---|---|
| Unique order ID | Failed | 214 | 214 order IDs appear twice, all after 10:40 on Tuesday |
| Revenue against platform totals | Failed | 2 | Tuesday £31,860 against £24,150; Wednesday £42,300 against £30,950 |
| Volume within range | Failed | 1 | Wednesday orders 412 against a range of 238 to 301 |
| Required fields | Passed | 0 | |
| Freshness | Passed | 0 |
The blocking failures stop the reporting tables refreshing and mark the sales dashboard as stale. The analytics owner traces the duplicates to the release, the developers fix it on Wednesday afternoon, and the cleaned layer removes the second copy of each order. Without the tests, the month's revenue in the reporting pack would have been overstated by £19,060, and paid search, which closed most of the card orders, would have looked a fifth more efficient than it was.
Living with failing tests
A test suite is only useful while people believe it. Three habits keep it believable.
Fix or tune tests that fail without a real problem. A volume test that fires every Monday because the range ignores a weekly pattern, or a reconciliation test that fails every day because the definitions differ, teaches everyone to ignore the alerts, including the one that matters. Either fix the rule or fix the data, and record which.
Record known failures with an expiry. When a failure is understood and accepted for a while, such as unmapped spend from a campaign whose key is being agreed, record it as known with an owner and a date by which it must be resolved, rather than switching the test off. A disabled test is forgotten; an expiring exception comes back.
Add a test after every incident. Every data problem that reached a report without a test catching it is a missing rule. Write the rule, write the test, and add it to the suite, so that the same failure cannot reach a report twice. Over a year the suite becomes a record of everything that has gone wrong, which is the most useful specification a marketing table can have. The academy checkpoint on data models, platform loads and data tests tests which checks a marketing table needs and what happens when one fails, from who is told to what the dashboard shows.
The BI hub places testing beside the model, the metrics layer and dashboard governance.
Common mistakes
- Tests that return yes or no. Return the failing rows; they are the evidence and the starting point for the fix.
- Testing only the whole history. Test the window each load touched on every run, the history on a slower schedule.
- Thresholds copied from somewhere else. Set ranges and tolerances from your own data and tune them.
- No severity. Without blocking and warning levels, either everything stops or nothing does.
- Alerts to a shared inbox. Failures go to a named owner with the failing rows attached.
- Switching off noisy tests. Fix the rule or record an expiring exception.
- Reconciling unlike definitions. A test that fails every day is a test nobody reads.
- Testing after the dashboards refresh. Run tests between the load and the reporting tables.
