Answer-first summary for fast verification
Answer: 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.
The ALTER TABLE ADD CONSTRAINT command in Delta Lake requires that all existing data in the table must comply with the new CHECK constraint. If any existing records violate the constraint (e.g., invalid latitude or longitude values outside the specified ranges), the operation will fail. The error occurs because constraints are enforced on both existing and future data. The correct explanation is that existing data violates the constraints, making option C the correct answer. Other options like B and D incorrectly suggest constraints can only be added at table creation or before data insertion, which is not true. A and E are unrelated to the data validation issue described.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
Downstream consumers of a Delta Lake table are reporting data quality issues affecting their application performance, particularly due to invalid latitude and longitude values in the activity_details table. A junior engineer attempted to add CHECK constraints with the following code, which fails despite correct logic for valid coordinate ranges (-90 to 90 for latitude, -180 to 180 for longitude):
ALTER TABLE activity_details
ADD CONSTRAINT valid_coordinates
CHECK (
latitude >= -90 AND
latitude <= 90 AND
longitude >= -180 AND
longitude <= 180
);
ALTER TABLE activity_details
ADD CONSTRAINT valid_coordinates
CHECK (
latitude >= -90 AND
latitude <= 90 AND
longitude >= -180 AND
longitude <= 180
);
What is the reason for this execution 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.