Microsoft DP-800 (Developing AI-Enabled Database Solutions) Practice Test 30

Question 290

A nightly ingestion pipeline must embed hundreds of thousands of static documents while enforcing API rate limits and checkpointing independently of write transactions. Which mechanism addresses the requirement?

  1. AI_GENERATE_EMBEDDINGS
  2. Database trigger with guarded work
  3. External batching worker
  4. Azure Function on change event

Explanation

Correct Answer

C:

An external worker can batch requests, retry transient failures, and upsert stored vectors without holding long SQL insert transactions.

Incorrect Answers

A:

AI_GENERATE_EMBEDDINGS calls an authorized external model definition to produce numeric embeddings from character input; the missing behavior is External batching worker; this mechanism does something else.

B:

A DML trigger responds to table changes inside a database transaction, so remote model calls or expensive processing must be treated cautiously; that function is not interchangeable with External batching worker for the requested result.

D:

A change-triggered Azure Function can generate and store embeddings after database changes, decoupling inference latency from OLTP writes; it addresses a different step; the question calls for External batching worker.

Question 291

A remote generation endpoint intermittently responds with HTTP 429, and the procedure needs bounded retry or queueing rather than duplicate untracked work. Which option would you recommend?

  1. SQL structured lookup
  2. Redact confidential fields
  3. Limit retrieved context
  4. Audit retrieved sources
  5. Citation correctness check
  6. Retry transient model throttling

Explanation

Correct Answer

F:

Rate-limit-aware retries and idempotent request handling reduce failures without overwhelming the inference service.

Incorrect Answers

A:

A parameterized structured SQL lookup provides an exact current record without relying on probabilistic text generation; the required capability here is Retry transient model throttling, which this operation does not supply.

B:

Redaction and data minimization remove unnecessary secrets or identifiers before leaving the SQL trust boundary; the alternative cannot substitute for Retry transient model throttling under the given constraints.

C:

Limiting and ranking context passages keeps input within token budgets and reduces irrelevant material sent to the generator; the condition in the stem is resolved by Retry transient model throttling, not by this feature alone.

D:

Logging approved source identifiers and prompt metadata creates a traceable record for investigation without storing unrestricted sensitive content; the missing behavior is Retry transient model throttling; this mechanism does something else.

E:

Citation evaluation checks correspondence between produced claims and referenced evidence instead of merely counting links; that function is not interchangeable with Retry transient model throttling for the requested result.

Question 292

The Azure deployment identity signs in successfully but cannot change stored procedures; it needs appropriate database permissions. Which choice best meets these constraints?

  1. Credential rotation
  2. Federated GitHub identity
  3. Protected environment secret
  4. Database contained user grant

Explanation

Correct Answer

D:

Authentication alone does not grant SQL object privileges, so a contained user or equivalent principal must receive deployment rights.

Incorrect Answers

A:

Revoking and rotating compromised credentials eliminates continued use of exposed secrets, unlike merely deleting a log message; for the stated conditions, the deciding feature is Database contained user grant rather than this behavior.

B:

Federated workload credentials exchange trusted action identity assertions for Azure access, requiring explicit trust configuration; it addresses a different step; the question calls for Database contained user grant.

C:

Environment-scoped secret controls restrict availability to authorized workflows, although long-lived passwords should be minimized; this differs from the Database contained user grant requirement described in the scenario.

Question 293

The live test database has changed manually and a developer needs to inspect differences against the project's schema model. What is the right solution for this specific requirement?

  1. Schema Compare
  2. SqlPackage Script
  3. Declarative object file
  4. SqlPackage DeployReport

Explanation

Correct Answer

A:

Schema Compare contrasts schema definitions from two sources and can help identify drift and reconcile them.

Incorrect Answers

B:

SqlPackage Script emits deployment SQL instead of immediately publishing the changes to the target database; the missing behavior is Schema Compare; this mechanism does something else.

C:

SQL projects store declarative object definitions, which DacFx compares with deployed schemas to derive necessary changes; that function is not interchangeable with Schema Compare for the requested result.

D:

DeployReport describes planned deployment changes derived from model comparison and supports approval workflows; for the stated conditions, the deciding feature is Schema Compare rather than this behavior.

Question 294

An API response should include the complete nested `shippingAddress` object from a stored JSON document without flattening it to text fields. Which choice best meets these constraints?

  1. JSON_VALUE
  2. JSON_QUERY
  3. OPENJSON
  4. ISJSON with ARRAY type constraint

Explanation

Correct Answer

B:

JSON_QUERY extracts an object or array fragment and preserves its JSON structure as an expression result.

Incorrect Answers

A:

JSON_VALUE extracts a single scalar at a path and is not intended to return entire JSON objects or arrays; this differs from the JSON_QUERY requirement described in the scenario.

C:

OPENJSON converts JSON input to a relational rowset and can use an explicit WITH schema for typed extraction; the alternative cannot substitute for JSON_QUERY under the given constraints.

D:

ISJSON(value, ARRAY) tests both JSON syntax and the required top-level array shape on SQL versions that support its second type-constraint argument; the condition in the stem is resolved by JSON_QUERY, not by this feature alone.

Question 295

Database administrators must not have ordinary plaintext access to protected health-identifying values at query processing time. Which technique applies to this scenario?

  1. RLS tenant filtering
  2. Encrypted sensitive column
  3. Column-level permissions
  4. Masked response

Explanation

Correct Answer

B:

Always Encrypted places decryption with approved clients and key stores rather than relying only on engine-side masking.

Incorrect Answers

A:

A database RLS policy supplies the filtering boundary across application query paths using supported security predicates; that function is not interchangeable with Encrypted sensitive column for the requested result.

C:

Column-level GRANT or DENY policies can scope SELECT privileges to designated columns where supported; it addresses a different step; the question calls for Encrypted sensitive column.

D:

Dynamic Data Masking changes result visibility according to effective permissions while leaving encrypted-at-rest requirements separate; this differs from the Encrypted sensitive column requirement described in the scenario.

Question 296

A retrieval pipeline compares vectors using a metric whose behavior depends on vector magnitude and wants to control that effect. Which database mechanism should be chosen?

  1. Metadata-filtered retrieval
  2. Embedding normalization
  3. Model dimension compatibility
  4. VECTOR(1536) column

Explanation

Correct Answer

B:

Normalization scales vectors to a consistent norm for appropriate metrics, which can make some similarity comparisons more consistent.

Incorrect Answers

A:

Enforcing tenant metadata and database authorization filters during retrieval constrains candidate content independently of vector similarity; the alternative cannot substitute for Embedding normalization under the given constraints.

C:

Query and stored vectors must have compatible dimensionality and semantic model space for meaningful distance calculations; the missing behavior is Embedding normalization; this mechanism does something else.

D:

VECTOR(n) declares the element count for a vector, so stored values must match the intended embedding dimension; that function is not interchangeable with Embedding normalization for the requested result.

Question 297

A production release detects unexpected objects missing from the database and policy requires investigation before automatic reconciliation. Which feature would solve the stated problem?

  1. Import changes into SQL project
  2. Block drifted release
  3. Review object dependency graph
  4. Compare live schema to DACPAC

Explanation

Correct Answer

B:

A drift gate can halt deployment when actual target state deviates materially from the expected controlled baseline.

Incorrect Answers

A:

Updating the project from the database can reconcile reviewed schema changes back into declarative source control; it addresses a different step; the question calls for Block drifted release.

C:

Model dependency validation and schema comparison help reveal impacts that a text-only change review may miss; the required capability here is Block drifted release, which this operation does not supply.

D:

Comparing the deployed database with the approved DACPAC reveals schema drift that normal source-code reviews cannot see; the alternative cannot substitute for Block drifted release under the given constraints.

Question 298

A procedure must send a set of matching rows and columns directly to an application data reader. What should the developer implement?

  1. OUTPUT parameter
  2. Input parameter
  3. Result set from SELECT
  4. RETURN status code

Explanation

Correct Answer

C:

Executing SELECT within a procedure returns a result set that clients can consume, unlike an integer-only return code.

Incorrect Answers

A:

An OUTPUT parameter returns a variable value to the caller when the invocation and parameter declaration allow output; the missing behavior is Result set from SELECT; this mechanism does something else.

B:

An input parameter supplies a value for the procedure's execution without embedding a different literal in every call; that function is not interchangeable with Result set from SELECT for the requested result.

D:

A stored procedure RETURN value is an integer status mechanism, conventionally used for control or result codes; it addresses a different step; the question calls for Result set from SELECT.

Question 299

Relevant troubleshooting instructions frequently straddle chunk boundaries, so adjacent segments should share a small amount of text. Which database mechanism should be chosen?

  1. Semantic section chunking
  2. Fixed-size token chunks
  3. Hierarchical parent-child chunks
  4. Overlapping chunks

Explanation

Correct Answer

D:

Overlap repeats limited context between adjacent chunks to reduce information loss at boundaries, at added storage and embedding cost.

Incorrect Answers

A:

Semantic chunking follows document structure or topic boundaries, making returned units more coherent than arbitrary cuts; the required capability here is Overlapping chunks, which this operation does not supply.

B:

Fixed-size chunking divides content into bounded segments, with boundaries that may not respect semantic topics; the alternative cannot substitute for Overlapping chunks under the given constraints.

C:

Parent-child chunk mapping keeps fine-grained searchable chunks linked to larger source sections for context expansion; the condition in the stem is resolved by Overlapping chunks, not by this feature alone.

Question 300

A public service needs one controlled order-cancellation operation without arbitrary SQL execution against the underlying database. What is the right solution for this specific requirement?

  1. Restricted DAB entity permissions
  2. Stored procedure execution boundary
  3. Parameterized SQL statements
  4. Allowlisted MCP tools

Explanation

Correct Answer

B:

Exposing a reviewed procedure with EXECUTE permission provides a constrained interface compared with granting direct table writes.

Incorrect Answers

A:

DAB entity permissions can limit operations exposed to each role, complementing database-side permissions; for the stated conditions, the deciding feature is Stored procedure execution boundary rather than this behavior.

C:

SQL parameters separate supplied values from executable query syntax and are a core mitigation against injection; this differs from the Stored procedure execution boundary requirement described in the scenario.

D:

An allowlisted set of restricted MCP tools reduces agent capabilities; prompt instructions cannot substitute for backend enforcement; the required capability here is Stored procedure execution boundary, which this operation does not supply.

Developing AI-Enabled Database Solutions (DP-800) Practice Tests