Data Quality Invariants and Reconciliation

Consider an illustrative situation. A nightly pipeline loads card payments into a warehouse table called payments. The run is green, every column has the expected type, the primary key test passes, and the row count is within a few percent of yesterday’s. On Thursday the finance team reports that the daily captured total is about 2.4 percent below what the processor’s settlement file shows. Nothing failed. Every check that existed was satisfied, and the data was still wrong.

That outcome is not a sign of weak tooling. It is what you should expect when checks are chosen because they are easy to write instead of because they are tied to the ways the data can be wrong. Each kind of check can detect some faults and cannot detect others, and the useful engineering question is which faults a given check could ever have caught. This post builds that vocabulary in four parts: invariants that must hold exactly, statistical checks for properties that are only expected to hold approximately, reconciliation against an independent reference, and the arithmetic of detection limits and false positives that determines whether any of this is usable in operation.

This post is written for readers who have met metric definitions in a semantic layer and backpressure and replay in streaming pipelines, its two prerequisite topics. From the first it relies on one idea: a published number is only meaningful relative to a stated grain, filter set, and time window, so a check on a metric has to restate that definition. From the second it relies on another: replay and retries can apply the same effect more than once and can delay data past a cutoff, which is why a green pipeline run does not certify the data. Everything else needed here is explained below.

What a data quality check is, and what it is checking against

A data check is a predicate evaluated over a dataset, together with a decision about what to do when the predicate fails. The predicate always compares the data with something. The comparison target determines the strength of the claim the check can make, so it is worth naming the targets before listing techniques.

Compared against	Example	What a pass supports	What a pass cannot show
A rule fixed in advance	amount_minor > 0	Every row inspected satisfies the rule	Rows that never arrived; rows that satisfy the rule and are wrong
A relation among the data itself	Line items sum to the order total	Two parts of the dataset agree	An error that is consistent across both parts
The data’s own history	Row count within the usual range	The dataset resembles earlier loads	A fault that history also contains, or a legitimate change
An independent reference	Settlement file totals	Agreement with a second source, within its scope	An error shared by both sources, or records outside the comparison scope

The first row is the domain of invariants, the third is the domain of statistical checks, and the fourth is reconciliation. The second row sits between invariants and reconciliation: a relation within one dataset is an invariant in form, but it is only as independent as the two sides are. The data quality glossary entry lists dimensions such as completeness, uniqueness, validity, consistency, timeliness, and accuracy. Those dimensions say what can go wrong. The table above says what each kind of evidence can see.

Structural invariants

A structural invariant is a rule about the shape and identity of the data that should hold for every published version regardless of business meaning: columns exist with the agreed types, keys are unique and non-null, enumerated columns hold only permitted values, and references point at rows that exist. They are cheap to state, usually cheap to evaluate, and they catch a large share of mechanical faults such as a changed export format, a duplicated delivery, or a failed join.

dbt’s documentation illustrates the pattern well. In dbt, a data test is a SQL select statement that returns the rows that disprove an assertion, and the test passes when it returns zero rows. The four generic tests that ship with dbt are unique, not_null, accepted_values, and relationships. dbt also supports singular tests, which are single SQL queries saved as files, and custom generic tests (documentation read on 3 October 2026). The failing-rows form is a good general design even outside dbt, because a test that returns the offending rows is also a diagnosis.

Here is a small illustrative payments table of six rows that was constructed for this post. It contains a null amount, a negative amount, a duplicated key, an orphaned order reference, and a pending payment that carries a capture time. The queries below were run in SQLite to confirm what each returns. The setup was:

CREATE TABLE payments(payment_id TEXT, order_id TEXT, status TEXT, amount_minor INTEGER,
                      captured_at TEXT, created_at TEXT);
INSERT INTO payments VALUES
 ('p1','o1','captured', 1500,'2026-03-01','2026-03-01'),
 ('p2','o2','captured', NULL,'2026-03-01','2026-03-01'),
 ('p3','o3','captured',-200,'2026-03-02','2026-03-02'),
 ('p3','o3','captured',-200,'2026-03-02','2026-03-02'),
 ('p4','o4','pending',  900, '2026-03-03','2026-03-04'),
 ('p5','o9','captured', 700,'2026-03-03','2026-03-03');
CREATE TABLE orders(order_id TEXT);
INSERT INTO orders VALUES ('o1'),('o2'),('o3'),('o4');

Each rule is written as a query that returns the rows breaking it.

-- Rule: amount_minor must be positive.
SELECT payment_id FROM payments WHERE NOT (amount_minor > 0);
-- returns p3, p3   (the NULL amount in p2 is NOT returned)

SELECT payment_id FROM payments WHERE amount_minor IS NULL OR amount_minor <= 0;
-- returns p2, p3, p3

-- Rule: payment_id is unique.
SELECT payment_id, count(*) FROM payments GROUP BY 1 HAVING count(*) > 1;
-- returns p3 with count 2

-- Rule: every payment refers to an existing order.
SELECT p.payment_id FROM payments p
LEFT JOIN orders o USING (order_id) WHERE o.order_id IS NULL;
-- returns p5

-- Rule: a pending payment has no capture time.
SELECT payment_id FROM payments WHERE status = 'pending' AND captured_at IS NOT NULL;
-- returns p4

The first two queries show the most common mistake in hand-written checks. In SQL, a comparison involving NULL evaluates to unknown rather than true or false, and a WHERE clause keeps only rows for which the condition is true. NOT (amount_minor > 0) is unknown for a NULL amount, so the row is not returned and the rule appears satisfied. The same property is why a CHECK constraint accepts NULL. If a column must be present, the rule needs its own explicit IS NULL clause, or the column needs a separate not_null test. The SQL NULL and three-valued logic entry covers the logic in more detail, and it matters here because an invariant that cannot fail on NULL is silently weaker than it reads.

Three limits apply to every structural invariant.

• A failing-rows test inspects rows that exist. A rule such as “no null customer id” cannot fail on a row that never arrived. An empty table returns zero failing rows and therefore passes every row-level rule. Completeness needs a different kind of evidence, which is the subject of the reconciliation section.
• Uniqueness is relative to a defined key. The duplicate in the example is caught because the same payment_id appears twice. A retry that generates a new identifier for the same logical payment produces two unique keys and passes. The primary and foreign keys entry describes declared keys, and the event deduplication entry describes why identity must be assigned when an event is created and preserved through retries; otherwise a key test measures only that the pipeline assigned distinct labels.
• Passing says nothing about meaning. accepted_values can confirm that a status is one of the permitted strings. It cannot confirm that captured was assigned to a payment that was really captured.

Business invariants

A business invariant is a rule that follows from what the data represents rather than from how it is stored. These rules tie several columns, rows, or tables together through the meaning of the domain. For the payments example, a few of them are:

• State and timestamp consistency. A payment in pending has no capture time. A captured payment has a capture time no earlier than its creation time.
• Conservation across stages. The sum of captured amounts for a day, minus refunds, equals the amount added to the daily revenue measure, within the rounding rule stated in the metric’s definition.
• Composition. The line amounts of an order sum to the order total in the same currency, and the minor-unit scale is the same across all rows of one currency.
• Legal transitions. An order can move from placed to shipped but not from cancelled to shipped. This is a rule about pairs of consecutive states, so the check reads two rows per order.
• Cross-system accounting identities. Every captured payment belongs to an order, and the order’s paid amount is the sum of its captures minus its refunds.

Business invariants are harder than structural ones in two ways. First, nobody can derive them from the schema. They have to be written down by someone who can say what must always be true and who is accountable for that statement, which is why a definition owner matters in the same way it does for a metric definition. Second, a rule that is true in the source system may be false in the warehouse for legitimate reasons. A source may enforce “captured amount does not exceed authorized amount” at write time, while a warehouse that loads payments and authorizations on different schedules can briefly see a capture whose authorization has not yet arrived. A check that fires on that transient state is not detecting corruption, and the fix is a stated tolerance for in-flight data and a cutoff, not a looser rule.

A useful way to organize invariants is by scope, because scope determines where and when the check can run.

Scope	Example	Needs	Typical failure it finds
Row	captured_at >= created_at	One row	Bad timestamps, unit mistakes
Key set	payment_id unique	The whole table	Duplicated delivery, replays
Group	Lines sum to order total	A group-by	Partial loads, missing lines
Cross-table	Payments reference orders	A join	Out-of-order loading, dropped parents
Cross-time	status transitions are legal	Prior versions	Mis-ordered updates, overwritten history

Cross-time rules are the ones most often missing, because a current-state table does not retain the previous state to check against. If the pipeline keeps only the latest version of each order, an illegal transition has already overwritten the evidence by the time a check runs, and the check has to run inside the load, against the incoming change and the stored row.

Severity and what a failure does

A failed check is only useful if it triggers a defined response. dbt’s severity config can be error or warn, with error as the default, and the error_if and warn_if configs set the failure count at which each response applies; both default to a condition of not equal to zero (documentation read on 3 October 2026). That gives two levels per check. As the quality threshold entry explains, the numbers behind the levels are statements about what the organization is willing to publish, so they should come from the consuming decision, not from whatever makes the suite pass today. A required identity rule, such as the uniqueness of a payment identifier, has a threshold of zero. A rule that tolerates missing optional values under a stated consumer contract can have a nonzero warning level.

A severity setting classifies a result. It does not by itself protect consumers. If the pipeline overwrites the public table and then runs the test, an error result tells you that bad data is already visible. Protection requires a publication design where the new version is built separately, validated, and only then made visible, for example by a switch to a validated snapshot. The data contract for the table, discussed in the data contract entry, should say which checks block publication and which only produce a visible warning.

Statistical checks

Many properties that matter cannot be written as exact rules. Daily payment volume is not fixed. The share of orders with a missing postcode is not zero. What can be stated is an expectation: the value should usually fall within a range that earlier data suggests. A statistical check encodes that expectation and flags values that depart from it.

The simplest form is a control-style limit on a monitored quantity such as the daily row count. NIST’s engineering handbook describes the idea: a chart shows a center line for the in-control process and upper and lower limits chosen so that almost all points fall within them while the process is stable. The handbook also states the arithmetic that matters for operations. If the monitored quantity follows a normal distribution, three-standard-deviation limits correspond to a probability of 0.00135 in each direction, 0.0027 for both, and the handbook notes this risk is a deliberately chosen, arbitrary level. Where the quantity is skewed, three-sigma limits give a different risk. The handbook’s example is a Poisson quantity with mean 0.8, where the upper three-sigma limit is about 3.48 and the probability of exceeding it by chance is 0.009 rather than 0.00135 (NIST/SEMATECH e-Handbook, section 6.3.1).

Two properties of the z-score deserve attention. It measures a distance, and it becomes a probability statement only under a distributional assumption. Also, when the mean and standard deviation are computed from the same sample that contains the suspect value, the largest attainable absolute z-score is bounded by a function of the sample size, which makes a three-sigma rule unable to flag any point in a sample smaller than 11 under common conventions. A baseline that excludes the value under test avoids that ceiling but introduces a different problem, which the simulation below shows.

What a baseline has to model

The data’s own history is only a good baseline if the history is comparable. Payments data has a weekly cycle. Weekday volume is assumed to be 100,000 rows with a standard deviation of 2,000, and weekend volume 60,000 with the same standard deviation. These are assumed values for illustration, not measurements. A baseline that pools all days has a mean of about 88,571 and a standard deviation of about 18,180, because most of its spread is the weekly cycle rather than noise. In that baseline a typical weekend day has a z-score of about minus 1.6 and a weekday with a 10 percent drop (90,000 rows) has a z-score of about 0.08. The weekly pattern has made the check blind to a loss that, against a same-weekday baseline, would be five standard deviations.

The following Python simulation draws baselines and test days from those assumed distributions and counts how often a three-sigma rule fires. It uses the standard library only. The “seasonal” baseline uses the prior days of the same type, and the “naive” baseline uses 28 mixed prior days with 20 weekdays and 8 weekend days. It was run with the seed shown, 100,000 trials per line.

import random, statistics as st

random.seed(53)
WEEKDAY, WEEKEND, SD = 100_000, 60_000, 2_000     # assumed, not measured
TRIALS = 100_000

def zscore(x, baseline):
    return (x - st.fmean(baseline)) / st.stdev(baseline)

def flag(z, cut=3.0):
    return abs(z) > cut

def day(kind, drop=0.0):
    mean = WEEKDAY if kind == "weekday" else WEEKEND
    return random.gauss(mean * (1 - drop), SD)

def seasonal(kind, k, drop):
    base = [day(kind) for _ in range(k)]               # same weekday type only
    return flag(zscore(day(kind, drop), base))

def naive(kind, drop):
    base = [day("weekday") for _ in range(20)] + [day("weekend") for _ in range(8)]
    return flag(zscore(day(kind, drop), base))         # 28 mixed days

def rate(fn, *a):
    return sum(fn(*a) for _ in range(TRIALS)) / TRIALS

The output of the reporting lines, which call rate for each configuration, was as follows.

clean weekday, 3-sigma rule
  seasonal baseline, 8 prior same-type days : 0.02597
  seasonal baseline, 52 prior same-type days: 0.0042
  naive 28-day mixed baseline               : 0.0
clean weekend
  seasonal baseline, 52 prior               : 0.00481
  naive 28-day mixed baseline               : 0.0
weekday with a fraction of rows missing (detection rate per day)
  drop   1%: seasonal(52) 0.010   naive 0.000
  drop   5%: seasonal(52) 0.322   naive 0.000
  drop  10%: seasonal(52) 0.972   naive 0.000

Four observations follow, and each one is about the design of the check rather than about the numbers.

First, the naive pooled baseline never fires. Its false-alarm rate is zero because its standard deviation is inflated by the weekly cycle, and its detection rate for a 10 percent loss is also zero for the same reason. A check with no false alarms and no detections looks quiet and healthy.

Second, the seasonal baseline with 52 prior same-type days fires on clean days at 0.0042 and 0.0048, a little above the 0.0027 that normal theory gives for a known mean and standard deviation. The baseline’s own estimation error widens the effective distribution of the test statistic.

Third, shrinking the baseline to 8 prior same-type days raises the clean-day false-alarm rate to 0.026, roughly ten times the textbook figure. With only eight observations, the estimated standard deviation is itself noisy, and a sample that happens to have a small spread turns an ordinary day into an apparent outlier. This is a property of estimating spread from few points, and it is the reason to say how many prior observations a rule uses rather than quoting only the multiplier.

Fourth, the detection rates match the arithmetic of a shift measured in standard deviations. A 5 percent loss of 5,000 rows is 2.5 standard deviations, so the probability that the day lands below minus 3 is roughly the probability that a standard normal falls below minus 0.5, about 0.31. The simulation’s 0.322 is consistent with that. A 10 percent loss is 5 standard deviations and is flagged on 97.2 percent of days. A 1 percent loss is half a standard deviation and is flagged at 1.0 percent, which is essentially the false-alarm rate. The check cannot tell a 1 percent loss from normal variation.

Under the assumption that a persistent 5 percent loss is independent day to day, the expected wait before the first alert is about 1 / 0.31, or about 3.2 days, and the lost data for those days is already published unless the check blocks publication. The simulation and the calculation are both conditional on the assumed noise level. A real series has autocorrelation, level shifts, holidays, and promotions, and none of those are in the simulation.

Where statistical checks fit and where they do not

A statistical check is a fair fit when an exact rule does not exist, when the monitored quantity has a stable and well-understood distribution, and when a human can investigate alerts at the rate they arrive. It is a poor fit for faults that preserve the distribution. A duplicated delivery that affects 0.08 percent of a day’s rows, which is 2 percent of one hour’s rows, is 83 extra rows on a day with 100,000 rows. Against a standard deviation of 2,000 that is far below anything a three-sigma rule can see, yet a unique-key invariant or a reconciliation by key would find it immediately.

Three further limits are worth stating.

• The baseline can absorb the fault. A baseline that updates from recent data will gradually accept a persistent loss as normal. The alert fires on the day of the change and then stops, which people sometimes read as recovery.
• A statistical flag is a prompt to investigate, not a diagnosis. A 12 percent weekday volume drop may be a holiday, a marketing change, or a missing partition. A check that blocks publication on statistical grounds alone will block legitimate changes, so the response to a statistical flag is usually a visible warning plus a defined investigation, not an automatic block, unless the cost of publishing a wrong figure is higher than the cost of a false stop.
• The check measures one quantity. A row-count check does not look at amounts, and an amount check does not look at the mix of statuses. Each additional monitored quantity increases coverage and also increases the number of ways to raise a false alarm.

Detection limits and false positives

Every check has two error rates, and they trade against each other. A false positive is an alert on healthy data. A missed detection, or false negative, is silence on faulty data. The hypothesis testing entry states the structure: the significance level is chosen in advance, the miss rate follows from the size of the real discrepancy, and the two move in opposite directions when the threshold moves. The statistical power entry adds that power is defined relative to a specified effect size, so “this check is sensitive” means little until you say sensitive to what. For a monitoring check, the effect size is the smallest loss that matters to a consumer, and the check has a minimum detectable shift at a stated detection probability.

The calculation for a one-sided lower limit at three standard deviations, assuming a known normal distribution, is that a shift of \(d\) standard deviations is flagged on a given day with probability \(\Phi(-3 + d)\), where \(\Phi\) is the standard normal cumulative distribution function. The table lists calculated values, and they depend on the normality assumption.

Shift in standard deviations	Share of a 100,000-row day (assuming SD 2,000)	Probability flagged on one day	Expected days to first alert if persistent
1	2%	0.023	about 44
2.5	5%	0.309	about 3.2
3	6%	0.500	2.0
5	10%	0.977	about 1.0

The expected-days column assumes independent days. The point of the table is that the useful operating range of a three-sigma rule starts near three standard deviations. Anything smaller than that is a loss the rule will take weeks to notice, if ever, and a low noise level is what makes small losses visible at all. Lowering variance by choosing a better baseline, comparing like with like, or monitoring a ratio rather than a count does more for detection than tightening the multiplier.

Alert precision depends on how rare faults are

False positives are not only a nuisance. Their share among alerts depends on how often real faults occur. With a one-sided false-alarm rate of 0.00135 and a detection probability of 0.977 for a 10 percent loss, Bayes’ rule gives the share of alerts that are real. If 1 percent of days contain such a loss, then about 88 percent of alerts are real. If only 0.1 percent of days do, that share falls to about 42 percent. The same check with the same thresholds looks reliable in one environment and noisy in another, which is the prevalence effect described in the precision entry, and the recall entry describes the complementary side: the share of real faults the check ever catches.

Scale makes this worse. If one team monitors 200 independent metrics daily with a two-sided three-sigma rule, the expected number of false alarms per day is 200 × 0.0027, about 0.54, and the chance of at least one false alarm on a given day is about 42 percent. Over a year that is on the order of 200 spurious alerts. Alert fatigue then becomes a data quality risk, because people learn to dismiss alerts and the one real alert is dismissed with the rest. The remedies are to monitor fewer, better-chosen quantities, to use exact invariants wherever an exact rule exists, to attach alerts to named owners, and to review both fired and non-fired checks periodically.

Reconciliation

Reconciliation compares the data with an agreed reference and investigates differences. The reconciliation entry gives the definition. This section concerns design: what to compare, at what depth, and how to read the result.

The reference and its scope

The reference might be a source extract, a delivery manifest, an independently produced total such as a processor’s settlement file, or a second system running in parallel during a migration. What matters is independence and scope. A reference computed by the same pipeline from the same intermediate table shares its errors and proves only that the arithmetic was repeatable. A reference whose scope is different from the data under test, for example a settlement file that is cut at a different time zone, produces differences that are not faults. Before comparing, align the cutoff, the time zone, the currency, the treatment of refunds, and the inclusion rules.

Depth levels and what each catches

Comparison can be done at several depths, and each deeper level catches faults the shallower level cannot.

Level	Compare	Catches	Misses
1	Row counts	Gross loss or duplication	Offsetting loss and duplication; wrong values
2	Totals of an amount column	Value differences that do not cancel	Offsetting value differences; wrong keys with same totals
3	Key sets, per partition or key range	Which records are missing or extra	Records with the right key and the wrong values
4	Row-level values by key, or hashes of canonical row content	Altered fields	Errors present in the reference too
5	Independent business totals, such as settled money	Faults shared by every internal copy	Faults outside the reference’s scope

A concrete case shows why level 1 and level 2 are weak. Suppose the reference contains orders A, B, and C for 10, 20, and 30 currency units, and the target contains A, B, and D for the same amounts. Both sides have three rows and a total of 60. Counts and totals agree, yet C is missing and D is unexpected. A key-set comparison shows the difference at once. A chunked checksum reduces the cost of such a comparison on large tables by comparing summaries for bounded key ranges, then looking at rows only in ranges that differ. A matching hash is strong evidence, not a mathematical proof, and the comparison must use the same columns, the same ordering, and the same treatment of NULLs and numeric representation on both sides.

Business reconciliation

Business reconciliation compares figures that carry a financial or operational meaning, such as captured payments against settled funds, or shipped units against inventory movements. It adds two requirements. The first is that the comparison has a break taxonomy, a defined list of reasons that two figures can legitimately differ. For payments such a list might include:

• Timing differences. The processor settles on a later day than capture, so the two sides disagree until the settlement date passes.
• Missing records. A payment exists on one side only.
• Duplicates. The same payment appears twice on one side, which is the kind of effect a replay or retry can produce.
• Amount differences. The same payment has different amounts because of fees, currency conversion, or rounding.
• Definition differences. The two sides define the measure differently, for example gross against net of refunds, which a metric definition should make explicit.

The second requirement is that breaks are managed as data. A break has an age, an owner, a classification, and a resolution. A difference that is known and explained, such as a settlement delay of two days, should be recorded as an accepted timing difference with an expiry date. An open break that has no owner and no age is a quiet source of drift. The quality threshold entry makes the related point that a tolerance should be tied to a reason, such as a rounding rule, rather than set to whatever lets a check pass.

Tolerance deserves care. Rounding each of many line amounts to the display precision can leave a total that differs from the sum of unrounded amounts by up to half a unit per line in the worst case, so a tolerance that is tied to the number of lines and the rounding rule is more defensible than a flat percentage. A tolerance that is larger than the fault you want to detect makes the check unable to detect it. If the question is whether a 2 percent loss occurred, a 5 percent tolerance answers no regardless of the data.

Cutoffs, lag, and replays

Reconciliation compares two moving things. If the target is fed by a streaming path with lag, rows that are still in flight at the cutoff are not discrepancies. State the cutoff as a point in event time or in source position, and state the tolerance for in-flight data as a separate item from any numeric tolerance. A target fed by an at-least-once path can contain duplicates after a replay, and the comparison has to separate the cases: a count that is higher by 83 with 83 repeated keys is a duplication break, while a count that is lower and then recovers after the next load is a timing break. Key-level comparison distinguishes them. A count-only comparison cannot.

Detection is not correction

Reconciliation finds the missing order or the double charge. Repairing it is a separate design problem. A repair that changes figures consumers have already seen needs to be announced as a restatement rather than applied quietly, and reversing an effect that downstream systems have acted on is not the same as deleting a row. A comparison also supports a claim only about the relationship it tested. If both sides took their data from the same upstream defect, they agree and are both wrong.

Putting the layers together

No layer is enough alone, and the layers cost different amounts to run. The following hypothetical incident shows how they combine. An upstream retry bug causes 2 percent of payments received in one hour to be loaded twice, with the second copy given a newly generated identifier, and the daily volume is 100,000 payments spread evenly over 24 hours. The duplicate count is about 83 rows. This is an assumed scenario, and the layers’ results below follow from the definitions, not from a measured run.

Layer	Does it see the fault?	Reason
Unique payment_id	No	The duplicate has a new identifier
Schema and accepted values	No	Every column is valid
Daily row-count control limit	No	83 rows is about 0.08 percent of the day, far below one standard deviation
Business invariant: one capture per order and amount	Yes, if defined	Two captures with the same order, amount, and card token within minutes violate it
Reconciliation by order key against the order system	Yes	Two payment rows exist for one order
Reconciliation of captured total against settled funds	Yes, at settlement time	The processor settled one payment, the warehouse counted two

The strongest evidence here is not a statistical flag but an invariant that encodes the business rule that an order has one capture, and reconciliation against a source that was not duplicated. The statistical check is the cheapest and the least informative for this fault. That ordering is typical. Statistical checks are best at faults that are large, sudden, and not describable by rule. Invariants are best at faults you can state in advance. Reconciliation is best at faults that every internal check shares.

A sensible placement is to run structural and business invariants as gates before publication, because they are inexpensive and exact, to run statistical checks as monitors that open an investigation, and to run reconciliation on a cadence matched to the comparison’s cost and the time within which a discrepancy must be found, with its owner, cutoff, and trigger written into the pipeline’s design so that it does not depend on someone remembering to ask.

Failure modes of the checks themselves

Checks are code and data, and they fail in recognizable ways.

• Vacuous passes. A failing-rows test over an empty table or an empty partition returns nothing and passes. Add a volume or presence check, and make the empty case a state you handle on purpose.
• Checks that cannot fail. The NULL case above is one example. Another is a test whose filter excludes the problem rows. When you write an important check, give it a known-bad example and confirm that it fails. dbt documents the error_if and warn_if pattern for asserting that a test catches known-bad fixtures, in which a test passes only when it returns failing rows.
• Thresholds that drift. Under delivery pressure a failing threshold is loosened and the suite returns to green. Keep an owner and a review date on each threshold and record loosening as an accepted risk with an expiry.
• Shared errors. A check that recomputes a figure with the same logic as the pipeline proves repeatability. Use an independent source for at least the checks that guard figures that drive decisions.
• Coverage blindness. Count what is covered. If a table has 40 columns and 5 are tested, say that. The same applies to the share of critical tables with an exact invariant, a statistical monitor, and a reconciliation.
• Definition drift. When the definition of a metric changes, the invariants and reconciliations that restate the old definition begin to fail or, worse, keep passing against an out-of-date rule. Record the deployed definition version next to the check, as the semantic layer entry recommends for metric definitions, so a failing or passing check can be tied to the definition it was written for.

What to decide before you write the checks

The following decisions turn the vocabulary into an operating design. Writing them down is more important than the tool used to execute them.

1. List the faults you care about in terms of consumer harm: missing records, duplicates, wrong amounts, stale data, wrong meaning.
2. For each fault, name the check that could detect it from the three families, and name the faults no check detects. That second list is the residual risk.
3. Set the response per check: block publication, publish with a visible warning, or open an investigation. A statistical flag rarely justifies blocking alone.
4. Choose thresholds from decisions, not from the current data, and record who owns each and when it is reviewed.
5. Choose baselines that are comparable, state how many observations each uses, and measure the clean-day false-alarm rate rather than assuming it.
6. Choose the reference and the cutoff for each reconciliation, define the break taxonomy, and give every open break an owner and an age.
7. Test the checks. Keep known-bad fixtures and, where possible, inject a controlled fault into a non-production copy to confirm detection.

The next post, Metadata and Lineage for Change Impact Analysis, takes up a different question that these checks raise: when a rule, a definition, or a column changes, which downstream datasets and consumers are affected.

References

• dbt Documentation: Add data tests to your DAG (read 3 October 2026)
• dbt Documentation: severity, error_if, and warn_if (read 3 October 2026)
• NIST/SEMATECH e-Handbook of Statistical Methods: 6.3.1 What are control charts?