DEV Community: Vaishnav Prabhu

Write-Audit-Publish: Never Promote a Bad Table Again

Vaishnav Prabhu — Fri, 09 Oct 2026 08:08:21 +0000

Most quality checks run after bad data is already live. You load the table, then test it, then
discover the problem — but by now a dashboard already served wrong numbers, or an agent already
answered with them. Write-Audit-Publish (WAP) flips the order: you prove the data is good
before anyone can see it.

The three steps

Write — build the new data into a staging location consumers can't see: a temp table, a separate schema, or a zero-copy clone/branch of the target.
Audit — run your quality gates against that staged copy: uniqueness at the grain, row-count vs baseline, freshness, reconciliation against a trusted source.
Publish — only if every check passes, promote the staged data to production with an atomic swap (a rename or clone-swap), so consumers flip from old-good to new-good with no bad state in between. If a check fails, nothing is published; production keeps serving the last known-good data while you investigate.

Why it's better than audit-after-load

Consumers never see bad data. The failure mode becomes "today's refresh is delayed," not "the dashboard is wrong." Stale-but-correct beats fresh-but-wrong almost every time.
The swap is atomic. No window where half the table is updated and queries see a mix.
Rollback is trivial. The previous version is still right there; promoting it back is one operation.

Making it cheap

The pattern used to be expensive — you'd copy the whole table. Modern warehouses make it nearly
free with zero-copy clones and table/catalog branching: you stage on a clone, audit it,
and publish by swapping pointers, with no bulk data movement. That's what turned WAP from a
nice-to-have into a default.

Where it fits in your stack

Wire the audit step to the same tests you already write (dbt tests, a data-quality scan) and let
their result gate the publish. The mental model: your transformation job doesn't "load the
table," it "proposes a new version," and only a passing audit promotes it.

Takeaways

Validate on a staged copy and publish only what passes — consumers never see bad data.
Promote with an atomic swap so there's no half-updated state, and rollback is one step.
Zero-copy clones and branching make WAP cheap enough to be the default.
Reframe loads as "propose a version"; a passing audit is what makes it live.

Vector-Native Analytics: Embeddings as a First-Class Warehouse Column

Vaishnav Prabhu — Fri, 09 Oct 2026 08:04:04 +0000

For years, "AI data" lived somewhere else — a separate vector database bolted onto the side of
your warehouse, kept in sync with brittle pipelines. That's changing. Modern warehouses can
store embeddings as a column and run similarity search in SQL, right next to your structured
data. That unlocks a genuinely new modeling pattern: hybrid queries that filter with
ordinary SQL and rank by semantic similarity in the same statement.

Why keeping vectors in the warehouse matters

One copy of the data, one governance model, one query engine. You can write something like
"find the records in this region, from last quarter most similar to this description" — a
structured filter and a semantic rank together — without shuttling data between systems or
reconciling two sources of truth. No sync pipeline to break at 2am.

The modeling decisions that make or break it

Embeddings are data, so they need the same discipline as any model:

Pick the embedding grain. What is one vector of? A row? A text field? A record plus its context? This is a grain decision, and it determines whether retrieval returns signal or noise. Too coarse and results are vague; too fine and they lose meaning.
Version the embeddings. The moment you change embedding models, old and new vectors aren't comparable. Store the model/version alongside each vector so you know what's stale, and plan how you'll re-embed.
Keep rich metadata. Store source, date, entity, and permissions next to each vector so you can filter retrieval, not rely on similarity alone. Metadata is what makes hybrid search work.
Mind the cost. Embedding generation and vector indexes aren't free. Model the refresh cadence deliberately — re-embed on change, not on every run.

What it's good for

Semantic search over your own records — tickets, docs, products — governed like the rest of your data.
Entity resolution / dedup — near-duplicate records that exact matching misses.
RAG over the warehouse — serving trusted, filtered context to an LLM straight from governed tables instead of a disconnected store.

A caution

Vector search returns the nearest match, not the correct one — it's confident even when
it's wrong. Treat similarity as a ranking signal, keep a relevance threshold, and always carry
metadata so you can filter and audit what came back. Semantic ≠ accurate.

Takeaways

Warehouses can now hold embeddings as columns and do similarity search in SQL — one copy, one governance model.
Hybrid queries (SQL filter + semantic rank) are the new superpower; no separate vector DB to sync.
Treat embedding grain, versioning, metadata, and refresh cost as first-class modeling choices.
Nearest isn't correct — threshold and filter with metadata.

Modeling Your Warehouse So AI Can Actually Use It

Vaishnav Prabhu — Fri, 09 Oct 2026 08:01:58 +0000

Everyone wants to point an LLM at their data — "ask questions in plain English," "let
the agent pull the numbers." Then it confidently returns the wrong revenue figure, and
trust evaporates. The problem usually isn't the model. It's the data layer underneath
it. An LLM is only as good as the structure, definitions, and correctness of what it
reads. Here's how to model for that.

Give the model a contract, not a guess

If you let a text-to-SQL tool or an agent freelance joins against raw tables, it will
invent a definition of "active customer" that doesn't match anyone else's. A
semantic layer (or a set of certified metric models) fixes this: define each
metric once — its grain, its filters, its joins — so the model composes from trusted
building blocks instead of reconstructing business logic from column names. The
semantic layer becomes the contract between your warehouse and anything, human or AI,
that queries it.

Make tables legible

Models reason over your schema the way a new analyst would — from names and docs. You
reduce hallucinated joins dramatically just by making the warehouse legible:

Clear names. gross_sales beats amt2. The model can't infer what it can't read.
Documented grain and keys. State the primary key and the grain of every model; it's what keeps an agent from double-counting.
Expose curated views, not raw tables. Point AI consumers at a clean, certified layer so they can't wander into staging and half-built tables.

Treat embeddings as a modeling decision

Retrieval-augmented generation (RAG) lives or dies on what you chose as a chunk.
That's a grain question, same as any fact table. Before you store vector columns next
to your rows, decide: what is one "document"? A row? A paragraph? A record plus its
context? Chunk too coarse and retrieval returns noise; too fine and it loses meaning.
Keep rich metadata alongside each embedding (source, date, entity, permissions) so
you can filter retrieval instead of hoping similarity alone is enough.

Correctness matters more with AI, not less

A dashboard with a bad number invites a double-take. An agent with a bad number states
it as fact and moves on. So the fundamentals you already know carry extra weight:

Uniqueness and grain — a fan-out that doubles a metric will be surfaced by an agent with total confidence.
Freshness — an agent won't notice the feed is a day stale; your contract has to.
Definitions — if "revenue" is ambiguous in the warehouse, it'll be ambiguous (and wrong) in the answer.

In other words: the data-quality gates and reconciliation checks you build for humans
become the guardrails that keep AI honest.

Takeaways

Put a semantic/metrics layer between your warehouse and any LLM so definitions are a contract, not a guess.
Make the schema legible — clear names, documented grain and keys, curated views over raw.
Treat RAG chunking and embeddings as a grain-and-metadata modeling problem.
Correctness (uniqueness, freshness, clear definitions) matters more with AI, because an agent repeats your mistakes with confidence.

Related in In the Pipeline*: running those models safely once the data is ready.*

Fan-Out Joins: The Silent Cause of Inflated Metrics

Vaishnav Prabhu — Fri, 09 Oct 2026 07:07:22 +0000

Here's a bug that has quietly wrecked more dashboards than almost anything else: you join a fact
table to a dimension, someone later lets a duplicate sneak into that dimension, and every fact
row that matches gets multiplied. Your SUM() inflates. Nothing errors. The dimension looks
fine. The numbers are just… bigger than reality, and nobody's sure why.

What a fan-out actually is

A join multiplies rows whenever the key you join on isn't unique on the other side. Join a fact
to a dimension that has two rows for the same key, and every matching fact row comes back
twice. Aggregate that, and totals double. Three duplicate dimension rows? Triple. The fact
table didn't change — the join invented rows.

Why it's so hard to spot

The duplicate dimension rows are often identical on the visible attributes, so eyeballing the dimension reveals nothing.
If only a few keys are affected, the grand total still looks plausible.
It usually appears right after a routine load that "shouldn't have changed anything," so it's easy to wave off.

The classic source is a Slowly Changing Dimension that ended up with two "current" rows for one
key (see SCD Type-2 Without the Duplicate-Current-Row Footgun). But any non-unique join key —
a mapping table with a dupe, a dimension built from a fan-out of its own — does it.

How to catch it

Assert the grain. The join key must be unique on the dimension side. Make it a test:

   select join_key, count(*)
   from dim
   group by join_key
   having count(*) > 1;   -- must return zero rows

Watch the ratio. Compare a metric to a trusted baseline. A number that's suspiciously
close to exactly 2× or 4× is a duplication fingerprint, not real growth.
Count rows across the join. If a fact table's row count jumps after a join step, the join
is adding rows that shouldn't exist.

How to prevent it

Guarantee uniqueness before you join. Dedupe the dimension to one row per key, or select the current version explicitly.
Be defensive at read time. A view can enforce one row per key with qualify row_number() over (partition by key order by valid_from desc) = 1, so one upstream glitch can't fan out every metric downstream.
Test the invariant in CI. A uniqueness test on the join key turns a silent production disaster into a failed build.

Takeaways

A non-unique join key multiplies facts and inflates every aggregate built on them.
Duplicate dimension rows are often invisible and total-preserving — don't trust eyeballing.
Assert key uniqueness as a test, watch for round-number ratios, and dedupe before joining.

SCD Type-2 Without the Duplicate-Current-Row Footgun

Vaishnav Prabhu — Fri, 09 Oct 2026 07:04:13 +0000

Slowly Changing Dimension (SCD) Type-2 is how we keep history in a dimension: when a
row's attributes change, we close the old version and open a new one. Done right,
exactly one row per business key is "current" at any time. Done wrong, you get
two current rows for the same key — and every fact that joins to that dimension
silently doubles.

The failure mode

A hand-rolled Type-2 loader usually does two steps:

Expire the existing current row when a change is detected.
Insert the new version as current.

The classic bug: the expire step and the insert step don't cover the same set of
rows. For example, the insert fires for both new keys and changed keys, but the
expire only fires for changed keys. If a key that already has a current row slips
into the "new" path, you insert a second current row without closing the first —
and now the key has two is_current = true records with open end-dates.

Downstream, a fact table joining on that key fans out: every matching fact row is
duplicated, and any SUM() over it inflates. The dimension looks fine at a glance;
the damage shows up as mysteriously doubled metrics.

Why it hides so well

The duplicate rows are often identical on the business attributes, so casual inspection of the dimension reveals nothing.
Aggregate totals can still look plausible if only a handful of keys are affected.
It frequently appears right after a load that should have been a no-op, which makes it easy to dismiss.

How to prevent it

1. Prefer a single atomic MERGE over separate update + insert.
A MERGE keyed on the business key lets you expire-and-insert in one statement with
consistent matching logic, removing the "two steps disagree" class of bug.

2. If you keep update+insert, expire every prior current row you're about to replace — not just the subset flagged as "changed."

3. Add a uniqueness guarantee as a hard invariant. A post-load assertion that
must return zero rows:

select business_key, count(*)
from dim
where is_current = true
group by business_key
having count(*) > 1;

Wire this into your tests (e.g., a dbt unique test on the current-rows view, or a
data-quality check) so a regression fails the pipeline instead of reaching marts.

4. Make consumers defensive. Views that read the dimension can enforce one row
per key with a QUALIFY ROW_NUMBER() OVER (PARTITION BY business_key ORDER BY valid_from DESC) = 1, so a single upstream glitch can't fan out sales everywhere.

Takeaways

"Current" must mean exactly one row per key — enforce it, don't assume it.
A MERGE keyed on the business key avoids the update/insert mismatch entirely.
Guard it with a uniqueness assertion in CI/tests, and make downstream views defensive with a de-duplicating window function.

Next in this series: fan-out joins — how a duplicated dimension row quietly inflates every metric built on top of it.

Catching the Incident Before the Alert: Anomaly Detection for Pipelines

Vaishnav Prabhu — Fri, 09 Oct 2026 07:01:31 +0000

Static thresholds are the smoke detectors of data engineering: useful, but they only go off
when something's already on fire, and they miss the slow leaks entirely. "Alert if rows < 1000"
won't catch a table that quietly dropped 30%, or a feed that's complete in total but broken for
one segment. Anomaly detection asks a better question: is today normal for this thing?

Why "normal" needs a baseline, not a number

Data has shape — weekday/weekend rhythms, month-end spikes, seasonal trends. A good baseline
captures that: compare today against a rolling window of comparable days (same day-of-week),
not a flat constant. Then flag deviations relative to that baseline. Suddenly a Wednesday that's
35% below recent Wednesdays stands out, even though it clears your old static floor.

The three signals worth watching

Volume — row counts per load vs baseline. Catches partial feeds and double loads.
Freshness — is the newest record from the period you expect? Catches stalled upstreams.
Distribution — did the shape of a key column shift (null rate, category mix, a metric's mean)? Catches silent logic bugs that keep volume normal but change meaning.

The lesson that matters most: check per segment, not just totals

This is the subtle one. A feed can match perfectly in aggregate while being badly wrong
underneath, because per-segment errors cancel out. One store is doubled, another is missing,
and the fleet total looks fine. If you only monitor the grand total, you'll certify broken data
as healthy. Run your anomaly checks at the grain that matters — per segment, per channel,
per role — and the offsetting errors stop hiding.

Keep it explainable and quiet

Fancy models are tempting, but an anomaly alert nobody understands gets muted, and a muted
alert is worthless. Favor methods whose output you can explain in a sentence ("28% below the
trailing 4-week median for this segment"). Tune aggressively against false positives — alert
fatigue kills these systems faster than missed anomalies do. Add seasonality and ML only where
a simple baseline provably isn't enough.

Takeaways

Replace flat thresholds with baselines that respect weekday and seasonal shape.
Watch volume, freshness, and distribution — the last one catches silent meaning changes.
Detect at the real grain, not the total, so offsetting per-segment errors can't hide.
Explainable and low-false-positive beats clever-but-muted every time.

Self-Healing Pipelines: Using AI to Triage Incidents

Vaishnav Prabhu — Fri, 09 Oct 2026 06:57:30 +0000

When a pipeline fails at 2am, the slow part usually isn't the fix — it's the triage: reading
logs, figuring out which task broke, deciding whether it's a transient blip or a real data bug,
and writing it up. That triage is exactly the kind of pattern-matching LLMs are good at. Used
carefully, an AI layer can shrink time-to-understanding from an hour to a minute — without
handing it the keys to production.

What "self-healing" actually means (and doesn't)

It does not mean an AI silently rewriting your pipeline. It means a bounded assistant that
observes, explains, and proposes — and only acts within a tight, pre-approved box. Think
of it as an always-on on-call buddy that does the first pass.

A sensible ladder of autonomy

Roll this out in stages, earning trust at each rung:

Explain. On failure, the assistant pulls the logs and context and writes a plain-English summary: which task, likely category (transient / data / code), and the smoking-gun lines. Zero actions — pure triage acceleration.
Classify and route. It tags the incident and routes it: page a human for data-correctness issues, or note "likely transient" for a timeout.
Propose remediation. It drafts the fix — "rerun partition 2026-08-05 after the upstream lands" — and a root-cause summary, ready for a human to approve with one click.
Act within guardrails. For a small set of reversible, pre-approved actions (retry a known-flaky step, re-trigger a failed sensor), it can act automatically and log what it did.

Destructive or irreversible actions — deletes, backfills, schema changes — stay human-approved,
always.

The guardrails that make it safe

Bounded actions. An allowlist of safe operations, nothing else.
Full audit trail. Every AI decision and action is logged and reviewable.
A circuit breaker. Repeated auto-retries must escalate to a human, not loop forever.
No masking. Auto-retrying a flaky step is fine; auto-retrying until a real bug happens to pass is how you hide incidents. Track "succeeded only after N retries" so fragility still surfaces.

Where it pays off first

Start with the highest-volume, lowest-stakes failures: transient API timeouts, late upstreams,
flaky sensors. That's where AI triage removes the most 2am pages for the least risk — and where
you build the track record to justify more autonomy later.

Takeaways

The win is faster triage and explanation, not autonomous rewriting.
Climb an autonomy ladder: explain → classify → propose → act-in-a-box.
Keep destructive actions human-approved, log everything, and add a circuit breaker.
Never let auto-remediation mask a real bug — surface "passed only after retries."

Running an LLM as a Pipeline Step Without 2am Surprises

Vaishnav Prabhu — Fri, 09 Oct 2026 06:55:07 +0000

Teams are dropping LLM calls into data pipelines everywhere now — to classify tickets,
extract fields from messy text, summarize records, tag content. It feels like adding
one more transform. It isn't. A SQL transform is a pure function: same input, same
output, every time. An LLM step is a slow, rate-limited call to an external service
that can return something different each run, or something that isn't even valid.

Treat it like the unreliable network dependency it is, and the 2am surprises mostly
go away. Here's the short playbook.

1. Force structure, then validate it

Never let free-form text flow downstream. Ask the model for structured output
(JSON that matches a schema) and validate every response against that schema before
you trust it. If it doesn't parse or a required field is missing, that's a failure —
retry it, route it to a dead-letter table, or fall back. The moment you depend on a
model "usually" returning clean output is the moment a malformed response corrupts a
downstream table.

2. Make re-runs cheap and safe

LLM calls cost money and time, so a naive re-run can be expensive and non-reproducible.
Two habits fix this:

Cache by input hash. If you've already processed this exact input with this exact prompt and model version, reuse the result. Re-runs become near-free.
Pin your versions. Record the model name and prompt version with each output. "The numbers changed" is impossible to debug if the model silently updated underneath you.

3. Budget for failure up front

Wrap the call the way you'd wrap any flaky API: a timeout, retries with backoff, and a
cost ceiling so a runaway loop can't quietly burn your budget. Decide in advance
what happens when the model is down or slow — skip, use last-known-good, or fail the
run loudly. Don't let a provider outage become a silent data gap.

4. Treat evals as a data-quality gate

This is the big one. Before an AI step's output reaches production, gate it like any
other quality check:

% valid — what share of responses passed schema validation?
Accuracy on a sample — hold a small labeled set and measure agreement each run.
Drift — did the distribution of outputs shift sharply versus a recent baseline?

If those fall below threshold, the run goes red and stops — exactly like a row-count or
uniqueness gate. An LLM step with no eval is a green checkmark hiding unknown quality.

5. Observe the things that only AI steps have

Log tokens, latency, and cost per run alongside a quality metric, and alert on drift.
These are the signals that tell you a prompt change or a model update quietly degraded
things — long before a human notices weird results in a report.

Takeaways

An LLM step is a non-deterministic external call, not a pure transform — engineer it like a flaky dependency.
Demand structured output and validate it; cache by input; pin model and prompt versions.
Gate outputs with evals the same way you gate data quality, and observe tokens, cost, latency, and drift.

Related in The Grain*: modeling your warehouse so these models have correct, usable
data to work with in the first place.*

Why Your Orchestrator Task "Succeeded" But Your Data Is Wrong

Vaishnav Prabhu — Fri, 09 Oct 2026 06:52:46 +0000

A green checkmark means your task ran without throwing an exception. It does not
mean the data it produced is correct. These are two very different guarantees, and
confusing them is one of the most common ways bad data reaches production
dashboards undetected.

The gap between "ran" and "right"

Most orchestrators judge success on exit code: the process finished, nothing threw,
mark it green. But a task can exit cleanly while:

loading half the expected rows (an upstream feed was incomplete),
duplicating rows because a dimension join fanned out,
writing values that are internally consistent but wrong (a silent unit or timezone bug),
or succeeding on a retry that silently reprocessed a partial payload.

None of these trip an exception. All of them produce a green run and wrong numbers.

Make correctness a first-class, separate signal

The fix is to stop treating "the job ran" as a proxy for "the data is good," and add
an explicit quality gate as its own step that can fail the pipeline:

Row-count / volume checks — did we land roughly the expected number of rows for this period, versus a recent baseline? A 30%+ swing is worth blocking on.
Uniqueness checks — assert the natural key is unique at the table's grain. This is your early-warning system for fan-out and duplicate loads.
Freshness checks — is the newest record actually from the period you expected?
Reconciliation checks — does this table agree (within tolerance) with an independent source of truth?

Run these after the load, as a gate the downstream steps depend on. A failing gate
should turn the run red and stop propagation — a loud failure now is far cheaper than
a silent one that someone finds in a dashboard next week.

Watch the "succeeded after N retries" trap

A task that fails twice and passes on its third attempt still shows as success. If
the underlying action isn't idempotent, those earlier partial attempts can leave
duplicated or inconsistent data behind. Two defenses:

Make the operation idempotent (truncate-and-reload a partition, or MERGE on a key) so a retry is always safe.
Alert on "succeeded only after retries" as a distinct signal, so fragility surfaces before it becomes an outage.

Takeaways

Exit code answers "did it run," not "is it right." Instrument both.
Add uniqueness, volume, freshness, and reconciliation gates as blocking steps.
Treat retries and partial reloads as correctness risks, not just reliability ones.

Next in this series: a repeatable RCA framework for when one of these gates finally fires.

A Repeatable RCA Framework for Data Incidents

Vaishnav Prabhu — Fri, 09 Oct 2026 06:51:19 +0000

Most data incidents get debugged by vibes: someone opens logs, scrolls, guesses, reruns
something, and hopes. That works until it doesn't — and it never produces a learning the
team can reuse. A repeatable root-cause framework turns firefighting into a routine. Here's
one that holds up: Localize → Characterize → Root-cause → Remediate → Prevent.

1. Localize

Find where the wrongness enters before asking why. Walk the lineage and pin the first
step whose output is already bad. Narrow it to a specific task, table, and time window. Most
wasted RCA time comes from theorizing about causes while still unsure which table even broke.

2. Characterize the scope

Measure the blast radius before chasing mechanism:

Fleet-wide or local? One store/segment, or everything?
One metric or many? A single column, or the whole load?
New or chronic? Was yesterday fine? When did it start?

Scope is a powerful filter. A problem that hits one metric across all segments points
somewhere very different than one that hits all metrics for one segment.

3. Root-cause by comparison

The fastest root-causing technique is diffing a bad run against a good one: same query,
different day; this segment vs a healthy segment; expected vs actual row counts. Differences
light up the cause. Watch for ratios that are suspiciously round — a value that's exactly 2×
or 4× its neighbor usually means duplication (a fan-out or a double load), not a slow drift.

4. Separate the real cause from red herrings

Incidents are littered with distractions: a scary-looking stack trace from a post-success
callback, a deprecation warning, a slow-but-harmless step. Ask of each: did this actually
change the data? If the task was already marked successful before the error fired, that
error is noise. Name your red herrings explicitly so nobody re-chases them next time.

5. Remediate, then prevent

Remediation is two moves: fix the root cause, then repair the already-written bad data
(rebuild the affected partitions — a dedupe or patch often won't fully undo it). Prevention
is the part teams skip and shouldn't: add the check that would have caught this — a
uniqueness assertion, a volume gate, a reconciliation test. Every incident should leave
behind one new guardrail, or you'll meet it again.

Make it a template

Keep a short, fill-in-the-blanks incident doc: symptom, localized step, scope, the
good-vs-bad comparison that cracked it, root cause, remediation, and the new guardrail. Five
of these and your team has a pattern library — and the next incident resolves in minutes.

Takeaways

Localize the broken step before theorizing about why.
Scope first: fleet-vs-local and one-metric-vs-many narrow the search fast.
Diff a bad run against a good one; distrust suspiciously round ratios.
Discard post-success errors and warnings that never touched the data.
Every incident ships one new guardrail.