Databricks Data Engineer Associate Question 148
Select 2You are working with a Delta Lake table that contains customer transaction data. The table has a column named status, which must not contain the value 'cancelled'. How would you efficiently validate that no rows in the table have the value 'cancelled' in the status column?
- A
Run a SQL query: SELECT COUNT(*) FROM table WHERE status = 'cancelled'. If the result is 0, the value is not present.
- B
Use the Delta Lake
VACUUMcommand to identify rows with 'cancelled' in thestatuscolumn. - C
Use the Databricks Data Quality Dashboard to automatically validate that 'cancelled' is not present in the
statuscolumn. - D
Run a DataFrame operation: df.filter(df.status == 'cancelled').count() == 0 to check if any rows have the value 'cancelled'.
Show answer and explanation
Correct answers: A, D
Explanation
To validate that a value is not present in a specific column, you can either use a SQL query to count the rows where the value exists or use a DataFrame operation to filter and count those rows. Both approaches confirm the absence of the value if the count is 0. The VACUUM command and a non-existent Databricks Data Quality Dashboard are not valid methods for this task.
- A. Correct.
Correct. Running a SQL query to count rows where the
statuscolumn equals 'cancelled' is an efficient way to validate its absence. If the count is 0, the value is not present. - B. Incorrect.
Incorrect. The
VACUUMcommand is used for cleaning up old data files in Delta Lake and cannot be used to validate the contents of a column. - C. Incorrect.
Incorrect. The Databricks Data Quality Dashboard does not exist as an automated tool for validating specific column values. This is not a valid approach.
- D. Correct.
Correct. Using a DataFrame operation to filter rows and count them is another valid way to check whether the value 'cancelled' is present in the
statuscolumn.