ETL or ELT for a mid-sized team, and how to tell which you need

The modern answer is ELT: load raw data into the warehouse first, transform it there with SQL. Storage is cheap, compute is elastic, and transformation logic in SQL is easier to review than a pipeline in someone's ETL tool.

That answer is right often enough to be a reasonable default. It is not right always, and the cases where it fails are predictable.

The actual difference

Both patterns extract from sources and land analyzable data. The question is where transformation happens and what is stored along the way.

ETL transforms before loading. The warehouse receives data already shaped. Raw data either is not retained or lives outside the warehouse.

ELT loads raw, then transforms inside the warehouse in layers. Raw data is retained, so you can reprocess when a transformation turns out to be wrong.

That last property is why ELT won for analytics. Transformations are always wrong eventually, and reprocessing from retained raw data is far cheaper than asking a source system for two years of history again.

Constraint	Pattern	Why
Regulated data that must not land raw	ETL	Masking or tokenizing before load is a hard requirement, not a preference
Analytics on business data, sources cooperative	ELT	Retained raw data makes reprocessing cheap and mistakes recoverable
Source can only export nightly files	Either	The batch window decides freshness, not the pattern
Sub-minute freshness required	Neither, streaming	Both patterns are batch-shaped; forcing them here produces a fragile system
Warehouse compute is the budget constraint	ETL	Transform where compute is cheaper than warehouse compute
Transformation logic changes frequently	ELT	SQL in version control beats reconfiguring a pipeline tool

The bill nobody forecasts

ELT moves cost from engineering time to warehouse compute, and warehouse compute is metered.

The pattern that produces surprise invoices is straightforward: a transformation layer that fully rebuilds from raw on every run, scheduled hourly, over a table that grows. It works fine in month one and costs real money by month six, and nothing in the code changed.

Three habits keep this under control:

1. Incremental models by default. Full rebuilds should be a deliberate choice with a reason, not the default that nobody revisited.
2. Partition and cluster on what you filter by. Most warehouse cost is scanning data the query did not need.
3. Attribute cost per model. If you cannot see which transformation costs the most, you cannot optimize the one that matters.

Where teams actually get stuck

In practice, neither pattern is the hard part. Three other things are.

Extraction from uncooperative sources. A source with no API, no change-data-capture, and no reliable updated-at column determines your entire design. You will either poll and diff, drop files, or read a replica. This is the real work, and it is why an anticorruption layer sometimes belongs in front of the source.

Schema drift. Someone adds a column, renames one, or changes a type upstream. Without contract tests on the source, this surfaces as a silently wrong dashboard rather than a failed job.

Ownership of the semantic layer. "Active customer" means one thing to finance and another to sales. That disagreement is not solved by either pattern; it is solved by naming an owner for each definition. Pipelines that skip this produce numbers that are all defensible and mutually inconsistent.

A reasonable default for a mid-sized team

If nothing in the constraint table forces your hand:

• Load raw into the warehouse, retained, partitioned by ingestion date.
• Transform in SQL in version control, with layers separating raw, cleaned, and business definitions.
• Run tests on the output tables, not the pipeline: uniqueness, not-null, referential integrity, row-count variance.
• Alert on freshness, not just on failure. A job that silently stops running looks identical to one with no new data.
• Mask or tokenize regulated fields at ingestion, before they land, which is where this quietly becomes ETL for a subset of columns.

That last point is the practical answer for most regulated environments: ELT for the majority of the data, ETL for the columns that cannot land raw. Which columns those are, if you handle health data, follows from the technical safeguards.

If the "pipeline" being proposed is really a bot reading screens because the source has no interface, that is a different decision entirely: see RPA versus API integration.

This is the substance of our data and AI operations work, and how we handle access to client data is written down in our security practices.