DS0-001 Full-Length Exam Simulation

Question 1

A database administrator needs to implement column-level encryption for sensitive PII in a SQL Server environment. Which tool should be used to ensure the encryption keys are managed outside the database engine?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

SQL Server Always Encrypted with Azure Key Vault. SQL Server Always Encrypted with Azure Key Vault stores encryption keys outside the database engine, ensuring even DBAs cannot see plaintext PII. Column-level encryption is applied at the client driver, so the database only ever handles ciphertext. Azure Key Vault provides centralized, hardware-backed key management with auditing and access policies.

Answer

Database File Stream

Answer

Transparent Data Encryption (TDE)

Answer

SQL Server Data Masking

Question 2

A database administrator needs to grant a new analyst read-only access to a specific table named 'SalesData' in a PostgreSQL database. The analyst should be able to query the table but not modify it. Which SQL statement should the administrator execute?

Accepted Answer

GRANT SELECT ON SalesData TO analyst;. The GRANT SELECT statement is the correct way to provide read-only access to a table in PostgreSQL. It allows the analyst to query the table without granting modification rights. Other options either grant excessive privileges (ALL PRIVILEGES), use invalid privilege types (USAGE, READ), or fail to meet the requirement. This aligns with the principle of least privilege.

Answer

GRANT USAGE ON SalesData TO analyst;

Answer

GRANT ALL PRIVILEGES ON SalesData TO analyst;

Answer

GRANT READ ON SalesData TO analyst;

Question 3

A database administrator needs to grant a user the ability to retrieve data from a specific table but must prevent the user from modifying or deleting any data. Which SQL statement should the administrator execute?

Accepted Answer

GRANT SELECT ON table_name TO user_name;. The correct statement is GRANT SELECT, which provides read-only access. It satisfies the need to retrieve data while ensuring the user cannot modify or delete records. Other options either grant excessive privileges or do not grant the necessary read access.

Answer

GRANT REFERENCES ON table_name TO user_name;

Answer

GRANT ALL PRIVILEGES ON table_name TO user_name;

Answer

GRANT INSERT, UPDATE, DELETE ON table_name TO user_name;

Question 4

A database administrator is designing a backup strategy for a 2 TB OLTP database that must support point-in-time recovery with a maximum data loss window of 15 minutes. Full backups complete in 4 hours, and the transaction log is backed up every 10 minutes. What is the most appropriate recovery model for this database?

Accepted Answer

Full recovery model. The full recovery model is necessary to support transaction log backups and point-in-time recovery. With log backups every 10 minutes, the maximum data loss is 10 minutes, meeting the 15-minute requirement. The simple recovery model does not allow log backups, and bulk-logged recovery may not guarantee point-in-time recovery after bulk operations. The model database is not a recovery model for user databases.

Answer

Bulk-logged recovery model

Answer

Simple recovery model

Answer

Model recovery model

Question 5

A database administrator is deploying a new PostgreSQL 15 instance that will store customer PII. The security policy requires that all data at rest be encrypted, and the platform team wants encryption tied to the host's Trusted Platform Module without managing passphrases. Which deployment approach best satisfies this requirement?

Accepted Answer

Rely on the operating system's full-disk encryption configured with TPM-backed key sealing, and leave PostgreSQL's own storage unencrypted.. Storing PII at rest requires confidentiality for every byte the database writes, including WAL, temporary files, and indexes. TPM-sealed full-disk encryption delivers that transparently and removes the operational burden of passphrase entry, because the key is unsealed only by the specific host. Application-layer column encryption and TLS protect narrower scopes, and checksums address integrity rather than secrecy.

Answer

Set the ssl=on and ssl_cert_file parameters in postgresql.conf so that client connections and stored data are both encrypted.

Answer

Enable the pgcrypto extension and encrypt individual PII columns with pgp_sym_encrypt using a key stored in the application config.

Answer

Configure data_checksums=on during initdb so that every page is checksummed and therefore protected against unauthorized reads.

Question 6

A data architect is designing a schema for a system that must store highly variable product attributes across thousands of categories, where each category has a different set of attributes and the schema must evolve frequently without downtime. Which database structure is best suited for this requirement?

Accepted Answer

A document-oriented NoSQL store with a flexible schema per product. A document-oriented NoSQL store handles variable attributes per product without schema migrations, directly supporting frequent evolution and avoiding downtime. Fixed-column tables, attribute-per-dimension star schemas, and entity-attribute-value designs either require schema changes or introduce query complexity and performance issues.

Answer

A relational table with a fixed set of columns for all possible attributes

Answer

A normalized relational schema with an entity-attribute-value table

Answer

A star schema with a dimension table per attribute

Question 7

A data engineer is profiling a customer table and notices that the Email column contains several NULL values, duplicate addresses, and inconsistent formatting such as mixed uppercase and lowercase characters. The engineer wants to quantify how many rows have a NULL Email before deciding on a cleansing strategy. Which SQL expression should be used to return that count?

Accepted Answer

SELECT COUNT(*) FROM Customer WHERE Email IS NULL;. Missing values are detected with the IS NULL predicate, and the number of such rows is obtained with COUNT(*), which counts rows regardless of column contents. COUNT(Email) would exclude NULLs and return zero when combined with the IS NULL filter. Therefore, counting all rows where Email IS NULL gives the accurate count of missing email addresses needed for data profiling.

Answer

SELECT COUNT(*) FROM Customer WHERE Email = NULL;

Answer

SELECT COUNT(Email) FROM Customer WHERE Email IS NULL;

Answer

SELECT COUNT(Email) FROM Customer;

Question 8

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

Accepted Answer

Missing column in GROUP BY. SQL requires that every column in the SELECT list be either aggregated (e.g., SUM, COUNT, AVG) or included in the GROUP BY clause when aggregation is used. The error 'column is not part of an aggregate or GROUP BY' means a non-aggregated column was selected without being listed in GROUP BY, so the engine cannot determine which value to return per group.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE

Question 9

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

Accepted Answer

Incompatible data types. Incompatible data types are the most likely DDL-related failure point when migrating between different relational database engines (e.g., Oracle to PostgreSQL, SQL Server to MySQL). Each RDBMS implements its own type system with different sizes, precision, and semantics — for example, Oracle's NUMBER vs. SQL Server's DECIMAL, or MySQL's TINYINT(1) vs. PostgreSQL's BOOLEAN — so DDL that creates tables will fail or silently truncate data if types aren't mapped correctly.

Answer

Table naming conventions

Answer

Storage engine choice

Answer

Primary key naming

Question 10

A data warehouse team is deploying a partitioned fact table in SQL Server 2022 that will hold seven years of sales data. Most queries filter on the order date, and the team wants the oldest year moved to cheaper storage while remaining queryable. Which deployment design best meets these goals?

Accepted Answer

Create a partitioned table with a partition function on the order date, map each year to its own filegroup, and place the oldest filegroup on cheaper storage with a read-only filegroup setting.. Native partitioning on the date column provides two things at once: the optimizer eliminates partitions that cannot match a date predicate, and each partition lives in its own filegroup so it can be placed on a different storage tier. Marking the oldest filegroup read-only prevents accidental modification while keeping the data available to the same queries.

Answer

Create seven separate tables, one per year, and build a UNION ALL view across them for reporting queries.

Answer

Enable table compression on the fact table and archive the oldest year to a separate database that is restored on demand.

Answer

Create a single unpartitioned table with a clustered columnstore index and rely on column elimination to skip the oldest year.

Question 11

Which THREE of the following are components of a standard SQL SELECT statement?

Accepted Answer

WHERE. The WHERE clause (C) is a core component of a standard SQL SELECT statement because it filters the rows returned by the query according to a specified predicate. The FROM clause (D) is also essential, as it specifies the table or tables from which the data is retrieved. The GROUP BY clause (E) is a standard SELECT component that aggregates rows sharing common values into groups, typically used with aggregate functions such as COUNT, SUM, or AVG. By contrast, DROP (A) is a DDL statement used to delete database objects like tables or views, and GRANT (B) is a DCL statement used to assign privileges to users, so neither belongs inside a SELECT statement.

Answer

DROP

Answer

GRANT

Question 12

A data engineer is designing a database for a sensor network that generates 20,000 readings per second. The schema is fixed, and each reading must be stored with minimal write latency while preserving the exact order of arrival for later time-series analysis. Which database structure should the engineer choose?

Accepted Answer

Append-only log-structured store keyed by timestamp and sensor ID. The scenario demands high-throughput, low-latency ingestion with preserved arrival order. An append-only log-structured store achieves this by writing sequentially and keying entries by timestamp and sensor ID, which supports both fast writes and efficient time-range reads. Relational, document, and graph structures each introduce overhead or modeling mismatches that prevent meeting the stated performance and ordering requirements.

Answer

Document store with each sensor's readings embedded in a single growing document

Answer

Graph database with sensor nodes connected by reading edges

Answer

Relational table with a composite primary key on sensor ID and timestamp

Question 13

A company is designing a distributed NoSQL database deployment using MongoDB. They need to ensure high availability and automatic failover in the event that a primary node becomes unreachable. What must the architect configure to achieve this?

Accepted Answer

Deploy a MongoDB Replica Set with at least three nodes.. MongoDB achieves high availability and automatic failover through replica sets, which require a minimum of three nodes to maintain a majority for elections. Deploying a replica set with at least three nodes (option D) ensures that if the primary becomes unreachable, the remaining nodes can elect a new primary automatically, providing both redundancy and failover. This is the standard MongoDB architecture for HA.

Answer

Implement database mirroring using the primary-secondary synchronous replication setting.

Answer

Increase the write concern to 'all' to force nodes to write simultaneously.

Answer

Configure a sharded cluster with a single mongos instance.

Question 14

Which document is essential to establish the legal basis for processing personal data under GDPR between a controller and a processor?

Accepted Answer

Data Processing Agreement (DPA). A Data Processing Agreement (DPA) is the legally required contract under GDPR Article 28 that governs the relationship between a data controller and a data processor. It specifies the subject matter, duration, nature and purpose of processing, the type of personal data, categories of data subjects, and the obligations and rights of the controller. Without a DPA in place, the processing of personal data by a third party is unlawful under GDPR.

Answer

Service Level Agreement (SLA)

Answer

Acceptable Use Policy (AUP)

Answer

Data Privacy Impact Assessment (DPIA)

Question 15

A data architect is designing a schema for a high-concurrency e-commerce system. They decide to use a surrogate key instead of a natural key for the Orders table to ensure join performance and data integrity. What is the primary advantage of this design choice?

Accepted Answer

It reduces the storage size of the index compared to a string-based natural key. A surrogate key (e.g., an auto-incrementing integer or GUID) is typically much smaller than a natural key such as a string-based order number or composite business key. Because the surrogate key is used in indexes and foreign key relationships, the smaller key size reduces index page size, allowing more index entries per page, which improves join performance and reduces I/O. This is the primary advantage in a high-concurrency e-commerce system where Orders is joined frequently.

Answer

It prevents the insertion of duplicate records

Answer

It ensures that the database complies with ACID requirements

Answer

It automatically creates a clustered index on the table

Question 16

A DBA is designing a backup strategy for a large SQL Server database that must support point-in-time recovery and minimize backup windows. The database is in the full recovery model. Which two backup types should the DBA include in the strategy to achieve these goals? (Choose two.)

Accepted Answer

Full backup. To support point-in-time recovery, the full recovery model requires a full backup to establish the baseline and transaction log backups to capture ongoing changes. These two types are essential. Differential backups can help reduce restore time but are not mandatory. Copy-only and filegroup backups serve other purposes and do not by themselves enable point-in-time recovery.

Answer

Filegroup backup

Answer

Copy-only backup

Answer

Differential backup

Question 17

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

Accepted Answer

Move the log to a high-performance dedicated drive. The log drive is the write-ahead log (WAL/redo) target, and it is written sequentially and synchronously on every commit. If that drive is slow or shared with data files, commits stall and I/O wait spikes. Moving the log to a dedicated high-performance device (SSD/NVMe, or a dedicated volume) removes contention and gives the sequential log stream the IOPS and low latency it needs.

Answer

Use data compression

Answer

Increase the buffer pool size

Answer

Reduce the database recovery interval

Question 18

A data engineer is deploying a new MySQL 8.0 instance that will store customer records for users in the European Union. The compliance team requires that all data at rest be encrypted and that encryption keys be managed separately from the database server. Which action should the engineer take?

Accepted Answer

Enable InnoDB tablespace encryption using a keyring plugin backed by a remote key management service.. InnoDB tablespace encryption with a keyring plugin connected to a remote key management service encrypts the tablespaces, redo logs, and undo logs while keeping the master key outside the database host. This directly meets both the at-rest encryption mandate and the separation-of-key-custody requirement, unlike transport encryption or filesystem-level encryption.

Answer

Enable binary log encryption and rotate the binary log encryption key every 30 days.

Answer

Configure SSL/TLS for all client connections and require X.509 certificates for authentication.

Answer

Enable Transparent Data Encryption at the operating system level using eCryptfs on the data directory.

Question 19

When reviewing a database security configuration, which finding poses the highest risk?

Accepted Answer

Enabled default accounts with default passwords. Enabled default accounts with default passwords pose the highest risk because they are publicly known credentials that attackers routinely scan for and exploit. Default accounts like 'sa', 'admin', or 'root' with unchanged passwords (e.g., 'password', 'admin') provide immediate, often privileged access to the database, enabling data theft, ransomware, or lateral movement. This is a critical vulnerability that violates every security baseline (CIS, OWASP, NIST).

Answer

Old server hardware

Answer

Lack of database documentation

Answer

Redundant indexes

Question 20

Which technology provides the best protection against data exfiltration from an on-premises database by a rogue administrator?

Accepted Answer

Database Activity Monitoring (DAM) with Firewall. A Database Firewall inspects incoming and outgoing SQL traffic to block unauthorized queries or data exports.

Answer

Database Clustering

Answer

Database Archiving

Answer

Database Sharding