Topic: data-engineering
-
Slowly changing dimensions: overwrite, add a row or add a column
When a dimension attribute changes (a customer moves, a product is reclassified), type 1 overwrites and loses history, type 2 adds a new row with effective and expiry dates and a current flag under a new surrogate key, and type 3 keeps the previous value in an extra column. Snapshot tools implement type 2 by comparing an update timestamp or a set of columns on each run.
-
How far back should a scheduled pipeline reprocess for late-arriving events, and how have teams chosen the window?
Open question: stream engines admit that some events can be arbitrarily delayed, and batch schedulers run each interval once after it closes; a common compromise re-runs the last N intervals on every run, but N is usually a guess. What evidence has been used to size N, and what happened to the events that arrived later still?
-
ETL versus ELT: where the transformation runs and what that changes
ETL transforms data in a separate engine before loading it into the target; ELT loads raw data first and transforms it with the target store's own processing. The choice decides where compute is paid for, what raw data lands in the warehouse, and how easily a transformation can be rerun.
-
Freshness and row-count checks on raw source tables catch most pipeline incidents earlier than column-level tests downstream
Hypothesis: in a warehouse with layered models, the majority of incidents that end up visible to report consumers first show as a stale or under-sized raw source load, so freshness and volume checks at the source layer detect them earlier than not-null, uniqueness and accepted-value tests on downstream models; a proposed comparison over recorded incidents.
-
Idempotent data pipelines: partition overwrite, safe reruns and backfills without double counting
A pipeline task should produce the same output whenever it is rerun for the same data interval: read a fixed partition of input, replace rather than append the corresponding partition of output, and upsert by key where replacement is impossible. Backfills then become ordinary reruns over a range of intervals instead of a source of duplicated rows.
-
Columnar storage basics: how a Parquet file is laid out and why analytical reads touch less data
A Parquet file is a sequence of row groups, each holding one column chunk per column, each chunk split into pages that are the unit of encoding and compression; the metadata sits at the end so that a reader opens the footer, picks only the needed columns and skips row groups and pages by their min/max statistics. Column-wise layout is what makes dictionary and run-length encodings effective.
-
Data quality checks: freshness, volume, nulls and uniqueness as a minimum test set
Four cheap checks catch most broken loads: the source was updated recently enough (freshness), the interval delivered a plausible number of rows (volume), keys and required measures are not null, and the declared grain is unique. Express each as a query that returns failing rows, run it after loading and before publishing, and separate warnings from blocking errors.
-
Schema evolution with Avro and Parquet: reader and writer schemas, merged files and compatibility modes
Avro resolves a writer's schema against a reader's schema field by field, filling missing fields from reader defaults and ignoring unknown ones; Parquet files with different but compatible schemas can be merged by the reading engine at a cost; a schema registry enforces backward, forward or full compatibility. Adding optional fields with defaults is the safe move, renaming and type changes are not.
-
Star schema basics: facts, dimensions and declaring the grain
A star schema stores measurements in fact tables and descriptive context in dimension tables linked by keys; the design starts by declaring the grain, what one fact row represents, because every measure and dimension must be consistent with it. Fully additive measures sum across any dimension, semi-additive ones not across time, and ratios must be stored as their components.
-
Deduplication strategies for records: exact rows, keep-latest by key and bounded windows
Decide first what counts as a duplicate: identical rows, several versions of one key, or messages redelivered within a window. Exact duplicates fall to DISTINCT; versions need a keep-latest rule with an explicit ordering; redelivery is deduplicated on an idempotency key within a bounded time or state window, as message queues and stream engines do.
-
Downsampling and retention tiers for time-series data
Keep raw samples for a short window, roll them up into fixed bins with count, sum, min and max for a longer one, and delete by partition when a tier expires; choose aggregates that can be re-aggregated, align bins to a fixed origin, and run the rollup only after late data for the bin has arrived.
-
Choosing between batch and streaming: required latency, event time and late data
Batch processes a bounded input after its interval closes and is reproducible by construction; streaming processes an unbounded input as it arrives and must reason about event time, watermarks and late data to give stable answers. Pick streaming only when a consumer acts within seconds of an event; otherwise the batch path is simpler, and it is needed for reprocessing anyway.
Machine-readable: JSON