DS0-002
Free DS0-002 practice questions
10 questions from our DS0-002 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Database Fundamentals
A company runs an e-commerce platform needing strong consistency for payments and an activity feed that tolerates eventual consistency at very high write volume. Which design BEST fits both needs?
- AA single relational database for both, scaling vertically as feed traffic grows
- BA relational database for payments and a wide-column NoSQL store for the feed Correct answer
- CA graph database for payments and a relational database for the activity feed
- DA single key-value store for both, with application code handling payment rollbacks
Answer and explanation: Polyglot persistence lets payments use an ACID relational engine while the feed uses a horizontally scalable wide-column store designed for high write volume and eventual consistency. One relational database would struggle to scale the feed writes economically. Handling payment rollbacks in application code over a key-value store weakens transactional guarantees. A graph database brings no benefit to payments, and a relational feed store faces the same write-scaling limits.
Question 2 of 10, Database Fundamentals
A developer writes a stored procedure and a user-defined scalar function. Which statement correctly describes a difference between them?
- AA function can only run DDL statements, while a stored procedure can only run DML statements
- BA scalar function returns a value usable in SELECT; a procedure runs via CALL or EXEC Correct answer
- CA stored procedure can be used directly inside a WHERE clause, while a function must be called with EXEC
- DA stored procedure is compiled each time it runs, while a function is compiled only once at creation
Answer and explanation: A scalar function returns a single value and can be embedded in expressions such as SELECT lists or WHERE clauses, whereas a stored procedure is executed as a statement with CALL or EXEC. Procedures cannot be used inside a WHERE clause. Functions are not restricted to DDL; in most engines they are restricted from side effects. Compilation and plan caching behaviour does not split along the lines described.
Question 3 of 10, Database Deployment
A company deploys a new database on a VM. The VM has a 2 TB volume for data and logs on the same disk, and write latency spikes during heavy transaction periods. Which change BEST addresses the issue?
- AIncrease the volume to 4 TB so more free space is available to the engine
- BMove the operating system page file onto the shared data and log volume
- CEnable compression on the data volume to reduce the number of writes
- DPut the transaction logs on a separate low-latency volume Correct answer
Answer and explanation: Transaction log writes are sequential and latency-sensitive, so separating them from random data-file I/O on a dedicated fast volume reduces contention. Adding the page file to the same disk increases contention. File-system compression adds CPU overhead and is generally discouraged for database files. More capacity does not improve latency on the same disk.
Question 4 of 10, Database Deployment
A company must migrate a 4 TB on-premises database to a cloud DBaaS with less than 15 minutes of downtime. Which migration approach BEST meets the requirement?
- ACopy the data files to object storage and attach them to the managed service
- BRebuild the schema in the cloud and have users re-enter recent transactions
- CSeed the target from a backup, then sync with replication or CDC and cut over Correct answer
- DTake a full export during a maintenance window and import it into the target service
Answer and explanation: Seeding from a backup and keeping the target in sync with replication or change data capture leaves only a short cutover window. A full export and import of 4 TB would take hours of downtime. Managed DBaaS offerings typically do not let you attach raw data files. Manual re-entry is error-prone and not a migration strategy.
Question 5 of 10, Database Management and Maintenance
A stored procedure runs fast for most customers but very slowly for one large customer, and performance changes after each server restart depending on which customer calls it first. What is the MOST likely cause?
- AIndex fragmentation that grows after every server restart
- BA deadlock between concurrent calls of the same procedure on the table
- CParameter sniffing cached a plan built for unrepresentative values Correct answer
- DA missing primary key on the customer table
Answer and explanation: The cached plan is built for the first parameter values seen, so a plan optimized for a small customer performs badly for a large one, or vice versa; this is parameter sniffing. Fragmentation does not depend on which customer runs first. Deadlocks cause errors, not plan-dependent slowness. A missing primary key would affect all customers.
Question 6 of 10, Database Management and Maintenance
Capacity reports show the database grows 40 GB per month and the volume has 300 GB free. Purchasing storage takes three months. What should the DBA do?
- ADelete the oldest transaction log backups to free space on the volume
- BShrink the database files monthly to keep free space stable
- CWait until free space drops below 50 GB before raising a request
- DRequest more storage now, since about seven months of space remain Correct answer
Answer and explanation: About 7.5 months of growth remain, and with a three-month procurement lead time plus margin the request should be raised now. Waiting until 50 GB leaves only about five weeks, less than the lead time. Shrinking does not reduce real data growth and fragments indexes. Deleting log backups breaks recovery and does not address data growth on the volume.
Question 7 of 10, Data and Database Security
A company stores encrypted backups in cloud object storage. An attacker with stolen storage credentials could delete them. Which combination BEST protects the backups?
- AVersioning enabled with the same credentials used by production
- BObject lock in compliance mode plus separate backup credentials Correct answer
- CLifecycle rules moving backups to an archive tier after 30 days
- DServer-side encryption with provider keys plus public read access disabled
Answer and explanation: Object lock in compliance mode prevents deletion or overwrite until retention ends, and separate credentials limit who can touch backups at all. Encryption and blocking public reads do not stop deletion by an authenticated attacker. Archive tiers reduce cost but allow deletion. Versioning with shared production credentials lets an attacker delete versions too.
Question 8 of 10, Data and Database Security
A test team needs a copy of production that contains no real personal data but keeps realistic formats for testing. Which technique is MOST appropriate?
- ATransparent data encryption enabled on the test copy
- BStatic data masking applied when the test copy is created Correct answer
- CDynamic data masking applied to the test team's database role
- DRestricting the test team to read-only access on production
Answer and explanation: Static masking permanently substitutes realistic but fake values in the copy, so no real personal data reaches the test environment. Dynamic masking leaves real data in the database, where privileged users or bypasses could expose it. TDE protects files at rest but users still see real data. Read-only production access still exposes real personal data.
Question 9 of 10, Business Continuity
A DBA plans backups for a 6 TB database with a 4-hour nightly window. A full backup takes 7 hours. Which strategy fits the window while keeping restores reasonable?
- AWeekly full on the weekend, nightly differentials, and frequent log backups Correct answer
- BNightly full backups started early in the evening so they finish after the window
- CMonthly full backups and nightly incrementals with no log backups
- DA nightly full backup of only the largest tables to save time
Answer and explanation: A weekend full plus nightly differentials keeps each weeknight job inside the window, and log backups support point-in-time recovery; restore needs one full, one differential and the logs. A nightly full overruns the window. Monthly fulls with incrementals create a long restore chain. Backing up only some tables yields an inconsistent, incomplete recovery.
Question 10 of 10, Data Integration
A company trains a hiring model using historical decisions that favored certain groups. Which data integration concern should be raised before using the data?
- ABias in the training data that the model would learn and reproduce Correct answer
- BRetention costs from keeping years of historical HR records in the warehouse
- CEncoding issues that turn accented names into garbled characters
- DToo many indexes on the source HR tables slowing extraction
Answer and explanation: Historical decisions that favored certain groups embed bias, and a model trained on them will reproduce it, so data must be assessed and mitigated before use. Encoding, storage costs and extraction performance are technical concerns that do not address unfair outcomes.