Microsoft Certified: Fabric Analytics Engineer AssociateImplement and manage semantic models (30-35%)Medium
A data engineer is configuring a semantic model in Microsoft Fabric. The model contains a table with customer information, including a 'CustomerKey' column. The business requires that the 'CustomerKey' column is always unique and never contains blank values, as it is used as the primary key for relationships across the model. How should the data engineer enforce this data quality rule within the semantic model design?
- ASet the 'CustomerKey' column as a key column in the model properties.
- BCreate a calculated column that checks for uniqueness and blanks, then filter on it.
- CDefine a Row-Level Security (RLS) rule that excludes rows with blank 'CustomerKey'.
- DApply a DAX filter to the 'CustomerKey' column to remove blanks.
Show answer & explanationAnswer & explanation
Correct answer: A. Set the 'CustomerKey' column as a key column in the model properties.
Setting a column as a 'key column' in the semantic model properties explicitly declares it as unique and non-nullable, allowing the modeling engine to optimize queries and enforce data integrity expectations for relationships.
Why the other options are wrong
- B. Creating a calculated column to check these conditions is a reactive measure; it doesn't prevent bad data from being loaded or optimize the model's understanding of the key.
- C. RLS is for security and filtering user-specific rows, not for enforcing data quality rules like uniqueness or non-nullability for the entire model.
- D. A DAX filter only hides data at query time; it doesn't enforce data quality at the model level or prevent blanks from being loaded.
Key Columns in Semantic Models
A column designated as a 'key' in a semantic model (e.g., in Power BI Desktop or Tabular Editor) indicates to the modeling engine that its values are unique and non-nullable. This is critical for defining relationships and optimizing query performance.
- Enforces uniqueness and non-nullability for the specified column.
- Essential for defining valid relationships between tables.
- Helps the modeling engine optimize data storage and query execution.
Memory trick: Key column set, uniqueness met, data integrity, you bet!