You need to recommend a solution that will resolve the ingestion pipeline failure issues. Which two actions should you recommend? Each correct answer…

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.

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.

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.