data-engineering

ELT is the default on modern cloud warehouses like Snowflake and BigQuery: load raw data first, then transform in-warehouse with dbt. ETL still wins when compliance requires pre-load masking, sources must be filtered before governed storage, or the destination is an operational system.

Airflow fits enterprises with 100+ pipelines and the largest provider ecosystem. Dagster suits greenfield dbt-centric platforms with software-defined assets and local testing. Prefect offers the fastest Python-native laptop-to-production path but has serverless cold starts of 5-15 seconds.

Polars is multi-threaded with a lazy API offering predicate pushdown and streaming for larger-than-RAM data, benchmarking 5-10x faster aggregations than pandas. Switch when pandas becomes a bottleneck, before reaching for Spark, which is only needed when data exceeds single-node memory.

On BigQuery, partition and cluster tables since on-demand billing charges $6.25 per TiB scanned regardless of LIMIT. On Snowflake, set auto-suspend to 60 seconds for ETL workloads, use Economy scaling for batch, and watch for zombie warehouses and excessive Time Travel retention.

Use effective_from, effective_to, and is_current columns with a two-step pattern: first close changed rows by setting effective_to and is_current = 0, then insert new current versions. A single MERGE cannot do both operations from one source row without nested tricks.

Only reach for Spark when data will not fit on a single node or you need distributed streaming and MLlib. DuckDB and Polars handle hundreds of gigabytes on one machine far faster, with Polars processing a 650GB Delta dataset in 12 minutes versus over an hour for PySpark.