# 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. citeturn22view2

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. citeturn14view0

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. citeturn22view1

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. citeturn21search9turn21search1 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. citeturn21search7

### 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. citeturn5view2turn8view0turn8view1

### 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. citeturn14view0turn15view5turn5view2

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. citeturn14view0turn22view1turn21search9turn21search7

## 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. citeturn1search4turn1search2turn1search6

### 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. citeturn1search1turn1search21

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. citeturn22view2

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. citeturn22view2turn15view0

### 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. citeturn15view0

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. citeturn15view0turn1search3

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. citeturn10view1turn22view3

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. citeturn15view4turn15view5

## 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. citeturn1search2turn1search4turn1search1turn15view0turn1search3

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. citeturn10view1turn22view3

### 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. citeturn22view3

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. citeturn17view1 BigQuery also has data-viewer roles distinct from editing capabilities. citeturn17view0turn16view0

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. citeturn18search1turn18search5

### 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. citeturn1search2turn1search6

**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. citeturn8view0turn8view1

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

```sql
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. citeturn1search4turn1search2turn1search1

## 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. citeturn22view1

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. citeturn8view1turn5view2

### 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. citeturn8view0turn8view1

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. citeturn21search9 CRAG provides separate evidence that adding retrieval does not automatically eliminate factual error. citeturn21search7

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 citeturn14view0 |
| 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 citeturn15view4turn15view5 |
| 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 citeturn1search4 |
| 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 citeturn1search2turn1search6 |
| 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 citeturn1search1 |
| 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 citeturn15view0 |
| 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 citeturn1search3 |
| 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 citeturn22view2 |
| 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 citeturn10view1 |
| 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 citeturn22view3 |
| 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 citeturn17view1 |
| 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 citeturn5view1turn5view2 |
| 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 citeturn18search1turn18search5 |
| 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 citeturn22view1 |
| 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 citeturn21search9turn21search1 |
| 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 citeturn21search7 |

### 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. citeturn22view3turn15view4

**“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. citeturn22view2

**“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. citeturn1search4turn1search2

**“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. citeturn1search1

**“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. citeturn21search9turn21search7

**“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. citeturn19view0

### 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. citeturn17view1turn18search1

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

```text
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 citeturn22view3 |
| **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 citeturn22view1turn21search7 |
| **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:

```text
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. citeturn5view1 |
| dbt materially changes contracts, tests, Semantic Layer or model-version behavior | These are implementation examples used in the workflow. citeturn15view0turn1search2 |
| OpenLineage changes its object/column-lineage specification | The diagnosis design relies on current lineage semantics. citeturn10view1turn22view3 |
| A warehouse changes ingestion/deduplication or IAM behavior | At-least/exactly-once and permission assumptions are provider/version dependent. citeturn22view2turn17view1 |
| New enterprise text-to-SQL evaluations materially change capability evidence | Current Spider 2.0 evidence should not be treated as timeless. citeturn22view1 |
| New RAG/long-context evidence materially changes source-fidelity expectations | Current evidence shows retrieval/context use remains an independent reliability layer. citeturn21search9turn21search7 |
| 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.