The traditional ETL pattern -- Extract, Transform, Load -- transforms data before loading it into the target system. The modern ELT pattern -- Extract, Load, Transform -- loads raw data first and transforms it in the warehouse using SQL. ELT has become the dominant pattern because cloud warehouses (Snowflake, BigQuery, Redshift) provide cheap storage and powerful compute, making it more efficient to transform data in place than in a separate processing layer.
ELT also preserves raw data, enabling retroactive analysis. When business requirements change and you need to recalculate a metric using a different formula, the raw data is available for reprocessing. Under ETL, transformed data may be the only copy, making historical recalculation impossible without re-extracting from source systems.
ETL remains appropriate for specific scenarios: when data must be cleansed for compliance before storage (PII must be masked before loading), when source data volumes exceed reasonable storage costs (filtering out 90% of events before loading), or when the transformation requires processing capabilities that SQL cannot efficiently provide (complex ML feature engineering, image processing).
API-based extraction from SaaS tools (CRM, marketing platforms, payment processors) is the most common pattern. Use incremental extraction -- pulling only records modified since the last extraction -- to minimize API calls and processing time. Track extraction state (last modified timestamp or cursor token) to enable reliable incremental pulls without missing records or reprocessing unnecessarily.
Database replication for internal systems provides the most complete and timely data access. Change data capture (CDC) streams every insert, update, and delete from the source database to the warehouse, typically with sub-minute latency. Debezium (open source) and Fivetran (managed) are common CDC tools. CDC avoids the load that periodic full extractions place on source databases and provides near-real-time data freshness.
File-based extraction handles bulk data exchanges from partners, legacy systems, and external data providers. These sources typically deliver CSV, JSON, or XML files to SFTP, S3, or email. Build extraction pipelines that detect new files, validate schema and completeness, and load them into the warehouse with metadata (source, arrival time, row count) that enables quality monitoring.
dbt (data build tool) has become the standard framework for managing SQL-based transformations in ELT architectures. It organizes transformations as version-controlled SQL models with dependency management, testing, and documentation. Over 30,000 organizations use dbt according to their 2024 community survey, making it the most widely adopted transformation framework.
Structure transformations in layers: staging (light cleaning and renaming of raw source data), intermediate (business logic joins and calculations), and marts (final consumption-ready tables organized by business domain). This layered approach isolates complexity -- changes to source data affect only the staging layer, business logic changes affect only intermediate models, and consumption patterns affect only marts.
Test transformations aggressively. dbt supports schema tests (not null, unique, accepted values, relationships) and custom data tests (SQL queries that should return zero rows). Run tests after every pipeline execution. A transformation that produces correct results today may break tomorrow when source data changes. Automated tests catch these breaks before wrong data reaches dashboards and decisions.
Pipeline orchestration coordinates the execution of extraction, loading, and transformation steps in the correct sequence with appropriate error handling. Apache Airflow is the most widely deployed orchestrator, though Dagster, Prefect, and managed alternatives (Astronomer, MWAA) reduce operational burden. The orchestrator defines dependencies (transformation X cannot run until extraction Y completes), schedules execution, retries failed tasks, and alerts on failures.
Design pipelines for idempotency -- running the same pipeline twice should produce the same result. This means using MERGE or INSERT-OVERWRITE rather than INSERT-APPEND, including date partitioning that prevents duplicate processing, and structuring transformations so they can be safely re-executed. Idempotent pipelines can be restarted after failure without manual cleanup, dramatically reducing incident recovery time.
Separate extraction cadence from transformation cadence when they have different requirements. Extract from high-volume sources hourly or continuously, but run expensive transformations daily if the business only needs daily-refreshed metrics. This separation avoids running costly transformations more frequently than necessary while keeping raw data current for ad-hoc analysis.
Monitor four pipeline health dimensions: freshness (when did data last arrive), volume (how many records were processed), schema (did the structure change), and quality (do values fall within expected ranges). Alert on deviations from expected baselines for each dimension. A pipeline that loaded 500 records when it typically loads 50,000 is probably broken even if it completed without technical errors.
Build runbooks for common failure scenarios. API rate limit exhaustion, source schema changes, warehouse compute capacity limits, and authentication token expiration are the most frequent pipeline failures. Documenting diagnosis steps and remediation procedures for each scenario reduces mean time to recovery and enables on-call responders who did not build the pipeline to fix issues quickly.
Track pipeline performance trends over time. Gradually increasing execution duration, growing data volumes, and rising error rates provide early warning of capacity issues before they cause failures. Monthly pipeline health reviews that examine these trends enable proactive scaling and optimization rather than reactive firefighting when pipelines start timing out or missing SLAs.
Part of our complete guide: Data Analytics & Insights →
This article is part of our comprehensive knowledge hub on data analytics & insights. Read the full guide for a complete strategic framework.
Our team helps companies implement the frameworks and strategies covered in this article.
Get in Touch