Databricks Data Engineer Associate Question 147
Select 3You are tasked with ensuring that a specific column in a Delta table does not contain a forbidden value ('INVALID'). Which of the following approaches can you use to validate that the value 'INVALID' is not present in the column 'status'?
- A
Write a query using the NOT EXISTS clause to check for the value 'INVALID' in the 'status' column.
- B
Use the
filtertransformation in a PySpark DataFrame to exclude rows where the 'status' column contains 'INVALID' and verify the count matches the original table. - C
Perform a SQL query using the COUNT function to count rows where the 'status' column equals 'INVALID'.
- D
Define a Delta table constraint to prevent the insertion of rows with the 'INVALID' value in the 'status' column.
- E
Use the DROP COLUMN command to remove the 'status' column from the Delta table.
Show answer and explanation
Correct answers: A, B, C
Explanation
To validate that a specific value is not present in a column, you can use SQL queries like NOT EXISTS or COUNT to check for its absence. Additionally, in PySpark, you can filter the data and verify row counts. Constraints in Delta tables are preventative mechanisms and do not address existing data. Dropping the column removes all its data but does not validate the presence or absence of a specific value.
- A. Correct.
Using the NOT EXISTS clause is a valid SQL approach to check if a specific value is absent from a column.
- B. Correct.
Filtering rows with the 'filter' transformation and comparing row counts is a valid PySpark method to validate the absence of a specific value.
- C. Correct.
Using a COUNT query to check for the number of rows with the 'INVALID' value is a straightforward SQL-based validation approach.
- D. Incorrect.
While constraints in Delta tables can prevent invalid data from being inserted in the future, they do not validate the absence of a value in existing data.
- E. Incorrect.
Dropping the column eliminates the data entirely but does not validate whether a specific value exists in the column.