Skip to content
Tech Interview Prep

Top 100 Data Architect Practice MCQs

Every question on one page: open one, choose an answer, and see why at once. Signed in, your latest answer to each question counts, and you can answer again after 24 hours.

Curated: · Written: · Reviewed:

Reviewed 100Review pending 0

Answers to reviewed questions count toward this role and the overall leaderboard; your latest answer to each one is the one that counts.

100 questions

MCQ-1`fct_orders` holds 1,440,000 rows and 480,000 distinct `order_id` values. `line_id` is populated on every row and `(order_id, line_id)` is unique. Every row carries `header_amount`, the order's total. Finance sums `header_amount` and reports $36.0M for the last 30 days; the order-header source reports $12.0M for the same window. Which explanation accounts for the gap?
MCQ-56A nightly publish writes fact_orders. The catalog entry declares grain order_id. The dbt model's unique test is on (order_id, line_no). Last night 6.1% of rows shared order_id; nine certified dashboards read the catalog grain and counted one per order. The publish job ran the dbt test and passed. Which statement explains why the publish passed while the dashboards undercounted?
MCQ-57A dbt project builds fct_order_lines from stg_orders joined to stg_order_lines and keys it on order_line_sk, which the source ERP re-issues every time a line is edited; each extract row lands as its own row. On the last full load, unique and not_null on order_line_sk are green across 12,480,000 rows, while (order_id, line_id) repeats on 41,000 pairs whose extra copies carry the same line_number and line_amount as their original, summing to $9.18 million of line revenue that the finance dashboard counts. The model is meant to hold one row per order line. Which change should ship?
MCQ-2fact_orders holds one row per order (2,400,000 rows this quarter) with order_total as its only measure and order_number carried on the fact itself as a degenerate dimension, because no order attribute has ever been worth a table of its own. A new requirement asks for an order's current status and every status change it went through in the last 90 days. The engineer adds order_status and status_changed_at to fact_orders and appends a fresh fact row on each status change. Nine days later, 640,000 changes have been loaded, the table holds 3,040,000 rows, and the nightly test asserting one row per order_number fails on exactly 640,000 orders, each duplicated pair carrying the same order_total and a different status. Which change should the team make?
MCQ-49An order pipeline lands one row per milestone event in fct_order_events (order_id, milestone, event_time): 2.0M orders a day across four milestones. The team adds fct_order_pipeline, one row per order with placed_at, packed_at, shipped_at, delivered_at and per-stage lag columns, loaded hourly by a job that processes only orders with new events since the last run. After the first run the table holds 2.0M rows. After the second run, in which 500 orders received a delivered event and no other milestones arrived, it holds 2,000,500 rows. What does the count show, and what should change?
MCQ-45A fact table sits at order-line grain with a revenue column that BI tools sum. Marketing wants every line attributed to each promotion that applied, and a line can carry several promotions. Which review comment should be accepted?
MCQ-29A team is standing up a new BI platform: dashboard and analyst SQL over fact tables of a few hundred million rows, dimensions of 40 to 150 attributes that change rarely, and no requirement for attribute-level history. Which modelling choice is defensible for this workload?
MCQ-7A fact table has eight low-cardinality boolean flags; three more are requested. Two of the existing flags have independent effective-dated history. The team is choosing between adding all flags as fact columns and creating a junk dimension for the flags that lack independent history. Which justification is sound?
MCQ-68A nightly load writes 400 Parquet files holding 18,000,000 rows keyed on (order_id, line_no). CI runs a uniqueness test on a 1% sample of files and it is green. The downstream join to payments produced 14 billion intermediate rows and timed out the close; 180,000 rows were later found to repeat a key already present, spread across 2 of the 400 files. Which change to the CI check would have failed on this load?
MCQ-93A retail company runs a 3NF PostgreSQL OLTP schema. The orders table has 1,000 rows and $1.4M total revenue. order_items has 3,500 rows (3.5 per order), and shipments has 2,000 rows (2 per order). An analyst builds a revenue dashboard by joining orders, order_items, and shipments in one query and summing orders.order_total. What does the dashboard report, and what should the team do?
MCQ-98`fct_orders` has one row per order with `order_amount`; `fct_shipments` has one row per shipment with `ship_amount`. The certified metric `total_commercial_value` is defined as `SUM(order_amount) + SUM(ship_amount)`. For Q1 it returns $87M, while the finance ledger says $60M. Every Q1 shipment belongs to an order placed in Q1, so the $27M of shipment amount is already inside the $60M of order amount. What should the analytics engineer do?
MCQ-47A retail warehouse has fact_sales at 2.2B rows and dim_customer at 180M rows. The customer dimension is a star: one wide table with 40 attributes, including district_id and region_id. Region hierarchy changes independently: last month a reorg moved 3% of districts, affecting 12M customers. Because dim_customer is type-2 versioned on any attribute change, that reorg inserted 12M new customer rows and the nightly dimension rebuild took 6 hours against a 2-hour SLA. Region-level revenue queries join fact_sales to dim_customer, then district, then region, and p99 is 180s. Which change is justified?
MCQ-46A retail warehouse's bus matrix ticks the cell for fact_sales × dim_customer. dim_customer is rebuilt nightly from the CRM extract: 480,000 members keyed on customer_key. A new finance dashboard inner-joins fact_sales to dim_customer on customer_key. For Q1 it returns 9,672,000 of the fact's 12,400,000 rows and $412.6M of the $529.0M the ledger reports. The 2,728,000 unmatched rows carry ERP customer numbers the CRM extract has never contained. Which diagnosis and action is correct?
MCQ-96A retailer's customer dimension holds `date_of_birth` as an SCD type 0 attribute, and the nightly load rejects any change to it. A signup-form bug wrote a wrong DOB for 18,400 customers over two weeks; correcting them moves the 'age 25–34' band by 4 percentage points. Analytics needs the corrected values live, and compliance needs to know who changed each one. What should the team do?
MCQ-5A `dim_customer` table tracks `region` with SCD type 3: columns `region_current`, `region_previous`, and `region_changed_at`. Customer 8841 moved EMEA → APAC in January 2024 and APAC → AMER in March 2025. The pipeline keeps no other history table. An analyst asks for the region the customer was in during 2023. What does the row hold, and what should the team do?
MCQ-99A retail warehouse uses SCD type 6 for dim_customer. Customer 42 moved from West to East on 2024-03-01. dim_customer now holds two rows: SK 1001: customer_id 42, region West, effective_start 2023-01-01, effective_end 2024-02-29, is_current false, current_region NULL. SK 1002: customer_id 42, region East, effective_start 2024-03-01, effective_end 9999-12-31, is_current true, current_region East. fact_sales holds USD 9.1M of 2023 sales and USD 55.9M of 2024 sales for customer 42. The as-was report joins fact_sales to dim_customer on customer_id and sale_date between effective_start and effective_end, then groups by region; it returns USD 9.1M West and USD 55.9M East. The as-is report uses the same date-range join but filters current_region = 'East' and groups by current_region; it returns USD 55.9M, while the ledger total for customer 42 is USD 65.0M under East. The BI tool's join pattern cannot change, so only the dimension load can be altered. Which dimension load action fixes the as-is report while keeping the as-was report correct?
MCQ-4fct_orders stores customer_id and order_ts and no dimension surrogate key, and replaying or backfilling the fact table is not available this quarter. dim_customer is loaded as SCD type 2 with closed-open valid_from/valid_to ranges plus an is_current flag. Customer 42 placed an order on 2024-01-10 while its region was EMEA. On 2024-03-01 the source changed that customer's region to APAC, so the loader closed the EMEA row (valid_from 2023-06-01, valid_to 2024-03-01) and inserted the APAC row (valid_from 2024-03-01, valid_to 9999-12-31, is_current true). The report joins f.customer_id = d.customer_id and d.is_current, and Q1 EMEA revenue now reads 3.62M against 4.10M before the change, with zero new or cancelled orders. Which change restores the January figure?
MCQ-3A retail warehouse keeps `dim_customer` as a type-2 SCD: `customer_sk` is assigned by the warehouse, `crm_id` is the CRM's UUID, and each version row carries `effective_start`, `effective_end` and `is_current`. `fct_orders` stores `crm_id` and joins to the dimension row where `is_current = true`. The CRM merged 18,400 duplicate person records into 9,200 survivors, retiring the other 9,200 `crm_id`s; 6.3 million order facts still carry retired `crm_id`s and no longer resolve to any dimension row, so they drop out of customer-level reporting. Auditors require that an order placed before the merge still reports against the customer as it was known then. Which change fixes the join?
MCQ-97An e-commerce warehouse keeps a type-2 customer dimension of 1.1 million rows. The low-cardinality attribute risk_band (5 values) changes for about 40% of customers each day, and because it sits on the customer row the dimension versions roughly 440,000 rows daily: about 160 million rows added in a year. A 30-day fact scan that joins to the customer dimension now spills 2.4 TB. The team must keep risk_band as of each fact date. Which change should they make?
MCQ-100A retailer's warehouse keeps `dim_customer_current` (one row per customer, unique on `customer_id`) and `dim_customer_history` (append-only, with `valid_from`/`valid_to`). `fct_orders` holds `order_id`, `customer_id`, `order_ts`, `amount` and no customer name. Legal renames arrive as full snapshots and the source overwrites the old name, so one rename of customer 4471 restated eight years of "name at order" in the app and 2.2 million order PDFs stopped matching the warehouse. Which load-and-join design keeps those PDFs matching the name as of each order?
MCQ-76Store 417 moved 8 km from the West region into Central on 2024-03-01. The sales fact table holds 1.2M rows from 2019-2024 and joins to dim_store on store_key. Finance must report 2023 West sales as they were on the sale date; logistics needs store 417's current region. Overwriting dim_store.region would move $6.4M of 2023 West sales to Central, dropping West from $48.0M to $47.7M and missing a $48.0M bonus threshold by 0.3%. Which design meets both requirements?
MCQ-44A nightly job appends a full 4.1M-row customer snapshot into dim_customer, and a CDC stream MERGEs about 12,000 change rows a day into the same table. After seven nights dim_customer holds 28.7M rows, COUNT(DISTINCT customer_id) is still 4.1M, and a downstream SUM(lifetime_value) runs about 7× the expected figure. The unique test on customer_id samples 500 rows and passes. What is the correct fix?
MCQ-39A fraud team trains on 12 months of labeled card transactions. The feature customer_risk_score is read from dim_customer, a type-1 table updated in place. Offline AUC is 0.94; production AUC is 0.61. An audit finds the score was updated after the transaction for 18% of training labels, a median of 12 days later. The team rebuilds the feature as a point-in-time join against the type-2 history as of each transaction's event time and retrains on the same 12 months. What should they expect?
MCQ-58A metric store defines `net_revenue` as `SUM(o.gross_amount - r.refund_amount)` over `fct_orders o INNER JOIN fct_refunds r ON o.order_id = r.order_id`. `fct_orders` holds 1,000,000 orders totalling $84.0M gross. `fct_refunds` holds at most one row per order and totals $5.28M. 80,000 orders (8%) have no refund row and total $9.6M gross. The warehouse's certified fact reports net revenue of $78.72M. What does the metric store report?
MCQ-36A nightly orders pipeline loads `fact_orders` under a contract that requires `order_id` unique and `amount` non-null, with severity `error`. Last night's staging load held 1,900,000 rows; the uniqueness test failed on 76,000 of them, all from the producer's new v3 writer, which emits one retry row per failed payment. The consumer list is 3 dashboards plus the refunds job, and v3 ships to all regions tomorrow. What should the pipeline do with last night's partition?
MCQ-80A dbt model fct_orders selects 80 columns from stg_orders and writes 80 columns; revenue_usd is computed as sum(quantity * unit_price). A refactor replaced that expression with the literal 0. The job-level lineage graph still shows fct_orders <- stg_orders, the model's tests (not_null and unique on order_id, revenue_usd >= 0) all pass, and the GMV dashboard reports $0 for two days. Which check would have failed on the refactor commit?
MCQ-81A lineage service reports column coverage as (certified columns with a column-level lineage facet) / (certified columns). At the start of the quarter it reports 70%: 1,400 certified columns, 980 with a facet. Over the quarter the team certifies 300 columns produced by a notebook job whose tooling cannot emit facets, and backfills facets for 120 columns that were already certified. At the end of the quarter, which reading of the service's report is correct?
MCQ-82A data platform's quality dashboard shows one green tile: 99.2% of all tests passed in the last 24 hours. The payments product's uniqueness check has been red for 8 days, and $2.2 million in duplicate payouts have shipped. The platform's quality SLA is defined per product, per check, per window. What should the on-call engineer do first?
MCQ-23A daily orders data product promises a partition by 06:00 UTC, completeness within 1 percent of that day's source row count, uniqueness on order_id, and downstream jobs blocked whenever a check fails. The implemented check compares the loaded count with the trailing 7-day average under a 10 percent tolerance. On a sale day the source holds 3,000,000 rows, the trailing average is 2,400,000, and the 06:00 partition holds 2,610,000. The check passes, the pricing job runs at 06:15, and it falls back to the previous day's price for every order it cannot find. What does the evidence establish?
MCQ-24A weekend Python notebook wrote 380,000 rows into warehouse.customer through a direct connection, bypassing the scheduler and emitting no OpenLineage events. The crawled lineage graph still showed warehouse.customer sourced from crm.customer, so downstream consumers read the wrong source for 11 days. Which change makes lineage evidence for this class of failure?
MCQ-35A `core.events` table has nine writers and no catalog owner. Team 7 changed `event_id` from `INT` to `STRING`; 11 downstream jobs broke, and rollback took 16 hours. The table now holds 2.1B rows of mixed types. Which fact best explains why the break reached production?
MCQ-70A marketplace's order service has been event-sourced since 2021: each command the order state machine accepts appends one domain event (OrderPlaced, OrderCancelled, OrderReinstated, OrderRefunded) to an append-only log, and rejected commands append nothing. Most reinstatements are payment-auth retries, logged as OrderCancelled followed by OrderReinstated. Forty BI dashboards read the log directly with hand-written SQL. Q3 held 500,000 orders and 41,200 OrderCancelled events across 34,000 distinct orders; 10,500 orders ended the quarter with OrderCancelled as their final event. At a $485 average order value those readings are $16.5M and $5.1M at risk. Finance needs the net rate (2.1%) for revenue at risk, Ops needs the gross rate (6.8%) for fraud review, and any definition change has to be recoverable for past quarters. Last quarter an edit to one dashboard's SQL moved its number for six weeks before anyone noticed. Which change should the team make?
MCQ-31A retail chain stores `sales(event_ts timestamptz, store_id, amount_cents)` and `stores(store_id, tz)` with IANA zone names. The nightly close derives the civil day as `(event_ts AT TIME ZONE 'UTC')::date` and sums per store. For the New York stores (America/New_York, UTC-4 on 2024-06-15), the close labeled 2024-06-15 is $86,000 short of the POS total for that local day, and the close labeled 2024-06-14 is $86,000 over; no rows were lost or duplicated. Which statement explains the discrepancy?
MCQ-8A shipment fact table is partitioned by ingestion date. A type-2 dim_carrier table records each carrier's service tier with valid_from/valid_to. The 2 March fill-rate dashboard joins each fact to the dimension row whose validity window contains the fact's ingestion date, and filters facts to the 2 March ingestion partition. On 8 March, 190,000 shipment events with event time 2 March arrive and land in the 8 March partition; the 2 March dashboard reads 88.0% fill-rate, while a manual count over all 200,000 events with event time 2 March gives 92.1%. Which change makes the dashboard read 92.1%?
MCQ-30A mobile checkout buffers purchase events offline and uploads them when connectivity returns; roughly 4% of events arrive 2–6 hours after the purchase. Finance reports hourly revenue by the hour the purchase happened, and an on-call alert fires when any hour drops more than 30% versus the same hour last week. Which policy should the team adopt?
MCQ-41A Flink job reads clickstream events from Kafka, assigns event time from each record's `event_ts` field, and applies `WatermarkStrategy.forBoundedOutOfOrderness(Duration.ofSeconds(5))` before hourly tumbling windows. A mobile SDK retry bug makes roughly 8% of events arrive 10 to 40 minutes after their `event_ts`. The streaming hourly counts run about 8% below the nightly batch reconciliation of the same topic. What is the cause and the fix?
MCQ-84A clickstream job assigns each click to a 30-minute tumbling window keyed by user_id, and a visit is defined as 30 minutes of inactivity. One user clicks at 10:52, 11:05, 11:20, 11:35, 11:50 and 12:05. How many sessions does the tumbling job emit for this user, and how many would a 30-minute inactivity session window emit?
MCQ-85A Flink 1.18 job reads a 16-partition Kafka topic and computes 1-minute event-time tumbling windows. At 09:00 the producer for partition 7 dies; the other 15 partitions keep publishing on time. From 09:00 the window operator emits nothing, and by 11:30 the freshness SLO is still breached even though every event from the 15 live partitions arrived within 200 ms of its timestamp. What is the correct diagnosis and fix?
MCQ-83A payments pipeline runs Spark Structured Streaming 3.5 against Kafka: ```python (payments .withWatermark("event_time", "10 minutes") .groupBy(F.window("event_time", "1 hour")) .agg(F.sum("amount").alias("revenue")) .writeStream.outputMode("append") .format("parquet") .option("path", "s3://rev/hourly") .start()) ``` A dbt model reads the output as `SELECT window_start, SUM(revenue) FROM hourly GROUP BY 1`. The 14:00–15:00 window was emitted at 15:10 with revenue = $96,000. At 15:40 the producer replays 20,000 payments whose event_time falls in 14:00–15:00, totalling $4,000: 18,000 of them ($3,600) are byte-identical duplicates of payments already counted, and 2,000 ($400) are genuinely new. The watermark at 15:40 is 15:30. What does the dbt model report for 14:00?
MCQ-66A Kafka consumer loads events into `fct_events` once an hour. The freshness SLO requires hour H's events to be queryable by H+30 min. On Tuesday the consumer group's offsets were reset to earliest, so every hourly run re-consumed old events, succeeded, and committed rows stamped `loaded_at = now()`. The freshness tile reads `max(loaded_at)` and stayed green all day, yet analysts found Tuesday's events missing. Which change makes the SLO fail on this failure?
MCQ-67A nightly load copies `orders` from Postgres to Snowflake. Its completeness contract compares `SELECT COUNT(*) FROM orders WHERE created_at >= :run_date` on the source with the loaded row count for the same predicate, both run in a session whose timezone was switched from UTC to America/Los_Angeles 21 days ago. Last night both sides returned 3,808,000 rows and the contract passed, but the APAC revenue dashboard has read $0.00 for 21 days; the source holds 192,000 APAC orders that day (4.8% of 4,000,000) worth $3.1M. Which change makes the contract catch this?
MCQ-69A retail warehouse loads `fct_orders` at 02:00 and `dim_customer` at 04:00 from separate domains. The order pipeline resolves `customer_sk` by lookup and writes NULL when the customer row is not yet present. Finance reads dashboards that inner-join `fct_orders` to `dim_customer` at 09:00, and three other marts inner-join the same table. Over the last 8 Mondays the customer job finished between 01:30 and 11:00. Last Monday the order load wrote 41,200 rows with NULL `customer_sk`; the dashboards reported $5.9M less GMV than the source system, and the gap closed only after Tuesday's customer load backfilled the keys. Which change should the review require?
MCQ-52An Iceberg table is written with `PARTITIONED BY (days(order_ts))`; the spec is fixed by the platform team and 40 consumers read it. One dashboard filters `WHERE DATE(order_ts AT TIME ZONE 'US/Pacific') = DATE '2024-03-05'` and its plan scans 18,400 files / 6.2 TB. The same Pacific day filtered as `order_ts >= TIMESTAMP '2024-03-05 08:00:00' AND order_ts < TIMESTAMP '2024-03-06 08:00:00'` scans 48 files / 84 GB. Which diagnosis and fix does this measurement support?
MCQ-15A table `events` is partitioned by `days(event_time)`. On 2024-03-10 the spec is evolved to `hours(event_time)`; files written before that date keep the day spec. Two single-hour queries each return 1.2M rows: Q1 for 2024-03-05 14:00–15:00 and Q2 for 2024-03-15 14:00–15:00. The scan report shows Q1 read 6.8M rows from one file and Q2 read 1.2M rows from one file. Which explanation accounts for the difference?
MCQ-9A retail orders pipeline writes each order to fact_sales within seconds of checkout, keyed by a customer surrogate; dim_customer is loaded nightly from the CRM. On day 1, 27,400 orders from first-time buyers arrive before their customer rows exist, and the load job drops any fact whose customer_id is not yet in dim_customer. Day-1 revenue is understated by $2.1M, and when the nightly load catches up 8 days later the same revenue reappears as a spike in week 2. The team must keep every fact, report revenue in the week the order occurred, and still track per-customer repeat purchases; customer_id is stable but name and segment can change. Which change to the pipeline meets these requirements?
MCQ-48Three date dimensions were loaded from three source extracts. Fiscal week 12 starts on a Monday in order_date_dim and a Thursday in ship_date_dim, and the ship-versus-order report has been 9% off for a quarter. The extracts belong to another team and cannot be changed; the fact table and ETL are yours. Which change should the team make?
MCQ-60A retailer's comp-sales dashboard joins each fact row to the date dimension 364 days earlier and groups by fiscal week. Its 4-5-4 calendar made FY2023 a 53-week year ending Saturday 2024-02-03; FY2024 is a 52-week year starting Sunday 2024-02-04. FY2024 week 1 (2024-02-04 to 2024-02-10) net sales were $12.4M; FY2023 week 1 (2023-01-29 to 2023-02-04) was $10.0M and FY2023 week 2 (2023-02-05 to 2023-02-11) was $9.0M. The dashboard reports FY2024 week 1 comps at +37.8%. What is the correct comparable-week comp?
MCQ-59A revenue mart converts JPY invoices to USD with `JOIN fx_rates r ON r.currency = f.currency AND r.rate_date = CURRENT_DATE`. fx_rates is append-only, one row per currency per day. Yesterday's published report showed ¥1.20B of July JP revenue as $8.00M at 150.0 JPY/USD. Today, with no new or changed invoices, the same July rows report $8.43M, and today's rate is 142.3 JPY/USD. Which change fixes the restatement?
MCQ-32A revenue dashboard sums an `amount` column over a 14-day window and reports 162,000,000. The fact table has no currency column: US rows store USD and JP rows store JPY, and the two entities contributed 40,000 rows each, summing to 81,000,000 apiece. USD/JPY moved from 145.0 to 109.4 across the window. Finance says the total is wrong. Which change makes the certified metric correct?
MCQ-42A streaming job reads `payments` from Kafka at 4,000 events/s and appends each event to a warehouse table; the sink commits the Kafka offset only after the warehouse write returns. During a 3-hour incident the job restarts 14 times, and each restart replays the events written since the last offset commit: one full 200 s interval, 800,000 events. After recovery `COUNT(*)` is 13.3 million and `COUNT(DISTINCT user_id)` is 2.1 million. Which change makes the replay produce the correct table?
MCQ-86A sink writes one warehouse row per Kafka record and keys idempotency on hash(produced_at), a field the producer's retry client refreshes on every attempt. During a Monday broker incident, 2.8 million records were each retried 3 times and the table gained 8.4 million extra rows. The team is replacing the key. Two changes are already scheduled: a repartition of the topic from 24 to 48 partitions, and a republish of the same events under a new topic name. Which key holds the sink at one row per business event through the retries and both scheduled changes?
MCQ-87A third-party service you cannot modify publishes order events at-least-once. On a normal day 12,000,000 events arrive; during a broker rebalance 4% were redelivered. `raw_orders` is append-only by policy and stamps each delivery with a fresh `ingest_ts`, so redelivered rows are not byte-identical. A nightly job loads `fct_orders` with `INSERT INTO fct_orders SELECT * FROM raw_orders WHERE load_date = CURRENT_DATE`; `fct_orders` is joined to `dim_customer` on `order_id`, and finance reconciles one row per order. Forty consumers read `fct_orders`, and the revenue dashboard's `SELECT SUM(amount) FROM fct_orders` now reports about 4% more than the ledger. Which change fixes the overstatement?
MCQ-88An orders table is partitioned by `order_date`. A snapshot dump lands daily at 14:00 and is written with `INSERT OVERWRITE` on the day's partition; an hourly CDC job `MERGE`s late changes into the same partition at :00. On 2 March the 20:00 backfill ran `INSERT OVERWRITE` on partition `2024-03-02` from the 14:00 dump, whose max `updated_at` is 14:00. The next morning, 140,000 orders with `updated_at` between 14:00 and 20:00 are missing from that partition. Which change prevents the same loss on the next backfill?
MCQ-43A daily pipeline loads `orders_delta_2024-03-02.parquet` into `fact_orders`, partitioned by `event_date`. The file holds 24,000,000 rows: one per order touched that day, carrying that order's current values, including orders already present in the partition, and it is not the complete contents of 2 March. The loader is `INSERT INTO fact_orders SELECT * FROM delta`. After a backfill re-ran the same file, the 2 March partition holds 48,000,000 rows, SUM(order_value) is 2,400,000,000 (was 1,200,000,000), and AVG(order_value) is unchanged at 50. Which change makes re-running the same delta leave the 2 March partition unchanged?
MCQ-25Fourteen producers publish Avro to the subject orders-value, set to BACKWARD compatibility. The currency field is declared as a plain string; the allowed codes USD, EUR, GBP live in a contract file that a weekly lint reads, and nothing on the publish path enforces them. A loader appends every record it reads to the append-only table stg_orders. The scheduler retried the loader once after a 300-second timeout, so batch B12's 40,000 rows were appended twice; none of those order_ids had been loaded before. A promotion job runs every 10 minutes: it deletes from stg_orders every row whose order_id already exists in fact_orders, appends the remainder to fact_orders, and clears those rows from stg_orders. fact_orders has no unique constraint on order_id. In the same hour, 900 records, all from other batches, carried currency 'XYZ'. What does fact_orders hold for these two cases once the 10:10 run finishes?
MCQ-71An orders service writes the order row and an outbox row in one Postgres transaction. A poller reads unpublished outbox rows in batches of 500, publishes each batch to Kafka, waits for the acks, then marks the batch published. During a 25-minute Kafka outage 9,400 orders committed. After recovery the poller published all 9,400, but it was killed once after Kafka acked batch 12 and before that batch's mark committed, so on restart it republished batch 12. The warehouse now holds 9,900 rows for those 9,400 orders. Which change removes the 500 duplicates without dropping any order?
MCQ-34An order service dual-writes to Postgres A and Postgres B in two separate transactions. The API mints a new order_id on every HTTP attempt; retries keep the same payment_intent_id. A nightly warehouse job builds fact_orders as SELECT order_id, payment_intent_id, amount, status FROM A.orders UNION SELECT order_id, payment_intent_id, amount, status FROM B.orders and sums amount by order_id. After a five-hour timeout incident, the job reports 1,012,000 rows and revenue 1.2% high against the ledger's 1,000,000 orders. Traces show 0.6% of calls committed in A, timed out before B, and were retried; the retry wrote a second row to A and the first row to B. Finance requires any A/B disagreement to be reported, not silently resolved. Which next step fixes the pipeline?
MCQ-26An `orders` table in the warehouse (4,000,000 rows) is written by two jobs. A nightly batch job at 02:00 merges a full OLTP snapshot exported at 23:59 into `orders`: `MERGE INTO orders t USING snapshot s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET t.amount = s.amount, t.status = s.status WHEN NOT MATCHED THEN INSERT (...)`. A Debezium CDC stream applies insert, update and delete events to the same table continuously, keyed on `order_id`, with no change-sequence column. Between 23:59 and 02:00 the OLTP system commits 2,400 updates and 300 deletes, all applied by the CDC stream before 02:00. After the 02:00 batch job finishes, what does `orders` show for those 2,700 rows?
MCQ-53An hourly Iceberg maintenance job runs `rewrite_data_files` and then `expire_snapshots` with `older_than` set to two hours before each run. A bad load lands at 02:00 and is found at 08:00; the on-call's rollback to the 01:00 snapshot fails because the catalog no longer has it. The team needs a 6-hour rollback window. Which change delivers it?
MCQ-89An orders table is partitioned by order_date. At 02:00, an incremental job upserts today's partition from Kafka and writes 1.2M keys. A backfill job reprocesses the last 90 days from a Parquet snapshot taken at 23:00 yesterday; its filter is order_date >= current_date - 90, so it also writes today's partition. The backfill takes 4 hours and writes each key once. The table has no partition-level lock. The dashboard queries today's partition continuously and must never show an order's status from before its latest Kafka update. In yesterday's snapshot, 480k of today's orders had status='shipped'; by 02:00 those 480k are 'delivered' in Kafka. What should the team do?
MCQ-90A Snowflake warehouse keeps 90 days of time travel and nightly clones of `events`. A bad backfill wrote NULL region codes on 3 March; on 14 March an engineer restored `events` to the 3 March 02:00 clone to clear them. `erasure_requests` shows 4,180 completed requests in the last 90 days, 260 of them completed between 3 March 02:00 and 14 March. Analyst SELECT on `events` is blocked until the fix is verified. Which re-run clears the block?
MCQ-74An Iceberg v2 table is written by a Flink CDC job that commits every 5 minutes and writes 10 delete files per commit (one per touched partition). A 12-query dashboard pack runs at 06:45 and must return by 07:00; the fraud dashboard's contract requires data no older than 5 minutes. Compaction last succeeded 40 hours ago, leaving 4,800 delete files. This morning each dashboard query scanned all 4,800 delete files, the pack consumed 9× its normal slot-hours, and it returned at 07:22. The compaction job rewrites the whole table in about 90 minutes. Which change gets the pack back under 07:00?
MCQ-75An events table holds 400 days for 4.1M customers: 2 TB, about 2 billion rows. Two queries must run in seconds: a point lookup for one customer on one day, and a daily rollup over all customers for one day. The table is currently partitioned by customer_id: 4.1M partitions, 11 minutes of planning, and the point lookup was killed before returning a row. Which layout serves both queries?
MCQ-12A producer added a required `channel` field to an existing Avro topic and registered the new writer schema. The new reader schema declares `channel` with no default. Decoding the 14 days of retained files (2.8 million records) written before the change fails, and the pipeline's retries have already duplicated 410,000 facts. Which change lets the new reader decode the retained files?
MCQ-73A warehouse replays 14 months of Avro order files. The writer schema (v1) has no `channel` field. The reader schema (v2) declares `channel` as a string with default `"web"`. The warehouse column `channel` is declared NOT NULL. 22% of the historical orders were placed on mobile. What does the replay produce?
MCQ-13A payments service writes `Order` protobuf messages to a Kafka topic with 7-day retention. Field 7 is `int64 amount_cents`. A schema revision deletes field 7 and adds `int64 item_count` at field 7; the producer deploys on Monday, and the revenue job plus two dashboards decode the topic with the new schema. What should the review require?
MCQ-14An Iceberg v2 `orders` table has columns `order_id` (field id 1), `amount` (field id 2), and `ts` (field id 3). A migration runs `ALTER TABLE orders RENAME COLUMN amount TO amount_usd`. Three consumers touch the table: a Spark batch job running `SELECT order_id, amount FROM orders` with a schema cached 12 hours earlier; a JDBC report that reads result-set column 2 by ordinal; and a Flink job that writes new rows positionally as `(order_id, amount_usd, ts)`. Which statement about what happens is correct?
MCQ-16A payments team writes daily Avro files to object storage under one Schema Registry subject whose compatibility level is BACKWARD. The warehouse job that reloads the last 14 months of files was upgraded to reader schema v14 and now fails on every file written before v9 with `Missing required field currency`. v9 added `currency` with default `"USD"`; v14 removed that default. The registry accepted every version. Which change prevents this class of failure?
MCQ-72An orders consumer failed to decode 2.1 million records over 40 minutes after a shipments schema change. The consumer is configured with `use.latest.version=true` against subject `common-value`, which both orders and shipments use. Each order payload contains a schema id. `common-value` added a required `zipPlus4` field. Which diagnosis explains the failure?
MCQ-95A nightly Spark job reads raw JSON events from S3 with schema inference and feeds a certified revenue dashboard whose query is `SELECT SUM(CAST(amount AS DOUBLE)) FROM events`. On day 1 a producer changed `amount` from a JSON number to a string with a currency suffix (`"12.50 USD"`). For 3 days the dashboard showed $0 revenue and the job exited 0. A separate ML feature job that writes the same events into a typed Parquet table with schema-on-write rejected the day-1 batch immediately. Which explanation accounts for the difference?
MCQ-94A payments team shreds its JSON events into a warehouse table using a fixed column list; keys not on the list are dropped, and the raw payload string is retained for 90 days. A release added `payer_tax_id`; six weeks later an audit found 44,000 tax ids sitting unclassified in the raw payload column, with no shredded column for them. Compliance now requires that any identifier absent from the reviewed PII catalog be quarantined within one load cycle, and the producer ships weekly releases with no schema registry. Which change to the shredding contract meets that requirement?
MCQ-54A promotions dashboard's attributed revenue drops 9% overnight. The landing zone writes Avro with a schema registry; the curated Parquet job builds its projection from an explicit list of 40 columns. Two days earlier a producer added `promo.discount`, nested inside the event record. The raw Avro files contain the field; last night's curated Parquet rebuild does not. Which explanation fits?
MCQ-55A team must standardise on one table format for a 2.8B-row events table on S3, read by Trino and written by 20 concurrent Spark MERGE jobs. Next quarter the partition key changes from day to hour; rewriting the 2.8B rows would take about 9 hours and add 1.4 PB. All three candidates (Hudi, Iceberg, Delta) pass the team's 100M-row MERGE and 1-day-filter benchmarks. Which choice meets every requirement?
MCQ-17A team adds `region_code CHAR(2)` to the `orders` table (840M rows), backfills every row, then runs `ALTER TABLE orders ALTER COLUMN region_code SET NOT NULL`. The column has no default. A nightly loader still inserts with `INSERT INTO orders (order_id, amount_cents, created_at) SELECT order_id, amount_cents, created_at FROM staging`, naming only the original columns. On PostgreSQL 16, what happens on the loader's next run?
MCQ-28A checkout service reads every user's profile by user_id: 5,000 keyed reads/s in every hour of the day, p99 SLO 20 ms, and a profile edit must be visible to that user's next request within 1 s. The profiles live in the columnar warehouse that BI also queries. Upstream files land at 02:00, the contract requires them merged within the hour, and the merge drives profile-read p99 from 18 ms to 940 ms and times out 2.4% of checkout sessions for 47 minutes, 11 nights running. With every BI query paused during a 02:00 merge, profile p99 is still 940 ms. A projection of the same profiles into a keyed serving table fed by the warehouse change stream reads at 18 ms p99 and lags 300 ms at peak write rate. Which change should ship?
MCQ-91A ranking model scores 0.84 AUC offline and 0.68 in its first live week. Two features are suspect. `orders_30d` is built in training by `WHERE created_at >= now() - interval '30 days'` run at 03:00 UTC, and in serving by a per-user counter that resets at the user's local midnight. `spend_30d` excludes refunded orders in training and includes them in serving. On 1,000 sampled production ids, top-decile overlap between the training and serving feature vectors is 93%, while exact per-feature match is 58% for `orders_30d` and 91% for `spend_30d`. What does this measurement establish?
MCQ-27Orders reach a retail platform two ways. A dbt job at 02:00 builds fct_orders in the warehouse from the day's events. A Spark job writes the same events as 60-column Parquet into an S3 prefix with one directory per day (dt=YYYY-MM-DD), overwriting the partitions it rewrites. The ML team trains from the Parquet copy because the warehouse table keeps only 12 of those 60 columns, and retraining is triggered on demand several times a day, so the Spark job cannot be pinned to a slot after the dbt run. Last Tuesday the backfill died at 03:20 with 12 of the day's 31 partitions rewritten. The 07:00 training run listed the prefix, took those 12 alongside 19 partitions from the previous load, trained on a day that never existed, and the order-count feature came out 6% low. Which change gives the ML reader the day as of the last completed load, even when a run dies halfway?
MCQ-10A customer MDM hub builds golden records from two sources: Billing, the system of record for billing address and legal name, and CRM, the system of record for account owner and segment. Survivorship is 'most recent updated_at wins' per attribute across both sources. Last night's CRM export stamped all 410,000 of its rows with the export time, including rows where no field changed. After the load, 19,200 golden-record addresses had flipped to CRM values and 1,140 invoices were mailed to former offices; Billing's addresses were correct in every case. Of the flipped rows, 3,400 had also been genuinely edited in CRM by sales reps that day. Which change guarantees CRM can never win the billing address attribute while leaving CRM authoritative for account owner and segment?
MCQ-11A retail bank builds one golden customer record nightly from three sources: the core billing system (contract address, the address legal notices must use), the CRM (service address), and a marketing platform that rewrites the address for about 40% of customers with the last campaign click. Survivorship is most-recently-updated non-null wins, applied to the whole row, so the winning source supplies every attribute. Last month 6,800 of 41,000 contract addresses were replaced by campaign addresses, 420 dunning letters went to campaign addresses, and collections paused for 38 days. Legal must keep using the contract address; marketing must keep the most recent campaign address for targeting. Which change meets both requirements?
MCQ-6A retail warehouse loads sales.customer keyed on crm_id (one row per account, 240,000 rows) and support.customer keyed on email, 72,000 rows. 12% of those emails (8,640) are shared mailboxes, each serving exactly 2 accounts. The cross-domain dashboard "accounts with open tickets" joins the two tables on email and has reported 8,640 fewer accounts than finance's account count for six weeks. The sales load cannot change this sprint. Which fix makes the dashboard correct?
MCQ-22Four domain teams each publish a `customer` data product. Finance joins them by email and cannot attribute 7.2% of quarterly revenue ($18M) to a single person because the same customer appears under different local keys. The domains own their pipelines and SLAs, and the central platform team has no authority to take them over. Finance needs cross-domain customer identity for next quarter's close. Which policy should the platform team adopt?
MCQ-61A B2B SaaS company's `fct_bookings` joins `dim_org_hierarchy` on `org_id` alone. Q1 closed with $22M of bookings under the Platform division. On April 3 the Data Platform team moved into a new AI Infrastructure division, and the Q1 close pack now shows that $22M under AI Infrastructure, so it no longer reconciles to the filed Q1 numbers. Finance needs the close pack to keep reporting the Q1 org, the live dashboard to show the new one, and analysts to reproduce the org as of any past date. The close pack must be reproducible from the warehouse, which three other teams also query. Which change should the review accept?
MCQ-62A retail warehouse builds customer 360 by joining orders to a person dimension on `lower(email)`. The person dimension holds one row per email. An audit of 2.4M order rows finds 610k distinct emails, 55k of which map to two or more loyalty accounts; the largest maps to 14 accounts and $1.8M of orders, and marketing has flagged it as the top VIP. The team proposes to keep the email join and add a window function that assigns each order to the account with the most recent login among the accounts sharing that email. What does this change produce?
MCQ-63A retail lake holds two customer-identifier columns. `email_sha256` is an unsalted SHA-256 of the lowercased address; `email_token` is a random token issued by a vault that is the only system able to map a token back to an address. A security team joins both columns against a leaked list of 180 million addresses: 62% of the hash column resolves to a real address within 4 hours, and 0% of the token column does. The catalog labels both columns "pseudonymised". An erasure request arrives for a customer whose rows appear in both tables. Which handling is correct?
MCQ-18An audit of a Snowflake warehouse finds that `analytics.customers.email` carries the PII tag and is masked for the BI role, but a nightly job rebuilds `sandbox.customers` with `CREATE TABLE sandbox.customers AS SELECT * FROM analytics.customers`, and last week a BI service account exported 1.1 million raw addresses from the sandbox table. The BI team must keep querying `sandbox.customers` for reporting, and the copy job must keep running. Which change stops raw addresses leaving the sandbox?
MCQ-19Snowflake. `analytics.customers` holds 4.2M rows across 6 regions; the EU region holds 700,000 of them. A row access policy `region_filter` limits role `SUPPORT_ANALYST` to its own region. A masking policy on `tax_id` returns NULL for `SUPPORT_ANALYST`. Analyst `ana` (role `SUPPORT_ANALYST`, region EU) runs: ```sql SELECT COUNT(*), COUNT(tax_id) FROM analytics.customers; ``` What does the query return?
MCQ-78A retail warehouse has 40 domains. HR tags salary columns `confidential`; the logging domain tags request-payload columns `confidential`. The role `logs_reader` is allowed `confidential` in the logging domain, so last quarter 2,200 HR salary columns were queryable by `logs_reader`. Domains add about 15 new tag names a week, and the security team must answer 'which columns are salary-class across all domains?' for a regulator. The platform team wants domains to keep classifying their own data. Which change should it make first?
MCQ-37An EU payments company keeps its primary Postgres in eu-central-1. A nightly job copies the users table to a global analytics warehouse in us-east-1, projecting user_id, email_prefix (the first 6 hex characters of SHA-256 of the lowercased email), device_id and country. The table holds 4.2 million EU users. Legal asks whether the nightly copy is a cross-border transfer of personal data. Which conclusion is correct?
MCQ-64A diversity report is rebuilt from one HR extract each month: Table A is headcount by department, Table B is headcount by department and gender, and any count below 5 prints as '<5' in both. This month Engineering is the largest department at 123 people, and its Table B row reads Men 122, Women '<5'. Department rosters are published on the company intranet. The privacy office accepts the '<5' rule but says the report still identifies a person in Engineering. Which change to next month's run removes that disclosure?
MCQ-65A retail platform stores EU customer emails in an eu-west-1 Postgres primary. A nightly job replicates them to a US Snowflake warehouse for the US ops team. The warehouse uses a KMS key in us-east-1, and the US ops role has kms:Decrypt on that key. A test run as that role decrypts 3.6 million EU email addresses from the US replica. The transfer impact assessment says 'encrypted replica, no access.' The DPO asks what to change before the quarterly audit. Which action best addresses the residency exposure?
MCQ-77A SaaS company erases a customer's rows from its production Postgres cluster 7 days after a GDPR erasure request. Nightly full backups go to object storage with 35-day retention. A separate nightly logical dump feeds the analytics warehouse and is kept 90 days. On day 40 an engineer restores the day-0 dump, the last one taken before the erasure, into a sandbox the analytics team can query, to reproduce a billing bug. Which statement about the erasure is correct?
MCQ-92A nightly reverse ETL sync pushes the `high_value_eu` segment: 1.9M rows: from the warehouse to a new ad platform. The connector's payload maps `email` and `lifetime_spend`. The platform's matching API accepts only a SHA-256 hash of the email, and last night's run sent raw `email` and matched 0 of the 1.9M users. The platform has no signed DPA and no entry in the RoPA. What should the team change before the next run?
MCQ-79A platform loader is the only writer allowed into certified.orders: it extracts staging rows whose updated_at is past a high-water mark, merges them on order_id, replaces customer_email with a SHA-256 hash, and emits lineage. Its Tuesday run committed 620,000 masked rows at 23:04 and left the watermark at Tuesday 23:00. That night a domain team, under deadline, ran a Spark job that appended Parquet straight into the certified table's S3 prefix using a cluster role that still held the pre-loader bucket write grant; a midpoint task failed, the job retried, and it committed the same 620,000 orders again, unmasked. Wednesday's audit: the table holds 1,860,000 rows, 1,240,000 customer_email values are live addresses, every order_id appears three times, and the two newest commits are the Spark job's. Which move leaves certified.orders matching the source and stops the next producer from writing around the loader?
MCQ-21A Delta Lake 3.x table `customers` on S3 holds one row per customer, with `delta.logRetentionDuration` at its 30-day default. A GDPR erasure request arrives for customer_id 4471. The team runs `DELETE FROM customers WHERE customer_id = 4471`, then `VACUUM customers RETAIN 168 HOURS`, the default, and reports the erasure complete. Two days later an auditor runs `SELECT * FROM customers VERSION AS OF 5`, a version committed three days before the DELETE, and gets the row for 4471 back. The erasure SLA is 30 days. What is the correct diagnosis and fix?
MCQ-38An `orders` table holds 4.2 billion rows, partitioned by day. A nightly job drops partitions older than 90 days: about 11 million rows a night. Litigation now covers 2,400 customer ids: roughly 180,000 rows, 40,000 of them already past 90 days, spread across 300 of the last 400 daily partitions. The team must keep those rows, keep deleting everything else on the 90-day clock, and be able to show auditors that no hold outlives its case. Which policy does that?
MCQ-20customer_dim holds one current row per customer and customer_dim_history holds type-2 rows (11 for customer 8842); a nightly job rebuilds 3 downstream marts from both, and snapshot 2209 is the last snapshot holding the email. A previous erasure for another customer passed the AS OF check, the snapshot was gone, yet a scan of the data directory still returned the email, and a sandbox restored from those files put the customer back into all 3 marts. Which procedure for customer 8842 satisfies both checks: AS OF 2209 fails with snapshot expired, and a directory scan returns no email?
MCQ-40A nightly reverse ETL job syncs warehouse segment members to a CRM. The warehouse table has customer_sk (surrogate) and email; the CRM contact has external_id (unique) and email. A crosswalk maps warehouse customer_sk to crm_contact_id for 11,400 of the 12,000 members in tonight's segment; the other 600 are new signups with no CRM contact yet. Last night the job wrote customer_sk into CRM external_id. CRM now has 12,000 new contacts, and the 11,400 existing contacts were not updated. Which change should the team make before tonight's run?
MCQ-33An orders pipeline in BigQuery (GoogleSQL) exposes a JSON column `payload`. The certified revenue metric is `COALESCE(SUM(SAFE_CAST(JSON_VALUE(payload, '$.amount') AS NUMERIC)), 0)`. A producer change moves the amount to `$.price.amount`. Six days later, 9 of 11 revenue dashboards still read `$.amount` and report $0, while the other 2 read `$.price.amount` and report the correct revenue. Backfill is possible, but the team wants the next producer path change to fail the load rather than silently null a certified column. Which change should they make?
MCQ-50A retailer computes on-hand inventory by summing transaction-grain movement facts (one row per receipt or issue, keyed (event_time, warehouse, sku)) from each SKU's first receipt. A shipment of 1,200 units out of W-12 posted 2024-03-14 22:00 was dropped from the feed. The balance read 1,200 units high from that day on, and ops oversold 1,200 units over the next 6 days; a physical count found the gap 11 months later. The WMS exposes a complete balance export nightly at 23:00. Which change catches the dropped movement on the day it should have posted?
MCQ-51An SCD2 customer dimension is fed by a nightly CDC job. The job reads yesterday's snapshot (2,000,000 keys) and today's full extract, and emits a tombstone for every key present yesterday but absent today. The downstream merge closes the current version and inserts a new one whenever a tombstoned key reappears. Last night the extract wrote 800,000 rows and exited non-zero; the job ignored the status and ran the diff anyway. Which account of the run and the change that prevents it is correct?