ELT — Extract, Load, Transform — is the data integration pattern in which raw data is extracted from source systems, loaded directly into the target analytical platform in its raw form, and transformed within the target platform’s compute engine rather than in an intermediate ETL server. The reversal of the T and L is not cosmetic: it reflects a fundamental shift in where the transformation compute runs. In traditional ETL, a dedicated ETL server transforms data before it reaches the warehouse — constraining the transformation compute to the ETL server’s resources. In ELT, transformation runs inside the cloud data platform (Snowflake, BigQuery, Azure Synapse, Databricks) where the compute is elastic, the data is already co-located with the storage, and transformations are expressed in SQL that the platform’s native query engine optimises.
How It Works
| Characteristic | ETL | ELT |
|---|---|---|
| Transform location | ETL server / middleware | Target data platform |
| Raw data availability | Not typically stored pre-transform | Raw data stored in landing/raw zone |
| Transformation language | Platform-specific (SSIS, Informatica, DataStage) | SQL (typically through dbt, Spark SQL, or native platform SQL) |
| Scalability | Limited by ETL server capacity | Elastic — scales with the data platform’s compute |
| Re-transformation | Requires re-running the full ETL pipeline | Re-run SQL transformations against stored raw data |
| Best for | Complex transformations requiring procedural logic not expressible in SQL; strict data security requirements for in-transit data | Cloud-native architectures; SQL-expressible transformations; environments requiring raw data reprocessing |
dbt (data build tool) has become the dominant transformation framework for ELT pipelines — providing a SQL-based transformation layer with dependency management, testing, documentation, and incremental materialisation built in. In finance BI, dbt models translate raw GL extracts, EPM exports, and HR system outputs into the dimensional model structures that BI reports consume.
Design Decisions and Trade-offs
ELT’s raw data retention is both an advantage and a cost consideration. Storing raw source data in the landing zone means transformations can be re-run against the original data if logic changes — but it also means raw financial data (journal lines, payroll records) is resident in the analytical platform with the access controls applied to the platform, not to a hardened ETL server. The security model for raw data in the landing zone must be explicit: who can query raw tables, how long raw data is retained, and whether raw data must be anonymised before being accessible to non-privileged analysts.
Common Implementation Errors
The specific ELT error that produces brittle, unmaintainable transformation layers is building the transformation logic directly in the BI tool’s query layer — Power BI M queries or Power Query — rather than in the data platform. When transformations (joins, aggregations, business rule calculations) live in the BI layer rather than the data platform, they execute on the BI server for every report refresh, they are invisible to the data platform’s query optimiser, and they cannot be tested or documented with the same rigour as platform-layer SQL. Every developer who builds a report reimplements the same logic in the BI layer rather than consuming a shared, governed transformation in the data platform layer. Transformations belong in the data platform; the BI tool should consume pre-built, governed tables.
How Loop Wise Solutions Designs for This
We implement all business logic transformations in the data platform layer — using dbt or native SQL — and establish a policy that BI tools consume only the curated, governed tables from the semantic layer. BI tool query layers handle visualisation and formatting; they do not perform joins, business rule calculations, or metric definitions that belong in the governed data model.
Answers before you ask.
ELT reverses the order: raw data is extracted and loaded into the target analytical platform first, then transformed there using that platform's compute. ETL transforms data before loading. The difference is where transformation runs — inside the powerful target in ELT, versus a separate engine before loading in ETL.
Because modern cloud data platforms have abundant, scalable compute, so transforming inside the target is efficient and elastic. Loading raw first makes data available sooner and lets transformation logic be revised without re-extracting. This flexibility and the economics of cloud compute are why ELT has largely displaced ETL for cloud-native architectures.
The raw data is preserved and available immediately, so new transformations can be built or existing ones changed without going back to source. It also enables schema-on-read exploration of raw data. Keeping the untransformed source in the platform gives flexibility that ETL loses by discarding pre-transformation detail before loading.
When transformation must happen before data lands — for compliance, sensitive-data masking, or when the target lacks the compute to transform efficiently. If raw data cannot be permitted into the target, or heavy transformation is better done externally, ETL remains appropriate. The choice depends on platform capability and data-handling constraints, not fashion.