Data Transformation
Clean, cast, join, aggregate, deduplicate, enrich, filter, normalize, denormalize — nine operations, each with a way of being wrong that does not raise an error.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What are the operations that turn raw arrival into a queryable table, and which of them can be wrong while every job reports success?
The analyst writing SELECT SUM(revenue) FROM fct_orders. They will never read your transformation. They will read its output and assume that one row means what its name suggests, that nothing is missing, and that nothing is counted twice.
A transformation is a function from one grain to another. Its input has a grain (one change record, one raw event, one order line) and its output has a grain (one order, one customer-day). The transformation is the place the grain changes, and the whole difficulty of this module is that nothing in SQL forces you to declare either one (Grain: What Does One Row Represent?).
Write one long SQL statement that reads the raw table, cleans it, joins in whatever it needs, filters out the rows that look wrong, and aggregates to the shape the dashboard wants. Schedule it nightly. It is one file, it is readable, and for the first six months it produces exactly the right number.
The amount column starts arriving as "1,299.00" from a new upstream export. A permissive cast turns every such row into NULL rather than raising, SUM skips nulls by definition, and revenue drops by the share of rows that came through the new export (Nullability & Defaults).
- The
amountcolumn starts arriving as"1,299.00"from a new upstream export. A permissive cast turns every such row intoNULLrather than raising,SUMskips nulls by definition, and revenue drops by the share of rows that came through the new export (Nullability & Defaults). - A join to
dim_customersstarts fanning out because a re-run inserted the same customer twice. Every order-level measure is now multiplied by that customer's duplicate count. The query is faster than usual, because a fan-out adds rows without adding work you would notice (Duplicate Rows). - The filter
WHERE status = 'completed'was correct until the application added'completed_partial'. The new status is dropped, revenue quietly excludes it, and the change that caused it was a perfectly valid application feature (Semantic Changes). - The enrichment
JOIN dim_customers ON c.customer_id = o.customer_idreads the customer's *current* tier, so a historical report changes every time a customer upgrades. Last quarter's number is different today than it was yesterday, and nothing was backfilled (Slowly Changing Dimensions). - Deduplication on
event_idlooks right and removes nothing, because the source re-emitted the same order under a fresh event id after a connector restart. The duplicates that mattered were never keyed by the column you deduplicated on (Deduplication). - Someone needs to fix a bug in the cleaning step. Because cleaning, joining and aggregating live in one statement, they cannot re-run cleaning alone, cannot test it, and cannot tell which of the three the bug is in (Model Layering).
What is actually happening
- Every transformation is one of nine operations or a composition of them. Clean normalises representation. Cast changes type. Join brings two datasets together on a key. Aggregate collapses many rows to fewer. Deduplicate removes repeats. Enrich attaches attributes from a reference dataset. Filter removes rows. Normalize splits one table into several. Denormalize merges several into one.
- Four of them change the grain — join, aggregate, deduplicate, normalize — and two more can change it accidentally. A join changes grain whenever the right-hand side is not unique on the join key, which is a property of the data, not of the SQL, and is therefore invisible in review (Grain: What Does One Row Represent?).
- The other five preserve the grain and change *meaning*, which is worse, because row counts are monitored and meaning is not. A cast that nulls, a filter that drops a category, an enrichment that reads current state — all of them leave the row count untouched (Volume Anomalies).
- SQL has no error for "this is semantically wrong". A permissive engine returns
NULLwhere a strict one raises; an inner join silently discards non-matching rows;SUMignores nulls. Every one of those behaviours is correct, documented and the direct cause of most wrong dashboards. - A transformation is only reproducible if it is a pure function of its declared inputs. The moment it references
current_timestamp, a mutable dimension, or a table it does not declare as a dependency, re-running it next month produces a different answer and the old answer cannot be recovered (Idempotent Data Pipelines).
Nine operations and how each one lies
Transformation looks like a large space of possibilities and is actually a short list. Almost every model in almost every warehouse is a composition of nine operations, and knowing the list matters less than knowing what each one does when the data stops cooperating.
Read the third column of the table below as the actual content of this lesson. The second column is what the operation is for; the third is what it does on the day the upstream changes, and in every case the answer is "succeeds, and produces a number that is wrong by an amount nobody can see".
Notice how few of these change the row count. Volume monitoring — the cheapest and most widely deployed data check there is — catches the join fan-out and almost nothing else on this list (Volume Anomalies).
| Operation | What it is for | How it silently goes wrong | What catches it |
|---|---|---|---|
| Clean | Normalise representation: trim, case-fold, standardise codes. | Case-folding merges two genuinely distinct entities; trimming strips a leading zero that was part of a postcode or an account number. | A distinct-count test on the cleaned key compared with the raw one — a drop means you merged something. |
| Cast | Give a column the type its meaning requires. | A permissive cast returns NULL instead of raising. SUM skips nulls, so the total falls by exactly the affected share and no error is raised anywhere. | Null-rate per column per run, compared with its own history. |
| Join | Bring two datasets together on a key. | Fan-out: the right side is not unique on the key, so every left row is multiplied. Or an inner join discards non-matching left rows entirely. | Uniqueness test on the join key of the right-hand table, plus rows-in versus rows-out. |
| Aggregate | Collapse many rows to fewer at a coarser grain. | Aggregating a measure that was already aggregated upstream, or grouping by a set of columns that is not the grain you think it is. | A declared output grain with a uniqueness test on its key. |
| Deduplicate | Remove repeats introduced by at-least-once delivery or re-runs. | Keyed on the wrong column, so it removes nothing while appearing to work. Or keyed too coarsely, so it removes legitimately distinct rows. | Compare the count removed against expectation; a dedupe that never removes anything is a dedupe that is not working. |
| Enrich | Attach attributes from a reference dataset. | Reads the dimension's current state, so a historical report changes whenever the dimension does. Yesterday's number is different today. | Re-run a closed period and compare with the previously published result; divergence means the model is not a function of its inputs. |
| Filter | Remove rows that do not belong. | An enumerated value the filter does not know about is dropped. NULL fails every comparison, so status <> 'cancelled' drops rows with no status at all. | An accepted-values test on the filtered column, which fails when a new category appears. |
| Normalize | Split one wide table into several related ones. | A downstream query forgets one of the joins and undercounts, or joins in the wrong direction and fans out. | Referential integrity tests between the split tables. |
| Denormalize | Copy attributes into a fact so consumers need fewer joins. | The copy freezes at build time and then disagrees with the dimension it came from, so two tables give two answers for the same customer's country. | A reconciliation query comparing the denormalised copy against its source dimension (Reconciliation). |
The grain changes and nothing tells you
The most expensive class of transformation bug is not a wrong value. It is a right value counted the wrong number of times, and it happens because the unit a row represents changed somewhere in the chain without anyone declaring it.
A join is the usual culprit, and the reason is worth being precise about. orders JOIN customers ON customer_id preserves the order grain if and only if customers has at most one row per customer_id. That is not a property of the SQL — it is a property of today's data. The same query is correct on Monday and produces double revenue on Tuesday because a re-run inserted duplicate customers overnight.
The defence is not care. Care does not survive contact with a table you did not build. The defence is declaring the grain of every model as a testable claim: this table is unique on this key, and the build fails if it is not.
| Stage | One row is | Breaks if |
|---|---|---|
| Raw change records | One change to one order — insert, update or delete — possibly delivered more than once. | You COUNT(*) and call it orders. Three updates to one order are three rows and one order (What a CDC Event Contains). |
| Deduplicated changes | One change to one order, exactly once — assuming the dedupe key is the one the source repeats on. | The source re-emitted after a restart with a fresh event id, so the dedupe key was never the repeating one (Deduplication). |
| Staging model | One order, at its latest known state. | "Latest" was chosen by arrival order rather than by the source's commit sequence, so an out-of-order update wins and the order is stuck in a stale state. |
| Order lines joined in | One order *line* — the grain has silently changed and every order-level measure now repeats per line. | Anyone sums order_total from this model. They will get the total multiplied by the average line count, which looks like a plausible business number (Fact Tables). |
| Enriched fact | One order with dimension keys attached — back to order grain, if the dimension joins were unique. | Any one of the dimension tables has a duplicate key. One duplicate customer inflates that customer's every measure and nothing else's, which is nearly impossible to spot in an aggregate. |
| Customer metric | One customer, with lifetime measures. | It is joined back to an order-level table and re-aggregated, double counting the pre-aggregated measures. |
Six stages, three legitimate grain changes, and one accidental one. The accidental one — the order-line join — is indistinguishable from the legitimate ones without a declared grain and a test that enforces it.
A transformation is a function, or it is not reproducible
There is one property that separates a transformation you can fix from one you cannot: whether re-running it over the same inputs produces the same outputs. Call it purity, determinism or idempotency — the practical consequence is the same. If it holds, every bug is a re-run. If it does not, every bug is permanent.
The property is broken in three ordinary ways, and none of them looks like a mistake at the time. Referencing current_date inside logic rather than as a bounded parameter. Joining to a table that is mutated in place. Appending rather than replacing, so a second run adds rows instead of producing the same rows.
The third is the most common and the easiest to fix. An INSERT re-run appends; a partition replace re-run converges. That difference is the whole of idempotency in a batch transformation, and it costs one line of write configuration (Idempotent Data Pipelines).
The model appends its output with `INSERT INTO fct_orders SELECT ...`, filters with `WHERE order_date >= current_date - 7`, and joins `dim_customers` for the customer's current tier. It is correct on its first run and every run after it is a new set of rows.
The model takes an explicit date range as a parameter, writes by replacing exactly the partitions in that range, and joins the customer dimension on `order_date BETWEEN valid_from AND valid_to` so the tier is the one that was true when the order was placed.
The first version is not a function of its inputs: its output depends on when it ran and on the current contents of a mutable dimension. That means a re-run cannot reproduce a previous result, so a bug found in March cannot be repaired for February — the inputs that would have produced February's correct answer no longer exist. The second version replaces rather than appends, so running it twice converges to one state, and reads the dimension as of the event time, so the answer for a closed period is stable forever (SCD Type 2 in Practice).
How to build it
Most important first.
- Declare the output grain in words before writing the SQL, and add a uniqueness test on the key that grain implies. This one habit catches the entire fan-out class of bug, which is otherwise the most expensive one here (Data Tests).
- Split cleaning from modelling. Cleaning is per-source and mechanical — rename, cast, trim, coerce timezone. Modelling is business logic. Keeping them in different models means a cast bug is fixed and re-run without touching business logic (Model Layering).
- Cast strictly where you can afford to, and where you cannot, count what the permissive cast nulled and alert on it. A silent null is a dropped row wearing a disguise.
- Never filter on an enumerated value with
=orINagainst a list you maintain by hand unless a test asserts that the set of observed values is exactly that list. Prefer excluding what you mean to exclude, and be explicit aboutNULL—status <> 'cancelled'drops every row where status is null. - Enrich against a dimension that models history if the question is historical, and against current state only when the question genuinely means "as of now". Getting these two backwards is the most common modelling error in the field (SCD Type 2 in Practice).
- Make every transformation re-runnable to the same result: no
now()in logic, explicit input ranges, and a write that replaces a bounded partition rather than appending (Full Refresh vs Incremental).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- A transformation guarantees exactly what its tests assert and nothing else. There is no implicit promise of completeness, uniqueness, referential integrity or type sanity; the warehouse will not supply one (Data Tests).
- It guarantees determinism only if it is a pure function of declared inputs. A model reading a mutable upstream table gives no reproducibility guarantee at all, even though it will appear reproducible for as long as the upstream happens not to change.
- It guarantees nothing about ordering. A
SELECTwithoutORDER BYmay return rows in any order, and a deduplication that picks "the first row" without an explicit ordering key is choosing arbitrarily, differently on each run. - Atomicity is a property of the write, not of the transformation. Ten
INSERTstatements are ten observable states unless wrapped in a transaction or published atomically (Atomic Publish).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The two tests that pay for themselves immediately: uniqueness on the output grain key, and row count in versus row count out with an expected relationship (equal for a filter-free clean, less-than-or-equal for a filter, exactly-equal-per-key for an enrichment join).
- Add a null-rate test on every column produced by a cast. A cast that starts nulling is otherwise indistinguishable from a source that started sending fewer values.
- These miss the entire semantic class. A transformation that computes gross revenue where the definition says net will pass uniqueness, pass row counts, pass null rates, and be wrong in a way only a person who knows the business will notice (Two Dashboards, Two Numbers).
- Transformation adds one scheduling interval on top of whatever ingestion delivered. A five-minute stream feeding an hourly transformation is hourly data, and describing the platform by its fastest hop misleads every consumer who hears it.
- A transformation that reads the *complete* previous period cannot run until that period is complete, which means its freshness is bounded by the latest arriving record it must wait for, not by its own runtime (Late-Arriving Data).
- Splitting one large transformation into layered models does not slow the chain down meaningfully — the layers run back-to-back in one build — but it does make the chain's freshness visible per model instead of as one opaque number.
- A new value in an enumerated column is a schema-compatible change that breaks a filter. This is the single most common way a correct upstream change produces a wrong downstream number (Semantic Changes).
- A widened type upstream — integer to decimal, or a numeric exported as a formatted string — passes ingestion and lands in a cast that must now decide between raising and nulling. Decide in advance which one you want; the default is usually the wrong one.
- A column rename upstream breaks a transformation loudly, which is the good case. A column whose *meaning* changed while its name and type stayed the same breaks nothing and corrupts everything downstream of it (Data Contracts).
- A transformation bug is recoverable exactly as far back as the earliest untouched input. If raw is retained and the transformation is deterministic, the fix is a re-run over a bounded range (Keeping Raw History: The Recovery Position and the Liability).
- If the transformation wrote in place over its own input — the classic "clean the raw table" — the fix is not available at any price. That is the argument for a raw layer, and it is the only argument it needs (The Raw Landing Zone).
- Re-running a transformation whose logic references current state does not reproduce history; it produces a *new* answer for an old period. Before backfilling anything, check whether the model is a function of its inputs or of the calendar (What Backfills Break).
What can go wrong
- A permissive cast nulls a column and
SUMsilently skips it, so the metric falls by exactly the share of affected rows. - A join fans out because the right-hand table lost uniqueness, multiplying measures without any error.
- A filter on an enumerated column silently drops a new category that the application added last week.
- A deduplication keyed on the wrong column removes nothing and is trusted because it exists.
- An enrichment reads current dimension state, so historical reports change every time a dimension does.
- The mitigation itself fails: a uniqueness test written on a surrogate key rather than the business key passes forever while duplicates accumulate (Surrogate Keys).
- "The transformation ran, so the data is transformed." It is transformed. Whether it is *correct* is a separate question with a separate answer, and only tests can supply it (The Pipeline Succeeded. The Data Is Wrong.).
- "A join cannot lose rows." An inner join discards every left row without a match, silently. A left join preserves them and produces nulls that then vanish in aggregates. Both are correct SQL and both change the number (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
- "Cleaning the data early is efficient." Cleaning early is fine; cleaning in place destroys the evidence you need when the cleaning turns out to be wrong. Land raw, clean into a new model (The Raw Landing Zone).
- "We do not need to declare grain, it is obvious from the SQL." It is obvious only when the data cooperates. Grain is a claim about uniqueness, and a claim that is not tested is a hope (Grain: What Does One Row Represent?).
Operating it
- Null rate per column per run, charted against its own history. It is the cheapest detector of the cast failure and it costs one aggregate query (Distribution Tests).
- Rows in and rows out for every model, per run. A fan-out shows as an output count that grew without an input that grew.
- The set of distinct values in every column a filter references, compared with the previous run. A new value appearing is not an error, but it is always worth a human look (Data Observability).
- At 10x volume, full rebuilds stop fitting in their window and the transformation must become incremental — which introduces watermarks, late data and a whole new failure class (Incremental Processing).
- At 100x, join strategy stops being the optimiser's problem and becomes yours: broadcast versus shuffle, and whether the skewed key needs salting (Broadcast Joins).
- Model count scales worse than data volume. Two hundred models with implicit dependencies is not a bigger version of twenty; it is a different problem, and it is the one the dependency graph exists to solve (The Transformation DAG).
- The dominant driver is bytes scanned by the transformation itself, which is decided by whether it reads a bounded partition or the whole history (Scan Cost).
- Joins drive shuffled bytes, and shuffled bytes grow super-linearly with skew because one key's share lands on one worker (Data Skew).
- Repeating work is the quiet driver: rebuilding all of history nightly costs proportional to history rather than to the day that changed, and nothing in the bill says which one you are doing (Compute Waste).
- Splitting one statement into layered models costs more objects, more storage and more names to agree on. It buys the ability to test, re-run and fix one step in isolation, which is what makes any of this maintainable past the first bug.
- Strict casting costs failed runs — loudly, at inconvenient hours. Permissive casting costs silent nulls. There is no third option, and the loud one is cheaper.
- Every test added is runtime spent on every build. A model with forty assertions is slower and more trustworthy, and the right number is decided by what a consumer would actually notice, not by completeness (Data Quality).
Transformation fault lab
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- GENERALThe nine operations and their silent failure modes are properties of relational transformation, not of a tool. What changes between stacks is which of them the engine makes loud — some raise on a bad cast, most return null.
- ENGINE-SPECIFICCast strictness differs sharply: some engines raise on a malformed numeric string while others return null, and the same SQL therefore produces an error in one warehouse and a silently reduced total in another. Check your engine's cast semantics before relying on either behaviour.
- SIMPLIFIEDPresenting transformation as nine discrete operations is a teaching frame; a real model composes several of them in one statement, which is precisely why the failure is hard to attribute after the fact.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns how transformation code is reviewed, versioned, tested in CI and rolled back. A model is software and deserves the same delivery discipline; that domain is being built separately.