Top 100 Business Intelligence Developer 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.
MCQ-5`fct_orders` holds one row per order with `shipping_cost`; `fct_order_lines` holds one row per line with `line_amount`. A report joins the two facts on `order_id` and returns `SUM(shipping_cost)` and `SUM(line_amount)` grouped by `order_date`. `SUM(shipping_cost)` comes out inflated in proportion to each order's line count, while `SUM(line_amount)` matches finance. Which change should the review require?⌄
MCQ-32A retail star schema has `fct_order_line` at one row per order line: 1.2M rows across 300k orders, with `line_amount` summing to $3.65M. `order_number` was given its own table, `dim_order`, holding only `order_number`; it was loaded from the line-level source without deduplication, so it has 1.2M rows with each order number repeated once per line. A revenue report joins the fact to `dim_order` on `order_number` and returns $14.6M. Which change addresses the cause of the inflated total?⌄
MCQ-99Your weekly orders metric is SELECT COUNT(*) FROM orders o JOIN order_items i ON i.order_id = o.order_id, filtered to the week. For 4-10 May it returned 199,000 rows and for 11-17 May, 360,000. Before 6 May every order carried exactly one item row, so those row counts were order counts; the marketplace integration that went live on 6 May writes exactly three item rows per order, and the other 120,000 orders in the 11-17 May week still carry one each. Settled payments, one row per order and taken straight from the processor, moved from 196,000 to 198,000 across the same two weeks. What should you conclude, and what should you do?⌄
MCQ-100A reporting layer's certified 'Monthly Revenue' measure joins orders(order_id, order_total) to shipments(shipment_id, order_id) on order_id and sums orders.order_total. For March it reports $4.21M. The finance ledger (the audited source of record, closed and signed off for March) says $3.90M. March had 50,000 orders and 54,000 shipment rows. No other transformation touches the amount. What explains the $0.31M gap?⌄
MCQ-8An analyst's 'revenue by region' report reads the normalised source directly: it joins orders (1,000 rows, $1.35M total revenue) to shipments (3,000 rows, three per order) and to carriers (a lookup, one row per carrier). Grouped by region, the report totals $4.05M. Which statement explains why the report totals $4.05M?⌄
MCQ-58A bookings model change adds a parcels join so freight can sit beside bookings. It passed a 40-order validation sample in which every order had exactly one parcel, and shipped. The March production run contains three orders, and between them they carry every multi-parcel case: order 1 books 100 and ships 2 parcels, order 2 books 200 and ships 1 parcel, order 3 books 300 and ships 3 parcels. The model is select sum(o.bookings) as gross_bookings from orders o join parcels p on p.order_id = o.order_id where o.month = '2025-03'; parcels holds one row per parcel. What does the model report for March gross bookings?⌄
MCQ-1A nightly load into `fct_order_lines` is declared as one row per order line, keyed on the surrogate `order_line_id`. After a source migration the extract regenerates `order_line_id` on every run, so each order line now lands twice with two different ids. Row count went from 1.2M to 2.4M and the revenue dashboard's `SUM(line_amount)` reads $4.8M against finance's $2.4M. The existing uniqueness test on `order_line_id` passes. What should the team do?⌄
MCQ-39A revenue model's fct_sales table is declared at the grain of one order line, about 4.0M rows a day, and its only tests are freshness (newest loaded_at within 2 hours) and a row count that fails only when the table is empty. A developer changes the join to dim_promotion from promotion_key to campaign_id; dim_promotion holds several promotions per campaign, so promoted order lines fan out. The next day fct_sales holds 5.6M rows and total_sales reports $1.9M against $1.34M in the source of record. Both tests pass and the error runs for six days. Which test, added to the suite, would have failed on the day of the change?⌄
MCQ-10A dashboard tile reports 1.24M monthly active users (MAU). The certified MAU asset reports 890K for the same month. The tile is an uncertified personal asset; the certified asset has a named owner and tests. The tile runs:
```sql
SELECT COUNT(*)
FROM monthly_activity m
JOIN account_plans p ON p.account_id = m.account_id
WHERE m.month = '2024-06';
```
`monthly_activity` holds one row per active account for the month. `account_plans` holds one row per account per plan version; 350K accounts moved to a new plan in June, giving each a second row. The certified asset runs `SELECT COUNT(DISTINCT account_id) FROM monthly_activity WHERE month = '2024-06'`. Which figure is the correct MAU, and why?⌄
MCQ-91A monthly active accounts dashboard reports 1,240,000 accounts for March. The certified finance measure for the same month is 1,180,000. The dashboard query left joins accounts to subscriptions and filters out rows where subscription_status = 'cancelled'. It does not deduplicate account_id. The analyst says the 60,000 gap is because finance excludes accounts that have not logged in. What should the reviewer do first?⌄
MCQ-81A warehouse models its org hierarchy with a closure table, org_closure(ancestor_key, descendant_key, depth), which stores a self-row at depth 0 for every node. The hierarchy is:
| org_key | name | parent_key |
| --- | --- | --- |
| 1 | Company | NULL |
| 2 | North | 1 |
| 3 | Sales | 2 |
| 4 | Support | 2 |
| 5 | West | 1 |
| 6 | Sales | 5 |
| 7 | Enterprise | 6 |
fact_revenue has one row per org that booked revenue this month: org 3 = $47K, org 4 = $23K, org 7 = $39K. A report joins fact_revenue to org_closure on fact_revenue.org_key = org_closure.descendant_key and groups by org_closure.ancestor_key. Finance exports the report and sums the revenue column to reconcile against the $109K ledger total. What does that sum come to?⌄
MCQ-47A BI developer is asked by the sales VP for "average revenue per customer" for Friday's board deck. The orders table holds 1.8M rows; a retry job on 2024-03-11 inserted 214k duplicate rows, and 31% of orders are guest checkouts with a NULL customer_id. The VP wants one number. What should the developer deliver?⌄
MCQ-31A sales fact table has one row per order line, with OrderDateKey and ShipDateKey both foreign keys to a single Date dimension. Two reports group revenue by month: one joins Date on OrderDateKey, the other on ShipDateKey. For March, the order-date report shows $4.10M and the ship-date report shows $3.73M; for April, $3.90M and $4.21M. A stakeholder asks why the totals differ and which report is wrong. What is the correct response?⌄
MCQ-34After an acquisition, a warehouse loads a customer dimension from two source systems. Both number customers from 1, so 4,100 customer_id values exist in both. The dimension is Type 2, a region change inserts a new row, and each fact must join to the row current at its event date. The fact load joins on customer_id alone, and 61,000 facts land on the wrong company. Which change fixes the load?⌄
MCQ-52The product dimension is loaded by a nightly job that runs after the fact load, so each morning a few thousand fact rows have no matching product row yet. The revenue report must go out on schedule, its total must match the source fact table, and the unmatched facts must be visible as unmatched rather than folded into a real product. Which approach is defensible?⌄
MCQ-53A nightly load adds a new source whose region mapping is not yet agreed, and 4% of its fact rows arrive with a NULL region key. The finance total includes those rows; the grouped revenue report excludes them; the two have differed by exactly that amount for three months. The model must serve the grouped report and drill-down from a region to its dimension row, consumers also query the fact table directly, and the unmapped share has to stay visible so the source mapping can be chased. Which change should the review accept?⌄
MCQ-6A retention report joins the sales fact table to the support team's customer dimension on customer_id and reports 76%. The sales team's report, built on the sales dimension, reports 62%. Sales marks a customer active from their first order (96,000 customers); support marks a customer active from their first ticket (74,000 customers). Both dimensions are complete for their own definition, customer_id is the primary key of both, both are rebuilt from the same nightly batch, and the sales fact has one row per customer. What explains the 14-point gap?⌄
MCQ-7A retail BI team's customer dimension carries region, credit tier, and account manager. Region changes when a customer relocates, account manager changes on reassignment, and credit tier is corrected in place when the source system fixes a data-entry error. Finance re-runs last year's regional revenue report each quarter and expects the figures not to move, and marketing publishes a month-end count of active customers by region that includes customers with no orders that month. Which handling should the developer justify?⌄
MCQ-98A reporting mart is being designed from four source entities: orders, customers, products and a calendar. Reviewers must settle which layout is a star schema and why that shape suits analytical queries. Which layout is the star schema and why?⌄
MCQ-61A semantic layer serves a quarterly revenue-by-team report. The fact table stores the team's surrogate key as of the transaction date. On 1 July a reorganisation merged Team B into Team A: the dimension was versioned, closing the Team A and Team B rows at 2024-06-30 and opening a new Team A row from 2024-07-01. The report joins `fact.team_key = dim.team_key AND dim.is_current = true`. Q2 (Apr–Jun) revenue was 600 under Team A and 900 under Team B. What does the report return for Q2, and what is the correct fix?⌄
MCQ-80A retail warehouse has a customer_dim Type 2 dimension: 900,000 customers, 60 attributes, 41 million rows (about 45 versions per customer). Two attributes change often, credit_score_band monthly and loyalty_tier weekly, while the other 58 (name, signup date, address) change rarely. A 12-month compliance report joins the fact table to this dimension and must show the credit_score_band that was in effect on each transaction's date; the dimension join is the slowest part of the query at 9 s. Which change addresses the cause of the row growth while keeping that report correct?⌄
MCQ-2A semantic model exposes an account balance measure that forty reports and ad-hoc analyst extracts read. Summing it across accounts is correct; a board pack that summed it across the twelve months of the year showed 14.2m against a 1.2m year-end balance. What is the correct remediation?⌄
MCQ-3A conversion dashboard stores a `conversion_rate` column on each channel row and computes its Total row by averaging that column. Last month Paid Search ran 38,000 sessions and 1,900 conversions; Email ran 2,000 sessions and 800 conversions. The Total row currently reads 22.5%, and the growth team is about to set next quarter's budget from it. What should the Total row show?⌄
MCQ-26A Power BI model has a Sales table with columns Product, Revenue and Margin. A measure is defined as Margin % := DIVIDE(SUM(Sales[Margin]), SUM(Sales[Revenue])). A matrix puts Product on rows and Margin % in values, with a total row. The data is:
| Product | Revenue | Margin |
| --- | --- | --- |
| A | 500,000 | 50,000 |
| B | 300,000 | 90,000 |
| C | 200,000 | 110,000 |
What does the total row display for Margin %?⌄
MCQ-4A weekly active-user dashboard reads a rollup table holding one distinct-user count per day. For the week of 3–9 March the seven daily counts are 41,000, 44,000, 39,000, 46,000, 43,000, 40,000 and 38,000 (291,000 in total) while a distinct count over the same week's raw events returns 168,000. Users pick any seven-day window, and the dashboard must render in under 5 seconds; a full-week scan of the events table takes 90 seconds. What should the engineer do?⌄
MCQ-24A dashboard's 'average order value' tile is AVG(amount) over the orders table. A pipeline change made `amount` NULL instead of 0.00 for refunded orders. For EU this week the table holds 100.00, 200.00 and 300.00 for three settled orders and three refunded orders, and the tile reads 200.00. The source contract says a refunded order settles at 0.00. Once the pipeline stores 0.00 for the three refunded orders, what does the tile read, and why?⌄
MCQ-28A revenue table `orders(order_date date, amount numeric)` on PostgreSQL 16 holds five rows: (2024-03-01, 100), (2024-03-04, 200), (2024-03-05, 50), (2024-03-05, 70), (2024-03-07, 30). A report runs `SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) AS running_total FROM orders ORDER BY order_date;`. What does `running_total` show on the first row dated 2024-03-05?⌄
MCQ-35A monthly units-sold report must show one row for every product in every month of the quarter, with 0 where nothing sold, and the transaction fact table must not be modified. The warehouse has `fact_sales` at transaction grain, `dim_product` with 3 products, and `dim_date` with 3 months. In the quarter `fact_sales` holds 5 rows: P1 sold 400, 380 and 410 units in Jan, Feb and Mar; P2 sold 250 in Jan; P3 sold 90 in Mar. The report is built by grouping `fact_sales` by product and month, so it returns 5 rows and P2 and P3 vanish from the months they did not sell. Which change produces the report the business asked for?⌄
MCQ-54A sales table holds five rows: (EU, A, 100.00, complete), (EU, B, 200.00, complete), (EU, A, 30.00, refunded), (US, A, 400.00, complete), (US, B, 50.00, pending). Finance defines booked revenue as status = 'complete', reports returns separately, and the ledger total for the month is 700.00. The report's detail rows come from a query that filters status = 'complete', and its region subtotals come from a second query that filters status IN ('complete', 'refunded'); the report shows EU/A 100.00, EU/B 200.00, US/A 400.00 and an EU subtotal of 330.00. Which change makes the report correct?⌄
MCQ-86A sales dashboard reports revenue by customer band. Band is defined on a customer's lifetime revenue: low below $1,000, mid $1,000 to $10,000, high above $10,000. The dashboard's grand total matches finance to the cent, but the band breakdown is disputed. Customer 4471 has three orders: $400, $700, and $9,500. The dashboard shows $1,100 in low and $9,500 in mid, and nothing in high. Which diagnosis is correct?⌄
MCQ-50A board pack's top-five products table is disputed: two products are tied at 400 units and the row numbered 5 changes between refreshes. The table must show exactly five rows, numbered 1 to 5 with no gaps and no repeated numbers, and the same product must appear at each number on every rerun. Which query change fixes it?⌄
MCQ-18A board pack reports segment conversion rates to one decimal place. A 2.9-point gap between two 34-session segments, one conversion apart, was read as a finding and drove a campaign change, and the pack is used to compare segments against each other and against a target. What is the correct remediation?⌄
MCQ-41Checkout success rate has always been published as successful charges divided by all checkout attempts. On 28 April the team redeployed it so the denominator counts only attempts that reached the processor, dropping the fraud model's pre-auth blocks, and the dashboard now shows 65.0% for the week of 21 April against 86.9% for the week of 28 April. Those weeks (21 April, 28 April): 240,000 and 245,000 attempts, 60,000 and 62,000 pre-auth blocks, 156,000 and 159,000 successful charges. A checkout release shipped in the week of 28 April and product wants to know whether it worked. What is the right reading of the two points, and what should the team do with the series?⌄
MCQ-19A region-month revenue model is up for certification. Its fact table holds one row per region, month and sales channel, and the growth column must be defensible for every region. The February extract failed for three of twelve regions, so those regions have no February rows at all. Which approach should the model use?⌄
MCQ-29A semantic model has `dim_date` with one row per date (`date_key`, `calendar_quarter`, `fiscal_quarter`, `fiscal_year`, `is_trading_day`); facts join on `date_key`. The fiscal year starts 1 February. A measure `Fiscal QTD Sales` is `CALCULATE(SUM(sales[amount]), DATESQTD(dim_date[date]))`. On 15 March 2025 it returns $3.0M; finance's fiscal QTD for the same date is $2.4M. January 2025 sales are $0.6M, February $1.1M, and 1–15 March $1.3M. Which diagnosis is correct?⌄
MCQ-25Two dashboards query the same `fct_revenue` table. For the same day they report 142,000 and 136,400 in daily revenue: a 3.9% gap, while their month totals match to the cent. One dashboard converts `occurred_at` to the viewer's browser zone; the other reports the warehouse's UTC. Finance reconciles daily revenue against a ledger that closes at 23:00 US Eastern. Which change should the team make?⌄
MCQ-27A retail chain compares FY2024 week 12 with FY2023 week 12. FY2024 week 12 booked $4.20M; FY2023 week 12 booked $4.70M, a 10.6% decline. The comparison offsets by 364 days, so both weeks start on the same weekday. Easter fell inside FY2023 week 12 and falls in FY2024 week 15; in this chain the Easter week runs about $0.60M above an ordinary week. What should the analyst conclude?⌄
MCQ-15A retailer's weekly revenue tile sits in front of the executive committee, which uses it to decide whether the week is on track against the commitment the company made to the board. The annual plan is seasonally phased: week 1's plan is $2.5M, reflecting the January trough. Week 1 actual revenue is $2.4M. The last week of December was $4.0M. The same week a year earlier was $2.6M. Q4's weekly average was $3.2M. Which headline should the tile carry?⌄
MCQ-64A revenue dashboard compares month-to-date bookings against the full prior month, so on day 3 it prints a decline of about 88% every month; the board saw the figure twice before anyone questioned it. The board reviews on day 3 and wants one change figure it can read the same way every month, month-end close concentrates bookings into the last week, and the same measure feeds the board pack, the sales dashboard and the forecast. Which change addresses the cause?⌄
MCQ-97A metrics service rebuilds a `daily_revenue` table hourly and stamps each successful build with `built_at`. Two consumers read it: an exec dashboard configured with `max_staleness = 2h` and a finance export configured with `max_staleness = 26h`. A consumer's renderer shows the last successful build while its age is within tolerance and the failure state once it is not. The 06:00 build succeeded; the 07:00, 08:00 and 09:00 builds all failed on a lock timeout against the upstream table. At 09:20 both consumers read the table. What does each render?⌄
MCQ-11`daily_revenue` is loaded nightly by merging the day's `subscription_charges` into `daily_revenue` on `customer_id`, keeping the row with the latest `updated_at`. `subscription_charges` is at grain (`customer_id`, `plan_id`). For 2024-03-11 the source holds 5,000 rows across 4,800 distinct customers and sums to 1,420,000; the 200 customers holding two plans have second rows summing to 80,000. The load ran once and the table's 2024-03-11 total reads 1,340,000. Re-running the load for the same date leaves it at 1,340,000. Which action corrects the 2024-03-11 figure?⌄
MCQ-12Nightly, an incremental load reads billing rows where `updated_at` is newer than the last watermark. Finance reports that the dashboard's revenue for a closed month is 0.8% below the ledger, and the gap grew over six months. Billing confirms invoices are amended after posting, credits and restatements, and some restatements are posted with the original posting date, so `updated_at` does not move. The billing extract is 4.2 TB and the load window is 02:00–04:00. Which approach is defensible?⌄
MCQ-33A nightly job loads orders into the warehouse with `SELECT * FROM orders WHERE updated_at > :watermark`. The source hard-deletes cancelled orders. After twelve months the warehouse holds 215,100 order rows against 210,000 in the source, and trailing-month revenue runs 2.3% above the finance ledger, which is reconciled by hand each month. Which change fixes the drift?⌄
MCQ-57fct_orders is a dbt incremental model with unique_key='order_id', incremental_strategy='merge', and this is_incremental() filter: where updated_at > (select max(updated_at) from {{ this }}). On 2025-03-04 the source team backfills 12,400 December 2024 orders; the backfill rows carry their original updated_at (2024-12-01 to 2024-12-31) and _loaded_at='2025-03-04 08:00'. Before the run, max(updated_at) in fct_orders is 2025-03-03 23:00. A tax-calculation fix is merged the same day. Finance needs FY2024 revenue corrected and future backfills to land without manual repair. Which action does both?⌄
MCQ-60A nightly Spark job loads the source `orders` table into the warehouse with `INSERT OVERWRITE staging SELECT * FROM source_orders`; `staging` was created with the source's original column order. On Tuesday the source team inserted a new `promo_code` column between `amount` and `currency`. Wednesday's load finished with no error and the same 172,000 rows as the source. The executive revenue report computes average order value as `SUM(amount)/COUNT(*)` and now shows it down 40%, with 68,800 of the 172,000 rows in `staging` holding a NULL `amount`. What is the correct diagnosis and fix?⌄
MCQ-82March revenue on the board pack reads $4.21M; the ledger says $3.94M. `fct_orders` is loaded nightly by a job that deletes the business-date partition and then inserts the day's extract, and every row carries `load_run_id` and `extract_ts`. Grouping March rows by `load_run_id` returns two runs: 4470 with 12,400,000 rows summing $3.94M, and 4471 with 850,000 rows summing $0.27M. Run 4471 was a retry of the insert task alone, the delete task had already been marked successful, and it re-read the same extract. What explains the $4.21M?⌄
MCQ-83An FX join in the revenue mart used the rate at load time instead of the transaction date, overstating bookings in every year it touched. The corrected model is ready to ship. The audit committee's restatement threshold is 1% of the published figure: at or above it, the year must be restated in a filed note; below it, the year is corrected in place with a footnote. The corrected figures are FY1 40.1m against 41.2m published, and FY2 46.6m against 46.8m published. FY1 sits in a signed filing; FY2 does not. Fourteen dashboards and three analyst extracts read the mart directly. Which plan is correct?⌄
MCQ-63A monthly revenue mart holds one row per order and is reconciled to the general ledger each month. March's published net revenue is 4.10m and the March GL period is closed, so no posting can change it. In April a 120,000 order placed in March is refunded, and the GL posts the refund in April. The mart also feeds a customer lifetime-value dashboard that sums each customer's net orders. Which change to the mart is correct?⌄
MCQ-74A revenue pipeline reads the billing source (which retains 90 days of detail), applies the revenue-recognition rule inside the extraction job, and writes straight to the modelled revenue table; no raw landing zone exists. Finance changes the recognition rule and needs FY2022–FY2023 restated on the new basis. What is the correct remediation?⌄
MCQ-88A post-mortem on a reconciliation failure found the new reporting model showed 12.13m where the spreadsheet it replaced showed 12.50m. The gap is a formula-driven manual adjustment applied every month for four years, documented nowhere, and still applied today. Which change addresses the cause?⌄
MCQ-14A payments platform certifies a monthly net-revenue measure that feeds the board pack and the regulatory filing. The general ledger is the source of record. Two differences are known and expected: a channel the warehouse feed excludes, and a one-day settlement lag at month end. Last year two internal dashboards agreed with each other for twelve months while both understated revenue by 1.2%, because they shared the same exclusion filter. Which reconciliation policy should the platform adopt for this measure?⌄
MCQ-13An orders semantic layer refreshes nightly and publishes at 06:00. Four checks run before publication: freshness (source max(updated_at) within 6 h) blocks; row count (within ±10% of the 7-day median) blocks; referential integrity (every fact.customer_key exists in dim_customer) warns to Slack; reconciliation (dashboard revenue within 0.5% of the finance ledger) warns to Slack. Last night the source was on time and the fact loaded 1,000,000 rows against a 1,010,000 median, but 60,000 rows carried customer_key = -1, the unknown member. The dashboard published at 06:00 showing $3.95M; the ledger shows $4.20M. Which change should the review require?⌄
MCQ-9Two certified dashboards report Q3 net revenue for the same business unit. Both start from $102.0M gross sales and subtract $3.9M refunds. Dashboard A reports $93.9M; Dashboard B reports $98.1M. The $4.2M gap is intercompany sales, which A subtracts and B does not. The metric catalog lists 'net revenue' once, with no owner and no written definition. Finance wants the two dashboards to agree before Friday's board deck. What should the engineer do?⌄
MCQ-70A revenue dashboard publishes one Q3 figure. The analyst's model sums the fact table's region rows to 98.1m and subtracts 4.2m of intercompany eliminations rows, giving 93.9m, which finance certifies as external revenue. The sales VP's board pack shows 98.1m and asks for the dashboard tile to carry that figure. The pipeline has been re-run and both the region rows and the eliminations rows are correct; finance says what the tile means is a business decision it will revisit. What should the dashboard publish for Q3?⌄
MCQ-89A retail company's board deck reports a 12.4% return rate. The versioned dbt model that the BI tool reads from produces 10.1%. The gap is a filter excluding `is_test_order = true` that an analyst added directly to the BI tool's saved report six weeks ago; the dbt model has no such filter. The same return rate feeds the finance and customer dashboards from that model. The audit requirement is that any published figure be reproducible by checking out a git tag and running one dbt command. Which action should the team take?⌄
MCQ-96A nightly job reports order status from `order_events(event_id, order_id, updated_at, status)`, keeping one row per order:
```sql
SELECT order_id, status FROM (
SELECT order_id, status,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) AS rn
FROM order_events) t
WHERE rn = 1;
```
The loader replaces the table each night with the same set of events, stamping every row with that load's timestamp. Two runs against the same table return identical results, but the run after the nightly load reports the count of orders as `shipped` moving by about 4,100, and the events themselves are unchanged. Which change fixes the cause?⌄
MCQ-76Your nightly report reads `orders_v2`, owned by a source team. On Tuesday at 16:40 they renamed `order_total` to `total_amount` and merged; their CI was green. Your 02:00 load failed on the missing column and the 09:00 exec review ran on stale numbers. You want the next rename to fail before the producer merges, without freezing their schema. Which change does that?⌄
MCQ-75A warehouse loads the OLTP schema 1:1 into a raw layer that analysts query directly. The OLTP team ships a schema migration about every 7 days; last quarter three of them renamed or dropped columns and broke 41 of the 90 analyst reports, at roughly 2 analyst-days each to repair. A new subject area is being designed. Which design decision does this evidence support?⌄
MCQ-59A team is renaming a column in a source table tonight. The change is breaking and there is no rollback window. The data catalog lists four direct dependents; it is maintained by hand, was last refreshed eight months ago, and its owner field is blank for about a third of assets. The transformation graph lives in the dbt project and runs four levels deep, source column → staging model → mart model → metric → report, and both the reports and two scheduled extracts read the mart live rather than being rebuilt from it. Which impact check should gate approval of the rename?⌄
MCQ-85A semantic-layer change is under review. The layer defines `active_customers`, a count over cohorts of at least 1,000 accounts, and `revenue_by_account`, one row per account, over a warehouse table merged nightly from the CRM on `updated_at`. Three analyst teams pull extracts from the table; backups are kept 35 days and the DR runbook restores from them. A customer exercises erasure and the CRM deletes the row. Which statement should the review accept?⌄
MCQ-87A retailer's support desk takes about 600 order-status calls a day. Agents read status from a dashboard backed by the warehouse, whose nightly ETL starts at 02:00 and finishes at 03:30; orders placed after 03:30 read 'processing' until the next morning's load, and agents have quoted wrong statuses to customers. The team wants agents to see current status within seconds without rebuilding the warehouse. Which change should the team make?⌄
MCQ-22An orders dashboard's 'today' tile reads `orders_daily`, a table a batch job rebuilds once a day at 06:00 by recomputing the whole table from the 400M-row `orders` table; the recompute takes 40 minutes and the tile reads the result in 40 ms. The ops team's live view runs `SELECT count(*) FROM orders WHERE created_at >= date_trunc('day', now())`, which takes 2.4 s. The tile's p99 budget is 300 ms. At 06:00 both surfaces show 0.42M orders; at 18:00 the tile still shows 0.42M while the ops view shows 1.98M. Which change makes the tile track the ops view without breaking the p99 budget?⌄
MCQ-37A retail chain's semantic model sits over a 60-million-row order table in a transactional ERP. Pricing analysts open the dashboard at 09:00 each morning to set the day's promotions; the ERP team permits batch reads only between 22:00 and 06:00, and the model refreshes at 02:00. Analysts have complained that a figure they saw at 09:00 was still on screen at 16:00. Which change should the review accept?⌄
MCQ-55A dashboard's query scans 1,096 partitions where the filter should select 31. The predicate is `WHERE DATE(load_date) BETWEEN :start AND :end`, and `load_date` is the table's partition column (DATE). The query is generated by a BI tool whose SQL template is vendor-managed and cannot be edited; the tool allows extra filter conditions to be appended to the generated SQL. The table is loaded once daily, and repartitioning a 4 TB table is out of scope. Which change restores pruning?⌄
MCQ-56A Parquet fact table has 84 columns and 34 GB on disk. A hand-written query, `SELECT user_id, event_type, ts FROM events WHERE event_date = '2024-03-01'`, reads 1.2 GB and returns in 0.9 s. The dashboard's ORM issues `SELECT * FROM events WHERE event_date = '2024-03-01'` for the same rows; it reads 34 GB and takes 25 s. Both run on the same engine against the same files. What accounts for the 28× gap in bytes read?⌄
MCQ-51A SQL Server 2022 report defines a CTE `monthly_totals` that aggregates a 200-million-row fact table, then references that CTE four times in the outer query. It runs in 16 s. Rewriting the same logic to load the aggregate into a temp table once and reference the temp table four times runs in 5 s. What explains the 11 s difference?⌄
MCQ-36A dashboard shows revenue by customer tier for the last 365 days and never shows customer detail. The current query groups raw_orders by customer_id, returns 820,000 rows, and takes 42 s; the BI tool then sums those rows to the 5 tiers. raw_orders has 1.1 billion rows for the period, 820,000 distinct customer_id values, and 5 tier values. Two pre-aggregated tables exist: daily_revenue_by_customer has 820,000 customers * 365 days = 299,300,000 rows; daily_revenue_by_tier has 5 tiers * 365 days = 1,825 rows. The freshness requirement is 24 hours. Which source and grouping returns the tier totals while reading the fewest rows?⌄
MCQ-93A nightly report computes revenue by region from `order_lines`, a 2-billion-row warehouse table; the result is 12 rows. Each row is about 100 bytes on the wire, the worker's link to the warehouse sustains 100 MB/s, and the job must finish inside a 5-minute window. The current code runs `SELECT * FROM order_lines` through `pd.read_sql` and then `df.groupby('region')['amount'].sum()`. Which change should the team make?⌄
MCQ-95A dashboard export runs `SELECT * FROM events` against a 20-million-row warehouse table clustered on `event_time`, then sorts the 20 million rows (~24 GB) in Python to keep the 500 most recent. It takes 40 minutes and 24 GB of client RSS; the warehouse has 32 workers and spills to disk. Which change removes the 40-minute cost?⌄
MCQ-94An enrichment step joins a 2,000,000-row fact table to a 50,000-row lookup table. Version A calls, once per fact row, `lookup[lookup.key == row.key]`, and the job takes 14 minutes. Version B builds a dict from the lookup once and calls `d.get(row.key)` per fact row; it takes 3 seconds. A reviewer asks why the gap is this large. Which explanation is correct?⌄
MCQ-21A fact table carries one partition per report date, 2,920 of them across 8 years, refreshed nightly by a job that re-reads and rewrites every partition in a rolling window. Profiling shows the cost is flat at about 18 s per partition whether or not that partition's rows changed, and the job does not detect unchanged partitions. The window is 730 days, so the run takes 3 h 40 min; the upstream extract lands at 04:30 and reports are due at 06:00, giving the job 90 minutes of wall clock. Over 90 nights of instrumentation, 2-3 partitions per run carried changed rows, and corrections to earlier dates have arrived as late as 45 days after the report date. Those corrections are the normal returns and chargeback process, not a feed defect. Which change should the team make?⌄
MCQ-42A weekly operations dashboard has 34 tiles and a p95 load time of 22 seconds. Ninety days of interaction telemetry: the top 4 tiles account for 91% of interactions, the next 10 for 8%, and the remaining 20 for 1%. Those 20 tiles also drive 78% of the dashboard's warehouse spend. Six people now ask for six new tiles, each phrased as 'I want to see everything about my area', and the team has one sprint. What should the team do?⌄
MCQ-79A 2 TB fact_orders table is stored as 4,000 blocks of about 500 MB, sorted by order_id in load order, with per-block min/max zone maps. The query mix is 85% filtering a 1-day order_date range (about 0.3% of rows), 10% filtering a single customer_id, and 5% filtering status. The nightly load window is 30 minutes; a full re-sort of the table takes about 4 hours. Which change should the team make?⌄
MCQ-78On Postgres 16, a dashboard aggregates a 400M-row `orders` table by hour. The team added `mv_orders_hourly` and refreshes it with a plain `REFRESH MATERIALIZED VIEW mv_orders_hourly` every 5 minutes via pg_cron; the refresh takes about 50 s, and the view has no unique index. Since deploying, dashboard queries are canceled by a 30 s `statement_timeout` roughly once every 5 minutes, and product requires the hourly figures to be no more than 10 minutes old. Which change removes the periodic cancellations while keeping the figures within that freshness bound?⌄
MCQ-77You are asked to review a semantic-layer change covering the cost of a query in a consumption-priced warehouse. The change is otherwise correct. Which statement should the review accept?⌄
MCQ-16A weekly reliability dashboard plots two bars side by side: checkout at 97.4% success and search at 98.6%, on a y-axis running 94% to 100%. Both figures are correct, search's bar is about a third taller, and the on-call team moved checkout to the bottom of the queue. Which change addresses the cause of the misread?⌄
MCQ-17An on-call dashboard shows 40 service tiles, each coloured green, amber or red and nothing else. Last month an engineer with deuteranopia read a red tile as green and escalated a Sev-1 22 minutes late, and the weekly ops review prints the same dashboard in greyscale, where all three statuses look alike. The team wants one change that makes status readable both on the printed review and for the colour-deficient engineer. Which change does that?⌄
MCQ-44A BI tool exports the quarterly business review as a 40-page PDF deck. The revenue chart stacks 7 regions and encodes them by colour only; the two most-clashing series sit at a 1.4:1 luminance contrast ratio, the axis labels export at 6pt, and the chart leaves the tool as a single raster image, which a screen reader announces as 'chart'. Six of the 40 recipients print the deck on mono laser printers, three project it in a bright room, and one reads it with a screen reader. Which change makes the deck usable in all three of those conditions?⌄
MCQ-43A payments team's reporting layer emits a daily count of failed captures. Over the last 365 days the count had median 90, p95 260, p99 420, and max 610. The on-call log records 8 days in the year when a capture-worker restart was actually needed; a missed restart day costs about 40 minutes of failed captures, while a page that needs no action costs the on-call about 3 minutes of triage. The rotation's stated budget is at most one page a month sustained, 12 a year, and recipients filter anything above that. A backtest of candidate thresholds gives:
| Threshold | Days fired/yr | Of those, actioned |
| --- | --- | --- |
| 250 | 44 | 5 |
| 300 | 22 | 8 |
| 380 | 11 | 8 |
| 420 | 4 | 4 |
| 610 | 1 | 1 |
Which threshold should ship?⌄
MCQ-40A warehouse table `fct_events` feeds six dashboards. Two of them report "Weekly Active Accounts" for the same week: the exec dashboard shows 412,000, the growth dashboard shows 388,000. Both query the same table: the exec SQL filters `WHERE event_count > 0`, the growth SQL filters `WHERE session_count > 0`. The only written definition of the metric is a Confluence page last edited 14 months ago, which neither dashboard links to and which had 3 views in the last 90 days. A director presented 412,000 at a review before anyone noticed the gap. Which change addresses the cause?⌄
MCQ-23A Power BI semantic model serves 240 users across three regional teams. Each report scopes data with a visual-level filter on Region, and the model defines no RLS roles. An EMEA manager connects to the model with Analyze in Excel and sees rows for all three regions. Which change closes the exposure for every consumer of the model?⌄
MCQ-84A revenue dashboard is published to 400 employees. It shows average revenue per customer by region. Region North has 2 customers and displays an average of $50k. A rep who closed a $30k deal with one North customer opens the dashboard. What follows for the North cell?⌄
MCQ-66A checkout A/B test was predeclared to run until 12,000 users per arm, with a two-sided 95% interval and a single analysis at the end. On day 4, with 340 users per arm, the dashboard shows +3.2 percentage points and a 90% interval of +0.1 to +6.3, so the PM wants to ship. What should the analyst report?⌄
MCQ-67A dashboard reports 30-day retention as the share of users who signed up in the last 90 days and were active on day 30 after signup. A campaign added 2,700 signups in the last three weeks; the three earlier cohorts total 30,400 signups, and the pooled figure fell from 62% to 57%. The board asks whether retention got worse. Which presentation answers the board's question?⌄
MCQ-68A monthly revenue forecast renders a single point value on the finance dashboard, and finance uses that value as the plan number. The model was refit last month. Back-testing it over the trailing 12 months gives a mean absolute error of 18% of the level, but the ±4% interval the dashboard prints around the point was outside the actual in 7 of those 12 months. Which change is justified by these numbers?⌄
MCQ-20A quarterly deck shows marketing spend and revenue as two lines on a dual axis; both grew with headcount over the same eight quarters. The deck concludes "spend is driving revenue" and the reallocation vote is Friday. No experiment has been run and none can be run before the vote. Which change to the deck is defensible?⌄
MCQ-69A semantic layer defines net_revenue at the grain of order line, with customer_id, product_category and region as dimensions. The monthly review shows net revenue down 6.0% MoM. An analyst submits this decomposition of the 6.0-point decline: price -2.0 pp, volume -1.5 pp, mix -1.0 pp, new customers +0.5 pp, total -6.0 pp. What should the reviewer do?⌄
MCQ-65A monthly revenue total is certified: finance ties it to the ledger to the cent before publishing. The pipeline computes it by sampling 1% of 40M order rows; last month one large customer fell outside the sample and the total came in 11% low, with nothing on the report marking it as an estimate. The full computation takes 90 s. What should the team do about the sampling?⌄
MCQ-48A subscription company's weekly exec summary carries one acquisition figure: blended trial-to-paid conversion, total conversions ÷ total trials. Q1 reads 8.0% (8,000 / 100,000) and Q2 reads 15.3% (15,300 / 100,000), and the board deck calls it a near-doubling, which does not reconcile with the two channel rows on page four of the appendix: paid search 20.0% (4,000 / 20,000) → 18.0% (14,400 / 80,000); organic 5.0% (4,000 / 80,000) → 4.5% (900 / 20,000). The post-mortem asks which figure the exec summary should carry.⌄
MCQ-72A subscription business churned 12,000 accounts last quarter. Its cancellation survey, shown to every churner, drew 4,800 responses, and 2,928 of those named price as the reason. Product has a pricing decision due Friday and cannot rerun the survey before then. It asks the analyst for "the share of churners who left over price." What should the analyst report?⌄
MCQ-92A retail BI team's executive dashboard shows July net revenue as $4.2M, pulled from a scheduled Python notebook that sums the raw orders table. The certified net revenue metric lives in the semantic layer and was redefined on 3 March to exclude refunds; it reports $3.9M for the same July period. The notebook was last edited in January and has no owner. What is the correct diagnosis and fix?⌄
MCQ-38Eleven reports, three notebooks, and two scheduled extracts read the same warehouse through a semantic layer. An audit found six definitions of active customer (74k–96k) and three of revenue (93.9m–98.1m); the executive summary used the most generous active-customer figure, and the discrepancies traced to filters written inside the extracts and notebooks. Finance reclassifies customers monthly, and the regulatory extract must reproduce any figure it published last quarter. Which policy should the team adopt?⌄
MCQ-45A retail company's semantic layer holds 60 certified metrics. Six months ago the platform team blocked analysts from creating custom measures in the BI tool; in-platform duplicate measures fell from 22 to 2. The monthly close now takes 6 days instead of 3, and an audit found 14 spreadsheet copies of Net Revenue. A survey of the 14 authors found 11 needed a region split the certified Net Revenue metric does not expose, 9 said the certified metric takes 35–40 s to return versus under 2 s for their spreadsheet, and 3 could not find the certified metric in the catalog. Which action should the review accept?⌄
MCQ-46A BI estate has 900 reports. Forty refresh hourly and 860 refresh daily, so the estate runs 1,820 refreshes per day. Twelve months of view logs show 460 reports with zero direct views: 12 regulatory filings opened only during the annual audit window, 180 delivered by scheduled email to distribution lists, 60 embedded as tiles in an executive dashboard, and 208 with no direct views and no downstream delivery. Of those 208, 40 are among the hourly-refresh reports and 168 refresh daily. The team will archive the largest set of reports that no consumer still uses. How many daily refreshes does that reclaim?⌄
MCQ-49A product analyst asks your data team for a dashboard showing net revenue retention. Today the number is pulled from the warehouse by hand: 2 hours per pull, four pulls a year, and it lands in a board slide. No operational decision reads it: the board reviews it and moves on. A dashboard would take 6 engineer-days to build and would refresh hourly. Your team has one engineer free this sprint. What should you do?⌄
MCQ-71A BI developer is asked to estimate a new 'net revenue by region' chart. The source is a Salesforce extract the team has not used before. Finance defines net revenue as bookings minus credits; sales defines it as bookings minus refunds. Across the team's last five requests, visual build averaged 1 day, while agreeing definitions and reconciling an unfamiliar source averaged 9 days. The requester wants a two-day estimate. What should the developer do?⌄
MCQ-90An analytics team adds one number to its onboarding review: days from a new analyst's start date to the first figure finance accepts without correction. Last quarter the median was 12 days. The instrumented steps for a typical analyst were 1 day to find the certified revenue model, 1 day to write the query, 1 day reconciling the number with the finance analyst, and 9 days queued for the warehouse role that grants read access to the revenue tables. Adoption of the certified model over the same quarter rose from 40% to 70%. Which action follows from the breakdown?⌄
MCQ-62A US parent consolidates its UK subsidiary. Q3 local revenue was £8.0m. The platform translates every period at the latest closing rate, so Q3 first showed $9.6m at the 30 September rate of 1.20; by 15 October, with the rate at 1.15, the same quarter read $9.2m with no new transactions. The CFO wants the Q3 revenue figure to reflect what the subsidiary earned in the quarter and to stop moving after the quarter closes. Q3 rates: weighted-average transaction-date 1.25, budget 1.30, 1 July 1.35, 30 September 1.20, 15 October 1.15. Which figure should the report show?⌄