SQL and data questions
"What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?" INNER JOIN: rows matching in both tables. LEFT JOIN: all rows from the left table plus matched rows from the right — non-matching right rows are NULL. RIGHT JOIN: the opposite. FULL OUTER JOIN: all rows from both tables, NULLs where no match. For BI: LEFT JOINs are most common joining a fact table to a dimension — you want all facts regardless of whether the dimension key exists (data quality issues cause non-matches). "What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?" COUNT(*): all rows including NULLs. COUNT(column): non-NULL values. COUNT(DISTINCT column): unique non-NULL values. Counting orders vs counting unique customers are different business questions that look similar in SQL — always clarify the business question before writing the query.
Data modelling questions
"What is the difference between a star schema and a snowflake schema?" Star schema: central fact table with denormalised dimension tables — simple joins, fast queries, easy for end users. Preferred for BI tools and self-service analytics. Snowflake schema: dimension tables normalised into multiple related tables — more storage efficient, but joins are complex and queries slower. BI tools perform better with star schemas. "What is a slowly changing dimension (SCD)?" A dimension attribute that changes over time (customer address). Type 1: overwrite old value, no history. Type 2: new row with effective dates, full history (preferred for analytics). Type 3: new column for previous value, one prior state only.
Visualisation and Power BI questions
"How do you decide which chart type to use?" Driven by the question: trends — line chart; part-to-whole — bar (more readable than pie for many categories); comparison — bar; distribution — histogram or box plot; correlation — scatter; geographic — map. Best rule: ask what decision this should enable, then choose the simplest chart that answers it clearly. "What is DAX in Power BI?" Data Analysis Expressions — the formula language for calculated columns, measures, and tables in Power BI, SSAS, and Power Pivot. Used for time intelligence (YTD, rolling averages) and measures that aggregate differently depending on filter context (CALCULATE is the core DAX function for overriding filter context).
Stakeholder and requirements questions
"How do you translate a vague business request into a specific reporting requirement?" Start with the decision the stakeholder needs to make, not the data they think they need. Ask: "What decisions will you make differently with this information?", "What does success look like?", "Who else will use this and what will they ask?" Then map to data, metrics, and visualisations. "Tell me about a time a stakeholder disagreed with data you presented." Stay calm, investigate before defending (data quality issue? definition mismatch?), understand their reasoning, document the agreed definition for future consistency.