Which statement explains the cause of this failure?

The downstream consumers of a Delta Lake table have been complaining about data quality issues impacting performance in their applications. Specifically, they have complained that invalid latitude and longitude values in the activity_details table have been breaking their ability to use other geolocation processes.

A junior engineer has written the following code to add CHECK constraints to the Delta Lake table:

A senior engineer has confirmed the above logic is correct and the valid ranges for latitude and longitude are provided, but the code fails when executed.

Which statement explains the cause of this failure?
A . Because another team uses this table to support a frequently running application, two-phase locking is preventing the operation from committing.
B . The activity details table already exists; CHECK constraints can only be added during initial table creation.
C . The activity details table already contains records that violate the constraints; all existing data must pass CHECK constraints in order to add them to an existing table.
D . The activity details table already contains records; CHECK constraints can only be added prior to inserting values into a table.
E . The current table schema does not contain the field valid coordinates; schema evolution will need to be enabled before altering the table to add a constraint.

View Answer

Answer: C

Explanation:

The failure is that the code to add CHECK constraints to the Delta Lake table fails when executed. The code uses ALTER TABLE ADD CONSTRAINT commands to add two CHECK constraints to a table named activity_details. The first constraint checks if the latitude value is between -90 and 90, and the second constraint checks if the longitude value is between -180 and 180. The cause of this failure is that the activity_details table already contains records that violate these constraints, meaning that they have invalid latitude or longitude values outside of these ranges. When adding CHECK constraints to an existing table, Delta Lake verifies that all existing data satisfies the constraints before adding them to the table. If any record violates the constraints, Delta Lake throws an exception and aborts the operation.

Verified Reference: [Databricks Certified Data Engineer

Professional], under “Delta Lake” section; Databricks Documentation, under “Add a CHECK constraint

to an existing table” section.

https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-alter-table.html#add-

constraint