ETL Pipelines

• 1ETL (Extract, Transform, Load) transforms data before loading; ELT (Extract, Load, Transform) loads raw data first and transforms in the warehouse. ELT is dominant in modern cloud architectures because cloud warehouses provide elastic, cheap compute.
• 2The Extract phase must handle schema evolution, API pagination, rate limiting, incremental extraction (only new/changed rows), and source system load management. CDC (Change Data Capture) is the most efficient extraction method for databases.
• 3dbt is the standard for SQL-based transformations: it compiles SELECT statements into materialized tables/views, adds testing (not-null, unique, referential integrity), generates documentation, and tracks data lineage. dbt models are versioned in Git alongside application code.
• 4Airflow DAGs define task dependencies, retry policies, SLA deadlines, and alerting. Best practice: each task should be atomic and idempotent. Use the 'sensor' pattern to wait for upstream data arrival rather than scheduling by wall-clock time.
• 5Data quality is the hardest part of ETL. Implement checks at every stage: row count validation after extraction, schema conformance after transformation, reconciliation totals after loading. Tools like Great Expectations and dbt tests automate these checks.
• 6Reverse ETL pushes transformed data from the warehouse back to operational tools (Salesforce, Marketo, Zendesk). Tools like Census and Hightouch sync warehouse segments to CRMs, enabling the warehouse to be the single source of truth for customer data.

Daily Sales Analytics Pipeline

dbt Transformation at JetBlue

• ⚠Building monolithic ETL jobs that extract, transform, and load in a single script. When the job fails, you cannot tell which phase failed or retry from the failure point. Break pipelines into discrete, idempotent tasks: extract (write raw to staging), transform (staging to clean), load (clean to final) -- each independently retriable.
• ⚠Ignoring schema evolution from source systems. When a source adds, removes, or renames a column, brittle ETL pipelines break silently (dropping data) or loudly (crashing). Use schema registries, contract tests, or flexible formats (JSON in a VARCHAR column) to handle schema changes gracefully.
• ⚠Treating data quality as an afterthought. Adding tests only after bad data causes an incident is reactive. Build quality checks into every pipeline from day one: row counts, null rates, value distributions, cross-source reconciliation. dbt tests and Great Expectations make this low-effort.
• ⚠Over-engineering with streaming when batch ETL meets the SLA. If your dashboard refreshes hourly and your users are fine with hourly data, a batch ELT pipeline running every hour is far simpler and cheaper than a streaming pipeline. Match the pipeline complexity to the actual freshness requirement.