Skip to content
Tech Interview Prep
Technical interview guide

Semantic Data Modeling for BI

The metrics layer that defines business terms consistently, so 'revenue' means the same thing in every report.

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

Scope: Current dbt MetricFlow, LookML, Power BI, Tableau, Cube, OpenMetadata, BigQuery, and Snowflake guidance reviewed 2026-09-04.

Curated: · Written: · Reviewed:

25 questions

MCQ-4A checkout flow reports conversion by device class for one week: mobile 10 conversions from 100 eligible sessions, desktop 60 from 200, tablet 35 from 700. The weekly review needs a single conversion rate for the whole flow, and the three classes carry very different traffic volumes. Which figure belongs on the summary tile?
MCQ-3A fact table holds one row per product per day with columns units_sold, invoice_line_revenue, inventory_on_hand, discount_pct, and distinct_customers. A monthly report groups by product and region and sums every column over the 30 days in the period. For which column is that 30-day SUM wrong, even though the same SUM is correct when the report groups by product and region alone?
MCQ-2An orders table has rows (order_id, order_total): (1, 100), (2, 200), (3, 50). An order_items table has one row per item: order 1 has three items, order 2 has one item, order 3 has two items. A report runs: SELECT SUM(o.order_total) FROM orders o JOIN order_items i ON i.order_id = o.order_id; What total does this query return?
MCQ-1A `fct_order_lines` table holds one row per order line: 1,000 rows whose `revenue` sums to $250,000. A `fct_shipments` table holds one row per shipment: 1,400 rows, and a single order can ship in several shipments. An analyst runs `SELECT SUM(o.revenue) FROM fct_order_lines o JOIN fct_shipments s ON o.order_id = s.order_id` and gets $350,000. Which statement explains that result and gives the correct fix?
MCQ-17A metric `completed_revenue` is defined as `sum(order_amount)` filtered to `status = 'completed'` and `is_refunded = false`. Its unit test fixture supplies the rows below and asserts the metric's total. | order_id | order_amount | status | is_refunded | | --- | --- | --- | --- | | 101 | 120.00 | completed | false | | 102 | 80.00 | completed | true | | 103 | 200.00 | pending | false | | 104 | 45.50 | completed | false | | 105 | 30.00 | completed | false | | 106 | 60.00 | completed | false | | 107 | 90.00 | completed | NULL | What value should the assertion expect?
MCQ-5A semantic model declares orders → customers as many-to-one on customer_id. The customers table holds 1,000,000 rows but only 940,000 distinct customer_id values: 60,000 customer_ids appear twice, once for a superseded address and once for the current one. The orders table has 2,000,000 rows totaling $3,100,000; 400,000 of those rows, totaling $620,000, belong to the 60,000 duplicated customer_ids. A dashboard built on this model reports $3,720,000 while the finance ledger reports $3,100,000. Which change makes the dashboard report $3,100,000?
MCQ-8A warehouse keeps dim_customer as a Type 2 SCD with surrogate key customer_sk and natural key customer_id. Customer 42 has two rows: customer_sk=1001, region='EMEA', valid_from 2023-01-01, valid_to 2024-03-31; and customer_sk=1002, region='APAC', valid_from 2024-04-01, valid_to 9999-12-31. fct_orders holds one row: customer_id=42, order_date 2024-05-10, amount 500. An analyst runs: ```sql SELECT d.region, SUM(f.amount) AS revenue FROM fct_orders f JOIN dim_customer d ON f.customer_id = d.customer_id GROUP BY d.region; ``` What does the query return?
MCQ-18A warehouse's `fact_orders` holds 2.4M order lines and $18.6M of revenue, and carries both `customer_key` (surrogate) and `customer_id` (natural). `dim_customer` is a Type 2 SCD: 1.4M rows for 1.1M customers, `customer_key` unique, `customer_id` not. 300,000 customers have two rows after an address change, and those customers account for 900,000 order lines and $5.1M of revenue. An analyst runs `SELECT c.region, SUM(o.revenue) FROM fact_orders o JOIN dim_customer c ON o.customer_id = c.customer_id GROUP BY c.region;` and gets $23.7M against a ledger total of $18.6M. What produces the $23.7M?
MCQ-6A team is modeling a many-to-many between order lines and sales reps. The fact table has three lines: L1 $1,000 (rep A 70%, rep B 30%), L2 $2,000 (rep A 50%, rep B 50%), L3 $3,000 (rep A 100%). They need a revenue-by-rep report that totals $6,000 without double counting. If they model this correctly, what revenue should the report show for rep A?
MCQ-19A semantic model defines orders (1,000 rows, one per order, SUM(amount) = $500,000), order_items (exactly 3 rows per order), and refunds (200 rows, one per order). It declares both orders→refunds and orders→order_items→refunds. A report that groups SUM(orders.amount) by refunds.reason returns $1,500,000. What is happening, and what should be done?
MCQ-25An orders fact table has three date foreign keys (order_date, ship_date and delivery_date) all pointing at one 30-year date dimension of about 11,000 rows that carries the company's fiscal calendar. Finance maintains that calendar in a single place and it must not be duplicated. Analysts need order month and ship month side by side in one pivot to measure backlog, and must also slice revenue by delivery month. Which modeling approach meets this?
MCQ-12A warehouse converts order amounts to USD by joining each order to `fx_rates` on `rate_date = order_date` and `rate_type = 'spot'`. Finance's policy is that monthly revenue is reported at the month-end closing rate. March's EUR orders are €10,000 on 2024-03-15 and €6,000 on 2024-03-29. The EUR rows in `fx_rates` are spot 1.07 on 03-01, spot 1.08 on 03-15, spot 1.14 on 03-29, closing 1.09 on 03-31, and spot 1.05 on 04-02. What does the pipeline report as March EUR revenue in USD?
MCQ-7fct_bookings has booking_id, booked_at (UTC, set by the booking service when the customer confirmed), loaded_at (UTC, set by the loader on every write), and amount_usd. The daily revenue dashboard is sum(amount_usd) grouped by date_trunc('day', loaded_at), and finance closes its books on UTC calendar days. Sunday 2024-03-03 produced 9,000 bookings worth 540,000 USD, all with booked_at on that date, but a broker outage split the load: 6,000 rows (360,000 USD) landed with loaded_at on 03-03, and 3,000 rows (180,000 USD) landed on 2024-03-04 between 01:00 and 03:00 UTC. 2,100 of the 9,000 bookings were confirmed between 00:00 and 05:00 UTC. On 2024-03-10 at 02:00 UTC the full-reload job upserted all 50,000 rows in the table, and its upsert sets loaded_at to the write time. The dashboard now reads 0 USD for 2024-03-03 and one large bar on 03-10. Which change to the day definition keeps 2024-03-03 at finance's 540,000 USD, now and through future reloads?
MCQ-23A payments ledger is append-only. Each row carries `event_time` (when the payment occurred) and `ingest_time` (when the row was written); a correction is a new row with the same `event_time` and a later `ingest_time`. The March close ran at 2025-04-02T00:00Z and published March net revenue of $1,240,000, computed as the sum of `amount` for rows with `event_time` in March and `ingest_time <= 2025-04-02T00:00Z`. On 2025-04-11 a refund row arrives: `event_time` 2025-03-22, `ingest_time` 2025-04-11, `amount` -$18,000. The team re-runs the close query with the original as-of timestamp and also runs the current view (no `ingest_time` filter). What do the two March totals show?
MCQ-11A nightly job loads about 1.2M rows into fact_sales. Roughly 0.4% of them (about 4,800 rows, $310k of the night's $78M) carry a customer_id whose dim_customer row has not been created in the source yet; it usually appears within 48 hours. The load drops those rows today, so the nightly finance reconciliation against the source ledger comes up about $310k short every night. fact_sales.customer_key is NOT NULL with a foreign key to dim_customer, the BI layer inner-joins facts to dim_customer, and dim_customer already carries a pre-built unknown-member row at customer_key = -1. The reconciliation has to tie out the same night, and each of those facts must end up attributed to its real customer once that customer's dimension row arrives. Which change should the team make?
MCQ-15Semantic model `sales` has fact `order_items` at one row per order line, dimensions `order_date`, `country`, `channel`, `customer_tier`, and metric `revenue = SUM(line_amount)`. Materialized pre-aggregation `agg_sales_daily` is grouped by `order_date`, `country`, `channel` and stores `revenue`. A dashboard query asks for `revenue` for 2024-03-04 through 2024-03-10, `country = 'DE'`, `channel = 'web'`, `customer_tier = 'gold'`. The engine incorrectly routes it to `agg_sales_daily`. In `agg_sales_daily`, DE/web revenue for that week is 4,800 across all customer tiers; in `order_items`, DE/web/gold revenue for that week is 1,150. What does the incorrectly routed query return, and which mismatch caused it?
MCQ-16A semantic layer caches finished metric results under `sha256(metric_id, filters_json, grain)`, where `filters_json` is the raw filter map as received. The cache is large enough that nothing is evicted in this window. `v4` redefines revenue to exclude refunds, and a cached entry returns the currency it was computed in. The layer serves these eight requests in order: | # | tenant | metric | version | filters | grain | currency | |---|---|---|---|---|---|---| | 1 | acme | revenue | v3 | {region: EMEA} | day | USD | | 2 | acme | revenue | v3 | {region: EMEA} | day | USD | | 3 | globex | revenue | v3 | {region: EMEA} | day | USD | | 4 | acme | revenue | v4 | {region: EMEA} | day | USD | | 5 | acme | revenue | v3 | {region: EMEA} | day | EUR | | 6 | acme | revenue | v3 | {region: EMEA} | week | USD | | 7 | acme | revenue | v3 | {region: EMEA} | week | USD | | 8 | globex | revenue | v3 | {region: EMEA} | week | USD | Which requests return a cached result that was computed for a different tenant, metric version, or currency?
MCQ-13A semantic layer compiles each metric request to SQL and caches the result under a key of `(metric, dimensions, filters, time grain)`. Row-level security is enforced by appending `tenant_id = :tenant` to the compiled SQL, using the caller's tenant. Six analysts (one per tenant, tenants 1 through 6) issue the identical request `revenue by week` for the same 90-day range inside the same 5-minute cache window. The first analyst (tenant 1) gets the right number; the other five get tenant 1's number. The team wants to keep the cache and keep analysts in the same tenant sharing entries. Which change does that?
MCQ-14An HR semantic layer publishes headcount and avg_comp for each group and suppresses any group with fewer than 5 employees. Finance has 6 employees. Querying team = Finance returns headcount 6, avg_comp 100,000. Querying team = Finance and role = analyst returns headcount 5, avg_comp 80,000. Both rows are returned as designed. What do these two results establish?
MCQ-20A metrics service keeps one HyperLogLog sketch per country per day and rolls them up weekly by merging the seven daily sketches. It must cover 200 countries over 90 days, hold the relative standard error at or below 1%, and keep total sketch storage under 250 MB. HyperLogLog uses 6 bits per register and has relative standard error ≈ 1.04/√m for m registers. Which configuration meets all three constraints?
MCQ-21`fct_orders` is built from `stg_orders` and `stg_payments`. Two models read it: `mart_revenue` selects `fct_orders.payment_method`, and `ml_churn_features` selects only `customer_id` and `amount`. `mart_revenue` feeds the Revenue Ops dashboard; `ml_churn_features` feeds the Churn Watch dashboard. An engineer renames `payment_method` to `payment_type` in `stg_payments`, and the team's lineage is column-level. Which assets does the impact analysis flag for re-validation?
MCQ-24A semantic layer serves 240 certified metrics to 18 dashboards. After a model change, `net_revenue` on the finance dashboard reads $4.2M while the same metric on the exec dashboard still reads $4.8M. The observability stack records only the count of YAML metric files and dashboard page views, so nobody noticed for nine days. Which added signal would have caught the divergence on the first day?
MCQ-22A semantic layer has two certified metrics both named revenue. Finance's definition is recognized bookings net of refunds; product's is gross transaction value including refunds. For March they return $4.2M and $4.6M, and the $400K gap is refunds. Forty-seven dashboards and twelve alerts reference revenue, and the board deck used the product number in the finance section. What is the safest next step?
MCQ-10A B2B SaaS company certifies 'Active Subscribers' in its semantic layer; the definition counts paid subscriptions only. The metric feeds the board's ARR bridge and a credit agreement that defines ARR as paid subscriptions. Paid subscribers are 48,200 this quarter, up from 46,346 last quarter. Product asks to include the 6,400 trial accounts so the board deck shows 17.8% growth instead of 4.0%. The metric has a named business owner (VP Finance) and a technical steward (analytics engineer). Which action is correct?
MCQ-9`checkout_conversion` is defined as `orders / sessions`, computed hourly into `analytics.conv(hour, orders, sessions)`. It feeds 40 dashboards that query the table directly, a fraud model that retrains weekly on the trailing 12 months of hourly rows, and an alert that fires when the hourly value drops below 2.0%. The team wants to exclude bot sessions from the denominator. Bots are 14% of session rows and 0.3% of orders, and the `is_bot` flag exists in the events table for the last 14 months only. Which shipping plan is correct?