Databricks Data Engineer Professional Question 180
Select 1You are working on a Delta Lake table that stores sales data, and you want to prevent invalid data from being written to the table. Specifically, sales amounts should always be non-negative, and the 'region' column should only contain one of the following values: 'North', 'South', 'East', 'West'. How can you enforce these constraints when writing to the table?
- A
Add a CHECK constraint to enforce 'sales_amount >= 0' and use a GENERATED COLUMN to restrict 'region' values.
- B
Use Delta Lake's constraint capabilities to define a CHECK constraint for 'sales_amount >= 0' and another CHECK constraint to restrict 'region' to specific values.
- C
Leverage Databricks SQL to create a trigger that validates the data before insertion.
- D
Define 'sales_amount' as a NOT NULL column and 'region' as a STRING column, then manually validate the data in your ETL process before writing.
- E
Add a CHECK constraint on 'sales_amount >= 0' and use a NOT NULL constraint on 'region' to validate its values.
Show answer and explanation
Correct answer: B
Explanation
Delta Lake allows defining CHECK constraints to enforce data quality rules at the table level. In this scenario, a CHECK constraint can ensure that 'sales_amount' is non-negative and that 'region' contains only valid values ('North', 'South', 'East', 'West'). This approach is more robust and maintainable compared to alternatives like manual validation or using unsupported features like triggers.
- A. Incorrect.
Incorrect: While CHECK constraints can enforce conditions like 'sales_amount >= 0', GENERATED COLUMNS are not designed to restrict specific values like 'region'. This approach is not valid in Delta Lake.
- B. Correct.
Correct: Delta Lake supports CHECK constraints, which can enforce conditions like 'sales_amount >= 0' and restrict 'region' values to a predefined set. This is the most effective way to enforce these constraints.
- C. Incorrect.
Incorrect: Databricks SQL does not support triggers, so this is not a viable solution for enforcing constraints in Delta Lake tables.
- D. Incorrect.
Incorrect: While defining NOT NULL columns and validating data in the ETL process can help, this approach does not enforce constraints at the Delta Lake table level. Manual validation is error-prone and not the recommended approach.
- E. Incorrect.
Incorrect: A NOT NULL constraint ensures that a column cannot have NULL values, but it does not validate specific values (like restricting 'region' to 'North', 'South', etc.). This approach is incomplete for the given scenario.