DP-800 practice question 1 of 15

You need to recommend a solution that will resolve the ingestion pipeline failure issues. Which two actions should you recommend? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Full set: 61 questions
Answer options
A: Enable snapshot isolation on the database.
B: Use a trigger to automatically rewrite malformed JSON.
C: Add foreign key constraints on the table.
D: Create a unique index on a hash of the payload. Correct answer
E: Add a check constraint that validates the JSON structure. Correct answer
Correct Answer:
D. Create a unique index on a hash of the payload.; E. Add a check constraint that validates the JSON structure.
Explanation

Ingestion pipeline failures are commonly caused by data quality issues such as malformed data or duplicate records. Adding a CHECK constraint that uses the ISJSON() function (Option E) enforces that any data inserted into the target column is structurally valid JSON, preventing errors from malformed payloads. To prevent failures from duplicate data, creating a unique index on a computed hash of the JSON payload (Option D) is an efficient and robust method to enforce uniqueness at the database level.

Why Incorrect

A. Snapshot isolation is a concurrency mechanism that addresses locking and blocking issues, not data integrity failures during ingestion.
B. Using a trigger to rewrite malformed JSON is a complex and brittle approach that can hide upstream data quality problems and is not a recommended practice.
C. Foreign key constraints enforce referential integrity between tables, which is a different class of data validation than payload structure or duplication.

References

1. Microsoft Learn, "CHECK Constraints". This document explains that CHECK constraints enforce domain integrity by limiting the values accepted in a column, which can be used with functions like ISJSON().

2. Microsoft Learn, "JSON Data in SQL Server". This page details the ISJSON ( expression ) function, which "tests whether a string contains valid JSON" and can be used in a CHECK constraint.

3. Microsoft Learn, "Create Unique Indexes". This document describes how unique indexes enforce data uniqueness. Applying this to a hash of a large column (like a JSON payload) is a standard pattern for ensuring entity uniqueness.

Scroll to Top