ETL Pipeline Template for Engineering Teams

ETL transforms data before loading it into the destination, typically using a middleware tool (Informatica, Talend, custom scripts). ELT loads raw data into the warehouse first, then transforms it using the warehouse's compute power (SQL via dbt, Snowflake tasks, BigQuery scheduled queries). ELT is the modern standard for cloud warehouses because it separates extraction from transformation, making each easier to debug and maintain independently.

Use incremental extraction when the source table is large (over 1M rows), has a reliable timestamp or sequence column for detecting changes, and you need to run frequently (hourly or more). Use full refresh when the table is small (under 100K rows), lacks a reliable incremental key, or when the source overwrites records in place (making change detection unreliable). Start with full refresh for simplicity and move to incremental when performance requires it.

Build schema detection into your extraction step. Compare the current source schema against the expected schema before extracting. Options: (1) fail loudly and alert the team (safest), (2) extract only known columns and log new ones for review, (3) extract all columns including new ones and handle them in the transformation layer. Never silently ignore schema changes. The [data lineage template](/templates/data-lineage-template) helps track how schema changes propagate through your pipeline.

Match the pipeline frequency to the consumer's freshness requirement. Daily batches work for board reports and weekly dashboards. Hourly runs work for operational dashboards and alerting. Near-real-time (every few minutes) works for customer-facing analytics. Real-time streaming is needed only when users expect sub-second data freshness. Each step up in frequency multiplies infrastructure cost and operational complexity.

An idempotent pipeline produces the same output whether it runs once or multiple times for the same input window. Techniques: (1) use `MERGE` or `INSERT ... ON CONFLICT` instead of blind `INSERT`, (2) process data by date partition and replace the entire partition on each run, (3) use deterministic transformation logic with no side effects. Idempotency is critical because pipelines will be re-run during debugging, backfills, and incident recovery. ---