# Data Quality Before Adding AI: Evidence Dossier

**Research snapshot:** October 10, 2026  
**Perspective:** Academic  
**Decision supported:** Which data defects undermine AI outputs, and how should they be diagnosed before an analytical AI assistant is trusted?  
**Active-work scope:** A small professional data team with ingestion pipelines, a cloud analytical warehouse, dbt-style transformations, and an AI assistant asked to explain changes in monthly revenue or active-customer counts.  
**Evidence boundary:** Public sources and synthetic fixtures only. No private systems or customer results were accessed. Recommendations below are readiness and diagnosis controls, not authorization for autonomous production repair.

## Executive answer and uncertainties

### Bottom line

**Do not make “clean all the data” the prerequisite for AI. Make “prove the answer path” the prerequisite.**

For an analytical assistant answering questions such as *“Why did September revenue increase?”*, the most consequential defects are those that can change the metric while leaving the result superficially plausible:

| Defect class | Typical consequence for revenue / active-customer analysis | Correct first response |
|---|---|---|
| Missing or dropped events | Under-count or under-sum | Reconcile against an independent completeness signal |
| Late events | Wrong period attribution; apparent month-over-month movement | Compare event time with ingestion time and the metric's late-arrival policy |
| Replays / duplicates | Inflated sums or event counts | Check uniqueness at the declared source grain |
| Schema drift | Failed transformations or, worse, silently wrong fields/types | Validate source/model contracts and version changes |
| Transformation/grain defects | Join fanout, double counting, dropped records | Test declared grain, join cardinality, and reconciliation across stages |
| Freshness defects | Correct logic applied to stale or partially settled data | Check freshness **and** completeness; they are different |
| Semantic-definition drift | Numerically valid answer to the wrong definition of “revenue” or “active” | Pin a versioned metric definition and effective date |
| Retrieval/source-version error | Assistant retrieves obsolete metric documentation | Require source/version fidelity |
| SQL/model error | Wrong tables, filters, joins, date logic, or interpretation | Evaluate against executable known-truth cases |
| Permission defect | Correct answer disclosed to the wrong user | Enforce authorization outside the model |

The central distinction is **model capability versus system reliability**. An LLM may be capable of writing sophisticated SQL while the system still returns the wrong business answer because its source is incomplete, its metric definition is stale, its join changes grain, or its evaluation oracle is itself wrong. Spider 2.0 was explicitly designed around more enterprise-like SQL workflows with 632 problems, large schemas, multiple dialects, and supporting documentation; its original o1-preview code-agent baseline solved only 17.0% of tasks, despite much stronger results reported on older benchmarks. A subsequent expert audit found annotation errors in 52.8% of BIRD Mini-Dev examples and 62.8% of the 121 Spider 2.0-Snow examples with public gold SQL, and corrections substantially changed agent scores and rankings. That combination is a warning against treating either fluent demos or leaderboard numbers as workflow correctness. citeturn25view1turn25view0

The academic evidence also argues against the simplistic formulation **“dirty data always means clean it automatically first.”** CleanML evaluated real errors across 14 classification datasets and found that the effect of cleaning depended materially on the dataset, error type, cleaning method, and learning algorithm; cleaning could reduce downstream performance in some settings, and no single cleaning strategy dominated. citeturn1academia39turn9view1turn9view2 A broader 2024 PVLDB evaluation of 12 repair algorithms likewise found that many repair algorithms can introduce additional errors, while also concluding that appropriately selected repair can improve downstream tasks in most of the tested settings. Its downstream experiments, however, deliberately injected errors into datasets initially treated as correct, so its conclusion should not be transferred as a universal production law. citeturn22view0turn23view1turn24view2

For **live structured analytics**, the priority should therefore be:

**authoritative definition → deterministic data checks → lineage diagnosis → deterministic calculation → AI interpretation → human judgment when evidence is insufficient.**

The AI assistant should not be the component that decides whether duplicated revenue events are legitimate, reconstructs records that never arrived, invents what “active customer” means, or grants itself data access.

### The most important epistemic limit

Some defects are **not identifiable from the defective dataset alone**.

Suppose the warehouse contains 999 payment events. One possible world is that 999 payments occurred; another is that 1,000 occurred and one event was dropped upstream. If every observation available to the assistant is identical in those two worlds, no LLM, anomaly detector, or SQL agent can tell which world is true. Detecting the missing event requires additional evidence: for example a source-system control total, sequence number, CDC offset, expected partition manifest, ledger balance, or another independently generated record.

The same limitation applies to semantics. If “active customer” could legitimately mean either “customer with ≥1 paid transaction this calendar month” or “customer with ≥1 product session in the trailing 30 days,” the warehouse schema alone cannot reveal which question the user intended. That case requires an authoritative definition or clarification, not smarter prompting.

### Main uncertainty

I found substantial primary evidence on data cleaning, tabular error detection/repair, practitioner data failures, text-to-SQL capability, and benchmark reliability, but **no public controlled production study that directly measures an LLM analytical assistant across the complete defect chain in this exact architecture—capture → ingestion → warehouse → dbt transformation → semantic definition → text-to-SQL → explanation**. The recommendations below therefore combine direct empirical evidence where it transfers, official system capabilities, mathematical/data-system invariants, and explicitly labeled engineering interpretation. No production effect size is claimed.

## Evidence synthesis across the data and AI layers

The phrase “data quality for AI” hides three materially different problems: **training data**, **retrieval/context data**, and **live analytical data**. Evidence from one should not be silently transferred to another.

| Layer | What is defective | What failure looks like | What the evidence establishes | Transfer limitation |
|---|---|---|---|---|
| Model training / supervised ML | Labels, missing cells, inconsistent values, duplicates, feature noise | Changed learned model performance | Cleaning and repair effects are heterogeneous; some methods help and some hurt | Does not tell us the effect of duplicated invoice events on a SQL `SUM()` |
| Retrieval / semantic context | Stale metric docs, wrong versions, irrelevant documentation, contradictory definitions | Assistant writes plausible SQL using the wrong business meaning | Text-to-SQL audits show semantic/domain annotations can themselves be wrong or ambiguous | There is little public controlled evidence isolating warehouse semantic-document defects end-to-end |
| Live structured warehouse | Missing rows, late events, duplicates, schema changes, bad joins, stale loads | Directly wrong count, sum, period comparison, or cohort | Database constraints, reconciliation, lineage and executable tests can identify many classes deterministically | Automatic repair research does not establish safe autonomous repair of a production metric |
| Assistant/query layer | Wrong schema selection, filters, SQL, retrieved definition, explanation | Correct data converted into a wrong answer | Modern enterprise text-to-SQL remains difficult; benchmark correctness itself needs auditing | Benchmark score ≠ reliability in a specific organization's workflow |

### What data-cleaning research actually says

**CleanML** is especially useful as counterevidence to blanket cleaning claims. It studied five types of naturally occurring data errors across 14 real-world classification datasets, multiple cleaners, and seven ML algorithms. Cleaning impact varied substantially across datasets and learning algorithms; validation-selected cleaning could still degrade test performance. The authors also found that duplicate removal behaved differently from several other defect classes: in their classification setting, models could sometimes be more robust than the duplicate-cleaning intervention. citeturn1academia39turn9view1turn9view2

That finding is **not** evidence that duplicate transaction events are harmless to revenue. The objectives differ. For a revenue aggregate

\[
R=\sum_{i=1}^{n} amount_i,
\]

an unintended replay of a valid event with amount \(x\) changes the result to \(R+x\) unless downstream logic deduplicates it. A classification model, by contrast, can sometimes tolerate repeated training observations. This is exactly why effect sizes from training-data papers should not be ported to live analytical systems.

**HoloClean** provides stronger evidence that automated probabilistic repair can work on structured tabular errors, but also illustrates why “repair” is not synonymous with “truth.” Its evaluation used Hospital, Flights, Food, and Physicians datasets; reported HoloClean F1 scores were approximately 0.832, 0.763, 0.783, and 0.897 respectively in the reported experiments. For the larger Food and Physicians datasets, ground truth was not exhaustively known: thousands of cells were manually labeled, with the authors explicitly noting limitations for estimating recall. Runtime also ranged from minutes on some datasets to hours on the largest Physicians experiment. citeturn12view0turn12view1turn12view2 Those are respectable experimental results, but they do not justify silently overwriting revenue records in a production warehouse.

**HoloDetect** demonstrates that learned error detection can be quite effective under its experimental conditions: the paper reports average precision around 94% and recall around 93% across its tabular datasets. citeturn13academia28 Again, this is detection of erroneous cells under a study protocol—not detection of an upstream payment event that never arrived, nor resolution of an undocumented change in the business meaning of “active.”

The more recent **Automatic Data Repair: Are We Ready to Deploy?** is particularly instructive. The authors evaluated 12 repair methods and explicitly found that many methods could increase the number of erroneous cells by incorrectly modifying correct values. They also found semantic errors harder to repair than syntactic ones. In downstream experiments they used classification, regression, clustering, and k-nearest-neighbor workloads; because corresponding clean ground truth was unavailable for those experiments, they treated the initial datasets as correct and injected missing values, typos, Gaussian noise, and misallocated values into features and labels. They report that properly selected repair usually improved those downstream tasks, but that result is conditional on the study's injection model, algorithms, and ability to compare choices. citeturn22view0turn23view1turn23view4turn24view2

This matters operationally: **production teams usually do not know which repair algorithm is optimal for the particular hidden defect they are facing.** Selecting a repair retrospectively against known truth is easier than deciding what to change when the truth is precisely what is uncertain.

### Practitioner evidence points to propagation, not just local errors

Sambasivan et al.'s CHI study interviewed 53 AI practitioners working in high-stakes applications in India, East and West Africa, and the United States. The authors describe “data cascades”—upstream data problems that compound into downstream failures—and report them in 92% of the cases they studied. citeturn20view0 This is valuable qualitative evidence about organizational practice, but 92% should **not** be interpreted as an incidence rate for ordinary Czech or US analytical warehouses: the sample was qualitative, purposive, and focused on high-stakes AI.

Its most transferable lesson is structural: downstream model work can obscure an earlier data defect. For a revenue assistant, debugging the prose model before checking whether an invoice partition was dropped is the wrong diagnostic order.

### Text-to-SQL evidence separates capability from workflow trust

Spider 2.0 intentionally increases realism relative to older text-to-SQL benchmarks: tasks can involve more than a thousand columns, BigQuery or Snowflake environments, metadata, documentation, and project code. The large drop in the original baseline relative to earlier benchmarks demonstrates how much harder enterprise-style analytical workflows are than schema-to-query toy tasks. citeturn25view1

But benchmark numbers need their own data-quality audit. Jin et al.'s 2026 study performed an AI-assisted but human-adjudicated audit. It found annotation errors in 52.8% of 498 unique BIRD Mini-Dev examples and 62.8% of the 121 Spider 2.0-Snow examples with public gold SQL. When 16 open-source BIRD agents were re-evaluated on a corrected sampled subset, relative performance changes ranged from −7% to +31% and ranking shifts from −9 to +9 positions. Their SAR-Agent was useful but itself achieved 83% precision on BIRD Mini-Dev and 89% on Spider 2.0-Snow, which is another reason to retain expert adjudication. citeturn25view0

The implication is stronger than “benchmarks are imperfect”: **your evaluation labels, expected SQL, metric documentation, and supposed source of truth are themselves data products that need versioning and review.**

## Defect-to-diagnosis map

Before diagnosis, start from a **decision contract**, not from the model prompt.

For the concrete question *“Why did monthly revenue change?”*, a minimally usable contract should identify the metric version, authoritative source, declared source and output grains, qualifying statuses, period timestamp, timezone, currency/FX policy, treatment of refunds/credits/tax, late-event policy, effective date, and data owner. For *active customers*, substitute the customer identity rule, qualifying activity, measurement window, exclusions, handling of merged accounts, and whether “active” is event-based, transaction-based, subscription-based, or another business definition.

There is deliberately no universal definition of either KPI in this dossier. For the worked example below, “revenue” is a synthetic operational metric, not financial-statement revenue under IFRS or US GAAP.

| Defect | Observable signature | Evidence required to diagnose it | Can an AI assistant detect it? | Appropriate gate |
|---|---|---|---|---|
| **Missing source events / dropped partition** | Expected IDs, offsets, partitions, or source control totals do not reconcile | Independent source manifest, ledger/control count, CDC position, expected partition schedule | Only if given an independent expectation. It cannot infer an invisible missing record from the same incomplete table | **Abstain** from causal explanation when completeness is material and unverified |
| **Null/missing values inside present records** | Required field null rate changes or violates constraint | Column-level expectation, source contract | Yes, but deterministic SQL/test is simpler and reproducible | Block affected metric if required field is used |
| **Late events** | Large event-time ↔ ingestion-time lag; cross-period arrivals | Both timestamps plus explicit settlement/watermark policy | AI can summarize; deterministic calculation should identify the rows | Clarify whether metric is provisional or closed |
| **Replay / duplicate ingestion** | Multiple rows share a supposedly stable idempotency/event key | Declared grain and source guarantee that key is unique | Easy for SQL/AI to flag; deciding whether two similar records are truly duplicates may require source knowledge | Block a sum/count when violated at a required unique grain |
| **Schema drift** | Required column disappears; type changes; enum/domain changes | Versioned schema/contract and source changelog | Structural changes are better caught deterministically; AI can explain the impact | Fail the transformation or affected metric |
| **Semantic drift without schema drift** | Same field name/type, changed meaning; or changed metric formula | Versioned semantic spec and effective date | Only if authoritative old/new meanings are available | **Clarify** or **abstain** if definition cannot be pinned |
| **Freshness** | Last successful load exceeds SLA | Load timestamp, source freshness rule | Straightforward deterministic check | Label result stale or block according to SLA |
| **Partial but fresh load** | Recent timestamp but row/control-total mismatch | Independent completeness check | Freshness alone will miss it | Abstain despite “fresh” timestamp |
| **Transformation/grain defect** | Uniqueness breaks after a join; row count or amount unexpectedly changes | Declared grain, join cardinality expectations, stage reconciliation | AI can inspect SQL, but deterministic before/after assertions are stronger | Block interpretation |
| **Wrong period logic** | Difference between event-time and ingestion-time/month boundaries/timezones | Metric temporal definition | AI may detect when prompted, but query should be tested | Block until correct time basis is known |
| **Wrong semantic document retrieved** | Response cites metric v1 while v2 is effective | Retrieval provenance and version metadata | Yes, if provenance is observable; otherwise it may simply repeat stale content | Require correct source/version before answering |
| **Wrong SQL/model reasoning** | Result differs from executable gold/control query | Known-truth fixture, independent baseline | This is the main place to evaluate the model itself | Retry/refine; abstain after bounded failure |
| **Permission violation** | Query/result includes objects or rows inaccessible to user | Identity-aware warehouse/semantic-layer authorization | The model must not be the authority | Hard fail; no answer or diagnostic leakage |

Two distinctions in that table deserve emphasis.

**Freshness is not completeness.** A pipeline can ingest a record five minutes ago and still have lost 20% of yesterday's events. dbt's current freshness mechanisms provide a way to express and evaluate recency SLAs, while dbt data tests and unit tests support assertions over data and transformation logic. Those are useful mechanisms, but freshness by itself does not establish that all expected data arrived. citeturn4search3turn3search1turn4search1

**Schema consistency is not semantic consistency.** dbt model contracts can require expected column names and data types and fail builds when a model's shape does not match its contract. Current documentation also notes that enforcement of constraints is platform-dependent and that common warehouse constraints such as primary-key, foreign-key, or uniqueness declarations may be definable without actually being enforced. citeturn20view1 A field can therefore remain `DECIMAL(18,2)` while quietly changing from net amount to tax-inclusive gross amount. A schema contract would be satisfied; the metric would still be wrong.

For an **active-customer** metric, defect effects can differ from revenue. A duplicated activity event may have no effect on `COUNT(DISTINCT customer_id)` when one event is sufficient for activity, but the same replay can incorrectly activate a customer under a definition such as “at least three qualifying events.” Defect priority therefore has to be evaluated against the **actual metric function**, not against a generic taxonomy of “bad data.”

## Minimum sufficient control stack

The goal is not global data perfection. The goal is a **bounded, observable chain of evidence for the particular answer being generated**.

For this scoped workflow, I would require the following controls before allowing an AI assistant to provide a confident causal explanation.

| Control | Minimum evidence | Why it precedes AI interpretation |
|---|---|---|
| **Metric contract** | Metric name/version, formula, grain, inclusion rules, time basis, effective date, owner | Otherwise “correct SQL” can implement the wrong question |
| **Source completeness / settlement signal** | Control total, sequence/offset continuity, partition manifest, source reconciliation, or an explicit statement that completeness is unverified | Missing rows cannot be recovered from inference alone |
| **Freshness** | Data-as-of timestamp and SLA status | Prevents explaining old data as current |
| **Grain and required-key tests** | Uniqueness at declared grain; non-null required IDs/timestamps/amounts | Catches replay and many transform defects cheaply |
| **Schema/version check** | Expected required fields/types and source/model version | Prevents unnoticed structural drift |
| **Transformation reconciliation** | Row/count/sum invariants at important boundaries; join-fanout tests; unit tests for edge cases | Separates upstream defects from transformation defects |
| **Lineage/provenance** | Actual upstream objects, transform/job version, relevant columns, code/version metadata | Narrows the search space and makes the result reproducible |
| **Authorization** | Query executes under effective user/service permissions; sensitive rows/columns protected outside the LLM | Correctness is insufficient if data is disclosed improperly |
| **Query/result provenance** | Executed SQL or equivalent code, metric version, data snapshot/as-of, source objects, checks that passed/failed | Makes an AI explanation auditable rather than merely persuasive |

dbt's current documentation provides building blocks for several of these controls: data tests for assertions, unit tests for testing SQL transformation logic against static inputs, freshness configuration, and model contracts. These are **provider capabilities, not empirical evidence that a dbt project is correct**; the team still has to choose the right assertions and maintain them. citeturn3search1turn4search1turn4search3turn20view1

Lineage has a different role. OpenLineage's current object model represents jobs, runs, input/output datasets and facets including schema, versions, quality metrics, SQL/source-code locations, and related metadata; its column-lineage facet can represent direct and indirect dependencies and transformations such as aggregation and joins. citeturn25view2turn25view3 This lets a diagnostic process ask: *Did the discrepancy first appear in raw ingestion, staging, or the revenue mart? Which source columns feed this metric? Which job/code version produced it?*

But **lineage is a debugger, not an oracle**. A lineage graph can tell you that `monthly_revenue.revenue_usd` depends on `invoice_events.amount` through a particular aggregation. It cannot tell you whether an absent invoice should have existed.

A practical diagnosis therefore runs upstream until it finds the **last verified-good boundary**:

`assistant explanation ← query result ← semantic metric ← mart ← staging ← raw ingestion ← source control`

If the raw source reconciles to the authoritative system but the mart does not, the likely defect is downstream of raw. If raw already disagrees with the source control total, inspecting the LLM's SQL first is wasted effort.

### Behavior gates

The assistant should have four distinct outcomes, not a binary “answer/error” state:

**Answer** when definition, permissions, data readiness, deterministic calculation, and source/version provenance are all satisfactory.

**Clarify** when more than one plausible metric interpretation remains—for example “active customer” has two current definitions and the user did not disambiguate.

**Abstain** when correctness cannot be supported—for example a critical partition is incomplete, the authoritative source cannot be reconciled, the relevant metric definition is unavailable, or requested data are unauthorized.

**Human review** when evidence establishes a defect but not the correct repair—for example two records share a near-duplicate fingerprint but no stable source event ID proves which one is authoritative, or a business-policy change determines whether late revenue should restate a closed month.

For this scope, autonomous remediation should remain outside the assistant's authority.

## Worked synthetic example and portable test asset

**Worked example — synthetic, not a customer result.**

Assume the organization's metric contract says:

> **Monthly operational revenue** = sum of `amount_usd_cents / 100` for `status='paid'`, one record per unique `event_id`, grouped by the calendar month of **event time**. Customer tags are irrelevant to the calculation.

Further assume the independently reconciled truth is:

- August 2026 revenue = **$120,000**
- September 2026 revenue = **$126,000**
- True month-over-month change:

\[
\Delta R = \$126{,}000-\$120{,}000=\$6{,}000
\]

\[
MoM = \frac{126{,}000-120{,}000}{120{,}000}\times100 = 5.0\%
\]

The flawed pipeline contains three defects:

1. A **$2,400 August event arrives on September 2** and is grouped by ingestion month.
2. A **$7,200 September event is replayed** with the same `event_id`.
3. A **$9,000 event belongs to a customer with two tags**, and a many-to-many tag join is performed before revenue is summed.

The apparent result becomes:

| Diagnostic state | August | September | Apparent Δ | Apparent MoM |
|---|---:|---:|---:|---:|
| Flawed query: ingestion month + replay + fanout | $117,600 | $144,600 | $27,000 | **22.96%** |
| Correct event-time attribution | $120,000 | $142,200 | $22,200 | 18.50% |
| Plus deduplication at `event_id` grain | $120,000 | $135,000 | $15,000 | 12.50% |
| Plus correct aggregation grain / no irrelevant tag fanout | $120,000 | $126,000 | $6,000 | **5.00%** |

A fluent assistant operating directly on the flawed mart could produce an elaborate explanation for a **22.96% increase that did not happen**. Better language generation does not repair the causal chain.

Notice that the late event changes the month-over-month delta by $4,800, not $2,400: correcting its temporal allocation raises August by $2,400 **and** lowers September by $2,400.

### Portable SQLite fixture

I executed the following self-contained fixture in an in-memory SQLite database during this research. Its assertions passed. It requires only Python's standard library and does not access a network or external data.

```python
import sqlite3

db = sqlite3.connect(":memory:")

db.executescript("""
CREATE TABLE events (
  row_id INTEGER PRIMARY KEY,
  event_id TEXT NOT NULL,
  customer_id TEXT NOT NULL,
  event_ts TEXT NOT NULL,
  ingested_ts TEXT NOT NULL,
  amount_usd_cents INTEGER NOT NULL,
  status TEXT NOT NULL,
  schema_version TEXT NOT NULL
);

CREATE TABLE customer_tags (
  customer_id TEXT NOT NULL,
  tag TEXT NOT NULL
);

-- August true revenue:
-- 40,000 + 35,000 + 42,600 + 2,400 = 120,000 USD.
--
-- A4 is an August event that arrives in September.
INSERT INTO events VALUES
(1,'A1','c1','2026-08-05','2026-08-05',4000000,'paid','v1'),
(2,'A2','c2','2026-08-12','2026-08-12',3500000,'paid','v1'),
(3,'A3','c4','2026-08-20','2026-08-20',4260000,'paid','v1'),
(4,'A4','c5','2026-08-31','2026-09-02', 240000,'paid','v1'),

-- September true revenue:
-- 60,000 + 49,800 + 9,000 + 7,200 = 126,000 USD.
--
-- row 9 is an ingestion replay of event S4.
INSERT INTO events VALUES
(5,'S1','c1','2026-09-05','2026-09-05',6000000,'paid','v1'),
(6,'S2','c2','2026-09-10','2026-09-10',4980000,'paid','v1'),
(7,'S3','c3','2026-09-15','2026-09-15', 900000,'paid','v1'),
(8,'S4','c4','2026-09-22','2026-09-22', 720000,'paid','v1'),
(9,'S4','c4','2026-09-22','2026-09-22', 720000,'paid','v1');

-- c3 has two tags. Joining tags before aggregation will duplicate S3.
INSERT INTO customer_tags VALUES
('c1','standard'),
('c2','standard'),
('c3','enterprise'),
('c3','beta'),
('c4','standard'),
('c5','standard');
""")

# Flawed query:
# - groups by ingestion month rather than event month;
# - does not deduplicate replays;
# - joins a many-to-many dimension before summing.
flawed = db.execute("""
SELECT
    substr(e.ingested_ts, 1, 7) AS month,
    SUM(e.amount_usd_cents * 1.0 / 100) AS revenue_usd
FROM events e
JOIN customer_tags t USING (customer_id)
WHERE e.status = 'paid'
GROUP BY 1
ORDER BY 1
""").fetchall()

# Correct query for this synthetic metric contract:
# - retain one row per event_id;
# - use event time;
# - do not join an irrelevant many-to-many dimension.
correct = db.execute("""
WITH dedup AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY event_id
            ORDER BY row_id
        ) AS rn
    FROM events
)
SELECT
    substr(event_ts, 1, 7) AS month,
    SUM(amount_usd_cents * 1.0 / 100) AS revenue_usd
FROM dedup
WHERE rn = 1
  AND status = 'paid'
GROUP BY 1
ORDER BY 1
""").fetchall()

# Deterministic duplicate/replay diagnostic.
duplicates = db.execute("""
SELECT
    event_id,
    COUNT(*) AS row_count,
    SUM(amount_usd_cents) / 100.0 AS duplicated_rows_usd
FROM events
GROUP BY event_id
HAVING COUNT(*) > 1
""").fetchall()

# Deterministic cross-month late-arrival diagnostic.
late_arrivals = db.execute("""
SELECT
    event_id,
    event_ts,
    ingested_ts,
    amount_usd_cents / 100.0 AS amount_usd
FROM events
WHERE substr(event_ts, 1, 7) <> substr(ingested_ts, 1, 7)
""").fetchall()

# Deterministic join-fanout diagnostic:
# compare the same deduplicated September population before and after join.
join_fanout = db.execute("""
WITH dedup AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY event_id
            ORDER BY row_id
        ) AS rn
    FROM events
),
sep AS (
    SELECT *
    FROM dedup
    WHERE rn = 1
      AND status = 'paid'
      AND substr(event_ts, 1, 7) = '2026-09'
)
SELECT
    (
        SELECT SUM(amount_usd_cents) / 100.0
        FROM sep
    ) AS pre_join_usd,
    (
        SELECT SUM(s.amount_usd_cents) / 100.0
        FROM sep s
        JOIN customer_tags t USING (customer_id)
    ) AS post_join_usd
""").fetchone()

print("flawed:", flawed)
print("correct:", correct)
print("duplicates:", duplicates)
print("cross-month late arrivals:", late_arrivals)
print("join fanout pre/post:", join_fanout)

# Known-truth assertions.
assert flawed == [
    ('2026-08', 117600.0),
    ('2026-09', 144600.0)
]

assert correct == [
    ('2026-08', 120000.0),
    ('2026-09', 126000.0)
]

assert duplicates[0][0:2] == ('S4', 2)
assert late_arrivals[0][0] == 'A4'
assert join_fanout == (126000.0, 135000.0)

print("All synthetic assertions passed.")
```

The diagnostic pattern is more important than this particular SQL:

**first localize the defect deterministically, then let the assistant interpret the verified evidence.**

A model could use the resulting diagnostics to say:

> “The initially reported 22.96% increase is not supported. After event-time attribution, replay deduplication, and removal of a join fanout, September revenue is $126,000 versus $120,000 in August, a 5.0% increase. The largest detected distortions were a $9,000 join fanout and a $7,200 replay.”

That is a materially different role from asking the model to inspect a dirty table and guess what went wrong.

## Reproducible task-level evaluation before deployment

No public benchmark should substitute for an evaluation against the actual analytical behavior the team wants.

The following is a proposed protocol; **it has not been executed against a production AI system**.

### Evaluation corpus

Create a small, version-controlled warehouse fixture with known truth for both monthly revenue and active customers. Preserve a clean base version and inject defects individually and in selected combinations.

| Test case | Controlled condition | Expected system behavior |
|---|---|---|
| Clean baseline | No defects | Return exact known metric and correct explanation |
| Duplicate replay | Same authoritative event ID ingested twice | Detect grain violation; do not explain inflated result |
| Missing partition | One expected source partition absent | Abstain unless an independent source proves completeness another way |
| Late event | Prior-period event arrives this period | Apply documented temporal policy; label provisional/restated status |
| Schema drift | Required field renamed/type changed | Block before interpretation |
| Join fanout | One-to-many dimension joined before aggregation | Localize transformation defect |
| Dropped rows in transformation | Raw reconciles, mart does not | Localize defect downstream of raw |
| Metric definition v1 → v2 | Warehouse unchanged; business definition changes | Attribute change to definition where appropriate; cite effective version |
| Stale retrieval | Assistant index contains v1 while v2 is effective | Reject stale source; use v2 or abstain |
| Ambiguous “active customer” | Two legitimate definitions available | Ask for clarification rather than choose silently |
| Unauthorized user | Correct metric exists but role lacks access | Produce no unauthorized data |
| Wrong model-generated SQL | Query executes but computes a different metric | Fail result-level correctness despite syntactically valid SQL |
| Combined replay + fanout + late data | Interacting defects | Diagnose all material contributors or conservatively abstain |
| Recovery case | Defect is corrected and data/version advances | Stop using stale failure state; reproduce corrected result |

Evaluation should score **results**, not exact SQL strings. Different SQL programs can be semantically equivalent, while identical-looking aggregate values can occur accidentally on a particular fixture.

The 2026 benchmark-audit evidence is directly relevant here: an evaluation is only as trustworthy as its gold answers and domain semantics. Jin et al. found that corrections to supposedly authoritative text-to-SQL annotations materially changed system rankings. For semantic cases, the gold should therefore be reviewed by a knowledgeable analyst/data owner; executable cases should additionally contain machine-checkable assertions. citeturn25view0

### Required metrics

I would report dimensions separately rather than hide them in a composite “AI quality score”:

| Dimension | Measurement |
|---|---|
| **Numeric/query correctness** | Exact result for integer counts/currency-cents fixtures; explicitly defined tolerance only where the metric itself is approximate |
| **Defect localization** | Correctly identifies capture vs ingestion vs transformation vs semantics vs query/model layer |
| **Source fidelity** | Uses the intended authoritative dataset/document |
| **Version fidelity** | Uses the semantic definition and transformation/source version effective for the requested period |
| **Ambiguity handling** | Correct clarification/abstention rate, plus false-clarification/false-abstention rate |
| **Permission compliance** | Number of unauthorized disclosures or queries on negative access tests |
| **Reproducibility** | Same data snapshot + semantic version yields the same deterministic numeric result |
| **Failure recovery** | Correct behavior after the underlying defect or version is changed |
| **Latency** | End-to-end P50/P95 for the real task |
| **Cost** | Warehouse execution cost plus model/tool consumption, using the team's actual billing units |

For a release test set, **unauthorized disclosures should be zero**. That is a proposed safety criterion, not an empirically derived statistical threshold.

For ordinary deterministic revenue/count fixtures, exact arithmetic is preferable to a fuzzy semantic score. Tolerances should appear only where the business metric itself requires them—for example approximate distinct counting, FX rounding, or another documented approximation.

### Compare AI with a non-AI baseline

The primary baseline should be a **certified fixed SQL query/dashboard for the same metric**. A second useful baseline is a **human analyst with the same data, documentation, and permissions**.

The comparison should ask:

\[
\text{Does AI improve coverage or time-to-diagnosis without reducing correctness, provenance, or permission compliance?}
\]

That is a better deployment question than “Can the LLM answer the question?”

For a narrow KPI that can already be answered by one reliable query, an AI layer may add little besides prose. Its strongest potential leverage in this scoped workflow is instead likely to be **navigating the diagnostic evidence**—lineage, failed tests, semantic definitions, historical changes, and query results—after deterministic controls have established what can safely be interpreted.

The AI should likewise not perform arithmetic that SQL or code can perform exactly. A sensible separation is:

| System component | Primary responsibility |
|---|---|
| Retrieval | Find the right metric specification, lineage, test result, and relevant metadata |
| SQL/code | Compute numbers and deterministic diagnostics |
| Data tests/contracts | Establish invariants and readiness gates |
| Lineage | Identify dependency path and likely defect boundary |
| LLM | Translate intent, plan investigation, synthesize verified evidence, explain uncertainty |
| Human | Resolve ambiguous business truth, approve consequential repair, adjudicate disputed semantics |

## Contradictions, limitations, and context

### Apparent contradictions in the literature

**“Cleaning can hurt” versus “repair is always worthwhile.”** CleanML found that cleaning could lower classification performance and that the optimal intervention depended on the dataset/model/error combination. citeturn1academia39turn9view1 Ni et al. conclude that repair is worthwhile for downstream tasks when suitable methods are chosen, while simultaneously finding that many repair algorithms introduce additional errors. Their downstream evaluation relies substantially on controlled error injection into datasets treated as initially correct. citeturn22view0turn23view1turn24view2

These findings are not directly irreconcilable. They use different datasets, repair methods, task objectives, and error-generation conditions. More importantly, “choose the best repair using known experimental truth” is not equivalent to “safely decide which production cells to alter when ground truth is unknown.”

**Duplicate robustness in ML versus duplicate correctness in analytics.** CleanML's classification findings include conditions in which duplicate cleaning did not improve—and could hurt—model performance. citeturn9view2 That result should not be used to argue against idempotency for financial/event aggregates. An unwanted duplicate contributes directly to a `SUM()` or non-distinct `COUNT()` by construction.

**Strong error-detection performance versus autonomous repair.** HoloDetect and HoloClean show that probabilistic methods can identify or repair many structured data errors under benchmark conditions, but neither demonstrates that a model can establish the authoritative business truth for an undocumented revenue semantic change or an event absent from all observed data. citeturn13academia28turn12view1

**Enterprise-style benchmarks versus trustworthy leaderboards.** Spider 2.0 is useful evidence that realistic analytical SQL is hard. citeturn25view1 The 2026 annotation audit is useful counterevidence against treating its or BIRD's gold labels as infallible: semantic, database-understanding, domain-knowledge and ambiguity defects were all observed. citeturn25view0 Both facts can be true simultaneously.

### Where the proposed controls can fail or require adaptation

A uniqueness check is only meaningful when the declared grain really is unique. Repeated records can be legitimate business events; blind deduplication is dangerous when no authoritative idempotency key exists.

Exact reconciliation is inappropriate for inherently approximate measures such as approximate distinct counts. In that case the metric contract must specify algorithm and acceptable error behavior rather than pretending the number is exact.

Streaming systems may intentionally expose provisional data before a watermark or settlement period. “Incomplete” then need not mean “broken”; the answer should identify its provisional status and the expected settlement rule.

Accounting periods can be reopened or restated. A metric therefore sometimes requires an **as-of/version dimension**, not a single supposedly timeless monthly value.

A semantic change can be intentional. The correct diagnosis may be “the metric definition changed,” not “the data is dirty.”

Privacy controls can restrict the diagnostic evidence visible to an assistant. A system may need to expose aggregate failure metadata without exposing the customer-level rows that triggered it.

And for ML training, causal inference, forecasting, computer vision, or other tasks outside this active scope, the live-SQL controls in this dossier are insufficient. CleanML and the repair literature demonstrate that interactions between data interventions and learned models require task-specific empirical evaluation. citeturn1academia39turn22view0

### Czech and US relevance

The technical diagnosis is essentially jurisdiction-independent: a replayed event or fanout join changes the same arithmetic in Prague as in New York.

The Czech/EU context adds a particularly explicit legal overlay when the assistant processes identifiable customer data. GDPR Article 5 requires, among other principles, data minimization, accuracy, and integrity/confidentiality; Article 32 requires risk-appropriate technical and organizational security measures and regular testing/evaluation of those measures. citeturn21view0turn21view1 That reinforces—not replaces—the technical recommendation that authorization and data access be enforced outside model prompting.

For US deployments, applicable privacy/security obligations vary by domain, jurisdiction, contract, and data category; this dossier does not assert a single US legal regime. NIST's Generative AI Profile, released July 26, 2024, remains useful official risk-management guidance but is not empirical evidence that any specific analytical assistant is correct. citeturn17search2

### Important unresolved evidence gaps

The public literature still leaves several high-value questions poorly answered:

There is little controlled evidence measuring how individual warehouse defects—replayed CDC events, late-arrival distributions, fanout joins, stale metric documents—propagate through **modern agentic analytical assistants** and affect final business explanations.

There is no defensible universal “data quality score” threshold above which an organization becomes AI-ready. The required evidence depends on the metric and decision.

There is limited empirical work combining **data quality + semantic versioning + lineage + permissions + text-to-SQL + natural-language explanation** in one evaluated system.

Most automatic repair studies focus on erroneous observed values. Real operational pipelines also fail by **omission**, where the relevant record is absent entirely; those cases need external completeness evidence.

Research benchmarks frequently use injected corruption because exact clean truth is otherwise difficult to obtain. Injection allows controlled experimentation but can poorly approximate correlated, process-driven production defects. Ni et al.'s downstream design is an explicit example of this trade-off. citeturn23view1turn24view2

### Inaccessible material

The primary ACM metadata page for **Raha: A Configuration-Free Error Detection System** was accessible, but an author-final-manuscript PDF endpoint surfaced through MIT DSpace returned HTTP 405 in this research session. I therefore did not use detailed Raha result numbers that I could not inspect directly. citeturn14search0turn14search2

A direct CIDR-hosted PDF for the 2026 text-to-SQL annotation-error work could not be rendered through the research tool, but the authors' arXiv v3 HTML was accessible in full and is the version used for the reported design and results. citeturn25view0

## Claim-to-source dossier and publication angles

All sources below were inspected on **2026-10-10**. “Provider documentation” indicates product/specification behavior, not independent empirical validation.

| Claim / use in this dossier | Original source and date | Exact scope / evidence type | Important limitation | Direct URL / DOI |
|---|---|---|---|---|
| Cleaning effects on ML are heterogeneous and can be negative | **CleanML: A Study for Evaluating the Impact of Data Cleaning on ML Classification Tasks**, Li et al.; ICDE 2021 / arXiv v3 Apr. 5, 2021. citeturn1academia39turn9view1 | Primary empirical study; 14 real-world classification datasets with naturally occurring errors, multiple cleaners and ML algorithms | Classification training data, not live SQL aggregates | https://arxiv.org/abs/1904.09483 ; https://doi.org/10.1109/ICDE51399.2021.00009 |
| Probabilistic structured-data repair can achieve substantial but imperfect precision/recall and incur material runtime | **HoloClean: Holistic Data Repairs with Probabilistic Inference**, Rekatsinas et al., PVLDB 2017. citeturn12view0turn12view1turn12view2 | Primary empirical database-repair study across four real datasets | Partial/manual truth for larger datasets; not production autonomous remediation | https://doi.org/10.14778/3137628.3137631 |
| Learned tabular error detection can attain high benchmark precision/recall | **HoloDetect: Few-Shot Learning for Error Detection**, Heidari et al., 2019. citeturn13academia28 | Primary empirical error-detection study; reported average precision ≈94%, recall ≈93% across evaluated datasets | Cell-error detection does not establish missing-event completeness or business semantics | https://arxiv.org/abs/1904.02285 |
| Configuration-free error detection is an active line of database research | **Raha: A Configuration-Free Error Detection System**, 2019. citeturn14search0 | Primary research metadata/abstract | Full author PDF was inaccessible in this session; no detailed result claim used | https://doi.org/10.1145/3299869.3324956 |
| Automatic repair can itself introduce errors; semantic errors are difficult; downstream benefit depends on method/task | **Automatic Data Repair: Are We Ready to Deploy?**, Ni et al., PVLDB 17(10), 2024; inspected arXiv v2 dated Mar. 31, 2025. citeturn22view0turn23view4turn24view2 | Primary comparative empirical study; 12 repair algorithms, multiple datasets/error rates/types and four downstream-task classes | Part of downstream evaluation injects designed corruption into data treated as initially correct; not a warehouse-AI study | https://arxiv.org/abs/2310.00711 ; https://doi.org/10.14778/3675034.3675051 |
| Upstream data problems can propagate into downstream AI work in practice | **“Everyone wants to do the model work, not the data work”: Data Cascades in High-Stakes AI**, Sambasivan et al., CHI 2021. citeturn20view0 | Primary qualitative study; interviews with 53 practitioners across several countries; authors report 92% prevalence of data cascades in studied cases | Qualitative/high-stakes sample; 92% is not a general industry incidence estimate | https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/ |
| Enterprise text-to-SQL is substantially more difficult than older benchmark settings | **Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows**, Lei et al., Nov. 12, 2024. citeturn25view1 | Primary benchmark paper; 632 enterprise-oriented workflow problems involving large schemas and warehouse dialects | Benchmark performance does not equal target-workflow correctness | https://arxiv.org/abs/2411.07763 |
| Text-to-SQL gold annotations can themselves be heavily defective and distort rankings | **Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards**, Jin et al., 2026, arXiv v3. citeturn25view0 | Primary empirical audit: 498 unique BIRD Mini-Dev cases, 121 public-gold Spider 2.0-Snow cases; human SQL-expert adjudication; 16 agents re-evaluated | Audit covers selected benchmark subsets, not every benchmark/task | https://arxiv.org/abs/2601.08778 ; https://arxiv.org/html/2601.08778v3 |
| dbt supports data assertions | **dbt Developer Hub — data tests**, current page inspected Oct. 10, 2026; page surfaced with Mar. 13, 2026 update metadata. citeturn3search1 | Provider documentation | Mechanism availability, not evidence that a project's tests are sufficient | https://docs.getdbt.com/reference/resource-properties/data-tests |
| dbt provides freshness configuration for recency expectations | **dbt Developer Hub — freshness**, current page inspected Oct. 10, 2026; search metadata Sep. 16, 2026. citeturn4search3 | Provider documentation | Freshness does not prove completeness | https://docs.getdbt.com/reference/resource-properties/freshness |
| dbt unit tests can validate SQL model logic against small static inputs | **dbt Developer Hub — unit tests**, current page inspected Oct. 10, 2026; search metadata Sep. 14, 2026. citeturn4search1 | Provider documentation | Does not validate upstream source truth unless fixture/expectations encode it | https://docs.getdbt.com/docs/build/unit-tests |
| dbt model contracts can enforce expected model shape, with platform-dependent constraint enforcement | **dbt Developer Hub — Model contracts**, v2 docs inspected Oct. 10, 2026. citeturn20view1 | Provider documentation; names/types/contracts and constraint-support matrix | Some warehouse PK/FK/unique constraints may be metadata rather than enforced integrity constraints | https://docs.getdbt.com/docs/mesh/govern/model-contracts |
| OpenLineage can encode job/dataset/run provenance, versions/quality metadata and column dependencies | **OpenLineage Object Model and Column Level Lineage Facet**, version 1.53.0 docs inspected Oct. 10, 2026. citeturn25view2turn25view3 | Open specification/provider documentation | Instrumentation shows dependencies/metadata; it does not prove values are correct | https://openlineage.io/docs/spec/object-model/ ; https://openlineage.io/docs/spec/facets/dataset-facets/column_lineage_facet/ |
| EU personal-data processing is subject to minimization, accuracy, confidentiality/integrity and risk-appropriate security obligations | **Regulation (EU) 2016/679 (GDPR)**, adopted Apr. 27, 2016; OJ publication May 4, 2016; in force at inspection. citeturn21view0turn21view1 | Binding EU law, Articles 5 and 32 | Applies when processing falls within GDPR's scope; not a technical AI-quality benchmark | https://eur-lex.europa.eu/eli/reg/2016/679/oj |
| NIST maintains a GenAI-specific risk-management profile | **NIST AI 600-1, Generative Artificial Intelligence Profile**, released Jul. 26, 2024. citeturn17search2 | Official US government risk-management guidance | Guidance, not an empirical validation study or workflow correctness certificate | https://www.nist.gov/itl/ai-risk-management-framework |

**Evidence assessment:** the strongest directly transferable evidence is not a numerical effect size such as “duplicates reduce AI accuracy by X%.” No credible universal number of that kind emerged. The stronger conclusion is structural: different defect classes have different observability and effects, automatic correction has false-repair risk, complex analytical models remain fallible, and even evaluation truth can be defective. Those findings support task-level deterministic gates rather than a generic cleanliness score. citeturn1academia39turn22view0turn25view0

**Candidate distinct article angles**

| Angle | Core thesis |
|---|---|
| **Your AI does not need perfect data. It needs a provable answer path.** | Replace vague “AI readiness” scoring with metric-specific evidence gates |
| **The data defects an AI analyst will confidently explain instead of noticing** | Missing events, late data, replays, fanout, stale semantics, and wrong retrieval can all yield fluent but false narratives |
| **Lineage is a debugger, not a truth machine** | Lineage narrows where to investigate but cannot reveal records or business truth that were never observed |
| **Test AI on broken data, not clean demos** | A serious evaluation deliberately injects operational defects and measures clarification, abstention, permissions and recovery |
| **Data cleaning can hurt ML—and that does not mean duplicate revenue is OK** | Separates training-data research from live structured analytics and prevents invalid transfer of empirical findings |
| **Fresh is not complete** | A recent timestamp can coexist with missing data; AI readiness needs independent completeness signals |
| **The benchmark can be wrong too** | Text-to-SQL annotation audits show why internal “golden” questions need executable truth and expert semantic review |

**Useful reusable asset:** the complete SQLite fixture above can serve as a seed for a CI evaluation suite. It already exercises event-time versus ingestion-time semantics, replay detection, declared grain, join fanout, exact expected results, and machine-checkable assertions. No separate download is required.

**Freshness triggers for this dossier:** re-research is warranted when the deployed assistant/model or text-to-SQL architecture changes materially; when a new corrected Spider/BIRD/enterprise benchmark revision appears; when dbt's testing, contracts, freshness, or semantic-layer behavior changes; when the OpenLineage specification version changes materially; when the organization's own metric definition, source schema, ingestion architecture, or permission model changes; or when relevant Czech/EU or US legal obligations change. The most important trigger is internal rather than academic: **every semantic-definition or data-contract change should invalidate any assumption that yesterday's evaluation still proves today's answer path.**