data-engineeringlisted

# Data Engineering & Data Science Production Reference ## ELT vs ETL — Decision Framework **ELT is the production default** on modern cloud warehouses (Snowflake, BigQuery, Databricks). Load raw data first, transform in-warehouse with dbt. Storage/compute decoupling makes this cheaper and operationally simpler than a separate transformation tier. **ETL still wins when:** - Compliance requires pre-load masking/tokenization (HIPAA, PCI DSS) - Source data must be filtered before reaching governed storage - Destination is an operational system, not a warehouse **ELT advantages:** preserves raw data for reprocessing when business logic changes; no transformation infrastructure to manage; one pipeline to operate. --- ## Pipeline Design Principles - **Idempotency is non-negotiable** — re-running a job must produce the same result; enables safe backfills and retries - **At-least-once semantics** (with idempotent writes/dedup) for most analytics; exactly-once is expensive and rarely necessary - **Late-arriving data**: handle with watermarks and windowing - **Late-arriving dimensions** (fact arrives before its dimension): use placeholder/inferred dimension rows rather than dropping facts or accepting null FKs - **Watermark storage**: store in an audit table in the target DB, not just an orchestrator variable — makes it debuggable --- ## Incremental Loads & CDC **Watermark-based incremental**: pull rows where `updated_at > last_watermark`. Store the watermark in the target DB