Free CompTIA DataSys+ DS0-002 Full-Length Practice Exam: 90 Questions

This free full-length CompTIA DataSys+ DS0-002 practice exam includes 90 original IT Mastery questions across the exam domains.

Use these questions for self-assessment, scope review, and deciding what to drill next.

Count note: this page uses the full-length practice count maintained in the Mastery exam catalog. Some certification vendors publish total questions, scored questions, duration, or unscored/pretest-item rules differently; always confirm exam-day rules with the sponsor.

Exam snapshot

• Exam route: CompTIA DataSys+ DS0-002
• Practice-set question count: 90
• Time limit: 90 minutes
• Practice style: mixed-domain diagnostic run with answer explanations

Full-length exam mix

Use this as one diagnostic run. IT Mastery gives you timed mocks, topic drills, analytics, code-reading practice where relevant, and full practice.

Practice questions

Questions 1-25

Question 1

Topic: Data and Database Security

A company runs an OLTP database containing PII in a cloud VPC. The web application, nightly ETL server, standby replica, and DBAs must continue connecting; DBAs already use a VPN-connected jump host. Which recommendation BEST reduces exposure without blocking required database operations?

Options:

• A. Place the database on a private subnet and allowlist only required sources.

• B. Move the database to a perimeter network with the web servers.

• C. Keep the public listener and require longer database passwords.

• D. Block all inbound database connections except DBA workstation access.

Best answer: A

Explanation: The core concept is attack surface reduction without disrupting legitimate database paths. For a database containing PII, the public listener is unnecessary if all required clients can reach it through private networking or the VPN jump host. Placing the database in a private subnet and allowlisting only the application tier, ETL server, standby replica, and administrative jump host limits who can attempt connections while preserving the stated workload, integration, replication, and admin requirements.

A strong recommendation should reduce exposure at the network boundary and still support required database operations. Password changes alone do not remove public reachability, and overly broad blocking would interrupt business functions.

• Password-only control reduces credential risk but leaves the database port exposed to the internet.
• DBA-only access ignores required application, ETL, and replica connections.
• Perimeter placement increases exposure by placing the database closer to internet-facing systems instead of isolating it.

Question 2

Topic: Database Fundamentals

A DBA needs to correct a status value for 1.8 million orders rows after a business rule change. The table is under normal OLTP load, the correction must be consistent with existing customers relationships, and the maintenance window is short. Which approach is the BEST professional decision?

Options:

• A. Run one set-based UPDATE with a joined predicate

• B. Loop through each order row in an application script

• C. Disable foreign keys, update rows, and re-enable them

• D. Export the table, edit the file, and reimport it

Best answer: A

Explanation: Set-based logic is preferred when the same relational operation applies to many rows. In this case, a single UPDATE using a predicate and join to customers expresses the full correction as one database operation. The optimizer can choose an efficient access path, locks and logging are handled by the database engine, and the join keeps the change tied to existing relational rules. A row-by-row loop adds unnecessary round trips and procedural overhead. Export/reimport increases operational risk, and disabling constraints weakens integrity during the change. The key distinction is to describe what set of rows must change, not to manually process each row one at a time.

• Application loop adds procedural overhead and usually performs worse for a large uniform correction.
• Export and reimport creates avoidable downtime and integrity risk for an OLTP table.
• Disabling foreign keys ignores the stated need to keep the correction consistent with existing relationships.

Question 3

Topic: Database Fundamentals

An operations team reports that a nightly audit for active customers with no orders suddenly returns zero rows after a legacy import. The Orders.customer_id column is nullable, and the report must not change table definitions or miss customers who truly have no matching order. The current query is:

SELECT c.customer_id
FROM Customers c
WHERE c.status = 'Active'
  AND c.customer_id NOT IN (
      SELECT o.customer_id
      FROM Orders o
  );

Which is the BEST professional decision?

Options:

• A. Use a LEFT JOIN with o.customer_id <> c.customer_id.

• B. Change the query to an INNER JOIN on customer_id.

• C. Rewrite the filter as a correlated NOT EXISTS predicate.

• D. Keep NOT IN and add DISTINCT to the subquery.

Best answer: C

Explanation: This is an anti-join problem: the report needs active customers for whom no matching order exists. NOT IN is risky when the subquery can return NULL. If Orders.customer_id includes NULL, comparisons against the subquery can evaluate to unknown, causing the predicate to exclude rows unexpectedly. A correlated NOT EXISTS checks each customer against matching order rows and ignores unrelated NULL values because the equality condition only succeeds for actual matches. This meets the operational requirement without changing the schema or table data.

The key takeaway is to prefer NOT EXISTS for absence checks when the subquery column may contain NULL.

• Inner join trap returns customers that have matching orders, which is the opposite of the audit requirement.
• Inequality join trap compares nonmatching rows and can create duplicates or miss the intended absence condition.
• DISTINCT trap removes duplicate values, but one remaining NULL can still make NOT IN unsuitable.

Question 4

Topic: Data Integration

A DBA is validating a new nightly partner feed before allowing an ELT job to load it into staging. The intake ticket lists the assumptions that must be confirmed before acquisition is approved. Which validation checklist best matches the ticket?

Exhibit: Intake ticket

Source: partner SFTP /exports/customer_delta.json
Expected classification: Restricted PII
Expected format: JSON, UTF-8, one object per line
Expected schema: customer_id int, email string, updated_at ISO 8601
Connectivity: SFTP over SSH using service account svc_ingest

Options:

• A. Verify classification, parse JSON Lines, validate schema, and test SFTP access

• B. Mask email values and skip source connectivity testing

• C. Compare row counts after staging and rebuild target indexes

• D. Validate CSV headers and test an HTTPS API token

Best answer: A

Explanation: Acquisition validation should prove that the incoming source matches the documented intake assumptions before the pipeline trusts or loads the data. In this ticket, the checks must cover four areas: data classification as Restricted PII, JSON Lines format with UTF-8 encoding, the expected fields and data types, and SFTP connectivity using the named service account and path. These checks reduce the risk of loading the wrong data, mishandling sensitive records, or failing at runtime due to access or schema drift. Post-load tuning or transformation controls may still be needed, but they do not replace acquisition validation at the source boundary.

• Post-load checks can help reconciliation, but row counts and index work do not confirm source classification, format, schema, or connectivity.
• Wrong interface fails because CSV headers and an HTTPS API token conflict with the JSON Lines over SFTP ticket.
• Masking only may protect email values, but skipping connectivity testing leaves the acquisition path unverified.

Question 5

Topic: Database Fundamentals

A DBA needs to automate a nightly task that checks table growth and records results in an admin schema. Security requirements prohibit embedding a DBA password in a script and prohibit opening database access from a general-purpose automation subnet. The task should use only the permissions needed for the checks. Which scripting approach best meets these requirements?

Options:

• A. ORM-based job using the application’s full production account

• B. Shell script on a shared automation host using the DBA account

• C. Server-side scheduled procedure using a least-privileged service identity

• D. Python script on a DBA workstation using saved credentials

Best answer: C

Explanation: For database administration automation, the safest scripting approach should minimize credential exposure, network exposure, and privileges. A server-side scheduled procedure or database job can run close to the database, avoid opening connectivity from unrelated systems, and use a dedicated service identity with only the permissions needed to read growth metadata and write results. This also avoids tying automation to a personal DBA password. Client-side scripts can be valid for some tasks, but they need secure credential handling and network access; the stem explicitly restricts both. The key takeaway is to choose the runtime location and identity that reduce the exposed attack surface while still supporting the administrative task.

• Saved workstation credentials fail because they expose a personal or reusable secret and depend on a user-managed endpoint.
• Shared automation host fails because it requires opening access from a prohibited subnet and misuses a DBA account.
• Application account reuse fails because production application credentials usually have broader permissions than this admin check needs.

Question 6

Topic: Database Fundamentals

A DBA must produce a report from the following schema without changing tables or stored data.

Exhibit: Schema and requirement

Customers(customer_id PK, status, name)
Orders(order_id PK, customer_id FK, order_ts, amount)

Need:
- One row per active customer
- Include active customers with no orders
- Show the most recent order when one exists
- If order_ts ties, choose the highest order_id

Which query pattern should the DBA use?

Options:

• A. Create a summary table and query only that table

• B. Use an INNER JOIN and filter on MAX(order_ts)

• C. Rank orders in a CTE, then LEFT JOIN the top row

• D. Add an is_latest column to Orders and filter it

Best answer: C

Explanation: The requirement is a query-shaping problem, not a schema-change problem. A common pattern is to rank each customer’s orders using ROW_NUMBER() with PARTITION BY customer_id and an ORDER BY order_ts DESC, order_id DESC. Filtering the ranked result to rn = 1 returns one deterministic latest order per customer, including the tie-breaker. Joining that result back to active customers with a LEFT JOIN keeps customers who have no matching orders, with order columns returned as null. The key is combining a window function for “one best row per group” with an outer join for row preservation.

• Inner join trap drops active customers who have no orders and may return duplicates when timestamps tie.
• Latest flag trap requires changing stored data and maintaining a derived value.
• Summary table trap adds unnecessary schema and data maintenance for a report that can be produced by query logic.

Question 7

Topic: Data and Database Security

A DBA reviews database access after a new reporting service is deployed. The security team says access should no longer depend on being inside the corporate network.

Exhibit: Current database access rule

Resource: reporting-db
Allowed source: 10.20.0.0/16 (corporate/VPN networks)
Authentication: shared SQL login used by reporting jobs
Device/user posture: not checked
Authorization: broad read access to reporting schema
Logging: connection success/failure only

Which next action best applies Zero Trust architecture to this database access?

Options:

• A. Require identity-based access with least privilege and posture checks

• B. Allow only VPN clients to reach the database port

• C. Move the database to a different private subnet

• D. Keep the subnet rule and rotate the shared SQL password

Best answer: A

Explanation: Zero Trust removes implicit trust based on network location. In the exhibit, access is granted mainly because the request comes from a corporate or VPN subnet, and the reporting jobs use a shared SQL login with broad read permissions. A better design verifies the workload or user identity, checks required context such as device or service posture, grants only the permissions needed for the reporting task, and records enough activity for monitoring. Network controls still help reduce exposure, but they are not sufficient as the primary trust decision.

The key takeaway is to make every database access request explicitly verified and narrowly authorized, even from internal networks.

• Password rotation only improves credential hygiene but still leaves access based on network location and a shared identity.
• VPN-only access changes the network path but continues to treat being on the network as the main trust signal.
• Private subnet move may reduce exposure, but it does not add identity verification, posture evaluation, or least privilege.

Question 8

Topic: Database Fundamentals

A web application uses an ORM to display an order history page. Since a recent code change, database CPU and query count have increased during peak traffic. A DBA captures this SQL pattern for one page load:

SELECT order_id, order_date, customer_id
FROM orders
WHERE customer_id = ?;

-- Repeated once for each returned order
SELECT line_id, product_id, quantity, price
FROM order_lines
WHERE order_id = ?;

Which implementation choice would best reduce unnecessary database load?

Options:

• A. Increase the database connection pool size

• B. Add client-side pagination after fetching all rows

• C. Use eager loading for order lines

• D. Move the repeated query into a stored procedure

Best answer: C

Explanation: The captured pattern shows an ORM-related N+1 query problem: one query retrieves the parent rows, then a separate query runs for each parent to retrieve related child rows. This creates unnecessary database load because query count grows with the number of orders on the page. Configuring the ORM or query to eager load the related order lines, often as a join or batched related-data fetch, reduces round trips and total statement execution. The key is to change how related data is retrieved, not simply give the application more connections or wrap the same repeated work differently.

• More connections may hide wait time temporarily, but it does not reduce the number of SQL statements or CPU work.
• Client-side pagination after fetching all rows still transfers and processes unnecessary data before trimming the result.
• Stored procedure wrapper can centralize logic, but repeating the same lookup per order remains inefficient unless the access pattern changes.

Question 9

Topic: Data Integration

A database team is designing ingestion for sensor readings from manufacturing equipment. Operators need dashboard updates within 5 seconds of each reading so they can stop a line before parts are damaged. Historical reports can still run from the warehouse after the data is stored. Which ingestion design best fits the timing requirement?

Options:

• A. Use a streaming source with continuous event ingestion

• B. Load hourly CSV exports with an ETL job

• C. Request weekly API extracts from the equipment system

• D. Run a nightly database dump into the warehouse

Best answer: A

Explanation: Streaming data sources continuously produce events that must be ingested and processed as they arrive or within a short latency target. In this scenario, each sensor reading must reach an operational dashboard within 5 seconds, so the ingestion design should support continuous event flow rather than scheduled file or extract processing. The warehouse can still store the data for later reporting, but that does not change the operational ingestion need. Non-streaming sources, such as hourly files, nightly dumps, or weekly extracts, are better for batch analytics when delayed availability is acceptable.

• Hourly files miss the 5-second update target because the data is only available after each scheduled export.
• Nightly dumps are useful for bulk transfers or reporting but are too delayed for operational monitoring.
• Weekly extracts are non-streaming and fit periodic analysis, not immediate line-stop decisions.

Question 10

Topic: Database Deployment

A DBA is preparing deployment documentation for a schema change to a production OLTP database. The compliance team will not approve the change until the required SOP evidence is attached.

Exhibit: Compliance note

Deployment: Add customer_status column
Required evidence:
- Approved maintenance window
- Pre-change backup verification
- Step-by-step implementation procedure
- Rollback procedure if validation fails
- Post-change validation sign-off

Which SOP documentation best satisfies the requirement?

Options:

• A. Data dictionary field definition

• B. Entity relationship diagram update

• C. Third-party service onboarding SOP

• D. Change management and maintenance SOP

Best answer: D

Explanation: SOP documentation should match the operational control being audited. The exhibit is not asking whether the new column is logically valid or how it relates to other entities. It requires evidence that the production change will be performed under an approved process: scheduled maintenance, backup verification, implementation steps, rollback planning, and post-change sign-off. Those items belong in a change management and maintenance SOP because they define repeatable procedures for safely modifying a production database while meeting organizational compliance expectations. Schema artifacts can support the change, but they do not replace the required operational SOP.

• ERD update helps document relationships, but it does not prove maintenance approval, rollback planning, or validation sign-off.
• Data dictionary defines the new column, but it does not document the controlled production-change procedure.
• Vendor onboarding may support third-party compliance, but the exhibit is about an internal database deployment change.

Question 11

Topic: Data Integration

A DBA must choose an exchange format for a new supplier order feed. The integration team wants the format that best preserves the visible structure with minimal transformation.

Exhibit: Feed requirements

Which data format is the best choice?

Options:

• A. XML

• B. JSON

• C. Delimited flat file

• D. Fixed-width flat file

Best answer: B

Explanation: JSON is the best fit when data is semistructured, hierarchical, and exchanged through a REST API. The order header can be represented as an object, repeating line items as an array, and optional fields can be omitted or included without forcing every record into the same column layout. XML can also represent hierarchy, but the exhibit does not require XML-specific features such as XSD validation, document markup, or mixed content. Flat files work best for simple, tabular records with a predictable set of columns. They would require flattening or repeating header data to represent multiple line items per order.

• XML validation trap fails because the exhibit explicitly says there is no XSD or document markup requirement.
• Delimited file trap fails because repeating line items and optional fields do not fit cleanly into one stable row structure.
• Fixed-width trap fails because fixed positions are poorly suited to variable nested content and optional attributes.

Question 12

Topic: Database Deployment

A company is deploying a custom order-management application. The database must store persistent relational OLTP data, support schema and index administration by the DBA team, and meet a provider-managed backup and patching requirement. The team does not want to manage guest operating systems or database engine installation. Which deployment approach is the BEST fit?

Options:

• A. Install a database engine on IaaS virtual machines

• B. Use a managed relational DBaaS offering

• C. Use a SaaS order-management application

• D. Run the database in stateless application containers

Best answer: B

Explanation: A managed relational DBaaS approach best matches the division of responsibilities in the scenario. The workload needs persistent relational OLTP storage and DBA-level control over database objects such as schemas and indexes, but the organization wants the provider to handle lower-level administration such as engine installation, patching, and managed backups. IaaS would provide persistence and flexibility, but it would leave more OS and database platform administration with the customer. SaaS would reduce administration further, but it would not fit a custom application requiring DBA control over the database design.

• IaaS database hosting fails because the team would still manage the guest OS and database engine installation.
• SaaS application use fails because it replaces the custom application/database control model rather than hosting its database.
• Stateless containers fail because they do not by themselves satisfy persistent relational database requirements.

Question 13

Topic: Data and Database Security

A company is moving a customer database to a managed cloud database service. The table contains PII for customers in Country A, and the legal team states that this PII must remain within Country A. The application also needs high availability, and management wants a read replica for reporting. Which decision is the BEST professional recommendation?

Options:

• A. Deploy the primary in Country A and a replica in another country

• B. Deploy primary and replicas only in Country A regions

• C. Store backups in the lowest-cost region available

• D. Deploy globally and rely only on encryption at rest

Best answer: B

Explanation: Regional data residency requirements should directly affect cloud region, replication, and backup placement decisions. If PII must remain within Country A, copies of that data should not be replicated, backed up, or stored in regions outside the approved jurisdiction unless the legal requirement explicitly allows it. High availability can still be supported by using multiple availability zones or approved regions within Country A, depending on the provider’s regional design. Encryption is important, but it does not by itself satisfy a residency rule that controls physical or jurisdictional data location.

The key takeaway is to treat replicas and backups as regulated copies of the same sensitive data, not as exceptions to residency requirements.

• Cross-border replica fails because a read replica is still a copy of the regulated PII.
• Encryption-only approach fails because encryption protects confidentiality but does not change where the data resides.
• Lowest-cost backups fail because backup location must also comply with the stated residency requirement.

Question 14

Topic: Data Integration

A DBA team supports a nightly ELT pipeline that loads customer data from a SaaS API into a reporting warehouse. The source occasionally adds optional fields or changes numeric fields to strings. Downstream reports require stable column types, and auditors need evidence showing how records were transformed and why any records failed. Which quality-control action is the BEST professional decision?

Options:

• A. Manually sample failed records after each run

• B. Add version-controlled mappings, validation checks, and run logs

• C. Grant the pipeline account broader write permissions

• D. Increase the pipeline schedule to run hourly

Best answer: B

Explanation: Pipeline quality control should make integration behavior observable and repeatable. In this scenario, the risk is not mainly speed or permissions; it is uncontrolled source changes that can break warehouse types and reporting trust. Version-controlled mappings define expected fields and transformations, validation checks detect schema and data-type problems before or during loading, and run logs provide support evidence for failures and audit review. This combination helps the DBA team maintain the pipeline over time without relying on memory or manual inspection.

The key takeaway is to control and document integration behavior, then record evidence from each run.

• More frequent runs do not prove that mappings, data types, or transformation rules are correct.
• Manual sampling may help investigation, but it is inconsistent and not maintainable as the primary control.
• Broader permissions increase operational and security risk without addressing schema drift or audit evidence.

Question 15

Topic: Data and Database Security

A DBA is reviewing a proposed order database for an e-commerce application. The design stores customer names, order totals, full payment card numbers, expiration dates, and a processor authorization code for recurring billing. The database will run on a managed DBaaS platform and feed a nightly reporting job. Which decision is the BEST professional recommendation before production?

Options:

• A. Rely on DBaaS compliance and keep the schema unchanged

• B. Grant analysts read access to full card numbers for reconciliation

• C. Treat it as GDPR-only because customer names are stored

• D. Classify it as PCI DSS in scope and minimize retained card data

Best answer: D

Explanation: PCI DSS is relevant when a database stores, processes, or transmits payment card account data such as a full primary account number (PAN). A managed DBaaS platform may provide compliant infrastructure controls, but it does not remove the organization’s responsibility for schema design, access control, encryption, logging, and retention decisions. The best recommendation is to reduce PCI scope by not storing full card data unless there is a valid business need, using processor tokens where possible, and strongly protecting any retained cardholder data. Reporting should use non-sensitive identifiers, tokens, or truncated values rather than full PANs. The key takeaway is that payment card data drives PCI DSS security and compliance decisions even when the database is hosted by a third party.

• Privacy-only classification misses that full payment card numbers trigger payment card security requirements, not just general personal-data handling.
• Provider-only reliance fails because DBaaS compliance does not automatically make the customer’s database design and access model compliant.
• Analyst full access increases exposure and conflicts with least privilege when reporting can use tokens, masked values, or truncated identifiers.

Question 16

Topic: Business Continuity

A retail company runs an OLTP order database in a cloud region with multiple availability zones. The design must tolerate the loss of one zone, keep the application writable again within minutes, prevent committed order loss, and avoid split-brain writes during a network partition. Which continuity design is the BEST professional decision?

Options:

• A. RAID storage and VM restart within the same zone

• B. Nightly full backups stored in a different zone

• C. Synchronous cross-zone replication with quorum-based automatic failover

• D. Asynchronous active-active replication with DNS round-robin writes

Best answer: C

Explanation: The core issue is combining infrastructure fault tolerance with database consistency. A multizone design handles the infrastructure failure, but the replication and failover method determines whether committed transactions remain consistent. Synchronous replication confirms writes on another zone before commit, supporting a zero committed-transaction-loss requirement. Quorum-based failover helps ensure only one primary is promoted, reducing split-brain risk during a partition. Backups are still important, but they do not provide minute-level writable recovery or prevent transaction conflicts during failover.

• Backups only help recovery after data loss, but nightly backups do not meet minute-level availability or zero committed-order-loss goals.
• Asynchronous active-active may improve availability, but round-robin writes can create conflicts and committed transaction gaps during failover.
• Same-zone restart may recover from a VM failure, but it does not tolerate a full zone outage.

Question 17

Topic: Data and Database Security

A DBA is reviewing audit evidence for a customer database that stores PII. The application and reporting tool are the only approved clients. Which remediation plan best applies defense-in-depth to the findings?

Exhibit: Security review summary

Options:

• A. Rely on encrypted backups and add masking to the customer table

• B. Enable failed-login auditing and require MFA only for database administrators

• C. Restrict network access, enforce TLS, replace shared access with least-privilege roles, and enable auditing

• D. Keep network access open and rotate the shared account password more frequently

Best answer: C

Explanation: Defense-in-depth uses multiple complementary controls so one failure does not expose the database. The exhibit shows gaps at several layers: the listener is reachable from anywhere, TLS is optional, one shared account has excessive privileges, and database auditing is incomplete. A strong remediation plan should reduce the attack surface, protect data in motion, improve accountability and authorization, and record sensitive access. Encrypted backups help protect stored backup media, but they do not address live database exposure or shared-account misuse. The key is to correct the visible weaknesses across layers, not to add one isolated control.

• Password-only focus leaves the listener exposed and does not solve shared-account accountability or excessive privileges.
• Backup reliance protects backup copies but does not protect active sessions, network exposure, or database access paths.
• Admin-only MFA helps privileged administrators, but it misses application identity, TLS enforcement, network restriction, and sensitive-access auditing.

Question 18

Topic: Business Continuity

A DBA manages a transactional payroll database with nightly full backups and 15-minute transaction log backups stored off-site. The business requires proof that backups can be restored without affecting production and that restored data is not corrupted. Which action is the BEST professional decision before a failure occurs?

Options:

• A. Review backup job success alerts each morning

• B. Restore the latest backup over production during a maintenance window

• C. Schedule isolated restore tests with hash validation and integrity checks

• D. Keep additional off-site copies without performing restores

Best answer: C

Explanation: Backup usability is confirmed by testing the restore process, not only by confirming that backup jobs completed. For a production payroll database, the safest approach is to restore backups into an isolated test or recovery environment, validate the backup files with hashes or checksums, and run database integrity checks after restore. This verifies that the files are readable, the restore procedure works, encryption or access requirements are handled, and the restored database is not corrupted. The test should be documented and repeated on a schedule aligned with recovery requirements. Extra copies and alerts are useful controls, but they do not prove that a backup can actually be restored.

• Job success only fails because a completed backup job can still produce a file that is unreadable, incomplete, or unusable during restore.
• Production overwrite creates unnecessary operational risk and violates the requirement to avoid affecting production.
• Extra copies only improves storage resilience but does not validate restore steps or restored data integrity.

Question 19

Topic: Database Fundamentals

A DBA reviews the following SQL used to disable outdated marketing preferences without changing table structure:

UPDATE customer_profile
SET email_opt_in = FALSE,
    updated_at = CURRENT_TIMESTAMP
WHERE last_contacted < DATE '2024-01-01'
  AND email_opt_in = TRUE;

Which concept best describes what this statement is using?

Options:

• A. Set-based UPDATE operation

• B. Row-by-row cursor processing

• C. DDL schema modification

• D. Read-only SELECT query

Best answer: A

Explanation: The SQL statement uses the CRUD Update principle with set-based logic. UPDATE changes existing rows, SET defines the new column values, and WHERE limits the affected rows to only matching records. Because the database engine evaluates the predicate and applies the change to all qualifying rows as a set, the script is not manually looping through each customer record. It also does not define or alter database objects, so it is not DDL. The key takeaway is to identify the SQL verb first, then use the surrounding clauses to confirm whether the operation is create, read, update, or delete.

• Cursor processing would involve an explicit cursor or loop that processes one row at a time.
• DDL modification would use commands such as CREATE, ALTER, or DROP to change database structure.
• Read-only query would retrieve data with SELECT rather than modifying existing rows.

Question 20

Topic: Data Integration

A DBA is reviewing AI-assisted output for an ETL mapping from a CRM JSON feed into a warehouse table. The pipeline must preserve source identifiers exactly, mask PII in non-production, store currency as DECIMAL(12,2), and reject rows without customer consent.

Exhibit: AI-generated mapping summary

What is the BEST professional decision?

Options:

• A. Revise only the customer_id cast and accept the rest.

• B. Reject the output as-is and require a corrected, reviewed mapping.

• C. Accept the output because the generated SQL is syntactically valid.

• D. Deploy the output with monitoring and fix bad rows later.

Best answer: B

Explanation: AI-assisted integration output should be validated against visible requirements before use. In this case, the output is not merely incomplete; it conflicts with multiple stated controls. Casting a string identifier to an integer can remove leading zeros or change the source value. Copying email into non-production without masking violates the PII constraint. Storing currency as FLOAT risks precision issues compared with DECIMAL(12,2). Filling a missing consent timestamp with the current time also changes the meaning of the data instead of rejecting the row. The safest professional decision is to reject the output as-is and require a corrected, human-reviewed mapping before deployment.

• Syntax-only validation fails because valid SQL can still violate schema, privacy, and business rules.
• Fix later fails because consent and PII issues should not be knowingly deployed into a pipeline.
• Single-field revision fails because the exhibit shows several independent defects, not just an identifier issue.

Question 21

Topic: Business Continuity

A DBA reviews a proposed continuity design for an order-processing database. The business requires automatic recovery from a single zone outage, RTO under 5 minutes, and no loss of committed orders.

Exhibit: Continuity design summary

Which change most directly addresses the remaining continuity gap?

Options:

• A. Add more application servers behind the load balancer

• B. Use synchronous commit replication with durable remote logs

• C. Keep asynchronous replication and run backups every 15 minutes

• D. Reduce DNS time-to-live for application endpoints

Best answer: B

Explanation: The design already addresses part of the infrastructure failure requirement by placing the application tier and a database replica in separate zones. The gap is database consistency for committed orders. With asynchronous replication, a primary-zone failure can occur before recent committed transactions reach the replica. Storing transaction logs only on Zone A storage, with hourly off-site copies, also leaves a window where committed transactions can be lost. Synchronous commit replication, or an equivalent design that durably records logs in another zone before acknowledging commit, better aligns failover with the no-loss requirement. Faster backups or endpoint changes may improve recovery operations, but they do not make the promoted database contain every committed order.

• Application redundancy helps service availability, but it does not protect database commits or transaction logs.
• DNS tuning can speed client redirection, but it does not change replica currency or log durability.
• More frequent backups reduce potential data loss, but a 15-minute backup interval still does not meet a no-loss requirement.

Question 22

Topic: Database Fundamentals

A facilities company is selecting a database model for a new monitoring system. The system will ingest temperature and vibration readings from 8,000 devices every few seconds, retain the readings for trend analysis, and support fast queries by device ID and time range. Which database model is the best professional decision?

Options:

• A. Graph database

• B. Key-value database

• C. Time series database

• D. Object-oriented database

Best answer: C

Explanation: A time series database fits data that arrives as timestamped events or measurements and is commonly queried over time windows, such as device metrics, sensor readings, logs, and trends. The stem describes frequent append-style ingestion, a consistent measurement shape, retention for trend analysis, and queries filtered by device ID and time range. An object-oriented database is better when the main need is to persist complex application objects with relationships and behavior-like structure, not high-volume chronological measurements. The access pattern is the deciding factor: time-based writes and time-range reads point to a time series model.

• Object persistence is tempting if devices are modeled in an application, but the primary workload is measurement history by timestamp.
• Graph relationships would fit path or relationship traversal, which is not the main access pattern here.
• Simple key lookup can be fast, but key-value stores do not natively target time-window analytics across many readings.

Question 23

Topic: Business Continuity

A payment database runs in an on-premises data center and asynchronously replicates to a warm standby in a cloud region. The primary site has a storage failure and is expected to be unavailable for several hours. The business requires an RTO of 30 minutes and an RPO of 5 minutes; the standby shows a 2-minute replication lag. Applications connect through a DNS alias. Which action is the BEST professional decision?

Options:

• A. Allow writes to both primary and standby environments

• B. Wait for the primary storage array to be repaired

• C. Restore the latest full backup to a new server

• D. Promote the standby and redirect the DNS alias

Best answer: D

Explanation: Failover moves service to an alternate environment when the primary environment cannot meet availability requirements. Here, the primary site will be down for hours, but the warm standby is only 2 minutes behind, which satisfies the 5-minute RPO. Promoting the standby and redirecting the DNS alias can also meet the 30-minute RTO if done through the documented DR process. The action should include validation after promotion, but the core decision is to make the standby the active database endpoint. Restoring from backup is usually slower and may lose more data, while waiting for repair violates the recovery time objective.

• Waiting for repair fails because the outage is expected to last several hours, exceeding the 30-minute RTO.
• Restoring a full backup is less direct than using a current warm standby and may not meet the RPO or RTO.
• Dual writes risk split-brain behavior and data inconsistency during a failover event.

Question 24

Topic: Business Continuity

A DBA must recover the Sales database after a maintenance script incorrectly updated product prices. Monitoring confirms the first incorrect transaction committed at 14:37:18. The business wants the latest recoverable target that excludes the bad changes.

Exhibit: Validated restore points

Which restore point should be selected?

Options:

• A. RP-1445

• B. RP-1500

• C. RP-1430

• D. RP-1437

Best answer: D

Explanation: Restore point selection should align with the incident time and the recovery goal. Because the first bad transaction committed at 14:37:18, any restore point that includes transactions after that time would also include the incorrect price updates. The best target is the most recent validated restore point before the bad transaction, which minimizes data loss while excluding the corruption. An earlier restore point is safer but loses more valid work than necessary.

• RP-1430 is before the incident, but it loses more valid transactions than needed.
• RP-1445 includes transactions after 14:37:18, so it may preserve the bad updates.
• RP-1500 is even later than the incident and would not meet the exclusion requirement.

Question 25

Topic: Data and Database Security

A DBA is preparing evidence for a security audit finding that an application stores database credentials insecurely. The environment handles regulated customer data, and the audit package must support the finding without exposing live secrets. Which evidence is the BEST to include?

Options:

• A. A database report listing active service accounts and their assigned roles

• B. An email from a developer stating that the password is stored in the application

• C. A redacted config excerpt showing credential fields, file path, timestamp, and repository commit ID

• D. A connection log showing repeated successful logins from the application server

Best answer: C

Explanation: Audit evidence should directly support the specific finding and be safe to share. For an insecure credential-storage finding, the strongest evidence is a sanitized artifact from the actual storage location, such as a configuration file or repository entry, with the secret redacted. Including context such as file path, timestamp, and commit ID makes the evidence traceable and reviewable without exposing regulated data or live credentials. Account lists and connection logs may support other findings, but they do not prove how credentials are stored.

• Account listing supports authorization or excessive-access findings, not storage of credentials.
• Connection activity shows use of credentials, but not whether they are hard-coded or insecurely stored.
• Developer email is weaker than traceable system evidence and may not be independently verifiable.

Questions 26-50

Question 26

Topic: Data and Database Security

A DBA is reviewing a proposed cloud deployment for a customer database that will use cross-region replication for disaster recovery. Which action is best supported by the compliance note?

Exhibit: Compliance note

Dataset: customer_profile
Regulated fields: name, email, national_id, billing_address
Residency requirement: EU resident PII must remain in EU-approved regions.
Applies to: primary databases, read replicas, backups, and failover targets.
Proposed design: Primary in EU region; async replica in US region.

Options:

• A. Move the replica to an EU-approved region

• B. Keep the US replica and enable encryption at rest

• C. Keep the US replica because it is asynchronous

• D. Replicate only during disaster recovery testing

Best answer: A

Explanation: Regional data residency requirements affect where regulated data may be stored or processed, not just how it is protected. The exhibit states that EU resident PII must remain in EU-approved regions and that the rule applies to replicas, backups, and failover targets. Because an asynchronous replica still stores a copy of the regulated fields, placing it in a US region would violate the stated residency requirement. The deployment should use an EU-approved region for disaster recovery replication or redesign DR so regulated data does not leave the allowed geography.

Encryption is important, but it does not by itself change the location where regulated data resides.

• Encryption-only control fails because encryption at rest protects confidentiality but does not satisfy the stated location restriction.
• Async replica assumption fails because asynchronous replication still creates a stored copy outside the approved region.
• Testing-only replication fails because the note applies to failover targets and copies of regulated data, including temporary DR copies.

Question 27

Topic: Database Fundamentals

A DBA is designing ingestion for partner order events delivered as JSON. Each event includes nested customer and items[] objects plus _metadata fields such as source system and extraction time. Analysts must filter by item attributes and metadata without losing new fields. Which approach best meets the requirement?

Options:

• A. Flatten each event into a fixed-width text file

• B. Store each payload as an encrypted binary object

• C. Load only required fields into a normalized table

• D. Validate JSON and store it in a queryable semistructured column

Best answer: D

Explanation: JSON is semistructured data, so the handling approach should preserve its hierarchy, optional fields, and embedded metadata while still supporting queries. Validating the payload at ingestion helps catch malformed records, and storing the validated document in a queryable semistructured column or document-oriented structure keeps nested objects such as items[] available for path-based filtering. This also avoids losing new partner fields that may appear later. Pure flattening can be useful for a curated reporting layer, but it should not be the only retained form when structure and metadata must remain queryable.

• Fixed-width flattening loses nested relationships and makes new or optional JSON fields difficult to preserve.
• Binary object storage may preserve bytes, but it prevents normal database querying of item attributes and metadata.
• Required-field loading supports a known relational model, but it discards fields outside the selected schema.

Question 28

Topic: Database Fundamentals

A DBA supports an order-entry database used by a web app, a reporting import job, and occasional bulk loads. The team needs automation that validates order status changes, writes an audit row, and applies consistently whenever the table is modified, even if the change does not come from the web app. Which implementation is the best professional decision?

Options:

• A. Add a Python script to each analyst workstation

• B. Add an ORM event hook in the web application

• C. Schedule a PowerShell job to scan changes hourly

• D. Use a database trigger or stored procedure on the server

Best answer: D

Explanation: Automation that must protect integrity and auditing for every data change belongs closer to server automation. A trigger, stored procedure, or similar database-side mechanism runs in the database environment and is not dependent on which client, application, import job, or workstation initiated the change. That fits the stem because the rule must apply to the web app, reporting import job, and bulk loads. Client automation is better for tasks such as launching utilities, moving files, generating local reports, or coordinating administrative commands from outside the database. Here, placing the logic only in a client or application path would leave gaps when another path modifies the table.

• Workstation scripts fail because they depend on client machines and do not automatically cover all database write paths.
• ORM hooks fail because they cover the web application path but not direct imports or bulk loads.
• Hourly scanning fails because it is delayed and detects changes after they occur instead of enforcing the rule at modification time.

Question 29

Topic: Database Management and Maintenance

A DBA reviews a database migration that is currently queued for automatic deployment to production tonight. The application tests passed, but the change affects customer records.

Exhibit: Proposed change and policy note

ALTER TABLE customer_profile
  DROP COLUMN consent_source;

ALTER TABLE customer_profile
  ADD COLUMN marketing_opt_in BOOLEAN NOT NULL DEFAULT FALSE;

Policy note: Fields used to prove customer consent must be retained for audit review.
Production changes that affect regulated customer data require documented impact review, rollback plan, and change approval before release.

What is the best next action?

Options:

• A. Deploy because automated application tests passed

• B. Pause deployment and submit the change for approval

• C. Deploy first and document the change afterward

• D. Run the change manually during off-hours

Best answer: B

Explanation: Change management protects production stability and compliance obligations when a database modification introduces risk. In this case, dropping consent_source may remove evidence needed for audit review, and the policy explicitly requires documented impact review, a rollback plan, and change approval before release. Passing application tests does not prove compliance acceptability or operational readiness. The proper step is to stop the automated release path until the change is assessed and approved through the defined process.

The key takeaway is that operational or compliance risk moves a database change from routine deployment into formal change control.

• Automated tests only fails because functional tests do not replace compliance impact review or approval.
• Manual off-hours execution still bypasses the required change process and rollback planning.
• After-the-fact documentation fails because approval must occur before a risky production modification.

Question 30

Topic: Database Deployment

A financial services team is deploying a database for customer account transfers. Each transfer must update the source account, destination account, and ledger entry as one unit. Users must see the committed balance immediately after a transfer, and conflicting balances are not acceptable. The service also needs high availability across zones, but integrity is the top priority. Which deployment approach is BEST?

Options:

• A. ACID relational database with strong consistency and multi-zone HA

• B. Distributed cache as the primary system of record

• C. BASE document database with eventual consistency across zones

• D. Key-value store with asynchronous conflict resolution

Best answer: A

Explanation: ACID is the better fit when the workload requires atomic, consistent, isolated, and durable changes. In this scenario, a transfer is not just a single write; it must update multiple related records as one transaction, and users must see the committed result immediately. Strong consistency prevents conflicting balances from being visible after a transfer. High availability can still be addressed with multi-zone deployment, but the design should not trade away transactional integrity for eventual consistency. BASE-style systems are useful when availability, horizontal scale, and flexible data models matter more than immediate consistency, but that trade-off does not match financial transfer requirements.

• Eventual consistency fails because delayed convergence can expose stale or conflicting account balances.
• Conflict resolution is risky here because financial transfers should not rely on later reconciliation as the normal consistency model.
• Cache as record fails because a cache is not the right durable transactional system of record for ledger updates.

Question 31

Topic: Database Management and Maintenance

A development team is troubleshooting defects that appear only with realistic order history and customer relationships. The test database is six months old and no longer matches production data patterns. The environment must not expose real customer PII. Which maintenance action is the best choice?

Options:

• A. Refresh test from production and mask sensitive fields

• B. Generate a small synthetic data set manually

• C. Keep the current test data for stability

• D. Let developers query production in read-only mode

Best answer: A

Explanation: Database refresh is the appropriate maintenance process when test or development environments need data that reflects current production patterns. A refresh replaces or updates non-production data from an approved production source, usually a backup, export, or replicated copy. Because the scenario requires realistic order history and customer relationships, the refresh should preserve representative volumes, referential integrity, and data distribution. Because the environment must not expose real customer PII, sensitive columns should be masked, anonymized, or otherwise protected before developers use the data. Direct production access may reduce staleness, but it increases operational and security risk. The key takeaway is to make non-production data realistic enough for testing without treating it as unrestricted production data.

• Production access fails because read-only access still exposes live PII and can create operational risk.
• Manual synthetic data fails because a small hand-built set is unlikely to reproduce realistic relationships and data patterns.
• Stale test data fails because it no longer represents current production behavior, which is the stated problem.

Question 32

Topic: Business Continuity

A DBA reviews the quarterly DR exercise for an order-processing database. The approved requirements are RPO of 15 minutes or less, RTO of 30 minutes or less, documented failover and failback steps, and test evidence before accepting the DR plan.

Test evidence: Failover to the DR site completed in 22 minutes, the DR copy was 8 minutes behind production, and application validation passed. The team did not have a documented failback procedure, and the return to the primary site was improvised and not timed.

What is the BEST professional decision?

Options:

• A. Replace replication with nightly full database dumps

• B. Document failback and retest the end-to-end process

• C. Require synchronous replication before any approval

• D. Accept the plan because RPO and RTO were met

Best answer: B

Explanation: A DR plan is not accepted only because failover met the numeric objectives. RPO measures acceptable data loss, and the 8-minute lag is within the 15-minute requirement. RTO measures recovery time, and the 22-minute failover is within the 30-minute requirement. However, the stated acceptance criteria also require documented failover and failback steps plus test evidence. Because failback was improvised and not timed, the DBA should close that documentation and evidence gap, then retest the full workflow. The key takeaway is that DR validation must prove the complete recovery cycle, not just the initial failover.

• RPO/RTO only misses the explicit requirement for documented and tested failback.
• Nightly dumps would likely weaken the recovery point and does not address the missing failback evidence.
• Synchronous replication may be useful in some designs, but the measured RPO was already within the approved objective.

Question 33

Topic: Database Fundamentals

A retail database is used by several applications that insert and update OrderLine rows. The DBA must ensure that every order-line change automatically checks available inventory, rejects changes that would create negative stock, and writes an audit row in the same transaction, regardless of which application made the change. Which SQL construct best meets this requirement?

Options:

• A. A scheduled SQL job

• B. A stored procedure called by applications

• C. A row-level trigger on OrderLine

• D. A read-only view over OrderLine

Best answer: C

Explanation: A trigger is the best fit when integrity-related automation must occur automatically at the database level for every qualifying table change. Because multiple applications can modify OrderLine, relying on application code or an optional procedure call would leave gaps. A row-level trigger can inspect the inserted or updated row, reject invalid changes according to inventory rules, and insert an audit record as part of the same transaction. If the trigger raises an error, the data change can be rolled back with the audit logic kept consistent.

The key point is that the rule must be enforced where the data change occurs, not only where one application chooses to call it.

• Scheduled job runs after the fact, so invalid stock changes could be committed before the job detects them.
• Stored procedure only helps if all applications are forced to use it, which the scenario does not guarantee.
• Read-only view can expose data safely, but it does not automate validation and audit writes for base-table changes.

Question 34

Topic: Data Integration

A DBA is asked to support analytics across an on-premises order database, a cloud CRM, and a SaaS support system. Analysts need governed SQL access for dashboards with current data, but compliance wants to avoid creating unnecessary replicated copies of customer PII. Source systems must remain the systems of record. Which approach is the BEST professional decision?

Options:

• A. Automate screen extraction with RPA bots

• B. Implement a virtual data warehouse layer

• C. Build a nightly full-copy ETL warehouse

• D. Use a RAG chatbot over exported files

Best answer: B

Explanation: A virtual data warehouse is a strong fit when users need a unified analytical view across multiple systems while minimizing copied data. It provides a logical access layer for querying distributed sources, often using federation, virtualization, metadata, and governance controls. In this scenario, the key constraints are current dashboard data, SQL-based analytics, multiple heterogeneous sources, and reduced PII replication. RPA is better for automating repetitive UI tasks when APIs or direct integrations are unavailable, not for governed analytical query access. A physical ETL warehouse can be valid when durable integrated copies are required, but it conflicts with the stated goal to avoid unnecessary replicated PII.

• RPA bots are tempting for legacy integration, but the need is governed analytics, not repetitive UI-based data entry or extraction.
• Nightly full-copy ETL may support reporting, but it adds replicated PII and may not meet the current-data dashboard requirement.
• RAG over files supports retrieval-assisted answers, but exported files are not a governed SQL analytics layer.

Question 35

Topic: Data Integration

A database team is adding a generative AI assistant for analysts. The assistant must answer questions about internal data dictionaries, ETL mappings, and retention procedures. The main requirement is that responses be grounded in the currently approved documentation and should say when the documentation does not contain an answer. Which implementation choice best meets this requirement?

Options:

• A. Ask users to paste relevant documents into each chat

• B. Fine-tune the model once using archived documentation

• C. Use RAG with approved-document retrieval and a grounding prompt

• D. Use a larger general-purpose model with no document retrieval

Best answer: C

Explanation: Retrieval-augmented generation (RAG) is used when AI output needs to be grounded in supplied data or documentation. For this requirement, approved documents should be indexed or otherwise searchable, relevant passages should be retrieved at query time, and the prompt should instruct the model to answer only from that retrieved context, cite or reference the source material, and state when the answer is not found. This reduces hallucinations and keeps answers aligned with current database documentation. Fine-tuning can teach patterns, but it does not reliably guarantee current, source-grounded responses when documentation changes. The key takeaway is to combine retrieval with explicit grounding instructions, not just a more capable model.

• Archived fine-tuning can become stale and does not force answers to use current approved documents.
• No retrieval leaves the model relying on general training data instead of the organization’s data dictionary and procedures.
• Manual pasting is inconsistent, error-prone, and does not provide a controlled grounding process for analysts.

Question 36

Topic: Business Continuity

A database team is updating the DR plan for an OLTP order database. The standby system does not need automatic failover, but recovery must use recent committed transactions if the primary site is lost.

Exhibit: DR requirements and current options

Which action is best supported by the exhibit?

Options:

• A. Keep the secondary offline until a disaster occurs

• B. Use only hourly storage snapshots

• C. Configure log shipping to the warm secondary

• D. Rely on the nightly full backup only

Best answer: C

Explanation: Log shipping is appropriate when a database needs periodic movement of transaction log backups from a primary database to a secondary database for disaster recovery. In this scenario, the required RPO is 15 minutes and the proposed log backup/copy interval is every 5 minutes, so the standby can be kept close enough to the primary to meet the recovery-point target. Because automatic failover is not required, a warm secondary maintained through log shipping is a good fit. The DR plan should still include restore testing and documented failover/failback steps, but the exhibit supports log shipping as the recovery mechanism.

• Nightly full backup leaves up to a full day of possible data loss, which does not meet a 15-minute RPO.
• Hourly snapshots may lose more than 15 minutes of changes unless the snapshot interval is reduced.
• Offline secondary does not receive ongoing transaction movement, so it cannot maintain a recent recovery point.

Question 37

Topic: Database Fundamentals

A DBA is investigating slow page loads in a .NET order-entry application. Monitoring shows each request runs one query for an order and then many similar queries for related line items. The development team confirms the app uses Entity Framework for object-to-table mapping. Which implementation choice best addresses the database-performance issue?

Options:

• A. Tune Entity Framework loading and generated SQL

• B. Increase the ODBC connection timeout

• C. Rebuild dbt transformation models

• D. Export line items to flat files

Best answer: A

Explanation: ORM frameworks map application objects to database tables and generate SQL on behalf of the application. In this scenario, Entity Framework is the component producing repeated relationship queries, a common N+1 query pattern. A DBA should work with developers to review the ORM-generated SQL, loading behavior, projections, and mappings so the application retrieves related data more efficiently. Similar ORM-performance reviews may involve Hibernate, SQLAlchemy, or Ebean in other application stacks.

A timeout change might hide symptoms, but it does not reduce the number or cost of generated queries.

• dbt transformations are used for analytics-style data transformations, not per-request object relationship loading in a .NET application.
• Connection timeout may reduce failures during long waits, but it does not fix excessive generated SQL.
• Flat-file export changes the integration pattern and does not address the live application query pattern.

Question 38

Topic: Database Management and Maintenance

A DBA must schedule a database change for an upcoming application release. Which schedule best coordinates the database, application, and operational constraints shown in the exhibit?

Options:

• A. Add column Thu 21:00; deploy app Thu 22:30; backfill Fri 23:00

• B. Add column Tue 21:00; deploy app Tue 22:30; backfill Wed 23:00

• C. Add column Tue 21:00; backfill Tue 21:15; deploy app Tue 22:30

• D. Deploy app Tue 22:30; add column Thu 21:00; backfill Fri 23:00

Best answer: B

Explanation: Release scheduling for database changes should sequence dependencies while respecting maintenance, application, and operations windows. Here, the app v4 deployment depends on the new nullable column being present, so the column should be added before the app release in the approved Tuesday DB window. The app then deploys in its approved Tuesday window. The backfill must wait until after app v4 is deployed and should avoid the daily ETL period and the Thursday reporting freeze, making a later non-conflicting window the safest choice. The key is not just picking an available database window, but coordinating it with application readiness and operational blackout periods.

• App before schema fails because app v4 requires the column before deployment.
• Thursday scheduling conflicts with the reporting freeze and does not match the stated app release window.
• Early backfill fails because the backfill is allowed only after app v4 is deployed.

Question 39

Topic: Database Management and Maintenance

A DBA supports a normalized order-entry database. Executives need a sales dashboard that reads 18 months of orders by day, product category, and region. The dashboard is read-heavy, can be up to 30 minutes behind, and must not slow order-entry transactions. Which design is the BEST professional decision?

Options:

• A. Run dashboard queries directly on OLTP tables

• B. Further normalize product and region tables

• C. Add nullable region columns to every order row

• D. Create a refreshed reporting summary table

Best answer: D

Explanation: Denormalization is appropriate when a read-heavy reporting workload justifies controlled redundancy. In this case, the dashboard needs repeated aggregate reads across 18 months of data, tolerates 30 minutes of latency, and must not affect order-entry performance. A reporting summary table, refreshed on a schedule or after ETL, can store precomputed totals by day, category, and region while the normalized transactional schema remains the source of truth. This limits update anomalies because redundant data is derived and refreshed through a controlled process rather than manually maintained in core OLTP rows. The key tradeoff is accepting some storage and refresh complexity to meet reporting performance requirements.

• Widening order rows adds uncontrolled redundancy to the transactional table and increases the risk of inconsistent region data.
• Further normalization may improve data integrity, but it usually increases joins and does not address the dashboard performance goal.
• Querying OLTP directly ignores the requirement to avoid slowing order-entry transactions during reporting reads.

Question 40

Topic: Business Continuity

A DBA reviews the recovery evidence for a transactional database that needs fast rollback after failed deployments and a recoverable copy retained for 30 days.

Exhibit: Recovery evidence

Snapshot schedule: every 4 hours
Snapshot retention: 48 hours
Last snapshot rollback test: 5 minutes, successful
Last full database backup: 23 days ago
Backup alert: scheduled backups disabled
Snapshot storage: same storage array as database volumes

What is the best interpretation of this evidence?

Options:

• A. Snapshots are useful, but backups must be re-enabled

• B. Snapshots replace backups because rollback testing succeeded

• C. Only increase snapshot frequency to meet the requirement

• D. The backup schedule is acceptable until retention expires

Best answer: A

Explanation: Snapshots are point-in-time images that can provide fast rollback, especially for failed changes or deployments. In this exhibit, the 5-minute rollback test shows that snapshots are effective for quick operational recovery. However, they do not satisfy the full continuity requirement by themselves: retention is only 48 hours, scheduled backups are disabled, and the snapshots are on the same storage array as the database. A complete backup strategy should include recoverable backup copies with appropriate retention, validation, and storage separation from the primary failure domain. The key takeaway is that snapshots can complement backups, not replace them.

• Successful rollback test proves the snapshot mechanism works, but it does not prove 30-day recoverability or protection from array failure.
• Retention not expired is misleading because 48-hour snapshot retention cannot meet a 30-day recoverable-copy requirement.
• More frequent snapshots may reduce rollback point age, but frequency does not address disabled backups or same-array dependency.

Question 41

Topic: Data and Database Security

A finance database contains PII. Reporting analysts need daily totals without seeing PII, an ETL job must load nightly data into staging tables, and DBAs need emergency troubleshooting access. The company uses SSO and requires individual accountability. Which configuration best satisfies least privilege while preserving operational access?

Options:

• A. A shared administrator account for analysts, ETL, and DBAs

• B. RBAC groups for views, a separate ETL service principal, and audited DBA break-glass access

• C. Temporary owner permissions for analysts and ETL during reporting windows

• D. Direct analyst grants on base tables and a shared ETL login

Best answer: B

Explanation: Least privilege requires each identity to receive only the permissions needed for its role, while authentication should preserve accountability. Analysts should use an SSO-backed group mapped to read-only reporting views that exclude PII. The ETL workload should use its own nonhuman service principal with only the staging-table permissions it needs. DBAs can retain operational access through named accounts, MFA, approval or break-glass workflow, and audit logging. This design avoids shared credentials and avoids granting broad base-table or owner permissions just to make operations easier.

• Base-table grants expose PII when analysts only need aggregated reporting views.
• Shared logins break individual accountability and make investigation or revocation difficult.
• Owner permissions are broader than needed and increase the impact of mistakes or compromise.

Question 42

Topic: Database Deployment

A company is moving an internal order-processing database to the cloud. The DBA team must still create schemas, tune indexes, manage user permissions, and configure backup retention, but it does not want to patch the operating system or install database engine software. Which deployment option best fits this objective?

Options:

• A. SaaS

• B. DBaaS

• C. IaaS

• D. PaaS

Best answer: B

Explanation: DBaaS is the best fit when the organization needs a cloud-hosted database service without managing the underlying server OS or database installation. The provider handles much of the platform operation, such as infrastructure provisioning and engine maintenance, while the DBA team still works with schemas, indexes, permissions, connectivity, and database-level backup settings. This differs from renting virtual machines, where the team would still install and patch the database software. It also differs from SaaS, where the database is hidden behind a complete application. PaaS can host applications and related services, but the database-specific managed service model described here is DBaaS.

• SaaS application is too high-level because users consume a finished application and usually do not manage schemas or indexes directly.
• IaaS virtual machines would keep OS and database engine installation and patching with the customer.
• General PaaS is broader application platform hosting, while the requirement is specifically for a managed database service.

Question 43

Topic: Database Management and Maintenance

A DBA deployed a version-controlled database migration during a scheduled maintenance window. After deployment, an application workflow began returning incorrect results because a changed view definition filters out valid active customers. No data corruption is reported, and the migration package includes a reviewed rollback script that restores the prior view definition. What is the best maintenance action?

Options:

• A. Run the approved rollback script from version control

• B. Manually edit the production view until tests pass

• C. Leave the change in place until the next release

• D. Restore the entire database from the last full backup

Best answer: A

Explanation: When a database change causes unexpected behavior, the preferred first response is usually the controlled rollback path for that change, especially when the issue is isolated and a reviewed rollback script exists. Using the version-controlled rollback restores the known-good object definition, keeps the database aligned with the change history, and supports audit requirements. A full database restore is usually too disruptive when there is no data corruption or broad failure. Manual edits may fix the symptom, but they create configuration drift and can bypass testing, approval, and documentation. The key is to reverse the specific change through the same governed process used to deploy it.

• Manual edits are risky because they can create drift from the repository and bypass review.
• Full restore is excessive because the issue is isolated to a view definition and no corruption is reported.
• Waiting for release leaves a known production defect active when a reviewed rollback is already available.

Question 44

Topic: Database Management and Maintenance

A reporting query against Orders and Customers suddenly changed from a targeted lookup to scanning most of Orders. The table and indexes are unchanged, but last week a bulk load added many new Orders rows for a few high-volume regions. The optimizer estimates only a small number of matching rows, while actual rows are much higher. What maintenance action should the DBA take first?

Options:

• A. Increase the maximum number of database connections

• B. Create or refresh statistics on the affected columns

• C. Add a duplicate index on the region column

• D. Convert the reporting query into a stored procedure

Best answer: B

Explanation: Query optimizers use statistics to estimate row counts, value distribution, and selectivity for predicates and joins. In this scenario, the schema and indexes did not change, but a bulk load changed the data distribution. The mismatch between estimated and actual rows indicates the optimizer is planning with stale or missing information. Creating or refreshing statistics on the filtered and joined columns is the most direct first action because it helps the engine choose a plan based on current data. Adding objects or changing application structure should wait until the optimizer has accurate metadata to work with.

• Duplicate index is unlikely to help because the existing index structure is unchanged and the symptom is bad cardinality estimation.
• Connection limit addresses session capacity, not poor query plan selection.
• Stored procedure conversion changes packaging, but it does not by itself improve the optimizer’s understanding of data distribution.

Question 45

Topic: Database Management and Maintenance

A DBA is reviewing overnight alerts for a 24x7 OLTP database. The service has a 15-minute RPO, and maintenance actions should avoid downtime when possible.

Alert summary:

02:00 Full backup: Completed successfully
02:15 Log backup: Failed - backup repository volume full
02:30 Log backup: Failed - backup repository volume full
Database storage: 1.91 TB used of 2.00 TB allocated
Growth trend: about 80 GB per week

Which action is the BEST professional decision?

Options:

• A. Disable log backups until more database storage is provisioned

• B. Free or expand backup repository space and rerun the log backup

• C. Ignore the failures because the full backup completed successfully

• D. Shrink the database files before addressing the backup alerts

Best answer: B

Explanation: The most urgent alert is the repeated log backup failure because the database has a 15-minute RPO. A successful full backup does not satisfy that recovery objective for ongoing OLTP changes after 02:00. The failure message identifies the immediate cause: the backup repository volume is full. The DBA should restore backup capacity and rerun the failed log backup, then continue with a capacity plan for the database storage alert because 1.91 TB of 2.00 TB and 80 GB per week growth indicate an upcoming storage limit risk. The key is to separate the immediate backup objective breach from the longer-term database storage capacity issue.

• Full backup only fails because it does not protect changes made after the full backup within the required 15-minute RPO.
• Shrinking files first is risky and does not address the failed backup repository condition causing the RPO breach.
• Disabling log backups worsens recoverability and ignores the operational requirement for frequent recovery points.

Question 46

Topic: Database Fundamentals

A DBA is investigating slow checkout processing in an OLTP application that uses an ORM against a managed relational database. The schema has valid foreign keys, the current indexes match the documented access paths, and the team must preserve transaction integrity. An ORM trace shows 1 order lookup followed by 300 separate line-item queries and 300 single-row commits. What is the BEST professional decision?

Options:

• A. Replace all ORM access with hand-written SQL

• B. Move checkout reads to a read replica

• C. Tune ORM mappings for eager loading and batched writes

• D. Increase the database connection pool size

Best answer: C

Explanation: ORMs can simplify application development, but generated SQL can be inefficient when it creates N+1 query patterns or commits each row separately. In this scenario, the database design and indexes are not the primary issue; the trace shows excessive round trips and transaction overhead. A DBA should work with the application team to inspect the generated SQL and adjust ORM behavior, such as eager loading related rows, batching writes, and using set-based operations where appropriate. That targets the visible performance problem without removing needed transaction integrity or overengineering the data access layer.

Adding capacity may hide symptoms, but it does not correct inefficient generated SQL.

• Connection pool increase may allow more concurrent sessions, but it can worsen database pressure when each request still performs hundreds of queries.
• Read replica routing does not address excessive commits and may not be appropriate for checkout transactions that require current writes.
• Replacing all ORM access is too broad; selective ORM tuning or targeted SQL is a better first professional response.

Question 47

Topic: Database Deployment

A DBA is validating a new reporting database before the first production load. The validation notes are shown. Which validation check best addresses the issue supported by the exhibit?

Options:

• A. Data value check for invalid order status codes

• B. Index analysis for missing order status indexes

• C. Referential integrity check for orphaned customer IDs

• D. Data mapping check for renamed customer columns

Best answer: C

Explanation: The exhibit points to a referential integrity validation problem. dw_order.customer_id is a child foreign key that must match an existing dw_customer.customer_id parent row. The test result shows 184 staged orders with no matching customer row, so the DBA should validate and resolve orphaned customer IDs before loading the target table. The approved order_status values rule out a domain-value issue, and the mapping note shows the source and target customer fields are already identified. Index analysis may affect performance, but it does not explain missing parent records.

• Index focus fails because an index can improve lookup performance but does not create missing parent customer rows.
• Status validation fails because the exhibit states all order_status values are approved.
• Column mapping fails because the mapping between staged and target customer IDs is already documented.

Question 48

Topic: Data and Database Security

A DBA is reviewing a quarterly security audit for a database account used by a vendor ETL job. The vendor contract ended last month, but the nightly load still appears in audit reports.

Exhibit: Audit excerpt

Which interpretation is best supported by the exhibit?

Options:

• A. The SQL code contains an injection flaw

• B. The database encryption key was exposed

• C. The expired account is still able to authenticate

• D. The ETL server is an unauthorized source

Best answer: C

Explanation: The core audit issue is account lifecycle enforcement. The account is marked expired, but the login history shows successful authentications after that date. Because the source host is the approved ETL server and the SQL review found no dynamic SQL, the exhibit does not support an unauthorized-host or SQL injection conclusion. A reasonable next action would be to disable or lock the expired vendor account, confirm whether any exception was approved, and review the post-expiration activity for unauthorized data access. The key takeaway is that an expired account finding becomes a security concern when audit evidence shows it can still be used.

• Unauthorized source is not supported because the source host is identified as the approved ETL server.
• SQL injection is not supported because the SQL review specifically found no dynamic SQL.
• Key exposure is not supported because the exhibit contains no credential, key, or encryption-storage evidence.

Question 49

Topic: Data and Database Security

A DBA manages a cloud-hosted patient scheduling database that stores PHI and PII. Encryption at rest, TLS, and RBAC are already configured. An upcoming compliance review requires proof that sensitive data is retained only as long as policy allows and that access to it is periodically reviewed. Which action is the best professional decision?

Options:

• A. Enable stronger encryption keys for all database files

• B. Create a documented retention schedule with audit logging and access-review evidence

• C. Move the database to a lower-latency region

• D. Create read replicas for compliance reporting

Best answer: B

Explanation: Compliance evidence requires more than a technical control being enabled. In this scenario, encryption, TLS, and RBAC already address important protection requirements, but the audit asks for proof of retention and periodic access review. A documented retention schedule, audit logs, and recorded access reviews connect the technical environment to governance requirements. This creates evidence that the organization knows what sensitive data it holds, how long it should keep it, who can access it, and when that access was reviewed.

Stronger encryption may improve protection, but it does not prove retention compliance or access governance. The key takeaway is that database security controls should be paired with documented, reviewable governance evidence.

• More encryption may be useful, but it does not answer the audit request for retention and access-review evidence.
• Region change addresses residency or latency only if those are stated requirements, which they are not here.
• Read replicas can support reporting or availability, but they may increase sensitive-data exposure without proving governance compliance.

Question 50

Topic: Database Fundamentals

A DBA is reviewing a nightly SQL job that applies approved customer tier changes. The job is correct on small test data but now runs for several hours in production.

Exhibit: Procedure excerpt

DECLARE tier_cursor CURSOR FOR
SELECT CustomerID, NewTier
FROM PendingTierChanges
WHERE Approved = 1;

OPEN tier_cursor;
FETCH NEXT FROM tier_cursor INTO @CustomerID, @NewTier;

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE Customers
    SET Tier = @NewTier
    WHERE CustomerID = @CustomerID;

    FETCH NEXT FROM tier_cursor INTO @CustomerID, @NewTier;
END;

Which interpretation is best supported by the exhibit?

Options:

• A. The procedure prevents data integrity because it uses a WHERE clause.

• B. The procedure uses row-by-row logic where a set-based update would be more maintainable and efficient.

• C. The procedure is slow because Approved should be updated before Tier.

• D. The procedure requires a trigger to make each customer update atomic.

Best answer: B

Explanation: SQL is optimized for set-based operations. The exhibit processes approved tier changes one row at a time with a cursor, which can increase runtime, locking duration, and maintenance complexity as production volume grows. When each row follows the same update pattern, a joined UPDATE can usually express the work in one statement, making the intent clearer and letting the database optimizer choose an efficient access plan.

A cursor is not always wrong, but it should be justified by row-specific procedural requirements. Here, the visible logic is a straightforward lookup from PendingTierChanges to Customers, so the programming choice is the likely maintainability and performance concern.

• WHERE clause concern is incorrect because the WHERE CustomerID = @CustomerID predicate limits each update and supports integrity.
• Statement order claim is unsupported because the exhibit does not show a dependency requiring Approved to be changed first.
• Trigger requirement is a trap because each individual UPDATE is already a statement-level operation; a trigger would not fix the row-by-row design.

Questions 51-75

Question 51

Topic: Database Fundamentals

A DBA team needs to automate monthly partition maintenance and statistics refresh for an OLTP database. The job must run during a maintenance window even if administrator workstations are offline, use a least-privilege database execution context, and be easy for DBAs to review and version. Which scripting approach best fits these constraints?

Options:

• A. ORM migration executed during application startup

• B. Server-side SQL routine invoked by a controlled database job

• C. Shell script stored on a DBA laptop and run over SSH

• D. Python script launched manually from an analyst workstation

Best answer: B

Explanation: The core decision is runtime location. Because the task is database maintenance that must run independently of user workstations, a server-side SQL routine scheduled through a controlled database job is the best fit. It keeps the automation close to the database objects it maintains, can run under a least-privilege database execution context, and is typically readable and reviewable by DBAs in source control. Workstation-based scripts depend on a person’s machine, session, or credentials. Application-startup migrations are better for application schema deployment, not recurring operational maintenance. The key takeaway is to place automation where it can run reliably with the right operational access and ownership.

• Workstation dependency fails because a manually launched analyst script will not reliably run when users are offline.
• Laptop-based shelling adds operational fragility and may require broader remote server access than needed.
• Application startup couples database maintenance to application deployment and runtime behavior, which does not fit a scheduled DBA maintenance task.

Question 52

Topic: Business Continuity

A quarterly restore test for a customer database restores the data successfully in 12 minutes. The application remains unavailable for 45 minutes because the team did not validate DNS cutover and firewall rules for the DR site. The RTO is 30 minutes. Which improvement should the DBA implement for the next DR test?

Options:

• A. Add another off-site archive copy

• B. Replace the next restore test with a tabletop review

• C. Increase the database backup frequency

• D. Add end-to-end connectivity validation to the DR runbook

Best answer: D

Explanation: A DR test should be improved based on the specific gap it reveals. In this case, the database restore met the time need, but the service still missed the RTO because dependent recovery steps were not tested. The best improvement is to make the next exercise end-to-end: include DNS cutover, firewall validation, application login or health checks, owners, and timing in the DR runbook. That tests whether the restored database can actually support the application within the recovery window. Backup frequency and extra archive copies address recoverability, but they do not fix a failed cutover dependency.

• Backup frequency targets RPO and data loss, but the restore already completed quickly enough.
• Off-site archive improves backup resilience, but it does not validate DNS, firewall, or application access.
• Tabletop only may improve discussion, but it reduces technical validation after a hands-on readiness gap was found.

Question 53

Topic: Database Fundamentals

A DBA is cataloging a new feed from partner applications. Each record arrives as JSON, uses consistent keys such as customerId and eventTime, and may include optional nested objects that vary by partner. The data must be ingested before a final relational schema is defined. How should the DBA classify this feed?

Options:

• A. Semistructured data

• B. Binary data

• C. Structured data

• D. Unstructured data

Best answer: A

Explanation: Semistructured data has recognizable organization, such as tags, keys, or document structure, but it does not require every record to follow the same fixed table schema. In this scenario, the JSON feed has consistent keys that make fields identifiable, while optional nested objects can vary across partners. That combination fits semistructured data and supports ingestion before a final relational design is complete. Structured data would imply a predefined, consistent schema such as rows and columns with enforced data types. Unstructured data lacks reliable field markers, such as free-form documents or images. The key takeaway is that JSON or XML with meaningful keys or tags is usually semistructured when the shape can vary.

• Fixed schema assumption fails because the optional nested objects vary before a final relational schema exists.
• No organization assumption fails because the JSON keys provide identifiable fields and partial structure.
• Binary classification fails because the feed is text-based JSON records, not opaque binary content.

Question 54

Topic: Data Integration

A DBA is troubleshooting an acquired CSV before it is loaded into staging. The source team says the manifest was generated from the file they published. No transformation rules have run yet. What is the best next action?

Exhibit: Acquisition validation

Options:

• A. Increase the database connection timeout and rerun the load

• B. Proceed because the row count and delimiter match

• C. Reacquire the file and validate its checksum before loading

• D. Modify the target column mappings in staging

Best answer: C

Explanation: Integrity checks should be validated before trusting acquired data. A matching row count only shows that the same number of records was detected; it does not prove the file content is intact. The SHA-256 mismatch means the landing file is not byte-for-byte the same as the file described by the source manifest. The invalid UTF-8 replacements provide additional evidence that bytes were changed, lost, or interpreted incorrectly during acquisition. The safest next step is to reacquire the source file and verify the checksum before loading it into staging or applying transformations. Mapping changes are premature because the problem appears before staging load logic runs.

• Schema mapping is not supported because no transformation or staging load has run yet.
• Row count trust fails because row counts can match even when field values or bytes are corrupted.
• Connection timeout is not indicated; the exhibit shows content validation failures, not an unavailable database connection.

Question 55

Topic: Data and Database Security

A company is moving a customer database containing PII to a managed DBaaS platform. Database administrators must use corporate MFA, access must be removed automatically when HR disables a user, and permissions should follow centralized job-role groups rather than locally managed database accounts. Which action is the BEST professional decision?

Options:

• A. Use one shared privileged database account stored in a password vault

• B. Federate the DBaaS with the corporate IdP and map IAM groups to database roles

• C. Create individual local database logins and review them monthly

• D. Issue client certificates directly to each DBA without IdP integration

Best answer: B

Explanation: Centralized identity governance is best supported by federated identity and IAM-based authorization. In this scenario, the corporate identity provider is the authoritative source for user status, MFA, and group membership. Federating the DBaaS with that IdP allows database access decisions to reflect HR-driven deprovisioning and corporate authentication policy. Mapping IAM groups to database roles also separates identity lifecycle management from database permissions while preserving least privilege.

Local database accounts can work technically, but they create a second identity store that must be synchronized and audited separately. Shared accounts weaken accountability. Certificates can strengthen authentication, but certificate issuance alone does not satisfy centralized governance or job-role authorization.

• Local logins create a separate identity lifecycle and may not immediately reflect HR-disabled users.
• Shared privileged access weakens individual accountability and conflicts with least-privilege administration.
• Certificate-only access may authenticate a device or user, but it does not provide centralized group-based authorization by itself.

Question 56

Topic: Data Integration

A DBA is helping standardize a small data-integration validation step before loading weekly exports into a reporting database. The step must run in a Python environment, compare tabular CSV extracts to expected row counts and column summaries, and perform basic cleansing and aggregation. No model training or statistical computing platform migration is required. Which library context is the BEST fit?

Options:

• A. MATLAB

• B. Tidyverse

• C. pandas

• D. scikit-learn

Best answer: C

Explanation: pandas is commonly used in Python for dataframe-based data manipulation and analysis. In this scenario, the DBA needs a lightweight validation step for CSV extracts: compare row counts, summarize columns, cleanse values, and aggregate tabular data before database loading. Those tasks align directly with pandas. NumPy supports efficient numerical arrays and is often used underneath pandas, but it is less focused on labeled tabular data workflows. scikit-learn is mainly a machine learning library for model training and evaluation. Tidyverse provides similar data manipulation concepts, but in the R ecosystem rather than Python. MATLAB is a separate numerical computing and analysis environment, not the best fit for this Python-based integration step.

• Modeling library fails because scikit-learn is aimed at machine learning workflows, not basic CSV validation and cleansing.
• R ecosystem fails because Tidyverse is appropriate for R-based data manipulation, while the requirement specifies Python.
• Separate platform fails because MATLAB would introduce a different analysis environment when no migration is required.
• Array focus would make NumPy plausible for numeric computation, but the stated need is labeled tabular manipulation.

Question 57

Topic: Database Fundamentals

A DBA reviews a nightly maintenance script that marks overdue invoices. The same status value is applied to every qualifying row, and there are no per-row calculations, external calls, or exception rules.

Exhibit: Proposed script excerpt

FOR each row IN (
  SELECT invoice_id
  FROM invoice
  WHERE status = 'open'
    AND due_date < CURRENT_DATE
)
LOOP
  UPDATE invoice
  SET status = 'past_due'
  WHERE invoice_id = row.invoice_id;
END LOOP;

Which interpretation is best supported by the exhibit?

Options:

• A. Keep the loop to preserve row order.

• B. Replace the update with delete and insert steps.

• C. Rewrite it as one set-based UPDATE.

• D. Add DISTINCT to the cursor query.

Best answer: C

Explanation: Set-based logic treats the qualifying rows as a set and lets the database engine apply one operation to that set. In this case, every invoice that matches status = 'open' and an overdue due_date receives the same new value. There is no row-specific calculation or side effect that requires procedural looping. The equivalent operation is a single UPDATE invoice SET status = 'past_due' WHERE status = 'open' AND due_date < CURRENT_DATE. This is typically clearer and more efficient because the database can optimize the operation as one relational statement. Row-by-row logic is mainly justified when each row needs distinct procedural handling that cannot be expressed safely in SQL.

• Row order is not relevant because the final status value does not depend on processing sequence.
• Using DISTINCT does not address the procedural loop and is unnecessary when invoice_id is already the row identifier.
• Delete and insert changes the operation’s meaning and can disrupt keys, constraints, auditing, or related rows.

Question 58

Topic: Data and Database Security

A DBA supports a customer database that stores PII and payment tokens. The database is hosted in a managed cloud database service and is accessed by an internal web application. An audit found shared administrator credentials, broad network access from the corporate VPN, optional TLS for client connections, and no column-level protection for sensitive fields. Which remediation plan is the best professional decision?

Options:

• A. Add a web application firewall and leave database permissions unchanged

• B. Apply RBAC with MFA, enforce TLS, restrict network access, and mask sensitive columns

• C. Enable at-rest encryption and keep the existing administrator access model

• D. Move the database on-premises and block all remote application connections

Best answer: B

Explanation: Defense in depth uses multiple complementary controls so one failed control does not expose the whole database. In this scenario, the audit findings span several layers: identity has shared admin credentials, network access is too broad, client connections do not consistently use encryption in transit, and sensitive columns lack database-level protection. A balanced remediation should apply least privilege with named identities and MFA, require TLS, narrow allowed network paths to the application or approved subnet, and add masking or similar protection for sensitive fields. Enabling only one control leaves known gaps. Rehosting the database is not necessary when the stated risks can be mitigated with layered controls in the current managed service.

• At-rest only misses the shared administrator account, optional TLS, broad network access, and exposed sensitive columns.
• Rehosting overcorrects the deployment model and disrupts application access without directly solving least privilege or column protection.
• WAF only may help at the application edge, but it does not fix database identities, permissions, TLS enforcement, or sensitive-field exposure.

Question 59

Topic: Database Deployment

A company is deploying a distributed database for a global activity-feed service. Users can post reactions from any region, the feed must remain writable during regional network partitions, and a reaction count may be briefly stale as long as replicas converge later. Which consistency approach best fits this requirement?

Options:

• A. Two-phase commit across all regions

• B. BASE with eventual consistency

• C. ACID with serializable transactions

• D. Single-primary relational transactions only

Best answer: B

Explanation: ACID prioritizes strong transaction guarantees: atomicity, consistency, isolation, and durability. It is a strong fit when every transaction must be immediately correct, such as payments or inventory decrementing. BASE prioritizes basically available, soft-state, eventually consistent behavior. In this scenario, writes must continue during partitions and stale reaction counts are acceptable if replicas converge later. That is the classic trade-off where BASE and eventual consistency are preferred over strict cross-region transactional consistency.

The key distinction is not relational versus NoSQL by itself; it is the workload’s tolerance for temporary inconsistency in exchange for higher availability.

• Serializable transactions add strong isolation but reduce availability during cross-region partition scenarios.
• Two-phase commit coordinates all participants, which conflicts with the need to keep accepting writes during partitions.
• Single-primary transactions can provide consistency, but they do not best match global writable availability with acceptable stale reads.

Question 60

Topic: Database Management and Maintenance

A DBA receives alerts for an OLTP order-entry database. Users report intermittent transaction rollbacks during peak load, but CPU and disk I/O are within baseline. The application must preserve referential integrity and cannot use dirty reads.

Exhibit: Session monitor excerpt

Which action is the BEST professional decision?

Options:

• A. Investigate a deadlock and review the deadlock graph

• B. Enable dirty reads for the order-entry transactions

• C. Increase CPU capacity for the database server

• D. Increase the application connection pool size

Best answer: A

Explanation: Deadlock monitoring evidence appears when two or more transactions form a circular wait: each transaction holds a lock and waits for a resource locked by another transaction in the same cycle. In the exhibit, session 54 holds Orders:1001 and waits for Inventory:15, while session 77 holds Inventory:15 and waits for Orders:1001. Because CPU and disk I/O are normal, this is not primarily a resource-capacity problem. The professional next step is to review deadlock details, such as a deadlock graph or lock report, and then remediate the transaction pattern, for example by using a consistent object access order or shortening transaction duration. Lowering isolation to dirty reads would violate the stated integrity constraint.

• CPU capacity misses the visible circular lock wait and conflicts with normal CPU and I/O metrics.
• Dirty reads may reduce blocking, but they violate the requirement to preserve integrity.
• Connection pool growth can make contention worse by allowing more concurrent transactions to compete for the same rows.

Question 61

Topic: Database Deployment

A DBA is preparing the final deployment report for a new order database. Stress and regression tests met the required KPIs, backup restore testing succeeded, and schema validation passed. However, the production connection approval for a dependent inventory service is still pending, and the cutover decision is due today. What should the report emphasize?

Options:

• A. Risk status with escalation for the pending approval

• B. Readiness status marking the deployment complete

• C. Maintenance status scheduling later index review

• D. Performance status showing that KPIs were met

Best answer: A

Explanation: Deployment reporting should match the decision the audience needs to make. In this case, performance testing, restore testing, and schema validation are favorable, but a required production connection for a dependent service is not approved. That unresolved dependency can block or disrupt cutover, so the report should communicate risk status and identify the needed escalation. A readiness report would be appropriate only if required prerequisites were satisfied or residual risks were acceptable and documented. A performance report alone would omit the main deployment decision issue.

• Performance-only reporting is tempting because KPIs passed, but it misses the unresolved dependency that affects cutover.
• Complete readiness is not supported because a required production connection approval is still pending.
• Post-deployment maintenance may be useful later, but it does not address the immediate go/no-go risk.

Question 62

Topic: Database Deployment

A DBA is reviewing a production deployment request for a new reporting database. The deployment SOP says the release can proceed only after prerequisite checks and resource projections are complete.

Exhibit: Deployment readiness summary

Which interpretation is best supported by the exhibit?

Options:

• A. Escalate only if the SLA is missed after deployment

• B. Block the release pending storage and backup planning

• C. Proceed and monitor storage growth after go-live

• D. Proceed because network and escalation prerequisites are complete

Best answer: B

Explanation: Deployment readiness is not based on one approved prerequisite. A production database release also requires capacity planning and operational validation before go-live. In the exhibit, the firewall rule and escalation contact are complete, but the 12-month storage growth estimate is missing even though the target allocation is only 2 TB for a 1.6 TB database. The backup duration test is also not performed, which means the team has not validated whether backup operations fit the operational window or recovery expectations. These gaps are deployment blockers under the stated SOP because prerequisites and resource projections must be complete before release.

The key takeaway is that incomplete planning data can block deployment even when some technical prerequisites are approved.

• Network-only readiness fails because approved connectivity does not replace storage projections or backup validation.
• Post-go-live monitoring fails because it delays required capacity planning until after the release risk exists.
• Reactive escalation fails because escalation is for handling issues, not for bypassing incomplete deployment readiness checks.

Question 63

Topic: Data and Database Security

A database administrator is preparing a replicated reporting database for a marketing analytics team. The team needs daily trend reports by city, age range, and loyalty tier, but it does not need customer names, email addresses, street addresses, or exact birth dates. The production OLTP database must remain unchanged. Which data-protection technique is the BEST professional decision?

Options:

• A. Suppress all demographic fields from the reporting copy

• B. Grant analysts read-only access to production views

• C. Anonymize and generalize data in the reporting copy

• D. Encrypt the PII columns at rest in production

Best answer: C

Explanation: The core trade-off is preserving business utility while reducing privacy exposure. For analytics that needs trends by city, age range, and loyalty tier, an anonymized or generalized reporting copy fits best: names and emails can be removed, exact birth dates can be converted to age bands, and street addresses can be excluded or reduced to city-level attributes. This supports the required reports without exposing unnecessary PII and without changing the production OLTP workload. Encryption at rest protects storage media but does not limit what authorized analysts can query. Suppressing all demographics would reduce exposure but would also break the stated reporting need.

• Encryption only protects stored data but does not reduce exposure to users who are allowed to query decrypted results.
• Over-suppression removes fields the analytics team explicitly needs for city and age-range reporting.
• Production access increases operational and privacy risk when a reporting copy can meet the business need.

Question 64

Topic: Business Continuity

A DBA supports an on-premises order database with a 15-minute RPO and a 1-hour RTO. Transaction log backups are already copied off-site every 5 minutes, but the last DR exercise exceeded the RTO because operators manually restored logs in the wrong order and forgot the post-restore validation query. What is the BEST professional decision?

Options:

• A. Automate the restore sequence and validation checks

• B. Increase full backup frequency to every hour

• C. Require a manager to approve each restore step

• D. Rewrite the DR runbook with more detailed screenshots

Best answer: A

Explanation: Continuity processes should be automated when manual steps create recovery risk, especially when an organization has defined RPO and RTO targets. In this scenario, the backup cadence already supports the 15-minute RPO, but the recovery test failed because people performed ordered restore and validation tasks incorrectly. Automating the restore sequence, checks, and alerts makes the process repeatable, auditable, and less dependent on operator memory during an incident. The automation should still be tested and documented, but the primary improvement is reducing manual recovery variance. Adding more approvals or documentation does not remove the operational failure point that caused the missed RTO.

• More full backups does not address the failed manual log restore sequence that caused the RTO miss.
• Better screenshots may help training, but they still leave critical recovery steps manual.
• Extra approvals can slow recovery and increase handoff risk during a time-sensitive incident.

Question 65

Topic: Database Deployment

A DBA is preparing network access for a new OLTP database. The application tier runs in a perimeter network and scales automatically, so its instance IP addresses can change. The database must remain unavailable from the public internet, support failover across two zones, and accept connections only on its documented listener: TCP port 5432. Which configuration is the BEST professional decision?

Options:

• A. Public static database IP restricted to the application instances

• B. Private single-zone database with manual IP allowlisting

• C. Database in the perimeter network with all TCP ports allowed

• D. Private multizone database with firewall access from the app subnet to TCP 5432

Best answer: D

Explanation: Database connectivity should be limited by network placement, firewall scope, port, and protocol. Because the application instances use dynamic IPs, rules tied to individual instance addresses are fragile. A stable source such as the application subnet, security group, or equivalent network identity is safer. Keeping the database on private network addresses prevents direct public internet access, while allowing only TCP 5432 enforces least privilege. A multizone deployment supports availability by keeping database service reachable during a zone failure or failover event. The key is to combine private placement, narrow firewall rules, and multizone design without exposing the database or overbroadly opening ports.

• Public static IP reduces allowlist complexity but violates the requirement to keep the database unavailable from the public internet.
• All TCP ports ignores least privilege and increases the database attack surface.
• Manual IP allowlisting is brittle because autoscaled application instances can receive dynamic IP addresses, and single-zone placement weakens availability.

Question 66

Topic: Database Deployment

A team is validating a new order-processing database before cutover. The requirements are to verify that common lookup queries will perform acceptably, source fields are loaded into the correct target columns, status and amount fields contain only allowed values, and every order references an existing customer. Which validation approach best addresses these requirements?

Options:

• A. Run a backup test, compare row counts, encrypt columns, and review users

• B. Analyze candidate indexes, reconcile mappings, profile values, and check foreign keys

• C. Normalize all tables, archive old rows, mask PII, and rebuild statistics

• D. Increase storage, run stress tests, enable auditing, and schedule maintenance

Best answer: B

Explanation: Deployment validation should map each requirement to the type of evidence that proves it. Index analysis validates whether important queries have appropriate access paths and selectivity. Data mapping validation confirms each source field lands in the intended target column with the expected transformation. Data value checks validate domains, ranges, formats, and nullability, such as allowed statuses or nonnegative amounts. Referential integrity checks verify relationships, such as each orders.customer_id matching an existing customer key. A general infrastructure or security review may be useful, but it does not prove these specific deployment requirements.

• Backup and security checks are useful operational controls, but they do not validate index suitability, field mappings, value domains, or foreign keys.
• Normalization and masking may support design and privacy goals, but they do not directly prove the cutover data loaded correctly.
• Capacity and auditing address operations and monitoring, but they miss the required data-quality and relational-integrity checks.

Question 67

Topic: Data Integration

A DBA must acquire customer support records from a vendor system into an analytics database. Which acquisition method best fits the constraints in the integration note?

Exhibit: Integration note

Options:

• A. ODBC extraction directly from the vendor database

• B. ETL using the API, then tokenize and flatten before loading

• C. FTP delivery of CSV files from the vendor

• D. ELT by loading raw JSON into staging, then tokenize later

Best answer: B

Explanation: The acquisition method must match all visible constraints: source connectivity, data format, security handling, and transformation timing. The note says the vendor exposes only a REST API with OAuth 2.0, so a database driver or file-transfer approach is not supported. Because email and phone values must be tokenized before storage, the pipeline should transform sensitive data before loading it into the analytics database. That makes ETL the better fit than ELT in this scenario. The nested JSON can be parsed and flattened during the transform step, then loaded into relational target tables.

The key distinction is transformation timing: ELT is often useful for loading first and transforming inside the target, but it conflicts with the stated pre-storage tokenization requirement.

• Raw JSON staging fails because sensitive fields would be stored before tokenization.
• ODBC extraction fails because the vendor provides REST API access only, not direct database connectivity.
• FTP and CSV fail because the source format and delivery method are JSON over API, not files.

Question 68

Topic: Data Integration

A DBA is creating a data integration runbook so analysts can choose the right analysis context for profiling extracts, preparing training data, and validating transformations. Which runbook entry correctly identifies the common library or tool contexts?

Options:

• A. NumPy R packages, pandas numerical computing suite, scikit-learn spreadsheets, Tidyverse Python, MATLAB SQL engine

• B. NumPy relational storage, pandas backup automation, scikit-learn ETL scheduling, Tidyverse monitoring, MATLAB access control

• C. NumPy visualization, pandas model training, scikit-learn SQL, Tidyverse MATLAB, MATLAB R packages

• D. NumPy arrays, pandas DataFrames, scikit-learn ML, Tidyverse R, MATLAB numerical analysis

Best answer: D

Explanation: These tools are commonly recognized by their analysis context. NumPy is a Python foundation for numerical arrays and vectorized operations. pandas is used heavily for tabular data manipulation with DataFrames, such as filtering, joining, reshaping, and profiling extracts. scikit-learn is a Python machine learning library used for model training and evaluation workflows. Tidyverse is a collection of R packages used for data wrangling, visualization, and analysis. MATLAB is a numerical computing and analysis environment often used in engineering, scientific, and matrix-oriented workloads. The key is to identify the context, not a specific command or syntax.

• Swapped functions fails because pandas is not primarily a model-training library and scikit-learn is not a SQL engine.
• Wrong ecosystems fails because Tidyverse belongs to R, while pandas and NumPy are Python contexts.
• DBA operations fails because these tools are analysis libraries or environments, not backup, monitoring, or access-control features.

Question 69

Topic: Database Management and Maintenance

A database team needs to refresh a development environment before testing a release that changes several tables and stored procedures. Developers need representative row counts and data distributions for query testing, but the production database contains PII and cannot be affected during business hours. Which refresh approach is the best professional decision?

Options:

• A. Generate a small synthetic dataset from the new schema only

• B. Copy selected production tables manually during business hours

• C. Point development directly to production using read-only accounts

• D. Restore a recent production backup to isolated development, mask PII, then run validation checks

Best answer: D

Explanation: A database refresh should give test or development teams data that is close enough to production to validate behavior, performance, and data relationships without creating unnecessary operational or security risk. In this scenario, the important constraints are representative data, PII protection, release validation, and no production disruption. Restoring from a recent backup or snapshot into an isolated non-production environment supports realistic row counts and distributions. Masking or anonymizing sensitive columns reduces exposure of PII. Validation checks after the refresh, such as schema version, referential integrity, and basic row-count checks, help confirm that the environment is usable for testing the release.

• Read-only production access still exposes production to non-production testing risk and does not create an isolated refresh target.
• Small synthetic data may be useful for unit tests, but it does not satisfy the representative distribution requirement.
• Manual table copies can disrupt production and may break dependencies, referential integrity, or release validation consistency.

Question 70

Topic: Database Management and Maintenance

A database operations manager receives CPU and disk-space alerts, but weekly reviews still miss failed index maintenance jobs until query performance degrades. The team needs a low-noise way for managers to see whether scheduled maintenance completed successfully across all production databases. Which improvement best closes this visibility gap?

Options:

• A. Lower the CPU utilization alert threshold

• B. Increase database transaction log retention

• C. Enable more detailed query execution plans

• D. Add a scheduled maintenance status report

Best answer: D

Explanation: The gap is not a lack of system-health telemetry; managers already receive CPU and disk-space alerts. The missing visibility is job-level reporting for scheduled maintenance outcomes. A recurring maintenance status report or dashboard can summarize completed, failed, skipped, and late jobs across production databases, making the review process proactive and management-friendly. It also reduces noise because it reports the operational state managers need instead of generating more infrastructure alerts.

Changing performance thresholds, retaining logs longer, or collecting query plans may help troubleshooting, but they do not directly show whether scheduled maintenance succeeded.

• Lower CPU threshold adds more resource alerts, but the problem is missed job outcomes, not undetected CPU pressure.
• Longer log retention may preserve evidence for investigation, but it does not create routine management visibility.
• Detailed query plans support query tuning after degradation, but they do not report maintenance job completion status.

Question 71

Topic: Database Deployment

A DBA is preparing the deployment status report for a new order-processing database before the go-live decision. Which report interpretation is best supported by the exhibit?

Exhibit: Pre-deployment validation summary

Options:

• A. Post-deployment performance trend only

• B. Deployment risk due to failed performance KPIs

• C. Ready for deployment because regression passed

• D. Data integrity risk due to schema incompatibility

Best answer: B

Explanation: Deployment reporting should summarize the status that decision-makers need for the current phase: readiness, risk, or performance. In this case, schema compatibility and regression tests support functional readiness, but the performance KPIs do not meet the stated targets. Because the exhibit also states that any failed KPI requires escalation, the report should communicate deployment risk tied to performance, not a simple ready status. The key is to report the status supported by the evidence and the escalation rule, rather than overemphasizing only the checks that passed.

• Regression-only readiness fails because passing functional tests does not override failed performance KPIs.
• Schema incompatibility is not supported because the schema compatibility check passed.
• Trend-only reporting is incomplete because the failed KPI rule requires escalation before go-live.

Question 72

Topic: Database Management and Maintenance

An OLTP database has started returning intermittent read errors, and the error log shows page checksum warnings for one table. Users must remain online if possible, and the recovery objective is no more than 15 minutes of data loss. Which action is the best professional decision?

Options:

• A. Disable foreign keys and reload the affected table

• B. Rebuild all indexes during the next maintenance window

• C. Restore the last full backup immediately

• D. Run integrity checks and validate restoreable backups

Best answer: D

Explanation: When database reliability is in question, the DBA should first confirm whether corruption exists, determine its scope, and verify that recovery options are usable. Page checksum warnings point to possible physical data corruption, not just a performance issue. Running database integrity or consistency checks, reviewing related logs, and validating that backups can be restored supports an informed decision with the stated RPO. Immediate repair or restore without validation can increase data loss or downtime, while index maintenance does not prove that the underlying data pages are reliable. The key is to assess integrity and confirm a safe recovery path before taking disruptive action.

• Index rebuilds may help fragmentation or performance, but they do not reliably diagnose page-level corruption.
• Disabling constraints can hide integrity problems and risks introducing more inconsistent data.
• Immediate full restore may ignore the 15-minute RPO and should not be attempted before validating restore points and logs.

Question 73

Topic: Database Management and Maintenance

A DBA receives the following overnight monitoring summary for a production order database. Which interpretation and next action are best supported by the exhibit?

Options:

• A. Address backup target capacity and rerun the failed full backup

• B. Increase CPU capacity for the database server

• C. Disable growth alerts until the database reaches 2.00 TB

• D. Ignore the alert because index maintenance completed successfully

Best answer: A

Explanation: The monitoring summary shows two important storage-related conditions: the data volume is close to its 2.00 TB limit, and the full backup failed because the backup target has no free space. A completed index maintenance job does not offset a failed backup. The best immediate action is to restore backup capacity and rerun the failed backup so the database has a current recoverable copy. After that, the DBA should also plan for database storage growth because the current growth rate will reach the limit soon. The key distinction is that a job completion alert for one task does not make a separate failed backup job safe to ignore.

• Completed maintenance is a distraction because index maintenance success does not prove backup success.
• CPU capacity is not supported because the exhibit identifies storage and backup target space, not processor pressure.
• Disabling alerts increases risk because the database is already near its limit and the backup has failed.

Question 74

Topic: Database Deployment

A team is deploying an order-processing database. Creating an order must insert the order row, decrement inventory, and record payment status. If any step fails, the database must return to its prior valid state, and other sessions must not see a partially completed order. Which implementation choice best meets this requirement?

Options:

• A. Run each statement in autocommit mode

• B. Use one ACID transaction with rollback on failure

• C. Write the order first, then batch-update inventory later

• D. Use eventual consistency with asynchronous reconciliation

Best answer: B

Explanation: ACID is the appropriate model when transaction integrity and immediate consistency are decisive. In this scenario, the order insert, inventory decrement, and payment status update form one business unit of work. Atomicity ensures all steps commit or none do, consistency ensures rules leave the database in a valid state, isolation prevents other sessions from seeing partial work, and durability preserves committed results after completion. Eventual consistency and delayed updates can be useful for scalable distributed workloads, but they allow temporary divergence that does not meet the stated requirement.

• Eventual reconciliation fails because it permits temporary inconsistency instead of enforcing one all-or-nothing transaction.
• Autocommit statements fail because one statement could commit before a later step fails.
• Batch inventory updates fail because they separate dependent changes and can expose invalid order or stock states.

Question 75

Topic: Business Continuity

A production order database is unavailable because the primary node has failed. Monitoring shows the standby replica is healthy and 2 minutes behind the primary. The business requires service restoration within 15 minutes and accepts up to 5 minutes of data loss. What action should the DBA prioritize?

Options:

• A. Restore the latest full backup to new storage

• B. Take an immediate backup of the failed primary

• C. Fail over applications to the standby replica

• D. Wait for the primary node to recover automatically

Best answer: C

Explanation: During a database availability event, the first priority is to restore service in a way that satisfies the stated continuity metrics. Here, the standby replica is healthy and only 2 minutes behind, which is within the 5-minute RPO. Failing over to that replica is also the most likely action to meet the 15-minute RTO. A restore from backup may be necessary later for rebuilding the failed primary or recovering data, but it is not the fastest availability action when a usable replica already exists.

• Backup restore delay fails because restoring a full backup is typically slower than using a healthy standby replica.
• Waiting for recovery fails because it risks missing the 15-minute RTO without improving availability.
• Backing up the failed node fails because it does not restore service and may not be possible while the primary is unavailable.

Questions 76-90

Question 76

Topic: Data Integration

A DBA uses a generative AI assistant to draft an ETL mapping from a CRM API into a customer warehouse table. The mapping includes PII fields, must preserve referential integrity with customer_id, and will run in a nightly production load. The AI output looks plausible, but the DBA cannot find two of the source fields in the CRM API documentation. What is the best professional decision?

Options:

• A. Replace the ETL process with manual data entry

• B. Validate the mapping against source documentation and test data before deployment

• C. Use the mapping only if the AI cites a RAG source

• D. Deploy the mapping because the AI output is syntactically valid

Best answer: B

Explanation: Generative AI can produce hallucinations: plausible-looking fields, joins, transformations, or constraints that are not actually present in the source system. In this scenario, missing source fields are a direct warning sign. Because the load handles PII and affects referential integrity in production, the DBA should validate the AI-generated mapping against the CRM API documentation, data dictionary, sample source records, and test-load results before deployment. Human oversight is part of responsible AI use in database and integration work.

Syntactic validity or a confident AI response does not prove the mapping is correct. The key takeaway is to treat AI output as a draft that requires verification.

• Syntax-only trust fails because valid-looking ETL code can still reference nonexistent fields or apply incorrect transformations.
• RAG citation reliance fails because cited context can reduce risk but does not remove the need to verify the actual mapping.
• Manual replacement is excessive because the issue is validation of generated output, not a need to abandon automation.

Question 77

Topic: Business Continuity

A company requires a customer database to tolerate up to 15 minutes of data loss and remain available if the primary database server fails. The proposed design uses a nightly full backup and transaction log backups every 15 minutes to off-site storage. There is no standby database instance or automated failover.

Which implementation choice best addresses the missing requirement?

Options:

• A. Increase transaction log backups to every 5 minutes

• B. Add a replicated standby database with failover

• C. Encrypt all backup files before storage

• D. Store another backup copy in a second off-site location

Best answer: B

Explanation: Backups protect recoverability of data, but they do not keep the database service running when the primary server fails. In this scenario, the 15-minute log backup interval can support the data-loss target, but restoring backups requires time and a working database host. The missing availability control is redundancy with a standby database and a failover process, such as replication to another instance that can take over service. Backup frequency, backup location, and backup encryption improve recovery point, resilience of stored copies, or confidentiality, but they do not by themselves provide continuous service.

• More frequent backups may reduce potential data loss, but they still require a restore before users can reconnect.
• Extra off-site copies improve backup survivability, but they do not create a running replacement database service.
• Backup encryption protects stored backup confidentiality, but it does not address failover or uptime.

Question 78

Topic: Database Deployment

A company is moving an order-entry database to a cloud-hosted environment. The DBA must choose a deployment approach that matches the persistence and administration requirements in the planning note.

Exhibit: Planning note

Data: durable relational order data
Required admin control: schema, indexes, users, query tuning
Provider-managed: OS patching, database engine patching, backups, HA
Not required: application feature ownership by the provider

Which deployment approach best matches these requirements?

Options:

• A. SaaS order management application

• B. Ephemeral container storage

• C. Managed relational DBaaS

• D. Self-managed database on IaaS

Best answer: C

Explanation: Deployment models differ in who owns persistence and administration. The exhibit requires durable relational data and DBA control over database-level objects such as schemas, indexes, users, and query tuning. It also explicitly assigns OS patching, engine patching, backups, and high availability to the provider. That combination fits a managed relational DBaaS: the database remains persistent and administrable, but infrastructure and much of routine platform maintenance are handled by the cloud provider.

Self-managed IaaS would give more control but would also keep OS and engine maintenance with the customer. SaaS would usually hand over the application itself, which the note says is not required.

• IaaS overcontrol fails because the customer would still administer the VM, OS, and database engine patching.
• SaaS application ownership fails because the provider would own the application features, not just the database platform.
• Ephemeral storage fails because order data requires durable persistence, not temporary storage tied to a container lifecycle.

Question 79

Topic: Database Fundamentals

A DBA supports a nightly Python reconciliation script that updates order status rows in a production relational database. When an analyst runs it from a laptop over VPN, it often times out, stops if the laptop disconnects, and uses the analyst’s broad personal database role. The database is reachable only from the private application subnet. Which recommendation is the BEST professional decision?

Options:

• A. Run it from a public cloud function with embedded database credentials

• B. Give the analyst a permanent DBA role for the script

• C. Run it from an approved in-subnet scheduler with a least-privileged service account

• D. Increase the VPN timeout and keep the analyst-run process

Best answer: C

Explanation: Script runtime location can directly affect database access, security, and performance. A laptop over VPN is a poor runtime for a production nightly job because connectivity is fragile, execution depends on a user device, and personal elevated credentials weaken accountability and least privilege. Running the script from an approved scheduler or job host inside the private application subnet keeps network paths stable and close to the database while allowing a dedicated service account with only the permissions needed for the reconciliation task. The key DBA decision is not just where the code is stored, but where it executes and under which identity.

• Longer VPN timeout may reduce one symptom but still depends on a user laptop and broad personal access.
• Permanent DBA role violates least privilege and does not solve unstable runtime connectivity.
• Public function with embedded credentials conflicts with private subnet access and creates credential-management risk.

Question 80

Topic: Data and Database Security

A DBA is reviewing access for a production healthcare database that stores PHI and supports a 24/7 OLTP application. The organization requires RBAC, least privilege, and auditable change records. The application service account is not part of this finding.

Audit excerpt:

Which action is the BEST professional decision?

Options:

• A. Disable the expired account and reduce the reporting role to read-only access

• B. Enable encryption at rest for the patient tables

• C. Leave access unchanged until the next annual access review

• D. Reset passwords for both principals and keep current permissions

Best answer: A

Explanation: Expired accounts and excess privileges are authorization risks because they create unnecessary paths to sensitive data. In this scenario, the contractor account no longer has a business need, so it should be disabled or deprovisioned through an auditable change. The reporting role still has a valid purpose, but UPDATE and DELETE exceed a reporting need and violate least privilege. Reducing it to read-only access aligns with RBAC while preserving availability for the 24/7 application because the application service account is not affected. The key is to remediate the specific access findings rather than apply unrelated controls or delay action.

• Annual review delay fails because known expired access and excessive permissions should not remain active after discovery.
• Password reset only fails because authentication changes do not remove unnecessary authorization.
• Encryption at rest helps protect stored data but does not correct who is allowed to access or modify it.

Question 81

Topic: Database Deployment

A DBA is reviewing two proposed database workloads before selecting schema and deployment patterns. Which interpretation is best supported by the exhibit?

Options:

• A. Sales app is OLAP; revenue mart is OLTP.

• B. Both workloads are OLAP because both may use SQL queries.

• C. Both workloads are OLTP because both store business data.

• D. Sales app is OLTP; revenue mart is OLAP.

Best answer: D

Explanation: OLTP workloads support day-to-day transactions, typically many small inserts, updates, deletes, and point reads with low-latency needs. The sales app matches OLTP because it processes active orders and needs fast lookups by order ID. OLAP workloads support analysis and reporting, often using batch-loaded historical data, wide scans, joins, and aggregations. The revenue mart matches OLAP because it consolidates multiple sources and serves monthly trend dashboards. The deciding factor is the workload pattern and access need, not whether the system stores business data or uses SQL.

• Reversing the labels fails because batch-loaded dashboard aggregation is analytical, not transaction processing.
• Calling both OLTP ignores the revenue mart’s historical, cross-source aggregation pattern.
• Calling both OLAP confuses the query language with the workload type; OLTP systems can also use SQL.

Question 82

Topic: Data and Database Security

A hospital reporting database must allow clinicians to view patient records only when the clinician is assigned to the patient, the record is tagged for that clinic location, and the request is made from an approved hospital network during the clinician’s shift. Which authorization design best meets this requirement?

Options:

• A. Attribute-based access control policy

• B. Shared service account

• C. Database ownership chaining

• D. Role-based access control group

Best answer: A

Explanation: Attribute-based access control (ABAC) is the best fit when authorization depends on multiple attributes at decision time. In this scenario, the database must evaluate user attributes (assigned clinician), resource attributes (patient record and clinic location), and environmental or context attributes (network and shift time). A simple role such as clinician is not specific enough because two users with the same role may need different access based on assignment, location, or time. The key takeaway is that ABAC supports context-aware authorization beyond static group membership.

• Role-only access fails because a clinician role cannot by itself enforce patient assignment, location, network, and shift constraints.
• Shared account use weakens accountability and cannot express per-user contextual authorization.
• Ownership chaining controls object access paths within a database, not dynamic access decisions based on user and environment attributes.

Question 83

Topic: Database Management and Maintenance

A database has an Orders table that stores customer_name, customer_email, and customer_phone on every order row. Customer contact changes are applied manually to each order, and audit reports show different phone numbers for the same customer. Which data-management task best improves integrity and maintainability?

Options:

• A. Add a computed column for the customer phone number

• B. Create a view that hides duplicate customer columns

• C. Move customer attributes to a Customers table with a foreign key

• D. Denormalize orders into a wider reporting table

Best answer: C

Explanation: The core issue is an update anomaly caused by storing the same customer facts in many order rows. Normalization improves data integrity and maintainability by placing customer attributes in one Customers table and referencing that row from Orders with a foreign key. This reduces duplicate updates, makes inconsistent contact data less likely, and clarifies the relationship between customers and orders. A view can make queries easier to read, but it does not remove the duplicated data. Denormalization can help some read-heavy reporting workloads, but it usually increases the need to manage consistency.

• Computed column fails because phone numbers are stored facts, not values derived from other columns.
• Denormalization fails because the symptom is inconsistent duplicated data, and a wider table would increase that risk.
• View-only change fails because it can improve usability but does not fix the underlying storage design.

Question 84

Topic: Business Continuity

A DBA is reviewing a risk analysis for an order-processing database that must remain available during a single component failure. Based on the exhibit, which item is the single point of failure that should be prioritized for remediation?

Exhibit: Architecture summary

Options:

• A. Shared storage array

• B. Synchronous standby database

• C. Off-site transaction log shipping

• D. Application server pool

Best answer: A

Explanation: A single point of failure is any component whose failure can interrupt the service because no independent redundant path exists. In this architecture, the application tier has multiple servers, and the database tier has a primary plus a synchronous standby in another zone. However, both database servers rely on one shared storage array. If that array fails, the primary and standby can both lose access to the database files, making the server-level redundancy ineffective. The risk analysis should prioritize independent or replicated storage to remove that dependency.

• Application pool is not the single point because three servers sit behind a load balancer.
• Synchronous standby improves availability because it provides a database server failover target in another zone.
• Log shipping supports recovery and reduces data loss, but it is not the shared dependency that can stop both DB servers.

Question 85

Topic: Database Management and Maintenance

A DBA is reviewing the morning health check after users report slow order-entry screens. Based on the baseline, daily usage, throughput, and log evidence, what is the most likely system health issue?

Exhibit: Health summary

DB log excerpt:
09:05:12 WARN read latency exceeded threshold for data files
09:06:44 WARN query timeout waiting on data file read

Options:

• A. Storage I/O degradation is reducing throughput

• B. Unexpected user demand is overloading the database

• C. CPU saturation is causing query timeouts

• D. A failed backup job is blocking transactions

Best answer: A

Explanation: System health assessment compares current activity to the normal baseline and then confirms the pattern with logs. In this exhibit, daily order volume and active sessions are within expected ranges, so the slowdown is not caused by unusual demand. CPU is also within its baseline range. The abnormal signals are reduced transactions per minute, much higher query latency, disk read latency far above baseline, and log entries showing waits on data file reads. Together, these point to a storage I/O problem affecting database responsiveness and throughput. The next investigation should focus on the storage path, disk subsystem, or related infrastructure rather than user volume or CPU capacity.

• Unexpected demand fails because order volume and active sessions are still within the normal baseline.
• CPU saturation fails because CPU utilization is normal while disk read latency is abnormal.
• Failed backup blocking is unsupported because the exhibit includes no backup job alert or blocking message.

Question 86

Topic: Database Deployment

A company moves an order-entry application from an on-premises database to a managed DBaaS instance in a private subnet. The schema was imported successfully, integrity checks pass, and a DBA can connect from a jump host. Application servers in a separate subnet receive TCP connection timed out when using the new database hostname and port. Security policy requires private connectivity only. What is the best next action?

Options:

• A. Change the database isolation level for order transactions

• B. Add indexes to the largest order tables

• C. Redesign the schema to reduce table dependencies

• D. Review routing, DNS, and firewall rules between subnets

Best answer: D

Explanation: A connectivity timeout after a successful database import usually points first to network configuration, not database design. In this scenario, the schema and integrity checks passed, and the database accepts connections from a jump host, so the database service is running and the design is not the immediate issue. The failure is limited to application servers in another subnet using the database hostname and port. For a private DBaaS deployment, the DBA should validate routing, DNS resolution, subnet rules, security groups, firewall ACLs, and allowed ports before changing schema or transaction behavior.

The key takeaway is to separate reachability failures from database modeling or performance problems.

• Schema redesign does not address a TCP timeout, especially when the imported schema already passes integrity checks.
• Isolation-level change affects transaction behavior, not whether an application server can open a network connection.
• Index tuning can improve query performance after connection, but it cannot fix blocked routing, DNS, or port access.

Question 87

Topic: Database Management and Maintenance

A DBA is reviewing a database update before the release window. Which change-control safeguard should be required before approving the update?

Exhibit: Change request excerpt

Change: Add NOT NULL column to orders and backfill values
Objects: orders, order_items, staging_orders
Deploy method: Paste SQL from ticket into production console
Script storage: Ticket attachment only
Validation: Developer tested on local database
Rollback: "Run reverse steps if needed"
Approval: Pending DBA review

Options:

• A. Run the SQL manually during the release window and monitor errors.

• B. Require an approved, version-controlled migration package with tested rollback steps.

• C. Disable referential constraints during deployment to prevent blocking.

• D. Approve the change after the DBA saves a copy of the ticket attachment.

Best answer: B

Explanation: Database change control should make every production update reproducible, reviewable, and reversible. The exhibit shows several integrity risks: manual console execution, scripts stored only as an attachment, local-only validation, and an undefined rollback. A proper safeguard is an approved migration package stored in version control, reviewed through the release process, tested in an appropriate environment, and paired with clear rollback steps. This protects the schema definition, data transformation, release history, and recovery path if the change fails. Manual execution may still occur in some organizations, but it should not replace controlled, auditable deployment artifacts.

• Ticket attachment only fails because it does not provide durable version history, controlled review, or reproducible deployment evidence.
• Manual console execution increases drift risk and makes it harder to prove exactly what changed in production.
• Disabling constraints can hide data-integrity problems and does not address release or rollback control.

Question 88

Topic: Data and Database Security

A company is deploying a customer database for an order-entry application. The database must accept connections from the application servers and from DBA workstations over the corporate VPN. No customer or partner systems should connect directly to the database. Which layered defense control best reduces the database attack surface while preserving required connectivity?

Options:

• A. Enable data masking on customer columns used by reports

• B. Expose the database listener publicly and require stronger passwords

• C. Place the database on a private network segment with allowlisted inbound rules

• D. Increase database audit logging for all login attempts

Best answer: C

Explanation: Attack surface reduction focuses on limiting the paths an attacker can use to reach the database. In this scenario, the database still needs connectivity from two trusted sources: application servers and DBA workstations through VPN. A layered defense approach would place the database in a private segment and enforce inbound allowlist rules for only those sources and required database ports. This combines logical network isolation with firewall or security-group style controls. Strong authentication, masking, and auditing are useful controls, but they do not remove unnecessary network exposure.

• Public listener leaves the database reachable from untrusted networks, increasing exposure even if passwords are stronger.
• Data masking protects displayed sensitive values but does not control who can connect to the database service.
• Audit logging improves detection and investigation, but it does not prevent unwanted connection attempts.

Question 89

Topic: Database Management and Maintenance

A DBA must apply a critical database engine security patch to a production OLTP system. The database supports 24/7 order entry, runs in a primary/standby high-availability configuration, and has a 15-minute RPO. Management wants the risk reduced this week without causing an extended outage. Which patch management action is the BEST professional decision?

Options:

• A. Test the patch, verify backups, then perform a rolling patch

• B. Delay the patch until the next quarterly maintenance window

• C. Disable replication, patch both servers, then reinitialize HA

• D. Patch the primary immediately during peak hours

Best answer: A

Explanation: Patch management should reduce security exposure without creating unnecessary availability or data-loss risk. For a 24/7 OLTP database with a primary/standby HA design, the safest practical approach is to validate the patch in a nonproduction environment, confirm a recent usable backup or restore point that supports the RPO, and then patch nodes in a rolling sequence. Patching the standby first, failing over when appropriate, and then patching the former primary can limit downtime while preserving service continuity. Monitoring after each step helps catch performance, integrity, or connection issues before moving to the next node.

The key takeaway is to combine testing, backup validation, and staged deployment instead of treating patching as either an emergency shortcut or a long deferral.

• Immediate primary patching ignores peak workload risk and could cause an avoidable outage if the patch fails.
• Quarterly delay leaves a critical security exposure open even though the scenario asks to reduce risk this week.
• Disabling replication removes the HA protection that should be used to preserve availability during the patch.

Question 90

Topic: Data and Database Security

A healthcare analytics team stores PHI in a database used by internal analysts. The organization must reduce accidental disclosure and provide audit-ready evidence that the requirement is being enforced. Which implementation best supports both the technical control and the compliance evidence need?

Options:

• A. Move the database to a private subnet with no public access

• B. Classify PHI fields, enforce DLP rules, and retain audit reports

• C. Encrypt database files and backups using managed keys

• D. Publish a PHI handling policy and require annual attestation

Best answer: B

Explanation: A governance action supports compliance evidence when it connects policy requirements to measurable enforcement. For PHI, classifying sensitive fields identifies what must be protected, DLP rules help prevent or alert on improper disclosure, and retained audit reports show that the control operated over time. This gives the organization both a practical safeguard and evidence for reviews or audits. Encryption, policy documents, and network restrictions can be important, but each addresses only part of the requirement unless it also demonstrates data-specific enforcement and retained evidence.

• Encryption only protects stored data but does not show PHI-specific handling, disclosure prevention, or user activity evidence.
• Policy attestation documents expectations but does not technically enforce database handling rules.
• Private subnet reduces exposure but does not classify PHI or provide evidence of DLP enforcement.

Continue with full practice

Use the CompTIA DataSys+ DS0-002 Practice Test page for the full IT Mastery practice bank, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.

Try CompTIA DataSys+ DS0-002 on Web View CompTIA DataSys+ DS0-002 Practice Test

Focused topic pages

• Database Fundamentals
• Database Deployment
• Database Management and Maintenance
• Data and Database Security
• Business Continuity
• Data Integration

Free review resource

Use the full IT Mastery practice page above for the latest review links and practice page.