DS0-002 practice questions
CompTIA · DS0-002 · 300 questions
Original practice questions for the CompTIA DataSys+ V2 (DS0-002) exam, covering database fundamentals, database deployment, database management and maintenance, data and database security, business continuity, and data integration, written to the official DS0-002 exam objectives with vendor-neutral, standard SQL.
This course contains the use of artificial intelligence.
About the DS0-002 exam
- Time allowed
- 1 hour 30 minutes
- Questions
- Maximum of 90
- Passing score
- 700 (scale 100-900)
- Format
- Multiple-choice and performance-based
Schedule this exam The certification this earns
Exam details published by the vendor, checked 9 October 2026. Vendors change fees and formats without notice — confirm on the vendor's own page before you book.
Practice Quizzes
Test your knowledge with standard 20-question practice sets.
Quiz 1
Quiz 2
Quiz 3
Quiz 4
Quiz 5
Quiz 6
Quiz 7
Quiz 8
Quiz 9
Quiz 10
Quiz 11
Quiz 12
Quiz 13
Quiz 14
Quiz 15
Browse by Domain
Study specific topics at your own pace.
Database Fundamentals · 57 questions
- A startup's checkout must insert an order, its line items and a payment row as one unit, and the database itself must reject any payment that references an order that does not exist. Which database type best fits these requirements?
- An online retailer's catalog holds items whose attributes differ widely: shoes have sizes and widths, laptops have CPU and memory specs, and some items carry nested lists of variants. Developers want to store each item as one nested JSON record and still query on fields inside it. Which NoSQL type is the most natural fit?
- A web tier stores user session data and always retrieves it by session ID; it never searches by any other attribute, and each lookup must complete in well under a millisecond. Which database type best fits this access pattern?
- An analytics team runs queries that scan billions of rows of sales history, but each query reads only three or four of the table's 120 columns to compute totals. Which storage model reduces the disk I/O for this workload the most?
- A fraud team must find rings of accounts connected through shared devices, shared addresses and shared payment cards, often four or five hops apart. Relational queries with many self-joins are timing out. Which database type is designed for this kind of query?
- A support portal converts every knowledge-base article into an embedding and must return the articles whose meaning is closest to a user's question, even when they share no keywords. Which database type is built for this retrieval?
- An IoT platform ingests a temperature reading from 50,000 sensors every second. Queries ask for averages per five-minute window over the last day, and readings older than 90 days are downsampled to hourly values. Which database type best fits this workload?
- An engineering application models assemblies as in-memory objects that use class inheritance and references between parts. The team wants to persist these objects directly, with the class hierarchy intact, instead of translating them into tables through a mapping layer. Which database model does this describe?
- A payroll table defines fixed columns such as employeeid INTEGER, paydate DATE and grosspay DECIMAL(10,2), and every row must conform to that definition. How is this data classified?
- A legal department wants to catalog an archive of scanned contract images, recorded customer calls and site photographs. None of the files follows a predefined data model. How should a DBA classify this content?
- A vendor sends a daily JSON feed. Each record carries self-describing field names, but some records include optional fields and nested arrays that others omit. How is this data classified?
- A social media app must scale writes horizontally across several regions to absorb viral traffic spikes. A post's like count can be briefly out of date between regions without harming the business, and the post format changes often. Which choice best fits these requirements?
- User profiles are stored as JSON. The application must fetch a profile by user ID, but it must also find profiles by email address and list every user in a given city. A developer proposes a key-value store. Why is a document database the better fit?
- A monitoring team loaded server CPU and memory metrics into a graph database, modeling each reading as a node linked to its host. Queries that average CPU per host in hourly buckets across the last month are now slow. Which change best matches the workload to a database type?
- A data lake receives three feeds: CSV exports from an ERP system with a fixed column layout, application logs written as JSON lines with varying fields, and product image files. How should the three feeds be classified, in that order?
- A developer must create a new shipments table and lengthen the customers table's email column from 100 to 255 characters. Which SQL subtype covers both statements?
- Prices in the products table must rise by 10 percent, but only for products in the 'Garden' category. Which statement makes this change correctly?
- A funds-transfer routine debits one account and then credits another. Yesterday the server failed between the two UPDATE statements, leaving money debited but never credited. How should the routine be changed?
- A DBA is running a long data-correction transaction in several steps. After step 3 succeeds, step 4 might need to be undone if its row counts look wrong, but the work from steps 1 through 3 must be kept. Which approach does this within the same transaction?
- A report must list every customer with the number of orders each has placed, including customers who have never ordered, who should appear with a count of 0. Which FROM clause supports this report?
- A query joining a 2,000-row customers table to a 50,000-row orders table returns 100,000,000 rows. The query is: SELECT c.name, o.total FROM customers c, orders o WHERE o.total > 0; What is the most likely cause?
- The employees table holds employeeid, name and managerid, where managerid refers to another row's employeeid in the same table. Which query lists each employee's name beside the manager's name?
- A sales report must show every salesperson's row with region and revenue, plus a column giving that person's rank within their own region by revenue, highest first. Which expression produces the rank column?
- A monthlysales table holds one row per month with salesmonth and amount. The report must show each month's amount next to a running total from the first month through that month. Which expression produces the running total?
- The products table has productid, category and price. Which query returns only the products priced above the average price of their own category?
- A query must return customers who have never placed an order. The orders.customerid column allows NULLs, and some legacy order rows have a NULL customerid. Which WHERE clause on the customers table returns the correct customers?
- An analyst needs every region whose total sales exceed 500000. The query SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 500000 GROUP BY region; fails. Which corrected query returns the intended result?
- An application's account features are register, view profile, change email address and close account, where closing removes the user's row. Which SQL statements map to these four actions, in that order?
- A developer's script opens a cursor over one million rows in the orders table and, for each row, runs an UPDATE that sets status = 'ARCHIVED' when orderdate is before 2020-01-01. The job takes hours. What should the DBA recommend?
- Marketing wants one mailing list combining the newslettersubscribers and webinarattendees tables, each with an email column. A person who appears in both tables must receive only one email. Which statement builds the list?
- Compliance requires that every change to the salary column in the employees table be recorded in a salaryaudit table with the old value, new value and time, no matter which application or user makes the change. Which approach meets this requirement?
- After a row-level trigger was added to a busy orderitems table, inserts slowed sharply, and on some days the trigger's own UPDATE against orderitems fires the trigger again until the engine aborts the statement. What is the most likely explanation?
- Dozens of client applications send the same multi-step order-creation SQL as ad hoc text, some by concatenating user input into the statements. The DBA wants one reusable implementation, lower injection risk, and the ability to grant execute rights without table rights. Which solution fits?
- Analysts repeatedly write the same expression to convert a price and a currency code into US dollars, and they need to use it inside the SELECT lists and WHERE clauses of their own queries. Which object should the DBA create?
- A routine must accept a start date and an end date, return a multi-row sales summary for that range, and insert a row into a reportlog table every time it runs. Which database object should implement this routine?
- A nightly data-cleanup script currently runs from a DBA's workstation and fails whenever that laptop is shut down or the DBA is logged out. The cleanup must run every night unattended. What is the best change?
- A web form checks in the browser that the email field is well formed and the quantity is a positive number, which cuts round trips to the database. A developer proposes removing the server-side validation because the browser already checks. What should the DBA advise?
- A Windows-based DBA team must automate the same health checks across 60 SQL Server instances, reading Windows event logs and service states as well as running queries on each instance. Which scripting language is the most natural fit?
- A script must call a vendor's REST API, reshape the nested JSON response into rows, and load those rows into a database table on a schedule. Which language and approach is the best fit?
- On a Linux database host, nightly dump files must be compressed, files older than 14 days must be deleted, and the job must run from cron. Which scripting option is the most direct fit?
- A legacy job parses fixed-width mainframe text reports using dozens of complex regular expressions before loading the results into a database. Which scripting language is traditionally associated with this kind of regex-heavy text processing?
- An administrator must apply the same schema change to 40 database servers overnight, unattended, using identical steps on each and keeping a log of each server's result. Which approach best meets this need?
- A developer writing complex SQL wants syntax highlighting, code completion, result grids and a graphical view of execution plans while working interactively. Which environment provides these features together?
- A maintenance script runs on an application server and connects to the database with a username and password stored in plain text at the top of the script file. Several operators can read that file. What should the DBA do?
- A reporting script on a laptop pulls all 40 million rows of the sales table across the network and then sums revenue by region in memory. It is slow and saturates the network link. What is the best fix?
- A DBA does two kinds of work: one-off exploratory queries while investigating a slow report, and a nightly production export that must run unattended. Which pairing of environments fits best?
- A Java application using Hibernate lists 200 orders with their line items. The SQL log shows one query for the orders followed by 200 separate queries, one per order, to load the items. What is the cause, and what is the usual remedy?
- A .NET application using Entity Framework calls ToList() on the Customers set and then filters the resulting list with a LINQ Where clause for customers in one country. Database monitoring shows the full table being read on every request. What should be changed?
- A Python service built with SQLAlchemy has slow endpoints, and the DBA needs to see the exact SQL statements the ORM sends to the database before tuning anything. What is the most direct first step?
- Analytics engineers built dbt models materialized as tables, so every scheduled run rebuilds each large fact table from scratch and consumes heavy warehouse compute. New rows arrive daily, and older rows rarely change. Which change reduces the load on the warehouse?
- A Java development team is adopting Ebean for a new order-management service, and a junior developer asks what the framework will actually do between the application's classes and the relational database. Which description of an object-relational mapper (ORM) is accurate?
- A new application release went live yesterday, and CPU on the database server has roughly doubled during normal traffic. The release changed several entity mappings and repository methods in the application's ORM layer, but no database settings or hardware changed. What should the DBA do first?
- A developer submits an ORM query for review. In testing it returns exactly the right rows for a customer summary page, but the logged SQL is a SELECT against a table with about 80 columns, while the page displays only three of them. Which review conclusion is correct?
- A team has changed how its ORM loads related order lines and wants to know, objectively, whether the change made database performance worse before rolling it out widely. Which approach measures the impact most reliably?
- A monthly report query generated by the application's ORM joins six tables and runs for minutes. Profiling shows it needs a rewrite the ORM's query API cannot express, while the rest of the application uses the ORM without problems. Which remediation is most appropriate?
- An architecture review board is deciding whether a new service should use an ORM such as Entity Framework or hand-written SQL for its data access layer. Which statement best summarizes the trade-off the board should weigh?
- During a sales event the database starts rejecting connections with a too-many-connections error. Investigation shows the application's ORM configuration opens a brand-new database connection for every web request and closes it afterward. Which change is the most appropriate programming-side fix?
Database Deployment · 51 questions
- A company plans to replace a fifteen-year-old database platform. Before selecting a product, the project lead wants a structured comparison of what the current system does today against what the business now requires, so missing capabilities can be listed. Which requirements-gathering activity is this?
- A production database is 2 TB today and has grown at a steady 4 percent per month for the past two years. The new server must run for three years without a storage expansion. Which sizing approach should the planner use?
- For a new system, an analyst estimates storage by multiplying the expected row count by average row size, arriving at 500 GB, and proposes provisioning exactly that amount. What should the DBA point out about this estimate?
- A team plans to host a new OLTP order database, which performs thousands of small random writes per second, on a few high-capacity 7,200 RPM spinning disks because they offer the lowest cost per terabyte. What is the main concern with this storage specification?
- Company policy requires that customer records remain in the company's own data center. Separately, the analytics team runs large quarterly models on anonymized aggregates and needs a burst of compute for a few days each quarter. Which hosting approach best fits both requirements?
- Requirements for a new system list 50 internal analysts running ad hoc reports during office hours and up to 20,000 concurrent web customers placing orders through an application. What planning conclusion follows from these numbers and user types?
- A DBA is drafting a service level agreement for an internal database service and needs indicators that both parties can measure objectively each month. Which pair of KPIs is most appropriate?
- A production database outage has run past the response time agreed in the SLA. The on-call engineer is working the problem but is unsure which manager, vendor contact or business owner must be informed, and at what point. Which SLA element should have answered this?
- The business owner of a hosted database service asks the provider for evidence that the agreed availability commitment was actually met during each month of the last quarter. Which SLA component provides this evidence?
- An installation of a database engine on a new Linux server fails partway through. The log shows that a required operating system library is missing and that the service account the engine runs under does not exist. Which deployment step should the team add before the next attempt?
- A team must build database instances for development, test and production, and past incidents were caused by small configuration differences between environments that were set up by hand. Which provisioning approach best prevents this?
- A DBA is planning a major version upgrade of a production database engine that supports an order-processing application. Which sequence follows sound upgrade practice?
- The initial load of 400 million rows into a new database is running far slower than planned. Every target table already has all of its secondary indexes built and its constraints enabled. Which approach is most appropriate for the load?
- A team needs to install a custom-compiled extension into its database engine, which requires changing operating system packages and kernel settings on the host. It wants to run this in a public cloud. Which cloud service model fits?
- A three-person development team with no dedicated DBA is launching a new application. It wants the cloud provider to handle engine patching, automated backups and high availability so it can focus on its own schema and queries. Which deployment option fits best?
- A sales department uses a hosted SaaS customer relationship management application, and the IT team wonders how its database responsibilities differ from those of a DBaaS deployment. Which statement accurately describes the SaaS case?
- An analytics service runs on cloud compute nodes that are automatically destroyed and recreated during scale-in and scale-out events. After each event, data the service had written to the nodes' local disks is gone. Which design change keeps autoscaling while preventing the loss?
- Before go-live, a team must confirm two things: that the database sustains the projected holiday peak of 5,000 transactions per second, and that a recent schema change did not break any of the existing finance reports. Which testing combination addresses both?
- A release of a billing application failed in production because the new database schema renamed a column that a nightly export job still referenced by its old name. Which testing practice should be added before future schema deployments?
- After migrating an order system to a new database, users notice orders that reference customer IDs missing from the customer table, and some product descriptions are cut off midway. Which validation approach would have caught both issues before cutover?
- Early in a project, an architect is modeling customers, orders and products along with their attributes and relationships, but has not yet chosen data types, indexes, partitions or storage locations. At which level of schema design is the architect working?
- With the logical model approved, a DBA is now deciding how to partition the largest table by month, which file groups or tablespaces hold each partition, and on which storage volumes the indexes will live. Which design activity is this?
- A newly hired developer cannot tell what the codes 1 through 6 in an order table's status column mean, or whether the weight column stores kilograms or pounds. Which design documentation should the team maintain to answer these questions?
- An ERD for a training system shows crow's-foot symbols at both ends of the line between Student and Course: a student can enroll in many courses, and a course can have many students. How should this relationship be implemented in a relational database?
- External auditors want to understand, at a glance, how the tables in a payment system relate to one another, including which tables reference which and with what cardinality. Which design document is best suited to communicate this?
- A retailer runs a point-of-sale system that records thousands of short sales transactions per minute, and a separate warehouse where analysts aggregate years of sales by region and product. Which description matches the two systems' designs?
- Executives run large ad hoc sales reports against the production order database during business hours, and online checkout slows noticeably whenever they do. Which change best addresses the conflict?
- A BI team is designing a reporting model with one central sales fact table holding measures such as quantity and revenue, surrounded by date, store, product and customer tables. Each surrounding table is a single denormalized table joined directly to the fact table. Which design is this?
- Each of a company's four DBAs performs the weekly index maintenance differently: some rebuild everything, some reorganize selectively, and only some update statistics or check the results. Which action best makes this task consistent and repeatable?
- An internal security policy requires that every request for access to production databases be approved by the data owner and by the security team, with the approval recorded before any account is created. How should the DBA team operationalize this requirement?
- A qualified assessor evaluating the company's compliance with the Payment Card Industry Data Security Standard asks the DBA team how cardholder data in the database is protected, accessed and retained, and wants documented evidence. What best satisfies this request?
- An e-commerce application wraps two statements in one transaction: insert the order row, then decrement the product's inventory. The inventory update fails on a constraint violation, and the business requires that the order row must then not remain either. Which ACID property guarantees this behavior?
- During testing, a report transaction reads account balances that another transaction has changed but not yet committed. The other transaction then rolls back, so the report has shown values that never officially existed. Which ACID property is not being adequately enforced?
- A database server loses power without warning seconds after an application received a successful commit for a funds transfer. When the server restarts, the transfer is still present and complete. Which ACID property does this demonstrate, and what mechanism typically provides it?
- A user updates their profile on a globally distributed NoSQL store. For a few seconds, users reading from another region still see the old profile, and then everyone sees the new version. The system accepts writes even if a region is temporarily unreachable. Which consistency model does this behavior illustrate?
- An architect is choosing data stores for two new services: a banking ledger that records account debits and credits, and a social media activity feed that must stay available worldwide even during network partitions. Which pairing of consistency models is most appropriate?
- A new team is taking over a fifteen-year-old database that has no current documentation. The original developers have left, and the team must make schema changes next quarter. What should the team do first?
- An application performs mostly reads, and its single primary database server is saturated during peak hours. Three read replicas were built months ago but receive almost no traffic because every application connection points at the primary. Which change best uses the existing infrastructure?
- After a load balancer was placed in front of a primary database and two read-only replicas, the application intermittently fails with errors stating that the database is read-only. The balancer distributes all connections round-robin across the three servers. What is the correct fix?
- During a failover test, the standby database became the new primary within a minute, but the applications kept trying to reach the old server for over an hour until their configuration files were edited. Each application's connection string contained the old server's IP address. How should this be prevented?
- Clients receive a 'host not found' error when connecting to a newly built database server by its name, sdb-orders01. The database service is running and listening, and connections to the server's IP address on the database port succeed. What should the DBA check next?
- A company is designing a three-tier web application with browser clients, an application tier and a database tier. A developer proposes letting the browser-side code connect straight to the database to save a hop. Why does the client/server design keep the database separate behind the application tier?
- Public-facing web servers sit in a perimeter network (screened subnet) and must query the company's customer database. A network engineer asks where the database should live and what traffic should be permitted. Which design is most appropriate?
- An application server can ping the database server by host name, and the DBA confirms the database service is running and accepts local connections on the server itself. Connections from the application server to TCP port 5432, however, hang and then time out without any error message from the database. What is the most likely cause?
- After a weekend reboot, a database server came back with a different IP address from the DHCP pool. Several applications that use the server's IP address in their connection strings could no longer connect until their configuration was edited by hand. What should the DBA do to prevent a repeat?
- A company runs a production database with a cloud provider. The database must stay available if one data center in the region fails, and data residency rules require that all copies remain within that same region. Which deployment meets both requirements?
- A security team is writing firewall rules for two newly installed database servers: a Microsoft SQL Server default instance and a PostgreSQL server. Neither installation was customized. Which inbound TCP ports should the request list for the default configuration of each?
- A DBA is documenting a hybrid environment for the network team. It includes a MySQL server and an Oracle Database server, both installed with default network settings. Which default TCP ports should the documentation list for the MySQL server and the Oracle listener?
- To reduce exposure, management proposes moving every production database service from its default port to a nonstandard port and treating that change as the main network protection for the databases. How should the DBA characterize this proposal?
- An application connects to a database through a network load balancer. After periods of inactivity longer than about ten minutes, the first query on a pooled connection fails with a connection-reset error, and a retry succeeds. Direct connections that bypass the load balancer never show the problem. What is the most likely cause?
- A company adds a database replica in a distant cloud region and configures it for synchronous replication so that no committed transaction can be lost. Immediately afterward, write transactions on the primary take noticeably longer to commit, although read queries are unaffected. What best explains the slowdown?
Database Management and Maintenance · 54 questions
- A database data file grows by roughly 40 GB per month, and the volume holding it has 50 GB of free space left. Today the team learns about full disks only when applications begin to fail. What should the DBA configure so the team is warned with time to act?
- A nightly ETL job that loads the reporting database stopped working three days ago. The scheduler sends an email only when the job succeeds, and nobody noticed that the success emails had stopped. Which monitoring change addresses the root problem?
- During an audit, a DBA discovers that no database backups have run for a week. After a patching session, the scheduler service that triggers backups was left disabled, so no backup job started and no errors were logged. Which alerting approach would have caught this?
- A DBA must pick a weekly maintenance window and wants to understand how the database is normally used on weekdays compared with weekends. Which approach gives the most useful information?
- Users report that an order-entry database seems slower than usual this week. The DBA collects current metrics but has no record of what CPU, wait times, or query response times looked like when users considered performance normal. Which practice would have made this complaint quick to evaluate?
- Over several months, administrators made manual changes on individual database servers, and the servers no longer behave consistently. Memory caps, maximum connections and logging settings now differ from the approved build standard. What should the DBA do?
- A capacity planning report must state how much work the database completes each second during the evening peak so that it can be compared with the expected growth in orders. Which metric should the DBA report?
- The database service on a production server restarted unexpectedly three times overnight. Users noticed brief outages, but no planned work was scheduled. What should the DBA do first to find the cause?
- A database server responds slowly. Monitoring shows moderate CPU use, but the operating system reports a high rate of paging to the page file or swap space, and available physical memory is near zero. What is the most likely problem?
- CPU utilization on a database server has stayed near 100 percent since a new sales report was deployed on Monday. Before Monday, CPU averaged 35 percent. What should the DBA do first?
- After a database doubled in size over six months, the DBA notices the buffer cache hit ratio has fallen steadily and physical disk reads have risen, though the query mix and the server's RAM are unchanged. What is the most likely explanation?
- Monitoring on a database server shows CPU at about 15 percent, ample free memory, and most query wait time attributed to reading and writing data files. Disk queue length is high and average read latency has climbed sharply during business hours. What is the bottleneck?
- A large overnight batch loads millions of rows in one transaction. Partway through, the volume holding the transaction log runs out of space and the batch fails. Monitoring covered only the data file volume. Which change best prevents a recurrence?
- A cloud-hosted database is billed on consumption, including I/O requests, storage and data transferred out. After a partner integration went live, the monthly bill rose sharply although the user count stayed flat. Which practice will show what is driving the increase?
- An order application intermittently receives errors stating that its transaction was chosen as a deadlock victim and rolled back. The errors happen a few times a day under normal load, and retrying usually succeeds. What should the DBA do to address the cause?
- A reporting session has been waiting for four minutes on a row that another session updated but has not yet committed. No error has been raised, and the waiting session resumes as soon as the other session commits. A colleague calls this a deadlock. Which statement is accurate?
- At peak times an application starts receiving errors that new database sessions cannot be opened because the server has reached its connection limit. Off-peak, everything works. Several application servers each run their own connection pool. What should the DBA monitor and adjust?
- Overnight monitoring shows several thousand failed login attempts against a privileged database account, all from a single external IP address within two hours. No successful logins from that address are recorded. How should the DBA treat this event?
- A database vendor releases a security patch that fixes a remotely exploitable vulnerability in the engine version running in production. The organization has development, test and production environments and a monthly maintenance window. What is the best approach?
- A production database is four cumulative patches behind the vendor's current release. The team has skipped patches for a year because an earlier patch once broke an application. Which change to the team's practice is most appropriate?
- A storage controller on a database server logged hardware errors overnight before being replaced. The database is online and users report no problems, but the DBA is concerned that some data pages may have been written incorrectly. What should the DBA do?
- A team keeps database backups for seven days and runs a full database integrity check once a month. An auditor says this schedule could leave the team unable to recover a clean copy. What is the auditor's concern?
- During the weekly review of database logs, a DBA notices dozens of automatic file growth events on the same data file over the past month, each occurring during business hours. No outage has occurred yet. What is the best response?
- After a large purge removed about half the rows from an orders table, range queries on its indexes became slower. Index statistics show high fragmentation and low page density on several indexes. What should the DBA do?
- A heavily written table has eleven indexes, and insert latency has grown as indexes were added over the years. Index usage statistics collected over a full business cycle show that four indexes are never used for reads and one duplicates another's key columns. What should the DBA do?
- A query that runs thousands of times a day filters a 40-million-row customer table with WHERE emailaddress = ?. The execution plan shows a full table scan, and no index exists on emailaddress. What is the most effective fix?
- On a table with an index on orderdate, a column of type TIMESTAMP, this query performs a full scan: SELECT orderid FROM orders WHERE EXTRACT(YEAR FROM orderdate) = 2025; Which rewritten WHERE clause returns every 2025 order and allows the index to be used?
- Every month-end, transaction volume on a finance database roughly triples for three days, and response times degrade badly during that period. For the rest of the month, performance is fine. What should the DBA do?
- A transaction history table holds ten years of data, and policy requires keeping only the most recent seven years. The monthly DELETE that removes expired rows runs for hours, generates heavy logging and blocks other work. What design change best addresses this?
- A customer table includes several large, rarely used columns, such as a profile photo and free-text notes, alongside small columns that are read constantly. The DBA moves the large columns into a separate table that shares the customer primary key. Which technique is this?
- Developers have been running schema changes directly against the production database at midday, causing lock waits and two brief outages this quarter. Management asks the DBA for a process fix. What should the DBA implement?
- A development team deploys application code through an automated CI/CD pipeline, but database schema changes are still applied by hand, and environments regularly drift apart. The team wants schema changes to move through the same pipeline. What should the DBA set up?
- During an outage, an on-call DBA added an index to a production table to relieve severe blocking. Performance recovered, but no change ticket was raised and the index exists only in production. What should happen next?
- The test database was copied from production a year ago. Recent releases passed testing but failed in production because of data patterns that did not exist in the old copy. The production data includes customer PII. What should the DBA do?
- Two DBAs edited the same stored procedure directly in production on the same day. The second save silently overwrote the first DBA's fix, and nobody could say which version had been running the previous week. What practice prevents this?
- A maintenance window on a production database will include four tasks: a full backup, rebuilding several fragmented indexes, updating optimizer statistics, and purging rows older than the retention period. Which task should run first?
- A DBA must correct a batch of customer rows where the city was entered as 'Pittsburg' instead of 'Pittsburgh' for addresses in Pennsylvania. The production table has millions of rows. Which approach is safest?
- A DBA is defining a customer table: CREATE TABLE customer (customerid INT PRIMARY KEY, email ...); Every customer must have an email address, and no two customers may share one. Which column definition enforces both rules in the table itself?
- A populated orders table with millions of rows needs a new status column. Business rules say that existing orders should be treated as 'NEW' and that the column must never contain NULL. Which statement adds the column correctly in one step?
- Analysts need to query employee names, departments and hire dates from an employee table that also contains salary. They must never see salary, the data they query must always be current, and the DBA does not want to duplicate the data in another table. What should the DBA do?
- The same complex query, joining orders, customers, products and regions with several filter rules, has been copied into fifteen different reports. When a business rule changed, five reports were missed and now show different totals. What should the DBA do?
- Queries on a person table often filter on lastname and firstname together, and many others filter on lastname alone; none filter on firstname alone. Names are not unique. The DBA wants a single index that supports both query patterns. Which statement should the DBA use?
- An Orders table is currently a heap with no indexes. Most queries retrieve every column for a range of order dates, and a smaller set of lookups finds orders by CustomerID. Which indexing design best supports the primary access path?
- After a nightly bulk load tripled the size of a Sales table, several reports that used to finish in seconds now take minutes. Execution plans show estimated row counts far lower than the actual rows returned. What should the DBA do first?
- A sales dashboard runs under the READ UNCOMMITTED isolation level and occasionally displays order totals that include rows from transactions that are later rolled back. Which change prevents this with the least additional blocking?
- A month-end report runs inside one transaction: it counts the open invoices, then sums their amounts. Under ANSI SQL isolation levels, new invoices inserted between the two steps make the count and sum disagree. Which isolation level prevents this, and at what cost?
- An order-entry database also serves heavy reporting queries. Under the current isolation level, long reports take shared locks that block order updates. Reports must still see only committed data. Which isolation approach best resolves the conflict?
- An audit finds rows in the Orders table whose CustomerID values do not exist in the Customers table, where CustomerID is the primary key. Which statement prevents new orphaned orders from being created?
- A Customers table is referenced by both an Addresses table and an Invoices table. Deleting a customer should automatically remove that customer's addresses, but a customer who has invoices must not be deletable. Which foreign key configuration meets both requirements?
- An Orders table stores CustomerName and CustomerAddress on every order row. When a customer moves, the update often misses some rows and reports show two different addresses for one customer. Which design change removes the cause of this anomaly?
- An Employees table has the columns EmployeeID (primary key), EmployeeName, DepartmentID and DepartmentName. DepartmentName is determined by DepartmentID. Every value is atomic and the key is a single column. Which normal form does the table violate?
- A KPI dashboard query joins eight normalized tables and takes too long to refresh. The underlying data is loaded once per night and the dashboard is read hundreds of times a day. Which approach best improves read performance for this workload?
- An OrderLines table stores Quantity, UnitPrice and LineTotal. Several applications write to it, and some forget to recalculate LineTotal when Quantity changes. The DBA wants LineTotal to always equal Quantity multiplied by UnitPrice without relying on application code. Which solution meets this requirement?
- A Shipments table has a virtual computed column, TotalWeight, that is calculated from three other columns on every read. Most queries filter on TotalWeight, and plans show full scans that evaluate the expression for every row. Where supported by the DBMS, which change best improves these queries?
Data and Database Security · 57 questions
- Application servers connect to a database server across a shared network segment, and a packet capture shows query text and result sets in clear text. Which control addresses this exposure?
- A company stores sensitive customer fields in a managed cloud database. The security policy says neither the cloud provider nor the company's own DBAs may ever see these fields in plaintext. Which approach meets this requirement?
- A backup drive containing copies of a production database's data files and backup files is stolen from a courier vehicle. The data was not encrypted. Which control would have prevented the thief from reading the data?
- A company generates an encryption key in its own on-premises hardware security module and imports it into its cloud provider's key management service, which then uses it to encrypt the company's managed databases. Which key management model is this?
- A regulator requires that a bank's cloud-hosted database be encrypted with keys that remain exclusively in the bank's own hardware security module, so the cloud provider can never possess the key material. Which model satisfies this requirement?
- During a review, a DBA discovers that the master encryption key protecting a database's encrypted columns is stored in a file on the same server as the database. What is the best remediation?
- A healthcare analytics firm runs database workloads on shared cloud hardware. Its data is already encrypted at rest and in transit, but the firm must also protect data while it is being processed in memory from the host operator. Which technology addresses this requirement?
- Developers need a copy of the payments database with realistic-looking data for testing. Card numbers must keep their length and format so validation code still works, but developers must not see real card numbers. Which technique best fits this need?
- A hospital wants to share a dataset with outside university researchers. The agreement requires that no individual can ever be re-identified, even by the hospital. Which data protection technique meets this requirement?
- A public health report summarizes cases by town and age group. Some cells show counts of one or two people, which could reveal who has the condition in small towns. Which technique should be applied to those cells before publication?
- A company is retiring database servers whose disks held regulated customer data. The disks will not be reused. What is the most appropriate way to dispose of the data?
- A quarterly security audit finds that several database logins still belong to employees who left the company months ago. Some might be used by scheduled jobs. What should the DBA do?
- An auditor asks the DBA to demonstrate who connected to the production database, from which host or IP address, and when, including failed attempts. What should the DBA configure?
- Monitoring alerts show a database account exported large volumes of customer records overnight, and management suspects data theft that may lead to legal action. What should the DBA do first?
- During a code audit, a reviewer finds a login function that builds a query by concatenating the username and password typed by the user directly into a SQL string. Which remediation should the reviewer require?
- A scan of a source code repository finds application configuration files containing database connection strings with plaintext passwords, committed over several months. What is the best remediation?
- An investigation finds that employees regularly export customer lists from the CRM database and email them to personal accounts. Management wants sensitive data detected and blocked when it leaves through email and uploads. Which control best meets this goal?
- A company's customer database keeps every record indefinitely, including accounts closed many years ago, because staff think the data might be useful someday. What should the DBA recommend to governance stakeholders?
- A scheduled job purges customer records older than the retention period every month. The legal department has just placed a litigation hold on records for several customers involved in a lawsuit, some of which are due for purging this week. What should the DBA do?
- A European retailer stores customer data in a data center located in another country. Its legal team explains that the data remains subject to the privacy laws of the customers' home jurisdiction regardless of where it is stored. Which concept does this describe?
- A national regulation requires that citizens' records be stored only within the country. The primary database meets this, but an audit finds the cloud provider's geo-replicated read replica and automated backups are in a region abroad. What should the DBA do?
- A compliance analyst is preparing training for new DBAs on cloud data placement. Which statement correctly distinguishes data residency from data sovereignty?
- A customer table stores each person's full name, home address and national identification number. The team must classify these columns and decide on protection. Which classification and handling is most appropriate?
- A clinic's database stores patient names linked to diagnoses, treatment notes and insurance claim details. A new analyst requests full read access to build a scheduling dashboard. How should the DBA treat this data?
- An e-commerce company's order database stores customers' full primary account numbers (PANs) from payment cards so returning customers can check out faster. Which compliance obligation applies to this storage?
- A merchant currently stores full card numbers in several databases to support refunds and repeat purchases. The security team wants to reduce how many systems fall within PCI DSS scope while keeping those features. Which approach best achieves this?
- A customer in the European Union submits a request under the GDPR for the company to erase all personal data it holds about them. The data is spread across a CRM database, a data warehouse and nightly backups. What should the DBA do?
- A company's online newsletter signup form collects email address, date of birth, home address, phone number and employer, although the stated purpose is only to send a monthly newsletter. Which GDPR principle does this design violate?
- A new data platform is being designed. Stakeholders ask who should decide how long customer data is retained and which business roles may view it, and who should configure the database to enforce those decisions. Which split of responsibilities reflects sound data governance?
- Fifty financial analysts need identical read access to the same reporting tables, and an analyst's access must change automatically when they move to another job. What is the best way to grant this access?
- A review shows that a web application connects to its database with an account that has full administrative rights, although the application only reads and writes rows in six tables. What should the DBA do?
- A company requires that access to records depend on the user's department, the record's data classification and whether the request occurs during business hours. Which access control model best fits this requirement?
- An organization's RBAC design has grown to hundreds of roles such as Sales-EU-Confidential-ReadOnly, created for every combination of region, department and data sensitivity. Administration is becoming unmanageable. Which change best addresses this problem?
- Database administrators connect remotely to production database servers using only a username and password. After a phishing campaign targeted IT staff, the security team wants to make stolen passwords insufficient for privileged access. Which control should be required?
- Business users must remember separate passwords for the reporting portal, the analytics tool and two database web consoles, and the help desk is overwhelmed with reset requests. What solution best addresses this problem?
- Users on a Windows domain connect to a database server with integrated authentication. The security team wants clients to authenticate with time-limited tickets from the domain's key distribution center, with mutual authentication of the server and no password sent to it. Which protocol should be in use?
- After the database service was changed to run under a new domain service account, client connections that previously used Kerberos now fall back to NTLM, and double-hop delegation to a linked server fails. Which action most likely resolves this?
- A partner company's analysts need access to a shared reporting database. The partner manages its own user directory, and the DBA wants partner users to sign in with their existing partner credentials rather than receiving accounts in the company's directory. Which approach meets this requirement?
- A reporting application must call a company data API to pull a sales manager's own pipeline figures. Security requires that the application act on the manager's behalf with limited, revocable access and never see or store the manager's password. The application does not need to establish the manager's identity for its own sign-in. Which approach meets these requirements?
- A web application already uses an external provider's OAuth 2.0 flow to obtain API access tokens. The developers now need the application to verify who actually signed in and read basic profile claims such as name and email from a signed token issued by that same provider. Which technology adds this capability?
- After a DBA enabled TLS on a database server with a self-signed certificate whose subject is "localhost", every client connecting to db01.corp.example.com reports an untrusted certificate and a name mismatch. Security requires encrypted connections with server identity verification. What should the DBA do?
- At midnight every application connection to a production database began failing with TLS handshake errors. The DBA finds that the server certificate reached its expiry date, issues a replacement, and restores service. Which practice would best prevent a repeat of this outage?
- Three internal applications all connect to the same database using one shared login named appsvc. After rows were deleted from a payments table, the audit log shows only that appsvc ran the DELETE, so the team cannot tell which application did it. Which change restores accountability?
- An auditor finds that a branch office's database server sits in an unlocked supply closet. Anyone in the building can walk in, unplug the server, attach a USB device, or carry the chassis away. Which control most directly addresses the risk the auditor described?
- An external scan shows a production database server's listener port is reachable from any internet address. The database serves only two application servers in the same data center. Which change most directly reduces this exposure?
- A company is placing a three-tier application into a network with an internet-facing perimeter network (DMZ) and an internal network. The tiers are public web servers, application servers, and a database holding customer records. Which placement follows recommended practice for the database?
- During a hardening review, a DBA finds a new database server running with several default features enabled: a web-based management endpoint, a legacy remote-administration protocol, and an extra network listener. None of them are used by the applications or administrators. What should the DBA do?
- A company's database access policy states that any connection originating from the internal corporate network is trusted and may reach any database, while external connections must use a VPN. A security review recommends adopting a Zero Trust architecture. Which change reflects that model?
- A DBA team manages forty database servers across on-premises and cloud environments. Management wants a repeatable way to identify known weaknesses such as missing security patches, insecure default settings, and outdated components on every server. Which practice meets this requirement?
- A vulnerability scan flags a critical flaw in a database server's network listener. The vendor has published an advisory but no patch yet, and the database must stay in service for a customer-facing application. What is the most appropriate response?
- An inventory finds six test databases restored from production copies over the past two years, still online with real customer data and old login accounts. No project uses them and their owners have confirmed they are no longer needed. Which action best reduces the attack surface?
- A penetration tester types ' OR '1'='1 into the password field of an internal web application and is logged in as the first user in the table. The application builds its login query by concatenating the form values into a SQL string. What is this attack, and how should developers fix it?
- After a SQL injection finding, a team debates three controls for a customer search page: a maximum input length on the search box, a web application firewall in front of the site, and parameterized queries in the data-access code. Which statement describes how these controls should be used?
- A fraud-detection model starts approving obviously fraudulent transactions. Investigators find that an attacker with write access to the labeled training feed inserted thousands of fraudulent records marked as legitimate before the last retraining. What is this attack, and what response best addresses it?
- A ransomware infection on a database server encrypted the data files and also every backup file stored on a network share that the server's service account could write to. The team is redesigning its protections. Which preparation would most limit the damage from a repeat attack?
- A DBA receives an urgent call from someone who says they are a regional director traveling to a client meeting. The caller asks the DBA to reset the password on a production reporting login immediately and read the new password over the phone. What should the DBA do?
- A healthcare company is securing a database that stores patient records. The security architect insists on defense in depth rather than relying on one strong control. Which design best reflects that principle?
Business Continuity · 42 questions
- At a small company, database backups are run by hand by whichever DBA remembers at the end of the day. Twice last quarter no backup ran at all, and nobody noticed for several days. Which change best addresses the problem?
- A DBA is documenting what each backup type can do on its own. The team takes a full backup every Sunday night. Which statement accurately describes a full backup?
- A database receives a full backup every Sunday at 23:00 and an incremental backup every other night at 23:00. The storage fails early Thursday morning before any Thursday backup runs. Which restore sequence recovers the most data from these backups?
- A database receives a full backup every Sunday at 23:00 and a differential backup every other night at 23:00. The storage fails early Thursday morning before any Thursday backup runs. Which restore sequence recovers the most data from these backups?
- A database keeps its weekly full backup, but the nightly window for the daily backup job is shrinking as data volume grows. The team accepts a longer restore process if each nightly job finishes as quickly as possible. Which daily backup type fits, and what trade-off does it bring?
- A nightly backup file is copied from the database server to an off-site storage location over a WAN link that has dropped packets in the past. The DBA needs confidence that the off-site copy is bit-for-bit identical to the original before relying on it. What should the DBA do?
- A company has taken nightly database backups for three years, and every job has reported success. During an audit, the DBA admits that no backup has ever been restored. Which practice should the DBA put in place?
- At 10:42 a user accidentally deleted several thousand order rows in a production database that uses the full recovery approach with nightly full backups and transaction log backups every 15 minutes. The business wants the data as it was just before the mistake. What should the DBA do?
- A DBA must move one schema, including its tables, indexes, views, and data, from a production server to a newer major version of the same database product on a different operating system. The copy must be portable and loadable with standard client tools. Which method fits best?
- A DBA manages an 8 TB transactional database with a recovery time objective of four hours. A colleague proposes replacing the nightly physical backups with nightly logical dumps because the dump files are portable. How should the DBA evaluate the proposal?
- A DBA notices that a nightly backup job has reported success for the past week, yet each backup file written in that period is zero bytes. The team receives alerts only when a job exits with a failure code. What should the DBA change in the backup reporting and alerting?
- A company stores all of its database backups on the same storage array that hosts the production database files, in the same data center. Restores are fast, but an auditor flags a risk. Which change addresses the auditor's concern while keeping fast routine restores?
- Regulations require a bank to retain closed-account records for seven years. The records are queried only a few times a year, yet they still sit in production tables and inflate every nightly backup. Which approach best meets the retention requirement while reducing operational load?
- Before a major database engine upgrade, a DBA takes a storage snapshot of the database volumes so the team can roll back quickly if the upgrade fails. Which statement about this snapshot is accurate?
- A DBA scripted a nightly storage snapshot of a busy database server's data and log volumes. When the team reverted to one snapshot during a test, the database would not start cleanly and reported inconsistencies. Writes were active when the snapshot ran. What should the DBA change?
- An auditor asks the DBA team for the document that describes the database system's security requirements, its authorization boundary, and the security controls that are implemented or planned for it. Which disaster recovery planning document is the auditor requesting?
- A government contractor must be able to keep its essential business functions running during a disruption that could last weeks, including relocating staff to an alternate facility and defining succession for key roles. Which document should describe these arrangements?
- During a disaster recovery exercise, the recovery team must rebuild a database environment from scratch. They need documentation of its architecture, component dependencies, interfaces, and configuration settings. Which document provides this?
- A disaster recovery plan lists twelve systems but gives no recovery order. During a test, the team restored the application servers first, and they failed because the authentication and DNS services and the database were not yet available. What should the DBA add to the plan?
- A secondary database server is kept up to date by regularly backing up the primary's transaction log, copying each log backup file to the secondary, and restoring it there on a schedule. The secondary can be brought online if the primary fails. Which technique is this?
- A primary database ships transaction log backups to a disaster recovery server every 15 minutes, and each file is copied and restored on the secondary shortly after it is taken. If the primary is suddenly destroyed, how much committed data could be lost?
- A company's database disaster recovery plan has not been exercised in two years, and several staff named in it have changed roles. Management wants a first test that checks whether the plan's steps, contacts, and assumptions are still valid without any risk to production systems. Which test fits?
- During a full disaster recovery test, the team discovers that the database recovery runbook refers to two servers that were decommissioned last year and omits a newer reporting replica. The team improvises and completes the test. What should happen next?
- Business owners state that the order-processing database can tolerate losing at most five minutes of committed transactions in any disaster. The DBA currently takes only nightly full backups. Which metric did the business define, and what must change?
- An SLA states that the customer portal's database must be back in service within two hours of any outage. The current plan restores from off-site backups, a process the last test timed at six hours. Which metric is the SLA defining, and what does the test result mean?
- A retailer's finance director says the checkout database may lose up to one hour of orders in a disaster, but the checkout service must be running again within fifteen minutes. How should the DBA record these objectives?
- A fire destroys the primary data center. The order database has an asynchronously replicated secondary in another region that is a few seconds behind. The applications connect through a DNS name that points to the primary. What should the DBA do to restore service?
- After a failover, production has run on the disaster recovery server for a week while the original primary was repaired. Management now wants to return operations to the original primary. What must the DBA ensure before failing back?
- A company has two systems to protect. A trading platform must resume within minutes of a site failure, whatever the cost. A historical reporting system can be offline for several days, and the budget for it is minimal. Which disaster recovery site approach fits each system?
- A customer-facing database must stay available during monthly operating system and database patching, which currently requires a two-hour outage window. Which configuration allows patching with only a brief interruption?
- A two-node database cluster was built for high availability. Both nodes connect to one shared storage array, and all of their network traffic passes through a single network switch. Which risk analysis finding is accurate?
- A company runs its order database in a single cloud region. Management wants the database to keep serving orders if one data center in that region loses power, and the standby must stay close enough for synchronous commits with low latency. Which design best meets these requirements?
- A company operates two data centers 10 km apart connected by dedicated low-latency fiber. The business requires that no committed transaction be lost if the primary database server fails, and it accepts slightly slower commits to achieve this. Which replication mode should the DBA configure?
- A retailer replicates its primary database asynchronously to a replica on another continent. Monitoring shows the replica running about 45 seconds behind the primary. The primary site then fails, and the team promotes the replica to keep the business running. What is the most likely consequence of this failover?
- A SaaS company serves customers in Europe, North America and Asia from one database region. Distant users report slow page loads caused by read queries, and the business also wants the data to survive the loss of the entire hosting region. Which approach addresses both needs?
- A DBA configures database mirroring between a principal server and a mirror server, then adds a third server that holds no copy of the database. The goal is for the mirror to take over automatically if the principal fails. What is the purpose of the third server?
- A developer runs a DELETE statement without a WHERE clause on the primary database, and within seconds the rows are also gone from all three high-availability replicas. The manager asks why the replicas did not protect the data and how it can be recovered. Which response is correct?
- A database server loses power and restarts. When it comes back online, every transaction that was committed just before the outage is present, and every transaction that was still in progress has been rolled back. Which mechanism makes this recovery possible?
- A database uses a recovery setting that keeps every transaction log record until the log is backed up, so that point-in-time restore is possible. The only scheduled job is a nightly full backup. The log disk keeps growing and is now nearly full. Which action correctly resolves the problem?
- A design review covers two components: a two-node cluster in the main data center that fails over automatically when a server fails, and nightly backups copied to storage in a second region. The architect asks which component is high availability and which is disaster recovery. Which statement is correct?
- A DBA is choosing the RAID level for an eight-disk volume that will hold the data files of a busy OLTP database with heavy random writes. The volume must survive a disk failure, and write performance matters more than usable capacity. Which RAID level best fits?
- A two-node database cluster loses the network link between its nodes. Each node concludes the other has failed, promotes itself to primary and accepts application writes, leaving conflicting data on the two nodes. What is this condition called, and what design element prevents it?
Data Integration · 39 questions
- A new partner feed combines public product catalog data with customer names, email addresses and order histories. The feed is about to be loaded into a shared analytics area that many analysts can query. What should the DBA do first?
- A legacy data warehouse is fed by a dedicated integration server that pulls rows from several source systems, cleanses and conforms them in its own processing engine, aggregates them and then writes only the finished tables into the warehouse. Which data integration approach does this describe?
- A team is moving to a cloud data warehouse with elastic compute. Their new pipeline copies raw source data into staging tables in the warehouse first, then runs SQL models inside the warehouse to clean and reshape it. Which approach is this, and why does it suit the platform?
- A regulation states that national identification numbers must never be stored in the company's analytics platform, not even temporarily in staging tables. A new feed contains those numbers alongside the sales data analysts need. Which integration approach meets the requirement?
- A native Windows reporting application, not written in Java, must query SQL Server, PostgreSQL and MySQL databases through one standard, language-neutral driver interface that the application already supports. What should the DBA set up on the reporting server?
- A developer is building a Java microservice that must read and write a relational database. The developer asks what the service needs in order to open connections to the database through the standard Java data access API. What is required?
- A company wants partner applications to query a product table over HTTPS as RESTful resources, using standardized URL query options such as $filter, $select and $top instead of custom endpoints for each request type. Which protocol best fits this requirement?
- A partner uploads nightly data files to the company using FTP. An audit finds that the partner's login credentials and the file contents cross the network in clear text. The partner's systems support SSH. Which replacement best addresses the finding?
- A file server hosts a landing folder for nightly extracts. Linux ETL hosts must mount it, and Windows reporting hosts must also access it, each using its operating system's native file-sharing protocol. Security requires that obsolete protocol versions be disabled. Which configuration fits?
- A legacy integration with an insurance carrier sends requests over HTTP as XML documents, each with an envelope containing a header and a body. The available operations and their message formats are defined in a WSDL file supplied by the carrier. Which type of interface is this?
- A nightly job pulls records from a SaaS platform's API, which returns JSON over HTTPS in pages of 100 records with a next-page token and responds with HTTP 429 when a client exceeds its request quota. The job misses records and often fails with 429 errors. Which fix is best?
- A fraud team needs website clickstream events analyzed within a few seconds of each click. Today the web servers write events to log files that a batch job loads into the database once each night. Which data acquisition approach meets the new requirement?
- An analytics team proposes writing a scraper to collect daily prices from a supplier's public web pages. The supplier also offers a documented API to registered partners, and the company is a registered partner. What should the DBA recommend before any scraper is built?
- A company has two integration needs: a mobile app needs a compact, lightweight payload from its API, and an industry partner requires documents that use namespaces and are validated against the partner's published XSD schema. Which pairing of data formats fits these needs?
- A supplier sends a comma-delimited flat file whose first row holds the column names. Some product descriptions contain commas and are wrapped in double quotes. The import splits those rows into extra columns and loads the header as a data row. Which change fixes the import?
- A partner's nightly extract arrives by file transfer, but the import loads only part of it and several rows contain garbled characters. The received file's size also differs from the size stated in the partner's manifest, which includes a SHA-256 hash of the file. What should the DBA do first?
- A nightly load calls a vendor's API, which is unavailable during the vendor's occasional maintenance windows and returns HTTP 503 errors. When that happens the job fails on the first error, and nobody notices until analysts find stale data the next morning. Which improvement should the DBA make?
- After a nightly import, customer names such as José and Müller appear in the database as José and Müller. The source system exports the file as UTF-8, and the load job reads files using the server's default single-byte code page. Which action resolves the problem?
- A European supplier sends a file with dates written as 03/04/2026, meaning 3 April 2026. The import interprets the values as month/day, so many dates land in the wrong month and any value with a day above 12 is rejected. Which fix should the DBA apply?
- A source team inserted a new column into the middle of a daily extract file without notice. The load job maps fields to target columns by their position, so it now fails on type conversion errors or puts values into the wrong columns. Which change best prevents a repeat?
- A source system stores invoice amounts as DECIMAL(12,2), but the target column was created as DECIMAL(8,2). Rows with amounts above 999,999.99 now fail to load with numeric overflow errors. Which fix should the DBA apply?
- After a security hardening project, the ETL service account still connects to the database successfully, but every INSERT into the staging table now fails with a permission denied error. The job needs to insert and read rows in that table only. What should the DBA do?
- An extract query opens its database connection successfully but fails after 30 seconds with a command timeout error. The query scans a very large table without using any filter on an indexed column, although an index exists on the date column the extract needs. What is the best approach?
- A pipeline decrypts incoming files with a data encryption key retrieved from the company's key vault. Last night the security team rotated that key. Files received before the rotation still decrypt correctly, but every file encrypted since then fails with a decryption error. What is the most likely cause?
- A platform uses envelope encryption: each dataset is encrypted with its own data encryption key, and those keys are stored only in encrypted form, wrapped by a master key held in the key management service. A security administrator disabled that master key. Now no dataset can be decrypted. Which statement explains the failure?
- A database server was upgraded to a new major version that uses a stronger default authentication method. The application servers kept their older client drivers. Since the upgrade, connections fail with an error stating that the authentication method is not supported. What should the DBA do?
- A Python integration script runs correctly on a developer's workstation but fails on the production server with an error saying a library function does not exist. Investigation shows the server has an older Python interpreter and older library versions than the workstation. What is the best fix?
- Two integration scripts fail overnight. The first stops immediately, before processing any rows, with the message unexpected token near FROM. The second processes 10,000 rows successfully and then stops with a division by zero error. How should these failures be classified?
- An analyst working in Python must clean a large tabular CSV file before it is loaded into the database: remove duplicate rows, fill in missing values, join it to a lookup table and pivot several columns. Which library is designed for this work?
- A Python data pipeline has two steps. The first performs fast, vectorized mathematical operations on large multidimensional numeric arrays. The second trains a classification model on the prepared data and evaluates it with cross-validation. Which libraries best match these two steps?
- A statistics team works in R and wants one consistent collection of packages for importing, tidying and transforming data. A separate engineering group uses a commercial numeric computing environment with its own language for matrix computations and simulations. Which tools match these two groups?
- A developer asks an AI assistant to write a SQL query for a monthly sales report. The generated query looks well formed and confident, but it references a column named customertier that does not exist anywhere in the database schema. What is this, and what is the best response?
- Developers ask an AI assistant to write a query for last month's sales by region using a one-line prompt. Each time, the assistant returns different table names, a different definition of last month and sometimes nonstandard SQL. Which change best improves the consistency of the results?
- An internal chatbot answers employee questions about HR policy using only what its language model learned in training. Policies change monthly, so many answers are outdated. The company wants answers grounded in the current policy documents without retraining the model each month. Which approach fits?
- An AI tool reviews the customer master table and suggests address corrections, which are currently applied to the table automatically. Audits find that some corrections were wrong and overwrote valid addresses. The business wants to keep using the tool. Which control best addresses the problem?
- Analysts need to query a CRM database, an ERP database and files in cloud object storage together through a single SQL interface. A compliance rule forbids copying the data into a central repository, so each source must remain the system of record. Which approach meets this need?
- Clerks spend hours retyping data from emailed PDF forms into a legacy desktop application. The application has no API, and the vendor does not permit direct writes to its database. Management wants to automate the work without replacing the application. Which approach fits?
- A model trained on customer records performs poorly in testing. Investigation finds that many customers appear several times in the training data and that key fields such as region and account age are often empty. What should the team address first before tuning the model?
- A team must integrate two systems. A modern SaaS CRM offers a documented REST API with authentication and versioning. A legacy terminal-based order application can be used only through its screens, has no API and allows no database access. Which integration approach should the team use for each?
These questions are original practice material and are NOT actual exam questions or brain-dump content. All vendor marks are trademarks of their respective owners. This site is not affiliated with, endorsed by, or sponsored by CompTIA, Inc.