Build report-ready data that is reproducible, timely, and explainable

Reporting ETL shapes governed warehouse data into stable facts, dimensions, snapshots, aggregates, and extracts optimized for analytical consumption. It sits downstream of ingestion but remains a correctness-critical data product. Its outputs must preserve business grain, metric semantics, history, security, and lineage while meeting dashboard freshness and query-performance objectives. A fast wide table with duplicated revenue is not report ready.

This is the layer interviewers for analytics engineering and BI-platform roles actually probe. They are less interested in streaming ingestion or cluster tuning and more interested in whether you can take a business question — "show me revenue by customer segment by month" — and defend every decision between the warehouse table and the dashboard: grain, keys, history behavior, freshness, and what happens when the job runs twice. Expect the interview to open with a scenario, not a definition: "A finance dashboard is off by 3% versus the source system. Walk me through how you'd debug it." The strong answer starts with grain and keys, not with the scheduler.

ETL versus ELT, as the question is asked in 2026

The transformation moved into the warehouse years ago: you load raw into Snowflake/BigQuery/Redshift and transform with dbt-style SQL, so most modern pipelines are ELT. But "do you do ETL or ELT" is a screening question, and taking a side is the weak answer. The strong answer names where ETL still wins:

• Transformation must happen before load: legacy or non-warehouse sources (mainframe extracts, flat files) that cannot be queried in place.
• Compliance requires filtering or masking before data lands — row-level restrictions applied before the raw layer exists.
• Pre-aggregation before storage, when the raw volume is not worth keeping or the cost of querying it repeatedly is prohibitive.

So the answer is: ELT by default in a warehouse-centric stack, ETL where the constraint is upstream of the warehouse. Say that and move on; the interviewer is checking whether you know the decision is about where transformation runs, not about tool loyalty.

Start from the consumer contract, not the table

Identify measures, dimensions, grain, time basis, joins, filters, history behavior, freshness, quality, and access requirements. Choose a star schema, denormalized serving table, aggregate, or semantic model based on query patterns and change rate. Denormalization can reduce runtime joins but duplicates attributes and increases correction cost; it must not erase keys, lineage, or validity.

Grain and keys are the debugging tool

State the grain of every report table before writing a transform: one row per order, per order line, per customer-day? Most "the number is wrong" tickets are grain bugs. A join that fans out duplicates rows and double-counts revenue; a join that drops unmatched rows silently loses sales. Both look plausible on a dashboard.

The defense is keys. A natural key (order number) is what the business recognizes; a surrogate key is what you control. Use surrogate keys on dimensions so a business key can be re-keyed upstream without breaking history, and enforce uniqueness at the declared grain with a test, not a hope. When you debug a discrepancy, the first question is "what is the grain of this table, and is it actually true row-for-row?" — interviewers listen for that question, because candidates who reach for the scheduler logs first rarely find the fan-out join.

Facts, dimensions, and how much to snowflake

Star schema is the default for BI: a fact table at the declared grain, foreign keys to dimension tables, dimensions denormalized so a BI tool joins once. Snowflaking (normalizing dimensions into sub-dimensions) saves storage but adds joins the BI tool must generate, and BI tools handle multi-hop joins poorly — the tradeoff is storage cost versus query simplicity and semantic-model maintainability. Storage is cheap in a modern warehouse; snowflake sparingly, usually only for genuinely shared reference data.

Conformed dimensions — one customer dimension, one product dimension, one date dimension used by every fact — are what let two reports agree with each other. If each pipeline builds its own customer table, revenue-by-segment and churn-by-segment will disagree, and no reconciliation will explain why. Degenerate dimensions (the order number stored on the fact line, with no dimension table) are normal for high-cardinality identifiers you need for drill-through but not for analysis.

Make every transformation deterministic and idempotent

Given the same source versions, parameters, code, and reference data, a rerun should converge to the same target state. Use stable business and surrogate keys, explicit deduplication, deterministic tie-breaking, atomic publication, and run metadata. A retry after an uncertain commit must not duplicate facts or advance a watermark past uncommitted data.

Idempotency is what makes a reporting pipeline operable, because every pipeline is re-run: after a failure, after a late-arriving correction, after a backfill. A load that appends unconditionally double-counts on the second run, and the error is silent because the row counts look plausible and the totals are simply wrong. Make each load replace a well-defined partition or merge on a stable business key, so that running the same interval twice produces the same result as running it once, and test that property explicitly rather than assuming it.

Incremental processing and late data

Incremental processing is an optimization, not a different definition. Select changes using a reliable high-water mark, CDC offset, partition, or source version and include overlap for late or corrected records when needed. Merge by the target grain's unique key, handle deletes and rekeys, and advance checkpoints only after atomic success. Periodically compare incremental output with a clean full rebuild so accumulated drift is detected — the point of that comparison is to reveal divergence from missed updates, deletes, or logic changes, not to prove the incremental job is cheaper.

Late-arriving and changing data is the constraint that shapes the design. Source systems correct records after the fact, and a pipeline that reads only new rows since a watermark will never see a correction to an old one. Decide per source whether corrections are possible, use a change-capture mechanism or a bounded reprocessing window where they are, and record for each load what interval it covered so a discrepancy can be traced to a specific run rather than investigated across the whole history.

Time behavior, history, and as-of correctness

Time behavior must be explicit. Preserve business event time, source update time, ingestion time, and transformation time where relevant. Define reporting timezone, cutoff, fiscal calendar, late-arrival horizon, closed-period restatement, and incomplete-period labels. Watermarks express a completeness assumption, not proof that no older event will arrive. Backfills should use the same code and contracts as scheduled runs and must not silently overlap normal processing.

Slowly changing dimensions are how the reporting layer shows a value as it was known at the time. Type 1 overwrites current attributes — right for a corrected phone number. Type 2 creates effective-dated versions with surrogate keys, validity intervals, and a current flag — required for trend and cohort reporting, because "revenue by customer segment over 12 months" needs each fact joined to the segment as it was at sale time, not today's segment. A report labeled "sales by customer segment at sale time" that joins every historical fact to today's segment is the classic Type 1/Type 2 confusion, and it is a standard interview trap. Point-in-time joins (fact date between dimension valid_from and valid_to) are the mechanism; non-overlapping validity intervals are the invariant that makes them unambiguous.

Snapshot fact tables (periodic full captures, e.g. daily inventory balances) differ from cumulative event loads; balances do not sum over time, and treating a snapshot table like a transaction table is another common wrong-number bug. Where the reporting layer must show a value as it was known at the time — a restated financial figure is the common case — that is a Type 2 question, and it must be decided before the first load rather than retrofitted.

Pre-aggregation, publication, and quality gates

Pre-aggregation trades flexibility for speed. Declare aggregate grain, supported measures, rollup behavior, dimensions, filters, time zone, currency, semantic version, security scope, and freshness. Ratios, distinct counts, balances, and percentiles may not roll up by simple summation. Reconcile aggregates to base facts under representative filters and invalidate them on source correction, policy change, or metric-definition change.

Publish outputs atomically. Build and validate a new partition, table, snapshot, or view version before making it visible; avoid dashboards reading half-replaced data. Coordinate dependent models through data intervals or source-ready signals, not arbitrary sleep. Preserve a last-known-good version for bounded rollback while showing accurate data freshness. A successful scheduler task is not evidence that every report consumed a coherent snapshot.

Quality gates belong at input, transformation, and output boundaries — in the pipeline, not observed on a dashboard. Row-count and freshness checks catch a failed load; uniqueness, referential integrity, accepted-value, and reconciliation checks against the source catch the loads that succeed and are wrong, which are the dangerous ones. Test schema, uniqueness, not-null, accepted values, row counts, distribution, freshness, business invariants, access scope, and duplicate or missing partitions. Quarantine bad records only when partial publication is explicitly acceptable and coverage is visible. Never convert a failed source or partial load into plausible zeros. Decide per check whether a failure blocks publication or raises a warning, because a pipeline that always publishes will eventually publish something wrong to an audience that trusts it, and a pipeline that always blocks will be routed around within a quarter.

Operate with lineage and run evidence

Record code and configuration version, source and target snapshots, interval, watermark, row counts, inserts/updates/deletes, rejected records, tests, timings, cost, owner, and downstream consumers. Measure freshness and completeness SLOs, incremental-versus-full drift, failure and retry, backfill duration, late and duplicate rates, aggregate reconciliation, query latency, cost, and dashboard impact. Test retries, concurrent runs, source correction, schema drift, empty input, partial partitions, warehouse outage, task replay, and rollback.

What interviewers probe, and what a weak answer sounds like

The recurring probes, and the follow-ups they open:

• "Walk me through your last reporting pipeline." They want grain, keys, history strategy, and failure handling stated up front. Weak answer: a tool tour ("we used dbt and Airflow") with no grain, no key strategy, and no statement of what happens on rerun.
• "The dashboard disagrees with the source by 3%. Debug it." Strong answer starts at grain and joins — fan-out, drop-out, Type 1/Type 2 mismatch, timezone or fiscal-calendar cutoff — then checks watermarks and late corrections. Weak answer starts with the scheduler and never mentions grain.
• "What happens if this job runs twice?" They want the idempotency mechanism named: partition replace or merge on a stable key, plus the test that proves it. Weak answer: "we'd notice the row counts."
• "How do you handle late corrections?" They want a per-source decision, a capture mechanism or bounded reprocessing window, and the recorded load interval. Weak answer: "we use a watermark" with no acknowledgment that a watermark never sees corrections to old rows.
• "Type 1 or Type 2 here?" They want the reporting question to decide it: trends and cohorts need Type 2; current-state-only reporting can take Type 1. Weak answer: always Type 2, with no validity-interval or point-in-time-join detail — it signals the term was memorized, not used.
• "ETL or ELT?" Weak answer: picking a team. Strong answer: ELT by default in a warehouse stack, ETL where transformation must precede landing.

Likely follow-ups once you answer well: how you'd backfill six months without double-counting, how you'd version a metric definition change, how aggregates stay reconciled to base facts, and how you'd roll back a bad publish. Each of those is answered by something above — partition-replace idempotency, semantic versioning, reconciliation checks, last-known-good publication — so the guide you can defend is the guide you just read.