Silent Data Quality Failures: 6 Reasons Reports Don't Match

Month end. Finance says revenue is off by three percent against the ledger. Engineering opens the orchestrator and every job is green, every run finished inside its window, and the row counts match what they matched yesterday. Nobody in that meeting is wrong. They are both looking at real evidence.

That gap has a name. Silent data quality failures are the defects that pass every check you currently run and still change the answer. The pipeline reported success because it did exactly what you asked. You never asked it whether the number was right.

warning What a silent data quality failure actually is

Most pipeline monitoring watches the process. Did the job start, did it finish, did it raise an exception, did it land inside its SLA. All worth having. None of it inspects a single value.

A silent data quality failure is any defect that satisfies every one of those process checks and still produces a wrong figure. The job succeeds. The dashboard refreshes on schedule. The total is incorrect and nothing anywhere turns red.

So the discovery path is always the same, and always late. Someone in finance notices a figure looks odd, and an engineer spends two days reconstructing what a pipeline did three weeks ago. By then the number has been in a board pack.

AWS is direct about where the check belongs. Its documentation for AWS Glue Data Quality describes the ETL entry point as proactive, there to help you "identify and filter out bad data before you load a data set into your data lake". Before. Not when somebody asks.

schedule Late-arriving data, and the cut-off that lies

Your pipeline runs at 02

and reads everything stamped with yesterday's transaction date. A payment processor batches its settlements and delivers thirty hours later. Every night the job is right about what it could see, and wrong about the day.

Counts look healthy, because you counted what arrived rather than what happened. It surfaces only when someone queries the same period a fortnight later and gets a different total.

Detection and handling are two different jobs, and teams usually skip the first:

• Detect it. Record the row count and key aggregate for each closed period, then compare on every later run. If a "closed" day keeps growing, your cut-off is fiction.
• Handle it. Reprocess by event date rather than load date, and make that reprocess safe to run repeatedly. Which leads to the next failure.

Both belong in code, not in tribal knowledge. AWS's DQDL reference supports freshness assertions like ColumnValues "load_date" > (now() - 3 days), plus dynamic rules that compare a run against its own history: RowCount > avg(last(5)) tests the current count against the average of the previous five runs.

content_copy Duplicate rows, and the rerun that doubles a day

Most ingestion is at-least-once by design. Queues redeliver on failure, change-data-capture streams replay from a checkpoint, and a retried task re-reads the file it already read. Duplicates are therefore not an edge case. They are the default outcome of any retry.

A non-idempotent load turns an ordinary retry into a data defect. A job dies eighty percent of the way through appending yesterday's sales, someone reruns it, and the day now holds 1.8 times the revenue. Source-to-target counts for that file still agree, because the file really did load.

The fix is structural, not procedural. A runbook saying "check before you rerun" is not a fix, it is a hope.

• Replace blind appends with a partition overwrite or a MERGE on a business key, so running twice leaves the same table as running once.
• Give every row a deterministic key you can assert uniqueness on.
• Prove it the only way that proves anything: run the job twice on identical input and diff the outputs.

The assertion side is well served by tooling. dbt ships four generic data tests out of the box (unique, not_null, accepted_values and relationships), and its documentation puts the mechanic plainly: tests "are select statements that seek to grab 'failing' records, ones that disprove your assertion. If you assert that a column is unique in a model, the test query selects for duplicates". Glue's equivalents are IsUnique and IsPrimaryKey.

Where ingestion sits behind a queue, the delivery guarantee is part of your data model and not just your infrastructure. I went through those mechanics in scaling data pipelines with RabbitMQ.

public Timezones and boundaries: the off-by-one-day that moves money

This is the one finance finds and engineering does not, because it moves revenue between months rather than destroying it.

A source system writes local time. The warehouse column is declared timestamp without time zone. The PostgreSQL date/time documentation is unambiguous about what happens next: "In a value that has been determined to be timestamp without time zone, PostgreSQL will silently ignore any time zone indication." Silently. The offset is discarded, not converted.

Store the same event in a timezone-aware column and you get the opposite guarantee: "all timezone-aware dates and times are stored internally in UTC", converted on input and rendered back in the session's zone on output.

Now add a month boundary. A transaction at 23

on 31 March in New York is 03 on 1 April in UTC and 04 in London. One instant, three renderings, and depending on which the report groups by it lands in March or April. Across a quarter of late-evening transactions that is a real number, and it reconciles perfectly against itself every time you check.

Four boundary rules worth writing into the model:

1. Store UTC in a timezone-aware type. Always.
2. Convert to the reporting zone exactly once, in the semantic layer or the mart, never ad hoc inside each dashboard.
3. Make period bounds explicit. >= start AND < end beats BETWEEN for timestamps, which quietly includes the closing instant.
4. Reconcile a whole month rather than a day. Boundary errors cancel out inside a period and only show at its edges.

schema Schema drift: the column that quietly became a string

An upstream team ships a release. amount changes from a decimal to a string with thousands separators. A new value appears in a status enum. A column is renamed and the old name returns null.

Nothing throws. A schema-on-read job casts what it can, nulls what it cannot, and carries on. Your sum now misses every row where the cast failed. Row count unchanged, total wrong, pipeline green.

Of the six, this one most rewards a cheap test, because the behaviour is fully deterministic. Assert the contract at the ingest boundary:

• Presence and type. SchemaMatch, ColumnDataType and ColumnCount in DQDL; not_null and accepted_values in dbt.
• Null rate against history, not against zero. A column normally two percent null that is suddenly forty percent null is your signal. A rule demanding zero nulls gets switched off within a month.
• Accepted values on every low-cardinality column feeding a filter or a group-by. A new enum value dropping out of a WHERE clause is the same bug in a different hat.

Generating the expected DDL from a known-good sample first makes this faster; the JSON to SQL schema tool does that in the browser.

call_split Fan-out joins that double-count revenue

The most under-diagnosed failure on the list. You join a fact table to a dimension expecting one matching row, and for some keys there are two: overlapping validity periods in a slowly-changing dimension, a duplicated reference record, a mapping table that gained a row.

Every fact row that matches twice is counted twice. Revenue inflates by a percentage that depends on which keys are affected, so it reads as noise rather than as a bug. And it propagates. Every downstream aggregate inherits the inflation, which is how one bad reference row becomes six dashboards that disagree.

Three assertions close it:

1. Grain. After the join, assert exactly one row per intended key. IsPrimaryKey "order_id" in DQDL, or dbt's unique on the grain column.
2. Row-count stability. A lookup join must not change the fact row count. If it does, it is not a lookup.
3. Aggregate match. Compare the transformed total back to the source total. DQDL exposes AggregateMatch as a rule type; a sum() comparison in SQL does the same job for nothing.

Assertion three catches the other five failure modes as a side effect, which makes it the highest-value single test on this page.

fact_check The data quality check that would have caught each one

Failure mode	What it looks like	The check	When it runs
Late-arriving data	A closed period changes value weeks later	Stored count and total per closed partition, compared each run	Post-load, daily
Duplicates from retries	Totals inflate on days a job was rerun	Uniqueness on the business key (IsUnique, dbt unique)	Post-load, blocking
Non-idempotent rerun	Running twice differs from running once	Run twice on identical input, diff the output	CI, before merge
Timezone or boundary	Revenue drifts between adjacent months	Full-period aggregate against source, in one declared zone	Month-end close
Schema drift	A column silently nulls out	Type and presence assertions, plus null rate against history	Pre-load, blocking
Fan-out join	Totals inflate for no visible reason	Grain uniqueness after the join, plus AggregateMatch	Post-transform, blocking

Blocking matters more than the check. A test that writes a warning into a channel nobody reads is a slower way of having no test. Decide per check whether a failure quarantines the batch, stops the pipeline, or just records a number, and write that decision where the next engineer will find it.

savings What the reconciliation gap actually costs

The wrong number is rarely the expensive part. What follows it is. Two days of forensic work. A second set of figures kept in a spreadsheet because nobody trusts the first. A meeting that stops being about the business and becomes a meeting about the data.

That was the state of a Cloudera platform I built out for a telecom operator's finance function. The estate ran on un-optimised workflows and scripts, and the inconsistencies underneath undermined trust in the numbers the business ran on. What moved the needle was not new dashboards. It was ten orchestrated workflows and roughly fifty production scripts made consistent, with the data inconsistencies resolved. Reporting errors fell by 50% and operational efficiency rose 30%. Full case study here.

Reporting straight off a live operational database is the other pattern worth naming, which is how a university BI stack ended up slow, insecure and prone to integrity issues at once. Purpose-built marts give you somewhere to put the checks. A live master database gives you nowhere.

checklist Where to start if none of this exists

You do not need a data quality platform to stop the bleeding. You need one number you can defend. In rough order:

1. Pick the number that matters. Usually monthly revenue, or a volume count leadership quotes out loud. Reconcile it end to end by hand once, noting every discrepancy on the way.
2. Automate that reconciliation. Turn the manual comparison into a scheduled aggregate check between source and mart. That is assertion three above, and it is worth more than the next five items combined.
3. Add grain uniqueness to the two or three marts everyone actually queries.
4. Assert the schema at ingest, with null rates measured against history.
5. Make one job idempotent and prove it by running it twice.
6. Only then adopt a framework. AWS Glue Data Quality ships 25 or more rule types out of the box and allows up to 2,000 rules in one ruleset, so tooling capacity is not your limit. Deciding what "correct" means is.

Steps one and two inside a week is realistic for most teams, and that is when month end stops being an investigation.

If you are still building the pipeline these checks hang off, the Python ETL walkthrough covers the structure and Dagster versus Airflow versus Prefect covers where retries and backfills are made safe. Reporting accuracy gets bought at sector level more often than people admit, which is why it leads in banking and financial services work.

quiz Frequently asked questions

helpCan a data pipeline succeed and still produce wrong data?expand_more

Yes, and it is the normal case rather than the exception. Job status reflects whether the code executed, not whether the output is correct. A pipeline that appends duplicate rows, nulls a mis-cast column or misses late-arriving records finishes cleanly and reports success every time.

helpWhy do two reports show different numbers for the same metric?expand_more

Usually one of four causes: they read at different points in the refresh cycle, they apply different period boundaries or timezones, they define the metric differently (returns excluded in one, included in the other), or one sits downstream of a join that duplicates rows.

helpWhat is the fastest data quality check to add first?expand_more

An aggregate comparison between source and target for the metric leadership actually quotes. One scheduled query sums the same measure on both sides and alerts when the difference exceeds a set tolerance. It catches duplicates, fan-out joins, dropped rows and mis-cast columns in one test, which no schema-level rule does.

helpHow do I make an ETL job idempotent?expand_more

Replace append-only writes with a partition overwrite or a MERGE keyed on a business identifier, so a second run replaces rather than adds. Avoid non-deterministic values such as now() inside the transformation. Then verify it: run the job twice on identical input and diff the outputs.

helpAre row counts enough to validate a data load?expand_more

No. Row counts confirm that the same number of records arrived, not that they hold the same values. A fan-out join and a set of dropped rows can offset each other, and a mis-cast column changes no counts at all. Pair counts with a sum on the measures that matter.

flag The uncomfortable summary

Every failure on this page is boring, well understood and cheap to test for. None need a platform, a vendor or a rewrite. They persist because the tests that catch them are worth nothing on the day you write them and everything on the day something breaks, which makes them easy to defer forever.

What works is small. One defended number, one automated comparison, and a written decision about what a failing check is allowed to stop. Build that and month end changes from "which figure do we believe" to "what is this figure telling us".

If the numbers coming out of your platform cost more to defend than they cost to produce, that is the kind of work I do. Here is how I package it, or tell me where the reconciliation breaks and I will tell you which of the six it probably is.