ETL/ELTGENERALENGINE-SPECIFICTOOL-SPECIFIC

Where the Transformation Actually Runs

In the source, in a dedicated cluster, in the warehouse, in the query at read time, or in the BI tool. Each placement moves cost, freshness, testability and governance somewhere different.

What actually happens How to build it Can I trust it?▶ Run the lab

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.

The question

A number needs filtering, joining and aggregating before a human sees it — so which machine should do that work, and what does putting it there decide?

Who needs this

Whoever has to defend the number. If the definition lives in a versioned model, they can point at a commit. If it lives in a BI tool's calculated field, they can point at a screenshot, and the person disputing the number has a different screenshot.

What one row is

The unit here is one transformation step, not a pipeline: a single filter, join, cast or aggregate. Different steps of the same logical pipeline legitimately run in different places — a privacy filter at the source, a join in the warehouse, a currency conversion in the BI layer — and treating placement as a per-pipeline decision is what produces the two failure shapes at the ends of the spectrum.

The obvious build

Put the transformation wherever it was easiest to add it. A filter goes into the extract query because that is where the SQL already was. An aggregate goes into the dashboard because the analyst had the dashboard open. Nobody decides; the code accretes where the person was standing.

Why it breaks

The same metric is now computed in three places with three slightly different definitions, and the finance number, the product number and the executive number disagree by a few percent, permanently (Two Dashboards, Two Numbers).

How it breaks with real data

The same metric is now computed in three places with three slightly different definitions, and the finance number, the product number and the executive number disagree by a few percent, permanently (Two Dashboards, Two Numbers).
A filter added to the extract query means the raw layer no longer contains the excluded rows. The filter looked like a transformation and was actually a permanent narrowing of history (Keeping Raw History: The Recovery Position and the Liability).
A calculated field in the BI tool applies a business rule that no test, no model and no lineage graph can see. When it is wrong, the upstream investigation finds nothing, because nothing upstream is wrong (Where Did This Number Come From?).
A transformation was pushed into the source database "because it is only a small aggregate", and it now runs against the production OLTP system every fifteen minutes, competing with customer traffic (Workload Isolation).
A view stack computes the full chain at read time. It was fine with four dashboards and is now the largest single line of warehouse spend, and nobody can say which dashboard caused it (Cost Attribution).
A transformation runs in an external cluster that the data team owns and the analytics team cannot read. Every definition change is a ticket, so analysts stop asking and reimplement the logic downstream (Data Ownership).

Source→Ingestion→Raw→Transformation→Validation→Storage Model→Serving→Consumer→Observability

What is actually happening

Every transformation consumes compute somewhere, and "somewhere" is a real machine with an owner, a budget, a scaling behaviour and an access model. Moving the transformation moves all four (Separating Storage from Compute).
It also moves *when* the work happens. Transform-at-write does the work once per row and pays for it once; transform-at-read does the work once per query and pays per reader. Which is cheaper is entirely decided by the read-to-write ratio, and it is a ratio people almost never look up (Compute Waste).
Testability is a function of the language and the substrate. A transformation in version-controlled SQL or Python can have a test with fixed inputs and an expected output. A transformation expressed as a BI tool calculated field, or as a connector's configuration checkbox, usually cannot (Data Tests).
Governance follows placement in a way people find surprising. A filter *before* data crosses a boundary is prevention. The same filter after the crossing is an access control, which is a weaker claim about a system with more people in it (Data Classification).
Pushdown blurs the picture usefully. A query engine can push filters and column projections into the storage layer, so a transformation written at read time may physically execute at the source — which changes the cost and none of the governance (Predicate Pushdown, Projection Pushdown).
Freshness is decided by the *last* place work happens before the consumer sees it. A pipeline that materialises hourly and then computes in the BI tool is hourly; a pipeline that loads continuously and computes in a view is as fresh as the load, and as expensive as the number of people looking (Cost vs Freshness).

Five places, and what each placement decides

The spectrum runs from the source system to the reader's screen, and it is genuinely a spectrum: each step moves the work later, closer to the consumer, and further from the controls. Nothing about it is a ranking. A well-run platform uses at least three of these five deliberately.

Read the table by column rather than by row. The cost column tells you who pays and how the bill scales; the freshness column tells you what a consumer can ask for; the testability column tells you what happens when the number is disputed; and the governance column tells you whether a boundary is being enforced or merely described.

The two ends deserve special attention because they are the two failure shapes. Everything in the source is how analytics takes production down. Everything in the BI tool is how an organisation ends up with four revenue numbers and no way to reconcile them (Data Engineering Anti-Patterns).

Where it runs	What belongs there	Cost lands on	Freshness	Testability	Governance
In the source system	Boundary filtering, column projection, redaction of fields that must not leave.	Operational capacity — the most expensive compute in the company, sized for user latency.	As fresh as the source, because there is no hop yet.	Hard: the code lives in an extract query or a source-side view, often outside the data team's repository.	Strongest available. What is removed here never crosses the boundary at all, so no downstream access model can expose it.
In a dedicated compute cluster	Parsing proprietary formats, external enrichment, model inference, work SQL expresses badly.	Provisioned capacity held for a peak, charged whether or not anyone reads the result.	One batch behind, set by the cluster's schedule.	Good: ordinary application code with ordinary tests, if the team treats it that way.	Yours to define — which means it is only as governed as you made it, and an external runtime writing straight to serving is a lineage hole.
In the warehouse, materialised	Joins, aggregates, conforming, modelling — the bulk of analytical transformation.	Warehouse compute at build time; readers pay only to scan the result.	One build interval behind, tunable per model.	Best: version-controlled SQL with tests, lineage and a documented owner (dbt Concepts).	Good, provided the catalog knows about the model and the raw layer it derives from is access-controlled.
In the query, at read time	Cheap projections, per-user filters, anything whose freshness requirement exceeds the build interval.	Warehouse scan on every read — scales with reader count, not with data volume.	Maximal: the answer is as of the query.	Moderate: the SQL is reviewable if it lives in a view, and invisible if it lives in an ad-hoc dashboard query.	Depends entirely on whether the view is catalogued. A view is a governed object; saved dashboard SQL is not.
In the BI tool	Formatting, display units, chart-local sorting. Not business rules — although that is where they go.	Usually warehouse scan under a shared service account, attributed to nobody.	Whatever the layer beneath it provides, described to users as live.	Worst: typically no diff, no test, no review, and no way to enumerate every place a rule was copied.	Effectively none. Invisible to lineage, so impact analysis for a schema change or a deletion request cannot see it.

One metric, computed in three places

TOOL-SPECIFICWhether the BI layer can be made to select rather than compute depends on the tool: some expose a governed semantic layer that models can populate, while others make per-dashboard calculated fields the path of least resistance, in which case the drift is a property of the tool choice rather than of the team's discipline.

Definitional drift is easier to see than to describe. Below is one metric — recognised revenue for a period — written the way it tends to appear in a real platform: once in a model, once as a dashboard's custom SQL, and once as a calculated field. Every version was written by a competent person and every version is defensible on its own.

They differ in three ways, none of which is obviously wrong at the point of writing. The model excludes refunded orders; the dashboard SQL was written before refunds existed and never revisited; the calculated field converts currency using a rate the BI tool holds, which is not the rate the finance system uses. Three numbers, three defensible authors, one meeting where nobody can win.

The fix is not "ban SQL in dashboards" — that fails, because analysts have real needs and will route around a rule that blocks them. The fix is to make the model the *easiest* place to get the number from: name it, document its grain, expose it, and make adding to it fast enough that nobody has a reason to recompute it.

Where the definition of a metric lives

Wherever it was convenient

Each consumer computes the metric where they work — analysts in dashboards, engineers in models, finance in a spreadsheet fed by an export. Every version is correct for its author and none is authoritative.

One versioned definitional home, selected from everywhere else

The metric is defined once in a model or semantic layer, with its grain, its filters and its owner documented. Dashboards select from it. Exports read it. A change to the definition is a reviewable commit with a known blast radius.

Consistency between consumers is not achievable by agreement, only by construction: as long as two systems can each compute the number, they will eventually compute it differently, because business rules change and only one copy gets updated. Centralising the definition makes drift structurally impossible rather than merely discouraged (Data Contracts).

The same metric, three homes, three answers

1-- 1. In the model, version-controlled, tested, catalogued.
2--    One definitional home. Refunds excluded explicitly.
3SELECT order_date,
4       SUM(amount_eur) AS revenue_recognised
5FROM   fct_orders
6WHERE  status IN ('shipped','delivered')
7GROUP  BY order_date;
8 
9-- 2. In a dashboard's custom SQL, written before refunds existed.
10--    Nothing is wrong with it. It is simply older than the business.
11SELECT order_date, SUM(amount_eur) AS revenue
12FROM   fct_orders
13WHERE  status <> 'cancelled'
14GROUP  BY order_date;
15 
16-- 3. In the BI tool, as a calculated field, applying its own FX table.
17--    SUM([amount] * [bi_fx_rate])
18--    -- no diff, no test, no lineage edge, no owner

What to notice is not that one is right. It is that only the first can be diffed, tested, or found by an impact analysis when status gains a new value — and that the third does not appear in any query the platform can inspect, only in the tool's own definition store.

Choosing a placement for one transformation

Ask this per step, not per pipeline. A single logical flow can and should place its steps differently: redact at the source, parse in a runtime that has the parser, join and aggregate in the warehouse, format in the BI tool. Placing everything in one layer is a symptom of the decision never having been made.

Two questions dominate the rest. Does this step enforce a boundary — because if so it belongs before the boundary and nowhere else. And what is the read-to-write ratio — because that single number decides materialise versus compute-at-read for every model in the platform, and it is available from the query logs of any warehouse.

The cost shape below is the arithmetic behind that second question, drawn relative rather than absolute. The teaching is the crossover: a transformation read rarely should stay a view, the same transformation read constantly should be a table, and no principle other than the ratio decides it.

How the cost of one transformation scales with where it runs

Read-time compute in a heavily-read view chain

Scales with reader count and with the depth of the chain. Two hundred morning dashboard loads pay for the whole lineage two hundred times.

Provisioned external cluster held at peak

Insensitive to whether anyone reads the output. The classic shape of a dedicated transformation runtime.

Materialised build over unchanged history

Scales with dataset age rather than change rate. Incrementality is the lever, not a bigger warehouse (Incremental Processing).

Materialised incremental build

Scales with what actually changed, which is the target shape for anything read more than it is built.

Source-side projection and filter

Cheapest in compute and most expensive in optionality — every row it removes is a row nothing downstream can ever see.

Relative weights, not measurements. The ordering is what transfers between platforms; the magnitudes do not, and this domain never publishes a price.

Relative weights to show the ordering and the crossover, not measurements. Read it as: placement changes what the cost scales *with* — readers, provisioned hours, history age, or change rate — and choosing a placement is choosing which of those four your bill follows.

Where does this transformation step belong?

Take one step — a filter, a join, an aggregate, a cast. What does it need that only one placement provides?

The source system

when The step exists to stop data crossing a boundary, or to reduce volume so drastically that moving the rest is impractical.

cost Load on operational capacity, and code that lives outside the data team's repository and review process. Keep it small, keep it a projection or a redaction, and never let it grow into modelling.

A dedicated compute runtime

when The step needs a parser, an external call, a model, or stateful logic that SQL expresses badly.

cost A second system to operate and scale, held at peak capacity. Write its output back as a catalogued dataset so the lineage graph is not broken by it.

The warehouse, materialised

when The step is set-oriented, its result is read more often than it is built, and its freshness requirement is coarser than the build interval.

cost Build compute and a stale window. The default answer for most analytical transformation, and correct for exactly that reason (The Transformation DAG).

The warehouse, at read time

when Freshness must exceed the build interval, the step is cheap, or the result is read rarely enough that building it is waste.

cost Scan on every read, scaling with reader count. Verify the engine actually pushes the predicate down, because a wrapped partition column silently turns this into a full scan.

The BI tool

when The step is genuinely presentational: display units, chart-local sorting, number formatting.

cost Invisible to lineage and tests. Anything that would change a metric belongs upstream, and the boundary between "presentation" and "business rule" is exactly where this goes wrong.

How to build it

Most important first.

Push filters and projections as early as possible and push business meaning as late as is safe. Early filtering reduces everything downstream; early business rules bake a definition into history before anyone has argued about it (Source Pushdown).
Give every metric exactly one definitional home, and make it a versioned model. The BI tool should select from it, not recompute it. This is the single change that eliminates most "why do these two numbers differ" incidents (The Metrics Layer).
Never run analytical transformations against the operational source unless the aggregate is tiny and the schedule is generous. The failure is not a slow query; it is a slow application (OLTP vs OLAP).
Decide materialise-versus-compute-at-read per model using the read-to-build ratio, and revisit it when a model gains consumers. A view read twice a week and a view on eighty dashboards are different objects with the same syntax (Model Layering).
Where a transformation must run outside the warehouse — a parser, an external call, a model — put its *output* back into the platform as a first-class dataset with lineage, rather than letting it write directly into a serving table (Data Lineage).
Audit the BI layer periodically for logic. Calculated fields, custom SQL in a dashboard and spreadsheet exports are where definitions go to escape governance, and the only way to find them is to look (Data Platform Anti-Patterns).

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.

Transform-at-write guarantees every reader sees the same value, because the value was computed once. Transform-at-read guarantees only that every reader ran the same code — and only if they did.
A transformation in the source guarantees the destination never receives what it removed. Nothing downstream can weaken or bypass that, which is why it is the placement of choice for boundary enforcement.
A transformation in the BI tool guarantees nothing to anyone but that dashboard. It is invisible to lineage, to tests, to the catalog, and to the next person who builds a similar dashboard (Column-Level Lineage).
Pushdown is an optimisation, never a guarantee. Whether a predicate reaches the storage layer depends on the engine, the format, the statistics and the expression, and a function wrapped around a partition column silently disables it.

Can I trust it?

A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.

The check that would catch this

The check that exposes bad placement is a definition audit: take one metric, enumerate every place it is computed, and compare the expressions. Finding more than one is the finding; finding three that differ is the incident you were going to have next quarter.
A second check compares a metric computed in the model against the same metric computed in the BI tool for the same period, on a schedule. A drift alarm here catches a calculated field that someone edited (The Data Quality Dashboard).
They miss transformations you did not know to look for — a spreadsheet, a notebook, a scheduled export with a WHERE clause in it. Nothing automated will find those, and every organisation has them (Data Discovery).

Freshness

Read-time transformation is maximally fresh: the answer reflects the underlying data as of the query. That is the strongest freshness available and it is paid for on every single read (Scan Cost).
Write-time transformation is stale by exactly one build interval and costs the same regardless of how many people read it. For a dashboard opened by two hundred people each morning this is not a close comparison.
A transformation in the source is as fresh as the source and as risky as the load it places there. Freshness bought this way is bought from the application's latency budget (Latency Budgets: Spending 200 Milliseconds on Purpose).
Mixing placements sums their intervals. Materialised hourly, then aggregated in the BI tool at read: the consumer sees hourly data, computed live, and is frequently told the dashboard is real-time because the last step was (Freshness Monitoring).

When the schema or meaning changes

A definition change is easy where the definition has one home and impossible to complete where it has five. Placement therefore decides the cost of every future business rule change, which is a cost nobody estimates at design time (Semantic Changes).
Moving a transformation earlier — from the BI tool into a model, say — changes history unless you rebuild it. The new number and the old number will differ, and the difference will be attributed to the migration whether or not the migration caused it.
A source schema change breaks transformations in proportion to how far upstream they run. A filter in the extract breaks first and loudest; a calculated field in a dashboard breaks last and silently (Schema Evolution).

How to re-run this safely

A transformation that runs where its inputs are retained is re-runnable. One that runs where its inputs are transient — inside an extract query, inside a streaming operator with no replay, inside a dashboard session — is not (Reprocessing vs Retrying).
Recovering from a wrong BI-layer definition means finding every dashboard that copied it, which is a manual search with no completion criterion. This asymmetry is the strongest practical argument for centralising definitions (Impact Analysis).
Moving a transformation is itself a change that needs a backfill plan: rebuild history under the new placement, compare against the old, and publish both for a period so consumers can see the delta (Validating a Backfill Before You Publish).

What can go wrong

Failure modes

The same business rule implemented independently in a model and in a dashboard, drifting apart over a year of small edits.
An analytical aggregate scheduled against the production database, discovered during an application latency incident (OLTP Workloads).
A view chain that is cheap to write and expensive on every read, growing with dashboard count rather than data volume.
The mitigation failing: a metrics layer adopted as the single definitional home, with three dashboards still using their old calculated fields because migrating them was somebody's ticket.
A transformation in an external runtime writing straight into a serving table, so the lineage graph shows a table with no upstream and the incident walk stops there (Lineage Debugging).
Pushdown silently not happening — a cast or a function around a partition predicate — turning a read-time transformation from cheap to full-scan without any change in the code that broke it (Partition Pruning).

Misreads

"The warehouse is where transformation belongs." It is where most *set-oriented* transformation belongs. Boundary filtering belongs before the boundary and format parsing belongs where the parser is (ETL: Transform Before the Data Lands).
"A view is free." A view is free to create and charged on every read. Free-to-create and free are different properties, and the second one is what the bill responds to.
"BI-layer logic is just presentation." Currency conversion, deduplication, filtering out test accounts and defining "active" are all business logic, and all of them routinely live in dashboards.
"Pushing filters down is always right." Pushing a *projection* down is always right. Pushing a *predicate* down is right for the query and wrong for the pipeline if it means the excluded rows never land anywhere.
"If it runs in the warehouse it is governed." Governance applies to what the catalog knows about. Ad-hoc SQL saved in a dashboard runs in the warehouse and is governed by nothing (The Data Catalog).

Privacy, retention and access

Placement is the difference between prevention and mitigation. Filtering before a boundary means the data is not there; masking after means it is there and policy says who may see it. Both are legitimate; conflating them in a compliance conversation is not (Data Masking, Tokenisation & Encryption).
Transformations in the BI layer are outside lineage, which means impact analysis for a deletion request or a schema change cannot see them. Every such transformation is a gap in the map you use during an incident.
A transformation running in the source has the source's access model, which is usually the strictest in the company. That is a genuine argument for putting sensitive aggregation there, and it competes directly with the argument about operational load (Least Privilege).

Operating it

How you see it in production

Bytes scanned attributed per model and per dashboard. This is what converts "the warehouse is expensive" into "this view chain, read by that dashboard".
A count of distinct expressions computing the same named metric, across models and BI assets. A rising number is the early signal of definitional drift.
Query load on the operational source attributed to analytical clients, so a transformation that migrated into the source is visible before it is an application incident (Which Signal Actually Means "The Database Is Slow" in Observability terms).
Whether pushdown occurred, from the engine's query plan, for the read-time transformations you rely on being cheap (Query Engines).

What changes at 10x and 100x

At 10x readers, every read-time transformation becomes a materialisation candidate and the arithmetic changes without anyone changing the data.
At 10x data, transformations near the source get cheaper relative to those downstream — filtering early is worth more as volume grows, which is why pushdown matters more at scale than in a demo.
At 100x, the placement question becomes a physical layout question: a read-time transformation over a well-partitioned, well-clustered table can stay affordable, and the same transformation over unpartitioned files cannot (Physical Data Layout).
At high consumer count, the governance argument overtakes the cost argument. Two hundred people writing their own transformations produce two hundred definitions, and no amount of compute fixes disagreement (Who Owns Data Quality).

What drives cost here

Read-time transformation costs per read; write-time costs per build. The crossover is the read-to-write ratio, and it is the only number needed to decide materialisation for a given model.
Transformation in the source costs operational capacity, which is the most expensive compute in the company because it is sized for user-facing latency (Capacity or Efficiency: Which Problem Are You Solving?).
Transformation in a dedicated cluster costs provisioned capacity held for a peak, and the cost is insensitive to whether anyone reads the output (Idle Capacity: Headroom or Waste?).
Transformation in the BI tool usually costs warehouse scan, because the tool pushes a query down. It appears in the warehouse bill under a service account and is therefore attributed to nobody.

What this approach costs

Centralising definitions in models buys consistency and costs analyst autonomy: every new metric becomes a pull request, and teams that find that too slow will route around it in the BI tool where you cannot see them.
Materialising buys cheap reads and costs freshness plus a build to operate. Computing at read buys freshness and costs an unbounded, per-reader bill.
Pushing filters to the source buys the largest cost reduction available and costs the rows you filtered — which is fine for a projection and permanent for a predicate that turns out to have been wrong.

Where should this transformation live?

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 should this transformation live?

Four places, and the same logic is a good idea in one of them and a running incident in another.

The logic

Put it in

workable, with a costHashing an email address you may not store → In the warehouse, as SQL

	In the warehouse, as SQL
Buys you	Version-controlled, reviewable, testable, and re-runnable over data you still have. The default for a reason.
Costs you	It is not free — the warehouse is billed for the work — and a definition here is invisible to anything that does not read the warehouse.

This is not where it belongs. The obligation is about storage, so it must be satisfied before storage. This is the case that justifies the hardest place to put code.

GENERALThe rule underneath all five cases: put a definition at the highest layer that everyone who needs it can read, and put an obligation at the earliest point that satisfies it.

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 five placements exist in every analytical stack, including ones with no warehouse: a search index, a reporting replica and a spreadsheet reproduce the same spectrum, with the same trade of per-write against per-read cost.
ENGINE-SPECIFICWhether a read-time transformation is affordable depends on the engine's pushdown and vectorisation: a columnar engine reading a partitioned, statistics-rich format can make a view chain viable, while the identical SQL over row-oriented files scans everything every time (Vectorized Execution).
TOOL-SPECIFICBI tools differ enormously in whether their semantic layer is exportable and version-controllable; some can be treated as a governed definitional home, while others keep logic in per-dashboard state that no external system can enumerate.

Where the depth lives

This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.

Databasequery-execution cost-based-optimization

Architecturelayered-architecture

Securityleast-privilege

Observabilitydatabase-performance-signals cost-per-request

Cloudidle-capacity

Domains that do not exist yet

— Observability & Performance owns why a transformation moved into the operational database shows up as application tail latency before it shows up on any data dashboard.
— DevOps / Production Engineering owns the review and deployment path that makes a versioned model a governed definition and a dashboard calculated field an ungoverned one — the difference is entirely a delivery-process difference.