9 Deloitte Data Analyst Interview Questions & Answers

1. What is the difference between an inner join and an outer join, and when would an analyst use each?Sql And DatabasesEasyDeloitte

Question Details

Explain matching and unmatched rows, distinguish left/full outer behavior, and include a business-reporting example with a row-count validation check.

Short Interview Answer (30-60 seconds)

An INNER JOIN returns only rows where the join condition matches in both tables. A LEFT OUTER JOIN keeps every row from the left table and adds matching right-side data; when there is no match, the right-side columns are NULL. A FULL OUTER JOIN keeps matching rows plus unmatched rows from either table. I would use INNER JOIN when the report only needs records present in both datasets, LEFT JOIN when I must preserve the full left-side population, and FULL OUTER JOIN for reconciliation. I would also validate row counts because one-to-many joins can increase the number of output rows.

Detailed Explanation

See the Code while reading this explanation.

A join combines rows from two tables using a relationship such as customer_id. The key decision is which rows the report must keep when a match is missing. An INNER JOIN removes unmatched rows. A LEFT OUTER JOIN keeps every row from the left table and uses NULL for right-table columns when no match exists. A FULL OUTER JOIN keeps unmatched rows from both tables. Before writing SQL, I check each table's grain and relationship because one customer can have several orders, so one customer may produce multiple joined rows.

Useful Questions to Ask the Interviewer

Which table defines the population that must remain complete in the report?
Is the relationship one-to-one or one-to-many, and what should the output grain be?
Can the join key be NULL, duplicated, or missing from the other table?
Should unmatched records be excluded, shown with NULL values, or reported separately for reconciliation?
What row counts or trusted totals should I use to validate the result?

What is the difference between an inner join and an outer join, and when would an analyst use each? diagram

How to Explain It in an Interview

In the diagram, Customers contains one row per customer with customer_id as the primary key. Orders contains one row per order with order_id as the primary key and customer_id as the relationship back to Customers. The relationship is one customer to zero or many orders. That means a customer with two orders appears twice after a join at order grain.

With an INNER JOIN, only matching customer-order pairs remain. In the sample, customers 1, 3, and 4 have orders. Customer 1 has two orders, so the INNER JOIN returns four rows: two for customer 1, one for customer 3, and one for customer 4. Customers 2 and 5 disappear because they have no matching order. This fits a business report such as revenue from customers who actually placed orders.

A LEFT OUTER JOIN starts with the complete Customers population. Matching Orders rows are attached where they exist. Customers 2 and 5 still appear, but their order_id and amount values are NULL because there is no matching order. The result contains six rows: four matching order rows plus two unmatched customer rows. This is appropriate for a customer coverage or activity report because customers with zero orders must remain visible.

A FULL OUTER JOIN keeps matching rows plus unmatched rows from either side. In this sample, every Orders row has a matching customer, so the FULL OUTER JOIN also returns six rows. That is why the LEFT and FULL counts are the same in the diagram. If there were an order-side row with no matching customer, a FULL OUTER JOIN would retain it with NULL values for the customer columns, while the LEFT JOIN starting from Customers would not.

NULL handling is important. In an outer join, NULL values introduced on the non-matching side mean no matching row was found; they are not the same as zero or an empty string. For an equality join, a NULL join key does not compare equal to another value, including another NULL, because the comparison evaluates to UNKNOWN rather than TRUE. An INNER JOIN therefore does not match such rows. A LEFT JOIN still preserves an unmatched left row, and a FULL OUTER JOIN preserves unmatched rows from either side.

Filtering also matters. If I perform a LEFT JOIN and then put a condition such as o.amount > 100 in the WHERE clause, rows with no matching order have NULL for o.amount and are removed by that filter. If the business requirement is to keep every customer while limiting which Orders rows can match, I would put that condition in the ON clause instead.

Finally, I validate the output. The diagram's control counts are Customers = 5 rows, Orders = 4 rows, INNER JOIN = 4 rows, LEFT JOIN = 6 rows, and FULL OUTER JOIN = 6 rows. The LEFT JOIN has more rows than the five-row Customers table because customer 1 has two matching orders. I would also compare COUNT() with COUNT(DISTINCT customer_id), inspect unmatched rows, check for unexpected duplicate keys, and compare business totals with a trusted source before publishing the report.

Technical Approach

State the SQL dialect and identify the two tables, join columns, and keys.
Define the input grain: one row per customer in Customers and one row per order in Orders.
Identify the relationship as one customer to zero or many orders.
Decide which population must be preserved when a match is missing.
Use INNER JOIN when only matching rows are needed.
Use LEFT OUTER JOIN when every left-side row must remain.
Use FULL OUTER JOIN when unmatched rows from either side must remain.
Check NULL behavior and place filters so they do not accidentally remove required unmatched rows.
Validate COUNT(), distinct business keys, one-to-many row expansion, unmatched rows, and trusted business totals.

Time & Space Complexity

The main analytical risk is not the JOIN keyword itself but the number of rows it produces. In a one-to-many relationship, one left-side row can match several right-side rows, so the output may contain more rows than the left table. That can cause overcounting if an analyst later aggregates at the wrong grain. INNER JOIN can remove unmatched records that the business expected to see, while outer joins preserve more records and can produce a larger result. Performance depends on data size, indexes, statistics, join conditions, and the database optimizer, but correctness and expected grain should be established before performance tuning.

Example

Using the diagram's Customers and Orders tables, the following PostgreSQL-style SQL demonstrates INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN on customer_id. The row-count checks confirm the sample results shown in the diagram: 5 Customers rows, 4 Orders rows, 4 INNER JOIN rows, 6 LEFT JOIN rows, and 6 FULL OUTER JOIN rows. LEFT and FULL have the same count here because every Orders row has a matching customer.

Code

-- INNER JOIN: keep only matching customer/order rows
SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.amount
FROM
  customers AS c
  INNER JOIN orders AS o ON c.customer_id = o.customer_id;


-- LEFT OUTER JOIN: preserve every customer
SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.amount
FROM
  customers AS c
  LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id;


-- FULL OUTER JOIN: preserve unmatched rows from either table
SELECT
  c.customer_id,
  c.customer_name,
  o.order_id,
  o.amount
FROM
  customers AS c
  FULL OUTER JOIN orders AS o ON c.customer_id = o.customer_id;


-- Row-count validation for the sample
SELECT
  COUNT() AS customer_rows
FROM
  customers;


-- 5
SELECT
  COUNT() AS order_rows
FROM
  orders;


-- 4
SELECT
  COUNT() AS inner_rows
FROM
  customers AS c
  INNER JOIN orders AS o ON c.customer_id = o.customer_id;


-- 4
SELECT
  COUNT() AS left_rows
FROM
  customers AS c
  LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id;


-- 6
SELECT
  COUNT() AS full_rows
FROM
  customers AS c
  FULL OUTER JOIN orders AS o ON c.customer_id = o.customer_id;


-- 6

Where it is used

INNER JOIN is common when a report should include only entities with matching activity, such as calculating revenue from customers who placed orders. LEFT OUTER JOIN is common for population-based reporting, such as listing every customer and showing order activity when available, including customers with zero orders. FULL OUTER JOIN is useful for reconciliation, migration checks, and completeness testing when an analyst needs to find records present in either dataset but missing from the other.

Why Interviewers Ask This

Interviewers want to see whether the candidate understands matching versus unmatched rows, which side of an outer join is preserved, row grain, NULL behavior, and one-to-many row multiplication. They also want to know whether the candidate chooses a join based on the reporting population and validates the output instead of relying only on correct SQL syntax.

Common interview mistakes

Common mistakes are assuming one joined row always equals one customer, forgetting that a one-to-many relationship can multiply rows, using INNER JOIN when customers with no activity must remain visible, reversing LEFT JOIN behavior, treating NULL as zero, assuming NULL join keys match each other, placing a right-table condition in WHERE and accidentally removing unmatched LEFT JOIN rows, and checking only SQL syntax without validating row counts, distinct keys, or trusted business totals.

Interview tip

State the grain first, then say which population must be preserved. A concise explanation is: INNER keeps matches, LEFT keeps all rows from the left plus matches from the right, and FULL keeps unmatched rows from both sides. Finish with a business example and a row-count check, especially noting that one-to-many joins can increase the number of output rows.

Interviewer may ask next

Why can a LEFT JOIN return more rows than the left table contains?

Because preserving every left-side row does not mean producing exactly one output row per left-side row. If one left-side row matches several right-side rows, SQL returns one joined row for each match. In the diagram, customer 1 has two Orders rows, so customer 1 appears twice. The five Customers rows therefore become six LEFT JOIN rows after combining four matching order rows with two unmatched customers. This is why an analyst should compare COUNT() with the distinct business key at the intended reporting grain.

How can a filter accidentally make a LEFT JOIN behave like an INNER JOIN?

After a LEFT JOIN, unmatched left rows contain NULL in the right-side columns. If the query then applies a WHERE condition such as WHERE o.amount > 100, those NULL rows do not satisfy the condition and are removed. If the requirement is to keep every customer while restricting which Orders rows can match, place the condition in the ON clause instead, for example LEFT JOIN orders AS o ON c.customer_id = o.customer_id AND o.amount > 100. The exact placement should follow the intended business logic.

2. Write SQL to select the second-highest salary in the engineering department.Sql And DatabasesEasyDeloitte

Question Details

State how duplicate salaries should be handled and show a window-function or equivalent solution.

Short Interview Answer (30-60 seconds)

Using standard SQL window-function syntax, I would filter the employees table to Engineering, exclude NULL salaries, rank salaries from highest to lowest with DENSE_RANK(), and return the distinct salary with rank 2. DENSE_RANK() is the right choice because equal salaries receive the same rank, so the answer is the second-highest distinct salary rather than the second employee row. If Engineering has fewer than two distinct non-NULL salaries, the query returns no rows.

Detailed Explanation

The task is to return the second-highest distinct salary among employees in the Engineering department. Using standard SQL window-function syntax, first filter the employee rows to Engineering and exclude NULL salaries. Then rank the remaining salaries from highest to lowest with DENSE_RANK(). Equal salaries receive the same rank, so duplicates do not change which salary is second-highest. Finally, return the distinct salary whose rank is 2. In the approved diagram, the input grain is one row per employee, and the output grain is one row containing the second-highest distinct salary when that value exists.

Useful Questions to Ask the Interviewer

Should "second-highest" mean the second-highest distinct salary when multiple employees have the same salary?
Should rows with a NULL salary be excluded from the ranking?
Is the source table employees with employee_id, department, and salary, as shown in the diagram, or should I adapt the query to another schema?
If there is no second-highest distinct salary, should the query return no rows or one row containing NULL?

Write SQL to select the second-highest salary in the engineering department. diagram

How to Explain It in an Interview

The diagram shows an employees table at one row per employee, with employee_id as the primary key. No join is needed because both the department and salary are already available in that table.

The query plan follows the same flow as the diagram:

Filter to department = 'Engineering'.
Exclude rows where salary IS NULL.
Rank the remaining salaries in descending order with DENSE_RANK().
Return the distinct salary where salary_rank = 2.

A window function calculates a value across related rows without collapsing the rows first. Here, DENSE_RANK() is useful because employees with the same salary are peers and receive the same rank, while the next distinct salary receives the next consecutive rank.

WITH ranked AS (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM employees
    WHERE department = 'Engineering'
      AND salary IS NOT NULL
)
SELECT DISTINCT salary
FROM ranked
WHERE salary_rank = 2;

Duplicate handling is the key point. In the approved diagram, two Engineering employees have salary 120000, so both receive rank 1. Salary 110000 receives rank 2, and salary 95000 receives rank 3. Therefore, the displayed result is 110000. SELECT DISTINCT makes the output one salary value even if several employees share the second-highest salary.

ROW_NUMBER() would not be appropriate for this interpretation because it numbers individual rows, so two employees tied at the highest salary could occupy row numbers 1 and 2. RANK() gives equal values the same rank but leaves gaps after ties, so rank 2 is not guaranteed to represent the second distinct salary. DENSE_RANK() directly matches the requirement.

For validation, check that the department filter is correct, NULL salaries are excluded, equal salaries receive the same rank, and the output matches the second value from the sorted distinct Engineering salaries. If fewer than two distinct non-NULL Engineering salaries exist, no row receives rank 2 and the query returns no rows.

Technical Approach

Read the employees table at one row per employee.
Filter to department = 'Engineering'.
Exclude salary IS NULL.
Apply DENSE_RANK() OVER (ORDER BY salary DESC) so equal salaries share a rank.
Filter to salary_rank = 2.
Use SELECT DISTINCT salary so the output contains the second-highest distinct salary once.
Validate against the descending list of distinct non-NULL Engineering salaries and check the fewer-than-two-values edge case.

Practical Complexity & Trade-offs

The database must filter the qualifying Engineering rows and order their salary values to compute the rank. The ordering step is typically the main cost and is commonly about O(n log n) for n qualifying rows, although the actual execution plan depends on the database engine, statistics, table size, and indexes. The final rank filter is comparatively inexpensive. Performance considerations do not change the correctness of the query.

Where it is used

This pattern is useful when analysts need the nth-highest distinct value within a filtered group, such as the second-highest salary in a department or another ranked business metric where duplicate values should share the same position.

Why Interviewers Ask This

The interviewer is checking whether you can translate a simple ranking requirement into correct SQL while handling department filtering, duplicate salary values, NULLs, window functions, and the expected output grain.

Common interview mistakes

Common mistakes are using ROW_NUMBER() when duplicate salaries should share a rank, using RANK() without accounting for rank gaps after ties, forgetting the Engineering filter, allowing NULL salaries into the ranking, returning duplicate rank-2 rows without DISTINCT, or assuming that a second distinct salary always exists.

Interview tip

State the interpretation first: "I am treating second-highest as the second-highest distinct salary." Then explain the input grain, filter Engineering and NULL salaries, use DENSE_RANK(), select rank 2, and mention the no-second-value edge case.

Interviewer may ask next

What happens if every Engineering employee has the same non-NULL salary?

There is only one distinct salary, so every qualifying row receives salary_rank = 1. No row receives rank 2, and this query returns no rows. If the required output contract is one row containing NULL instead, I would change the outer query to return a scalar or aggregate result that converts the missing value to NULL.

Why use `DENSE_RANK()` instead of `RANK()` or `ROW_NUMBER()`?

DENSE_RANK() matches the requirement for the second-highest distinct salary because equal salaries share the same rank and the next distinct salary receives the next consecutive rank. ROW_NUMBER() ranks individual employee rows, so duplicate top salaries could consume positions 1 and 2. RANK() also gives ties the same rank, but it leaves gaps after ties, so filtering for rank 2 is not guaranteed to return the second distinct salary.

3. How do you do data modelling?Business Intelligence ToolsMediumDeloitte

Short Interview Answer (30-60 seconds)

I start with the business question and trusted data sources, then clean and transform the data before modelling. I define the fact-table grain first, build a star schema with descriptive dimensions around the fact table, and create reusable measures in the semantic model rather than repeating logic in reports. I check how dimension filters propagate to facts, apply row-level security when access differs, choose the right refresh or connection approach based on freshness and performance needs, reconcile totals to a trusted source, test report performance, and publish with clear ownership and lineage.

Detailed Explanation

Data modelling is the process of organizing source data so reports can answer business questions consistently and safely. I start by understanding the question and the trusted data sources, then clean and transform the data before modelling. Next, I define the fact-table grain and build a star schema with descriptive dimensions around the fact table. I create reusable measures in the semantic model, confirm how filters affect them, apply row-level security when access differs, choose an appropriate refresh approach, validate totals against trusted sources, test performance, and publish with clear ownership.

Useful Questions to Ask the Interviewer

What business questions or decisions must the model support?
Which source systems are authoritative, and what is their expected latency or refresh frequency?
What should one row in the main fact table represent?
Which descriptive attributes should users filter or group by?
Which business metrics need one governed definition across reports?
Do different users or roles need different row-level access?
How fresh must the data be, and what performance expectations exist for the reports?

How to Explain It in an Interview

I would explain data modelling as one end-to-end workflow from source data to a published report.

Start with the business question and trusted data sources. I first clarify the decision the report must support and identify the authoritative sources, such as operational databases, files, cloud applications, or other source systems. I also check source ownership, schema, and latency so the model does not promise fresher data than the source can provide.

Transform and clean the data before modelling. In a BI workflow, I use ETL or Power Query to extract data, standardize data types, handle missing values, remove inappropriate duplicates, and create consistent keys. I keep source transformation separate from semantic calculations so the model stays easier to validate and maintain.

Define the fact-table grain before relationships. Grain means what one row represents. In the diagram, the example grain is one sales transaction line, meaning one product sold to a customer on a date. I define that first because incorrect or mixed grain is one of the fastest ways to create wrong totals.

Design the model as a star schema when that fits the reporting need. A star schema places a central fact table with numeric events in the middle and descriptive dimensions around it. In the diagram, Fact Sales is surrounded by Dim Date, Dim Product, and Dim Customer. The dimension side should have unique keys, and the fact table can have many rows for each dimension key. That supports clear one-to-many relationships and predictable filtering.

Separate dimensions from measures. Dimensions hold descriptive fields used for filtering or grouping, while measures are reusable calculations. I avoid storing business logic separately in every report. Instead, I define governed measures in the semantic model so all reports use the same metric logic.

Create reusable semantic measures and respect filter context. In the diagram, the example DAX measure is Total Sales = SUM(Fact Sales[SalesAmount]). A measure is calculated at query time in the current filter context, so filters from dimensions such as date, product, or customer narrow the related fact rows before the measure is evaluated. I only use calculated columns or fields when I truly need a stored row-level result.

Apply row-level security when access differs. If different users should see different slices of the data, I apply row-level security at the identity boundary of the semantic model and test it using representative roles. I do not rely on hiding visuals as a substitute for data security.

Choose refresh and connection behavior deliberately. If I import data, I set a refresh schedule that matches the business need and the source refresh frequency. I also make failure visibility part of the design so missed refreshes are not silent. If freshness is more important, I may evaluate DirectQuery or a live connection, but I explain that these choices create stronger dependence on source availability, latency, and performance.

Validate the model before publishing. I reconcile totals to a trusted source, test important calculations, inspect row counts, and check for null keys, duplicate dimension keys, missing periods, or incorrect relationship behavior. I also verify that dimension filters propagate correctly to the fact table.

Check performance, publish, and assign ownership. Before publishing, I test dashboard performance because a technically correct model can still be slow. Then I publish to the reporting workspace, assign access permissions, and document ownership and lineage so the reported number can be traced back to the source and the transformation steps.

Iterate based on stakeholder feedback. After publishing, I monitor usage, data quality, refresh reliability, latency, and stakeholder feedback. If business questions change, I revise the model instead of adding inconsistent report-level workarounds.

The main tradeoff is between simplicity, freshness, and performance. A clean star schema with governed measures is usually easier to understand, validate, and maintain. More direct or live access can improve freshness, but it can also increase dependence on the source system and may reduce report performance.

Technical Approach

Clarify the business question, trusted sources, required outputs, access rules, and freshness needs.
Extract, clean, and transform the source data into model-ready tables.
Define the grain of the fact table before creating relationships.
Separate numeric facts from descriptive dimensions and build a star schema where appropriate.
Create one-to-many relationships and verify filter propagation from dimensions to facts.
Add reusable semantic measures and distinguish them from calculated columns or fields.
Apply row-level security when different users need different row access.
Choose import, DirectQuery, or a live connection based on freshness, latency, performance, and governance needs.
Reconcile totals and key calculations to a trusted source and test edge cases.
Check dashboard performance, publish with permissions and ownership, and monitor refresh reliability, latency, and stakeholder feedback.

Practical Insights

The main cost comes from data volume, number of relationships, measure complexity, source speed, and refresh frequency. A clean star schema is usually easier to query, explain, and maintain than a model with ambiguous joins or mixed grain. Import often gives faster report performance but needs scheduled refreshes and storage. DirectQuery and live connection can improve freshness, but report speed then depends more on the source system and network latency. Extra security rules, high-cardinality fields, and unnecessary model columns can also make the model slower or harder to maintain.

Why Interviewers Ask This

Interviewers want to see whether you can turn raw source data into a reliable analytical model: define the right grain, separate facts from dimensions, create reusable measures, manage filters and access, choose a refresh approach, validate results, and publish a model that performs well and can be owned and maintained.

Common interview mistakes

Common mistakes are starting with relationships before defining grain, mixing facts and descriptive dimensions in one table without a reason, allowing duplicate keys on the dimension side, creating inconsistent joins, storing metric logic separately in many reports, confusing measures with calculated columns, ignoring filter context, skipping refresh-latency checks, using visual hiding instead of row-level security, failing to reconcile totals to a trusted source, and publishing without testing dashboard performance, permissions, ownership, or lineage.

Interview tip

Explain the answer as one clear flow: business question and trusted data sources, transformation, fact grain, star schema, reusable measures, filter context, row-level security, refresh strategy, validation, performance, and publishing. State the grain early and give one concrete validation example to show you can model data correctly and operationalize it.

Interviewer may ask next

What would you check if your model totals do not match the trusted source after publishing?

I would trace the issue step by step. First, I would confirm that both sides use the same time window, filters, units, and business definition. Then I would compare source row counts with transformed row counts, inspect joins for accidental duplication, check for null or unmatched keys, confirm dimension keys are unique, and verify the fact-table grain. After that, I would test the affected measure in the same filter context and confirm that relationships propagate filters as expected. The goal is to isolate the first stage where the numbers stop matching.

How would your modelling approach change if the business wants fresher data but the source system is slow?

I would first clarify the actual freshness requirement and the acceptable report response time. If scheduled import is too stale, I would evaluate DirectQuery or a live connection, but I would explain that those approaches make the report more dependent on source performance and latency. I would keep the model simple, reduce unnecessary columns and expensive logic, and test representative queries. If the source still cannot support the requirement, I would discuss a governed intermediate layer, aggregations, or a revised freshness expectation rather than promising near-real-time reporting that the source cannot reliably deliver.

4. What is the difference between Power BI Desktop and Power BI Service?Business Intelligence ToolsEasyDeloitte

Question Details

Compare report development, semantic models, publishing, refresh, sharing, collaboration, security, and deployment responsibilities.

Short Interview Answer (30-60 seconds)

Power BI Desktop is mainly the Windows authoring tool. I use it to connect to data, transform data with Power Query, build the semantic model, create DAX measures, define relationships and row-level security roles, design report pages, test performance, and save the work as a .pbix file. I then publish the report and semantic model to Power BI Service. The Service is the cloud environment where teams host content in workspaces, configure refresh, manage access, apply security for users, share reports or apps, collaborate, and support deployment across environments. In short: build in Desktop; operate and distribute in the Service.

Detailed Explanation

Power BI Desktop and Power BI Service are two parts of the same reporting workflow, but they serve different responsibilities. Desktop is the main Windows authoring environment, where analysts connect to data, transform it with Power Query, build the semantic model, create DAX measures, define relationships and row-level security roles, and design report pages. After testing the model and report, they publish the report and semantic model to Power BI Service. The Service is the cloud environment for hosting content, refreshing imported data, managing access, enforcing security, sharing reports, collaborating, and supporting deployment.

Useful Questions to Ask the Interviewer

Are you asking mainly about the analyst development workflow, or should I also cover governance and deployment responsibilities?
Is the semantic model using imported data, DirectQuery, or a live connection? That changes how refresh and source latency should be discussed.
Does the organization use separate development, test, and production workspaces or deployment pipelines?
Do different users need different data access through row-level security?

What is the difference between Power BI Desktop and Power BI Service? diagram

How to Explain It in an Interview

Think of the workflow as data source -> Desktop -> publish -> Service -> consumers.

1. Power BI Desktop: build and model

Power BI Desktop is the main authoring environment shown in the diagram. An analyst connects to databases, files, cloud sources, or on-premises sources. Power Query is used to clean and reshape source data before it enters the semantic model.

Inside the semantic model, I define the grain of each table before creating relationships. A typical analytical model may use a star schema, where descriptive dimension tables filter a fact table. I keep descriptive attributes separate from reusable measures. DAX measures calculate business metrics in filter context, while calculated columns store row-level values and should not be treated as interchangeable with measures.

Desktop is also where I create report pages, visuals, filters, and interactions. I can define row-level security roles in the model and test them before publishing. Before release, I would validate totals against a trusted source, check relationship behavior and filter context, and review report performance.

Desktop normally saves the work in a .pbix file. Its core responsibility is authoring: data preparation, semantic modeling, calculations, report design, local validation, and role definition.

2. Publish from Desktop to the Service

Publishing sends the report and its semantic model to a Power BI Service workspace. The workspace becomes the managed cloud location for the published content.

This boundary matters because authoring and operations are different responsibilities. The model is primarily built in Desktop, while the published semantic model has operational settings and consumers in the Service. Changes to the model or report can then be republished as part of the team's governed release process.

3. Power BI Service: operate and distribute

Power BI Service is the cloud environment used to host and operate published analytics. Teams use workspaces to organize reports and semantic models and control who can work with or consume them.

For imported semantic models, the Service is where manual or scheduled refresh is normally configured. If an on-premises source is involved, a data gateway may be required so the cloud service can reach that source. Refresh frequency should match how quickly the business needs new data while considering source latency, gateway availability, refresh duration, and failures. A successful refresh does not prove the source itself was current, so source freshness and refresh status are separate checks.

For DirectQuery or live-connection scenarios, the data-access behavior differs because the report may query an external source or semantic model instead of relying on a periodically refreshed imported copy. That changes the performance, latency, and refresh responsibilities.

4. Sharing and collaboration

The Service handles the main distribution workflow. Reports can be shared with authorized users, distributed through apps, and organized in team workspaces. Collaboration can include workspace-based co-development, comments, subscriptions, and alerts where applicable.

A major benefit is reuse. Multiple reports can use a governed shared semantic model instead of rebuilding the same metric logic repeatedly. This helps keep definitions consistent, but ownership and change control become more important because a model change can affect multiple downstream reports.

5. Security

Security exists at more than one layer. Workspace roles, sharing permissions, app access, and related permissions determine who can reach content. Row-level security controls which rows an authorized viewer can see when different users require different data access.

The model roles can be defined and tested during development in Desktop, while user or group membership and operational enforcement are handled in the Service. I would test security using representative identities because a technically correct role definition can still produce the wrong result if membership or permissions are configured incorrectly.

6. Deployment responsibilities

Desktop is the authoring environment; the Service is the operational environment for published content. For governed delivery, teams can separate development, test, and production content in Service workspaces and can use deployment pipelines when that capability fits their environment.

The key distinction is that Desktop itself is not the production hosting environment. It creates and validates the analytical content. Power BI Service hosts that content for consumers and manages cloud operations such as refresh, access control, sharing, collaboration, and deployment.

7. Practical model checks

Before publishing, I would check the data grain, relationships, DAX measures, filter behavior, totals, row-level security logic, and report performance. After publishing, I would check refresh status, gateway connectivity when required, permissions, security membership, report usability, and whether users are seeing sufficiently current data.

The simplest interview summary is: Desktop is the full authoring environment for data preparation, semantic modeling, calculations, and report design. Power BI Service is the operational home for published reports and semantic models, refresh, access control, sharing, collaboration, and deployment.

Technical Approach

Identify the boundary: Desktop is primarily for authoring; Service is primarily for cloud operations and distribution.
Trace the workflow from data sources into Desktop, through Power Query and the semantic model, into report development.
Validate grain, relationships, measures, security roles, totals, and report performance before publishing.
Publish the report and semantic model to a Service workspace.
In the Service, configure the appropriate refresh or connection behavior, including a gateway when required for supported on-premises scenarios.
Configure workspace access, sharing, apps, and row-level security membership as needed.
Support collaboration and reusable semantic models.
Use governed workspaces and deployment processes for development, test, and production when required.
Monitor refresh failures, source latency, permissions, performance, and downstream impact after deployment.

Practical Insights

There is no algorithmic Big-O complexity here. The practical costs are model size, source-query latency, refresh duration, gateway reliability, report rendering time, and governance overhead. Import mode can provide fast interactive reports but requires storing and refreshing data. DirectQuery can reduce the need to import data but makes report performance more dependent on the source and network. Reusing one governed semantic model can reduce duplicated metric logic, but changes must be controlled because several reports may depend on it.

Why Interviewers Ask This

The interviewer is checking whether you understand where Power BI development work happens versus where published analytics are operated, refreshed, secured, shared, collaborated on, and deployed. A strong answer distinguishes Desktop as the main authoring environment from the Service as the cloud environment for distribution and ongoing operations, while correctly explaining how semantic models, refresh, row-level security, workspaces, and deployment responsibilities cross that boundary.

Common interview mistakes

Common mistakes include saying that Desktop and Service are interchangeable; saying scheduled refresh is mainly a Desktop responsibility; treating measures and calculated columns as the same thing; assuming publishing only uploads visuals and not the associated semantic model; forgetting that on-premises refresh can require a gateway; saying row-level security is only a Desktop feature or only a Service feature instead of explaining the development-versus-enforcement responsibilities; calling Desktop the development/test deployment environment; ignoring DirectQuery or live-connection behavior when discussing refresh; and sharing reports without validating permissions, security, refresh status, totals, or performance.

Interview tip

Give the answer as one lifecycle: build and validate in Desktop, publish the report and semantic model, then operate, refresh, secure, share, collaborate, and deploy in the Service. Mention one important nuance, such as imported models using scheduled refresh while DirectQuery or live connections have different latency and refresh behavior.

Interviewer may ask next

If users in different regions should see different rows, where would you configure and validate that security?

I would define the row-level security logic and roles in the semantic model during development and test the role behavior before publishing. After publishing, I would manage the appropriate user or group membership and access in Power BI Service and validate the result using representative identities. I would also distinguish RLS from workspace permissions: workspace or sharing access controls whether someone can reach the content, while RLS controls which rows an authorized viewer can see.

How would your answer change if the semantic model used DirectQuery instead of imported data?

The Desktop-versus-Service responsibility split is still similar, but the data-access tradeoff changes. With an imported model, data is copied into the model and the Service commonly refreshes that imported data on a schedule. With DirectQuery, report interactions query the underlying source rather than relying only on a periodically imported copy, so freshness can be closer to the source but performance and availability depend more directly on the source, network, query design, and capacity. I would therefore focus less on import-refresh frequency and more on source latency, query performance, availability, security, and connection behavior.

5. How would you diagnose and optimize a slow Power BI report?Business Intelligence ToolsHardDeloitte

Question Details

Cover model shape, relationship direction, DAX measures, visual query load, cardinality, Import versus DirectQuery, source-query performance, and validation after tuning.

Short Interview Answer (30-60 seconds)

I would start with Performance Analyzer to identify which visuals and DAX queries are actually slow. Then I would inspect the semantic model: confirm the fact-table grain, favor a star schema, use clear one-to-many relationships with single-direction filtering from dimensions to the fact table where appropriate, and reduce unnecessary high-cardinality columns. Next I would simplify reusable DAX measures and reduce unnecessary visual queries or interactions. I would then review Import versus DirectQuery and, for DirectQuery, optimize query folding and the source. Finally, I would rerun the same scenarios, compare timings, validate totals and filters against a trusted source, confirm access behavior is unchanged, and iterate on the next bottleneck.

Detailed Explanation

The task is to find where a Power BI report is spending time, change the correct layer, and prove that the report is both faster and still correct. I would not immediately rewrite DAX or change storage mode. I would first measure the report, then follow a checkable path: baseline performance, model shape, DAX and visual load, storage and source performance, and validation. This separates symptoms from causes and prevents a performance change from accidentally changing totals, filter behavior, data freshness, or user access. Each tuning step should be measured before moving to the next bottleneck.

Useful Questions to Ask the Interviewer

Is the model using Import, DirectQuery, or a combination of storage modes?
Which pages, visuals, slicer interactions, or user actions are slow?
Is the delay mainly during report interaction, data refresh, or both?
What data-freshness or latency requirement affects the Import-versus-DirectQuery decision?
What is the fact-table grain, and which columns have high cardinality?
Are any many-to-many or bidirectional relationships required by the business logic?
For DirectQuery, what source system is involved, and can its queries, views, or indexes be tuned?
What trusted source or control totals can I use to verify the report after tuning?
Does row-level security or other access logic differ by user and need to remain unchanged?

How would you diagnose and optimize a slow Power BI report? diagram

How to Explain It in an Interview

I would diagnose the report from the user-facing symptom back toward the data source, changing one major layer at a time and retesting after each change.

First, I establish a baseline with Power BI Performance Analyzer. I reproduce the slow user interaction, such as opening a page or changing a slicer, and identify which visuals take the most time. Performance Analyzer helps separate the visual workload and exposes DAX-query timing, so I can focus on measured bottlenecks instead of guessing. I test one important interaction at a time so I know which action caused each result.

Second, I inspect the semantic model. A star schema organizes descriptive dimension tables around a central fact table that stores business events or measurements. Before changing relationships, I confirm the fact-table grain because I need to know what one fact row represents. I prefer clear one-to-many relationships and, where the business logic allows it, single-direction filtering from the one-side dimension to the many-side fact table. I avoid unnecessary many-to-many or bidirectional filtering because it can create more complex filter paths and make both performance and correctness harder to reason about.

I also check cardinality. Columns with many distinct values can increase model size and query work, especially when they are not needed for reporting. I remove unused columns and reduce unnecessary high-cardinality attributes where practical, but I do not change identifiers or business grain merely to make the model smaller.

Third, I review DAX and visual query load. Measures are calculations evaluated in filter context, so reusable business calculations should generally be expressed as clear measures instead of duplicating the same logic across the report. I look for unnecessarily expensive iterators, repeated calculations, avoidable row-by-row work, and filter logic that does more work than the visual needs. I keep descriptive attributes in dimensions rather than creating unnecessary calculated fields.

I then inspect the report page itself. Every visual, slicer, total, and interaction can add query work. I remove unnecessary visuals, totals, fields, or interactions and avoid high-cardinality slicers when they are not required. The goal is not simply to show less information; it is to eliminate work that does not contribute to the user's decision.

Fourth, I examine storage mode and source performance. Import loads data into Power BI's in-memory model, which normally gives fast interactive performance, but the data is only as current as the latest successful refresh. DirectQuery keeps data at the source and can send source queries during report interactions. That can support fresher-data or architecture requirements, but report speed then depends much more on source-query performance and source latency.

If freshness requirements permit, Import may provide faster interaction. If DirectQuery is required, I optimize the full source path. I preserve query folding where supported so suitable transformations can be translated into operations performed by the source. I push appropriate transformations toward the source, filter early where practical, retrieve only needed data, and tune the source with efficient SQL, views, and indexes when the underlying platform supports them. I also keep source latency visible because a well-written report cannot completely hide a slow data source.

I would not switch storage mode automatically. The decision depends on freshness requirements, source capabilities, data volume, governance, refresh constraints, and operating needs. A faster Import report is not a valid solution if the required data freshness cannot be met by its refresh process. Likewise, DirectQuery is not automatically better simply because it queries the source more directly; the source must be able to serve the interactive workload efficiently.

Finally, I validate every tuning change. I rerun the same Performance Analyzer scenarios and compare before-and-after durations under comparable conditions. I verify totals and filters against a trusted source or control totals and retest important slicer paths so changes to relationships or DAX have not altered semantics. If access differs by user, I confirm row-level security or equivalent access behavior still produces the intended result. Only after performance and correctness both pass would I publish the tuned report.

The process is iterative. After validation, I use the new measurements to identify the next largest bottleneck and repeat the loop. That keeps every optimization tied to evidence and prevents speculative tuning.

Technical Approach

Reproduce the slow page or interaction and capture a baseline with Performance Analyzer.
Identify the slowest visuals and inspect their DAX-query and visual workload.
Inspect the semantic model: confirm fact grain, star-schema shape, relationship cardinality, and filter direction.
Remove unused model elements and reduce unnecessary high-cardinality columns without changing business grain.
Review reusable DAX measures for repeated logic, unnecessary iterators, expensive row-by-row work, and avoidable filter-context complexity.
Reduce unnecessary visuals, totals, fields, slicers, and interactions that generate extra query work.
Confirm whether Import or DirectQuery is appropriate for the required freshness, governance, and source constraints.
For DirectQuery, preserve query folding where supported and optimize source transformations, SQL, views, indexes, and latency.
Rerun the same performance scenarios and compare before-and-after timings.
Validate totals, filters, business meaning, and access behavior against trusted controls, then iterate on the next measured bottleneck.

Practical Insights

There is no single Big-O complexity for tuning a Power BI report because the cost is spread across the semantic model, DAX calculations, visuals, storage mode, and data source. Import mode uses memory and refresh work to make interactive querying fast. DirectQuery avoids importing all data but can make each interaction depend on source-query latency and source capacity. High-cardinality columns consume more model resources, while complex DAX and many visuals increase calculation and query work. The practical objective is to reduce the data scanned, calculations executed, queries generated, and source work required to produce the same correct result.

Why Interviewers Ask This

This tests whether the candidate can diagnose Power BI performance systematically instead of guessing. A strong answer separates visual workload, DAX calculations, semantic-model design, storage mode, and source-query performance; understands grain, relationship direction, and cardinality; and validates that tuning improves speed without changing business results, filter behavior, or required access behavior.

Common interview mistakes

Common mistakes are optimizing before measuring; blaming DAX when the model or source is the bottleneck; leaving unnecessary many-to-many or bidirectional relationships; using high-cardinality fields in slicers without need; repeating calculations instead of using governed reusable measures; placing too many visuals and interactions on one page; switching from DirectQuery to Import without checking freshness requirements; assuming every Power Query transformation folds to the source; tuning the source without testing the actual report-generated workload; and declaring success because a page feels faster without validating totals, filters, access behavior, and comparable before-and-after timings.

Interview tip

Present the answer as one measured loop: baseline, model shape, DAX and visual load, storage and source, validation, then retest. Explicitly explain the Import-versus-DirectQuery tradeoff and finish by saying that a speed improvement is not successful unless the report still returns the same trusted results and preserves required access behavior.

Interviewer may ask next

What if Performance Analyzer shows one visual is still slow after you simplify its DAX measure?

I would stop treating DAX as the only possible cause and isolate the remaining layer. I would test the visual with simpler fields, inspect its relationship paths and cardinality, and check whether a high-cardinality slicer, grouping, or unnecessary interaction is increasing query work. If the model uses DirectQuery, I would examine the source query and its execution at the data source. If the visual requests more detail than the business question needs, I would reduce unnecessary fields or detail while preserving the required result. Then I would rerun the same interaction and verify that both timing and output are correct.

How would your approach change if the business requires near-real-time data and you cannot switch from DirectQuery to Import?

I would keep DirectQuery and focus more heavily on the complete source path. I would simplify the model and DAX, reduce unnecessary visual queries, preserve query folding where supported, push suitable transformations toward the source, and tune the source with appropriate SQL, views, and indexes. I would also measure source latency because each interactive query can depend on that system. The tradeoff is that the freshness requirement is preserved, but report responsiveness becomes more dependent on source efficiency and capacity, so validation should include realistic user interactions under representative source conditions.

6. What is the difference between a calculated column and a measure in Power BI?Business Intelligence ToolsMediumDeloitte

Question Details

Explain evaluation timing, row context, filter context, storage, performance, and when each is appropriate.

Short Interview Answer (30-60 seconds)

A calculated column is evaluated for each row and its result is stored in the model. In an Import model, it is normally computed during refresh using row context, so its stored value does not change when a user changes a slicer. A measure is evaluated on demand using the current filter context, so it can return different results for different slicers, filters, and visual groupings. I use calculated columns for persistent row-level attributes and measures for aggregations, ratios, and KPIs that should respond dynamically to the report.

Detailed Explanation

A calculated column adds a stored value to every row of a table, while a measure calculates a result when Power BI needs it for a visual. In the diagram, the calculated column creates Margin for each Sales row from Revenue minus Cost. The measure calculates Total Margin across the rows visible in the current report context. This distinction matters because calculated columns consume model storage and are tied to refresh, while measures shift calculation work to query time and respond to slicers and filters. The right choice depends on whether you need a persistent row attribute or a dynamic result.

Useful Questions to Ask the Interviewer

Is the value needed as a row-level attribute for grouping, filtering, sorting, or a relationship, or only as an aggregate shown in visuals?
Is the semantic model using Import mode, and how often is it refreshed?
Should the result change when users apply slicers, report filters, or visual-level groupings?
Is model size, refresh duration, or interactive report performance the more important constraint?

What is the difference between a calculated column and a measure in Power BI? diagram

How to Explain It in an Interview

Start with evaluation timing. A calculated column is evaluated row by row. In the illustrated Import-model flow, Power Query loads and transforms the source data, then the semantic model contains a calculated Margin column such as Margin = Sales[Revenue] - Sales[Cost]. Its value is calculated for each row during refresh and stored in the model. If the underlying Revenue or Cost changes, the stored Margin is updated when the model refreshes.

A measure works differently. The diagram uses Total Margin = SUM(Sales[Revenue]) - SUM(Sales[Cost]). Power BI evaluates that measure when a visual requests it. The measure result is not stored as another model column; the DAX definition is stored, and the result is computed for the current query context.

The context difference is central. A calculated column naturally has row context while it is being evaluated, meaning DAX can refer to values from the current row. Its stored value does not get recomputed merely because a user changes a report slicer. A measure is evaluated primarily through filter context. Slicers, report filters, visual filters, grouping fields placed on rows or columns, and filters propagated through model relationships determine which rows contribute to the measure result.

The diagram shows this behavior with a Product slicer. The stored Margin values remain attached to their individual Sales rows, while the Total Margin measure changes according to the products included by the current filter context. That is why measures are normally the right choice for totals, ratios, averages, percentages, and KPIs that must react to report interaction.

Storage is another difference. A calculated column adds materialized values to the model, which can increase model size. The impact depends on factors such as row count, data type, and cardinality. It can also add work during refresh because the expression must be evaluated for the relevant rows. A measure does not materialize one result per table row, so it avoids that calculated-column storage cost. However, its computation happens at query time, so a complex or inefficient measure can still make report visuals slower.

For model design, use a calculated column when the result truly needs to exist at row level. Examples include a category, flag, bucket, key, or another persistent attribute that must be used for grouping, filtering, sorting, or relationships. Use a measure when the requirement is an aggregate or KPI whose answer should change with the report's current filters.

The practical rule from the diagram is: use a calculated column when you need a persistent row-level attribute; use a measure when you need a dynamic aggregation or KPI that responds to report filtering. Do not choose a calculated column merely because its DAX expression looks simpler. Unnecessary calculated columns can consume memory and add refresh work. Do not assume measures are free either; complex measures move computation to query time and can affect interactive performance.

Finally, validate the design in the semantic model. Confirm that the table grain is correct, compare calculated values and measure totals with a trusted source, test slicers to verify that measures respond to the intended filter context, refresh the model to verify calculated-column behavior, and check report performance before publishing.

Technical Approach

Identify whether the requested result is row-level or aggregated.
Decide whether the value must persist as a field for grouping, filtering, sorting, or relationships.
If it must persist per row, use a calculated column and verify its row-context logic.
If it must respond to report interaction, use a measure and verify its filter-context behavior.
Check the storage and refresh cost of any calculated column.
Check the query-time cost of the measure.
Validate row values, totals, slicer behavior, and report performance before publishing.

Practical Insights

A calculated column trades more model storage and refresh-time work for a value that is materialized for every row. The exact cost depends on row count, data type, cardinality, and the expression. A measure avoids storing one calculated result per row, but it runs when a visual queries it. Simple measures are usually inexpensive, while complex measures over large or complicated models can increase query latency. The choice is therefore not simply 'columns are slow' or 'measures are fast'; it depends on when the work should happen and whether a persistent row-level value is actually required.

Why Interviewers Ask This

The interviewer is testing whether you understand how DAX evaluation context affects semantic-model design, storage, refresh cost, query-time performance, and the choice between persistent row-level attributes and dynamic business metrics.

Common interview mistakes

Common mistakes are using a calculated column for a metric that should respond dynamically to slicers; expecting a stored calculated-column value to recalculate whenever a report filter changes; assuming a measure has row context by default; confusing the stored model column with the aggregate produced by a measure; creating unnecessary calculated columns that increase model size; assuming measures have no performance cost; and failing to validate totals and filter behavior after changing the semantic model.

Interview tip

Explain the distinction in this order: timing, context, storage, performance, and use case. A strong one-line rule is: calculated column for a persistent per-row attribute; measure for a dynamic aggregation or KPI. Then give one DAX example of each and explain how a slicer affects the measure result but not the stored calculated-column values.

Interviewer may ask next

What happens if a measure needs row-by-row logic before it aggregates the result?

A measure can perform row-by-row logic with an iterator such as SUMX over the required table or table expression. The iterator establishes row context while evaluating each row, while the measure as a whole is still evaluated under the report's current filter context. For example, instead of materializing a Margin column, a measure could sum row-level Revenue minus Cost with an iterator when that design is appropriate. I would validate the result and compare query performance before choosing it over a stored calculated column.

How would your choice change if model size is already a problem but the required metric is expensive to calculate interactively?

I would treat it as a storage-versus-query-time tradeoff rather than automatically choosing one option. First I would confirm whether the value really needs to exist as a row-level field. If not, I would prefer a measure and optimize its DAX and model design. If the interactive calculation remains too expensive, I would investigate alternatives such as simplifying the expression, improving the model, or changing the aggregation design rather than adding a calculated column without measurement. I would compare refresh duration, model size, and representative report-query performance before deciding.

7. How would you implement row-level security in Power BI?NEWBusiness Intelligence ToolsMediumDeloitte

Question Details

Describe role design, static versus dynamic security, user-to-entity mapping, relationship requirements, testing, deployment, and common leakage risks.

Short Interview Answer (30-60 seconds)

I would first define who should see which rows. For a small fixed rule, I could use static RLS such as [Region] = "East". For scalable user-specific access, I would use dynamic RLS with a UserRegion mapping table and the rule UserRegion[UserEmail] = USERPRINCIPALNAME(). I would configure the UserRegion-to-DimRegion relationship so the security filter propagates, then let DimRegion filter FactSales through the normal active dimension-to-fact relationship. I would test with View as, publish, assign users or Microsoft Entra security groups, validate in the service, and check additive roles and workspace permissions.

Detailed Explanation

Row-level security, or RLS, makes one Power BI semantic model show different rows to different users. The task is not only to write a DAX filter. I first define the business access rule, decide whether security should be static or user-driven, map each signed-in user to the entities they may see, and design relationships that carry that filter to the fact data. Then I test the model before and after publishing. A good design also fails closed for unknown users and checks workspace permissions and overlapping roles.

Useful Questions to Ask the Interviewer

What entity controls access: region, country, customer, business unit, or team?
Can one user have access to multiple entities?
Are the access assignments stable enough for static roles, or do they change often?
What identity value is stored in the user-mapping source, and does it match the UPN returned by Power BI?
What are the grains of the mapping table, dimension, and fact table?
Are the relevant relationships active, and is dynamic security expected to propagate from a many-side mapping table to a one-side dimension?
Will consumers be workspace Viewers, or will some users be Admins, Members, or Contributors?
Should role membership be assigned directly to users or through supported Microsoft Entra security groups?
Is the semantic model using Import, DirectQuery, or a live connection?
What trusted totals or test identities can be used to verify that the security rules work?

How would you implement row-level security in Power BI? diagram

How to Explain It in an Interview

I would describe the implementation as one end-to-end security flow.

Define the access model. I start with the business rule: who is allowed to see which entity. The diagram uses region access, so the security model is based on users being allowed to see one or more regions. I would keep the role design as simple as possible and avoid assigning the same person to several overlapping roles unless that union of access is intentional.

Choose static or dynamic RLS. Static RLS is useful for a small, stable rule such as a fixed region filter:

[Region] = "East"

Dynamic RLS is more scalable when access differs by user. In that design, I use a mapping table such as UserRegion and filter it with the signed-in user's UPN:

UserRegion[UserEmail] = USERPRINCIPALNAME()

The important requirement is that UserRegion[UserEmail] contains the same identifier format that USERPRINCIPALNAME() returns. A UPN is the sign-in identifier and is not guaranteed to be identical to a user's email alias.

3. Define table grain before configuring relationships. I would define the model grain explicitly:

UserRegion: one row per user-to-region assignment.
DimRegion: one row per region.
FactSales: one row per sales fact at the model's defined transaction or event grain.

This prevents duplicate or ambiguous mappings from producing unexpected access.

Propagate security through the semantic model. The diagram uses UserRegion on the many side of a relationship to DimRegion on the one side. Because the RLS rule is applied to UserRegion, the relationship must be configured so the security filter can propagate from UserRegion to DimRegion. In this pattern, that means using the relationship's bidirectional cross-filtering together with "Apply security filter in both directions" for that security path.

DimRegion then filters FactSales through an active one-to-many relationship with normal single-direction filtering from the dimension to the fact table. The resulting path is:

signed-in user -> UserRegion -> DimRegion -> FactSales -> filtered report

I would use bidirectional security filtering only where the design requires it, because unnecessary bidirectional paths can create ambiguity and can hurt performance.

Make unknown users fail closed. If USERPRINCIPALNAME() does not match any row in UserRegion, the mapping table should return no authorized rows. That should propagate through the model so the user sees no data rather than defaulting to broader access.

Test in Power BI Desktop. I would use View as to simulate the role and test expected identities. I would verify both positive and negative cases: a user who should see one region, a user with several allowed regions, and an unmapped user who should see no rows. I would compare visible row counts and totals with a trusted expected result.

Check RLS performance. RLS filters are applied to DAX queries, so the design can affect report performance. I would compare visual query durations with and without the role applied, especially for a large model or DirectQuery. I would keep the security path simple, use active relationships, and avoid unnecessary bidirectional relationships.

Publish and assign role members. After testing in Desktop, I would publish the semantic model and report to the Power BI service. I would then assign users or supported Microsoft Entra security groups to the semantic model role. Role membership is managed in the service rather than in Power BI Desktop.

Validate again in the Power BI service. I would use Test as role when supported and also validate with real Viewer identities when necessary. This is especially important for dynamic RLS because identity behavior is part of the security design. For DirectQuery models with single sign-on, Test as role has limitations, so validation with an actual Viewer account is important.

Check workspace permission boundaries. RLS restricts workspace Viewers. It does not restrict users who have workspace Admin, Member, or Contributor permissions. If a consumer must be restricted by RLS, I would not give that person an editing workspace role.

Check overlapping roles. Power BI combines multiple RLS role memberships additively. If one role allows East and another allows West, a user assigned to both can see the union of those allowed rows. I would therefore prefer one intentional security outcome per user instead of treating one role as an explicit deny rule.

Consider storage mode and refresh behavior only where relevant. With Import models, RLS filters the data currently loaded into the semantic model, so report freshness depends on the last successful refresh. With DirectQuery, queries are sent to the source at report-query time and RLS can add performance cost. For live connections to Analysis Services, security is configured in the external model rather than recreated as Power BI semantic-model RLS.

Govern exports without treating them as an RLS bypass. A Viewer remains restricted by RLS even when they have Build permission and use features such as Analyze in Excel. I would still review export permissions, Build permissions, and tenant settings because they control how users can consume or redistribute the rows they are already authorized to see; they do not inherently override RLS.

My conclusion would be: use a simple static role for small fixed access rules, but prefer a dynamic user-to-entity mapping for scalable access. Keep the relationship path explicit, fail closed for unmatched identities, test both data correctness and performance, and validate the final permissions in the Power BI service before broad release.

Key Insight / Why This Solution Works

Translate the business access requirement into an entity-level rule.
Define the grain of UserRegion, DimRegion, and FactSales.
Choose static RLS for small fixed access or dynamic RLS for user-specific access.
For dynamic RLS, filter UserRegion with UserRegion[UserEmail] = USERPRINCIPALNAME().
Configure the UserRegion-to-DimRegion relationship so the RLS security filter can propagate from the mapping table to the region dimension.
Keep the DimRegion-to-FactSales relationship active and single-direction from dimension to fact.
Confirm an unmatched UPN returns no mapping rows and therefore no authorized fact rows.
Test expected, multi-entity, and unmapped-user cases with View as in Desktop.
Compare filtered row counts and totals with trusted expected results and check report-query performance.
Publish the semantic model and report.
Assign users or supported Microsoft Entra security groups to the RLS role in the Power BI service.
Validate with Test as role or real Viewer identities and confirm workspace permissions do not bypass the intended RLS boundary.

Why Interviewers Ask This

They want to know whether you can translate business access requirements into a secure Power BI semantic model. The judgment is whether you understand role design, static versus dynamic RLS, identity mapping, relationship-based security propagation, deployment, validation, performance, and the permission boundaries that can make an otherwise correct RLS design expose more data than intended.

Common interview mistakes

Common mistakes include treating RLS as only a DAX problem; using a mapping identifier that does not match USERPRINCIPALNAME(); creating duplicate or incorrect user-to-entity mappings; putting the RLS rule on the wrong table; forgetting to configure the security-filter direction required for the UserRegion-to-DimRegion path; adding unnecessary bidirectional relationships; using inactive relationships in the intended security path; allowing an unexpected identity to default to broad access instead of no rows; assuming multiple roles behave like deny rules when they are actually additive; testing only in Desktop; ignoring DirectQuery and SSO testing limitations; giving restricted consumers workspace Admin, Member, or Contributor permissions; and assuming export features bypass RLS instead of separately governing export and Build permissions.

Interview tip

Present the answer as one security path: identity -> role -> user-to-entity mapping -> dimension -> fact -> filtered report. Then finish with validation and failure cases. Mention USERPRINCIPALNAME(), the relationship security setting, additive roles, fail-closed behavior, service-side testing, and the Viewer versus workspace-editor boundary. That demonstrates end-to-end Power BI security reasoning instead of only memorizing a DAX expression.

Interviewer may ask next

What would you do if a user needs access to several regions, while an unknown user should see nothing?

I would keep UserRegion at one row per user-to-region assignment, so the same UPN can appear in several rows for several authorized regions. The dynamic rule UserRegion[UserEmail] = USERPRINCIPALNAME() returns all mapping rows for that user, and the configured security relationship propagates those regions through DimRegion to FactSales. If the UPN has no matching mapping rows, no region is authorized, so the model returns no fact rows. I would explicitly test both cases before deployment.

When would you choose static RLS over dynamic RLS, and what tradeoff would you explain?

I would choose static RLS when there are only a few stable access groups and the fixed role definitions are easy to maintain. I would choose dynamic RLS when many users have different or changing entity assignments, because a mapping table and one identity-based rule scale better. The tradeoff is that dynamic RLS introduces more dependency on identity quality, mapping-table maintenance, relationship design, and security-path performance, so it requires stronger validation and governance.

8. Do you want to work more on the technical side or client side?BehavioralEasyDeloitte

Interview tip:

Use STAR to structure your answer: briefly explain the Situation and Task, make Action the most detailed part, and finish with the Result. For example, describe a project where you enjoyed both analyzing the data and working with stakeholders, explain which part you want to focus on more, and show how combining technical analysis with clear communication helped support a business decision.

Situation

During a previous project, I worked on an analysis where I had to understand a business question, examine the available data, and explain the findings to stakeholders who would use the analysis to make a decision.

Task

My responsibility was not only to complete the analysis correctly, but also to understand what the stakeholders actually needed and present the result in a way that was useful to them. That experience helped me see which part of the Data Analyst role I enjoy most.

Action

I first spoke with the stakeholders to understand the decision they were trying to make and what information would be most useful. I then worked through the data, checked the important assumptions, and organized the findings around the business question instead of showing every technical detail. When some requirements became clearer during the work, I adjusted the analysis and explained how those changes affected the result. I enjoyed the technical work because I like investigating data and finding patterns, but I also enjoyed discussing the findings and answering questions. If I had to choose, I would like to stay slightly closer to the technical side because I want to keep building strong analytical skills. At the same time, I do not want to work in isolation. I want regular client or stakeholder interaction so I can understand the problem, explain the analysis clearly, and make sure my work supports a real decision.

Result

The analysis gave the stakeholders a clearer view of the issue and helped them discuss the next step with better information. I learned that my preferred role is a balance of both areas, with a stronger technical foundation and enough client interaction to understand needs and communicate results effectively.

Why Interviewers Ask This

Interviewers ask this question to understand what type of work motivates the candidate and whether that preference fits the role. A strong answer shows self awareness, flexibility, and an understanding that a Data Analyst often needs both technical analysis and strong communication with clients or business stakeholders.

Interviewer may ask next

Why do you prefer the technical side slightly more?

I enjoy taking a business question and working through the data until I understand what is really happening. The technical side lets me build deeper analytical skills and improve the quality of my work. I still value client interaction because it gives the analysis purpose and helps me make sure I am solving the right problem.

How would you handle a role that required more client interaction than technical analysis?

I would be comfortable with that because I also enjoy understanding stakeholder needs and explaining findings clearly. I would focus on asking good questions, understanding the decision behind the request, and communicating the analysis in simple terms. I would also look for opportunities to stay involved in the analytical work so I could continue developing both sides of the role.

9. If you were to explain the concept of the universe to a child how will you do it?BehavioralMediumDeloitte

Interview tip:

Use STAR to structure your answer: briefly explain the Situation and Task, make Action the most detailed part, and finish with the Result. For example, describe how you would understand what the child already knows, use a simple everyday comparison, explain the universe one idea at a time, check for understanding, and adjust your explanation based on the child's questions.

Situation

In my work as a Data Analyst, I often need to explain ideas that may seem complicated at first. If I needed to explain the universe to a child, I would use the same communication approach. I would start with something familiar instead of giving a scientific definition immediately.

Task

My goal would be to help the child understand the main idea without overwhelming them. I would want the explanation to be simple enough to remember while still being accurate. I would also pay attention to the child's questions so I could understand which parts needed more explanation.

Action

I would begin by asking what the child already knows about Earth, the Moon, and the stars. Then I would build from those familiar ideas. I might say, "Imagine that your home is inside a town, the town is inside a country, and the country is part of Earth. Earth is one planet near a star called the Sun. The Sun is one of many stars in a huge group of stars called a galaxy. There are many galaxies, and all of those stars, planets, galaxies, space, and everything else together are what we call the universe." I would use a picture or simple objects if they helped make the idea easier to see. I would avoid giving too many facts at once. After each idea, I would ask a simple question such as, "Does that make sense?" or "What do you think comes after our solar system?" If the child looked confused, I would change the example instead of repeating the same explanation. I would also be clear that scientists are still learning about the universe, so it is normal to have questions that do not yet have complete answers.

Result

The child would have a simple mental picture of how Earth fits into something much larger and would be able to ask meaningful questions about it. The main lesson for me is that good communication is not about showing how much I know. It is about understanding the audience, choosing the right level of detail, checking whether the message is clear, and adjusting when needed.

Why Interviewers Ask This

Interviewers ask this question to test whether a candidate can turn a very large and complex idea into a clear explanation for a specific audience. For a Data Analyst, this is important because analytical findings often need to be explained to people with different levels of technical knowledge. A strong answer shows audience awareness, simple communication, logical structure, adaptability, and the ability to explain uncertainty without creating confusion.

Interviewer may ask next

What would you do if the child still did not understand your explanation?

I would first ask which part was confusing instead of assuming I knew the problem. Then I would make the explanation smaller and use a different example, such as drawing circles for Earth, the Sun, the galaxy, and the wider universe. I would keep checking understanding as I added each idea. That is similar to how I communicate analysis at work. If one explanation does not connect with the audience, I change the approach rather than simply adding more detail.

What does this approach teach you about presenting analysis to business stakeholders?

It reminds me to start with what the audience needs to understand and then add detail only when it supports the decision. I should not begin with every calculation or technical term. I should explain the main idea, connect it to something meaningful for the audience, check for questions, and be clear about uncertainty. That makes the analysis easier to understand and more useful for decision support.

9 Deloitte Data Analyst Interview Questions & Answers

Questions with Detailed ExplanationsWith Detailed Explanations

1. What is the difference between an inner join and an outer join, and when would an analyst use each?Sql And DatabasesEasyDeloitte

2. Write SQL to select the second-highest salary in the engineering department.Sql And DatabasesEasyDeloitte

3. How do you do data modelling?Business Intelligence ToolsMediumDeloitte

4. What is the difference between Power BI Desktop and Power BI Service?Business Intelligence ToolsEasyDeloitte

5. How would you diagnose and optimize a slow Power BI report?Business Intelligence ToolsHardDeloitte

6. What is the difference between a calculated column and a measure in Power BI?Business Intelligence ToolsMediumDeloitte

7. How would you implement row-level security in Power BI?NEWBusiness Intelligence ToolsMediumDeloitte

8. Do you want to work more on the technical side or client side?BehavioralEasyDeloitte

9. If you were to explain the concept of the universe to a child how will you do it?BehavioralMediumDeloitte

Data Analyst Resume Examples