Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Business Intelligence for Marketing

Writing Data Tests for Marketing Tables

Write SQL tests that catch broken marketing data before a dashboard does: duplicates, missing values, unmapped rows, freshness, volume and reconciliation.
Tutorialadvanced10 min readUpdated

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_daily and sessions, 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.
  1. 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 KPI 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.

  2. 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.

  3. 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 Deduplication 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(*) > 1
    

    Test 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.

  4. 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.

  5. 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_name
    

    Run the same pattern for session source and medium pairs that map to no channel in your Channel Grouping, and for campaign values on sessions that do not parse under the Naming Convention. 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.

  6. 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 GA4 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.

  7. 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.

  8. 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.

  9. 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.

Illustrative example — not a real result

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.

TestResultFailing rowsDetail
Unique order IDFailed214214 order IDs appear twice, all after 10:40 on Tuesday
Revenue against platform totalsFailed2Tuesday £31,860 against £24,150; Wednesday £42,300 against £30,950
Volume within rangeFailed1Wednesday orders 412 against a range of 238 to 301
Required fieldsPassed0
FreshnessPassed0

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.

Frequently asked questions

Do I need a testing tool to test marketing tables?

No. A data test is a query that returns the rows breaking a rule, plus a schedule and somewhere to record the result. Plain SQL, the warehouse's own scheduler and a results table are enough for most marketing teams. Dedicated tools add conveniences such as test declarations next to the table definitions and built-in reporting, which become worth having when the number of tables and tests grows; the rules and the queries stay the same either way.

How is this different from the weekly data quality check?

The weekly check reads reports: a person looks at key events, campaign names and channel totals and spots what looks wrong. Data tests run inside the warehouse on every load, without a person, and check the tables before any report reads them. The two complement each other. Tests catch the mechanical failures fast and every time; the weekly check catches the problems that only make sense to someone who knows the business, such as a campaign that should have launched and did not.

What should happen to the dashboard when a test fails?

It depends on the test's severity, which should be decided when the test is written. A blocking failure, such as duplicated orders or a missing day, should stop the load from publishing to the reporting tables or mark the affected dashboards as stale, so that nobody quotes a wrong number. A warning, such as a handful of unmapped campaign rows, should let the load through and notify the owner. Either way the dashboard's viewers should be able to see when the data last passed its tests.

Terms used on this page

Where this page is used

Part of these learning paths

Also related

Sources

  1. [GA4] Set up BigQuery Export — Google Analytics Help

Related