Skip to content
Tech Interview Prep
Technical interview guide

ETL for Reporting

The lighter-weight, reporting-focused data prep that sits between raw warehouse tables and a BI tool.

Read
45 min
Practice MCQs
25
Interview Q&A
25
Edition
v4
Editorial status
Reviewed

Scope: Current dbt, Google Dataform and BigQuery, Snowflake streams/tasks, Apache Airflow, Apache Iceberg, and OpenLineage guidance reviewed 2026-09-04.

Curated: · Written: · Reviewed:

25 questions

MCQ-25A group ETL loads daily sales from 14 regional systems into one global fact table. Finance publishes each day on the UTC calendar day, and revenue is attributed to the date the sale occurred. Any published day's total must be reproducible from the source rows. What must the ETL normalize before aggregating?
MCQ-1A retail team is building the serving table for a revenue dashboard. The dashboard has 12 charts; every chart groups by day, region, and product_category and filters the last 90 days. The BI tool runs one query per chart against the serving table. The source order table has 400M rows and 1.2 TB; the raw event table has 8B rows. The nightly load window is 4 hours, and a full rebuild of the event table takes 9 hours. Which serving design should they build first?
MCQ-11An orders dashboard runs one query against a star schema: `fact_order_line` (600M rows, 120 bytes per row), `dim_customer` (8M rows), `dim_product` (400k rows), `dim_date` (3.7k rows). The query joins all three dimensions and aggregates revenue by customer region and product category. Profiling on the production cluster shows p95 = 4.2 s: 3.6 s building and probing the `dim_customer` and `dim_product` hash tables, and 0.6 s scanning and aggregating the 72 GB fact table. The SLA is 1.5 s. The team proposes a nightly-materialized wide table that copies `region`, `category` and `customer_name` onto every fact row, adding 90 bytes per row. Which prediction is correct?
MCQ-12A rollup `daily_sales` declares grain (day, region), measures `revenue = SUM(amount)`, dimensions `region`, time UTC day, security region-scoped, freshness 24 h. The router matches a query to the rollup when the query's grain, measures and dimensions are all declared. For 2024-03-01..2024-03-07 the base table holds $1,000,000 of revenue: $400,000 with status='completed' and $600,000 with status='refunded'. A dashboard asks for completed revenue by region for that week and shows $1,000,000. What explains the $600,000 overstatement?
MCQ-5`fact_orders` is a dbt incremental model declared at one row per `order_id`, and 40 downstream models join it on `order_id` expecting exactly one row. The hourly job merges a source that re-sends the trailing 48 hours of orders; an amended order keeps its `order_id` and gets a new `updated_at`. The model sets `unique_key = (order_id, updated_at)`. After a week, `count(*)` is 1,284,003 and `count(distinct order_id)` is 1,271,540, and every duplicated `order_id` was amended at least once. The table is 4.2 TB and the job must stay incremental. What should the team change?
MCQ-6A nightly job loads `crm.customer` into `dim_customer` as SCD Type 2 (`customer_sk`, `customer_id`, `valid_from`, `valid_to`, `is_current`). Thirteen jobs read it with `WHERE is_current`; a 3-year revenue trend joins `fact_sales` to `dim_customer` on `customer_sk`. Last night the CRM hard-deleted `customer_id = 4471` (a duplicate merged into 4472) and the CDC feed emitted one `op = 'D'` row for it. The trend's 2023 revenue for the merged account must not change. What should the load do with 4471?
MCQ-7A warehouse keeps `dim_customer` as a Type 2 slowly changing dimension with columns `customer_sk`, `customer_id`, `region`, `valid_from`, `valid_to`. `fct_orders` stores `customer_id` and `order_date`. Customer 4471 moved from EMEA to APAC on 2024-03-01. The monthly revenue-by-region report runs: ```sql SELECT d.region, SUM(f.amount) FROM fct_orders f JOIN dim_customer d ON f.customer_id = d.customer_id GROUP BY d.region; ``` Customer 4471's February orders total $180,000 and its March orders total $95,000. What does the report show for February?
MCQ-19A nightly job rebuilds the `orders_daily` partition by writing to `orders_daily_staging` and swapping it into place with a single metadata operation. Last night's run wrote 3.1M rows against an expected 4.2M, and the null rate in `customer_id` came out at 12% against a 0.5% gate. The gate runs after the staging write and before the swap, and the swap has not run. Dashboards read `orders_daily` and refresh every 5 minutes. What should the on-call engineer do?
MCQ-10An orders dashboard joins `fct_orders` to `dim_customer` and refreshes every 30 seconds. Each night the job truncates and reloads `fct_orders` (35 minutes), then truncates and reloads `dim_customer` (5 minutes). Between 02:05 and 02:45 the dashboard's revenue drops to zero and recovers, and some rows join to a customer key that does not exist yet. The warehouse is a columnar store where a 40-minute write transaction blocks every dashboard read. Which change removes the inconsistent window?
MCQ-14A daily revenue report reads exactly one partition per day and sums it. The report contract has two states only: a complete total, or the day marked unavailable. On 2024-03-11 the source extract died after 6 of 24 hourly shards landed; the loader's default is to commit the partition with whatever rows arrived, and the dashboard's freshness check only tests that the partition exists. The day's true revenue is about $480k, and the 6 loaded shards hold $120k. What should the pipeline emit for dt=2024-03-11?
MCQ-2A nightly job reads one Kafka partition of purchase events, aggregates revenue by day and product, and writes to `daily_revenue` in 10,000-row batches, committing each batch. It commits the Kafka offset only after the whole partition is written. Last night it wrote 40,000 rows for `2024-06-01`, then crashed before the offset commit. The full day has 100,000 product rows. On restart it reprocesses the same partition. Which change makes the transform idempotent for this retry?
MCQ-8A daily revenue job runs at 02:00 UTC: it reads `watermark`, processes the next day, writes partition `dt=<day>` with `INSERT OVERWRITE`, then sets `watermark = day`. After the 02:00 Jan 11 run, `watermark = Jan 10`. A bug fix must be backfilled for Jan 1–10. The backfill starts at 14:00 Jan 11, processes days in order, and takes 14 hours, finishing at 04:00 Jan 12. The normal job for Jan 11 runs at 02:00 Jan 12. Which backfill plan keeps Jan 1–10 correct and does not corrupt Jan 11?
MCQ-17A nightly chain builds a finance mart: raw_orders feeds stg_orders (j_stg), which forks into fct_revenue (j_rev) and dim_customer_snapshot (j_dim); j_mart builds rev_daily from those two. The exec dashboards read rev_daily live, and sales_sync reads only the current date partition of rev_daily and appends rows to Salesforce without deleting. Every job is idempotent per daily partition. On 12 March, gross revenue in rev_daily is found overstated for 2024-03-01 through 2024-03-03 because of a bad join in j_stg, and sales_sync has already pushed those three days. Which repair plan leaves both rev_daily and Salesforce correct?
MCQ-22An events table is partitioned by `event_date`, one partition per day: 1,180 partitions, ~35M rows each, about 3.2 years of history. Raw events stay in object storage for 400 days. Enrichment job v2.3 ran from 2024-03-04 through 2024-03-17 and wrote `event_ts_utc` five hours early, but only for the ~60% of rows that carried a `local_ts` field; the other 40% it wrote correctly. It was rolled back on 2024-03-18. A nightly rollup sums `bytes` per `event_date` and overwrites that day's aggregate. The last full backup was taken 2024-03-18 01:00. Which repair is correct?
MCQ-18A Debezium Postgres connector streams the `orders` table into a 12-partition Kafka topic. A custom sink task consumes that topic, calls `poll()`, and commits the returned offsets immediately: before the batch is written. The batch then goes to a warehouse bulk loader that flushes every 30 seconds and is not transactional with the offset commit. During a deploy the sink task is SIGKILLed 8 seconds after a `poll()` returned 400 records; none of that batch had been flushed. The consumer group restarts and resumes from the last committed offset. Which outcome and fix is correct?
MCQ-3An ingestion job syncs orders from a source where each committed transaction gets a monotonically increasing commit_seq, and each row also carries an application-supplied updated_at. The job checkpoints max(updated_at) from committed rows and rereads with updated_at > checkpoint. Checkpoint is 10:00:00.000. Commit_seq 811 commits at 10:02:31 updating order 9 with updated_at 10:02:30.000. Commit_seq 812 commits at 10:02:45 updating order 12 with updated_at 10:02:44.000. A transaction that updates order 7 with updated_at 10:01:10.000 is still open; the batch reads 811 and 812, publishes them, and sets checkpoint to 10:02:44.000. The open transaction then commits as commit_seq 813. On restart the job queries updated_at > 10:02:44.000 and never sees order 7. The source can backfill or correct updated_at to a value older than the transaction start. Which checkpoint value and predicate should replace the current scheme so this restart reads order 7, does not re-read orders 9 and 12, and lets the checkpoint advance monotonically after every batch?
MCQ-13A streaming job aggregates purchases into tumbling 1-hour event-time windows and emits a window's total when the watermark passes the window end. Allowed lateness is 30 minutes: the window's state is kept until the watermark passes window end + 30 minutes, and a record that lands in a still-retained window makes the job emit the updated total again. The watermark runs 10 minutes behind the largest event timestamp seen. The window [10:00, 11:00) has already emitted a total of 40. Then record X (event time 10:45, value 5) arrives while the watermark is 11:20, and record Y (event time 10:30, value 7) arrives while the watermark is 11:35. What does the sink receive for that window?
MCQ-9An hourly rollup service runs three identical workers. Each worker polls the `jobs` table every second, `SELECT id FROM jobs WHERE status = 'pending'`, and, when a row comes back, issues `UPDATE jobs SET status = 'running' WHERE id = $1` before starting work; there is no other locking. A run aggregates the completed hour into `hourly_revenue`, one row per product keyed by that hour, then advances `cursor.last_end`. Across yesterday's 24 jobs, five came out wrong: three are 3x and two are 2x, comparing `sum(amount)` in `hourly_revenue` with `sum(amount)` in `events` for the same hour. The other 19 match exactly. Each bad job has several `run started` log lines carrying the same `last_end` and `started_at` values inside the same millisecond, `jobs` holds no failed rows, and the log has no retry lines. `hourly_revenue` has exactly one distinct `window_end` per hour and no hour is missing. Which explanation fits these numbers?
MCQ-23An orders dashboard carries a 30-minute freshness SLA: the newest order event in the table must be no more than 30 minutes older than wall-clock time. At 09:45 the hourly batch reports success and writes 12,400 rows with updated_at = 09:45, but the CDC extract's watermark has been stuck at 08:00, so max(event_time) in the table is 08:00. Which reading correctly reports the SLA breach?
MCQ-24A nightly ETL incrementally updates reporting.orders from source.orders. This morning the pipeline reports: source row count 1,000,000; reporting row count 1,000,000; all 24 scheduler tasks succeeded; max(updated_at) in reporting is 02:14, inside the 06:00 SLA. Finance finds reporting SUM(order_amount) = $4,812,300 while source SUM(order_amount) = $4,798,100. Which daily data-quality check should the team add to catch this class of drift before finance does?
MCQ-16`fact_orders` loads from the orders service each night, and a gate decides whether the revenue dashboard publishes. Last night's partition holds the expected 1,204,880 rows, every `order_id` is unique, no `revenue_usd` value is null, and `max(loaded_at)` is 20 minutes old, so the gate's four checks (row count > 0, `order_id` uniqueness, not-null on `revenue_usd`, and freshness within 6 hours) all passed. The dashboard then published $1.42M for the day, but the orders service's own total for the same window is $1.01M: a currency conversion ran twice on the batch. Which additional check would have caught this before publish?
MCQ-15A nightly job loads a vendor CSV into `fact_orders` by column position. Its only validation is that the file has 12 columns and that each column parses as its expected type. `fact_orders.tax_amount` is documented in USD. The vendor's new release keeps 12 columns in the same order and the same types, but column 7 changes from tax in USD to tax in cents. The job runs green. What is the state of `fact_orders.tax_amount`?
MCQ-4An orders pipeline incrementally merges CDC events into `fact_orders` every 15 minutes. Each run passes: changed-key row counts match, and a checksum over rows updated in the last 24 hours matches. After a quarter, `fact_orders` has 1,000,000 rows and $10,080,000 revenue. A full rebuild from the immutable event log has 999,200 rows and $10,000,000 revenue. The team has never diffed the two. What does that diff most directly establish?
MCQ-21A daily DAG `daily_report` runs at 02:00 UTC. Upstream `load_events` is scheduled at 01:00 and normally finishes by 01:45, but in the last 10 days it finished at 03:10, 03:40, and 04:05 on three days. On two other days the source file was empty, `load_events` still succeeded, and `daily_report` published zeros. The current dependency is a fixed 30-minute sleep after 02:00. What change makes `daily_report` wait for the correct interval and avoid publishing zeros?
MCQ-20Postgres 15. `orders(id, tenant_id, created_at, amount)` has RLS enabled with `CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::int)`. A nightly job runs as `etl_svc`, the owner of `orders`, and builds the serving table: ```sql CREATE TABLE daily_revenue AS SELECT date_trunc('day', created_at) AS day, sum(amount) AS revenue FROM orders GROUP BY 1; ``` `daily_revenue` has RLS enabled with `CREATE POLICY tenant_isolation ON daily_revenue USING (true)`. The BI tool connects as `bi_reader`, runs `SET app.tenant_id = '<id>'` at connection checkout, then `SELECT day, revenue FROM daily_revenue WHERE day >= now() - interval '7 days'`. Tenant 7's dashboard shows $418,000 for yesterday, the sum across all 40 tenants, while tenant 7's own orders that day total $9,300. What is the cause?