Question 250
A change feed consumer loses its checkpoint and replays hundreds of SQL updates, causing duplicate downstream account notifications. A Data API builder endpoint works locally but production returns connection failures because the container cannot resolve or reach its private SQL endpoint. What is the most appropriate plan for this situation?
- Configure cache lifetime or bypass for freshness-sensitive balance data and test invalidation behavior; verify deployment network, DNS and private endpoint connectivity in addition to DAB entity configuration.
- Make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions; verify deployment network, DNS and private endpoint connectivity in addition to DAB entity configuration.
- Verify deployment network, DNS and private endpoint connectivity in addition to DAB entity configuration; do not Make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions.
- Make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions; implement bounded retries with durable failure handling and monitor unprocessed events.
Explanation
Correct Answer
B:First, event and change processing can deliver duplicates; downstream side effects must be safe to repeat. Second, a valid API schema cannot compensate for missing network reachability to its database backend.
Incorrect Answers
A:Configure cache lifetime or bypass for freshness-sensitive balance data and test invalidation behavior; verify deployment network, DNS and private endpoint connectivity in addition to DAB entity configuration: event and change processing can deliver duplicates; downstream side effects must be safe to repeat.
C:Verify deployment network, DNS and private endpoint connectivity in addition to DAB entity configuration; do not Make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions: make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions.
D:Make the consumer idempotent and persist appropriate checkpoints before treating retried events as new transactions; implement bounded retries with durable failure handling and monitor unprocessed events: a valid API schema cannot compensate for missing network reachability to its database backend.
Question 251
An AI-enabled database project needs two corrections. A migration alters invoice constraints but its tests check only that the SQL script executes, not whether old invoices remain valid. A pipeline runs unit tests against mocked queries but never checks whether the application can access the deployed stored procedure. Which course of action addresses the stated issues?
- Add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases; Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts.
- Add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases; add an integration test using a least-privileged application identity against an isolated deployed database.
- Add an integration test using a least-privileged application identity against an isolated deployed database; do not add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases.
- Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior; add an integration test using a least-privileged application identity against an isolated deployed database.
Explanation
Correct Answer
B:First, successful schema execution does not prove the application still meets business integrity requirements. Second, database object permission and actual connectivity are runtime integration concerns beyond function unit tests.
Incorrect Answers
A:Add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases; Version reference data alongside the SQL Database Project and apply controlled repeatable seed scripts: database object permission and actual connectivity are runtime integration concerns beyond function unit tests.
C:Add an integration test using a least-privileged application identity against an isolated deployed database; do not add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases: add representative data-integrity and end-to-end integration tests that exercise existing and new invoice cases.
D:Add negative and boundary unit tests covering invalid inputs, duplicates and transaction rollback behavior; add an integration test using a least-privileged application identity against an isolated deployed database: successful schema execution does not prove the application still meets business integrity requirements.
Question 252
A regulated SQL service must record who changed sensitive payroll entries, yet the application logs only success or failure messages. An administrator enables verbose SQL audit events and the system unexpectedly captures secret parameter values in unrestricted logs. Which response best addresses these needs?
- Configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection.
- Configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access; Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
- Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection.
- Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; do not configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access.
Explanation
Correct Answer
A:First, database audit events preserve accountability for protected data operations beyond generic application status logs. Second, observability must not turn sensitive business input into a second uncontrolled data store.
Incorrect Answers
B:Configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access; Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering: observability must not turn sensitive business input into a second uncontrolled data store.
C:Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring; review captured fields and apply data minimization, access restrictions and retention controls before continuing collection: database audit events preserve accountability for protected data operations beyond generic application status logs.
D:Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection; do not configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access: configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access.
Question 253
A database architect reviews two implementation risks. A stored report uses SELECT * after a schema expansion, unexpectedly exposing confidential columns to an API client. A query uses TOP 10 without ORDER BY and support observes a different group of customers on each execution. What is the most appropriate plan for this situation?
- Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records; define a business-specific ORDER BY with a stable tie-breaker before applying TOP.
- Select explicitly authorized columns and make schema output changes subject to contract review; define a business-specific ORDER BY with a stable tie-breaker before applying TOP.
- Define a business-specific ORDER BY with a stable tie-breaker before applying TOP; do not select explicitly authorized columns and make schema output changes subject to contract review.
- Select explicitly authorized columns and make schema output changes subject to contract review; use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists.
Explanation
Correct Answer
B:First, explicit projections avoid accidental information disclosure when the source table grows. Second, a limited result set is not meaningful or reproducible without a deterministic selection order.
Incorrect Answers
A:Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records; define a business-specific ORDER BY with a stable tie-breaker before applying TOP: explicit projections avoid accidental information disclosure when the source table grows.
C:Define a business-specific ORDER BY with a stable tie-breaker before applying TOP; do not select explicitly authorized columns and make schema output changes subject to contract review: select explicitly authorized columns and make schema output changes subject to contract review.
D:Select explicitly authorized columns and make schema output changes subject to contract review; use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: a limited result set is not meaningful or reproducible without a deterministic selection order.
Question 254
An application team must address both issues before release. Two sessions modify inventory and account balances and the second step fails, leaving one table updated without the other. An API retries a timed-out purchase request and creates two identical orders because the first transaction actually committed. Which course of action addresses the stated issues?
- Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; add an idempotency key enforced by a unique database rule and return the existing result on safe retries.
- Add an idempotency key enforced by a unique database rule and return the existing result on safe retries; do not Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling.
- Choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs; add an idempotency key enforced by a unique database rule and return the existing result on safe retries.
- Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; Guarantee transaction closure across success and error paths and verify the open transaction state in tests.
Explanation
Correct Answer
A:First, atomic transactions keep related state changes together when any step fails. Second, retries must distinguish a completed operation from a new one to avoid duplicate side effects.
Incorrect Answers
B:Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling.
C:Choose an appropriate isolation strategy that avoids dirty reads and measure its concurrency trade-offs; add an idempotency key enforced by a unique database rule and return the existing result on safe retries: atomic transactions keep related state changes together when any step fails.
D:Enclose the related modifications in one transaction with a tested rollback path and TRY/CATCH error handling; Guarantee transaction closure across success and error paths and verify the open transaction state in tests: retries must distinguish a completed operation from a new one to avoid duplicate side effects.
Question 255
Issue 1: A user asks for the current expense policy, but the index returns a superseded draft with high semantic relevance. Issue 2: An AI assistant returns retrieved documents but strips source IDs and section metadata before citing them. Issue 3: A support team stores an entire confidential PDF in every retrieval log even though only document IDs and scoring metrics are needed. Which complete response addresses the three issues in order?
- Filter results by approved status and effective version metadata before generating policy answers; retain provenance through retrieval and answer formatting with stable document and section references; and Minimize search telemetry to approved metadata and apply access and retention safeguards.
- Filter results by approved status and effective version metadata before generating policy answers; retain provenance through retrieval and answer formatting with stable document and section references; and apply authoritative tenant and access-control filters before exposing or using retrieved candidate text.
- Filter results by approved status and effective version metadata before generating policy answers; apply authoritative tenant and access-control filters before exposing or using retrieved candidate text; and Minimize search telemetry to approved metadata and apply access and retention safeguards.
- Apply authoritative tenant and access-control filters before exposing or using retrieved candidate text; retain provenance through retrieval and answer formatting with stable document and section references; and Minimize search telemetry to approved metadata and apply access and retention safeguards.
Explanation
Correct Answer
A:For issue 1, source authority and freshness must be enforced independently of semantic closeness. For issue 2, auditable answers require traceable source locations beyond a plausible title. For issue 3, search troubleshooting must not become an uncontrolled copy of sensitive source content.
Incorrect Answers
B:Filter results by approved status and effective version metadata before generating policy answers; retain provenance through retrieval and answer formatting with stable document and section references; and apply authoritative tenant and access-control filters before exposing or using retrieved candidate text: search troubleshooting must not become an uncontrolled copy of sensitive source content.
C:Filter results by approved status and effective version metadata before generating policy answers; apply authoritative tenant and access-control filters before exposing or using retrieved candidate text; and Minimize search telemetry to approved metadata and apply access and retention safeguards: auditable answers require traceable source locations beyond a plausible title.
D:Apply authoritative tenant and access-control filters before exposing or using retrieved candidate text; retain provenance through retrieval and answer formatting with stable document and section references; and Minimize search telemetry to approved metadata and apply access and retention safeguards: source authority and freshness must be enforced independently of semantic closeness.
Question 256
Issue 1: An application needs to describe diagrams from uploaded images but its external model accepts text input only. Issue 2: A multilingual support application is tested only with English despite having primary customers in Japanese and Arabic. Issue 3: A lightweight SQL assistant uses a costly large model for simple data categorization and fails its latency and budget goals. Which complete response addresses the three issues in order?
- Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; and Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements.
- Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; and Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements.
- Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; and Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements.
- Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; and evaluate a model with reliable structured-output support and validate its schema behavior on representative requests.
Explanation
Correct Answer
C:For issue 1, a text-only model cannot inspect actual image content absent from its supported input interface. For issue 2, model quality can vary substantially by language, making English-only evaluations inadequate. For issue 3, model size should be justified by tested workload outcomes rather than chosen for prestige alone.
Incorrect Answers
A:Evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; and Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements: a text-only model cannot inspect actual image content absent from its supported input interface.
B:Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; evaluate a model with reliable structured-output support and validate its schema behavior on representative requests; and Benchmark a smaller appropriate model against task accuracy, response-time and full operating-cost requirements: model quality can vary substantially by language, making English-only evaluations inadequate.
D:Choose an appropriately authorized multimodal model and test image understanding with representative sample diagrams; compare eligible models using target-language evaluation sets and verify meaning, safety and domain terminology; and evaluate a model with reliable structured-output support and validate its schema behavior on representative requests: model size should be justified by tested workload outcomes rather than chosen for prestige alone.
Question 257
A report uses NOT IN against a subquery containing NULL and returns no rows even though unmatched IDs exist. Which recommendation best resolves this issue?
- Use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists.
- Consider NOT EXISTS with a correlated equality test and verify NULL behavior using representative records.
- Select explicitly authorized columns and make schema output changes subject to contract review.
- Define a business-specific ORDER BY with a stable tie-breaker before applying TOP.
Explanation
Correct Answer
B:NULL in a NOT IN set can make the comparison unknown and unexpectedly filter all rows.
Incorrect Answers
A:Use parameterized statements or sp_executesql with typed parameters and review any dynamic identifier allowlists: parameterization separates user data from executable SQL syntax.
C:Select explicitly authorized columns and make schema output changes subject to contract review: explicit projections avoid accidental information disclosure when the source table grows.
D:Define a business-specific ORDER BY with a stable tie-breaker before applying TOP: a limited result set is not meaningful or reproducible without a deterministic selection order.
Question 258
An analytics pipeline needs to reuse a parameterized set of invoice rows joined into several different SELECT statements. What is the most defensible technical decision?
- Choose an appropriate table-valued function and validate its plan behavior and input filtering.
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements.
- Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
Explanation
Correct Answer
A:A table-valued function supplies a relational row set that callers can compose with other queries.
Incorrect Answers
B:Create a view exposing only approved columns and predicates with appropriate permissions and schema review: create a view exposing only approved columns and predicates with appropriate permissions and schema review: views centralize query logic and can restrict presentation but do not replace all underlying access security.
C:Use a deterministic scalar function or expression appropriate to the platform and performance requirements: a scalar calculation produces one result from inputs without needing write-time side effects.
D:Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Question 259
A team expects GitHub Copilot-generated SQL to be correct without test data or runtime permissions checks. A developer enables an AI extension with broad access to every repository even though it is needed for one isolated database project. What is the most appropriate plan for this situation?
- Test generated statements with representative fixtures and verify access behavior before accepting them into source control; restrict the extension and repository scopes using approved configuration and audit its permissions.
- Test generated statements with representative fixtures and verify access behavior before accepting them into source control; remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls.
- Restrict the extension and repository scopes using approved configuration and audit its permissions; do not test generated statements with representative fixtures and verify access behavior before accepting them into source control.
- Require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL; restrict the extension and repository scopes using approved configuration and audit its permissions.
Explanation
Correct Answer
A:First, AI suggestions require human validation of both logic and security implications. Second, least-privilege principles apply to development assistants as well as application identities.
Incorrect Answers
B:Test generated statements with representative fixtures and verify access behavior before accepting them into source control; remove and rotate any disclosed secret and use redacted configuration with approved enterprise AI controls: least-privilege principles apply to development assistants as well as application identities.
C:Test generated statements with representative fixtures and verify access behavior before accepting them into source control.
D:Require code review, schema diff inspection and backup and rollback readiness before executing AI-generated DDL; restrict the extension and repository scopes using approved configuration and audit its permissions: aI suggestions require human validation of both logic and security implications.
Question 260
A finance query repeats several nested subqueries and the developer needs one named intermediate result reused in a single statement. An analyst needs each transaction's running total within its account ordered by posting date, not one aggregate per account. Which course of action addresses the stated issues?
- Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use a common table expression to structure the query while validating the actual plan for repeated references; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance.
- Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; do not use a common table expression to structure the query while validating the actual plan for repeated references.
- Use a common table expression to structure the query while validating the actual plan for repeated references; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one.
Explanation
Correct Answer
B:First, a CTE provides readable statement-scoped query structure rather than automatically materializing data. Second, windowed aggregation retains individual rows while calculating a cumulative value.
Incorrect Answers
A:Use RANK or DENSE_RANK according to tie and gap requirements and order deterministically; use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance: a CTE provides readable statement-scoped query structure rather than automatically materializing data.
C:Use SUM as a window aggregate with PARTITION BY and an explicit ordered frame for the running balance; do not use a common table expression to structure the query while validating the actual plan for repeated references: use a common table expression to structure the query while validating the actual plan for repeated references.
D:Use a common table expression to structure the query while validating the actual plan for repeated references; use ROW_NUMBER partitioned by customer and ordered by timestamp plus a stable tie-breaker, then filter to one: windowed aggregation retains individual rows while calculating a cumulative value.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests