DP-800 Practice Questions

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.

The DISABLE TRIGGER Transact-SQL statement is specifically designed to make a trigger inactive without removing its definition from the database. This directly meets the requirement to prevent the ddlAudit trigger from firing during a deployment while ensuring the trigger object remains in place. After the deployment is complete, the trigger can be re-enabled using the ENABLE TRIGGER statement. This provides a controlled way to manage trigger execution during maintenance or deployment windows.

A. ALTER TRIGGER is used to modify the code or definition of the trigger, not to enable or disable its execution.

B. ALTER DATABASE is used for modifying database-level properties and does not provide an option to disable a specific trigger.

C. ALTER SERVER AUDIT SPECIFICATION is a command related to the SQL Server Audit feature, which is separate from DDL triggers.

E. ALTER DATABASE AUDIT SPECIFICATION is also part of the SQL Server Audit feature and is not used to manage DDL triggers.

1. Microsoft. (2024). DISABLE TRIGGER (Transact-SQL). Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/disable-trigger-transact-sql?view=sql-server-ver16. The documentation states, "Disables a trigger. A disabled trigger still exists as an object in the current database, but it does not fire when any Transact-SQL statements on which it was programmed are run." This perfectly describes the required action.

2. Microsoft. (2024). DDL Triggers. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver16. This page describes the management of DDL triggers, including enabling and disabling them as part of their lifecycle.

Because the SQL Server 2025 instance has no outbound connectivity, you must host the embedding model locally. Creating an external model project that references an ONNX file stored on local paths (D) places the model inside the database environment. EXECUTE permission on that external model project must then be granted (C) so that only AlApplicationUser can invoke AIGENERATEEMBEDDINGS, limiting model use to the authorised account.

A. CONTROL gives far broader rights than needed and is not specific to model execution.

B. Auditing records activity but does not prevent unauthorised execution.

E. A Foundry REST endpoint requires outbound network access, which the server does not have.

1. SQL Server 2025 (preview) – “CREATE EXTERNAL MODEL (Transact-SQL)” documentation, sections “LOCALFILE” and “ONNXRUNTIME” (Microsoft Learn, 2024).

2. SQL Server 2025 – “GRANT (Model)” syntax: GRANT EXECUTE ON MODEL::modelname TO username (Microsoft Learn, 2024).

3. SQL Server 2025 – “Using external models without network connectivity” white-paper, p. 4 (“local path deployment with ONNX runtime”).

The proposed solution is incorrect. The Transact-SQL statement DROP PARTITION SCHEME is used to remove the entire partition scheme definition from the database. It cannot be executed while the scheme is being used by a table, such as dbo.Orders. The goal is to remove data from a single, specific partition (the oldest month), not to delete the entire partitioning structure. The correct and most efficient method for this "sliding window" scenario is to use ALTER TABLE ... SWITCH PARTITION to move the target partition to a staging table, which can then be truncated or archived. This approach minimizes logging and impact on the primary table.

A. Yes: This is incorrect because the DROP PARTITION SCHEME command does not remove data from a specific partition. It attempts to remove the scheme's definition and would fail because the scheme is in use.

1. Microsoft Learn, DROP PARTITION SCHEME (Transact-SQL): This document states, "Removes a partition scheme from the current database." It also specifies under 'Arguments' and 'Permissions' that "A partition scheme can be dropped only if there are no tables or indexes currently using the partition scheme." This confirms the proposed command is inappropriate and would fail.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-partition-scheme-transact-sql

2. Microsoft Learn, Partitioned tables and indexes: This guide describes the "sliding window" scenario for managing large tables. It explicitly recommends using ALTER TABLE...SWITCH as the most efficient method to move old data out of a partitioned table. This highlights the correct procedure that should have been used instead of DROP PARTITION SCHEME.

Source: https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes, Section: "Managing partitions with a 'sliding window' scenario".

3. Microsoft Learn, ALTER TABLE (Transact-SQL): The documentation for ALTER TABLE details the SWITCH PARTITION clause. It explains that this operation reassigns a block of data between two tables. This is a metadata-only operation, making it extremely fast and minimally logged, thus meeting the requirement to minimize impact.

Source: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql, Section: "SWITCH [ PARTITION ... ] TO ...".

The SqlPackage.exe command-line utility includes the DriftReport action specifically for this purpose. This action compares the schema of a registered database against its baseline registration (typically the last deployed .dacpac) and generates an XML report detailing any changes or "drift" that has occurred directly on the production database, outside of the source-controlled deployment process.

B. The DeployReport action generates a report of changes that would be made by a deployment, not a report of drift that has already occurred.

C. The Extract action creates a .dacpac file from a live database. While this .dacpac could be used in a comparison, this action alone does not generate the drift report.

D. The Script action generates a T-SQL deployment script to update a target database. It does not produce a drift report.

1. Microsoft Docs, "SqlPackage DriftReport action". States: "The SqlPackage DriftReport action creates an XML report of the changes that have been made to a registered database since it was last registered."

2. Microsoft Docs, "SqlPackage command-line reference". Provides the syntax and parameters for all actions, clearly distinguishing the purpose of DriftReport from other actions like DeployReport and Script.

Yes. The GitHub Actions workflow is configured to trigger on a push to the main branch. The unit-tests job includes the condition if: github.ref == 'refs/heads/main', which evaluates to true during a direct push or merge to the main branch. Assuming the preceding build-and-deploy job succeeds, the unit tests will automatically execute.

Yes. The Build step executes dotnet build db1.sqlproj. For SDK-style SQL database projects, the build process parses the Transact-SQL scripts, resolves dependencies, and validates the database schema for syntax errors and valid object references. If the schema is invalid, the build fails and the .dacpac file is not generated.

No. The Deploy step executes SqlPackage /Action:Publish. This step takes the already-validated .dacpac file (the compiled database model) and publishes it to the target database. While SqlPackage does compare the source and target models to generate an incremental deployment script, the actual validation of the source schema itself has already occurred during the Build step.

Microsoft Learn: Contexts and expressions in GitHub Actions

Detail: Explains the github.ref property and if conditional execution in workflows. A push to the main branch sets the ref to refs/heads/main.

Link: https://docs.github.com/en/actions/learn-github-actions/contexts#github-context

Microsoft Learn: Database Projects (SDK-style) - Build and Publish

Detail: Outlines that dotnet build is used to compile SDK-style SQL projects into a DACPAC, which includes parsing and validating the SQL schema.

Link: https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/concepts/build-publish-deploy

Microsoft Learn: SqlPackage Publish

Detail: Documents the /Action:Publish parameter of the SqlPackage utility, which is responsible for incrementally updating a database schema to match the schema of a source .dacpac file, not for initial syntax/schema validation.

Link: https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish

Statement 1 (No): In Data API builder (DAB), when a client explicitly includes the X-MS-API-ROLE header, DAB evaluates the authorization rules strictly against that specific role. Because the Order entity in the configuration only has permissions mapped to the authenticated role, a request explicitly requesting the operations role will be denied access, even if the user token is otherwise valid.

Statement 2 (No): Data API builder does not support database policies (row-level authorization filters) on stored procedure entities. DAB enforces policies by dynamically injecting WHERE clauses into standard table or view queries. Since DAB cannot safely rewrite or inject predicates into precompiled stored procedures, the policy property is unsupported and will not be enforced by the DAB runtime engine.

Statement 3 (Yes): According to DAB's role resolution mechanics, if a client request includes a valid authentication token but omits the X-MS-API-ROLE HTTP header, the engine automatically defaults the context to the system role of authenticated. The request to the Order entity will succeed because it explicitly grants read permissions to the authenticated role.

Microsoft Learn: Data API builder - Authentication and Authorization. Section: Role resolution and X-MS-API-ROLE header behavior. (Available at: https://learn.microsoft.com/en-us/azure/data-api-builder/authentication-authorization)

Microsoft Learn: Data API builder - Stored procedures. Section: Limitations and unsupported features (Database policies). (Available at: https://learn.microsoft.com/en-us/azure/data-api-builder/stored-procedures)

Statement 1 (Yes): The Transact-SQL schema properly implements Dynamic Data Masking (DDM) functions (such as partial, email, and default) on columns containing Personally Identifiable Information (PII) like FullName, EmailAddress, and PhoneNumber. This limits sensitive data exposure to non-privileged users, satisfying standard application-level PII obfuscation requirements.

Statement 2 (No): Row-Level Security (RLS) filter predicates apply universally to all users, including those with database administrative privileges (db_owner or sysadmin). Unless the administrator is explicitly mapped in the dbo.UserRegionAccess lookup table (which the function relies on) or temporarily disables the security policy, the RLS policy will filter out the rows for them just as it does for standard users.

Statement 3 (Yes): Row-Level Security and Dynamic Data Masking are independent and fully compatible features. RLS applies first by filtering the rows at the database engine level. After RLS returns the permitted rows, DDM masking rules are successfully applied to those remaining rows before the result set is presented to the user.

Microsoft Learn: Row-Level Security

Section: Security (Best Practices & Considerations).

Detail: Officially states that "RLS applies to all users, including dbo and sysadmin. However, users with permissions to alter the security policy can alter or drop the security policy..." confirming administrators cannot natively bypass active RLS filter predicates without administrative intervention.

Source: Row-level security - SQL Server | Microsoft Learn

Microsoft Learn: Dynamic Data Masking

Section: Dynamic Data Masking (Security features integration).

Detail: Confirms DDM limits data exposure for PII and explicitly notes that "Dynamic data masking can be used in conjunction with other SQL Server security features (auditing, encryption, row level security...)", verifying that masking rules persist concurrently with RLS row filtering.

Source: Dynamic data masking - SQL Server | Microsoft Learn

Semantic query: Azure SQL Database supports native vector search primarily via the VECTOR_DISTANCE function, which calculates the mathematical distance between two vectors. Because a smaller distance means higher semantic similarity, the query must be ordered by distance in ascending order.

Keyword retrieval: The requirement explicitly asks for a "full-text search... for the keyword portion" that will be used in a weighted formula. CONTAINSTABLE evaluates exact or prefix keyword matches and natively returns a RANK column with values from 0 to 1000. FREETEXTTABLE searches for underlying meaning rather than exact keywords, making it less precise for pure "keyword" requirements.

Final ranking: To combine these two scores mathematically, they must align on the same directional scale. Vector distance is a "lower-is-better" metric, while the full-text RANK is a "higher-is-better" metric (0 to 1000). By dividing the RANK by 1000.0, you normalize it to a 0–1 scale. Subtracting it from 1.0 (1.0 - RANK/1000.0) inverts the scale so that it also becomes a "lower-is-better" metric. Finally, applying the requested 60% / 40% weights yields a unified score that can be accurately ordered.

Azure SQL Vector Search & Functions: Microsoft Learn, "Vector & Embeddings Frequently Asked Questions (FAQ) - SQL Server" — details the implementation of vector distance queries and native functions. (https://learn.microsoft.com/en-us/sql/sql-server/ai/vectors-faq)

Full-Text Ranking: Microsoft Learn, "CONTAINSTABLE (Transact-SQL)" — outlines how full-text ranking operates on a 0 to 1000 scale, essential for the weighting algorithm. (https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/containstable-transact-sql)

Hybrid Search Architecture: Microsoft Learn, "Microsoft SQL Server Database Provider - Vector Search - EF Core" — provides the architectural blueprint for combining exact semantic similarity (VECTOR_DISTANCE) with traditional full-text query components. (https://learn.microsoft.com/en-us/ef/core/providers/sql-server/vector-search)

Based on the provided configuration exhibits for the SalesApi item:

• Statements 1 and 2 (No): The SqlUsers group does not have the "Run Queries and Mutations" permission checked. Consequently, they are blocked at the API layer from executing any data retrieval (Queries) or data modification (Mutations) operations against SalesDB via the API. The Single Sign-On (SSO) connectivity setting is irrelevant if they lack the execution permissions on the GraphQL item itself.
• Statement 3 (Yes): The SqlUsers group is explicitly granted the "View and Edit GraphQL item" permission. This permission allows users to open the GraphQL item in the Fabric portal and modify its schema and metadata, which includes changing how fields map to the underlying data source objects.

Microsoft Learn: Share an API for GraphQL in Microsoft Fabric. Details the specific item-level sharing permissions for GraphQL, explicitly defining "Run Queries and Mutations" as the requirement for data access, and "View and Edit GraphQL item" for schema/mapping modifications. (Source: official Microsoft Fabric Data Engineering documentation).

Microsoft Learn: Roles in workspaces in Microsoft Fabric. Discusses how item-level permissions (like sharing an API with edit rights) interact with and can extend base workspace roles like the Viewer role for specific shared items. (Source: official Microsoft Fabric Administration documentation).