Data engineering and ETL

ETL transforms data before loading it into the target store, so the transformation engine sits outside the warehouse. ELT loads raw data first and transforms it inside the warehouse using its compute. ELT dominates because cloud warehouses (Snowflake, BigQuery, Redshift) decouple storage from elastic compute, making in-warehouse SQL transforms cheap, scalable, and easy to reprocess from raw history.

A shuffle is the redistribution of data across partitions/executors that occurs for wide transformations like groupBy, join, and repartition, where records must be regrouped by key. It is expensive because it serializes data, writes it to disk, and transfers it over the network between nodes, often causing skew and spill. Minimizing shuffles (broadcast joins, pre-partitioning, reducing key cardinality) is key to Spark tuning.

An idempotent pipeline produces the same final state regardless of how many times it is run for the same input/window. It is critical because retries, backfills, and at-least-once delivery cause re-execution; without idempotency you get duplicates or double-counting. Common techniques are MERGE/upsert on a deterministic key, partition overwrite by date, and deterministic deduplication.

-- Idempotent upsert
MERGE INTO target t
USING staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

SCD Type 1 overwrites the dimension attribute, keeping no history. SCD Type 2 preserves history by inserting a new row for each change, typically using effective_from/effective_to dates, a current/active flag, and a surrogate key, so facts can join to the dimension value that was valid at event time. Type 2 enables point-in-time analysis at the cost of more rows and join complexity.

CDC captures row-level inserts, updates, and deletes from a source database so downstream systems stay in sync. Log-based CDC reads the database transaction log (e.g., MySQL binlog, Postgres WAL), which is low-latency, captures deletes, and adds negligible load on the source. Query-based CDC polls with a timestamp/incrementing column, which misses hard deletes, adds query load, and can lose intermediate changes between polls.

A watermark is a heuristic timestamp asserting that the system has likely received all events up to that event time, allowing windows to be finalized despite out-of-order or late data. It solves the trade-off between completeness and latency: it lets the engine emit window results without waiting forever, while a configurable allowed-lateness can still update or drop very late events.

Columnar storage stores values of each column contiguously, enabling column pruning (read only needed columns), high compression (similar values together), and predicate pushdown via per-rowgroup min/max statistics. This drastically reduces I/O for analytical scans that touch few columns over many rows. Row formats like Avro are better for write-heavy, full-record streaming/ingestion use cases.

It is a layered data design: Bronze holds raw ingested data (append-only, schema-on-read), Silver holds cleaned, conformed, deduplicated, and joined data, and Gold holds business-level aggregates/marts optimized for consumption. The pattern provides incremental quality, reprocessability from raw, and clear separation of concerns across the pipeline.

They add a transaction/metadata layer on top of Parquet files to provide ACID transactions, snapshot isolation, time travel, schema and partition evolution, and efficient upserts/deletes on data lakes. Plain Parquet directories lack atomic commits, so concurrent writes and partial failures can leave readers seeing inconsistent files. The table format tracks which files constitute each consistent snapshot.

A consumer group lets multiple consumers cooperatively process a topic, with each partition assigned to exactly one consumer in the group, enabling horizontal scale-out and load balancing. Parallelism is therefore capped by the number of partitions: adding more consumers than partitions leaves some idle. Different consumer groups each get a full independent copy of the stream (pub/sub fan-out).

Partition pruning skips entire physical partitions (directories/files) whose partition-column values cannot match a query filter, avoiding listing/opening them at all. Predicate pushdown filters within scanned files using per-rowgroup/page statistics or pushing the filter to the storage layer. Pruning operates at the partition/metadata level; pushdown operates inside the file. Both reduce I/O but at different granularities.

Data skew occurs when records are unevenly distributed across partitions/keys, so a few tasks process disproportionately large data and become stragglers that dominate job runtime. Mitigations include salting hot keys (appending a random suffix to spread them, then aggregating in two stages), using broadcast joins for small tables, adaptive query execution (skew join handling), and isolating/handling hot keys separately.

At-most-once (may drop, never duplicate), at-least-once (never drops, may duplicate), and exactly-once (each record affects state exactly once). Exactly-once is hardest because it requires atomic coordination between consumption offsets and output side effects, typically via transactional sinks, two-phase commit, or idempotent writes plus checkpointed offsets. True end-to-end exactly-once usually means effectively-once through idempotency or transactions.

A star schema has a central fact table joined directly to denormalized dimension tables (one join hop), favoring query simplicity and speed. A snowflake schema normalizes dimensions into multiple related sub-tables, reducing redundancy/storage but adding joins and complexity. Star is generally preferred for analytics performance; snowflake suits very large or frequently changing dimension hierarchies.

A data contract is an explicit, versioned agreement between data producers and consumers specifying schema, semantics, SLAs, quality expectations, and ownership, often enforced in CI/CD. It solves the silent-breakage problem where upstream schema or semantic changes break downstream pipelines without warning, by making the interface explicit, testable, and change-controlled. It shifts data quality left to the producer.

A broadcast (map-side) join ships a small table to every executor so the large table can be joined locally without a shuffle, eliminating the costly network redistribution and dramatically speeding up the join. The main risk is memory pressure/OOM: if the broadcast side is too large, it can overwhelm executor and driver memory, so it is gated by a size threshold (spark.sql.autoBroadcastJoinThreshold).

A surrogate key is a system-generated, meaningless integer (or hash) primary key for a dimension row, independent of business attributes. It is preferred because natural keys can change, be reused, be composite/wide, or differ across source systems, whereas surrogates give stable, compact, single-column joins and are essential for SCD Type 2 where one business entity has multiple historical rows.

depends_on_past makes a task instance wait for the same task's previous DAG run to succeed; wait_for_downstream additionally waits for the immediate downstream tasks of the prior run to succeed. A backfill runs the DAG for historical logical dates that were missed or need reprocessing. Together they control ordering and reprocessing semantics so backfills respect cross-run dependencies.

Schema evolution is changing a dataset's schema over time while keeping producers and consumers interoperable. Compatible changes—such as adding a field with a default, or removing a field that has a default—let old and new readers/writers coexist (the registry classifies these as backward/forward/full compatible). Breaking changes—renaming or removing required fields, or changing a field's type incompatibly—violate compatibility and require a coordinated migration or a new schema version. A schema registry enforces these compatibility rules at write time.

The small files problem arises when ingestion (especially streaming or high-partition writes) produces many tiny files, inflating metadata/listing overhead and causing per-file open costs that cripple read performance and task scheduling. Remediation includes compaction/OPTIMIZE jobs that coalesce files into larger ones, tuning write batch size, reducing over-partitioning, and using table formats with auto-compaction/bin-packing.

Z-ordering physically co-locates rows that share similar values across multiple chosen columns using a space-filling (Z-order) curve, so that file-level min/max statistics become tight on those columns and enable strong data skipping. It helps when queries filter on several high-cardinality columns that aren't the partition key; it reduces files scanned. It is less useful for low-cardinality columns already handled by partitioning.

Incremental loads process only new/changed records (by high-watermark timestamp or CDC), drastically reducing cost and runtime versus reprocessing the entire source each run. The key correctness risk is late-arriving or out-of-order updates and the watermark boundary: records updated after the cutoff or with clock skew can be missed, so a lookback window, CDC, or periodic full reconciliation is needed.

A view materialization stores only the SQL definition and computes on read (cheap to build, slower/costlier to query). A table materialization fully rebuilds and persists results each run (fast reads, costly rebuilds). An incremental materialization inserts/merges only new or changed rows using an is_incremental() filter and a unique key, giving fast builds on large append-mostly datasets at the cost of more complex correctness handling.

{{ config(materialized='incremental', unique_key='id') }}
SELECT * FROM source
{% if is_incremental() %}
WHERE updated_at > (SELECT max(updated_at) FROM {{ this }})
{% endif %}

OLTP systems are optimized for many small, low-latency, concurrent transactional writes/reads with normalized schemas and row storage. OLAP systems are optimized for large scans and aggregations over historical data, typically columnar and denormalized. Running heavy analytics on OLTP causes lock contention, resource starvation of transactional traffic, and poor scan performance, which is why data is offloaded to a warehouse/lakehouse.

A DLQ is a separate sink where records that fail processing (malformed, schema-invalid, repeatedly erroring) are routed instead of being dropped or blocking the pipeline. It is important because it preserves bad records for inspection and reprocessing, prevents poison messages from stalling consumers, and keeps the main flow healthy while maintaining observability into data quality failures.

Additive measures can be summed across all dimensions (e.g., sales amount). Semi-additive measures can be summed across some dimensions but not time (e.g., account balance, inventory level—you average or take a period-end snapshot over time). Non-additive measures cannot be summed across any dimension (e.g., ratios, percentages, unit prices) and must be recomputed from their base components.

Kafka transactions let a producer atomically write output records and commit consumer offsets within a single transaction, coordinated by a transaction coordinator and an idempotent producer (enable.idempotence, transactional.id). Consumers reading with isolation.level=read_committed only see committed records, so a failure-and-replay never exposes partial or duplicated output. This achieves effectively exactly-once across consume-transform-produce.

Data lineage is the traceable map of where data originates, how it flows, and what transformations produce each downstream dataset/column. It is essential for impact analysis (what breaks if a source changes), root-cause debugging of bad numbers, regulatory compliance (GDPR/auditability), and trust, by making the provenance and dependencies of every field explicit and queryable.

Tumbling windows are fixed-size, non-overlapping, contiguous time buckets. Hopping/sliding windows are fixed-size but advance by a smaller hop, so they overlap and a record can belong to multiple windows. Session windows are dynamic, gap-based: they group events separated by less than an inactivity gap, so window length varies per key based on activity bursts.

The staging area is an intermediate zone where raw extracted data lands before transformation/loading into the target model. It decouples extraction from transformation (enabling independent retries and reprocessing), preserves an immutable copy of source data for auditing/replay, supports set-based bulk transforms, and isolates the warehouse from source availability and schema volatility.

Point-in-time correctness ensures training features reflect only data that was known at the prediction timestamp, preventing data leakage where future information inflates offline metrics but fails in production. Feature stores enforce it via point-in-time (as-of) joins between the entity/label timeline and time-versioned feature values, and by sharing the same transformation logic for offline training and online serving to avoid training-serving skew.

Horizontal partitioning (sharding) splits a table by rows—each partition/shard holds a subset of rows (e.g., by date or hash of key)—to distribute volume and enable pruning and parallelism. Vertical partitioning splits by columns—storing different column groups separately—so frequently accessed columns are read without touching wide/rare ones. Columnar formats are a fine-grained form of vertical partitioning.

Commonly monitored dimensions are: completeness (no missing required values/records), accuracy (values reflect reality), consistency (no contradictions across systems), timeliness/freshness (data is current within SLA), validity (values conform to format/range/type rules), and uniqueness (no unintended duplicates). Pipelines enforce these via automated tests/assertions, anomaly detection on volume/freshness, and alerting before bad data reaches consumers.