# 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](https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines) 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](https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/) 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](https://arxiv.org/html/2411.07763v2) More recent work further shows that benchmark labels themselves can be defective, making leaderboard accuracy an unsafe proxy for deployment correctness. [4](https://arxiv.org/html/2601.08778v1)

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](https://docs.getdbt.com/docs/build/semantic-models) |
| 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](https://docs.getdbt.com/reference/resource-properties/data-tests) |
| 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](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/) |
| Freshness and relevant time semantics | dbt distinguishes configurable freshness based on loaded timestamps, but that check establishes recency, not full completeness. [8](https://docs.getdbt.com/reference/resource-configs/freshness) |
| 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](https://docs.getdbt.com/docs/mesh/govern/model-contracts) |
| 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](https://openlineage.io/docs/spec/object-model/) |
| 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](https://docs.cloud.google.com/bigquery/docs/authorized-views) A September 2026 text-to-SQL study found substantial access-control-specific failures that standard unrestricted benchmarks did not expose. [12](https://arxiv.org/html/2607.22115v2) |
| 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](https://arxiv.org/html/2411.07763v2) |

### 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](https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines)

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](https://docs.cloud.google.com/bigquery/docs/authorized-views)

## 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](https://docs.getdbt.com/docs/build/semantic-models)

**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](https://docs.getdbt.com/reference/resource-properties/data-tests) 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](https://docs.getdbt.com/docs/mesh/govern/model-contracts) 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](https://docs.getdbt.com/reference/resource-configs/freshness) 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](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/)

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](https://openlineage.io/docs/spec/object-model/) 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](https://docs.getdbt.com/docs/build/semantic-models) 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](https://arxiv.org/html/2411.07763v2) 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](https://arxiv.org/html/2508.15276v2) 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](https://arxiv.org/html/2501.10858v1) 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](https://docs.getdbt.com/docs/build/semantic-models)

**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](https://docs.getdbt.com/reference/resource-properties/data-tests)

**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](https://docs.getdbt.com/reference/resource-configs/freshness)

**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](https://docs.getdbt.com/docs/mesh/govern/model-contracts)

**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](https://openlineage.io/docs/spec/object-model/)

**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](https://docs.cloud.google.com/bigquery/docs/authorized-views)

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](https://arxiv.org/html/2607.22115v2)

### 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](https://arxiv.org/html/2508.15276v2)

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

```sql
-- 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](https://docs.getdbt.com/reference/resource-properties/data-tests) 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](https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/) 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](https://arxiv.org/html/2411.07763v2)

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](https://arxiv.org/html/2508.15276v2) Reliable Text-to-SQL explicitly combines uncertainty detection, abstention and human interaction and reports strong schema-linking results in its benchmark setting. [14](https://arxiv.org/html/2501.10858v1)

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](https://arxiv.org/html/2601.08778v1)

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](https://arxiv.org/html/2607.22115v2)

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](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/)

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](https://docs.getdbt.com/docs/mesh/govern/model-contracts) 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](https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines) | 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](https://research.google/pubs/everyone-wants-to-do-the-model-work-not-the-data-work-data-cascades-in-high-stakes-ai/) | 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](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/) | 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](https://docs.getdbt.com/reference/resource-properties/data-tests) | 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](https://docs.getdbt.com/reference/resource-configs/freshness) | 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](https://docs.getdbt.com/docs/mesh/govern/model-contracts) | 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](https://docs.getdbt.com/docs/build/semantic-models) | 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](https://openlineage.io/docs/spec/object-model/) | 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](https://docs.cloud.google.com/bigquery/docs/authorized-views) | 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](https://arxiv.org/html/2411.07763v2) | 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](https://arxiv.org/html/2601.08778v1) | 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](https://arxiv.org/html/2508.15276v2) | 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](https://arxiv.org/html/2501.10858v1) | 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](https://arxiv.org/html/2607.22115v2) | 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](https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines)

**“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](https://docs.getdbt.com/reference/resource-configs/freshness)

**“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](https://openlineage.io/docs/spec/object-model/)

**“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](https://docs.getdbt.com/docs/build/semantic-models)

**“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](https://arxiv.org/html/2601.08778v1)

**“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](https://arxiv.org/html/2607.22115v2)

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

```yaml
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](https://docs.getdbt.com/reference/resource-configs/freshness) |
| OpenLineage specification change affecting run/dataset/version facets | Could alter recommended provenance implementation; this dossier inspected v1.53.0. [10](https://openlineage.io/docs/spec/object-model/) |
| Warehouse permission-model change | Authorization recommendations must follow the deployed platform; the BigQuery page inspected here was updated October 7, 2026. [11](https://docs.cloud.google.com/bigquery/docs/authorized-views) |
| 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](https://arxiv.org/html/2411.07763v2) |
| Corrections to BIRD, Spider 2.0 or successor benchmarks | The 2026 audit showed that annotation quality can materially change performance and rankings. [4](https://arxiv.org/html/2601.08778v1) |
| 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](https://arxiv.org/html/2607.22115v2) |
| 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](https://www.amazon.science/publications/deequ-data-quality-validation-for-machine-learning-pipelines)
2. ["Everyone wants to do the model work, not the data work": 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/)
3. [Spider 2.0: Evaluating Language Models on Real-World Enterprise Text-to-SQL Workflows](https://arxiv.org/html/2411.07763v2)
4. [Pervasive Annotation Errors Break Text-to-SQL Benchmarks and Leaderboards](https://arxiv.org/html/2601.08778v1)
5. [Semantic models | dbt Developer Hub](https://docs.getdbt.com/docs/build/semantic-models)
6. [About data tests property | dbt Developer Hub](https://docs.getdbt.com/reference/resource-properties/data-tests)
7. [The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost in Massive-Scale, Unbounded, Out-of-Order Data Processing](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/)
8. [freshness | dbt Developer Hub](https://docs.getdbt.com/reference/resource-configs/freshness)
9. [Model contracts | dbt Developer Hub](https://docs.getdbt.com/docs/mesh/govern/model-contracts)
10. [Object Model | OpenLineage](https://openlineage.io/docs/spec/object-model/)
11. [Authorized views  |  BigQuery  |  Google Cloud Documentation](https://docs.cloud.google.com/bigquery/docs/authorized-views)
12. [Benchmarking Text-to-SQL under Role-Based Access Control](https://arxiv.org/html/2607.22115v2)
13. [AmbiSQL: Interactive Ambiguity Detection and Resolution for Text-to-SQL](https://arxiv.org/html/2508.15276v2)
14. [Reliable Text-to-SQL with Adaptive Abstention](https://arxiv.org/html/2501.10858v1)


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