Skip to content
Tech Interview Prep
Technical interview guide

Data Warehousing & Modeling

Star and snowflake schemas, and the fact/dimension split that makes analytical queries fast.

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

Scope: BigQuery, Amazon Redshift, Snowflake, Apache Iceberg 1.11, PostgreSQL 18, dbt MetricFlow, and OpenLineage documentation current 2026-08-31.

Curated: · Written: · Reviewed:

25 questions

MCQ-12A retail chain reports month-end inventory balances per product per warehouse and compares stock levels across months. Which grain should the fact table use?
MCQ-4A team's 3NF warehouse holds fact_orders plus nine normalized dimension tables: product, category, subcategory, brand, store, region, customer, segment, and date. Their most-run recurring report, revenue by brand by region by month, joins fact_orders to product, category, subcategory, brand, store, region, and date, seven joins in all, and runs at p95 19 s. They rebuild the model as a star with four dimensions: dim_product (product, category, subcategory, brand), dim_store (store, region), dim_customer (customer, segment), and dim_date. After the rebuild, how many tables does the same report's query join?
MCQ-9A quarterly report rolls up month-end balances by summing them. For account A the month-end balances are Jan 31 = 1,200, Feb 28 = 1,410, Mar 31 = 1,350, and the report shows 3,960 as account A's Q1 closing balance. What is the correct Q1 closing balance for account A?
MCQ-10A campaign fact table stores one row per campaign per day with columns impressions, clicks and ctr (clicks / impressions). For campaign C the last two days are: day 1. 100 impressions, 10 clicks, ctr 0.10; day 2. 1000 impressions, 20 clicks, ctr 0.02. The weekly dashboard sums the ctr column and reports 12% for campaign C. What is the correct weekly CTR?
MCQ-1A nightly job loads `fct_orders` with one row per order and copies `customer_lifetime_value` from the customer master onto every order row. The table holds 5,000 orders for 1,000 customers, and orders per customer range from 1 to 900. A report that sums `customer_lifetime_value` over `fct_orders` returns $4.2M; summing the same column in `dim_customer` returns $840K. What is the correct fix?
MCQ-2A warehouse's fact_sales table is declared at the grain of one row per order line. It carries order_id, line_no, qty, line_amount, and order_total, where order_total is the whole order's value copied onto every line. An analyst runs SUM(order_total) grouped by order date and gets $1,000 for a day whose four orders were each worth $100, with 1, 2, 3, and 4 lines respectively. Finance reports $400 for the same day. What explains the gap?
MCQ-11A warehouse has `fct_order_lines` (one row per order line) and `fct_shipments` (one row per shipment), both carrying `order_id`. Order 1001 has three lines of $10 each and two shipments of $15 each. An analyst runs `SELECT SUM(line_amount), SUM(shipped_amount) FROM fct_order_lines JOIN fct_shipments USING (order_id) WHERE order_id = 1001`. What does the query return?
MCQ-23A claims fact table holds one row per claim with claim_amount, and each claim carries up to twelve diagnosis codes. Finance reports claim_amount by diagnosis, with the primary diagnosis credited a larger share of the claim than each secondary code, and each claim's amount counted exactly once across the report. Which structure supports that?
MCQ-7fct_orders holds 4,200,000 rows. dim_customer is SCD Type 2: it carries a surrogate key customer_sk and a business key customer_id, and 12,000 customers have two rows (one current, one expired) while every other customer has exactly one. 300,000 of the fact rows belong to those 12,000 customers. A query joins fct_orders to dim_customer on customer_id alone, with no other predicate. How many rows does the join return?
MCQ-3A retailer's star schema has `fact_sales` (1.2B rows; columns `sale_id`, `product_id`, `product_sk`, `sale_date_sk`, `amount`) and `dim_product`, an SCD Type 2 dimension keyed by surrogate `product_sk` with natural key `product_id` and an `is_current` flag. Of the 40,000 `product_id` values, 6,200 have two rows, a historical and a current version, and the rest have one. Each of those 6,200 products accounts for exactly 1,000 rows in `fact_sales`. A FY2024 revenue-by-category report joins `fact_sales` to `dim_product` on `product_id`; the join produces 1,206,200,000 rows, 6.2M more than the fact table. What accounts for the extra rows, and what is the correct fix?
MCQ-5`dim_customer` is a Type 2 dimension: `customer_key` (surrogate), `customer_id` (business key), `plan_tier`, `effective_from`, `effective_to` (exclusive), `is_current`. `fct_orders` stores the business key rather than the surrogate: `order_id`, `customer_id`, `order_date`, `amount`. Customer 4471 has two dimension rows: | customer_key | customer_id | plan_tier | effective_from | effective_to | is_current | | --- | --- | --- | --- | --- | --- | | 91 | 4471 | basic | 2024-01-01 | 2024-04-02 | false | | 402 | 4471 | pro | 2024-04-02 | 9999-12-31 | true | Three orders exist for that customer: 2024-01-15 for $200, 2024-02-20 for $300, 2024-04-10 for $500. An analyst runs: ```sql SELECT d.plan_tier, SUM(f.amount) AS revenue FROM fct_orders f JOIN dim_customer d ON d.customer_id = f.customer_id GROUP BY d.plan_tier; ``` What does this query return?
MCQ-25A retail warehouse loads order facts that carry only the product's natural business key. Price changes take effect at midnight and orders are timestamped at midnight too, so a fact's event timestamp can equal a version's start exactly. Every fact, current or historical, must land in exactly one version, never zero and never two, using a single range predicate on the dimension. Which attribute combination on the SCD Type 2 record achieves that?
MCQ-13`fact_order_fulfillment` holds one row per order, keyed by order_id, with milestone date columns order_placed_date, packed_date, shipped_date and delivered_date. The loader fills each date as the milestone occurs, updates the row in place, and never clears a date it has written. On 3 March, `SELECT COUNT(*) FROM fact_order_fulfillment WHERE packed_date IS NOT NULL AND shipped_date IS NULL` returns 1,200. On 10 March the same query returns 900, and the table still holds 480,000 rows. What explains the 300-order drop?
MCQ-6A source-system bug set region = 'EMEA' for 12,000 customers in a 400-million-row dimension who were always in 'APAC'. The value was never correct, re-runs of prior-period reports must show the corrected region, and re-pointing the 2-billion-row fact table to new dimension rows is not an option this quarter. Which change should the team make?
MCQ-8A retailer's `fct_orders` and `fct_returns` each join to their own `dim_date`. Both dimensions cover 2020–2024 and expose `date_key`, `calendar_date`, `fiscal_week` and `fiscal_year`, but they were built by different teams: the orders dimension starts fiscal weeks on Sunday, the returns dimension starts them on Monday, and the two assign different `date_key` values to the same calendar date. A report joins both facts to the orders `dim_date` on `date_key` to compare weekly orders against weekly returns. What does the report show?
MCQ-14Each night you load 480,000 staged order lines into fct_order_line, joining to dim_customer on customer_id to pick up customer_sk. dim_customer is a Type 2 SCD with a unique index on (customer_id, effective_date) and already carries one unknown-member row, customer_sk = -1. fct_order_line.customer_sk is NOT NULL, and the table also carries customer_id as a degenerate dimension. Last night 12,400 lines referenced customer_ids that had not yet arrived from the CRM feed; the inner join dropped them and the daily revenue report came in $310,000 short. The CRM rows are expected within a week, the report must include those lines tonight, and finance wants the $310,000 attributed to the right customers once the CRM rows land. What should the load do with the 12,400 lines?
MCQ-22A nightly job reads one day of events from S3 and writes them into fact_events, stamping each row with the run's start time. It crashes after committing 600 of the day's 1,000 rows. The scheduler retries the job, which re-reads the whole day and stamps the rows with a new start time; the next morning an analyst re-runs the same day as a backfill. The table must end with exactly 1,000 rows for that date, one per event. Which property of the load makes that hold?
MCQ-24A nightly pipeline rebuilds three related tables with three independent jobs. Each job truncates its table and then inserts the new rows in batches, committing as it goes, so readers see the table change while the job runs. Job windows: `orders` 02:00–02:40, `order_items` 02:05–02:50, `payments` 02:10–03:00. No job failed. A finance dashboard that joins all three tables reported 412,000 orders with no matching payment row when it ran at 02:55, and 0 when it ran at 04:00. What produced the 412,000?
MCQ-21A team's 400M-row events table makes a dashboard aggregation take 6 s at p95, and the dashboard runs that query 300 times an hour. They create a materialized view refreshed every 15 minutes. Which consequence should they expect?
MCQ-15A Hive table `events` is partitioned by `dt`, a string column holding 'YYYY-MM-DD' values, and has 1,095 daily partitions. A report runs `SELECT count(*) FROM events WHERE year(dt) = 2024 AND month(dt) = 3`. It reads all 1,095 partitions even though only the 31 partitions for March 2024 can match. Which change makes the query read only those 31 partitions?
MCQ-20A team migrates a 400 GB events table from Hive-style partitioning on a `dt` string column to an Iceberg table created with `PARTITIONED BY (days(event_ts))`. The table holds 90 day partitions, all written under the new spec. Analysts keep running `SELECT count(*) FROM events WHERE event_ts >= '2024-03-01 00:00:00' AND event_ts < '2024-03-02 00:00:00'`, which names no partition column. What does the query plan scan?
MCQ-16A 12 TB events table is partitioned by `event_date` (1,095 days) and `customer_id` (~40,000 active customers), with about 250 rows in each (day, customer) partition. A dashboard query that filters on `event_date = '2024-03-01'` and aggregates across all customers now takes 95 s, of which ~80 s is spent planning before any data is read. The dashboard must keep aggregating across every customer for a given day. Which change is most likely to bring the query back under 10 s?
MCQ-17A 4 TB Snowflake table is loaded in ingestion order, so one tenant's rows are scattered across the whole table. Analysts filter on `tenant_id` plus a one-day range, and each query reads roughly 90% of the table. The team adds a clustering key on `(tenant_id, event_date)`. What does that change?
MCQ-18A 2 TB `fact_orders` table is hash-distributed on `order_id` across 20 nodes. The `dim_customer` table (150 GB) is joined on `customer_id` in 80% of queries. `customer_id` is skewed: a handful of customers account for 45% of all orders. The team plans to redistribute `fact_orders` on `customer_id` to colocate with `dim_customer`. What is the main risk?
MCQ-19A 16-node cluster stores a 40 TB fact table partitioned by `sale_date`. The `customers` dimension is 2 GB, 12 million rows, and receives 8,000 updates per day. Queries join the fact table to `customers` and filter on `customers.region`. The current plan shuffles the 40 TB fact side to perform the join. The team considers replicating `customers` to every node. What is the most likely effect?