A data engineer is working on a Microsoft Fabric semantic model that contains sensitive customer data. The company's policy dictates that data analysts should only see customer data relevant to their assigned region. This security requirement must be enforced at the data source level, meaning the data returned to the semantic model itself must already be filtered. Which approach should the data engineer take to implement this security while minimizing data transfer and processing within the semantic model?
- AApply a filter directly in the Power Query step of the data transformation, based on the user's region.
- BImplement Object-Level Security (OLS) to hide customer columns not relevant to the user's region.
- CImplement Row-Level Security (RLS) within the semantic model using DAX filters.
- DCreate separate semantic models for each region, each with pre-filtered data.
Show answer & explanationAnswer & explanation
Correct answer: A. Apply a filter directly in the Power Query step of the data transformation, based on the user's region.
Applying a filter directly in the Power Query step (or the source query itself) pushes the filtering logic to the data source. This means only the relevant, filtered data is loaded into the semantic model, minimizing data transfer and processing within Fabric. This effectively enforces security at the 'data source level' as requested, before the data even reaches the semantic model for further processing or RLS.
Why the other options are wrong
- B. OLS hides columns or tables, but the requirement is to filter *rows* based on a user's region, not to hide entire columns.
- C. RLS in the semantic model filters data *after* it has been loaded into the model, which does not meet the requirement of filtering 'at the data source level' to minimize transfer.
- D. Creating separate semantic models for each region would be a high-maintenance and inefficient solution, leading to data duplication and management overhead, rather than a dynamic security implementation.
Source-Side Filtering for Security
Source-side filtering for security involves applying filters directly in the data source query or data transformation (e.g., Power Query) before data is loaded into the semantic model. This minimizes data transfer, enhances performance, and enforces security at the earliest possible stage.
- Filters data at the source, reducing data loaded into the model.
- Improves performance by minimizing data transfer.
- Enforces security 'at the data source level'.
- Can be implemented via source query parameters or Power Query filters.
Memory trick: Filter at the source, secure the flow, before the data's in the show.