Databricks Data Engineer Associate Question 402
Select 2You are working with a Delta table in Databricks that has a constraint to ensure the 'age' column contains only positive integer values. During a batch data insertion, you encounter a scenario where some rows violate this constraint. Which of the following statements accurately describes the behavior of the 'ON VIOLATION DROP ROW' and 'ON VIOLATION FAIL UPDATE' clauses if they are applied?
- A
ON VIOLATION DROP ROW will exclude the violating rows from being inserted into the table.
- B
ON VIOLATION DROP ROW will insert the violating rows into the table but mark them as invalid.
- C
ON VIOLATION FAIL UPDATE will prevent the entire batch insert operation if any row violates the constraint.
- D
ON VIOLATION FAIL UPDATE will skip only the violating rows and proceed with the insertion of valid rows.
Show answer and explanation
Correct answers: A, C
Explanation
The 'ON VIOLATION DROP ROW' clause ensures that rows violating constraints are excluded from the operation, allowing the insertion of only valid rows. Conversely, 'ON VIOLATION FAIL UPDATE' enforces strict validation by failing the entire operation if any row violates the defined constraints. These mechanisms allow for different handling strategies depending on the use case and data quality requirements.
- A. Correct.
Correct: When 'ON VIOLATION DROP ROW' is used, any rows that violate the constraint are excluded from being inserted into the table.
- B. Incorrect.
Incorrect: Violating rows are not inserted into the table with 'ON VIOLATION DROP ROW'; instead, they are dropped entirely.
- C. Correct.
Correct: 'ON VIOLATION FAIL UPDATE' ensures that if any row violates the constraint, the entire batch operation fails, preventing partial or invalid updates.
- D. Incorrect.
Incorrect: 'ON VIOLATION FAIL UPDATE' does not skip invalid rows. Instead, it fails the entire operation.