Secure, optimize, and deploy database solutions

DP-800 · 75 questions

• A county public-health clinic stores SSN-adjacent beneficiary identifiers that must stay encrypted even from database administrators viewing data pages. Which approach should the SQL AI developer choose?
• A city permits office must protect credit-card-on-file payment tokens in Azure SQL without treating full-disk encryption alone as sufficient. What should the developer implement?
• A 311 operations desk wants resident phone numbers partially obscured on agent screens without rewriting the intake application. Which feature should they configure?
• A housing authority needs caseworkers to retrieve only case rows for their assigned region. Which security feature should the developer implement?
• Courts IT must let clerks query docket summaries while blocking direct access to the underlying evidence metadata tables. What permission pattern should they use?
• A water utility billing app currently uses SQL authentication with passwords in config files. What change best aligns with passwordless secure access?
• County child-services adjacent case tables require a durable record of who read sensitive rows. Which capability should the team enable?
• Emergency management calls an external RAG model endpoint from SQL-related services and must avoid storing API keys in application settings. How should the endpoint be secured?
• A county open-data GraphQL endpoint in production must not allow anonymous clients to explore the full schema. What should operators enforce?
• Transit IT exposes fleet status through Data API builder REST endpoints. Before publishing, what security posture should they apply?
• Courts want Copilot-assisted SQL work against a staging database through an MCP endpoint. How should that MCP endpoint be secured?
• A library system must hide patron email details from most staff screens and also stop branch clerks from seeing other branches’ patrons. Which combination meets both goals?
• City finance uses Always Encrypted on grant disbursement identifiers and needs to understand what remains protected when backups leave the primary region. Which statement is correct?
• A public-health clinic must produce evidence of who accessed encounter tables for compliance reviews. How should SQL auditing be used?
• A permits vendor account was granted dbowner for convenience. What should county IT do to restore least privilege?
• Housing authority overnight batch jobs need to connect to Azure SQL without storing passwords in the job definition. Which approach should they use?
• Courts expose a model endpoint used by the docket API and must ensure only that API’s identity can call it. What should they configure?
• A 311 GraphQL API serves residents publicly but must keep internal priority-score fields out of public queries. What should developers enforce?
• A water utility must harden SCADA-adjacent REST endpoints that read operational SQL data. Which combination is most appropriate?
• County IT allows Copilot to use an MCP server against production SQL for read assistance but must prevent schema changes through that channel. What policy should they apply?
• After citywide query spikes, a transit DBA needs to tune Azure SQL for better parallel query behavior. Which action fits database configuration recommendations?
• Courts clerks concurrently update related docket rows and must choose a transaction isolation approach that preserves consistency under contention. What should the developer evaluate first?
• Permits analysts suspect applicationid lookups are scanning large tables. Which artifact should they inspect first to confirm the access method?
• During overnight 311 enrichment batches, operators need to see which wait types dominate. Which diagnostic approach should they use?
• Housing IT changed a fee-calculation stored procedure and fears query regressions. Which feature should they enable to track performance over time?
• A county public-health Azure SQL database is slow during morning clinic hours, and leaders want a portal view of the queries using the most CPU. Which Azure SQL feature should the SQL AI developer use?
• County clerk cashiers report freezes when the end-of-day close job runs while receipt inserts continue. Which performance issue should the SQL AI developer diagnose first?
• A water utility’s meter-insert job and a balance-update procedure repeatedly abort with deadlock errors during peak billing. What should the SQL AI developer focus on?
• A city library’s circulation database sees frequent reader/writer blocking during evening checkouts. Which isolation-related change should the SQL AI developer recommend?
• After a storm surge, emergency-management queries show large gaps between estimated and actual rows in execution plans on skewed incident tables. What problem should the team investigate?
• County open-data ETL loads large CSV extracts into Azure SQL and DMVs show elevated PAGEIOLATCH waits. What does that signal for the SQL AI developer?
• Parks reservation joins suddenly choose a poor plan after statistics shifts, and staff want the prior good plan restored quickly. Which Query Store capability should they use?
• Transit Azure SQL Query Performance Insight highlights expensive key lookups on vehicleid in active fleet queries. What should the SQL AI developer do next?
• A courts deadlock graph shows two clerk procedures accessing docket and payment tables in opposite order. How should the SQL AI developer reduce deadlocks?
• After migrating a municipal 311 database to a newer Azure SQL tier, the team wants modern cardinality estimation and optimizer behavior. Which configuration should they review?
• Housing waitlist updates under long SERIALIZABLE transactions cause widespread timeout storms for caseworkers. What should the SQL AI developer conclude?
• Permit counter OLTP spikes show high CXPACKET waits while short transactional queries run. Which action fits a careful performance response?
• A public-health blocked-session report shows long lock waits on encounter queries filtered by clinicid with scans. What root cause should the SQL AI developer suspect?
• A city SQL Database Project for permit fees needs automated checks that fee-calculation procedures return expected amounts. Which CI/CD practice should they add?
• County IT wants pipeline confidence that the clinic SQL Database Project deploys cleanly before API smoke checks. Which approach should they implement?
• The permits office needs fee-schedule lookup rows versioned with the schema so every environment gets the same reference values. Where should that data live?
• Housing authority developers are creating a modern SQL Database Project for case-management schema in CI. Which project style should they choose?
• Courts IT must put the SQL Database Project under Git with protected branches before clerks merge docket schema changes. What should they configure?
• 311 developers need a safe way to change request-table schema without committing straight to main. Which workflow should they follow?
• Two water-utility engineers edited the same meter table DDL on separate branches and Git reports a merge conflict. What should they do?
• Library CI pipelines need SQL connection secrets for deploy jobs. Where should those secrets be stored?
• Emergency-management production SQL has objects that are not in the Database Project model after hotfixes. What should the team detect and address?
• Open-data engineers add additive columns for new civic datasets and must ship them through the SQL Database Project. What is the correct sequence?
• Transit wants schema PRs that touch dbo objects to require designated reviewers before release. Which pipeline control should they use?
• The county clerk release pipeline must pause for human approval before a production dacpac deploy. Which control should they configure?
• A county public-health clinic SQL Database Project repo still allows engineers to push straight to main and skip review. Which pipeline control should the SQL AI developer enforce?
• Parks and recreation wants every pull request to prove the SDK-style SQL Database Project still builds before merge. Which CI step should they add?
• County finance wants the release pipeline to prove who is allowed to trigger production SQL Database Project deploys. Which control should they configure?
• A city’s 311 team needs neighborhood lookup rows available in every environment after SQL Database Project deploy. How should they ship that static data?
• Housing authority QA wants proof that Row-Level Security still filters case rows after each Database Project deploy to test. What should their integration suite assert?
• Courts operations discover hotfix DDL applied directly in production that never updated the SQL Database Project model. How should the pipeline respond to that drift?
• Water utility engineers open a pull request where one branch renames a meter column and another updates a dependent view that still references the old name. What is the correct conflict-resolution approach?
• County IT rotates deployment credentials used by GitHub Actions for SQL Database Project releases. Where should the updated secrets live?
• Transit wants weekend schema deploys to require on-call sign-off even when weekday approvals already exist. Which pipeline design fits?
• A public library SQL Database Project issues card numbers with a SEQUENCE and CHECK constraints. What should unit tests in the project cover?
• The permits bureau still maintains a mix of legacy and SDK-style SQL Database Projects and wants one standard. Which decision should the SQL AI developer recommend?
• City 311 wants Data API builder to expose request entities so mobile clients can page results and filter by status. Which DAB configuration focus is correct?
• Housing caseworkers need GraphQL queries that navigate from a case to related client records through Data API builder. What should developers configure?
• Courts expose infrequently changing statute lookup entities through Data API builder REST. How should they improve read performance for those lookups?
• An open-data portal must publish sanitized dataset metadata through Data API builder without exposing base tables that still hold PII. What should they expose?
• Transit needs a rider-alerts HTTP API backed by Azure SQL. Which deployment approach aligns with DP-800 Azure integration skills?
• Public-health API owners want telemetry on SQL-backed endpoints, including traces and queryable logs. Which Azure monitoring pairing should they recommend?
• Water utilities need meter reading changes to flow continuously into an Azure Function for downstream processing. Which SQL change technology fits best?
• Emergency management needs near-real-time incident table updates to fan out to operations dashboards. Which Azure SQL integration feature should they enable?
• The clerk’s office must incrementally sync docket headers to a search index without reloading the entire table each run. Which feature should they choose?
• Parks and recreation wants an Azure Function to react automatically when new reservation rows land in Azure SQL. Which binding approach should they implement?
• A city library wants branch managers notified when fine balances in SQL cross a configured threshold. Which Azure integration should they use?
• 311 product owners want Data API builder clients to run free-text search across request summary fields. What should developers configure?
• Housing GraphQL clients are failing intermittently and operations needs proactive notice when error rates spike. What should they configure?
• Permits applicants need fee quotes through an API, but the bureau does not want clients writing ad-hoc SQL against fee tables. How should they expose the logic via Data API builder?