A green pipeline proves only that the jobs finished. A successful refresh proves only that a refresh ran. A passing test suite proves only that the failures those tests were written to find did not occur. None of them proves that “net revenue, yesterday” matches the definition the business agreed on.
The fix is a layered set of scheduled SQL assertions: freshness, required values, uniqueness at the intended grain, relationship validity, and at least one check that encodes the metric’s own definition. Then show the result next to the number, labelled for what it covers. The sections below give the queries and the decisions behind them.
As an Amazon Associate I earn from qualifying purchases.
Why “green” is not the same as “correct”
Stakeholders usually notice the loud failures first. dbt Labs’ guidance on analytics checks uses a typical complaint as its example: “This dashboard hasn’t refreshed in over a day… what’s going on here?” That is a freshness failure, and it is the easy kind to see. The harder kind is a dashboard that refreshes on time and shows a wrong number. A join fan-out duplicates orders, a filter drops a region, or a feed arrives with yesterday’s data only half loaded.
Free tools Windows power users keep installed
One-click scans. No signup required.
Different checks answer different questions, so name them accordingly:
#1 Best Overall
- Pipeline completed: the orchestrator reported success.
- Data is fresh: the latest expected data arrived within the allowed window.
- Structure is sound: keys are present, unique and resolve to real records.
- Metric reconciled: the published value agrees with an independently defined reference within an agreed tolerance.
Each layer rules out a different class of failure. Only the last speaks to the metric’s meaning.
The check suite
These are implementation patterns, not production queries. Adapt syntax to your warehouse. Before writing any of them, settle the grain of each table, the time zone for business dates, and your policy for late-arriving data. Every check below assumes those answers.
1. Required values
For fields the contract says must exist, count violations. dbt’s analytics-check guidance includes not-null style tests for this.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsSELECT COUNT(*) AS invalid_rows
FROM analytics.orders
WHERE order_id IS NULL
OR order_date IS NULL;Expected result: zero, if those fields are contractually required. A null date silently drops a row from every date-filtered metric, which is why it belongs here.
2. Uniqueness at the intended grain
A table called a fact table is not necessarily unique on the key you assume. Check the actual grain.
SELECT order_id, COUNT(*) AS row_count
FROM analytics.orders
GROUP BY order_id
HAVING COUNT(*) > 1;Expected result: no rows when order_id is the declared grain. If the table is at line-item grain, group by the line identifier or a composite key instead. Duplicates at the wrong grain are the classic cause of inflated sums after a join.
3. Relationship validity
Find facts whose dimension key does not resolve to an upstream record. dbt describes relationship tests for exactly this mapping.
Recommended Free Tools
SELECT COUNT(*) AS orphan_rows
FROM analytics.orders AS o
LEFT JOIN analytics.customers AS c
ON o.customer_id = c.customer_id
WHERE o.customer_id IS NOT NULL
AND c.customer_id IS NULL;Expected result: zero, unless your model explicitly allows unknown or late-arriving dimensions. If it does, test for the documented placeholder member instead, so orphans still surface.
4. Freshness
Query the source’s load timestamp, then compare it with the arrival you expect.
SELECT MAX(loaded_at) AS latest_loaded_at
FROM raw.orders;A bare MAX only returns a value. The check is the comparison against explicit warning and error boundaries derived from the service expectation. dbt’s source freshness supports warn_after and error_after thresholds, plus loaded_at_field or loaded_at_query to say how the timestamp is found. The documentation distinguishes materializations, which affects what metadata is available. Its stated scope applies to dbt v2.0 and later, so check the docs for the version you run. Great Expectations documents timestamp-based freshness validation and custom SQL Expectations; its documentation page surfaced version 1.23.2 at the time of writing.
Remember that “latest timestamp is recent” is weaker than “the expected period is complete”. A feed that delivered only the first hour of a day is fresh and wrong. Where it matters, add a row-count or distinct-partition check for the window the metric covers.
5. A metric-specific business assertion
Generic checks cannot know what your metric means. Each important metric needs at least one assertion that states its definition: a reconciliation to a separately defined reference, an agreed bound on a rate, or an invariant such as “refunds never exceed the original charge”. This is editorial guidance rather than a documented standard. Choose the rule with the metric owner.
An illustrative reconciliation of daily revenue against a ledger:
WITH published AS (
SELECT SUM(net_revenue) AS value
FROM marts.daily_revenue
WHERE business_date = CURRENT_DATE - INTERVAL '1' DAY
),
reference AS (
SELECT SUM(net_amount) AS value
FROM finance.ledger_lines
WHERE business_date = CURRENT_DATE - INTERVAL '1' DAY
)
SELECT published.value AS published_value,
reference.value AS reference_value,
published.value - reference.value AS difference
FROM published CROSS JOIN reference
WHERE ABS(published.value - reference.value) > :approved_tolerance;The query returns a row only on failure. It is a teaching template: a ledger is not always the right reference, and no tolerance here is approved. Write down inclusion rules, currency handling, time zone, how restatements are treated, and the permitted variance.
Be wary of tempting shortcuts. “Revenue must be positive”, “it must increase”, or “it must be within X% of yesterday” will fire falsely, or miss real errors, once discounts, refunds, seasonality, late events and restatements are in play. If you use a day-over-day band, treat it as a warning for investigation, not proof of an error.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Scheduling the checks
- Tie cadence to arrival and completion. Run source freshness at a rhythm matched to when data should land. Run model-level checks after the build or refresh they validate, not on an unrelated clock. dbt provides a command to check freshness for configured resources.
- Treat examples as examples. Great Expectations’ documentation shows an hourly freshness schedule. That demonstrates one possibility, not a standard. Pick cadence from business latency needs, warehouse cost, and whether anyone can actually respond to an alert at that frequency.
- Record every run. Store the check name, target metric or table, run time, observed value, threshold, severity, and a link to failing rows or query details where it is safe to expose them. Without the observed value and threshold, an alert is hard to triage.
- Separate warning from error. A late non-critical feed might warn. A failed invariant on a published financial metric might block publication. The tools provide warn and error mechanisms, but the severity policy is yours to agree with data consumers.
Making the result visible next to the number
A check nobody sees does not protect a reader. dbt’s data-health tile, which can be embedded in dashboards fed by dbt models, reflects freshness and test status for the data behind it. Its quality state fails if dbt tests fail. That is useful, but it covers only what those tests cover.
So name the indicator precisely. “Pipeline ran at 06:10” is not “data fresh as of 06:00”, which is not “13 checks passed”, which is not “reconciled to the ledger within tolerance”. If a tile or badge says only “healthy”, readers will assume the strongest meaning. Put the covered scope, the last-checked time and a link to the metric definition beside the number.
Choosing where to run them
| Question | dbt | Great Expectations |
|---|---|---|
| Where checks live | Alongside transformations in the project | In expectation suites, defined and run separately |
| Freshness | First-class source and model configuration with warn and error thresholds | Timestamp-based and custom SQL freshness checks |
| Custom business rules | SQL-based tests | Custom SQL Expectations |
| Surfacing results | Data-health tile for dashboards fed by dbt | Not stated in the material reviewed; depends on your integration |
The documentation does not establish a neutral ranking on performance, cost or features, so choose on fit: where your transformations already live, how results reach your scheduler and on-call workflow, and how much extra tooling you can operate. Plain scheduled SQL that writes to a results table is also a legitimate starting point.
Limits of every check
A passing test rules out only the failure it was designed to find. Keep the metric’s definition and each check’s blind spots documented together, for example “uniqueness passes but does not detect missing orders”. No source reviewed shows that a particular checklist prevents a fixed share of incidents, so treat this suite as a way to shrink the unmonitored area, not eliminate it.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
