Data quality for an analytical AI assistant

Data Quality Before Adding AI: Evidence Dossier

Research snapshot: October 10, 2026
Perspective: General
Decision supported: Which data defects undermine AI outputs, and how should they be diagnosed?
Operating scope: Small professional data team; ingestion pipelines; cloud analytical warehouse; dbt-style transformations; AI analytical assistant; monthly revenue and active-customer analysis.
Evidence boundary: Public sources and synthetic fixtures only. No private systems or production remediation were accessed or performed.

Concise answer and uncertainties

The strongest conclusion is not “clean all your data before adding AI.” That is too broad, too expensive, and often impossible. The more defensible requirement is:

Before an AI analytical assistant explains a business change, the decision-critical path must be testable: the metric definition, grain, source completeness, identity/uniqueness, freshness, schema, lineage, permissions, and executed query must be observable enough to falsify a wrong answer.

That conclusion follows from several different evidence streams. Production data-validation work shows that violations of explicit data assumptions can cause failures or wrong predictions, and tools such as Deequ and dbt operationalize deterministic checks on missingness, uniqueness, accepted domains and relationships. 1 Research on data cascades documents how upstream data problems can propagate downstream, although that study concerned high-stakes AI rather than BI assistants and should not be treated as a measured effect size for analytics systems. 2 Enterprise-oriented text-to-SQL evaluations also show a large gap between fluent SQL generation and reliable end-to-end analytical work: Spider 2.0’s 632 tasks require metadata, documentation, codebases and complex schemas, and its reported results were far below those on older, simpler benchmarks. 3 More recent work further shows that benchmark labels themselves can be defective, making leaderboard accuracy an unsafe proxy for deployment correctness. 4

For a question such as “Why did September revenue change versus August?”, an assistant should not begin by interpreting the percentage change. It should first establish at least:

Required evidence	Why it comes before interpretation
Authoritative metric definition and version	“Revenue” can mean bookings, cash captured, invoiced revenue, recognized revenue, net revenue, or another measure. A syntactically correct query against the wrong definition is still wrong. Semantic models explicitly represent metric-related entities, time dimensions, dimensions and aggregations. 5
Declared grain and identity key	A one-payment-event table cannot safely be treated as one-order or one-customer data. unique, not_null and relationship tests provide deterministic checks once the intended key is known. 6
Completeness/reconciliation evidence	A fresh warehouse can still be incomplete; the newest record may have arrived while other expected records did not. Streaming research explicitly treats completeness, latency and correctness as separate tensions. 7
Freshness and relevant time semantics	dbt distinguishes configurable freshness based on loaded timestamps, but that check establishes recency, not full completeness. 8
Schema/contract compatibility	A changed or missing column, type or constraint can invalidate downstream assumptions. Enforced dbt model contracts can reject models whose output shape differs from the declared contract, subject to platform limitations. 9
Lineage and transformation version	Lineage identifies the jobs and datasets through which an answer was produced and therefore reduces the search space for diagnosis; it does not prove which upstream component is wrong. 10
Effective permissions	Authorization needs to be enforced by the data system rather than entrusted to a prompt. BigQuery, for example, supports authorized views plus row- and column-level restrictions. 11 A September 2026 text-to-SQL study found substantial access-control-specific failures that standard unrestricted benchmarks did not expose. 12
Executed SQL/code and result	Model intent, generated query, deterministic calculation and prose explanation are separate failure surfaces. Enterprise text-to-SQL research shows schema linking, dialect handling and multi-step reasoning remain material sources of error. 3

What the evidence does and does not establish

The evidence does establish that deterministic assumptions and contracts are useful control mechanisms; late and out-of-order data create genuine correctness-versus-latency trade-offs; semantic ambiguity causes text-to-SQL errors; access controls create an additional failure dimension; and benchmark scores can materially misrepresent real-world reliability. 1

The evidence does not establish a universal “data quality score” above which AI becomes safe, a universal defect-rate threshold, or that every organization needs a full observability platform before experimenting with AI. No reviewed source justifies such a threshold. The required controls depend on the decision’s materiality and on which failures can change the answer.

The most important uncertainty is epistemic rather than technical: many defects cannot be discovered from the analytical table itself. If an upstream event was never captured, or if two departments legitimately use different definitions of “active customer,” even an excellent model cannot reconstruct authoritative truth from the downstream rows alone. An anomaly detector or LLM may raise a useful hypothesis, but independent evidence is required to establish the defect.

For the Czech versus US context, the mechanics of missing events, duplicate ingestion, fan-out joins, freshness and semantic drift do not materially differ. The material contextual difference is the applicable authorization and governance policy. The technical recommendation is therefore jurisdiction-neutral: encode the permitted data boundary in the warehouse or controlled data interface and test the assistant inside that boundary, rather than expecting the language model to infer policy. Warehouse-native authorization mechanisms and the recent RBAC benchmark both support treating permission compliance as a separate systems property from query correctness. 11

Evidence synthesis around the decision

Begin with the analytical decision, not with “AI readiness”

Consider two concrete decisions:

Revenue decision: “September revenue is down/up versus August. Is this a real business change, and what explains it?”

A minimally adequate specification must settle the revenue measure, currency and unit, qualifying transaction states, treatment of refunds/chargebacks, period boundary and timezone, event time versus posting time, and the grain of the fact table. A semantic layer can formalize entities, time dimensions, grouping dimensions and aggregations, but merely having metadata does not prove that the chosen definition is economically correct. 5

Customer decision: “Why did monthly active customers change?”

Here the definition is at least as important as row correctness. “Active” might mean a qualifying payment, login, order, subscription state, or another event. The customer identity may be account-, user-, organization- or household-level. Distinct-count metrics are also affected differently by duplicates than additive measures: replaying the same customer’s payment can inflate revenue while leaving COUNT(DISTINCT customer_id) unchanged.

That distinction matters because “data quality” is not one scalar property. Defects interact with the metric’s grain and aggregation.

Defect-to-answer evidence map

Defect	Typical mechanism producing a wrong answer	Revenue effect	Active-customer effect	Strongest diagnostic evidence	What AI can responsibly do
Upstream capture failure	A real event never reaches the authoritative captured event stream	Usually understatement if the missing event is positive; direction depends on event type	Can remove a customer entirely if that was their only qualifying event	Independent source/control total, application transaction log or source manifest	Raise suspicion from discontinuity; cannot prove a missing event from downstream absence alone
Late/missing ingestion	Event exists upstream but has not reached the analytical snapshot by the reporting cutoff	Temporary under/overstatement depending on event semantics	Can temporarily omit otherwise-active customers	Source-to-warehouse key/count/sum reconciliation plus event-time/ingest-time analysis	Summarize reconciliation evidence and identify likely affected partitions
Duplicate/replayed ingestion	Same logical event is loaded more than once	Additive metrics can be inflated directly	Often no effect on COUNT(DISTINCT customer_id) if the replay has the same customer	Declared stable event key + uniqueness test; reconciliation against upstream event IDs	Generate duplicate queries or group suspicious rows; deterministic uniqueness should decide the condition
Schema drift	Column disappears, changes name/type/meaning, or a new state appears	Can silently null, cast, exclude or reinterpret data depending on transformation	Same	Schema diff/model contract, accepted-value checks and failed builds/tests	Explain contract difference; should not silently “repair” semantic changes
Transformation/grain defect	Wrong join cardinality, filtering or aggregation changes row multiplicity	Fan-out can multiply monetary facts	Can inflate counts unless deduplicated at the intended customer grain	Declared grain, key tests, relationship tests, pre/post-join row/count invariants, SQL review	Can inspect SQL and propose fan-out hypotheses, but needs declared expected grain
Temporal freshness defect	Result is computed on data whose most recent load is older than the decision expects	Snapshot may omit recent business	Same	Loaded-at freshness check; pipeline state	Report data_as_of and refuse “current” language when SLA fails
Completeness defect despite fresh data	Recent records exist but a subset of expected events is missing	Can be wrong despite passing a max-timestamp freshness test	Same	Reconciliation/control totals, expected partition/key coverage	An LLM cannot infer completeness from MAX(loaded_at)
Semantic-definition change	Formula, status inclusion, identity rule or period definition changed between periods	Produces an artificial discontinuity even when source events are correct	Particularly dangerous for “active” definitions	Versioned metric/semantic definition and change history	Detect definition/version mismatch if supplied; otherwise may confidently explain an artefact
Model retrieval/schema-selection error	Assistant selects wrong model, table, column or documentation	Arbitrary	Arbitrary	Captured retrieval trace; query comparison with approved semantic layer or baseline	This is an AI-system defect, not a source-data defect
SQL/code error	Wrong filter, join, date boundary or aggregation	Arbitrary	Arbitrary	Executed SQL, result comparison, unit/golden tests	Self-correction can help but cannot be treated as proof
Interpretation error	SQL number is right but causal story is unsupported	Numerical result remains right; explanation wrong	Same	Evidence-linked claims; independent business context; human review	Useful for hypothesis generation, not for inventing causality
Permission error	Assistant accesses data the caller may not use, or refuses allowed data unnecessarily	Potential confidentiality/policy failure even with correct number	Same	Execution under effective role; authorization test suite	Must not be the primary enforcement layer

dbt’s built-in tests map cleanly onto several deterministic cases: not_null checks missing values, unique checks duplicate key values, accepted_values checks declared domains, and relationships checks referential integrity. 6 But these tests only evaluate assumptions somebody encoded. They do not independently discover that payment_id rather than order_id is the correct grain, that “captured” rather than “settled” is the approved revenue state, or that an upstream system omitted an entire transaction.

Likewise, dbt model contracts provide useful structural guarantees: with enforcement enabled, the build performs a preflight check against declared column names and data types, and the documentation explicitly distinguishes contracts, which define output shape, from data tests, which validate model contents after build. It also warns that constraints vary in actual enforcement across warehouses and that governance added too early can increase maintenance. 9 That is important counterevidence to the idea that “more contracts everywhere” is automatically better.

Freshness is not completeness

This distinction deserves unusual emphasis because it is easy to hide behind a green dashboard.

dbt freshness checks are based on the age of the most recent loaded timestamp or equivalent metadata and can produce configured warning/error states. 8 Suppose 99 of 100 September payments disappear but payment number 100 arrives five seconds ago. A MAX(loaded_at) freshness test can be completely healthy while the dataset is catastrophically incomplete.

This is not a quirk of dbt. The Dataflow research frames unbounded, out-of-order processing around an unavoidable trade-off between correctness, latency and cost, noting that practitioners cannot in general know that an unbounded input has become permanently complete. 7

Therefore, for a bounded monthly finance question:

\text{Freshness} \neq \text{Completeness}

A useful readiness gate needs both a recency assertion and whatever completeness evidence the workflow can realistically obtain: source counts, event IDs, financial control totals, expected partitions, file manifests, batch acknowledgements, or another independently maintained control.

Lineage reduces the diagnostic search space; it does not establish truth

OpenLineage v1.53.0 represents pipeline observations as jobs consuming or producing datasets and supports runtime as well as design lineage events. 10 For a wrong monthly_revenue value, that graph can turn a vague incident—

“Revenue looks strange.”

into a bounded investigation—

monthly_revenue ← fct_payments ← stg_payments ← source ingestion job; the affected transformation changed in a particular run.

That is operationally valuable. But a dependency graph only says where data flowed. It does not by itself tell you whether September’s increase is real, whether the source itself is wrong, or whether the metric definition is appropriate. That remains a comparison/inference problem.

Semantic consistency is a different control layer from schema validity

Schema contracts can show that amount_cents INTEGER still exists. They cannot tell you that one producer started supplying euros where the downstream model assumes US dollars, or that the business changed the definition of active customer.

dbt’s current semantic-model specification requires metric-oriented metadata such as a time dimension and entities/join keys and supports dimensions and simple aggregations. 5 That makes a semantic layer a natural place to pin definitions used by an analytical assistant. Yet this is again a declared contract, not empirical validation of the business meaning.

A useful system should therefore distinguish at least four states:

structurally valid → data-valid under known invariants → semantically specified → business-authoritative.

Those are not synonyms.

Diagnostic architecture and decision matrix

Separate the assistant into independently testable stages

The safest architecture for this workflow is not “give the model the warehouse and ask why revenue changed.” It is a chain in which deterministic components produce evidence before open-ended interpretation.

Stage	Primary responsibility	Reliability test
Intent and definition resolution	Human + governed semantic metadata	Did “revenue,” “active” and the comparison period resolve to one authorized definition? If multiple valid interpretations remain, clarify.
Retrieval / schema selection	Retrieval layer / model	Did it select the approved metric/model and current documentation version?
SQL/code generation	Model or prebuilt query	Does generated SQL match approved baseline behavior for tested cases?
Execution	Warehouse	Was the exact SQL executed successfully under the caller’s effective permissions?
Deterministic calculation	Warehouse/code	Can subtotals, deltas and percentages be reproduced independently?
Data-quality gate	Tests + reconciliation + metadata	Were grain, uniqueness, completeness, freshness, contract and relevant controls satisfied?
Interpretation	Model	Are explanations restricted to observed evidence and clearly separated from hypotheses?
Business judgment	Human	Is there enough evidence to call a cause rather than merely report a correlation?

This decomposition matters because text-to-SQL correctness remains a separate problem from upstream data correctness. Spider 2.0 uses databases with large schemas and tasks that often require metadata, documentation, codebase inspection and multiple SQL operations; its v2 paper reported a 21.3% success rate for its o1-preview-based code-agent framework and dramatically lower execution accuracy for conventional methods on its harder lite/snow variants than on older benchmarks. 3 These are benchmark results, not an estimate of production failure probability, but they are strong evidence against treating fluent SQL as a solved systems problem.

Ambiguity is another distinct layer. AmbiSQL identifies cases such as a term mapping to several columns, an unspecified operation, or underspecified temporal context, and its design explicitly asks users targeted clarification questions rather than silently choosing an interpretation. 13 A separate SIGMOD study on Reliable Text-to-SQL similarly builds abstention and human interaction around uncertain schema linking, reporting near-perfect schema-linking accuracy in its BIRD experimental setting when human involvement is invoked as needed. 14 That is evidence for selective assistance and abstention, not evidence that every closed-model analytics assistant can reproduce those results.

Minimal sufficient controls

For the scoped small team, I would prioritize the following controls before investing in broad “AI data readiness” programs.

First, pin the decision contract. For every metric the assistant is allowed to explain, store the definition, grain, time semantics, unit, allowed dimensions, owner and version. A semantic model is one implementation path. 5

Second, protect identity and grain. Decision-critical fact tables should have deterministic assertions for the intended event/entity key, required fields, relationships and business domains. dbt provides the generic primitives, and custom tests can express arithmetic or multi-column invariants. 6

Third, reconcile the critical path instead of trying to validate every field. For revenue, that often means event count, distinct event ID count and monetary control total between an authoritative upstream boundary and the warehouse, segmented enough to locate failures. This is a recommendation derived from the defect mechanics rather than a claim that one reconciliation design works universally.

Fourth, separate freshness from completeness. Record both an ingestion timestamp and the business/event timestamp when available; define the cutoff at which a period is considered decision-ready; and do not convert a healthy freshness result into a completeness claim. 8

Fifth, fail loudly on structural change. Apply contracts/versioning around stable downstream interfaces rather than every experimental model. dbt itself cautions that governance features can increase maintenance when applied prematurely. 9

Sixth, retain query and lineage provenance. The answer should be reconstructable from the exact SQL/code, dataset versions or snapshots where available, semantic definition, execution timestamp and lineage path. OpenLineage provides a versioned model for jobs, datasets and runtime/design lineage that can support this evidence trail. 10

Seventh, enforce authorization outside the LLM. An assistant should query an authorized surface or execute under a controlled identity whose permissions are enforced by the warehouse. BigQuery authorized views can expose only a subset of source data while keeping the source dataset inaccessible to the principal, and row- and column-level controls are alternative/complementary mechanisms. 11

This last item is not theoretical. A September 2026 RBAC-aware text-to-SQL benchmark explicitly separated SQL utility from access-control compliance and found access-control-specific degradation and violations across evaluated systems. For example, in its Spider setting, some specialized/open-weight models showed violation rates around 34–50%, while several stronger commercial/reasoning systems were markedly lower; the exact numbers are benchmark-specific and should not be generalized to enterprise production. 12

When each diagnostic layer is appropriate

Layer	Strong at	Weak at / blind spot	False-positive or misuse risk	Recommended place
Deterministic data tests	Known invariants: nulls, uniqueness, domains, referential integrity, explicit arithmetic	Unknown defects and incorrect assumptions	A test can pass while the assumption itself is wrong	Mandatory on decision-critical invariants
Source→warehouse reconciliation	Missing/replayed records and monetary/count discrepancies	Source itself may be wrong; heterogeneous systems can have legitimate timing differences	Alerting on expected lateness or legitimate corrections	Mandatory where an authoritative comparison/control exists
Schema/model contracts	Structural drift and stable consumer interfaces	Business semantics and row correctness	Over-governance can slow rapidly changing models; platform enforcement differs	Use on stable downstream interfaces
Freshness checks	Detecting stale loads	Partial/incomplete-but-recent datasets	Treating recency as completeness	Mandatory, but never sufficient alone
Statistical monitoring	Surfacing previously unspecified shifts and prioritizing investigation	Cannot determine whether a shift is an error or real business event	Launches, seasonality, pricing changes and campaigns can all be legitimate anomalies	Secondary detector, not truth gate
Lineage	Finding affected upstream/downstream jobs, tables and transformations	Does not determine which node is factually wrong	Mistaking dependency for causality	Mandatory enough to reproduce important answers
Human investigation	Definition disputes, business context, source-authority conflicts	Slow and expensive	Inconsistent judgment without recorded evidence	Escalation path for material ambiguity
AI-assisted triage	Searching metadata, proposing hypotheses, drafting investigative SQL, summarizing evidence	Can replicate wrong definitions/data and hallucinate causal stories	Persuasive narrative can outrun evidence	After deterministic evidence, never as sole arbiter

AI versus a simple analyst baseline

The appropriate baseline is intentionally boring:

1. an approved metric definition;
2. a fixed or analyst-reviewed SQL query;
3. deterministic source/warehouse checks;
4. an analyst reading the resulting breakdown.

An AI assistant should be evaluated against that same task, not against whether users like its prose.

A practical evaluation record for each test case should capture:

Dimension	Observable measure
Factual/query correctness	Final metric and material breakdowns agree with the approved analyst/baseline result or an explicitly justified alternative
Source fidelity	Correct approved source/model selected; no undocumented substitution
Version fidelity	Metric definition, transformation/query version and data_as_of are recorded
Ambiguity handling	Ambiguous prompts trigger clarification or abstention rather than arbitrary resolution
Permission compliance	No answer exposes rows/columns/aggregates unavailable under the tested role
Reproducibility	Executed SQL/code and relevant inputs can reproduce the numerical result
Failure recovery	Failed data gate produces a diagnostic state, not a normal business explanation
Latency	End-to-end and warehouse-query latency measured under representative workloads
Cost	Warehouse execution plus model/tool cost recorded per evaluated workflow

There is no evidence-backed universal pass percentage or latency ceiling for this use case, so those should be chosen from business requirements rather than invented as “industry standards.”

Explicit gates for clarification, abstention and human review

Clarify before querying when multiple materially different metric definitions, period boundaries, customer identities or source mappings fit the user’s wording. AmbiSQL provides direct research evidence that schema, value, operation, source and temporal ambiguities are real text-to-SQL failure modes. 13

Abstain from a business explanation when a decision-critical uniqueness, reconciliation, schema, freshness/completeness or permission gate fails; when the metric/version cannot be established; or when the assistant cannot reproduce the result from recorded query evidence.

An appropriate response is not “Revenue fell because enterprise demand weakened.” It is:

“The current warehouse snapshot cannot support a business-cause explanation. September is missing 3.2% of expected payment events relative to the authoritative source control. The observed revenue change is therefore not decision-ready.”

That example wording is hypothetical; the percentage is illustrative, not a reported empirical result.

Escalate to human review when two sources disagree about authority, a semantic-definition change may be intentional, a transformation repair could alter historical reporting, permission policy is unclear, or the only remaining explanation is causal rather than a direct data observation.

Repair should be supported by stronger evidence than anomaly alone. A duplicate is repairable only when the logical key’s uniqueness is authoritative; a “missing” record is established only when an authoritative upstream source says it should exist downstream; and a schema change should be treated as defective only relative to an approved contract/version. The active scope here does not justify autonomous production remediation.

Worked synthetic revenue example

Scenario and assumptions

This example is deliberately small so every number is inspectable.

The synthetic business definition is:

\text{Monthly Revenue}
=
\frac{\sum \text{amount\_cents for captured payment events}}{100}

The assumed fact grain is one row per logical payment event, with event_id as an authoritative unique key.

Monthly active customers are:

\text{Active Customers}
=
\operatorname{COUNT(DISTINCT customer\_id)}

over the same qualifying captured events.

The source is assumed authoritative only for this fixture. In a real company that assumption must itself be established.

August contains:

\$100 + \$80 + \$20 = \$200

September’s true source events are:

\$120 + \$90 + \$30 = \$240

so the true month-over-month revenue movement is:

\frac{240-200}{200}=0.20=+20\%

But the warehouse snapshot has two simultaneous ingestion defects:

• the $30 event S3 is delayed/missing;
• the $90 event S2 was replayed twice.

Therefore warehouse raw September revenue is:

\$120+\$90+\$90=\$300

and a naïve assistant reports:

\frac{300-200}{200}=+50\%

It could then produce a beautifully written explanation of a 50% increase that never happened.

I executed the following portable fixture with SQLite during this research. The code below is the complete fixture and the diagnostic queries; no production system was involved.

-- Synthetic fixture: revenue change, delayed ingestion and replayed ingestion.
-- Compatible with SQLite versions supporting window functions.

DROP TABLE IF EXISTS source_payments;
DROP TABLE IF EXISTS warehouse_raw_payments;

CREATE TABLE source_payments (
    event_id       TEXT PRIMARY KEY,
    customer_id    TEXT NOT NULL,
    event_time     TEXT NOT NULL,
    amount_cents   INTEGER NOT NULL,
    status         TEXT NOT NULL
);

-- Authoritative source in this synthetic example.
INSERT INTO source_payments VALUES
('A1','C1','2026-08-05T10:00:00Z',10000,'captured'),
('A2','C2','2026-08-15T12:00:00Z', 8000,'captured'),
('A3','C3','2026-08-25T09:00:00Z', 2000,'captured'),

('S1','C1','2026-09-05T10:00:00Z',12000,'captured'),
('S2','C2','2026-09-15T12:00:00Z', 9000,'captured'),
('S3','C4','2026-09-29T23:30:00Z', 3000,'captured');

CREATE TABLE warehouse_raw_payments (
    ingest_row_id  INTEGER PRIMARY KEY,
    event_id       TEXT NOT NULL,
    customer_id    TEXT NOT NULL,
    event_time     TEXT NOT NULL,
    ingested_at    TEXT NOT NULL,
    amount_cents   INTEGER NOT NULL,
    status         TEXT NOT NULL
);

-- August is correctly ingested.
INSERT INTO warehouse_raw_payments VALUES
(1,'A1','C1','2026-08-05T10:00:00Z','2026-08-05T10:01:00Z',10000,'captured'),
(2,'A2','C2','2026-08-15T12:00:00Z','2026-08-15T12:01:00Z', 8000,'captured'),
(3,'A3','C3','2026-08-25T09:00:00Z','2026-08-25T09:01:00Z', 2000,'captured');

-- September defect:
-- S3 ($30) is missing/delayed.
-- S2 ($90) was replayed and ingested twice.
INSERT INTO warehouse_raw_payments VALUES
(4,'S1','C1','2026-09-05T10:00:00Z','2026-09-05T10:01:00Z',12000,'captured'),
(5,'S2','C2','2026-09-15T12:00:00Z','2026-09-15T12:01:00Z', 9000,'captured'),
(6,'S2','C2','2026-09-15T12:00:00Z','2026-09-15T12:02:00Z', 9000,'captured');

-- A. Authoritative monthly revenue.
SELECT
    substr(event_time, 1, 7) AS month,
    SUM(amount_cents) / 100.0 AS revenue
FROM source_payments
WHERE status = 'captured'
GROUP BY 1
ORDER BY 1;

-- Expected:
-- 2026-08 | 200.0
-- 2026-09 | 240.0

-- B. Naive warehouse revenue plus row/key evidence.
SELECT
    substr(event_time, 1, 7) AS month,
    SUM(amount_cents) / 100.0 AS revenue,
    COUNT(*) AS rows,
    COUNT(DISTINCT event_id) AS unique_events
FROM warehouse_raw_payments
WHERE status = 'captured'
GROUP BY 1
ORDER BY 1;

-- Expected:
-- 2026-08 | 200.0 | 3 | 3
-- 2026-09 | 300.0 | 3 | 2

-- C. Duplicate/replay detection.
SELECT
    event_id,
    COUNT(*) AS n,
    SUM(amount_cents) / 100.0 AS raw_amount
FROM warehouse_raw_payments
GROUP BY event_id
HAVING COUNT(*) > 1;

-- Expected:
-- S2 | 2 | 180.0

-- D. Revenue after deterministic deduplication by the
-- assumed authoritative event_id.
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY event_id
            ORDER BY ingested_at
        ) AS rn
    FROM warehouse_raw_payments
    WHERE status = 'captured'
)
SELECT
    substr(event_time, 1, 7) AS month,
    SUM(amount_cents) / 100.0 AS revenue
FROM ranked
WHERE rn = 1
GROUP BY 1
ORDER BY 1;

-- Expected:
-- 2026-08 | 200.0
-- 2026-09 | 210.0
--
-- Important: deduplication fixes the replay but still leaves
-- S3 missing, so $210 is still not the true $240 result.

-- E. Source-to-warehouse reconciliation for missing September events.
SELECT
    s.event_id,
    s.amount_cents / 100.0 AS amount
FROM source_payments AS s
LEFT JOIN warehouse_raw_payments AS w
    ON s.event_id = w.event_id
WHERE substr(s.event_time, 1, 7) = '2026-09'
  AND w.event_id IS NULL;

-- Expected:
-- S3 | 30.0

-- F. Metric-specific impact: monthly active customers.
SELECT
    'source' AS dataset,
    substr(event_time, 1, 7) AS month,
    COUNT(DISTINCT customer_id) AS active_customers
FROM source_payments
WHERE status = 'captured'
GROUP BY 2

UNION ALL

SELECT
    'warehouse_raw',
    substr(event_time, 1, 7),
    COUNT(DISTINCT customer_id)
FROM warehouse_raw_payments
WHERE status = 'captured'
GROUP BY 2

ORDER BY month, dataset;

-- Expected:
-- source        | 2026-08 | 3
-- warehouse_raw | 2026-08 | 3
-- source        | 2026-09 | 3
-- warehouse_raw | 2026-09 | 2

Observed results

View of September	Revenue	Change vs August $200	Active customers	Interpretation
Authoritative synthetic source	$240	+20%	3	True state under fixture assumptions
Defective warehouse raw	$300	+50%	2	Replay inflates revenue while missing C4 depresses customer count
Warehouse after dedup only	$210	+5%	2	Replay fixed; late event still missing
After reconciliation identifies S3 and ingestion is repaired	$240	+20%	3	Matches the synthetic authoritative source

This is why generic “data quality is bad” language is not precise enough. The same two defects create opposite-looking stories in two metrics:

• revenue appears to surge 50% instead of the true 20%;
• active customers appear to fall from 3 to 2, or −33.3%, even though the true count is unchanged at 3.

The duplicate row affects additive revenue but not the distinct-customer count. The delayed S3 event affects both revenue and the active-customer count because C4 has no other September event.

How the assistant should diagnose it

The wrong workflow is:

Query warehouse → see +50% → ask model to explain growth → receive plausible narrative.

The stronger workflow is:

Query warehouse → detect COUNT(*) != COUNT(DISTINCT event_id) → run source reconciliation → identify one missing ID and one replay → withhold business explanation → repair/reload through normal engineering process → recompute → only then investigate the remaining +20% real change.

Deterministic uniqueness tests directly support the replay check, and source freshness testing separately provides the recency signal. 6 The example also illustrates why those checks are complementary: a uniqueness test alone gets September from $300 to $210, which is still wrong.

What remains unknowable

Remove source_payments from the fixture and the epistemic situation changes sharply.

From warehouse_raw_payments alone, an investigator can show that S2 occurs twice only if event_id is known to be a unique logical-event identifier. Without that semantic contract, two rows might conceivably represent legitimate separate events.

More importantly, no SQL over the warehouse can discover the nonexistent downstream row S3 unless some independent evidence implies that it should exist. A historical anomaly might suggest incompleteness, but it cannot prove it.

And even after all technical defects are repaired, the data only proves:

\text{September revenue}=\$240

and:

\Delta=\$40=+20\%

It does not prove why the genuine $40 increase occurred. Attribution to pricing, acquisition, mix, seasonality or customer behavior requires additional dimensions, experiments or business evidence. Calling one of those causes from a simple month-to-month correlation would exceed the evidence.

Contradictions, counterevidence and unresolved gaps

“Bad data causes bad AI” is directionally useful but analytically incomplete

The Data Cascades study interviewed 53 practitioners across India, East and West African countries and the United States and reported data cascades as pervasive in its high-stakes-AI sample. 2 It is valuable evidence that upstream data work matters organizationally, but it is not a controlled study of warehouse analytics assistants, and its reported 92% prevalence must not be transposed into a claim that 92% of BI-agent failures come from data quality.

In the scoped workflow, an assistant can fail with perfect source data through wrong schema retrieval, SQL generation, date interpretation or narrative reasoning. Spider 2.0’s error analysis identifies schema linking, SQL dialects, multi-step planning and use of external documentation/code context as substantial model-side challenges. 3

Conversely, a deterministic dashboard can fail on the same duplicated or incomplete data. AI is not the cause of those errors; it is another consumer that may make the error more accessible and the explanation more persuasive.

AI can improve diagnosis; “never use AI for data quality” is also too strong

There is credible evidence for selective AI assistance. AmbiSQL uses an LLM-driven process to surface ambiguous phrases and ask for user clarification. 13 Reliable Text-to-SQL explicitly combines uncertainty detection, abstention and human interaction and reports strong schema-linking results in its benchmark setting. 14

That supports using an assistant to:

• search lineage and metadata;
• formulate candidate reconciliation queries;
• inspect suspicious join cardinalities;
• summarize failed tests;
• rank hypotheses for a human investigator.

It does not support replacing the underlying deterministic test with “ask the LLM whether the data looks right.”

Benchmark numbers are weaker evidence than they appear

A particularly important 2026 counterexample comes from Jin, Choi, Zhu and Kang. Through expert analysis they reported annotation-error rates of 52.8% in BIRD Mini-Dev and 62.8% in Spider 2.0-Snow. Re-evaluating 16 open-source agents on a corrected BIRD subset produced relative performance changes from −7% to +31% and leaderboard rank changes from −9 to +9 positions. Rankings on the uncorrected subset correlated strongly with the full uncorrected Dev set (r_s = 0.85, p = 3.26×10^-5) but weakly with the corrected subset (r_s = 0.32, p = 0.23). 4

This does not imply that text-to-SQL benchmarks are useless. It implies that benchmark labels are another data product whose quality must be inspected. For this decision, the practical implication is strong: do not convert a leaderboard score into an assumed target-workflow reliability level.

Access control exposes another capability/reliability gap

The 2026 RBAC benchmark was created precisely because conventional text-to-SQL evaluation generally assumes unrestricted database access. Its authors augment existing datasets with roles and access policies, use human-in-the-loop quality control, and report that many evaluated systems degrade or violate access constraints once roles are applied. 12

This is relevant evidence, but it has limits. The roles are benchmark artifacts rather than a longitudinal study of real corporate IAM policies, so its violation rates should not be quoted as expected production rates. The stronger inference is simply that permission compliance must be measured separately from SQL execution accuracy.

Statistical anomalies are not defects

A genuine 50% sales increase is statistically unusual in many businesses and perfectly correct. A failed pipeline can generate an ordinary-looking 2% movement. Therefore statistical monitoring and business materiality answer different questions:

\text{Unusual} \not\Rightarrow \text{incorrect}

and:

\text{ordinary-looking} \not\Rightarrow \text{correct}

Statistical monitoring is consequently best treated as a prioritization/discovery layer. Deterministic invariants and independent reconciliation are stronger evidence for defects when they are available.

Reconciliation proves agreement, not reality

Even source-to-warehouse reconciliation has an epistemic boundary. If both systems contain the same source capture error, perfect reconciliation merely demonstrates consistent replication. An upstream API can omit a payment before the warehouse ever sees it.

This is why the word authoritative matters. A team should know what external control, operational system, ledger, processor settlement file or application record is authoritative for the decision. That choice is domain-specific and cannot be automated from warehouse statistics.

“Exactly once” is less useful than idempotent evidence

For the analytical consumer, the important property is not a marketing claim that a distributed pipeline has “exactly once” semantics. It is whether the business-level event can be identified idempotently and the output reconciled. Distributed event processing explicitly deals with late, out-of-order and potentially revised information rather than assuming a point at which all unbounded data is permanently known. 7

A stable source event ID plus a uniqueness invariant is therefore more diagnostically useful than an unaudited statement that the pipeline “should not duplicate records.”

Contract coverage can become expensive without increasing decision reliability

dbt itself warns that governance features introduce structure and can complicate changes or increase maintenance when adopted too early. 9 The economic implication is to concentrate stronger contracts on stable, decision-critical interfaces, not to attempt to make every staging column perfect before allowing AI experimentation.

A sensible asymmetry is:

stricter controls around the handful of metrics the assistant is allowed to explain; lighter exploratory controls elsewhere.

That is an interpretation/recommendation rather than a measured optimum.

Important unresolved research gaps

The reviewed evidence does not provide a strong longitudinal study comparing, on the same real organizational analytics workflow, all of:

1. a conventional analyst,
2. an AI assistant over uncontrolled warehouse data,
3. an AI assistant behind deterministic quality gates,
4. an AI assistant behind quality gates plus semantic and lineage context.

That is the experiment most directly relevant to the question.

There is also insufficient public empirical evidence to assign a defensible percentage of AI-analytics failures to missingness versus duplicates versus semantic drift versus model reasoning. Any article claiming such a distribution would need new first-party incident data.

Likewise, the evidence does not identify a universally optimal monitoring architecture for a five-person data team. The economically correct boundary depends on query volume, revenue materiality, pipeline cadence and incident cost.

Claim-to-source dossier

The table distinguishes normative/provider documentation from empirical research. “Exact scope” states what the source actually supports, not what one might wish to infer from it. All sources were inspected on 2026-10-10.

Claim supported	Original source	Source date/version	Exact scope	Evidence type
Explicit data assumptions can be automatically validated; violating assumptions such as expected ranges, missingness or categorical domains can cause crashes or wrong predictions	Schelter et al., “DEEQU - Data quality validation for machine learning pipelines” — https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines 1	2018	ML/data pipeline validation at Amazon; does not measure BI-assistant failure rates	Primary company research / research paper
Upstream data problems can cascade into downstream AI problems; study based on 53 practitioners in several countries including US	Sambasivan et al., “Everyone wants to do the model work, not the data work” — DOI 10.1145/3411764.3445518; https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/ 2	2021	Qualitative high-stakes-AI practice; not an analytics-assistant evaluation	Peer-reviewed empirical qualitative research
Out-of-order/unbounded data creates correctness-latency-cost trade-offs and one cannot simply assume eventual complete information	Akidau et al., “The Dataflow Model” — DOI 10.14778/2824032.2824076; https://research.google/pubs/the-dataflow-model-a-practical-approach-to-balancing-correctness-latency-and-cost-in-massive-scale-unbounded-out-of-order-data-processing/ 7	2015	Large-scale streaming/data-processing model; supports late/completeness reasoning, not specific monthly-finance controls	Peer-reviewed systems research
dbt’s standard tests directly check nulls, duplicate values, accepted domains and referential relationships	dbt Developer Hub, Data tests — https://docs.getdbt.com/reference/resource-properties/data-tests 6	Last updated 2026-03-13	Current dbt test semantics; provider documentation, not evidence of defect prevalence	Versioned official documentation
dbt freshness expresses warn_after/error_after against a loaded timestamp or metadata; freshness therefore concerns recency	dbt Developer Hub, freshness — https://docs.getdbt.com/reference/resource-configs/freshness 8	Current docs inspected 2026-10-10; page revision current for dbt v2/current tracks	dbt freshness behavior; does not establish source completeness	Versioned official documentation
Enforced model contracts check output names/types against the contract; content tests remain a separate mechanism; warehouse constraint enforcement differs by platform	dbt Developer Hub, Model contracts — https://docs.getdbt.com/docs/mesh/govern/model-contracts 9	Last updated 2026-09-10	dbt models and supported materializations/platforms	Versioned official documentation
Semantic models explicitly represent entities/join keys, a time dimension, dimensions and simple metrics	dbt Developer Hub, Semantic models — https://docs.getdbt.com/docs/build/semantic-models 5	Current page for dbt v1.12+/v2; inspected 2026-10-10	dbt/MetricFlow semantics; shows one implementation of formal metric metadata, not correctness of the business definition	Versioned official documentation
A lineage model can connect jobs, runs and input/output datasets and record runtime/design lineage	OpenLineage, Object Model — https://openlineage.io/docs/spec/object-model/ 10	Specification v1.53.0	OpenLineage specification; establishes representational capability, not causal root-cause detection	Open technical specification
Warehouse authorization can restrict a principal to an approved view/subset and can use row-/column-level controls	Google Cloud, BigQuery Authorized Views — https://docs.cloud.google.com/bigquery/docs/authorized-views 11	Last updated 2026-10-07 UTC	BigQuery authorization behavior; one platform implementation	Official provider documentation
Enterprise-oriented text-to-SQL tasks remain substantially harder than older benchmarks; Spider 2.0 includes 632 workflow tasks, large schemas and project/documentation context	Lei et al., Spider 2.0 — arXiv 2411.07763; https://arxiv.org/html/2411.07763v2 3	v2, 2025-03-17	Benchmark of enterprise-like workflows; reported benchmark performance, not production reliability	Primary empirical benchmark paper
Benchmark annotation defects can materially change measured agent performance and rankings	Jin et al., “Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards” — arXiv 2601.08778; DOI 10.14778/3796195.3796206; https://arxiv.org/html/2601.08778v1 4	2026-01-13 preprint; VLDB 2026 publication	Expert analysis of BIRD Mini-Dev and Spider 2.0-Snow plus reevaluation of 16 open-source agents	Primary empirical benchmark-audit study
Query ambiguity can arise from schema mapping, values, intent, sources, context and temporal/spatial reference, motivating explicit clarification	Ding, Lin & Zeng, AmbiSQL — arXiv 2508.15276; https://arxiv.org/html/2508.15276v2 13	v2, 2026-03-23	Interactive ambiguity-resolution system and demonstration; not proof of universal ambiguity detection	Primary research / system demonstration
Abstention and human feedback can be incorporated into text-to-SQL schema linking rather than requiring the system to answer every query	Chen et al., “Reliable Text-to-SQL with Adaptive Abstention” — DOI 10.1145/3709719; https://arxiv.org/html/2501.10858v1 14	2025-01-18 arXiv; SIGMOD/PACMMOD 2025	BIRD-focused schema-linking and text-to-SQL experiments; parts rely on model internals/conformal techniques	Peer-reviewed empirical systems research
Standard text-to-SQL accuracy does not capture permission compliance; role-aware evaluation exposes violations and over-refusal	Fei et al., “Benchmarking Text-to-SQL under Role-Based Access Control” — arXiv 2607.22115; https://arxiv.org/html/2607.22115v2 12	v2, 2026-09-22	RBAC-augmented benchmark across Spider/BIRD/LiveSQLBench and multiple models; policies are benchmark constructions, not observed enterprise incidents	Primary empirical benchmark paper

Source-access note. The research used accessible HTML versions or official documentation wherever possible. Search also surfaced PDF versions of the Dataflow paper and the CIDR/VLDB text-to-SQL benchmark work; those PDFs were not substituted silently for inspected HTML sources. The dossier relies on the accessible official/research HTML records cited above. No private source, proprietary customer incident dataset or unpublished company benchmark was inspected.

Candidate article angles, practical assets and freshness triggers

Strongest reusable article angles

“AI does not need perfect data. It needs falsifiable data.”
This is the strongest general angle. It avoids the stale “garbage in, garbage out” cliché and gives data leaders an actionable standard: an AI answer should be capable of being disproven using source, grain, reconciliation, semantic version, lineage and executed-query evidence. The supporting evidence is stronger than a claim that all source data must first become “high quality.” 1

“A fresh table can still be wrong.”
Narrower and technically sharp. Explain the difference between MAX(loaded_at) freshness and completeness, then use the synthetic delayed-payment case. dbt’s freshness semantics and the Dataflow literature make this distinction particularly defensible. 8

“Seven ways an AI analyst can confidently explain the wrong number.”
Organize around source capture, late data, replays, schema drift, grain/join defects, semantic drift and model/query errors. The important twist is that only the last category is inherently an AI defect; the rest can also fool dashboards and human analysts.

“Lineage is a search-space reducer, not a truth oracle.”
A practitioner-focused piece showing how lineage can narrow a revenue anomaly from a top-line KPI to a specific source/transformation path without claiming that lineage identifies causal truth. OpenLineage’s current object model provides a concrete reference implementation. 10

“The metric changed—or its definition did.”
Focus on semantic consistency. This angle is especially useful for active-customer metrics, where a structurally flawless warehouse can show an artificial trend after an identity or qualification rule changes. dbt’s current semantic-model primitives provide a concrete technical anchor. 5

“Your AI benchmark can have bad data too.”
Use the 2026 annotation-error results to connect ordinary data-quality thinking to AI evaluation itself. The strongest message is not that benchmarks should be ignored, but that evaluation labels, source definitions and test fixtures deserve the same scrutiny as production datasets. 4

“Authorization is part of analytical correctness.”
A more advanced angle: an answer that is numerically correct but obtained from forbidden data is a system failure, while an assistant that over-refuses allowed queries is also operationally unreliable. The 2026 RBAC work makes this separable and measurable. 12

Portable practical asset: pre-interpretation evidence card

The following is a reusable specification rather than executable software. It can be implemented in metadata, an evaluation harness or the response envelope of an analytics assistant.

analytical_answer_evidence:
  question: "Why did monthly revenue change?"
  resolved_metric:
    name: "monthly_net_revenue"
    definition_version: "<required>"
    grain: "one logical payment event"
    aggregation: "sum(amount) after approved qualification/refund logic"
    time_basis: "<event_time | posting_time | other>"
    timezone: "<required>"
    currency_and_unit: "<required>"

  data_state:
    data_as_of: "<timestamp>"
    source_freshness:
      status: "<pass | warn | fail>"
      evidence: "<timestamp/check reference>"
    completeness:
      status: "<pass | unknown | fail>"
      evidence: "<reconciliation/control total reference>"
    uniqueness:
      status: "<pass | fail>"
      key: "<logical event key>"
    schema_contract:
      status: "<pass | fail>"
      version: "<contract version>"

  provenance:
    source_models:
      - "<model/table identifier>"
    lineage_run: "<lineage/run identifier>"
    transformation_version: "<git/dbt/artifact version>"
    executed_sql: |
      <exact SQL required for reproducibility>

  authorization:
    effective_role: "<role/service identity>"
    authorized_surface: "<view/model/policy boundary>"
    permission_check: "<pass | fail>"

  interpretation:
    observed_facts:
      - "<supported numerical observation>"
    hypotheses:
      - "<explicitly labeled, non-causal hypothesis>"
    unresolved_ambiguities:
      - "<none, or description>"
    decision:
      status: "<answer | clarify | abstain | human_review>"
      reason: "<evidence-based reason>"

The design deliberately keeps observed facts and hypotheses separate. That separation is especially important because a correctly calculated correlation does not establish a business cause.

Freshness triggers for this dossier

The evidence should be rechecked when any of the following occurs:

Trigger	Why it matters
Major dbt version or semantic-layer contract change	Test, freshness, contract or metric semantics may change; the cited documentation is explicitly versioned and current through 2026. 8
OpenLineage specification change affecting run/dataset/version facets	Could alter recommended provenance implementation; this dossier inspected v1.53.0. 10
Warehouse permission-model change	Authorization recommendations must follow the deployed platform; the BigQuery page inspected here was updated October 7, 2026. 11
Material new enterprise text-to-SQL evaluations	Model-side query reliability is changing quickly, so specific capability numbers should age faster than the underlying data-quality reasoning. Spider 2.0 itself shows how benchmark difficulty changes conclusions. 3
Corrections to BIRD, Spider 2.0 or successor benchmarks	The 2026 audit showed that annotation quality can materially change performance and rankings. 4
New evaluations combining permissions, semantic ambiguity and live enterprise data	Current evidence treats these reliability dimensions mostly in separate studies; a combined longitudinal evaluation would materially strengthen or revise this dossier. 12
Internal incident evidence becomes available	Real incident distributions could replace the deliberately non-quantified defect-priority assumptions used here

The practical bottom line remains stable across the evidence reviewed: put AI after the evidence boundary, not before it. Let deterministic systems establish what can be established mechanically; let lineage and reconciliation expose uncertainty; let the model retrieve, query and interpret within those constraints; and let it clarify or abstain when the authoritative data, semantic definition or permission boundary is unresolved. This is a materially lower bar than “make all data perfect,” but a much higher and more useful bar than “the chatbot returned a plausible number.”

Portable citation index

1. DEEQU - Data quality validation for machine learning pipelines - Amazon Science
2. “Everyone wants to do the model work, not the data work”: Data Cascades in High-Stakes AI
3. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
4. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
5. Semantic models | dbt Developer Hub
6. About data tests property | dbt Developer Hub
7. The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost in Massive-Scale, Unbounded, Out-of-Order Data Processing
8. freshness | dbt Developer Hub
9. Model contracts | dbt Developer Hub
10. Object Model | OpenLineage
11. Authorized views  |  BigQuery  |  Google Cloud Documentation
12. Benchmarking Text-to-SQL under Role-Based Access Control
13. AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL
14. Reliable Text-to-SQL with Adaptive Abstention

Import verification and asset availability

Captured from genuine ChatGPT Deep Research with snapshot 2026-10-10; completed in 7 minutes. Retrieved 2026-10-10. Original prompt and canonical conversation are retained on this run. Complete synthetic code/examples are inline in this report; no external sandbox asset download is required or claimed. Embedded examples and report statements about execution have not been independently run or verified during import. Delivery is not independent validation of source claims, dates or applicability. Evidence remains Captured.

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

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

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

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

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

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

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

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

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

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

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

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

Enterprise-style benchmarks versus trustworthy leaderboards. Spider 2.0 is useful evidence that realistic analytical SQL is hard. 1 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. 7 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. 13

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

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

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

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

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. 2	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. 4	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. 5	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. 16	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. 3	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. 6	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. 1	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. 7	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. 10	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. 8	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. 17	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. 9	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. 11	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. 14	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. 15	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. 13

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.

Portable citation index

1. Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
2. CleanML: A Study for Evaluating the Impact of Data Cleaning on ML Classification Tasks
3. arxiv.org
4. https://arxiv.org/pdf/1702.00820
5. HoloDetect: Few-Shot Learning for Error Detection
6. “Everyone wants to do the model work, not the data work”: Data Cascades in High-Stakes AI
7. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards
8. freshness | dbt Developer Hub
9. Model contracts | dbt Developer Hub
10. About data tests property | dbt Developer Hub
11. Object Model | OpenLineage
12. https://arxiv.org/pdf/1904.09483
13. CleanML: A Study for Evaluating the Impact of Data Cleaning on ML Classification Tasks
14. Regulation - 2016/679 - EN - gdpr - EUR-Lex
15. AI Risk Management Framework | NIST
16. Raha: A Configuration-Free Error Detection System
17. Unit tests | dbt Developer Hub

Import verification and asset availability

Captured from genuine ChatGPT Deep Research with snapshot 2026-10-10; completed in 11 minutes. Retrieved 2026-10-10. Original prompt and canonical conversation are retained on this run. Complete synthetic code/examples are inline in this report; no external sandbox asset download is required or claimed. Embedded examples and report statements about execution have not been independently run or verified during import. Delivery is not independent validation of source claims, dates or applicability. Evidence remains Captured.

Data Quality Before Adding AI: Practitioner Evidence Dossier

Research snapshot: 2026-10-10
Perspective: Practitioner
Target system: Small professional data team operating ingestion pipelines, a cloud analytical warehouse, dbt-style transformations, and an AI analytical assistant
Decision under study: Which data defects undermine AI outputs, and how should they be diagnosed before an assistant explains changes in monthly revenue or active-customer counts?

Decision answer and uncertainty

Bottom line

The useful rule is not “make all data perfect before adding AI.” It is:

Before an AI analytical assistant interprets a business result, deterministically establish that the specific metric, period, grain, source path, semantic version, and permissions needed for that answer are fit for purpose. If that evidence is missing or contradictory, the assistant should diagnose, clarify, or abstain—not explain the number.

For monthly revenue or active-customer analysis, the most dangerous defects are usually not the ones that crash a pipeline. They are defects that leave a plausible, queryable table behind: replayed events, incomplete periods, grain-changing joins, silent semantic changes, stale or wrong metric documentation, and valid-looking SQL against the wrong source. BigQuery, for example, explicitly documents that its default Storage Write API stream uses at-least-once semantics while application-created streams can use offsets for exactly-once writes; a successful ingestion API therefore does not by itself establish business-event uniqueness. 1

The minimum viable readiness layer for this workflow is:

decision owner → authoritative definition → preserved raw events and batch identity → source/control reconciliation → uniqueness/grain → schema contract → completeness and freshness → semantic/version contract → lineage → warehouse-enforced read permissions → deterministic reference calculation → AI interpretation.

That ordering matters. An LLM cannot reliably infer an event that never arrived, determine which of two legitimate definitions Finance intended, or prove that a plausible aggregate is complete from the aggregate alone. Research on “data cascades” found compounding downstream consequences from data problems in interviews with 53 practitioners working on high-stakes AI; the study reported data cascades in 92% of its interviewed cases. That is qualitative evidence from different application domains, not a measured effect size for analytical assistants, but it supports treating upstream data dependencies as first-class system components rather than as background plumbing. 2

Likewise, clean data is necessary but not sufficient for reliable AI analytics. Spider 2.0 uses databases derived from real enterprise applications, often with more than 1,000 columns; its authors found that their o1-preview-based code-agent framework solved 21.3% of Spider 2.0 tasks, versus 91.2% on Spider 1.0 and 73.0% on BIRD. This is benchmark evidence about model capability under complex text-to-SQL conditions, not expected accuracy for a small company’s revenue assistant. It does establish that database metadata, SQL generation, and system interaction remain independent failure layers after data quality has been addressed. 3

The same caution applies to retrieval. In the peer-reviewed “Lost in the Middle” experiments, model performance on multi-document QA and key-value retrieval varied materially with where relevant evidence occurred in a long context, showing that simply putting authoritative documentation into a model’s context does not guarantee that it will use it correctly. 4 CRAG, a 4,409-question RAG benchmark, similarly reported a substantial gap between retrieval augmentation and fully trustworthy factual QA; straightforward RAG improved accuracy in its experiments but did not eliminate wrong answers. Again, that is capability evidence, not a production estimate for this target workflow. 5

The key distinction: model capability versus system reliability

A useful architecture separates five things that are often blurred together:

Layer	What it should do	Primary reliability question
Retrieval	Locate metric contracts, lineage, run metadata and documentation	Did it retrieve the right source and right version?
SQL/code execution	Select, join and aggregate warehouse data	Did it query the right data at the right grain and snapshot?
Deterministic calculation	Compute sums, counts, deltas and decompositions	Do formulas and invariants reconcile?
Model interpretation	Turn checked results into an explanation	Are claims supported by the checked results rather than invented?
Human judgment	Resolve business meaning, materiality and remediation	Was an ambiguous definition or repair decision escalated to its owner?

NIST’s current AI Risk Management materials emphasize testing, evaluation, verification and validation rather than treating model output alone as assurance. The current NIST Playbook also calls for documenting operating conditions and limitations, testing validity and reliability, monitoring operation outside defined limits, and introducing human review or warnings when outputs may be unreliable. 6

Uncertainties that should remain explicit

There is no public causal study I found that measures how much this exact gate sequence reduces erroneous monthly-revenue explanations in a small cloud-warehouse team. The workflow below is therefore an engineering synthesis from database semantics, transformation tooling, lineage standards, AI evaluation guidance, and empirical AI-system research—not an experimentally proven universal recipe. 2

The evidence also comes from heterogeneous sources. dbt, Google Cloud and OpenLineage documentation establish what those systems can enforce or represent; they are provider/project documentation, not independent evidence of business outcomes. Data Cascades is qualitative empirical work. Spider 2.0, Lost in the Middle and CRAG are controlled AI evaluations that illuminate failure modes but do not reproduce this target company’s workflow. 7

What actually undermines an analytical assistant

Defect-by-defect diagnosis

The central practitioner distinction is between a defect the assistant can observe with evidence, one it can only suspect, and one it will simply repeat unless another system exposes the contradiction.

Defect	How the answer becomes wrong	Minimum deterministic evidence	What AI can reasonably help detect	What AI cannot establish alone
Upstream capture defect	A source event is never created or is created incorrectly	Independent source/control record, ledger reconciliation, sequence gap, external total	Explain a discrepancy once controls expose one; search lineage for affected path	That an absent event existed if every available source omitted it
Late/missing ingestion	Period is calculated before all expected events arrive	Close watermark, expected event/control total, source sequence, event-time vs ingestion-time evidence	Identify lateness patterns and affected periods	Completeness from MAX(ingested_at) alone
Replay/duplicate ingestion	Additive measures are counted multiple times	Stable event/idempotency key, uniqueness test, batch/run identity	Find duplicate keys and batches; propose dedup query	Whether duplicate-looking business events are truly duplicates without source semantics
Schema drift	Fields disappear/change type/meaning; transformations misbehave	Versioned schema/model contract and required-column checks	Explain contract violation or locate changed fields	Whether an intentional upstream semantic change should be accepted
Transformation or grain defect	Join fanout, filter error or aggregation at wrong grain	Declared model grain, uniqueness invariants, before/after row and control totals	Trace SQL/lineage; identify suspicious join	Intended grain if nobody defined it
Freshness defect	Query reads an old snapshot	Timestamp/watermark threshold appropriate to the source	Report staleness and block interpretation	Whether fresh-looking data are complete
Semantic-definition change	Same label denotes a different metric across periods or tools	Versioned metric definition, effective dates, owner and formula	Retrieve and compare explicit versions	Which definition the business intended if documentation conflicts
Retrieval/source error	Assistant uses obsolete or unrelated documentation	Retrieved source IDs, versions/hashes, effective dates	Surface conflicting sources and cite them	Guarantee correct use of evidence merely because it was retrieved
SQL/model error	Correct data are queried incorrectly or interpreted incorrectly	Reviewed reference queries/results and execution-based tests	Generate alternatives, compare outputs, explain differences	Self-certify correctness from fluent output
Permission defect	Assistant sees data the user should not see	Warehouse-enforced IAM/row/column controls and negative tests	Respect externally enforced scopes	Reliably substitute prompts for database authorization

This taxonomy is deliberately more specific than “bad data in, bad AI out.” dbt’s current generic tests include unique, not_null, accepted_values, and relationships; model contracts can enforce declared column names and types. Those mechanisms are useful but cover only a subset of the table above: a perfectly typed, non-null, unique dataset can still represent an incomplete month or the wrong business definition. 8

Freshness is not completeness

dbt’s freshness configuration evaluates sources/models against specified age thresholds. By design, that is evidence about how recently the configured timestamp advanced; it is not a source-to-target completeness proof. The distinction is an inference from what the documented freshness check actually measures, and the synthetic fixture later in this dossier demonstrates how a replay can make a table look recently updated while a genuine event remains missing. 9

That distinction is operationally important:

\text{freshness} \neq \text{completeness}

and

\text{row count} \neq \text{business-event count}

when retries or replays are possible.

For a revenue metric, a source control such as expected_unique_events = 4 plus expected_amount_cents = 42,000 is much stronger evidence than “four rows landed.” Ideally the source supplies one or more independent control totals; otherwise the team must be honest that its completeness evidence is weaker.

At-least-once delivery turns uniqueness into a business requirement

Google documents that BigQuery’s default Storage Write API stream is at-least-once, while exactly-once behavior is available through application-created streams using offsets. This is one concrete warehouse example of why a green ingestion job is not equivalent to a unique business-event set. 1

Exactly-once delivery is not a complete solution either. It addresses a delivery-layer property. The source itself can emit two different records representing the same business action, an upstream application can retry under new identifiers, corrections can legitimately repeat a business key, and later transformations can reintroduce duplication through fanout. The practical implication is to preserve both transport identity and business identity, and to define explicitly which one establishes the metric’s grain. This is an architectural inference from the documented delivery semantics and the need for explicit entity/grain definitions in semantic modeling. 1

Grain is a contract, not something an LLM should guess

Current dbt semantic-model documentation requires semantic models to describe entities, dimensions, time dimensions and metric aggregations; it distinguishes primary, unique, foreign and natural entity types. Its example explicitly describes a transaction fact as one row per transaction. 10

That is the right mindset even without dbt’s Semantic Layer:

A metric-ready model should answer, in plain language, what does one row represent?

For example:

fct_payment_event: one row per unique successful payment event.

is operationally testable.

“Revenue table” is not.

A fanout join can preserve valid SQL, valid types, recent timestamps and plausible totals while changing that grain. The deterministic defense is not asking an LLM whether the query “looks right”; it is checking invariants such as:

N_{\text{distinct event IDs, output}}
=
N_{\text{distinct event IDs, input}}

for a purported many-to-one enrichment, plus additive-control reconciliation before and after the join.

Semantic inconsistency is qualitatively different from dirty rows

Suppose one team defines an active customer as a customer with any successful transaction in the calendar month, while another defines it as a customer with qualifying activity during the trailing 30 days at month-end. Both can be computed flawlessly from perfectly clean data and still disagree.

There is no statistical test that can decide which definition management intended.

This is why the semantic contract needs, at minimum:

Contract field	Example
Metric name	active_customer
Version	v3
Owner	Growth Analytics
Grain	one customer per evaluation date
Formula	qualifying customers with ≥1 successful activity event
Time basis	trailing 30 days ending 23:59:59 UTC
Filters/exclusions	test accounts excluded
Effective from	2026-07-01
Supersedes	v2 calendar-month activity
Source path	certified activity fact model
Late-data policy	restate through T+2
Currency/unit where relevant	customers, not events

dbt now supports explicit semantic-model definitions and model versioning, providing product-level mechanisms for related governance concerns. 11

The assistant should therefore treat unknown or conflicting semantic versions as an ambiguity condition, not as an invitation to choose whichever definition produces the more plausible narrative.

Lineage turns “the number is wrong” into a bounded search

OpenLineage’s current specification, version 1.53.0 in the inspected documentation, represents runs, jobs and datasets and supports metadata facets including schema, versions, output statistics and data-quality information. Its column-lineage facet distinguishes direct transformations from indirect dependencies and records operations such as aggregation, join, group-by, filter, sort, window and conditional dependencies. 12

For an unexpected revenue movement, that can reduce the diagnosis from:

“Inspect the entire warehouse.”

to something closer to:

metric → revenue expression → payment amount/status/event-time columns → dedup model → raw payment source → implicated ingestion runs.

That is a large practical advantage, but lineage is evidence about declared/observed dependencies, not proof that the transformation is semantically correct. If instrumentation is incomplete or a hand-written process lies outside the lineage graph, the graph cannot expose it.

Google’s older “Hidden Technical Debt in Machine Learning Systems” paper made essentially the same system-level observation: data dependencies can be harder to detect than code dependencies, and the authors advocated tooling that makes dependency trees resolvable; it also explicitly identified data testing, monitoring and upstream producers as system concerns. This paper reflects Google engineering experience and conceptual analysis rather than a controlled experiment. 13

Minimal readiness and diagnosis workflow

Start with the decision, not with an observability platform

For this dossier, take the concrete analytical request:

“Why did monthly gross successful payment value change from August to September 2026?”

I use gross successful payment value, not “recognized revenue,” deliberately. A payment-events table does not, by itself, establish accounting revenue recognition. Calling payment volume “recognized revenue” before Finance has supplied the recognition policy would itself be a semantic defect.

For a true recognized-revenue decision, the authoritative source might instead need a billing/subledger/general-ledger path, recognition schedules, credit notes, currency policy, accrual treatment and a Finance-owned definition. The readiness workflow stays the same; the authoritative data path changes.

The minimum useful gate

For a small team, I would implement the following as a single answer-readiness record attached to each AI analytical run, rather than starting with a large generic “data quality program.”

Gate	Evidence required before interpretation	Hard failure action	AI’s allowed role
Owner and decision	Named metric/decision owner; requested comparison and materiality	Clarify	Ask for missing intent
Raw preservation	Immutable/replayable raw records or equivalent snapshot	Abstain from root-cause claim if raw evidence unavailable	Read metadata only
Batch identity	Ingestion/run ID, source sequence/offset where available, ingestion time	Quarantine implicated batch if inconsistent	Identify affected runs
Source reconciliation	Expected vs landed business count plus control amount/hash where available	Mark period untrusted	Explain mismatch
Uniqueness and grain	Declared business key plus uniqueness/fanout checks	Block additive explanation	Generate diagnostic SQL
Schema	Required columns and types match versioned contract	Block dependent metric	Locate breaking change
Freshness and completeness	Recent enough and period-close/watermark/completeness evidence	Mark provisional or abstain	State missing evidence
Semantic contract	Formula, time basis, filters, unit, definition version, effective dates, owner	Clarify conflicting/unknown definition	Retrieve exact contract
Lineage	Trace from metric to critical sources/transforms	Human review if critical dependency unknown	Narrow diagnostic scope
Permissions	Read-only warehouse role plus row/column restrictions appropriate to requester	Deny unavailable data	Never bypass
Reference calculation	Reviewed deterministic metric query/result for tested scenarios	Do not ship assistant as authoritative	Compare generated result
Answer evidence	Snapshot/run IDs, query, semantic version and checked outputs	Abstain from definitive narrative	Explain only checked facts

dbt contracts and built-in tests are useful implementations for schema and basic relational invariants; its freshness machinery supplies the recency portion of the gate, while semantic models and versions can encode parts of the semantic layer. 14

OpenLineage provides a standardized way to carry run/dataset/column relationships and relevant metadata, making it suitable for the lineage and reproducibility portions of the workflow. 12

What is genuinely minimal

For this particular task, I would not make deployment contingent on perfect quality across the entire warehouse.

The critical path is narrower:

raw payment/ledger source → normalization/deduplication → relevant dimensions → certified metric model → assistant query.

A nullable marketing-attribution field on an unrelated table should not block an answer about payment value. A duplicate payment event should.

That is the main leverage of lineage: quality gates should follow the decision’s critical dependency path rather than expand into an indiscriminate “clean the whole data estate” program. OpenLineage’s explicit representation of direct and indirect column dependencies is particularly useful for constructing this scope. 15

More sophisticated anomaly detection, statistical drift monitors, generalized observability platforms and autonomous repair can come later. They are not substitutes for event identity, grain, semantic definitions and source reconciliation.

Controlled AI access

For an analytical assistant, permissions should be enforced below the model, in the warehouse/tool boundary.

Google BigQuery, as one concrete implementation example, supports project/dataset/table controls, column controls and row-level policies; its row-level security documentation explicitly describes fine-grained access to subsets of rows based on user conditions and frames this as extending least privilege. 16 BigQuery also has data-viewer roles distinct from editing capabilities. 17

A safe initial design is therefore:

assistant can read certified marts, metric contracts, lineage, quality-check results, and approved metadata; submit SELECT queries within resource limits; and produce explanations.

assistant cannot insert/update/delete production data, modify transformations, rewrite quality rules, grant permissions, drop duplicates, or “repair” a historical month autonomously.

NIST SP 800-53 is a US security-control catalog rather than a law for ordinary private companies, but its current Release 5.2.0 remains a useful control reference for access management. NIST issued that release in August 2025. 18

Quarantine and recovery boundaries

When a hard gate fails, the preferred response is to preserve evidence rather than to make the bad rows disappear.

For example:

Replayed batch: retain raw replay rows with their batch identity; exclude them through an reviewed idempotent staging rule; do not delete the raw incident evidence.

Late batch: ingest it normally into raw history, rerun the affected deterministic transformation window, and mark previously published values as restated if the metric contract permits restatement.

Schema change: fail or quarantine the incompatible downstream path until the producer/consumer contract is reconciled. dbt model contracts can enforce names and types at the transformation boundary, but cannot decide whether a new business meaning is intended. 14

Fanout transformation: repair version-controlled transformation logic, then compare the rebuilt output with the preserved input and control totals before promotion.

Semantic disagreement: do not “repair the data.” Escalate to the metric owner, version the definition and recompute only according to an approved effective-date policy.

The AI may generate diagnostic queries and summarize evidence, but production recovery should remain an auditable engineering/owner operation. NIST’s AI RMF Playbook explicitly contemplates defined post-alert actions, human review, downstream notifications and logging of the conditions under which AI behavior exceeded validity limits. 19

Worked synthetic example and portable fixture

Scenario

This is a synthetic, executed fixture, not a customer result.

The business metric is:

GPV_m=
\frac{\sum_{\text{unique successful payment events in month }m}
amount\_cents}{100}

with USD as the only currency in the fixture.

August source truth is:

GPV_{Aug} = \$300.00

September source truth is four unique payment events:

\$100+\$150+\$50+\$120=\$420

so the reconciled month-over-month change is:

\Delta = \$420-\$300=\$120

\%\Delta
=
\frac{420-300}{300}\times100
=
40.0\%

The synthetic pipeline contains two ingestion problems and one transformation problem:

1. Event S2, worth $150, is replayed shortly before month close.
2. Genuine event S4, worth $120, does not land until October 2.
3. A customer dimension contains two legitimate historical versions for customer c2; a join on customer_id without validity dates fans out its payment row.

Before S4 arrives, there are four raw rows, exactly matching the source’s expected row count of four. The replay therefore masks the missing event if the team’s only check is COUNT(*).

The latest ingestion timestamp is also 2026-09-30 23:58:00, making the table appear very recently updated.

Yet:

COUNT(*)=4,\qquad COUNT(DISTINCT event\_id)=3

and:

SUM(raw)=\$450 \neq \$420\ source\ control

This is the core failure pattern: the job can be green, the table can be fresh, the raw row count can match expectation, and the answer can still be wrong.

Observed outputs from the executed fixture

I executed the full SQL below on SQLite 3.46.1 in this research session. No external or production system was queried.

Check	Observed result
Pre-close expected events	4
Pre-close raw rows	4
Raw row-count-only check	PASS
Pre-close distinct event IDs	3
Unique-event check	FAIL
Expected control amount	42,000 cents
Raw pre-close amount	45,000 cents
Amount reconciliation	FAIL
Duplicate found	S2, two copies across sep_01 and replay_20260930
Latest pre-close ingestion	2026-09-30 23:58:00
Naïve September pre-close value	$450
Naïve apparent change	+50.0%
Deduplicated but incomplete value	$300
Deduplicated but incomplete change	0.0%
Final deduplicated/reconciled value	$420
Correct final change	+40.0%
Buggy SCD join input/output rows	4 → 5
Buggy join input/output amount	42,000 → 57,000 cents
Join row-fanout ratio	1.25
Correct temporal join	4 rows, 4 events, 42,000 cents
Late event	S4, landed 2026-10-02 08:00:00

Notice how three superficially reasonable explanations are possible from different broken stages:

“September grew 50%.” — replay included, late event missing.

“September was flat.” — replay removed, late event still missing.

“September grew 90%.” — final events available, but historical dimension joined at the wrong grain.

The checked answer is +40%.

A language model can make any of the first three narratives sound persuasive. The reliability gain comes from the deterministic evidence, not from prose quality.

Complete copyable SQLite fixture

PRAGMA foreign_keys = ON;

DROP TABLE IF EXISTS source_control;
DROP TABLE IF EXISTS raw_payments;
DROP TABLE IF EXISTS customer_dim;
DROP TABLE IF EXISTS metric_contract;

CREATE TABLE source_control (
    month_start TEXT PRIMARY KEY,          -- ISO date, UTC business calendar in this fixture
    expected_events INTEGER NOT NULL,
    expected_amount_cents INTEGER NOT NULL,
    closed_at TEXT NOT NULL                -- source-declared close watermark
);

CREATE TABLE raw_payments (
    raw_row_id INTEGER PRIMARY KEY,
    event_id TEXT NOT NULL,                -- authoritative idempotency key from source
    customer_id TEXT NOT NULL,
    event_time TEXT NOT NULL,              -- business event time (UTC)
    ingested_at TEXT NOT NULL,             -- warehouse landing time (UTC)
    amount_cents INTEGER NOT NULL,
    currency TEXT NOT NULL,
    batch_id TEXT NOT NULL,
    schema_version TEXT NOT NULL
);

CREATE TABLE customer_dim (
    customer_id TEXT NOT NULL,
    segment TEXT NOT NULL,
    valid_from TEXT NOT NULL,
    valid_to TEXT                         -- NULL = open ended
);

CREATE TABLE metric_contract (
    metric_name TEXT NOT NULL,
    metric_version TEXT NOT NULL,
    effective_from TEXT NOT NULL,
    effective_to TEXT,
    grain TEXT NOT NULL,
    formula TEXT NOT NULL,
    time_basis TEXT NOT NULL,
    currency TEXT NOT NULL,
    late_arrival_policy TEXT NOT NULL,
    owner TEXT NOT NULL,
    PRIMARY KEY (metric_name, metric_version)
);

INSERT INTO source_control VALUES
('2026-08-01', 3, 30000, '2026-09-01 00:00:00'),
('2026-09-01', 4, 42000, '2026-10-01 00:00:00');

INSERT INTO metric_contract VALUES
('gross_successful_payment_value', 'v1', '2026-01-01', NULL,
 'one row per unique successful payment event',
 'SUM(amount_cents) / 100.0',
 'event_time in UTC calendar month',
 'USD',
 'period is provisional until source close plus 3 days; late events restate the month',
 'finance-data@example.test');

-- August baseline: $300.00.
INSERT INTO raw_payments VALUES
(1, 'A1', 'c1', '2026-08-05 12:00:00', '2026-08-05 12:01:00', 10000, 'USD', 'aug_01', 'v1'),
(2, 'A2', 'c2', '2026-08-10 12:00:00', '2026-08-10 12:01:00', 12000, 'USD', 'aug_01', 'v1'),
(3, 'A3', 'c3', '2026-08-15 12:00:00', '2026-08-15 12:01:00',  8000, 'USD', 'aug_01', 'v1');

-- September source truth is 4 unique events totaling $420.00.
-- S4 is deliberately late: it lands after the source's month-close watermark.
-- S2 is deliberately replayed in a later batch. The replay makes raw row count = 4
-- before S4 lands, so a row-count-only load check can pass while the period is wrong.
INSERT INTO raw_payments VALUES
(4, 'S1', 'c1', '2026-09-05 12:00:00', '2026-09-05 12:01:00', 10000, 'USD', 'sep_01', 'v1'),
(5, 'S2', 'c2', '2026-09-10 12:00:00', '2026-09-10 12:01:00', 15000, 'USD', 'sep_01', 'v1'),
(6, 'S3', 'c3', '2026-09-20 12:00:00', '2026-09-20 12:01:00',  5000, 'USD', 'sep_01', 'v1'),
(7, 'S2', 'c2', '2026-09-10 12:00:00', '2026-09-30 23:58:00', 15000, 'USD', 'replay_20260930', 'v1'),
(8, 'S4', 'c4', '2026-09-25 12:00:00', '2026-10-02 08:00:00', 12000, 'USD', 'late_20261002', 'v1');

-- SCD-like customer dimension. c2 legitimately has two historical versions.
INSERT INTO customer_dim VALUES
('c1', 'SMB',        '2026-01-01 00:00:00', NULL),
('c2', 'SMB',        '2026-01-01 00:00:00', '2026-09-15 00:00:00'),
('c2', 'Enterprise', '2026-09-15 00:00:00', NULL),
('c3', 'SMB',        '2026-01-01 00:00:00', NULL),
('c4', 'Enterprise', '2026-01-01 00:00:00', NULL);

-- CHECK A: pre-close reconciliation at 2026-10-01 00:00:00.
-- A mere raw row-count check passes (4 = 4), but distinct ids and amount controls fail.
WITH landed AS (
  SELECT *
  FROM raw_payments
  WHERE event_time >= '2026-09-01'
    AND event_time <  '2026-10-01'
    AND ingested_at < '2026-10-01 00:00:00'
)
SELECT
  sc.expected_events,
  COUNT(*) AS raw_rows,
  COUNT(DISTINCT event_id) AS distinct_events,
  sc.expected_amount_cents,
  SUM(amount_cents) AS raw_amount_cents,
  CASE WHEN COUNT(*) = sc.expected_events THEN 'PASS' ELSE 'FAIL' END AS row_count_only,
  CASE WHEN COUNT(DISTINCT event_id) = sc.expected_events THEN 'PASS' ELSE 'FAIL' END AS unique_count,
  CASE WHEN SUM(amount_cents) = sc.expected_amount_cents THEN 'PASS' ELSE 'FAIL' END AS control_amount
FROM landed
JOIN source_control sc ON sc.month_start = '2026-09-01';

-- CHECK B: identify replay duplicates before the late event arrives.
SELECT event_id, COUNT(*) AS copies, GROUP_CONCAT(batch_id, ',') AS batches
FROM raw_payments
WHERE event_time >= '2026-09-01'
  AND event_time <  '2026-10-01'
  AND ingested_at < '2026-10-01 00:00:00'
GROUP BY event_id
HAVING COUNT(*) > 1;

-- CHECK C: freshness is not completeness.
-- This looks "fresh" because a replay landed two minutes before close, while S4 is still absent.
SELECT MAX(ingested_at) AS latest_ingestion_before_close
FROM raw_payments
WHERE event_time >= '2026-09-01'
  AND event_time <  '2026-10-01'
  AND ingested_at < '2026-10-01 00:00:00';

-- CHECK D: value at different diagnostic stages.
WITH
aug AS (
  SELECT SUM(amount_cents) AS cents
  FROM raw_payments
  WHERE event_time >= '2026-08-01' AND event_time < '2026-09-01'
),
sep_raw_preclose AS (
  SELECT SUM(amount_cents) AS cents
  FROM raw_payments
  WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
    AND ingested_at < '2026-10-01 00:00:00'
),
sep_dedup_preclose AS (
  SELECT SUM(amount_cents) AS cents
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY event_id
             ORDER BY ingested_at, raw_row_id
           ) AS rn
    FROM raw_payments
    WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
      AND ingested_at < '2026-10-01 00:00:00'
  )
  WHERE rn = 1
),
sep_dedup_final AS (
  SELECT SUM(amount_cents) AS cents
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY event_id
             ORDER BY ingested_at, raw_row_id
           ) AS rn
    FROM raw_payments
    WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
  )
  WHERE rn = 1
)
SELECT
  aug.cents AS aug_cents,
  sep_raw_preclose.cents AS sep_raw_preclose_cents,
  sep_dedup_preclose.cents AS sep_dedup_preclose_cents,
  sep_dedup_final.cents AS sep_dedup_final_cents,
  ROUND(
    100.0 * (sep_raw_preclose.cents - aug.cents) / aug.cents,
    1
  ) AS naive_preclose_pct_change,
  ROUND(
    100.0 * (sep_dedup_preclose.cents - aug.cents) / aug.cents,
    1
  ) AS dedup_but_incomplete_pct_change,
  ROUND(
    100.0 * (sep_dedup_final.cents - aug.cents) / aug.cents,
    1
  ) AS final_reconciled_pct_change
FROM aug, sep_raw_preclose, sep_dedup_preclose, sep_dedup_final;

-- CHECK E: detect a transformation/grain defect.
-- First deduplicate source events. Then intentionally join the SCD dimension
-- incorrectly on customer_id only, ignoring validity intervals; c2 fans out.
WITH dedup AS (
  SELECT *
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY event_id
             ORDER BY ingested_at, raw_row_id
           ) AS rn
    FROM raw_payments
    WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
  )
  WHERE rn = 1
),
buggy AS (
  SELECT d.event_id, d.amount_cents, c.segment
  FROM dedup d
  JOIN customer_dim c
    ON c.customer_id = d.customer_id
)
SELECT
  (SELECT COUNT(*) FROM dedup) AS input_rows,
  COUNT(*) AS output_rows,
  COUNT(DISTINCT event_id) AS output_distinct_events,
  (SELECT SUM(amount_cents) FROM dedup) AS input_amount_cents,
  SUM(amount_cents) AS output_amount_cents,
  ROUND(
    1.0 * COUNT(*) / (SELECT COUNT(*) FROM dedup),
    2
  ) AS row_fanout_ratio
FROM buggy;

-- CHECK F: correct temporal join preserves event grain and reconciled amount.
WITH dedup AS (
  SELECT *
  FROM (
    SELECT *,
           ROW_NUMBER() OVER (
             PARTITION BY event_id
             ORDER BY ingested_at, raw_row_id
           ) AS rn
    FROM raw_payments
    WHERE event_time >= '2026-09-01' AND event_time < '2026-10-01'
  )
  WHERE rn = 1
),
correct_join AS (
  SELECT d.event_id, d.amount_cents, c.segment
  FROM dedup d
  JOIN customer_dim c
    ON c.customer_id = d.customer_id
   AND d.event_time >= c.valid_from
   AND (c.valid_to IS NULL OR d.event_time < c.valid_to)
)
SELECT
  COUNT(*) AS rows_after_correct_join,
  COUNT(DISTINCT event_id) AS distinct_events,
  SUM(amount_cents) AS amount_cents
FROM correct_join;

-- CHECK G: late-arrival evidence.
SELECT
  event_id,
  event_time,
  ingested_at,
  batch_id
FROM raw_payments
WHERE event_time >= '2026-09-01'
  AND event_time <  '2026-10-01'
  AND ingested_at >= '2026-10-01 00:00:00'
ORDER BY ingested_at;

This fixture is intentionally simple. In a production warehouse, the same controls would normally be implemented in the warehouse’s SQL dialect and integrated with transformation/test orchestration. dbt’s built-in tests and model contracts cover portions of uniqueness, referential integrity and schema enforcement, while source freshness covers recency; the source-to-raw financial/control reconciliation shown here remains a separate requirement. 8

Evaluation, baseline, and abstention policy

The non-AI baseline matters

Before evaluating an AI assistant, establish a simple analyst baseline for exactly the same task.

For a monthly additive metric, a reasonable baseline is:

reviewed metric SQL + fixed semantic version + checked source snapshot + deterministic month-over-month calculation + deterministic contribution slices + analyst interpretation.

For example, once September has reconciled to $420 and August to $300, the system can deterministically calculate the $120 change and decompose that change by segment, product, geography or other dimensions. The AI layer should be judged against that workflow—not against “the analyst had to write everything manually.”

This matters because benchmark leaderboard scores are not evidence that an AI assistant improves this particular process. Spider 2.0 itself shows a dramatic difference between older benchmark performance and its more enterprise-like environment, which is precisely why target-workflow evaluation is necessary. 3

I would keep the deterministic baseline if AI does not deliver one of two things without reducing reliability:

lower interaction cost — users can ask more varied questions without analysts writing bespoke SQL; or

higher diagnostic breadth — the assistant efficiently follows lineage, runs approved checks and surfaces explanations that analysts would otherwise have to assemble manually.

“Produces nicer prose” is not enough.

Evaluation scorecard

A production evaluation set should contain realistic but approved historical/synthetic cases, including known defects, rather than only happy-path questions.

Dimension	Observable test
Metric/result correctness	Final numeric result equals reviewed reference for pinned snapshot
Query correctness	Executed query returns reference result; inspect joins/grain for sensitive tasks
Source fidelity	Answer cites the expected source model/data asset
Version fidelity	Correct metric/schema/document version for the requested effective date
Ambiguity handling	Assistant asks for clarification instead of silently selecting one valid definition
Completeness awareness	Assistant marks open/incomplete periods provisional
Permission compliance	Unauthorized rows/columns remain inaccessible under adversarial prompts
Reproducibility	Same snapshot + semantic version + query/config reproduces numerical evidence
Attribution	Material narrative claims are traceable to checked outputs
Failure recovery	Run IDs, batch IDs and lineage are sufficient to reproduce diagnosis
Latency	p50/p95 end-to-end response time under target workload
Cost	Warehouse compute + retrieval/model cost per completed investigation
Abstention quality	Known unanswerable cases trigger abstention rather than confident fabrication

NIST’s current evaluation materials support explicitly testing deployed AI validity, reliability, operating conditions and behavior outside those limits, rather than assuming that a system that works in development will generalize to every operational input. 19

Clarify, abstain, or escalate

A useful assistant should not treat every failure identically.

Clarification is appropriate when the data appear valid but the request has more than one legitimate interpretation: booked versus recognized revenue, event month versus invoice month, calendar-month versus trailing-30-day active customer, gross versus net-of-refund value, or metric version not specified where versions overlap.

Abstention is appropriate when the requested factual answer cannot currently be supported: source/control reconciliation fails; an additive metric violates grain; the period is open beyond the metric’s allowed provisional policy; a critical source is unavailable; the required semantic version cannot be established; or the user lacks permission to access the required data.

Human review is appropriate when multiple authoritative sources contradict one another, a material restatement or production repair is proposed, a semantic definition must change, lineage is incomplete across a critical dependency, or the AI-generated query introduces an unexpected join/filter against sensitive data. NIST’s Playbook specifically includes human review and user warnings among responses to unreliable or unexpected AI behavior. 19

An answer after a soft warning should expose the uncertainty:

“September currently appears +8%, but the source has not reached its T+2 close watermark. Two of the previous six months received material late events after this point. Treat this as provisional.”

An answer after a hard reconciliation failure should be stronger:

“I cannot reliably explain the September change. Source control expects 4 unique events / 42,000 cents, while the current landing has 3 unique event IDs / 45,000 cents. One replay is present and at least one expected event is unresolved.”

That is a better analytical product than a fluent but unsupported explanation.

Retrieval needs its own evaluation

Giving the assistant a semantic catalog or large documentation context should not be conflated with semantic correctness. “Lost in the Middle” provides direct experimental evidence that models can fail to use relevant information robustly depending on its placement in long contexts. 4 CRAG provides separate evidence that adding retrieval does not automatically eliminate factual error. 5

For this workflow, log the actual artifacts used in each answer:

metric_contract_id, metric_version, effective_date, model/version, warehouse snapshot/run, retrieved document IDs, query text/hash, quality-gate results, and ideally the assistant/model configuration.

That makes “the AI used the wrong definition” observable rather than anecdotal.

Evidence register, contradictions, and unresolved gaps

Claim-to-source register

Claim supported	Original source	Source date/version	Inspected	Exact scope	Evidence type
Upstream data problems can compound through AI systems; study interviewed 53 practitioners and reported 92% prevalence of “data cascades” in its cases	Google Research / CHI, “Data Cascades in High-Stakes AI” — https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/ ; DOI landing: https://doi.org/10.1145/3411764.3445518	2021	2026-10-10	High-stakes AI practitioners in India, East/West Africa and US; not analytical warehouses	Qualitative empirical study 2
Data dependencies, testing, upstream monitoring and dependency visibility are system-level ML concerns	“Hidden Technical Debt in Machine Learning Systems” — https://papers.neurips.cc/paper/5656-hidden-technical-debt-in-machine-learning-systems.pdf	2015	2026-10-10	Production ML system design, largely Google practitioner experience	Practitioner/research systems paper 13
dbt generic tests include uniqueness, non-null, accepted-values and relationships checks	dbt Developer Hub — https://docs.getdbt.com/docs/build/data-tests	Updated 2026-09-16	2026-10-10	dbt data tests	Provider documentation 8
dbt model contracts can enforce declared names/types at model boundaries	dbt Developer Hub — https://docs.getdbt.com/docs/mesh/govern/model-contracts and https://docs.getdbt.com/reference/resource-configs/contract	Updated 2026-09-10 / 2026-09-08	2026-10-10	dbt model contracts	Provider documentation 14
Freshness checks evaluate timestamp-based recency thresholds	dbt Developer Hub — https://docs.getdbt.com/reference/resource-configs/freshness	Updated 2026-09-16	2026-10-10	dbt source/model freshness	Provider documentation 9
Semantic models encode entities, time dimensions, dimensions and metric aggregation metadata	dbt Developer Hub — https://docs.getdbt.com/docs/build/semantic-models	Updated 2026-09-08	2026-10-10	dbt v1.12+ semantic models	Provider documentation 10
Model versions provide explicit versioned relations/change management	dbt Developer Hub — https://docs.getdbt.com/docs/mesh/govern/model-versions	Updated 2026-09-10	2026-10-10	dbt model versioning	Provider documentation 20
BigQuery default Storage Write stream is at-least-once; application-created streams can use offsets for exactly-once writes	Google Cloud — https://docs.cloud.google.com/bigquery/docs/write-api-grpc	Living documentation; page date not exposed in parsed content	2026-10-10	BigQuery Storage Write API	Provider documentation 1
OpenLineage represents jobs/runs/datasets and supports dataset/run metadata for lineage/observability	OpenLineage — https://openlineage.io/docs/spec/object-model/	v1.53.0 docs	2026-10-10	OpenLineage specification	Open standard documentation 12
OpenLineage column lineage can distinguish direct transformations and indirect JOIN/GROUP_BY/FILTER/etc. dependencies	OpenLineage — https://openlineage.io/docs/spec/facets/dataset-facets/column_lineage_facet/	v1.53.0 docs	2026-10-10	Column-level lineage facet	Open standard documentation 15
BigQuery can enforce fine-grained row-level access based on user conditions	Google Cloud — https://docs.cloud.google.com/bigquery/docs/row-level-security-intro	Living documentation	2026-10-10	BigQuery row-level security	Provider documentation 16
NIST AI RMF/AIRC emphasizes TEVV and managing AI reliability in operation	NIST — https://www.nist.gov/itl/ai-risk-management-framework and https://airc.nist.gov/	AI RMF 1.0; current site notes revision underway	2026-10-10	General AI risk management, not warehouse-specific	Government guidance 21
NIST SP 800-53 remains a current security/privacy control catalog; Release 5.2.0 was issued in Aug 2025	NIST — https://csrc.nist.gov/pubs/sp/800/53/r5/upd1/final ; DOI https://doi.org/10.6028/NIST.SP.800-53r5	Original Rev. 5 2020; Release 5.2.0 2025-08-27	2026-10-10	Security/privacy controls; not generally a private-sector US legal mandate	Government standard/guidance 18
Enterprise-style text-to-SQL remains substantially harder than older benchmarks in Spider 2.0	Lei et al., Spider 2.0 — https://arxiv.org/abs/2411.07763 ; DOI https://doi.org/10.48550/arXiv.2411.07763	v1 2024-11-12; v2 2025-03-17; ICLR 2025 Oral	2026-10-10	632 enterprise-style SQL workflow problems; benchmark, not production SLA	Empirical benchmark 3
Long-context models may use relevant information inconsistently depending on context position	Liu et al., Lost in the Middle — https://aclanthology.org/2024.tacl-1.9/ ; preprint https://arxiv.org/abs/2307.03172	TACL 2024; original preprint 2023	2026-10-10	Multi-document QA and key-value retrieval experiments	Peer-reviewed empirical evaluation 4
Retrieval augmentation improves some factual QA but does not eliminate unreliable answers	Yang et al., CRAG — https://arxiv.org/abs/2406.04744	2024-06-07 preprint	2026-10-10	4,409 factual QA pairs; benchmark domains and mock retrieval APIs	Empirical benchmark 5

Contradictions and limits

“Add AI after fixing data” is too broad. A company can spend years chasing globally “clean” data and still not improve the readiness of a specific decision. The better scope is the critical lineage for the target metric. This is a recommendation derived from dependency/lineage evidence, not a published causal result. 15

“Exactly-once solves duplicates” is too broad. BigQuery exactly-once semantics concern write delivery through a particular stream mechanism; they do not define a business transaction’s identity or prevent later join fanout. 1

“dbt tests mean the data are trustworthy” is too broad. The built-in tests check concrete relational properties, and model contracts check declared structure. Neither establishes that every expected event arrived or that Finance’s chosen metric definition is correct. 8

“Fresh means complete” is false as a general inference. dbt’s freshness mechanism measures configured timestamp age, while completeness requires separate evidence such as controls, close watermarks or sequence reconciliation. 9

“RAG solves semantic inconsistency” is also too broad. Retrieval can expose the correct contract, but long-context and RAG evaluations show that model use of retrieved/contextual evidence remains imperfect. 4

“The LLM will catch bad data” is unsafe as a control strategy. Some defects are statistically conspicuous, but source omissions can be invisible from the warehouse alone, while semantic disagreements can generate multiple internally consistent answers. An AI can assist diagnosis only when the system supplies independent evidence against which the observed data can be checked.

Where the advice can fail

The full gate can be unnecessarily expensive for low-consequence exploratory work. A product analyst asking “roughly which channels moved yesterday?” may accept provisional data with an explicit warning. A Finance close, board KPI or customer billing analysis deserves materially stronger completeness and semantic evidence. The gate therefore needs risk-based severity, not one universal threshold.

Source reconciliation can also create false confidence. If the source control itself is wrong, matching the source proves consistency with that source—not correspondence with external reality. For material financial metrics, genuinely independent controls or ledger reconciliation are stronger than controls generated by the same buggy transformation.

Lineage can be complete syntactically but incomplete semantically. A graph may show that revenue came from amount, yet not encode whether the amount is gross, tax-inclusive, refunded, FX-converted or recognized under the appropriate accounting rule.

Likewise, an LLM may produce the correct answer for the wrong reason. End-result equality alone is therefore a weak evaluation for sensitive workflows; retain the executed query, source/version references and intermediate controls.

Inaccessible sources

Two potentially relevant primary sources were not substantively relied upon because the research browser could not inspect them adequately:

CIDR 2026, “Text-to-SQL Benchmarks are Broken: An In-Depth Analysis…”
Direct source attempted: https://www.vldb.org/cidrdb/papers/2026/p5-jin.pdf
The direct PDF fetch returned an internal retrieval error. Search metadata suggested it contains an analysis of reference-answer errors in text-to-SQL benchmarks, but I did not use the reported numerical findings because the primary paper could not be inspected directly.

Official EUR-Lex GDPR text, Regulation (EU) 2016/679
Direct source attempted: https://eur-lex.europa.eu/eli/reg/2016/679/oj/eng
The fetched page was blocked by a JavaScript/robot check. I therefore did not build detailed Czech-versus-US legal conclusions on uninspected text. 22

Czech and US relevance

I found no reason to make the core technical readiness workflow different for a Czech organization and a US organization. Event identity, reconciliation, grain, schema contracts, semantic versioning, lineage and reproducible evaluation are engineering concerns independent of country.

The meaningful jurisdictional difference is governance around who may access which data and for what purpose, particularly when personal or regulated data are involved. Because this dossier is about analytical correctness rather than legal compliance—and the authoritative GDPR text could not be inspected in the research environment—I would keep the article-level claim modest: enforce permissions at the data platform boundary and obtain jurisdiction-specific legal review where the assistant accesses regulated/personal data. BigQuery’s documented row-level/column-level mechanisms illustrate the technical pattern; NIST provides a US-origin security-control framework but should not be presented as a general US privacy law. 23

Practical decision rules and reusable angles

The practitioner decision rule

For a small data team, I would implement a two-mode assistant rather than postponing AI until every upstream problem disappears.

Diagnosis mode may run when a quality gate is failing. It can inspect approved raw metadata, batch IDs, source controls, lineage and quality results; generate read-only diagnostic SQL; identify candidate failure domains; and report what evidence is missing. It must not present the business metric as trustworthy.

Interpretation mode is unlocked only after the relevant hard gates pass. It may then answer “why did it change?”, decompose contributors and explain the result using the pinned semantic definition and snapshot.

That split is important. It allows AI to be useful before data are perfect without allowing AI fluency to launder bad data into an authoritative-looking conclusion.

A compact answer-readiness contract

For each material answer, preserve something logically equivalent to:

decision:
  question: "Why did gross successful payment value change Sep vs Aug 2026?"
  owner: "Finance Data"
  requested_at: "2026-10-10T..."

metric:
  name: "gross_successful_payment_value"
  semantic_version: "v1"
  grain: "unique successful payment event"
  unit: "USD"
  event_time_basis: "UTC calendar month"

data:
  warehouse_snapshot_or_as_of: "..."
  source_control_status: "PASS"
  unique_event_status: "PASS"
  schema_contract_status: "PASS"
  freshness_status: "PASS"
  completeness_status: "PASS"

lineage:
  critical_sources: [...]
  transformation_versions: [...]
  run_ids: [...]

access:
  principal: "..."
  role: "read-only analytics"
  policy_scope: "..."

execution:
  query_hash: "..."
  reference_check: "PASS"

answer:
  factual_result: "Aug $300; Sep $420; +$120; +40%"
  status: "trusted | provisional | abstained"
  evidence_refs: [...]

That is much closer to an auditable analytical product than a transcript containing only a question and a fluent answer.

Candidate article angles

Angle	Core thesis	Distinctive evidence/asset
Green pipeline, wrong number	Successful jobs and recent timestamps do not prove metric integrity	The replay + late-event fixture: row count passes while uniqueness/control totals fail
AI cannot repair a metric nobody has defined	Semantic ambiguity is not a row-quality problem	Active-customer/revenue definition contract
Build a trust gate, not a perfect warehouse	Scope quality controls to the decision’s critical lineage	Minimal readiness workflow
Freshness is not completeness	MAX(timestamp) can look healthy while a closed period is incomplete	Synthetic 23:58 replay masking the missing event
Lineage is a debugger, not decoration	Lineage’s highest-value AI use is narrowing diagnosis	OpenLineage direct/indirect JOIN/FILTER/AGGREGATION relationships 15
Benchmark scores are not your SLA	Text-to-SQL/RAG capability must be evaluated against the actual analytical workflow	Spider 2.0, CRAG, deterministic analyst baseline 3
Let AI diagnose bad data before you let it explain business	Diagnosis mode and interpretation mode should have different gates	Quarantine/abstention protocol
A fluent explanation is the last step, not the control	Deterministic evidence should precede narrative generation	End-to-end scorecard and answer-readiness record

Useful reusable assets

The strongest portable assets from this research are the defect/detection matrix, the answer-readiness gate, the executed SQLite late-event/replay/fanout fixture, the evaluation scorecard, and the clarify/abstain/review policy. None requires a particular model provider.

A useful production extension would be an incident bundle automatically produced whenever a hard gate fails:

metric + version
affected period
quality test and expected/observed value
source/batch/run IDs
critical lineage
raw evidence location
query/transformation version
current publication status
owner
recovery decision
restatement status

That bundle can be consumed both by engineers and by an AI diagnostic assistant without giving the model authority to modify the production system.

Freshness triggers for this dossier

This evidence dossier should be revisited when any of the following occur:

Trigger	Why it matters
NIST publishes the revision replacing/currently updating AI RMF 1.0	Current NIST site states the framework is being revised; evaluation guidance may change. 21
dbt materially changes contracts, tests, Semantic Layer or model-version behavior	These are implementation examples used in the workflow. 24
OpenLineage changes its object/column-lineage specification	The diagnosis design relies on current lineage semantics. 12
A warehouse changes ingestion/deduplication or IAM behavior	At-least/exactly-once and permission assumptions are provider/version dependent. 1
New enterprise text-to-SQL evaluations materially change capability evidence	Current Spider 2.0 evidence should not be treated as timeless. 3
New RAG/long-context evidence materially changes source-fidelity expectations	Current evidence shows retrieval/context use remains an independent reliability layer. 4
An article makes substantive EU/Czech or US legal claims	A fresh jurisdiction-specific legal-source review is required; this dossier intentionally does not establish a compliance opinion.

The strategic conclusion is narrower than “bad data makes bad AI” and more actionable:

AI readiness for analytics is the ability to prove what a number means, where it came from, whether the relevant period is complete, whether its grain survived transformation, which version was used, who may see it, and whether the same evidence can reproduce the answer.

Once those conditions are observable, AI becomes an additional retrieval, query-generation and interpretation layer that can be evaluated. Before they are observable, AI mostly adds another place for uncertainty to hide.

Portable citation index

1. Introduction to the Storage Write API (gRPC)  |  BigQuery  |  Google Cloud Documentation
2. “Everyone wants to do the model work, not the data work”: Data Cascades in High-Stakes AI
3. [2411.07763] Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
4. Lost in the Middle: How Language Models Use Long …
5. CRAG – Comprehensive RAG Benchmark
6. NIST SI Resource Center - AIRC
7. “Everyone wants to do the model work, not the data work”: Data Cascades in High-Stakes AI
8. Add data tests to your DAG | dbt Developer Hub
9. freshness | dbt Developer Hub
10. Semantic models | dbt Developer Hub
11. Semantic models | dbt Developer Hub
12. Object Model | OpenLineage
13. papers.neurips.cc
14. Model contracts | dbt Developer Hub
15. Column Level Lineage Dataset Facet | OpenLineage
16. Introduction to BigQuery row-level security  |  Google Cloud Documentation
17. BigQuery IAM roles and permissions  |  Google Cloud Documentation
18. SP 800-53 Rev. 5, Security and Privacy Controls for …
19. airc.nist.gov
20. Model versions | dbt Developer Hub
21. AI Risk Management Framework | NIST
22. eur-lex.europa.eu
23. Introduction to BigQuery row-level security  |  Google Cloud Documentation
24. Semantic models | dbt Developer Hub

Import verification and asset availability

Captured from genuine ChatGPT Deep Research with snapshot 2026-10-10; completed in 9 minutes. Retrieved 2026-10-10. Original prompt and canonical conversation are retained on this run. Complete synthetic code/examples are inline in this report; no external sandbox asset download is required or claimed. Embedded examples and report statements about execution have not been independently run or verified during import. Delivery is not independent validation of source claims, dates or applicability. Evidence remains Captured.

Data Quality Before Adding AI: Evidence Dossier for a Decision-Maker

Research snapshot: 2026-10-10, Europe/Prague
Decision supported: Whether to fix data, constrain an AI analytics pilot, or postpone it
Working environment: Small professional data team; ingestion pipelines → cloud analytical warehouse → dbt-style transformations/semantic definitions → AI analytical assistant
Concrete decision used throughout: “Why did monthly revenue or active-customer count change?”
Evidence boundary: Public primary sources, official/versioned documentation, published evaluation evidence, and synthetic fixtures only. No private company data or production systems were accessed.

Decision answer and key uncertainties

Bottom line

Do not wait for all data to become “perfect” before adding AI. But do not let an AI assistant decide whether the data underlying a material metric is trustworthy.

For monthly revenue or active-customer explanations, the minimum sensible architecture is:

authoritative business definition → deterministic data-quality gates → versioned lineage and permissions → deterministic query/calculation → AI interpretation → human judgment where needed

The most dangerous defects are not necessarily spectacular pipeline failures. They are defects that leave a plausible, executable, well-formatted number: missing events partly offset by duplicates, join fan-out, stale-but-reasonable data, a silent metric-definition change, or an AI query that selects the wrong valid table. Those failures can survive syntax checking and produce fluent explanations.

This distinction matters because current evidence does not support treating model capability as equivalent to analytical-system reliability. Spider 2.0 contains 632 enterprise-like text-to-SQL workflow problems, often over databases with more than 1,000 columns; its published o1-preview-based agent solved 21.3% of tasks, versus much higher results on older benchmarks. 1 A much newer Data Agent Benchmark revision, posted October 7, 2026, reports 104 questions across 17 datasets and four database systems, with its best evaluated agent at 57% pass@1. 2 These are capability benchmarks, not estimates of failure rates in your production workflow. Moreover, the evaluation artifacts themselves can be wrong: an ACL study found question and gold-SQL noise in BIRD, and a 2026 audit reported annotation errors in 52.8% of the audited BIRD Mini-Dev examples and 62.8% of the Spider 2.0-Snow examples for which gold queries were publicly available. 3

So the practical decision is not:

“Is our data good enough for AI?”

It is:

“For this decision, can we establish enough independent evidence that the metric, source, transformations and permissions are fit before AI interprets the result?”

My recommendation is therefore a staged AI pilot behind an evidence gate, unless one of three blocking conditions exists:

Decision state	What it means
Proceed with a gated pilot	The metric definition and grain are authoritative and versioned; the period is demonstrably complete enough; key reconciliation, duplication, schema and grain checks pass; the relevant lineage path is known; assistant permissions are tested; and the deterministic numerical result is reproducible.
Constrain the pilot	Data is usable for some questions but not others, or the period is still open. Let the assistant summarize validated evidence, surface test failures and request clarification, but prevent it from issuing an unqualified “why” explanation.
Postpone this use case	The team cannot establish the authoritative metric definition, cannot independently assess source completeness, has material unexplained reconciliation differences, lacks enough lineage to isolate the affected path, or cannot prove permission enforcement.

This is a decision-specific fitness standard, not a recommendation to remediate the whole warehouse.

What is actually uncertain

There is little strong empirical literature measuring an effect such as “5% missing warehouse events cause X% more hallucinations in an AI analyst.” The strongest evidence relevant here comes from three different categories that should not be conflated:

Empirical research shows that complex data-agent and text-to-SQL workflows remain fallible and that benchmark labels themselves can be noisy. 1

Provider and standard documentation establishes what concrete controls can observe: freshness thresholds, semantic entities and grain, row/column permissions, message-redelivery semantics, lineage run IDs, code versions and field transformations. 4

The defect-to-risk mapping in this dossier is an engineering interpretation of those mechanisms. It is not presented as measured causality or as a universal effect size.

A further semantic warning is important: for this use case, “explain why revenue changed” should normally mean account for the arithmetic movement—for example price, volume, customer, product, FX or refund components. It should not silently become a causal claim such as “the campaign caused revenue growth.” Descriptive warehouse data alone generally does not establish such causality.

Evidence synthesis around the decision

The defects that matter

A useful mental model is to separate four questions:

1. Did the world get captured correctly?
2. Did the data platform preserve and transform it correctly?
3. Are we asking the intended business question over the intended version of that data?
4. Did the AI system retrieve, calculate and describe that evidence correctly?

A model can fail even with perfect data. Perfect model reasoning cannot recover events that never reached the observable system.

The table below is a decision analysis rather than a measured ranking of defect frequencies.

Defect class	How it can corrupt monthly revenue / active customers	What can establish it deterministically	What AI can reasonably help with	What AI cannot establish from the corrupted dataset alone
Upstream capture defect	Transactions or activity never recorded; revenue/customers understated	Source-system control totals, source audit log, event-ID manifest, independent accounting/billing balance	Notice discrepancies when an independent control is supplied; investigate likely affected sources	Prove that an event that left no trace ever occurred
Late or missing events	A closed-looking month is incomplete; comparisons move as events arrive	Watermarks, expected partitions/files, event-time vs ingest-time distribution, source-vs-warehouse reconciliation	Identify unusual lag patterns and summarize affected periods	Know completeness from MAX(loaded_at) alone
Duplicate / replayed ingestion	Additive revenue inflated; counts may or may not inflate depending on COUNT(*) vs COUNT(DISTINCT ...)	Business/event-key uniqueness, idempotency tests, source-vs-target counts and amounts	Discover repeated keys and propose deduplication logic	Decide which repeated records are legitimate without business semantics
Schema drift	Values move column, become null, change type or are mapped to stale fields	Schema contract/diff, required-column and type checks	Suggest mapping changes and explain schema diffs	Determine intended business semantics solely from a renamed field
Transformation / grain defect	Join fan-out, incorrect many-to-many join, double aggregation or wrong temporal join yields plausible wrong totals	Primary-grain tests, relationship/cardinality checks, conservation/reconciliation checks, known-answer fixtures	Inspect SQL, identify suspicious joins, suggest tests	Prove that executable SQL has the intended business grain
Temporal freshness	Assistant answers with yesterday’s or last week’s state as though current	Explicit freshness SLA and snapshot/as-of timestamp	Explain a red freshness signal	Equate a fresh timestamp with complete data
Semantic-definition change	“Revenue” changes from bookings to recognized revenue, refund timing changes, “active” goes from 30 to 28 days, etc.	Versioned metric contract and effective dates	Compare definitions/changelogs and flag differences	Decide which definition management intended
Retrieval / SQL / model error	Wrong table, wrong metric version, wrong date condition, wrong arithmetic, unsupported narrative	Reference queries, result comparison, version/source IDs, deterministic computation and tests	Query planning, SQL generation, interpretation	Make fluent prose evidence of correctness
Permission or source-fidelity failure	Assistant sees rows/columns the user should not see, or explanation cites a different artifact/version than the calculation used	Execution under real assistant/user identity, row/column access tests, query/run logs, source/result hashes	Report the policy outcome and attach evidence	Replace warehouse authorization with a prompt instruction

Two operational lessons follow.

First, freshness is not completeness. dbt’s source-freshness mechanism evaluates source data against freshness criteria/SLA, and current dbt documentation explicitly notes that ordinary dbt build does not automatically include source-freshness checks; the team must configure them in the job. 5 A table can therefore be recently updated while still missing a partition, tenant, event type or subset. “Fresh” answers one question—how recently something arrived—not whether everything expected arrived.

Second, counts alone are weak reconciliation evidence. One missing event plus one replayed event can leave row count unchanged. A better gate combines distinct business keys, amount/control totals, expected partitions and—where feasible—source-side manifests.

This is also why declaring data assumptions explicitly is more defensible than asking an LLM to intuit them. Deequ was developed to express assumptions such as missingness and categorical constraints declaratively and automatically validate them at scale; its authors report production use cases at Amazon. 6 That research concerned ML data-quality validation rather than an LLM analytical assistant, but the design principle transfers directly: known invariants should be tests, not prompts.

Grain and semantics should come before the model

Before asking “why did revenue change?”, the team needs a contract resembling:

recognized_net_revenue_eur v3
Grain: one row per recognized invoice line
Time dimension: service/recognition month
Formula: gross recognized amount − applicable refunds/credits
Currency: EUR after specified FX treatment
Inclusion/exclusion rules: explicit
Effective definition version: explicit

For “active customers” the corresponding unresolved choices are often even more consequential: natural person vs account vs organization; event qualifying as activity; rolling-window length; timezone; suspended users; identity merges; anonymous activity; and whether the measure is a point-in-time state or “had activity during period.”

Modern semantic-layer tooling can encode part of this structure. dbt’s current Semantic Layer documentation makes entities, dimensions and metrics explicit, and its example specifically declares the fact-table grain as one row per order; semantic models map to dbt SQL models and define entities used for joining. 7

That is useful infrastructure, but it is not an oracle. A semantic layer can consistently propagate a wrong definition. What matters is ownership, versioning and effective dates as much as centralization.

Lineage is a diagnostic index, not proof of truth

Lineage is high leverage because it turns:

“Why is this number wrong?”

into something closer to:

“Which source, run and transformation capable of influencing this field changed between the two snapshots?”

OpenLineage distinguishes runtime events from design-time job/dataset metadata and can represent input/output datasets, run identifiers, source-code location/version and SQL-related metadata. 8 Its column-lineage facet can distinguish direct transformations and aggregations from indirect dependencies such as joins, filters, grouping, windows and conditionals. 9

For a revenue discrepancy, that enables a useful narrowing strategy:

revenue metric → fact revenue column → transforming model/run → join/filter dependencies → staging datasets → ingestion/source

That is materially better than scanning every data-quality dashboard in the warehouse.

But lineage itself does not tell you whether the original source was complete or whether finance’s definition is correct. It is a map of derivation, not a ground truth certificate. That limitation should be explicit.

Replay is not an exotic edge case

Duplicate protection deserves to be a standard control in many ingestion architectures because common messaging systems explicitly expose at-least-once behavior. AWS documents that standard SQS delivery can result in the same message being received again and recommends idempotent processing. 10 Google Pub/Sub likewise documents at-least-once delivery as the default, notes that redelivery can occur even after acknowledgment, and recommends idempotent subscribers; exactly-once delivery is an optional capability rather than the default behavior described there. 11

This does not imply that every warehouse ingestion pipeline contains duplicates. It establishes that replay is an expected design condition in representative, widely used ingestion infrastructure, so additive metrics should not depend on wishful uniqueness.

Permissions belong below the AI layer

For data access, “the system prompt says not to reveal PII” is the wrong control boundary.

A warehouse should evaluate the assistant under the actual principal whose entitlements are intended to apply. BigQuery, as one representative implementation, supports row-level policies that filter rows according to qualifying users and column-level policy tags checked when column data is queried. 12

There is a subtle transformation risk as well: BigQuery documents that when query results are copied into a new table, column policy tags are not automatically propagated in most cases. 13 That is provider-specific, but it illustrates the broader reason to test permissions at the final assistant-accessible object rather than infer them from the source table.

For Czech/EU teams, this intersects with existing GDPR obligations when personal data is involved. Article 5 requires personal data to be adequate/relevant/limited to what is necessary and accurate/kept up to date where necessary. 14 That makes indiscriminately feeding raw customer-level context to an assistant particularly hard to defend when an aggregate or pseudonymized representation is sufficient. This does not materially change the technical diagnosis between Prague and the United States; it changes the governance pressure around unnecessary personal-data exposure.

Reliable system design and analyst baseline

Separate five different capabilities

An “AI analyst” is usually several systems hidden behind one chat box. Treating them separately makes failures diagnosable.

Layer	Appropriate responsibility	Evidence required before trusting it
Retrieval / semantic resolution	Find the authorized metric, metadata, schema and documentation	Exact object/version retrieved; ambiguity explicitly resolved
SQL or code generation/execution	Produce and run the query	Executed query preserved; database/runtime errors surfaced; output compared with reference cases
Deterministic calculation	Percent changes, decompositions, reconciliations	Calculation in SQL/code, not free-form mental arithmetic; units visible
Model interpretation	Turn validated figures and diagnostic evidence into a readable explanation	Every material factual statement traceable to the evidence envelope
Human judgment	Decide materiality, intended semantics, whether descriptive attribution is enough, whether causality is being implied, whether remediation is justified	Named owner/reviewer and review record for material decisions

The LLM is most valuable toward the right-hand side: querying, navigation, hypothesis generation and explanation. The farther left the responsibility moves toward defining ground truth, the weaker the case for probabilistic control.

Current data-agent benchmarks support caution rather than fatalism. Spider 2.0 demonstrates that enterprise-like database environments involving extensive metadata and project documentation remain much harder than traditional text-to-SQL tasks. 1 DAB v3, revised only three days before this research snapshot, similarly reports substantial remaining failure on end-to-end data questions. 2 Neither result says a carefully constrained assistant cannot be valuable. They argue against making “the model looked impressive in a demo” a release criterion.

The evidence envelope I would require

For every material explanation, the assistant should receive—or the system should retain—an envelope along these lines:

{
  "decision": "explain_monthly_revenue_change",
  "metric": {
    "name": "recognized_net_revenue_eur",
    "definition_version": "rev_v3",
    "grain": "one row per recognized invoice line",
    "time_dimension": "recognition_month",
    "currency": "EUR"
  },
  "snapshot": {
    "as_of_utc": "2026-11-03T08:00:00Z",
    "period_status": "closed"
  },
  "source_controls": {
    "expected_event_count": 123456,
    "expected_amount_eur": 9876543.21,
    "reconciliation_status": "PASS"
  },
  "deterministic_tests": {
    "freshness": "PASS",
    "required_partitions": "PASS",
    "business_key_uniqueness": "PASS",
    "schema_contract": "PASS",
    "fact_grain": "PASS",
    "join_cardinality": "PASS",
    "source_to_fact_reconciliation": "PASS"
  },
  "lineage": {
    "pipeline_run_id": "RUN_ID_HERE",
    "transformation_code_version": "GIT_SHA_HERE",
    "source_dataset_versions": [
      "SOURCE_VERSION_HERE"
    ]
  },
  "permissions": {
    "execution_principal": "ai_analytics_assistant",
    "row_column_policy_test": "PASS"
  },
  "query": {
    "sql_or_query_id": "QUERY_ID_HERE",
    "sql_hash": "sha256:...",
    "result_hash": "sha256:..."
  },
  "decision_gate": "PASS"
}

The values above are illustrative placeholders, not observed production data.

OpenLineage’s object model is well aligned with retaining run identity, source-code version and input/output dataset evidence. 8 The point is not that OpenLineage is mandatory; it is that “which exact inputs and code produced this answer?” must have a machine-readable answer.

Minimum deterministic controls before interpretation

For the selected monthly-metric workflow, I would consider these the minimal sufficient set:

Control	Why it is blocking
Metric-definition/version check	Without it, the question itself is under-specified.
Grain and business-key test	Prevents many double-count/fan-out failures that still produce valid SQL.
Required schema/type contract	Prevents silent nulls/remapping after upstream changes.
Period completeness / expected-partition check	A fresh table can still be incomplete.
Source-to-staging and fact reconciliation	Gives an independent check that amounts/counts survived the path.
Duplicate/replay test	Required where business/event uniqueness is expected.
Join-cardinality check for metric-critical joins	Protects additive measures from fan-out.
Lineage/run/code-version capture	Makes diagnosis and reproduction tractable.
Real-principal permission test	Prevents the assistant layer from bypassing intended row/column restrictions.
Deterministic final calculation	Leaves the LLM to interpret numbers instead of inventing them.

This is not a mandate to run hundreds of tests on unrelated columns. A defect in an unused marketing-enrichment field is irrelevant to a finance explanation unless lineage shows that it influences the selected metric or explanatory dimension.

That gives a useful prioritization rule:

Fix defects on the decision’s dependency path in proportion to their ability to change the decision, not in proportion to the number of red dots on the data-quality dashboard.

What should force clarification, abstention or human review

Clarification should be mandatory when there are multiple legitimate interpretations of the question: gross vs net revenue, booked vs recognized revenue, local vs reporting currency, calendar vs fiscal month, natural-person vs account-level active customer, or the intended comparison period.

Abstention should be mandatory when a blocking deterministic gate fails, the metric definition/version is unavailable, a period expected to be closed cannot be reconciled, the source snapshot cannot be identified, or the caller does not have the required permission.

Human review should be mandatory for a material result after a semantic-definition migration; for a novel or unreconciled defect; when a narrative crosses from arithmetic attribution into causal explanation; or when remediation requires changing production data rather than merely rerunning validated transformations.

A good assistant response to a failed gate is therefore not a clever guess. It is something like:

“I cannot reliably explain the October movement yet. The warehouse fact is €70 above the source control total and the customer dimension violates its expected key grain. I can show those discrepancies and the affected lineage path, but I will not attribute the business movement until they are resolved.”

That is a feature, not a model failure.

Evaluate against the analyst doing the same job

The correct baseline is not “AI versus no answer.” It is a competent analyst with the same available data, definitions and permissions.

I would run a local evaluation with a deliberately small but stratified case set, for example:

• clean, unambiguous metric questions;
• intentionally ambiguous terminology;
• missing/late events;
• replayed events;
• schema changes;
• bad joins/grain;
• metric-definition version changes;
• stale snapshots;
• inaccessible dimensions/rows;
• retrieval of an obsolete but valid-looking metric.

The number of cases is a local design decision; forty or fifty well-chosen cases is only an example, not an industry benchmark.

Score both analyst and AI-assisted workflow on:

Dimension	Observable measure
Factual/query correctness	Does the validated result equal the reference result or an explicitly permitted tolerance?
Source/version fidelity	Did it use exactly the authorized metric, snapshot and transformation version?
Ambiguity handling	Did it clarify or abstain when multiple definitions were legitimate?
Permission compliance	Did it access or reveal anything outside the test principal’s entitlement?
Reproducibility	Can the same snapshot/code/query reproduce the material figures?
Failure recovery	When a gate fails, does it localize the issue rather than fabricate an answer?
Review effort	Analyst minutes required per accepted answer
Latency	Time from question to accepted answer
Cost	Warehouse + retrieval/model + review cost per accepted answer

Do not make generated-SQL string equality the main criterion. Multiple SQL formulations can be equivalent; the material criterion is the validated result and its provenance.

Worked synthetic diagnosis

Why one plausible number can conceal three defects

This fixture is deliberately small. It illustrates why an AI assistant should not interpret the final fact table before deterministic diagnostics have run.

Assume the authoritative monthly metric is:

R_m = \sum_{i \in m} \text{net\_revenue\_eur}_i

at one row per invoice line, assigned to the service/recognition month.

The synthetic authoritative values are:

Period	Authoritative revenue	Change from September
September	€600	—
October	€540	((540-600)/600=-10.00%)

Now inject three defects:

1. the €100 October line o3 never arrives;
2. the €80 line o1 is replayed once;
3. customer C2 exists twice in a dimension, faning out the €90 line during a join.

The raw October ingestion therefore totals:

540 - 100 + 80 = €520

but still contains five physical rows: four legitimate unique events plus one duplicate. A naive row-count check therefore passes.

The bad fact after the duplicate dimension join totals:

520 + 90 = €610

and now appears to show:

(610-600)/600 = +1.67\%

So an ungated assistant can quite plausibly say:

“October revenue increased 1.7%.”

The correct source-level movement is −10.0%.

The erroneous transformation has reversed the direction of the business story.

Copyable synthetic SQL fixture

The following is a self-contained synthetic fixture written using broadly portable SQL constructs. Minor type syntax may need adaptation by warehouse. This exact SQL was not executed against any production warehouse during this research; the expected values below were arithmetically cross-checked from the fixture rows.

/*
Synthetic diagnostic fixture.

Authoritative monthly revenue:
  September = EUR 600
  October   = EUR 540

Injected October defects:
  1. Missing source event o3 worth EUR 100
  2. Replayed ingestion event o1 worth EUR 80
  3. Duplicate customer dimension key C2, causing a EUR 90 join fan-out

Expected diagnostics:
  authoritative_oct_revenue_eur = 540
  raw_oct_revenue_eur           = 520
  bad_fact_oct_revenue_eur      = 610

  authoritative_oct_keys = 5
  raw_oct_rows            = 5   -- misleadingly "correct"
  raw_oct_distinct_keys   = 4
  raw_duplicate_keys      = 1
  missing_source_keys     = 1
  duplicate_dim_keys      = 1
*/

WITH source_authoritative AS (

    -- September authoritative invoice lines: total EUR 600
    SELECT '2026-09' AS revenue_month, 's1' AS line_id, 'C1' AS customer_id, 100.00 AS net_revenue_eur
    UNION ALL
    SELECT '2026-09', 's2', 'C2', 110.00
    UNION ALL
    SELECT '2026-09', 's3', 'C3', 120.00
    UNION ALL
    SELECT '2026-09', 's4', 'C4', 130.00
    UNION ALL
    SELECT '2026-09', 's5', 'C5', 140.00

    UNION ALL

    -- October authoritative invoice lines: total EUR 540
    SELECT '2026-10', 'o1', 'C1',  80.00
    UNION ALL
    SELECT '2026-10', 'o2', 'C2',  90.00
    UNION ALL
    SELECT '2026-10', 'o3', 'C3', 100.00
    UNION ALL
    SELECT '2026-10', 'o4', 'C4', 120.00
    UNION ALL
    SELECT '2026-10', 'o5', 'C5', 150.00
),

raw_ingested AS (

    -- o3 is missing.
    SELECT 'ingest_001' AS ingest_id,
           '2026-10' AS revenue_month,
           'o1' AS line_id,
           'C1' AS customer_id,
           80.00 AS net_revenue_eur,
           '2026-11-01T01:00:00Z' AS ingested_at

    UNION ALL
    SELECT 'ingest_002', '2026-10', 'o2', 'C2',  90.00, '2026-11-01T01:00:01Z'
    UNION ALL
    SELECT 'ingest_004', '2026-10', 'o4', 'C4', 120.00, '2026-11-01T01:00:03Z'
    UNION ALL
    SELECT 'ingest_005', '2026-10', 'o5', 'C5', 150.00, '2026-11-01T01:00:04Z'

    -- Replay of o1.
    UNION ALL
    SELECT 'ingest_006', '2026-10', 'o1', 'C1',  80.00, '2026-11-01T01:05:00Z'
),

customer_dim AS (

    SELECT 'C1' AS customer_id, 'segment_a' AS segment
    UNION ALL
    SELECT 'C2', 'segment_b'

    -- Erroneous second current row for C2.
    UNION ALL
    SELECT 'C2', 'segment_b_duplicate'

    UNION ALL
    SELECT 'C3', 'segment_c'
    UNION ALL
    SELECT 'C4', 'segment_d'
    UNION ALL
    SELECT 'C5', 'segment_e'
),

bad_fact AS (
    /*
    This intentionally demonstrates the bad transformation:
    neither ingestion replay nor dimension-key duplication is removed.
    */
    SELECT
        r.revenue_month,
        r.line_id,
        r.customer_id,
        r.net_revenue_eur,
        d.segment
    FROM raw_ingested r
    LEFT JOIN customer_dim d
      ON r.customer_id = d.customer_id
),

authoritative_oct AS (
    SELECT
        COUNT(*) AS authoritative_oct_keys,
        SUM(net_revenue_eur) AS authoritative_oct_revenue_eur
    FROM source_authoritative
    WHERE revenue_month = '2026-10'
),

raw_oct AS (
    SELECT
        COUNT(*) AS raw_oct_rows,
        COUNT(DISTINCT line_id) AS raw_oct_distinct_keys,
        SUM(net_revenue_eur) AS raw_oct_revenue_eur
    FROM raw_ingested
    WHERE revenue_month = '2026-10'
),

bad_fact_oct AS (
    SELECT
        COUNT(*) AS bad_fact_oct_rows,
        SUM(net_revenue_eur) AS bad_fact_oct_revenue_eur
    FROM bad_fact
    WHERE revenue_month = '2026-10'
),

raw_duplicate_key_count AS (
    SELECT COUNT(*) AS raw_duplicate_keys
    FROM (
        SELECT line_id
        FROM raw_ingested
        GROUP BY line_id
        HAVING COUNT(*) > 1
    ) x
),

missing_source_key_count AS (
    SELECT COUNT(*) AS missing_source_keys
    FROM source_authoritative s
    WHERE s.revenue_month = '2026-10'
      AND NOT EXISTS (
          SELECT 1
          FROM raw_ingested r
          WHERE r.line_id = s.line_id
      )
),

duplicate_dim_key_count AS (
    SELECT COUNT(*) AS duplicate_dim_keys
    FROM (
        SELECT customer_id
        FROM customer_dim
        GROUP BY customer_id
        HAVING COUNT(*) > 1
    ) x
)

SELECT
    a.authoritative_oct_keys,
    a.authoritative_oct_revenue_eur,

    r.raw_oct_rows,
    r.raw_oct_distinct_keys,
    r.raw_oct_revenue_eur,

    b.bad_fact_oct_rows,
    b.bad_fact_oct_revenue_eur,

    d.raw_duplicate_keys,
    m.missing_source_keys,
    c.duplicate_dim_keys,

    /* Reconciliation deltas */
    r.raw_oct_revenue_eur
        - a.authoritative_oct_revenue_eur
        AS raw_vs_authoritative_delta_eur,

    b.bad_fact_oct_revenue_eur
        - a.authoritative_oct_revenue_eur
        AS fact_vs_authoritative_delta_eur

FROM authoritative_oct a
CROSS JOIN raw_oct r
CROSS JOIN bad_fact_oct b
CROSS JOIN raw_duplicate_key_count d
CROSS JOIN missing_source_key_count m
CROSS JOIN duplicate_dim_key_count c;

Expected outputs from the fixture are:

Check	Expected result	Diagnostic meaning
Authoritative Oct keys	5	Expected business events
Raw physical rows	5	Misleading pass if used alone
Raw distinct business keys	4	One unique event missing
Duplicate raw keys	1	Replay present
Missing source keys	1	o3 absent
Duplicate dimension keys	1	Join cardinality broken
Authoritative Oct revenue	€540	Control
Raw Oct revenue	€520	−€20 / −3.70% versus source
Bad fact Oct revenue	€610	+€70 / +12.96% versus source
Bad fact versus September	+1.67%	Wrong direction
True October versus September	−10.00%	Correct movement

This example exposes an important failure mode: different data defects can partly cancel each other, so “the total is within 1%” is not sufficient evidence if offsetting errors are plausible.

The same defects affect active-customer counts differently. In this fixture, COUNT(DISTINCT customer_id) would not be inflated by the dimension fan-out or replay, but the missing customer/event could still undercount activity. A COUNT(*)-based activity measure could be affected by all three. This is why controls must derive from the actual metric grain and formula rather than from a generic “data quality score.”

What evidence justifies repair versus abstention

For the synthetic case, the correct response is to abstain first.

A justified repair sequence would be:

1. establish whether o3 exists in the authoritative source or re-extract the affected source partition;
2. identify replay semantics for o1 and apply the defined event/business-key deduplication rule;
3. restore the expected uniqueness/effective-dating rule for customer_dim;
4. rebuild the affected lineage path from a known transformation version;
5. rerun source → staging → fact reconciliation;
6. preserve the run/source/code identifiers;
7. only then allow the assistant to decompose the actual business movement.

An LLM can help investigate steps 1–4, but it should not certify them.

If no independent source-side evidence exists—no event manifest, accounting balance, source aggregate or controlled re-extraction—then an assistant should not announce that the missing event problem is solved merely because warehouse totals now “look normal.”

Decision economics, ownership and release criteria

Compare the viable strategies

Strategy	Advantage	Main weakness	My view
Make all data perfect before AI	Lowest obvious data-quality anxiety	Open-ended scope; fixes unrelated data; delays learning about the actual AI workflow	Usually the wrong target
Keep analyst-only workflow	Known operating model; easy accountability	Manual investigation cost and slower scaling remain	Essential baseline, not necessarily end state
Gated AI + analyst review	Concentrates controls on material dependency paths while learning where AI actually saves work	Requires evaluation harness, ownership and ongoing control maintenance	Recommended
Unconstrained AI over warehouse	Fastest demo and broadest apparent capability	Combines data defects, model errors and permission risk in one opaque surface	Poor choice for material metrics

The recommendation changes if the team cannot state the authoritative metric in the first place. Then the first project is semantic governance, not an AI assistant.

Transparent synthetic economics

The following is deliberately a hypothetical decision model, not measured ROI.

Assumptions:

Input	Synthetic base assumption
Investigations per month (N)	80
Analyst-only time per investigation	45 min
AI-assisted time including human review	20 min
Loaded analyst cost	€90/hour
Control maintenance	6 engineering hours/month
Loaded engineering cost	€100/hour
AI + retrieval + query infrastructure	€400/month
False alarms	5/month
Review cost per false alarm	0.5 analyst hour
One-time control/evaluation build	60 engineering hours = €6,000

Time-value formula:

\text{gross monthly labor value}
=
N
\times
\frac{t_{baseline}-t_{AI}}{60}
\times
C_{analyst}

Base case:

80
\times
\frac{45-20}{60}
\times €90
=
€3,000 / month

Recurring control cost:

6h \times €100 = €600/month

False-alarm review cost:

5 \times 0.5h \times €90
=
€225/month

Total recurring incremental costs:

€600 + €400 + €225 = €1,225/month

Synthetic operating benefit:

€3,000 - €1,225
=
€1,775/month

Synthetic simple payback:

€6,000 / €1,775
\approx 3.38\ months

Again: none of these inputs is claimed as a market rate, customer result or expected outcome.

Sensitivity to investigation volume, holding the other assumptions fixed:

Investigations/month	Hours saved/month	Gross labor value	Net after €1,225 recurring cost	Simple payback on €6,000
20	8.33 h	€750	−€475	None
50	20.83 h	€1,875	€650	9.23 months
80	33.33 h	€3,000	€1,775	3.38 months
150	62.50 h	€5,625	€4,400	1.36 months

Review effort is the critical economic variable. If AI-assisted work takes 35 rather than 20 minutes in the 80-investigation case:

80\times\frac{45-35}{60}\times€90
=
€1,200

which is less than the assumed €1,225 recurring cost.

The synthetic operating benefit becomes:

€1,200 - €1,225 = -€25/month

That is why the pilot should measure accepted-answer review time, not token usage or the number of questions asked.

False alarms are a real control cost

A very sensitive data-quality gate can be economically worse than a narrow one if it continuously stops valid analysis.

For each control, track at least:

\text{monthly false-alarm cost}
=
F
\times
t_{review}
\times
C_{reviewer}

and distinguish:

• false positives: control blocks good data;
• true positives with negligible materiality: technically real defect, irrelevant decision impact;
• true material defects: the cases that justify the control.

This strongly favors testing the lineage path and metric invariants rather than trying to detect every oddity in every warehouse table.

Ownership

The assistant should not become the accidental owner of unresolved business semantics.

Responsibility	Suggested owner
Revenue/active-customer definition and materiality	Finance/business metric owner
Source controls and ingestion health	Data platform / source owner
Grain, transformations, dbt tests, reconciliation	Analytics engineering
Lineage completeness	Data platform / analytics engineering
Row/column permissions	Security/data governance
Retrieval, SQL-generation and model evaluation	AI/data product team
Gold/reference cases and accepted-answer review	Analysts + metric owner
Production-data repair	Existing operational owner, not autonomous AI

Proposed go/no-go criteria

These are design choices for this workflow, not universal industry thresholds.

Go to a human-reviewed pilot only when:

• an owner has approved the metric definition, grain and version;
• every material input on the selected lineage path is identifiable;
• closed periods have an explicit completeness/reconciliation control, not just a freshness timestamp;
• no unexplained duplicate business keys exist where uniqueness is required;
• metric-critical dimension relationships meet expected cardinality;
• the exact query/result can be reproduced from a named snapshot and code version;
• the assistant is tested using its actual warehouse identity;
• the evaluation set contains clean and deliberately defective cases;
• the AI-assisted workflow is compared with the analyst baseline on the same cases.

A possible release standard for a small critical pilot could be zero observed permission violations, zero source/version substitutions, and zero material numerical errors that passed the deterministic gate in the release set. Those are deliberately conservative design thresholds, not statistical guarantees about future production behavior.

A business materiality threshold should be supplied by the metric owner. For illustration only, a finance team might block an explanation when an unexplained reconciliation delta exceeds:

\max(€25,000,\;0.5\%\times\text{monthly revenue})

There is no evidence in this dossier that €25,000 or 0.5% is a generally correct threshold; they are examples of how to make materiality explicit.

Proposed stop criteria

Pause the pilot immediately for investigation if:

• unauthorized data is accessed or disclosed;
• a materially wrong numerical conclusion passes all purported gates;
• an answer cites or describes a different metric/source version from the one actually executed;
• a system that is supposed to abstain when a blocking test is red instead gives a confident business explanation;
• a production correction is attempted automatically without the established operational approval path.

A softer review trigger is appropriate when analyst review effort ceases to improve materially over the non-AI baseline. The economics above show why an assistant requiring near-full manual reconstruction can be technically impressive but economically pointless.

Contradictions, failure conditions and regional relevance

“AI can detect bad data” and “AI repeats bad data” are both true

There is no real contradiction once the source of evidence is made explicit.

An AI system can detect a duplicate key if the key definition and data are observable. It can notice a schema diff if previous/current schema metadata are supplied. It can point out that a reconciliation test is red if that test exists.

It cannot infer with certainty that 10,000 upstream events were never emitted when nothing in its evidence establishes the expected 10,000.

This distinction is exactly why declarative validation systems such as Deequ are useful: assumptions become observable constraints rather than model intuition. 6

Fresh data can be wrong; complete data can be old

dbt’s freshness mechanism is explicitly centered on user-defined freshness/SLA information. 5 It should therefore be treated as one signal, not an overall certificate.

A dataset can be:

• recent but missing one region;
• old but fully reconciled for a historical closed period;
• fresh and complete but semantically wrong;
• fresh, complete and semantically valid but duplicated after a downstream join.

The gate should retain those dimensions separately.

Exactly-once ingestion does not eliminate downstream duplication

Some infrastructure supports stronger delivery modes than the default at-least-once semantics documented for representative systems such as Pub/Sub. 11 Even with perfect ingestion deduplication, a transformation can still create double counting through a many-to-many join, slowly changing dimension error or aggregation at the wrong grain.

Therefore “we use exactly-once” is not evidence that the revenue fact has a valid grain.

A semantic layer reduces inconsistency but can institutionalize a mistake

A central metric definition is valuable because consumers can converge on the same entities, dimensions and aggregations. dbt’s current Semantic Layer provides explicit structures for exactly that. 7

But centralization raises the blast radius of a mistaken definition. This argues for metric versioning, effective dates, owners and regression fixtures—not against semantic layers.

Lineage can be incomplete

OpenLineage can encode detailed run, source-code and field-level relationships. 8 Actual lineage coverage still depends on instrumentation. Dynamic SQL, user-defined functions, externally generated jobs or transformations occurring outside instrumented systems can create gaps.

A missing lineage edge should therefore be “unknown dependency,” not “no dependency.”

Benchmarks are useful, but not as production acceptance tests

The newest DAB version changed as recently as October 7, 2026 and now reports 104 queries, 17 datasets and a 57% best pass@1 result. 2 Spider 2.0 is a different workload and reports 21.3% for its cited code-agent configuration. 1

These do not conflict: benchmark task sets, versions, models, harnesses and scoring differ.

More importantly, benchmark-label quality itself is a live issue. Wretblad et al. found substantial question/gold-query noise in BIRD, while the 2026 benchmark audit reports that correcting benchmark annotations can materially change model rankings. 3

So leaderboard position is evidence of capability under a benchmark definition, not evidence that “our October revenue explanation will be correct.”

Provider documentation is not empirical performance evidence

AWS, Google Cloud, dbt and OpenLineage documentation are strong sources for how their mechanisms are specified to behave. They do not measure how often your pipeline fails or demonstrate that adopting the documented feature causes better business outcomes.

Similarly, the Amazon Deequ publication supports declarative data assumptions and automatic checking; it does not measure ROI for an LLM analytics assistant. 6

Czech/EU versus US relevance

The technical readiness standard is essentially the same in both contexts: authoritative metric, grain, reconciliation, lineage, permission enforcement and reproducibility.

The most material difference for this scoped use case is privacy governance when customer-level personal data enters the workflow. In the EU, including Czechia, GDPR Article 5 expressly requires data minimization and accuracy for personal data. 14 That strengthens the case for giving an assistant aggregate or pseudonymized evidence instead of unrestricted customer data where identifiable detail is unnecessary.

The EU AI Act contains data/data-governance requirements for high-risk AI systems in Article 10. 15 It should not be casually treated as a blanket requirement for every internal revenue analytics assistant; classification and applicability depend on the system and use. This dossier is not legal advice.

In the US, NIST’s Generative AI Profile is a voluntary cross-sector risk-management reference rather than a deployment-specific data-quality mandate. NIST describes it as a companion to the AI RMF intended to help organizations incorporate trustworthiness considerations into AI design, development, use and evaluation. 16

So I would not build different data-quality architectures for Prague and New York merely because of geography. I would apply the same technical evidence gate and layer relevant privacy, contractual and regulatory controls on top.

Where the recommendation can fail

The proposed approach is weaker when:

• there is no credible independent source/control against which completeness can be checked;
• the primary problem is inherently unstructured external context rather than warehouse data;
• the metric itself is exploratory and intentionally undefined;
• the decision is low consequence enough that a strict evidence gate costs more than occasional error;
• source systems have inconsistent business semantics that cannot be resolved by the data team;
• an assistant is being used purely for brainstorming rather than as a factual analytical interface;
• the analysis needs causal inference rather than arithmetic decomposition;
• permission systems cannot represent the business policy being requested.

The reverse situation also exists: in a mature, tightly contracted, low-latency data product with strong source controls and simple metrics, some proposed gates will be redundant. “Minimum sufficient” should stay minimum.

The largest unresolved evidence gaps

I found no high-quality public study directly comparing a gated AI analytical assistant with a skilled analyst on the same monthly-revenue-change workflow while injecting source missingness, replay, join fan-out, semantic changes, lineage gaps and permission restrictions. That would be the most decision-relevant experiment.

There is also no defensible universal materiality threshold, acceptable error rate, control count or ROI figure for this workflow. Those need to come from the organization’s own decision cost and pilot observations.

Finally, text-to-SQL and data-agent research is moving very quickly. The DAB paper itself was revised on October 7, 2026, only three days before this snapshot. 2 Any article quoting current frontier scores should therefore preserve benchmark version, evaluation date and harness rather than present a timeless “AI accuracy” number.

No load-bearing source was inaccessible during this research. Where a source also offered a PDF, I relied on its public HTML publication page or HTML paper rendering for the claims used here rather than inferring PDF-only content.

Claim-to-source dossier, publication angles and freshness triggers

Claim-to-source table

The inspected date for every source below is 2026-10-10.

Claim supported	Original source and source date	Direct URL / DOI	Exact evidentiary scope	Evidence type
dbt source freshness is an explicit SLA-oriented check; dbt build does not automatically include it, and job configuration affects whether failure blocks later steps.	dbt Labs, Source freshness, updated 2026-10-06. 5	https://docs.getdbt.com/docs/deploy/source-freshness	Current documented dbt behavior only; does not establish completeness or business correctness.	Versioned provider documentation
Semantic models represent entities, dimensions and metrics, and dbt’s example explicitly declares fact grain as one row per order.	dbt Labs, Quickstart for the dbt Semantic Layer, current 2026 documentation. 7	https://docs.getdbt.com/guides/sl-qs	Describes dbt v1.12+ semantic-model structure; not empirical evidence that semantic definitions are correct.	Provider documentation
Runtime/design lineage can represent jobs, datasets, runs, SQL/source-code metadata and dataset versions.	OpenLineage Object Model, living specification; page publication date not stated. 8	https://openlineage.io/docs/spec/object-model/	Specification of available lineage metadata; does not guarantee instrumentation completeness.	Open standard/specification
Field lineage can distinguish aggregation from indirect dependencies such as joins, filters, groups, windows and conditionals.	OpenLineage Column Level Lineage Dataset Facet, living specification; page publication date not stated. 9	https://openlineage.io/docs/spec/facets/dataset-facets/column_lineage_facet/	Specification of lineage representations, useful for narrowing transformation diagnosis.	Open standard/specification
Explicit data assumptions can be declaratively validated at scale.	Schelter et al., DEEQU – Data quality validation for machine learning pipelines, NeurIPS 2018. 6	https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines	ML-pipeline data validation and reported Amazon production requirements; not an LLM-assistant experiment.	Primary research/company publication
Standard SQS queues may deliver the same message more than once; consumers should be idempotent.	AWS, Amazon SQS at-least-once delivery, living documentation; page publication date not stated. 10	https://docs.aws.amazon.com/AWSSimpleQueueService/latest/SQSDeveloperGuide/standard-queues-at-least-once-delivery.html	SQS Standard delivery semantics only.	Provider documentation
Pub/Sub defaults to at-least-once/no-ordering semantics and may redeliver messages; exactly-once is separately supported.	Google Cloud, Subscription overview, updated 2026-10-09. 11	https://docs.cloud.google.com/pubsub/docs/subscription-overview	Google Pub/Sub subscription behavior; representative ingestion evidence, not a claim about every pipeline.	Provider documentation
BigQuery row-level security filters rows based on user conditions.	Google Cloud, Introduction to BigQuery row-level security, living documentation; page publication date not surfaced. 12	https://docs.cloud.google.com/bigquery/docs/row-level-security-intro	BigQuery-specific authorization mechanism.	Provider documentation
BigQuery checks column policy authorization at query time and combines it with other permissions.	Google Cloud, Introduction to column-level access control, living documentation. 17	https://docs.cloud.google.com/bigquery/docs/column-level-security-intro	BigQuery-specific column security.	Provider documentation
Query-result copies do not automatically inherit BigQuery column policy tags in most cases.	Google Cloud, Restrict access with column-level access control, living documentation. 13	https://docs.cloud.google.com/bigquery/docs/column-level-security	Provider-specific example of permission propagation risk through transformations/copies.	Provider documentation
Spider 2.0 contains 632 enterprise-like workflow tasks, databases often over 1,000 columns, and its cited agent configuration solved 21.3%.	Lei et al., v1 2024-11-12; v2 revised 2025-03-17; ICLR 2025 Oral. 1	https://arxiv.org/abs/2411.07763 ; DOI: https://doi.org/10.48550/arXiv.2411.07763	Enterprise-oriented text-to-SQL capability benchmark; not a production error-rate estimate.	Primary benchmark research
DAB v3 has 104 queries, 17 datasets and four DBMS; best evaluated agent reported 57% pass@1.	Ma et al., v1 2026-03-21; v3 revised 2026-10-07. 2	https://arxiv.org/abs/2603.20576 ; DOI: https://doi.org/10.48550/arXiv.2603.20576	End-to-end data-agent benchmark grounded in studied enterprise workloads; still a benchmark, not the target production workflow.	Primary benchmark/preprint
Noise/errors in questions and gold SQL can undermine text-to-SQL benchmark reliability.	Wretblad et al., ACL 2024, August 2024. 3	https://aclanthology.org/2024.acl-short.34/ ; DOI: https://doi.org/10.18653/v1/2024.acl-short.34	Study of BIRD-Bench noise and corrected-query evaluations.	Peer-reviewed empirical research
A 2026 audit found annotation errors in 52.8% of audited BIRD Mini-Dev examples and 62.8% of 121 Spider 2.0-Snow problems with released gold queries, and reported ranking shifts after corrections.	Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards, 2026, v3 inspected. 18	https://arxiv.org/html/2601.08778v3	Manual/aided benchmark annotation audit. These rates describe the audited benchmark examples, not production AI failure rates.	Primary preprint/audit
NIST’s GenAI Profile is a voluntary cross-sector companion to AI RMF for incorporating trustworthiness into AI lifecycle activities.	NIST AI 600-1, published 2024-07-26; NIST page updated 2026-04-08. 16	https://doi.org/10.6028/NIST.AI.600-1	Governance/risk-management framework, not empirical effectiveness evidence.	Official US government publication
GDPR requires data minimization and accuracy for personal data.	Regulation (EU) 2016/679, adopted 2016-04-27; OJ publication 2016-05-04, Article 5. 14	https://eur-lex.europa.eu/eli/reg/2016/679/oj	Binding EU legal text applicable within its scope; not a generic rule for non-personal business metrics.	Primary legislation
EU AI Act Article 10 contains data/data-governance requirements tied to high-risk AI systems.	Regulation (EU) 2024/1689, 2024, Article 10. 15	https://eur-lex.europa.eu/eli/reg/2024/1689/oj	Conditional regulatory relevance; should not be generalized to every analytics assistant.	Primary legislation

Evidence-strength interpretation

The strongest basis for the core recommendation is not one study saying “do these ten checks.” It is the convergence of several independent facts:

• enterprise data-agent tasks remain difficult even for strong models; 1
• benchmark scores themselves can overstate or misrank capability when evaluation data is wrong; 3
• deterministic data assumptions can be directly encoded and checked; 6
• freshness, grain, permissions and lineage are separately observable system properties rather than things the model must infer; 4
• common ingestion mechanisms can legitimately expose the pipeline to replay, making uniqueness/idempotency a system responsibility rather than an AI reasoning problem. 10

That supports the architectural conclusion: use probabilistic AI on top of validated evidence rather than as the mechanism that validates the evidence.

Candidate distinct article angles

“You do not need perfect data for AI. You need proof that the data is fit for one decision.”
The decision-maker angle. It rejects both “clean everything first” and “LLMs will figure it out,” replacing them with decision-specific fitness and materiality.

“The most dangerous data-quality problem is a plausible number.”
Lead with the synthetic €600 → €610 example: missing data, replay and join fan-out transform a real −10% movement into an apparent +1.7%. Strong practical narrative.

“Your AI analyst needs a pre-flight checklist, not a bigger prompt.”
Focus on deterministic gating: freshness, completeness, uniqueness, grain, reconciliation, lineage and permissions before model interpretation.

“Lineage is how you turn ‘the AI is wrong’ into a bounded engineering problem.”
Explain how field/run/version lineage narrows the search path while emphasizing that lineage is not proof of source truth.

“Why a text-to-SQL leaderboard cannot tell you whether your revenue answer is trustworthy.”
Contrast Spider 2.0/DAB capability evidence with annotation-noise findings and target-workflow evaluation.

“AI should explain the result, not certify the ledger.”
Sharper executive framing for finance/revenue analytics: deterministic systems establish the number; AI accelerates investigation and communication.

Reusable assets from this dossier

The most useful publication or implementation assets are already portable within this dossier:

• the defect → failure → deterministic control → AI limitation matrix;
• the synthetic SQL fixture showing missing event + replay + join fan-out;
• the evidence-envelope JSON for reproducibility and source/version fidelity;
• the analyst-vs-AI evaluation dimensions;
• the transparent ROI formula and sensitivity model;
• the go/constrain/postpone decision model.

A particularly useful visual adaptation would be a pipeline with two parallel failure surfaces:

Source → Ingestion → Warehouse → Transformation → Semantic Metric → Deterministic Result
data/system reliability surface

followed by:

Retrieval → SQL generation → Result selection → Explanation
AI capability/reliability surface

with lineage and permissions running across both.

Freshness triggers for future reuse

This dossier should be re-checked when any of the following occurs:

Data-agent evaluation changes. DAB was revised on October 7, 2026, only three days before this snapshot, so quoted frontier results are especially freshness-sensitive. 2

dbt behavior or Semantic Layer version changes. The current freshness page was updated October 6, 2026, and dbt’s semantic model behavior is version-specific. 5

Lineage-spec changes or instrumentation changes. A new facet specification is less important than whether your actual orchestration/query stack emits the relevant edges and run/version metadata. 8

Cloud access-control behavior changes. Particularly re-check propagation semantics for derived tables, views, masking and row/column policies. 12

Ingestion delivery semantics change. Moving between at-least-once and exactly-once configurations can change which duplicate controls are necessary, although it does not remove downstream join/grain risk. 10

The business metric changes. A new revenue-recognition policy, identity model, active-customer window, currency treatment or refund rule should invalidate the previous evidence envelope until regression tests and historical comparability are reviewed.

The model, retrieval stack or agent harness changes. The AI portion should be re-evaluated even when the data layer is unchanged; model capability is a separate failure surface.

EU/US governance changes. For Czech/EU deployments, refresh GDPR/AI Act applicability analysis when processing scope or use-case classification changes. For US-oriented governance, NIST itself notes that its GenAI Profile sits within an evolving AI risk-management framework. 14

The durable decision principle is less likely to expire:

Before AI explains a material metric, require independent evidence that the metric means what you think it means, the expected data arrived, the transformations preserved the intended grain, the query used the authorized version, and the caller was allowed to see the result. Let AI investigate and interpret that evidence; do not make it the evidence.

Portable citation index

1. [2411.07763] Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows
2. [2603.20576] Can AI Agents Answer Your Data Questions? A Benchmark for Data Agents
3. Understanding the Effects of Noise in Text-to-SQL: An Examination of the BIRD-Bench Benchmark - ACL Anthology
4. Source freshness | dbt Developer Hub
5. Source freshness | dbt Developer Hub
6. DEEQU - Data quality validation for machine learning pipelines - Amazon Science
7. Quickstart for the dbt Semantic Layer | dbt Developer Hub
8. Object Model | OpenLineage
9. Column Level Lineage Dataset Facet | OpenLineage
10. Amazon SQS at-least-once delivery - Amazon Simple Queue Service
11. Subscription overview  |  Pub/Sub  |  Google Cloud Documentation
12. Introduction to BigQuery row-level security
13. Restrict access with column-level access control | BigQuery
14. Regulation - 2016/679 - EN - gdpr - EUR-Lex
15. Regulation - EU - 2024/1689 - EN - EUR-Lex
16. Artificial Intelligence Risk Management Framework: Generative Artificial Intelligence Profile | NIST
17. Introduction to column-level access control | BigQuery
18. Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards

Import verification and asset availability

Captured from genuine ChatGPT Deep Research with snapshot 2026-10-10; completed in 8 minutes. Retrieved 2026-10-10. Original prompt and canonical conversation are retained on this run. Complete synthetic code/examples are inline in this report; no external sandbox asset download is required or claimed. Embedded examples and report statements about execution have not been independently run or verified during import. Delivery is not independent validation of source claims, dates or applicability. Evidence remains Captured.