DS0-001 Practice Test 18 — 15 Questions

Question 1

A financial services firm is designing a new data warehouse that will hold account balances and transaction histories. The governance committee wants to formalize who owns each data domain, who is accountable for its quality, and how stewardship duties are delegated across business units. Which artifact should the data governance lead produce first to establish these assignments?

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

Accepted Answer

A RACI chart for data domains. Establishing ownership and accountability requires an explicit mapping of data domains to roles. A RACI chart names who is responsible, who is accountable, who must be consulted, and who is informed for each domain, which is precisely the governance assignment the committee wants. Classification, retention, and metadata structures support governance but do not assign domain ownership or quality accountability.

Answer

A data classification matrix

Answer

A data retention schedule

Answer

A metadata repository schema

Question 2

You are performing a backup for a database that is in a 24/7 environment. Which backup type should you use if you want to avoid interfering with the existing backup sequence of full and differential backups?

Accepted Answer

Copy-only backup. A copy-only backup is a special type of backup that does not affect the normal backup sequence. It is independent of the regular full and differential backup chain, so it does not interfere with the differential base or log sequence. This makes it ideal for ad-hoc backups in a 24/7 environment without disrupting the existing backup strategy.

Answer

Full backup

Answer

Differential backup

Answer

Transaction log backup

Question 3

Which TWO of the following actions are considered best practices for maintaining data integrity in a data pipeline?

Accepted Answer

Automated schema validation. Automated schema validation (A) is a best practice because it enforces expected data types, column names, and constraints at each pipeline stage, catching malformed or drifted records before they corrupt downstream tables. Logging data quality metrics (E) is also a best practice because it provides continuous observability into completeness, uniqueness, validity, and timeliness, enabling alerting and root-cause analysis when integrity degrades. By contrast, using only manual entry (B) is error-prone and unscalable, hardcoding credentials in scripts (C) is a security anti-pattern that risks unauthorized access and tampering, and ignoring error logs (D) hides failures that would otherwise reveal integrity problems.

Answer

Using only manual entry

Answer

Hardcoding credentials in scripts

Answer

Ignoring error logs

Question 4

Which THREE of the following represent common data transformation tasks in a preparation workflow?

Accepted Answer

Data cleaning. Data cleaning (A) is a core transformation task because it fixes missing values, duplicates, outliers, and inconsistent formats so downstream analysis is reliable. Renaming columns (B) is a standard transformation that standardizes schema and improves readability before joining or modeling data. Data filtering (D) is also a transformation task because it selects or excludes rows based on conditions, reducing the dataset to the relevant subset. Database deletion (C) is an administrative or destructive operation, not a preparation transformation, and hardware installation (E) is an infrastructure task unrelated to data preparation workflows.

Answer

Database deletion

Answer

Hardware installation

Question 5

A company subject to GDPR needs to ensure that user data is deleted upon request across all distributed nodes in a NoSQL cluster. Which process is most critical to demonstrate compliance?

Accepted Answer

Automated Data Lineage and Lifecycle Tracking. GDPR's right to erasure (Article 17) requires that personal data be deleted across all systems where it resides, including replicas, shards, caches, and backups. Automated Data Lineage and Lifecycle Tracking is the only option that provides the visibility needed to locate every copy of a data subject's records across a distributed NoSQL cluster and enforce deletion policies consistently. Without lineage, you cannot prove to a regulator that deletion was complete.

Answer

Snapshotting

Answer

Database Sharding

Answer

Cache clearing

Question 6

A business analyst is preparing a quarterly sales report and needs to categorize customers into 'High', 'Medium', and 'Low' value tiers based on their total annual spend. The analyst writes a SQL query against a Microsoft SQL Server database and wants to assign the tier label in the result set without modifying the underlying table. Which SQL construct should the analyst use?

Accepted Answer

A CASE expression in the SELECT list that evaluates the total spend and returns the tier label.. A CASE expression in the SELECT list allows the analyst to evaluate each customer's total spend and return a tier label such as 'High', 'Medium', or 'Low' without modifying the table. This keeps the database unchanged and provides the required categorization for the report. Other options either modify data, aggregate without labeling, or filter without categorizing.

Answer

A HAVING clause that filters customers based on their total spend thresholds.

Answer

A GROUP BY clause on the total spend column to group customers into tiers.

Answer

An UPDATE statement that sets a new tier column in the customers table.

Question 7

A database administrator is deploying a new PostgreSQL database cluster that must support a read-intensive reporting workload. The reporting queries are long-running and scan large tables. The administrator wants to avoid blocking write transactions on the primary server. Which deployment approach should the administrator use?

Accepted Answer

Set up streaming replication with hot standby and route reporting queries to the standby server.. Streaming replication with hot standby allows a PostgreSQL standby to serve read-only queries, so long-running reports run on a separate server. This keeps the primary free to handle write transactions without competition from reporting queries. Synchronous replication would add write latency, while parallel query and partitioning keep the load on the primary.

Answer

Enable parallel query execution on the primary server to speed up reporting queries.

Answer

Configure synchronous replication to a standby server and direct reporting queries to the standby.

Answer

Implement table partitioning on the large tables and run reports against the primary server.

Question 8

A database administrator is deploying a new database server and needs to ensure that the database software starts automatically when the operating system boots. On a Linux system, which of the following should the administrator use to manage the database service?

Accepted Answer

systemctl. On modern Linux systems, systemd is the default init system, and systemctl is the command used to manage services. To ensure a database service starts automatically at boot, the administrator should use systemctl enable <service>. This creates the necessary symlinks for the service to be started during the boot process.

Answer

service

Answer

cron

Answer

init

Question 9

A financial services company stores trade records in a table where the combination of TradeDate and TradeID must be unique, but individual TradeID values can repeat on different dates. The database administrator needs to enforce this uniqueness at the database level. Which constraint should be implemented?

Accepted Answer

UNIQUE (TradeDate, TradeID). The requirement is to ensure that each TradeDate and TradeID combination is unique while allowing individual TradeID values to repeat on different dates. A composite UNIQUE constraint achieves exactly this by creating a unique index on both columns together. It does not restrict NULLs or conflict with a separate primary key, and it enforces the business rule at the database level without over-constraining the data.

Answer

FOREIGN KEY (TradeID) REFERENCES Trades(TradeID)

Answer

CHECK (TradeDate IS NOT NULL AND TradeID IS NOT NULL)

Answer

PRIMARY KEY (TradeDate, TradeID)

Question 10

Which THREE factors should be considered when choosing between a traditional RDBMS and a NoSQL database for a new project?

Accepted Answer

Consistency requirements (ACID vs BASE). Option A is correct because consistency requirements directly drive the choice: a traditional RDBMS enforces ACID transactions (atomicity, consistency, isolation, durability) with strong consistency, whereas many NoSQL databases follow BASE (basically available, soft state, eventual consistency), so the project's tolerance for stale reads and need for multi-row transactions must be evaluated. Option C is correct because data structure determines fit: RDBMSs use fixed schemas with tables, rows, and joins suited to structured data, while NoSQL stores (document, key-value, column-family, graph) handle semi-structured or unstructured data with flexible, evolving schemas. Option D is correct because scalability requirements differ fundamentally: RDBMSs typically scale vertically by adding CPU/RAM to a single node and are harder to shard, while NoSQL systems are designed for horizontal scaling via partitioning/replication across commodity nodes. Option B does not belong because the developer's preferred operating system is irrelevant—both RDBMS and NoSQL databases run on Windows, Linux, and macOS, and the choice is driven by data and workload needs, not the developer's desktop OS. Option E does not belong because the color of the database console UI is a cosmetic preference with no bearing on data modeling, consistency, scalability, or performance.

Answer

The developer's preferred operating system

Answer

The color of the database console UI

Question 11

Which TWO of the following are benefits of using a data warehouse?

Accepted Answer

Optimization for complex analytical queries. Option B is correct because a data warehouse is specifically architected for analytical workloads, using columnar storage, star/snowflake schemas, and MPP engines to efficiently run complex aggregations, joins, and OLAP queries over large historical datasets. Option C is correct because a data warehouse consolidates data from multiple heterogeneous source systems into a single, subject-oriented, integrated repository, giving the enterprise one authoritative version of the truth for reporting and BI. Option A is wrong because a data warehouse complements, rather than replaces, source systems — the OLTP systems remain the systems of record that feed the warehouse via ETL/ELT. Option D is wrong because a warehouse stores and serves data; it does not itself perform automated AI decision making, which requires separate ML/analytics tooling. Option E is wrong because warehouses are optimized for bulk analytical reads, not low-cost real-time transactional updates, which are the domain of OLTP databases.

Answer

Complete replacement of all source systems

Answer

Automated AI decision making

Answer

Low cost for real-time transactional updates

Question 12

When preparing a database deployment plan, what THREE factors must be considered to ensure successful capacity planning?

Accepted Answer

Peak transactional throughput (transactions per second).. Option A (peak transactional throughput in transactions per second) is correct because capacity planning must size CPU, memory, and I/O to handle the busiest workload period, not just average load, since peak TPS drives the required IOPS and connection limits. Option C (projected data growth rates over the next 12 to 24 months) is correct because storage, backup windows, and index maintenance scale with data volume, so forecasting growth prevents premature exhaustion of disk and memory resources. Option D (data retention and archival policies) is correct because retention periods determine how long data stays in primary storage versus being archived or purged, directly affecting total storage requirements, backup size, and query performance. Option B is incorrect because dashboard color scheme is a cosmetic UI preference with no bearing on resource sizing or performance. Option E is incorrect because the total count of registered Active Directory users does not indicate concurrent database connections or workload; only active, concurrent sessions and their query patterns matter for capacity planning.

Answer

The chosen color scheme for the database management dashboard.

Answer

The total number of users currently registered in the company's Active Directory.

Question 13

A data engineer must load a 400 GB Parquet dataset from an object store into a columnar warehouse. The source files are partitioned by date but each partition contains many small files averaging 5 MB. Queries against the loaded table are slow because the warehouse scans far more data than necessary. Which action most directly improves query performance by reducing scanned data?

Accepted Answer

Compact the small files into larger files and ensure the target table is partitioned on the same date column.. Query latency in columnar warehouses is dominated by how much data must be read and how many files must be opened. Many small files increase metadata and open/close overhead, and a lack of partitioning on the common filter column forces full scans. Compacting files and aligning partitioning with the date column enable partition pruning and efficient columnar reads, directly reducing scanned data.

Answer

Convert the Parquet files to CSV before loading to simplify parsing.

Answer

Increase the warehouse compute cluster size to add more nodes.

Answer

Compress each 5 MB file with gzip before loading.

Question 14

A retail company is implementing a new data classification policy. The security team needs to categorize data based on its sensitivity and the impact of unauthorized disclosure. Which data classification level typically requires the highest level of protection and is subject to the strictest access controls?

Accepted Answer

Restricted. Restricted data represents the highest level of sensitivity in most classification schemes. It demands the strictest access controls because unauthorized disclosure could result in severe consequences, including legal penalties, financial loss, or harm to individuals. By classifying data as Restricted, organizations enforce need-to-know access, encryption, and auditing to protect it appropriately. This aligns with the scenario's requirement for the highest level of protection.

Answer

Public

Answer

Internal

Answer

Confidential

Question 15

An organization is migrating sensitive financial records to a cloud environment. Which security control ensures that only authorized applications can decrypt the data?

Accepted Answer

Cloud Hardware Security Module (Cloud HSM). A Cloud Hardware Security Module (Cloud HSM) is a FIPS 140-2/3 validated appliance that stores cryptographic keys in tamper-resistant hardware and performs encryption/decryption operations inside the module. Because keys never leave the HSM in plaintext, only applications authorized to call the HSM's key operations can decrypt the data — satisfying the requirement that decryption be restricted to authorized applications.

Answer

Database Replication

Answer

Database Triggers

Answer

Shared Responsibility Model