Question 670
Issue 1: A team exposes a private products table through Data API builder but commits an unrestricted production connection string in the public configuration repository. Issue 2: A configured Data API builder entity includes internal audit columns in the REST response even though external users need only public product fields. Issue 3: A Data API builder endpoint returns too many rows per request and consumers accidentally download the full database table. Which complete response addresses the three issues in order?
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; update and validate the entity's published REST path and coordinate a versioned client migration; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
- Update and validate the entity's published REST path and coordinate a versioned client migration; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
- Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior.
Explanation
Correct Answer
D:For issue 1, Data API builder deployment settings must not expose database credentials or overprivileged access. For issue 2, API projections should expose only explicitly authorized data rather than mirroring an entire table. For issue 3, API pagination bounds response size and reduces accidental unbounded query load.
Incorrect Answers
A:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and update and validate the entity's published REST path and coordinate a versioned client migration: aPI pagination bounds response size and reduces accidental unbounded query load.
B:Move sensitive connection settings to protected deployment configuration and scope DAB database privileges; update and validate the entity's published REST path and coordinate a versioned client migration; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior: aPI projections should expose only explicitly authorized data rather than mirroring an entire table.
C:Update and validate the entity's published REST path and coordinate a versioned client migration; restrict entity fields and permissions in DAB configuration and validate the returned schema as a client role; and configure supported pagination and result limits with appropriate filtering and test expensive-query behavior: data API builder deployment settings must not expose database credentials or overprivileged access.
Question 671
A partitioned sales table still scans all partitions because analysts wrap the date column in an expression that prevents useful elimination. Which action best addresses the database requirement?
- Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation.
- Rewrite predicates to use compatible date ranges and validate partition pruning in the actual execution plan.
- Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead.
- Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process.
Explanation
Correct Answer
B:A partitioning strategy only reduces reads when the optimizer can identify the relevant partitions.
Incorrect Answers
A:Align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation: align source and destination partition-switch schema and index requirements before retrying the controlled metadata operation: efficient partition switching depends on structurally compatible tables and aligned boundaries.
C:Reject unnecessary partitioning and inspect join plans and indexes for the tiny reference table instead: partitioning adds management overhead and is not a substitute for addressing an actual join or index problem.
D:Partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process: partition by an appropriate date range and test partition elimination plus a policy-aligned data retirement process: range partitioning can support time-based maintenance when predicates and partition boundaries are designed together.
Question 672
A team grants a view SELECT permission and assumes this alone makes rows from every underlying table safe for all tenants. Which action best addresses the database requirement?
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements.
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review.
- Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Choose an appropriate table-valued function and validate its plan behavior and input filtering.
Explanation
Correct Answer
C:View access alone is not proof of full tenant isolation without evaluating underlying access paths.
Incorrect Answers
A:Use a deterministic scalar function or expression appropriate to the platform and performance requirements: 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.
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; Evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
D:Choose an appropriate table-valued function and validate its plan behavior and input filtering: choose an appropriate table-valued function and validate its plan behavior and input filtering: a table-valued function supplies a relational row set that callers can compose with other queries.
Question 673
An application team must address both issues before release. A 100-page equipment manual is embedded as one vector, and searches for individual error codes return unrelated chapter summaries. A chunking routine breaks contractual clauses midway through sentences and the retrieved text no longer contains the exceptions needed to answer. What is the most appropriate plan for this situation?
- Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness.
- Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap.
- Store stable source provenance and effective-version fields alongside every embedded chunk; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness.
- Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; do not Split the manual into coherent section-level chunks with source IDs and test retrieval granularity.
Explanation
Correct Answer
A:First, smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding. Second, chunks must preserve enough context for accurate interpretation, not just fit a token limit.
Incorrect Answers
B:Split the manual into coherent section-level chunks with source IDs and test retrieval granularity; Tune chunk size and overlap using measured recall, context quality and storage cost rather than maximizing overlap: chunks must preserve enough context for accurate interpretation, not just fit a token limit.
C:Store stable source provenance and effective-version fields alongside every embedded chunk; Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness: smaller meaningful chunks can preserve local relevance that is diluted by a single document-wide embedding.
D:Chunk at natural section or sentence boundaries with controlled overlap and validate clause completeness; do not Split the manual into coherent section-level chunks with source IDs and test retrieval granularity: split the manual into coherent section-level chunks with source IDs and test retrieval granularity.
Question 674
Issue 1: A team owns a highly sensitive payroll schema but its pull requests are approved by engineers unfamiliar with the data access rules. Issue 2: A feature branch for a database migration is months behind main and overwrites newer constraints when merged. Issue 3: A deployment pipeline executes on every repository commit, including experimental branches with no release authorization. Which complete response addresses the three issues in order?
- Use CODEOWNERS and required security or payroll data-owner review for changes touching those database objects; Rebase or reconcile against current source and inspect the generated schema diff before deployment; and restrict production triggers to approved branches or release tags with explicit gated authorization.
- Use CODEOWNERS and required security or payroll data-owner review for changes touching those database objects; Rebase or reconcile against current source and inspect the generated schema diff before deployment; and enforce branch protection and required reviewer approval for the SQL project before merge.
- Use CODEOWNERS and required security or payroll data-owner review for changes touching those database objects; enforce branch protection and required reviewer approval for the SQL project before merge; and restrict production triggers to approved branches or release tags with explicit gated authorization.
- Enforce branch protection and required reviewer approval for the SQL project before merge; Rebase or reconcile against current source and inspect the generated schema diff before deployment; and restrict production triggers to approved branches or release tags with explicit gated authorization.
Explanation
Correct Answer
A:For issue 1, domain-specific reviewers reduce the chance of approving changes that break business or privacy controls. For issue 2, keeping migration branches current reduces accidental reversal of already approved database controls. For issue 3, deployment triggers must reflect approved lifecycle boundaries, not arbitrary source events.
Incorrect Answers
B:Use CODEOWNERS and required security or payroll data-owner review for changes touching those database objects; Rebase or reconcile against current source and inspect the generated schema diff before deployment; and enforce branch protection and required reviewer approval for the SQL project before merge: deployment triggers must reflect approved lifecycle boundaries, not arbitrary source events.
C:Use CODEOWNERS and required security or payroll data-owner review for changes touching those database objects; enforce branch protection and required reviewer approval for the SQL project before merge; and restrict production triggers to approved branches or release tags with explicit gated authorization: keeping migration branches current reduces accidental reversal of already approved database controls.
D:Enforce branch protection and required reviewer approval for the SQL project before merge; Rebase or reconcile against current source and inspect the generated schema diff before deployment; and restrict production triggers to approved branches or release tags with explicit gated authorization: domain-specific reviewers reduce the chance of approving changes that break business or privacy controls.
Question 675
A security reviewer wants evidence of failed login attempts but a report shows only successful query calls. What is the most defensible technical decision?
- Configure appropriate SQL auditing and retain identity, action and timestamp evidence with protected access.
- Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring.
- Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering.
- Review captured fields and apply data minimization, access restrictions and retention controls before continuing collection.
Explanation
Correct Answer
B:Query activity alone cannot reveal credential guessing or other failed authentication events.
Incorrect Answers
A: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: database audit events preserve accountability for protected data operations beyond generic application status logs.
C:Protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering: protect the audit sink with appropriate retention and independent access controls to reduce unauthorized tampering: audit evidence is less trustworthy when subjects of the audit can alter or delete it unchecked; Enable and validate relevant authentication audit event coverage and correlate it with centralized security monitoring.
D: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: observability must not turn sensitive business input into a second uncontrolled data store.
Question 676
An application team must address both issues before release. An application login is granted sysadmin because it needs to read one approved product view and execute a single order procedure. A team grants SELECT on a view, but an analyst can still query the sensitive base table through a separate broad role. What is the most appropriate plan for this situation?
- Grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles; review all effective role memberships and revoke the unauthorized base-table access before relying on the view as a boundary.
- Review all effective role memberships and revoke the unauthorized base-table access before relying on the view as a boundary; do not grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles.
- Explicitly review and grant supported object or schema permissions for each authorized role; review all effective role memberships and revoke the unauthorized base-table access before relying on the view as a boundary.
- Grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles; assign a constrained service identity with the minimum insert and procedure rights needed for the job.
Explanation
Correct Answer
A:First, object-level privileges satisfy specific application needs without broad database administration authority. Second, permissions are cumulative through roles; hiding a column in one view does not revoke other direct paths.
Incorrect Answers
B:Review all effective role memberships and revoke the unauthorized base-table access before relying on the view as a boundary; do not grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles: grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles.
C:Explicitly review and grant supported object or schema permissions for each authorized role; review all effective role memberships and revoke the unauthorized base-table access before relying on the view as a boundary: object-level privileges satisfy specific application needs without broad database administration authority.
D:Grant narrowly scoped SELECT and EXECUTE rights to the intended objects and remove unnecessary elevated server roles; assign a constrained service identity with the minimum insert and procedure rights needed for the job: permissions are cumulative through roles; hiding a column in one view does not revoke other direct paths.
Question 677
Issue 1: A SQL stored procedure needs to call an approved external language model service but developers attempt to use unsupported network calls from ordinary SELECT expressions. Issue 2: An external model API responds intermittently with throttling, and SQL code retries indefinitely while holding an open business transaction. Issue 3: A model endpoint requires a structured JSON request but a stored procedure concatenates user text directly into raw JSON, breaking on quotation marks. Which complete response addresses the three issues in order?
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; Bound and back off retries outside long-running critical transactions and handle model errors explicitly; and Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; and Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
- Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; Bound and back off retries outside long-running critical transactions and handle model errors explicitly; and validate HTTP outcomes and the returned JSON structure before extracting and storing generated content.
- Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; Bound and back off retries outside long-running critical transactions and handle model errors explicitly; and Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint.
Explanation
Correct Answer
A:For issue 1, sQL-managed external REST invocation has a defined interface and security requirements for outbound model requests. For issue 2, external inference availability should not monopolize database locks or create uncontrolled request storms. For issue 3, structured JSON construction avoids malformed payloads and accidental text-to-syntax injection.
Incorrect Answers
B:Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; and Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint: external inference availability should not monopolize database locks or create uncontrolled request storms.
C:Use the supported sp_invoke_external_rest_endpoint procedure with approved credentials and safe request handling; Bound and back off retries outside long-running critical transactions and handle model errors explicitly; and validate HTTP outcomes and the returned JSON structure before extracting and storing generated content: structured JSON construction avoids malformed payloads and accidental text-to-syntax injection.
D:Validate HTTP outcomes and the returned JSON structure before extracting and storing generated content; Bound and back off retries outside long-running critical transactions and handle model errors explicitly; and Build valid JSON with supported SQL JSON functions and properly typed request fields before invoking the endpoint: sQL-managed external REST invocation has a defined interface and security requirements for outbound model requests.
Question 678
Issue 1: A reporting team repeats the same approved customer-eligibility SELECT in twelve applications and wants one centrally maintained read definition. Issue 2: An analytics pipeline needs to reuse a parameterized set of invoice rows joined into several different SELECT statements. Issue 3: A team grants a view SELECT permission and assumes this alone makes rows from every underlying table safe for all tenants. Which complete response addresses the three issues in order?
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review; choose an appropriate table-valued function and validate its plan behavior and input filtering; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- 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; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Use a deterministic scalar function or expression appropriate to the platform and performance requirements; choose an appropriate table-valued function and validate its plan behavior and input filtering; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary.
- Create a view exposing only approved columns and predicates with appropriate permissions and schema review; choose an appropriate table-valued function and validate its plan behavior and input filtering; and use a deterministic scalar function or expression appropriate to the platform and performance requirements.
Explanation
Correct Answer
A:For issue 1, views centralize query logic and can restrict presentation but do not replace all underlying access security. For issue 2, a table-valued function supplies a relational row set that callers can compose with other queries. For issue 3, view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Incorrect Answers
B: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; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: a table-valued function supplies a relational row set that callers can compose with other queries.
C:Use a deterministic scalar function or expression appropriate to the platform and performance requirements; choose an appropriate table-valued function and validate its plan behavior and input filtering; and evaluate ownership chaining and implement explicit row-level and source permissions appropriate to the tenant boundary: views centralize query logic and can restrict presentation but do not replace all underlying access security.
D:Create a view exposing only approved columns and predicates with appropriate permissions and schema review; choose an appropriate table-valued function and validate its plan behavior and input filtering; and use a deterministic scalar function or expression appropriate to the platform and performance requirements: view access alone is not proof of full tenant isolation without evaluating underlying access paths.
Question 679
An application team must address both issues before release. Users search a knowledge base for exact invoice numbers, error codes and identifiers, but a vector-only index ignores rare literal tokens. A search system must match inflected words and text terms, but developers use SQL LIKE with leading wildcards over millions of rows. What is the most appropriate plan for this situation?
- Implement or combine full-text search for exact terms and test identifier recall against representative queries; design full-text ranking and evaluate whether semantic reranking is needed for the user's queries.
- Implement or combine full-text search for exact terms and test identifier recall against representative queries; evaluate SQL full-text indexing and supported CONTAINS or related predicates for the intended language behavior.
- Evaluate appropriate exact-match filtering or tokenization settings for structured identifiers; evaluate SQL full-text indexing and supported CONTAINS or related predicates for the intended language behavior.
- Evaluate SQL full-text indexing and supported CONTAINS or related predicates for the intended language behavior; do not implement or combine full-text search for exact terms and test identifier recall against representative queries.
Explanation
Correct Answer
B:First, lexical search can preserve token matching that semantic embeddings may not rank highly. Second, full-text indexes support token-based search at scale more effectively than indiscriminate wildcard scans.
Incorrect Answers
A:Implement or combine full-text search for exact terms and test identifier recall against representative queries; design full-text ranking and evaluate whether semantic reranking is needed for the user's queries: full-text indexes support token-based search at scale more effectively than indiscriminate wildcard scans.
C:Evaluate appropriate exact-match filtering or tokenization settings for structured identifiers; evaluate SQL full-text indexing and supported CONTAINS or related predicates for the intended language behavior: lexical search can preserve token matching that semantic embeddings may not rank highly.
D:Evaluate SQL full-text indexing and supported CONTAINS or related predicates for the intended language behavior; do not implement or combine full-text search for exact terms and test identifier recall against representative queries: implement or combine full-text search for exact terms and test identifier recall against representative queries.
Question 680
A report needs distinct employee IDs from two sources, but the developer uses UNION ALL and counts duplicates as separate people. A product search assumes ORDER BY inside a subquery guarantees the final result order without an outer ORDER BY. Which course of action addresses the stated issues?
- Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters.
- Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters.
- Apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; do not choose UNION or explicit deduplication for the requested unique population after checking key equivalence.
- Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals.
Explanation
Correct Answer
B:First, uNION removes duplicate result rows whereas UNION ALL preserves them. Second, relational result order is not guaranteed without ordering at the final query boundary.
Incorrect Answers
A:Normalize timestamps to a common time basis and apply explicit business time-zone conversion for the report; apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters: uNION removes duplicate result rows whereas UNION ALL preserves them.
C:Apply ORDER BY to the final result query with a stable tie-breaker when output ordering matters; do not choose UNION or explicit deduplication for the requested unique population after checking key equivalence: choose UNION or explicit deduplication for the requested unique population after checking key equivalence.
D:Choose UNION or explicit deduplication for the requested unique population after checking key equivalence; Preaggregate the relevant child data or use correct join keys and cardinality checks before computing totals: relational result order is not guaranteed without ordering at the final query boundary.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests