Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Medium
A retail company is migrating its customer loyalty program data to a Microsoft Fabric Lakehouse. The source system is a legacy relational database that stores customer IDs as integers, but some older records have null values for customer ID. The new Lakehouse Delta table requires a non-nullable `CustomerID` column. During ingestion, how should the data engineer handle the null `CustomerID` values to meet the non-nullable constraint while preserving data integrity?
- AChange the `CustomerID` column in the Delta table to allow nulls temporarily.
- BStop the ingestion process and report an error for each null value.
- CFilter out all records with null `CustomerID` before ingestion.
- DReplace null `CustomerID` values with a default value like -1 or a generated unique ID.
Show answer & explanationAnswer & explanation
Correct answer: D. Replace null `CustomerID` values with a default value like -1 or a generated unique ID.
Replacing null `CustomerID` values with a default value (like -1) or generating a unique ID is the most appropriate method. This preserves all records, meets the non-nullable constraint, and allows for identification of records that originally lacked a customer ID without losing data.
Why the other options are wrong
- A. Changing the schema to allow nulls would violate the stated requirement for a non-nullable `CustomerID` column in the new Lakehouse Delta table.
- B. Stopping the process for each null is impractical for large datasets and doesn't provide a solution for ingesting the problematic records.
- C. Filtering out records with null `CustomerID` would result in data loss, which is generally undesirable for customer data.
Handling Null Values in Data Ingestion
Strategies for addressing null values during data ingestion to ensure data quality and meet target schema constraints, often involving imputation, default values, or removing records based on business rules.
- Impacts data integrity and analysis.
- Requires business context to choose the best strategy.
- Can involve replacement, removal, or separate handling.
Memory trick: Don't lose data to nulls, replace or generate a new ID.