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.
