ELT (extract, load, transform) is a data engineering pattern in which data is extracted from source systems, loaded in raw form into a central analytical platform, and only then transformed inside that platform using its own compute. The order of the last two steps is what separates it from classic ETL.
ELT became the dominant approach with cloud data warehouses and lakehouses, which provide elastic storage and massively parallel SQL engines. Moving the transformation work into the target lets teams keep a full raw history, rebuild models at any time and express business logic mostly in SQL under version control.
How the ELT process works
In the extract step, connectors read data from operational databases, SaaS applications, files and event streams. Extraction is either a full snapshot or incremental, based on timestamps, monotonically increasing keys or change data capture (CDC) from database logs.
In the load step, records land almost unchanged in a raw or staging layer, often with technical columns such as load time and source file. Loading is intentionally simple, so failures are rare and reprocessing is always possible from the stored raw data.
In the transform step, SQL or dataframe jobs running inside the warehouse clean, join, deduplicate and aggregate the raw tables into curated models for reporting, data science and applications. Many teams organize these layers as bronze, silver and gold or as staging, intermediate and mart models.
The table below shows what each stage typically produces and how often it runs.
| Stage | Main activity | Typical output | Typical frequency |
|---|---|---|---|
| Extract | Read source changes | Batches or CDC events | Every few minutes to daily |
| Load | Append raw records | Raw or bronze tables | Same as extraction |
| Transform: cleaning | Types, deduplication, renaming | Staging or silver tables | Hourly to daily |
| Transform: modeling | Joins, metrics, dimensions | Marts or gold tables | Hourly to daily |
ELT compared with ETL
The essential difference is where and when transformation happens. ETL transforms data on a separate processing server before loading, so only the cleaned result reaches the warehouse. ELT loads first and transforms with the warehouse engine, keeping the raw data available.
| Aspect | ELT | ETL |
|---|---|---|
| Transformation location | Inside the target platform | Separate processing engine |
| Raw data retention | Kept by default | Often discarded after transformation |
| Main language | Mostly SQL | Tool-specific or general-purpose code |
| Scaling model | Elastic warehouse compute | Capacity of the ETL server |
| Time to new data | Fast to land, model later | Slower, mapping required first |
| Sensitive data handling | Raw data enters the target | Can be masked before loading |
Neither pattern is universally better. ETL remains appropriate when sensitive fields must never reach the analytical store, when the target has little compute, or when heavy non-SQL processing is needed before loading. Hybrid designs that mask or filter during load and model afterward are common.
Building blocks of a modern ELT stack
An ELT platform combines several components, each with a narrow responsibility. Treating them separately makes it easier to replace one tool without rewriting the pipeline.
| Component | Responsibility | Key design concern |
|---|---|---|
| Ingestion connectors | Extract and load sources | Schema drift and API limits |
| Storage layer | Warehouse, data lake or lakehouse | Partitioning and cost |
| Transformation framework | Versioned SQL models and tests | Dependency order and reuse |
| Orchestrator | Schedules and retries jobs | Idempotent, restartable runs |
| Data quality checks | Tests for nulls, uniqueness, freshness | Clear failure thresholds |
| Catalog and lineage | Documents tables and dependencies | Ownership and discoverability |
Good practice is to make every transformation idempotent, so rerunning a job for the same period produces the same result. Incremental models that process only new or changed rows reduce cost, but they need a reliable watermark and a periodic full refresh to repair drift.
Choosing ELT and controlling its costs
ELT fits best when the target platform has scalable compute, when requirements change often and when analysts are fluent in SQL. The decision table summarizes typical situations.
| Situation | Recommended approach | Reason |
|---|---|---|
| Cloud warehouse, evolving questions | ELT | Raw history allows new models later |
| Personal data under strict rules | ETL or masked ELT | Limit exposure before loading |
| Heavy image or text processing | ETL or external jobs | SQL engines handle it poorly |
| Many SaaS sources, small team | ELT with managed connectors | Low maintenance ingestion |
| Sub-second operational updates | Streaming pipeline | Batch ELT adds minutes of latency |
The main operational risk is runaway compute spending, because every transformation runs on billed warehouse resources. Teams control it with incremental processing, clustering or partitioning, scheduled rather than continuous refreshes and monitoring of query cost per model.
Common mistakes in ELT pipelines
- Treating the raw layer as a dumping ground without ownership, retention rules or documentation, which turns a data lake into a swamp.
- Ignoring schema drift, so a renamed source column silently produces nulls in downstream models.
- Skipping deduplication of at-least-once loads, which inflates counts and revenue figures.
- Loading personal data unmasked into widely shared raw schemas, creating compliance exposure.
- Building long chains of views that recompute everything on each query instead of materializing key models.
Put simply
ELT is a way of collecting data first and tidying it later. Instead of cleaning everything before it is stored, a company copies the data as it is into one large, powerful storage system and then uses that system to sort, combine and summarize it.
It is like moving into a new house by bringing every box inside first and unpacking room by room afterward. Nothing is lost on the way, and if plans change, the original boxes are still there to unpack differently.
In practice
An online retailer with a three-person data team needs daily sales reporting from its order database, payment provider, advertising platforms and customer support tool. Managed connectors load raw tables into a cloud warehouse every hour, using change data capture for the order database.
Versioned SQL models then deduplicate orders, convert currencies, join payments with refunds and build a gold table of net revenue by day, channel and product. Automated tests check that order identifiers are unique and that data is never more than two hours old.
When marketing later asks for revenue attributed by campaign, the team writes one new model on top of the existing raw advertising data. Because the history was already loaded, the analysis covers the past two years immediately instead of starting from the day the request arrived.