skip to content

In an ELT pipeline, why keep an immutable copy of the raw extracted data?

level: middleimportance: should knowfreq 52%

answer

  1. logic changes, the source will not wait
  2. what can you rebuild from
  3. sources overwrite, purge and throttle
  4. corrections arrive as new rows, never edits

basics

~20 s

Transformation logic changes and has bugs. An untouched raw copy lets you rebuild every downstream table from what actually arrived, without re-extracting from sources that overwrite rows, purge history, throttle large reads or have since changed shape.

solid answer

~50 s

The raw layer is the pipeline's replay log. Downstream models are derived data — if the business rule was wrong for six months, the fix is to correct the statement and rebuild from raw, which is fast, auditable and requires nobody's permission. Without that copy, the same fix means going back to the source, and operational sources are poor archives: they update rows in place, hard-delete, retain only a rolling window, rate-limit or bill for large historical reads, and may have changed schema since. **Immutable** means the landed rows are never edited: a correction or a late-arriving record is a new row, distinguished by ingestion timestamp, not an update. That property is what makes a rebuild deterministic — re-running the same logic over the same raw partitions produces the same result, which is also what lets you reconcile a disputed number against exactly what arrived.

code

sql · 12 lines
sql
-- Raw stays append-only; the model picks the current version
SELECT *
FROM (
    SELECT
        r.*,
        ROW_NUMBER() OVER (
            PARTITION BY r.order_id
            ORDER BY r.source_updated_at DESC, r._ingested_at DESC
        ) AS rn
    FROM raw.orders AS r
) AS versioned
WHERE rn = 1;

go deeper

for a junior

Recall that curated tables are derived from raw, so keeping the raw input means a broken transformation can be fixed by re-running it rather than by asking the source system for history again.

for a middle

Explain why sources make poor archives — in-place updates, hard deletes, rolling retention, rate limits, schema drift — and what append-only landing actually looks like when a record changes.

for a senior

Demonstrate the operating discipline: partitioning and ingestion metadata that make rebuilds deterministic, a retention tier tied to a stated restatement policy, and a controlled erasure path for personal data.

for a principal

Own the policy question. Decide how far back the organisation is willing to restate published numbers, what that costs in storage, and how erasure obligations are honoured without destroying reproducibility.

## Derived data versus the record of what arrived In an ELT pipeline every curated table is *derived*: it is a function of the raw input and the transformation logic. If you keep the input and the logic, you can always reproduce the output. If you keep only the output, you have thrown away the ability to reproduce anything — and outputs are precisely the thing that turns out to be wrong. That is the whole argument for the raw layer. It is not sentimentality about data; it is the difference between a pipeline you can fix and a pipeline you can only apologise for. ## What goes wrong without it The realistic failure is not corruption, it is a mistake in logic. A currency conversion applied to the wrong column. A join that silently dropped rows with a null key. A filter that excluded a channel nobody noticed. These are typically discovered months later, when someone reconciles a total against another system. With raw retained, remediation is: fix the statement, re-run it over the affected partitions, publish the restated numbers, and record the change. Hours of compute. Without raw, remediation means re-extracting history from the source — and operational systems are hostile archives: - They **update rows in place**, so today's read of an order gives you today's status, not the status on the day it was processed. - They **hard-delete**. Cancelled records, closed accounts and purged customers are simply gone. - They **retain a rolling window**. Many APIs will not serve data older than ninety days at any price. - They **rate-limit or bill** for bulk historical reads, and the owning team has to approve the load. - They **change schema**. The column you need may have been renamed, split or dropped since. Any one of these turns "we will just pull it again" into a project. Several at once turn it into an impossibility. ## What immutable means in practice Immutable does not mean read-only to the loader — new data lands constantly. It means **existing landed rows are never modified or removed**. Concretely: - Loads **append**, typically into partitions keyed by ingestion time or by the extraction window. - A corrected or updated source record arrives as an **additional row**, carrying its own ingestion timestamp and, where available, a source-side change timestamp and operation type. - Downstream logic decides which version is current — picking the latest per business key — rather than the loader deciding by overwriting. - Re-running an extraction for the same window writes a new, identifiable batch; deduplication is a downstream concern, not something the raw layer hides. This is what makes rebuilds deterministic. If raw is mutable, re-running the same logic tomorrow can produce a different answer than it did today, and every reconciliation becomes an argument about whether the input changed. ## The audit and reconciliation payoff When finance disputes a revenue figure, the useful question is not "what does the model say" but "what did the source actually send us, and when did it arrive". An immutable raw layer answers that directly: you can show the arriving rows, their ingestion time, and the exact logic applied. Pipelines without it can only show the conclusion. This is also what makes an idempotent reload safe — you can prove that reprocessing a window did not change what was received, only how it was interpreted. ## The costs, honestly Raw retention is not free. Storage grows monotonically, and for high-volume event data it can dominate. Nobody should keep everything forever by default. Sensible practice is a **tiered retention policy**: full fidelity for the window in which you would realistically restate (often a year or two), then compaction, aggregation or cold-tier archival beyond it, driven by an actual restatement policy rather than by habit. The genuine tension is legal. Erasure obligations require that a specific person's data can be removed on request, which collides with "never modify landed rows". Real platforms resolve this with a deliberate, narrow exception path — targeted deletion or crypto-shredding applied to the raw layer under an audited process — rather than by pretending the conflict does not exist. Expect a senior interviewer to probe exactly there. ## Where the line sits Raw means *source-shaped*, not *unprocessed by any means*. Decompression, decoding, parsing into a tabular structure, adding ingestion metadata and masking restricted fields all happen on the way in. What must not happen is anything that loses information: no deduplication, no filtering out "bad" rows, no type coercion that silently nulls unparseable values, no business rules. If a transformation would make a future question unanswerable, it belongs downstream.

  • How do you reconcile an immutable raw layer with a right-to-erasure request?
    You define a narrow, audited exception rather than pretending the conflict away. Options are targeted deletion of that subject's rows across raw partitions, or crypto-shredding — encrypting personal fields per subject and destroying the key. Either way, erasure runs as a controlled, logged process outside the normal pipeline, and downstream models are rebuilt afterwards.
  • How long should raw data actually be retained?
    Long enough to cover the window in which you would realistically restate published numbers — commonly one to two years — then tiered down through compaction, aggregation or cold archival. Drive it from a stated restatement and audit policy, not from habit, because for high-volume event data indefinite full-fidelity retention can outgrow the compute bill.
  • Does keeping raw remove the need for data quality checks at load time?
    No. Raw retention preserves your ability to fix things later; checks tell you something needs fixing now. Run validation against the landed data — row counts, freshness, schema drift, key nullability — and let it gate downstream models. The difference is that a failed check quarantines and alerts rather than silently discarding rows in flight.

saying these in an interview costs you the question

  • Assumes the source can always be re-read for history
  • Overwrites landed rows when a record changes
  • Filters or deduplicates on the way into the raw layer
  • Keeps raw forever with no retention policy
  • Cannot say what immutability means beyond 'do not delete'

context

resolving links…