A Power BI sales model has 12 million order lines and customers can change region monthly; how would you model region history for a five-year revenue dashboard?

I would use a type 2 customer dimension and attribute each order to the customer version valid on the order date. - Each customer change creates a new surrogate key with valid-from, valid-to, and current-row fields while preserving the business customer ID. - The 12 million fact rows store the version surrogate key, so historical regional revenue does not move when a customer changes region. - I would test for overlapping validity windows and one matching dimension version per order before publishing the Power BI model.

A Snowflake customer SCD2 table has 8 million versions and corrections can arrive 14 days late; how would you keep a Looker retention dashboard historically correct?

I would rebuild every customer interval touched by the 14-day late-arrival horizon, then re-key retention facts whose EventTimestamp falls in a changed interval. - The dbt model orders source rows by CustomerID, EffectiveAt, and SourceUpdatedAt, derives exclusive ValidTo with LEAD, and rejects duplicate effective boundaries. - A merge updates CustomerVersionKey for existing and new facts where EventTimestamp is at least ValidFrom and less than ValidTo, rather than processing only newly arrived facts. - The LookML Explore joins fact.CustomerVersionKey to customer.CustomerVersionKey as many_to_one, while tests require one IsCurrent row per CustomerID and exactly one version match per retention event.

A Power BI order model has Order Date, Ship Date, and Delivery Date across 40 million rows; how would you support all three calendar views without ambiguous relationships?

I would use one conformed Date dimension with one active relationship and two inactive role-playing relationships. - Order Date would be active because it drives the default sales dashboard, while Ship Date and Delivery Date remain inactive. - Measures such as Shipped Revenue and Delivered Orders would activate the intended relationship with USERELATIONSHIP inside CALCULATE. - I would group and name measures by date role so report authors do not combine a Ship Date axis with an Order Date measure accidentally.

A Tableau customer dashboard covers 6 million purchases, and each customer can belong to up to five marketing segments at once; how would you model the segment relationship?

I would keep purchases at PurchaseLineID grain and materialize segment membership at the event date before Tableau queries it. - An effective-dated membership source stores CustomerID, SegmentID, ValidFrom, and exclusive ValidTo, but the warehouse resolves it into a PurchaseSegment bridge so Tableau does not have to resolve a many-to-many non-equi join at query time. - Full revenue may be shown independently for each overlapping segment, but those segment rows are not additive across the portfolio; an AllocationWeight is required when the total must reconcile. - I would test uniqueness of PurchaseLineID plus SegmentID, allocation weights totaling 1 per PurchaseLineID, and distinct-customer counts under single- and multi-segment filters.

A university Power BI model must report eligibility and attendance for 80,000 students at every course session, including eligible students with no attendance event; what fact design would you use?

I would use an eligibility coverage fact and a separate attendance event fact, both keyed at student and course-session grain. - EligibilityFact contains one row per StudentKey and CourseSessionKey by expanding term registrations against the scheduled sessions the student was expected to attend. - AttendanceFact contains one row per observed StudentKey and CourseSessionKey with AttendanceStatusKey and CheckInTimestamp, while both facts share Student, Course, Session, and Date dimensions. - Missing attendance is an anti-match from eligible rows to attendance rows on StudentKey plus CourseSessionKey, so a non-event is counted explicitly rather than guessed from a transaction table.

A Looker sales pipeline has 3 million opportunities and seven milestones from creation to payment; how would you model stage duration and current backlog?

I would use an accumulating snapshot with one row per OpportunityID and seven milestone dates updated as the opportunity advances. - The row stores CreatedAt, QualifiedAt, DemoAt, ProposalAt, WonAt, InvoicedAt, and PaidAt together with CurrentStageKey and TerminalStatusKey. - LookML dimensions calculate elapsed time between adjacent non-null milestones, while backlog measures count opportunities whose current milestone exists and next milestone is null. - The pipeline merges by OpportunityID, and a separate OpportunityStageEvent fact remains necessary for repeated stages, reopenings, or a complete transition audit.

A Power BI inventory dashboard stores 15,000 products across 120 warehouses at daily grain for two years; how would you model stock and movement?

I would separate the daily inventory snapshot from the movement fact because balances and flows have different grains and time additivity. - A dense snapshot at ProductKey, WarehouseKey, and SnapshotDate would contain about 1.314 billion rows over 730 days before accounting for inactive product-warehouse combinations. - Receipts, shipments, and adjustments remain at MovementLineID grain with signed QuantityChange and ValueChange for flow analysis and reconciliation. - Power BI balance measures select the last valid snapshot in the period, while movement measures sum over dates and reconcile opening balance plus movements to closing balance.

A Power BI account model has 2 million accounts, each shared by up to four sales representatives with changing credit percentages; how would you prevent double-counted quota dashboards?

I would keep company revenue in the invoice-line fact and materialize representative credit in a separate allocated fact. - The warehouse resolves AccountID, InvoiceDate, RepID, ValidFrom, ValidTo, and AllocationPercent into rows keyed by InvoiceLineID plus RepID because Power BI relationships cannot evaluate validity-range joins. - Each allocation row stores CreditedRevenue, with tests requiring AllocationPercent to total 1 and CreditedRevenue to total source LineRevenue for every InvoiceLineID. - Company Revenue reads the base fact and Credited Revenue reads the allocation fact, so selecting several representatives cannot multiply the legal company total.

A Tableau commerce dashboard receives 25 million order lines with order-level shipping fees and line-level discounts; what grain would you choose for its fact model?

I would declare the primary sales fact at one row per order line and handle header measures without repeating them as additive values. - Product quantity, line revenue, and line discount belong directly on the 25 million-row line fact. - Shipping can live in a separate order-header fact or be allocated to lines with a documented rule such as revenue share. - Tableau calculations would use the chosen shipping representation consistently, and a grain test would enforce uniqueness of order ID plus line ID.

A finance Looker model needs daily cash movements from 90 million transactions and month-end balances for 400 accounts; would you use one fact table or two?

I would use a transaction fact for cash flows and a periodic snapshot fact for month-end balances. - The 90 million-row transaction fact supports additive inflow and outflow measures by posting date, account, and transaction type. - The snapshot has one row per account and month-end date, making reported balance selection explicit rather than reconstructing every close from all history. - Looker explores can share Account and Date views, but balance measures must select the final snapshot and must not sum across months.

A Power BI support model has 18 million tickets, 12 low-cardinality flags, and ticket numbers shown on drill-through pages; how would you place those attributes?

I would keep TicketNumber as a degenerate dimension and benchmark the 12 flags as fact columns before introducing a junk dimension. - TicketNumber stays on the fact because a separate 18 million-row dimension with no other attributes adds a relationship without reducing grain. - VertiPaq often compresses 12 low-cardinality Boolean columns well; a junk dimension is justified only if the observed flag combinations and governed labels reduce model size or simplify slicing in a measured test. - In either design I would expose business labels, hide surrogate keys, and verify drill-through by TicketNumber plus model size with VertiPaq Analyzer.

A dbt model feeding a Tableau claims dashboard receives 5 million facts before some provider dimension rows arrive, with a 24-hour refresh SLA; how would you handle the unknown members?

I would create inferred provider members so every claim retains a valid dimension key during the 24-hour reporting window. - A missing provider business key maps to a minimal surrogate row marked Inferred rather than a null foreign key or a dropped claim. - When provider attributes arrive, dbt updates that inferred row in place so the 5 million-row fact does not need a key change. - Data tests would track unresolved inferred members by age, and Tableau would label them Pending Provider rather than merge them with truly unknown data.

In a Power BI model with 30 million sales rows, why can CALCULATE inside SUMX return a different result from SUMX over a naked SUM expression?

CALCULATE performs context transition, turning the iterator's current row context into filter context for the measure evaluation. - SUMX creates row context over its input table, but a raw aggregation does not automatically filter the model to that current row. - Wrapping the aggregation in CALCULATE, or calling an existing measure which gets implicit CALCULATE, applies the current row's columns as filters. - I would avoid iterating 30 million fact rows when the same report measure can iterate a smaller Product or summarized table at the required grain.

A Power BI margin dashboard has 60 million invoice lines; how would you write a CALCULATE measure for revenue from profitable products without filtering the full fact table row by row?

I would filter the Product dimension or a compact product set, then let relationships propagate that filter to the fact. - CALCULATE can apply FILTER over the 80,000-row Product table using a product-level margin measure or governed profitability attribute. - Filtering all 60 million invoice rows with FILTER is usually more expensive and can encode line-level semantics that differ from profitable-product semantics. - I would confirm whether profitable means current catalog classification or profitability in the selected report period before finalizing the measure.

A Power BI pricing report contains 12 million lines with different quantities and unit margins; which DAX iterator would you use for weighted margin per unit?

I would use SUMX for total line margin and divide by total quantity rather than average the UnitMargin values. - The numerator iterates invoice lines as Quantity multiplied by UnitMargin under the current ProductKey and DateKey filters. - The denominator sums Quantity in the same filter context, and DIVIDE returns blank or an agreed alternate result when quantity is zero. - I would reconcile the result with warehouse SQL because AVERAGEX over UnitMargin gives a one-unit line and a thousand-unit line equal weight.

A DAX measure in a 25-table Power BI model repeats the same prior-period expression four times; how would variables change its behavior and maintainability?

I would compute the prior-period result once in a named variable and reuse that scalar in the final expression. - Variables make the evaluation order visible and prevent four copies of a complex CALCULATE branch from drifting during edits. - A variable is evaluated in the filter context where it is declared, so moving it outside an iterator or conditional can change the result. - I would use domain names such as PriorQuarterRevenue and SelectedTarget, then keep the 25-table model's display expression short enough to review.

A Power BI retail model uses a 4-4-5 fiscal calendar across six years; how would you implement year-over-year dashboard measures?

I would use a complete fiscal Date dimension with explicit fiscal week and comparable-period keys rather than rely on Gregorian shortcuts. - The six-year calendar would assign fiscal year, quarter, period, week, and a prior-year comparable week key to every date. - Measures would shift through those fiscal attributes or a calculation group, so a 53-week year follows the agreed retail comparison rule. - I would test period totals around fiscal year boundaries because DATEADD and SAMEPERIODLASTYEAR assumptions can misalign a 4-4-5 calendar.

A Power BI executive dashboard refreshes daily at 07:00 and the current month has only 18 loaded days; how would you make year-over-year MTD comparison fair?

I would compare the current 18 loaded days with the same completed-day cutoff in the prior year. - A Last Complete Data Date measure would come from the fact load watermark, not from TODAY, because the 07:00 refresh may lag source activity. - Current MTD and prior-year MTD would both filter their Date ranges to the equivalent ordinal day or mapped business date. - The dashboard subtitle would expose the cutoff date so users know the comparison excludes later prior-year days.

A Power BI logistics model has 45 million orders with active Order Date and inactive Ship Date relationships; how would you calculate shipped revenue by month?

I would define Shipped Revenue with CALCULATE and USERELATIONSHIP between Date and Ship Date. - USERELATIONSHIP activates the existing inactive relationship only for that measure while the default Order Revenue continues to use Order Date. - The 45 million fact rows keep both date keys, and the Date dimension remains the single source for month and fiscal attributes. - I would pair Ship Date visuals with explicitly named shipping measures because a generic Revenue measure would still follow the active Order Date path.

A Power BI model compares 20,000 campaign targets with 80 million sales rows but the target table is intentionally disconnected; how would you apply selected campaigns in DAX?

I would use TREATAS to project the selected campaign keys onto the governed campaign or sales key column for the comparison measure. - The disconnected table remains a parameter surface, avoiding a physical relationship that could create ambiguous paths in the semantic model. - TREATAS should pass stable campaign IDs rather than display names, especially across 20,000 targets with possible duplicate labels. - I would keep target and actual measures separate, then calculate variance only after both receive the intended report filters.

BI Developer interview questions

100 real questions with model answers and explanations for Middle candidates.

See a BI Developer resume example →

Practice with flashcards

Spaced repetition · Hunter Pass

Questions

bipower-bidashboards

I would use a type 2 customer dimension and attribute each order to the customer version valid on the order date.

Each customer change creates a new surrogate key with valid-from, valid-to, and current-row fields while preserving the business customer ID.
The 12 million fact rows store the version surrogate key, so historical regional revenue does not move when a customer changes region.
I would test for overlapping validity windows and one matching dimension version per order before publishing the Power BI model.

Why interviewers ask this: The interviewer is checking whether you can preserve historical attribution with SCD2 rather than overwrite business history.

bilookersnowflake

I would rebuild every customer interval touched by the 14-day late-arrival horizon, then re-key retention facts whose EventTimestamp falls in a changed interval.

The dbt model orders source rows by CustomerID, EffectiveAt, and SourceUpdatedAt, derives exclusive ValidTo with LEAD, and rejects duplicate effective boundaries.
A merge updates CustomerVersionKey for existing and new facts where EventTimestamp is at least ValidFrom and less than ValidTo, rather than processing only newly arrived facts.
The LookML Explore joins fact.CustomerVersionKey to customer.CustomerVersionKey as many_to_one, while tests require one IsCurrent row per CustomerID and exactly one version match per retention event.

Why interviewers ask this: A strong answer handles late SCD2 corrections in the dimension, existing fact attribution, and the Looker join contract.

bipower-bi

I would use one conformed Date dimension with one active relationship and two inactive role-playing relationships.

Order Date would be active because it drives the default sales dashboard, while Ship Date and Delivery Date remain inactive.
Measures such as Shipped Revenue and Delivered Orders would activate the intended relationship with USERELATIONSHIP inside CALCULATE.
I would group and name measures by date role so report authors do not combine a Ship Date axis with an Order Date measure accidentally.

Why interviewers ask this: The interviewer is assessing whether you can expose multiple date roles explicitly without creating ambiguous filter paths.

bitableaudashboards

I would keep purchases at PurchaseLineID grain and materialize segment membership at the event date before Tableau queries it.

An effective-dated membership source stores CustomerID, SegmentID, ValidFrom, and exclusive ValidTo, but the warehouse resolves it into a PurchaseSegment bridge so Tableau does not have to resolve a many-to-many non-equi join at query time.
Full revenue may be shown independently for each overlapping segment, but those segment rows are not additive across the portfolio; an AllocationWeight is required when the total must reconcile.
I would test uniqueness of PurchaseLineID plus SegmentID, allocation weights totaling 1 per PurchaseLineID, and distinct-customer counts under single- and multi-segment filters.

Why interviewers ask this: The question tests whether you can represent effective-dated multivalued membership without relying on an unsupported range join or additive overlapping totals.

bipower-bidesign

I would use an eligibility coverage fact and a separate attendance event fact, both keyed at student and course-session grain.

EligibilityFact contains one row per StudentKey and CourseSessionKey by expanding term registrations against the scheduled sessions the student was expected to attend.
AttendanceFact contains one row per observed StudentKey and CourseSessionKey with AttendanceStatusKey and CheckInTimestamp, while both facts share Student, Course, Session, and Date dimensions.
Missing attendance is an anti-match from eligible rows to attendance rows on StudentKey plus CourseSessionKey, so a non-event is counted explicitly rather than guessed from a transaction table.

Why interviewers ask this: The interviewer wants a coverage fact that represents expected events and supports a valid comparison with observed attendance.

bici-cdlooker

I would use an accumulating snapshot with one row per OpportunityID and seven milestone dates updated as the opportunity advances.

The row stores CreatedAt, QualifiedAt, DemoAt, ProposalAt, WonAt, InvoicedAt, and PaidAt together with CurrentStageKey and TerminalStatusKey.
LookML dimensions calculate elapsed time between adjacent non-null milestones, while backlog measures count opportunities whose current milestone exists and next milestone is null.
The pipeline merges by OpportunityID, and a separate OpportunityStageEvent fact remains necessary for repeated stages, reopenings, or a complete transition audit.

Why interviewers ask this: A strong answer distinguishes the current process snapshot from the event history and defines all seven stated milestones.

warehousegrainbi

I would separate the daily inventory snapshot from the movement fact because balances and flows have different grains and time additivity.

A dense snapshot at ProductKey, WarehouseKey, and SnapshotDate would contain about 1.314 billion rows over 730 days before accounting for inactive product-warehouse combinations.
Receipts, shipments, and adjustments remain at MovementLineID grain with signed QuantityChange and ValueChange for flow analysis and reconciliation.
Power BI balance measures select the last valid snapshot in the period, while movement measures sum over dates and reconcile opening balance plus movements to closing balance.

Why interviewers ask this: The interviewer is testing grain discipline, correct row-count arithmetic, and recognition that inventory balances are semi-additive over time.

bipower-bidashboards

I would keep company revenue in the invoice-line fact and materialize representative credit in a separate allocated fact.

The warehouse resolves AccountID, InvoiceDate, RepID, ValidFrom, ValidTo, and AllocationPercent into rows keyed by InvoiceLineID plus RepID because Power BI relationships cannot evaluate validity-range joins.
Each allocation row stores CreditedRevenue, with tests requiring AllocationPercent to total 1 and CreditedRevenue to total source LineRevenue for every InvoiceLineID.
Company Revenue reads the base fact and Credited Revenue reads the allocation fact, so selecting several representatives cannot multiply the legal company total.

Why interviewers ask this: The interviewer is checking whether changing many-to-many allocation is resolved at a usable grain rather than delegated to an ambiguous Power BI filter path.

bitableaudashboards

I would declare the primary sales fact at one row per order line and handle header measures without repeating them as additive values.

Product quantity, line revenue, and line discount belong directly on the 25 million-row line fact.
Shipping can live in a separate order-header fact or be allocated to lines with a documented rule such as revenue share.
Tableau calculations would use the chosen shipping representation consistently, and a grain test would enforce uniqueness of order ID plus line ID.

Why interviewers ask this: A strong answer prevents mixed-grain columns from producing plausible but inflated dashboard totals.

transactionsmodelingbi

I would use a transaction fact for cash flows and a periodic snapshot fact for month-end balances.

The 90 million-row transaction fact supports additive inflow and outflow measures by posting date, account, and transaction type.
The snapshot has one row per account and month-end date, making reported balance selection explicit rather than reconstructing every close from all history.
Looker explores can share Account and Date views, but balance measures must select the final snapshot and must not sum across months.

Why interviewers ask this: The interviewer is evaluating whether you choose fact types from the BI question and each measure's additivity.

bipower-bidrill-through

I would keep TicketNumber as a degenerate dimension and benchmark the 12 flags as fact columns before introducing a junk dimension.

TicketNumber stays on the fact because a separate 18 million-row dimension with no other attributes adds a relationship without reducing grain.
VertiPaq often compresses 12 low-cardinality Boolean columns well; a junk dimension is justified only if the observed flag combinations and governed labels reduce model size or simplify slicing in a measured test.
In either design I would expose business labels, hide surrogate keys, and verify drill-through by TicketNumber plus model size with VertiPaq Analyzer.

Why interviewers ask this: The question checks whether you apply dimensional patterns with evidence instead of assuming a junk dimension always compresses a Power BI model better.

bidbttableau

I would create inferred provider members so every claim retains a valid dimension key during the 24-hour reporting window.

A missing provider business key maps to a minimal surrogate row marked Inferred rather than a null foreign key or a dropped claim.
When provider attributes arrive, dbt updates that inferred row in place so the 5 million-row fact does not need a key change.
Data tests would track unresolved inferred members by age, and Tableau would label them Pending Provider rather than merge them with truly unknown data.

Why interviewers ask this: The interviewer is assessing how you preserve fact completeness while late dimensions catch up with a dashboard SLA.

bipower-bidax

CALCULATE performs context transition, turning the iterator's current row context into filter context for the measure evaluation.

SUMX creates row context over its input table, but a raw aggregation does not automatically filter the model to that current row.
Wrapping the aggregation in CALCULATE, or calling an existing measure which gets implicit CALCULATE, applies the current row's columns as filters.
I would avoid iterating 30 million fact rows when the same report measure can iterate a smaller Product or summarized table at the required grain.

Why interviewers ask this: The interviewer is testing whether you understand DAX evaluation context and can connect correctness to model-scale choices.

measuresdaxmodeling

I would filter the Product dimension or a compact product set, then let relationships propagate that filter to the fact.

CALCULATE can apply FILTER over the 80,000-row Product table using a product-level margin measure or governed profitability attribute.
Filtering all 60 million invoice rows with FILTER is usually more expensive and can encode line-level semantics that differ from profitable-product semantics.
I would confirm whether profitable means current catalog classification or profitability in the selected report period before finalizing the measure.

Why interviewers ask this: A strong answer combines CALCULATE semantics, efficient filter shape, and a precise dashboard definition.

bipower-bicss

I would use SUMX for total line margin and divide by total quantity rather than average the UnitMargin values.

The numerator iterates invoice lines as Quantity multiplied by UnitMargin under the current ProductKey and DateKey filters.
The denominator sums Quantity in the same filter context, and DIVIDE returns blank or an agreed alternate result when quantity is zero.
I would reconcile the result with warehouse SQL because AVERAGEX over UnitMargin gives a one-unit line and a thousand-unit line equal weight.

Why interviewers ask this: The interviewer is checking whether the iterator and denominator implement the stated volume weighting rather than an unrelated average of percentages.

measuresdaxbi

I would compute the prior-period result once in a named variable and reuse that scalar in the final expression.

Variables make the evaluation order visible and prevent four copies of a complex CALCULATE branch from drifting during edits.
A variable is evaluated in the filter context where it is declared, so moving it outside an iterator or conditional can change the result.
I would use domain names such as PriorQuarterRevenue and SelectedTarget, then keep the 25-table model's display expression short enough to review.

Why interviewers ask this: The question tests whether you understand both the readability benefit and evaluation-context semantics of DAX variables.

bipower-bidashboards

I would use a complete fiscal Date dimension with explicit fiscal week and comparable-period keys rather than rely on Gregorian shortcuts.

The six-year calendar would assign fiscal year, quarter, period, week, and a prior-year comparable week key to every date.
Measures would shift through those fiscal attributes or a calculation group, so a 53-week year follows the agreed retail comparison rule.
I would test period totals around fiscal year boundaries because DATEADD and SAMEPERIODLASTYEAR assumptions can misalign a 4-4-5 calendar.

Why interviewers ask this: The interviewer is evaluating whether time intelligence reflects the business calendar rather than merely using built-in date functions.

bipower-bidashboards

I would compare the current 18 loaded days with the same completed-day cutoff in the prior year.

A Last Complete Data Date measure would come from the fact load watermark, not from TODAY, because the 07:00 refresh may lag source activity.
Current MTD and prior-year MTD would both filter their Date ranges to the equivalent ordinal day or mapped business date.
The dashboard subtitle would expose the cutoff date so users know the comparison excludes later prior-year days.

Why interviewers ask this: A strong answer prevents partial-period bias and ties time intelligence to the actual refresh state.

bipower-bidax

I would define Shipped Revenue with CALCULATE and USERELATIONSHIP between Date and Ship Date.

USERELATIONSHIP activates the existing inactive relationship only for that measure while the default Order Revenue continues to use Order Date.
The 45 million fact rows keep both date keys, and the Date dimension remains the single source for month and fiscal attributes.
I would pair Ship Date visuals with explicitly named shipping measures because a generic Revenue measure would still follow the active Order Date path.

Why interviewers ask this: The interviewer is checking precise use of inactive relationships and clear semantic naming for report authors.

bipower-bidax

I would use TREATAS to project the selected campaign keys onto the governed campaign or sales key column for the comparison measure.

The disconnected table remains a parameter surface, avoiding a physical relationship that could create ambiguous paths in the semantic model.
TREATAS should pass stable campaign IDs rather than display names, especially across 20,000 targets with possible duplicate labels.
I would keep target and actual measures separate, then calculate variance only after both receive the intended report filters.

Why interviewers ask this: The question tests whether you can create a virtual relationship deliberately without obscuring measure semantics.

Locked questions

21
A Power BI treasury dashboard has 10 million daily account balances with gaps on weekends; how would you calculate closing balance for any selected month?
bipower-bidashboards
22
A Power BI model has 70 base measures, four time comparisons, and three display currencies; how would calculation groups reduce the measure surface?
bipower-bimeasures
23
A Power BI matrix shows margin percentage for 60 products and its grand total does not equal the average of visible rows; is that a defect, and how would you define the measure?
bipower-bicss
24
A Power BI matrix drills from Region to Store across 1,200 stores and needs a different subtotal formula at each level; how would you control totals in DAX?
bipower-bimatrix
25
A Power BI report with 35 million sales rows needs each category's share of the currently selected region and year; would you use ALL, ALLSELECTED, or REMOVEFILTERS?
bipower-bi
26
A Power BI semantic model has 90 measures used by 14 reports; how would measure branching keep DAX totals and definitions consistent?
measuresdaxbi
27
A Looker order Explore joins 50 million order items to customers, refunds, and promotions; how would you declare the joins and prevent fanout?
bilookerjoins
28
A Looker project has 24 domain Explores that repeat fiscal fields and order-status logic; how would you reuse LookML without hiding domain differences?
bilookerlookml
29
A Looker Explore joins 12 million orders to multiple payments and shipments; when do symmetric aggregates protect dashboard measures, and what must the model provide?
joinsaggregationbi
30
A Looker finance dashboard queries a 300 million-row BigQuery ledger and must be ready by 08:00 daily; how would you use a PDT and datagroup?
queriesbigquerybi
31
A Tableau workbook shows revenue for 1,500 stores, and a FIXED store LOD ignores a regular Region filter; why, and how would you make the dashboard behavior intentional?
bitableaudashboards
32
A Tableau customer dashboard has 9 million purchases and needs customer lifetime value shown by segment and region; when would you use FIXED, INCLUDE, or EXCLUDE LOD expressions?
bitableaudashboards
33
A Tableau matrix shows monthly sales for 40 regions and needs running total plus rank within country; how would you configure table calculation addressing and partitioning?
configbitableau
34
A Tableau workbook scans 70 million events and a Top 20 Customer filter must apply only within the selected market; how would context filters change the result?
bitableau
35
A legacy SSAS Multidimensional cube has 600 million sales cells and a finance report needs a reusable Gross Margin calculated member; where would you define it in MDX?
daxcss
36
An SSAS Multidimensional cube serves 30 Excel reports and needs a rolling 12-month set that respects the selected calendar member; how would you express and constrain it in MDX?
excel
37
A dbt model feeds a Power BI dashboard from 250 million Snowflake events with a 45-minute refresh SLA; how would you design its incremental build?
bidbtpower-bi
38
A dbt incremental orders model feeds Looker, and 2 percent of 80 million rows arrive up to five days late; how would you choose the reprocessing window?
bidbtlooker
39
A dbt mart powers 18 Tableau workbooks and is expected to contain one row per customer per month; which data tests would you require before refresh?
bidbttableau
40
A dbt revenue mart serves 11 Power BI reports and source schema changes can add or remove columns weekly; how would you protect the semantic model contract?
bidbtpower-bi
41
A Power Query pipeline merges 35 million Orders rows with 4 million Customers rows from the same SQL Server database, but folding stops at a custom customer-segment function and refresh takes 96 minutes. How would you redesign it?
sqldatabasequeries
42
A Power BI incremental refresh policy retains five years of 180 million rows and refreshes the last seven days; how must Power Query folding use RangeStart and RangeEnd?
bipower-bipower-query
43
A Cloud Composer DAG must publish a Looker finance dashboard by 07:30 after three BigQuery sources and a dbt mart finish; how would you model the dependencies?
dependenciesbidbt
44
An Airflow pipeline refreshes a Power BI dataset with 22 tables by 06:00, but two dimensions can arrive 90 minutes after the fact; how would you handle dependency and late-data rules?
dependenciesbiairflow
45
You own a Power BI semantic model with a 140 million-row sales fact, eight dimensions, and 16 thin reports; how would you structure it for reuse?
bipower-bimodeling
46
A Power BI semantic model serves 4,000 employees across 12 regions and managers may see multiple regions; how would you design dynamic RLS?
designsemantic-layerbi
47
Three Power BI semantic models contain 6 million customer rows and use different customer tiers; how would you create shared dimensions for cross-domain dashboards?
bipower-bidashboards
48
A Power BI model exposes 110 measures to 25 report authors; what naming and style guide would you enforce?
bipower-bimeasures
49
A certified Power BI model feeds 30 self-service reports from 70 million rows; what should report authors be allowed to change, and what stays centrally owned?
bipower-bi
50
A Power BI semantic model supports finance and sales dashboards from a 200 million-row fact, and both teams want their own Product hierarchy; how would you set the boundary?
bipower-bidashboards
51
A Power BI sales page with 14 visuals takes 9.2 seconds to become interactive, while the target is under 4 seconds. How do you isolate the bottleneck?
bipower-bitracking
52
DAX Studio shows an 8.4-second measure with 7.1 seconds in the formula engine and 1.0 second in the storage engine on a 90-million-row model. What do you do next?
measuresdax
53
A 220-million-row Power BI model grew from 1.4 GB to 3.8 GB after a transaction ID and timestamp were added. How would you recover capacity without breaking drill-through?
bipower-bitransactions
54
One Power BI page contains 28 visuals and sends 64 queries whenever a slicer changes, producing a 12-second wait. How do you redesign it?
bipower-biqueries
55
A customer retention visual takes 6.7 seconds because a DISTINCTCOUNT measure scans 180 million fact rows for every month. How would you tune it in Power BI?
retentionmeasuresdax
56
A DirectQuery Power BI report has p95 visual latency of 18 seconds against Snowflake, but the same SQL completes in 5 seconds in the warehouse. How do you locate the missing 13 seconds?
sqlwarehousesnowflake
57
A composite Power BI model shows yesterday's imported revenue beside live DirectQuery orders, causing a 2.6 percent mismatch for the current day. How would you correct the model behavior?
bipower-bi
58
A DirectQuery executive dashboard scans 400 million rows per interaction even though executives only view daily totals by region. What Power BI design would you test?
bipower-bidesign
59
A Power BI dataset refresh succeeds in 22 minutes but peaks at 9 GB and evicts two other models from a 10 GB capacity. How do you reduce the footprint?
bipower-bicapacity
60
A release improves a Power BI page from 7.5 to 3.1 seconds on the developer's laptop, but the production target is p95 below 4 seconds for 300 users. What evidence do you require before deployment?
deploymentbipower-bi
61
A Tableau workbook with 36 sheets takes 14 seconds to open, while the team target is 5 seconds. How do you determine whether the issue is queries, calculations, or layout?
bitableauqueries
62
A 480-million-row Tableau extract takes 95 minutes to refresh and misses the 06:00 publishing window twice a week. What would you change?
bitableau
63
A Tableau live dashboard sends 18 Snowflake queries and consumes 22 credits during a 30-minute regional review. How do you tune it without losing the drill path?
queriessnowflakebi
64
A Looker subscription tile displays month-plan-region totals, but its generated SQL includes SubscriptionID and returns 3 million rows in 72 seconds. How would you fix the query path?
sqlqueriesbi
65
A Looker PDT over 2.1 billion BigQuery events rebuilds for 80 minutes nightly although only the latest two event dates can change. How would you make it incremental without missing corrections?
bilookerbigquery
66
Snowflake credits for BI rose from 110 to 180 per day after three new dashboards launched, but weekly active users increased only 5 percent. How do you find and contain the increase?
dashboardssnowflakezero-to-one
67
A BigQuery-backed dashboard bills 11 TiB on every hourly refresh although users only need the latest 30 days. What would you change?
dashboardsbigquery
68
A Looker dashboard repeats the same daily revenue aggregation across 12 tiles, generating 12 similar BigQuery jobs and 6 TB of billed data per load. How would you reduce it?
aggregationbigquerybi
69
At 08:00, 120 Tableau users arrive while two extract refreshes run, pushing p95 load time from 4 to 19 seconds. How do you stabilize the service?
bitableau
70
A Power BI incremental refresh of a 180-million-row SQL Server fact rises from 18 to 74 minutes after Merge Queries joins it to a local CSV lookup. How would you repair the query path?
sqlqueriesjoins
71
Power BI reports monthly recurring revenue 4.8 percent higher than Looker for the same 12,400 accounts. How do you lead the reconciliation?
bipower-bilooker
72
A Power BI RLS test shows 37 regional managers can see a national total card, although detail rows are restricted correctly. What is your response?
bipower-birow-level-security
73
A Power BI currency calculation group formats Margin Percent as dollars and converts YoY Percent after a new time item is added. How would you fix the interaction?
bipower-bicss
74
An HR Power BI model uses RLS to restrict managers to departments, but SalaryAmount must also be unavailable through Analyze in Excel for the manager role. Is hiding the field enough?
excelbipower-bi
75
UAT for a finance dashboard fails with 23 defects two days before month-end, including 5 KPI mismatches and 18 layout or filter issues. How do you recover?
defectsuatdashboards
76
An executive report says refreshed at 07:00, but source checks show the latest order is from 23:00 yesterday on 4 of the last 10 days. How do you fix the stale-report signal?
77
Weekly active users for a customer-success dashboard fell from 420 to 210 over 8 weeks while the team headcount stayed flat. How do you investigate adoption decline?
dashboardsdecision-making
78
A new semantic-model release changes historical conversion from 18.2 to 16.9 percent across 24 months, but the release note says only naming cleanup. What do you do?
79
After a customer-to-territory many-to-many relationship was added, one Power BI dashboard overstates revenue by 12 percent only when two regions are selected. How do you diagnose it?
bipower-bidashboards
80
Your domain has 3 stale reports, 2 contradictory executive KPIs, and 1 intermittent refresh failure before a 09:00 review. How do you prioritize the next 4 hours?
prioritization
81
Four stakeholder teams submit 27 requests for a new operations dashboard, but only 6 weeks are available. How do you run discovery?
dashboardscommunicationstakeholder-management
82
You inherit a customer-success BI domain with 45 reports, 17 unnamed owners, and 3 different refresh schedules. What does ownership look like in the first 30 days?
ownership
83
Sales, finance, and product use three definitions of net revenue that differ by 7 percent, and all want their version on a shared dashboard. How do you create a metric contract?
dashboardsmonitoring
84
Eighty self-service Looker users created 26 inconsistent revenue fields in 3 months. What guardrails do you add without shutting down exploration?
bilookerguardrails
85
In a 60-minute workshop, sales wants pipeline by created date while finance insists on expected-close date, producing a 15 percent quarterly difference. How do you resolve the conflict?
ci-cd
86
A VP asks for a 70-visual dashboard that reproduces a monthly 42-page PDF, but usage logs show only 9 pages are viewed by more than 5 people. What do you reject?
dashboards
87
A regional director asks you to disable RLS for 2 days so 14 managers can export a national customer list before planning. How do you respond?
row-level-security
88
Operations asks for 5-minute dashboard freshness, but the source lands every hour and the proposed change would add 600 dollars per day in warehouse cost. What should you reject or renegotiate?
dashboardswarehouse
89
Fifty-five Tableau web authors created 18 extracts with four revenue definitions, while the governed source must refresh by 07:00 and keep RLS-like entitlement filters. What self-service boundary would you establish?
bitableauboundaries
90
Your BI domain has capacity for 4 changes this quarter but receives 18 dashboard requests from 6 teams. How do you choose without becoming a ticket queue?
dashboardscapacitydata-structures
91
You must consolidate 32 marketing reports into at most 8 while preserving the workflows of 140 weekly users. How do you execute the migration?
migrations
92
Fourteen dashboards have fewer than 3 views each in 90 days but still run 48 scheduled refreshes per day. How do you sunset them safely?
dashboards
93
A LookML PR adds 18 fields and changes a join from many_to_one to many_to_many for an Explore used by 24 dashboards. What do you review before approval?
dashboardsjoinsbi
94
A dbt PR changes customer_month from one row per customer-month to customer-product-month and feeds 17 BI dashboards. How do you review the change?
dbtdashboards
95
A Power BI semantic model serves 11 reports and must move through development, test, and production with less than 15 minutes of disruption. How do you design the deployment pipeline?
deploymentbici-cd
96
A Tableau workbook release changes 9 calculations used across 6 dashboards. What regression suite do you require before publishing?
bitableauregression
97
A Looker revenue dashboard missed its 07:00 freshness SLO 6 times in a month, with a mean recovery time of 94 minutes. What monitoring and runbook do you add?
monitoringbirunbooks
98
Warehouse spend for one dashboard doubles from 35 to 70 dollars per day after a release, but no refresh fails. How do you monitor and respond?
dashboardsmonitoringwarehouse
99
Ten minutes after a semantic-model deployment, 8 of 20 Power BI report smoke tests fail and refresh errors rise to 30 percent. What do you do?
deploymentbipower-bi
100
A junior BI developer must add a new churn segment to a Looker dashboard used by 85 account managers within 5 days. How do you mentor them through the change?
bilookermentoring

Questions

1A Power BI sales model has 12 million order lines and customers can change region monthly; how would you model region history for a five-year revenue dashboard?

2A Snowflake customer SCD2 table has 8 million versions and corrections can arrive 14 days late; how would you keep a Looker retention dashboard historically correct?

3A Power BI order model has Order Date, Ship Date, and Delivery Date across 40 million rows; how would you support all three calendar views without ambiguous relationships?

4A Tableau customer dashboard covers 6 million purchases, and each customer can belong to up to five marketing segments at once; how would you model the segment relationship?

5A university Power BI model must report eligibility and attendance for 80,000 students at every course session, including eligible students with no attendance event; what fact design would you use?

6A Looker sales pipeline has 3 million opportunities and seven milestones from creation to payment; how would you model stage duration and current backlog?

7A Power BI inventory dashboard stores 15,000 products across 120 warehouses at daily grain for two years; how would you model stock and movement?

8A Power BI account model has 2 million accounts, each shared by up to four sales representatives with changing credit percentages; how would you prevent double-counted quota dashboards?

9A Tableau commerce dashboard receives 25 million order lines with order-level shipping fees and line-level discounts; what grain would you choose for its fact model?

10A finance Looker model needs daily cash movements from 90 million transactions and month-end balances for 400 accounts; would you use one fact table or two?

11A Power BI support model has 18 million tickets, 12 low-cardinality flags, and ticket numbers shown on drill-through pages; how would you place those attributes?

12A dbt model feeding a Tableau claims dashboard receives 5 million facts before some provider dimension rows arrive, with a 24-hour refresh SLA; how would you handle the unknown members?

13In a Power BI model with 30 million sales rows, why can CALCULATE inside SUMX return a different result from SUMX over a naked SUM expression?

14A Power BI margin dashboard has 60 million invoice lines; how would you write a CALCULATE measure for revenue from profitable products without filtering the full fact table row by row?

15A Power BI pricing report contains 12 million lines with different quantities and unit margins; which DAX iterator would you use for weighted margin per unit?

16A DAX measure in a 25-table Power BI model repeats the same prior-period expression four times; how would variables change its behavior and maintainability?

17A Power BI retail model uses a 4-4-5 fiscal calendar across six years; how would you implement year-over-year dashboard measures?

18A Power BI executive dashboard refreshes daily at 07:00 and the current month has only 18 loaded days; how would you make year-over-year MTD comparison fair?

19A Power BI logistics model has 45 million orders with active Order Date and inactive Ship Date relationships; how would you calculate shipped revenue by month?

20A Power BI model compares 20,000 campaign targets with 80 million sales rows but the target table is intentionally disconnected; how would you apply selected campaigns in DAX?

Locked questions