DS0-001 Practice Test 19 — 15 Questions

Question 1

A multinational corporation is subject to the General Data Protection Regulation (GDPR). The data protection officer is reviewing the data lifecycle for customer personal data. Which two actions are required to comply with GDPR's data minimization and storage limitation principles? (Choose two.)

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

Accepted Answer

Implement a process to delete or anonymize personal data once the purpose for processing has been fulfilled.. GDPR's data minimization principle requires collecting only necessary personal data, while the storage limitation principle requires deleting or anonymizing data once its purpose is fulfilled. These two actions directly align with Article 5(1)(c) and (e). Implementing them reduces privacy risks and demonstrates accountability. The other options either violate GDPR (indefinite retention) or are unrelated to the specific principles of minimization and storage limitation.

Answer

Store all personal data in a single centralized database to simplify management.

Answer

Retain personal data indefinitely to support future analytics.

Answer

Encrypt all personal data at rest using AES-256.

Question 2

A database administrator is evaluating storage subsystems for a mission-critical database. The database uses random I/O patterns for most operations. Which storage performance metric is most critical for this workload?

Accepted Answer

IOPS. IOPS (Input/Output Operations Per Second) measures the number of read/write operations a storage subsystem can handle per second. For a database with random I/O patterns, each operation targets a different block, so the ability to service many small, scattered I/Os concurrently is the limiting factor. IOPS is therefore the most critical metric for random I/O workloads, directly determining transaction throughput and query responsiveness.

Answer

Cache hit ratio

Answer

Sequential throughput

Answer

Latency variation

Question 3

A data engineer is designing a table that stores sensor readings. Each row must include the sensor ID, a timestamp, and a numeric reading. The sensor ID and timestamp together uniquely identify each row, and the table will be queried frequently by sensor ID for the most recent reading. Which table design best supports this scenario?

Accepted Answer

Create a composite primary key on (sensor_id, reading_time) and add a nonclustered index on (sensor_id, reading_time DESC).. The correct design uses a composite primary key to enforce the natural uniqueness of sensor_id and reading_time, and a composite nonclustered index to accelerate the common query for the latest reading per sensor. This approach maintains data integrity while optimizing for the specified access pattern.

Answer

Create a clustered index on reading_time and a separate nonclustered index on sensor_id.

Answer

Create a primary key on sensor_id only and rely on application logic to prevent duplicate timestamps.

Answer

Create a surrogate integer primary key and no additional indexes on sensor_id or reading_time.

Question 4

When performing sentiment analysis on customer feedback, which preprocessing step is most critical to ensure words like 'Running' and 'run' are treated as the same base concept?

Accepted Answer

Lemmatization. Lemmatization or stemming reduces words to their root or base form.

Answer

Stop-word removal

Answer

Tokenization

Answer

Feature scaling

Question 5

A database administrator is defining the data lifecycle policy for a new deployment. What is the final step in the data lifecycle for records that have exceeded their retention period and are no longer required for regulatory compliance?

Accepted Answer

Purging. Purging is the final step in the data lifecycle, where records that have exceeded retention and are no longer needed for compliance are permanently deleted from all storage systems. It ensures data is irrecoverably removed, satisfying regulatory and privacy requirements. Archiving, migration, and encryption occur earlier in the lifecycle.

Answer

Encryption

Answer

Migration

Answer

Archiving

Question 6

A data analyst is building a report that joins a fact table of retail transactions to a dimension table of products. The product dimension contains a column 'category' that is frequently used in filters, but the analyst notices that many transactions have a NULL category because the product was not properly matched during the ETL load. The analyst wants to ensure that all transactions appear in the report, even when the category is missing, and that missing categories are labeled as 'Uncategorized'. Which SQL join type and function combination should the analyst use?

Accepted Answer

LEFT JOIN with COALESCE on the category column.. Using a LEFT JOIN from the transaction fact table to the product dimension ensures every transaction is retained, even when no product match exists. The COALESCE function then replaces the resulting NULL category with 'Uncategorized', so missing categories are labeled consistently. This combination satisfies both requirements: all transactions appear, and missing categories are clearly identified.

Answer

RIGHT JOIN with COALESCE on the category column.

Answer

FULL OUTER JOIN with COALESCE on the category column.

Answer

INNER JOIN with COALESCE on the category column.

Question 7

Which regulatory act specifically governs the protection of healthcare-related data?

Accepted Answer

HIPAA. HIPAA (Health Insurance Portability and Accountability Act) is the U.S. federal law that specifically governs the protection and privacy of protected health information (PHI). It sets standards for how healthcare data is stored, transmitted, and accessed. SOX, PCI DSS, and GDPR address financial reporting, payment cards, and general data privacy respectively.

Answer

SOX

Answer

PCI DSS

Answer

GDPR

Question 8

A database administrator is deploying a new PostgreSQL 16 cluster for an online transaction processing (OLTP) application. The application must survive the loss of any single server in the primary data center without manual intervention, and the administrator wants the cluster to automatically promote a standby if the primary fails. The administrator has three servers available and wants to minimize the risk of split-brain. Which deployment approach should the administrator use?

Accepted Answer

Deploy a three-node cluster managed by Patroni with etcd as the distributed configuration store to elect a leader and promote a standby automatically.. Patroni with a three-node etcd cluster provides distributed consensus for leader election, automatic promotion of a standby, and protection against split-brain. A single synchronous standby with manual scripts does not automate failover. Logical replication and WAL shipping without a cluster manager do not promote a standby or elect a leader, so they cannot meet the automatic failover requirement.

Answer

Configure streaming replication with a single synchronous standby and a manual failover script triggered by a monitoring alert.

Answer

Use pg_basebackup to create a physical standby, configure WAL shipping, and rely on the operating system cluster software to restart PostgreSQL on the standby.

Answer

Configure logical replication from the primary to three subscribers and use a load balancer to route writes to the first subscriber that responds.

Question 9

A retail company is classifying its data assets for a new governance program. The legal team asks which category of data, if disclosed, would most directly trigger notification obligations under state breach-notification laws because it can identify a specific individual. Which classification should be applied?

Accepted Answer

Personally Identifiable Information (PII), because it can be linked to a specific person.. Breach-notification laws are triggered by unauthorized exposure of data that can identify a specific individual. PII, which includes direct identifiers like names and government numbers, is the classification that maps to those obligations. Public data, confidential business information, and system telemetry do not by themselves identify a person, so they do not drive the same notification duties in a retail governance program.

Answer

Public data, because it is already available to anyone.

Answer

System telemetry data, because it records user activity.

Answer

Confidential business data, because it affects competitive advantage.

Question 10

Which TWO of the following are DDL (Data Definition Language) commands?

Accepted Answer

DROP. DROP (A) is a DDL command because it removes entire database objects such as tables, views, or indexes from the schema, altering the database structure. CREATE (C) is also DDL since it defines new database objects like tables, indexes, or schemas, changing the structure of the database. INSERT (B) is a DML command used to add rows of data into existing tables, not to change schema. SELECT (D) is a DML/DQL command that retrieves data and does not modify database structure. UPDATE (E) is DML because it modifies existing row values within a table rather than defining or altering schema objects.

Answer

INSERT

Answer

SELECT

Answer

UPDATE

Question 11

A data analyst needs to calculate the average order value per customer for the previous month. The Orders table contains CustomerID, OrderID, and OrderTotal. The analyst writes a query that includes the AVG(OrderTotal) function and groups by CustomerID. However, the result shows some customers with an average of NULL. Which condition could cause this?

Accepted Answer

The OrderTotal column contains NULL values for some orders, and AVG ignores NULLs, resulting in a NULL average if all orders for a customer have NULL totals.. The AVG function ignores NULL values. If a customer has orders but all their OrderTotal values are NULL, the average calculation has no valid input and returns NULL. This is the most likely cause of the NULL averages. To avoid this, the analyst could use COALESCE to substitute NULLs with zero or filter out such customers depending on the business requirement.

Answer

The GROUP BY clause is missing the OrderID column, causing incorrect grouping and NULL averages.

Answer

The AVG function cannot be used with a GROUP BY clause and returns NULL when grouped.

Answer

The query is filtering out orders with zero totals, and AVG treats zero as NULL.

Question 12

A new data engineer joins a team and needs read access to a production analytics database to troubleshoot a reporting issue. The security policy requires least privilege and time-bound access. Which approach best meets the policy?

Accepted Answer

Grant the engineer temporary read access to the specific schemas needed, with an expiration set at the end of the troubleshooting window.. Time-bound, scoped grants align access with the actual task and remove it automatically when the work ends. Granting read access only to the schemas needed for the reporting issue, with an expiration, enforces least privilege and creates auditable evidence that privileged access was both justified and temporary.

Answer

Grant the engineer the same read-only role used by the analytics team, with no expiration.

Answer

Add the engineer to the database's superuser group so they can inspect any object during troubleshooting.

Answer

Share the credentials of an existing read-only service account with the engineer for the duration of the task.

Question 13

A production database is experiencing blocking and deadlocks during peak hours. The DBA suspects that long-running transactions are holding locks. Which dynamic management view (DMV) should the DBA query to identify the blocking chain and the SQL text of the blocking session?

Accepted Answer

sys.dm_exec_requests joined with sys.dm_exec_sql_text. To diagnose blocking, the DBA needs to see which session is blocking and what SQL it is running. sys.dm_exec_requests includes blocking_session_id and sql_handle, which can be passed to sys.dm_exec_sql_text to retrieve the SQL. Other DMVs provide related but incomplete information, such as lock details or wait statistics, without the full context of the blocking chain and query text.

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_locks

Answer

sys.dm_os_wait_stats

Question 14

You are implementing a data governance framework. Which role is primarily responsible for ensuring that the data quality, security, and lifecycle policies are defined and adhered to for a specific business domain?

Accepted Answer

Data Steward. A Data Steward is the role accountable for a specific business domain's data — defining and enforcing data quality rules, security classifications, and lifecycle policies for that domain. Stewards bridge business and IT, translating governance policy into domain-specific standards and monitoring adherence. This domain-scoped accountability is precisely what the question describes.

Answer

Database Administrator

Answer

Chief Data Officer

Answer

Data Architect

Question 15

A financial services firm allows analysts to query a data warehouse through a BI tool. Security policy requires that analysts see only customers in their assigned portfolio, even though all customer rows reside in one table. The warehouse supports session variables that carry the analyst's portfolio identifier. Which control should be implemented?

Accepted Answer

Apply row-level security policies that filter rows using the session variable containing the analyst's portfolio identifier.. The policy demands per-analyst row visibility on a shared table. Row-level security with a session-variable predicate enforces this in the database, so every query path is constrained automatically. Schema separation, table encryption, and BI-generated filters either duplicate data, fail to filter rows, or can be bypassed, leaving the portfolio restriction incomplete.

Answer

Encrypt the customer table with a key that differs per portfolio and distribute keys to analysts.

Answer

Create a separate database schema per portfolio and grant each analyst access only to their schema.

Answer

Configure the BI tool to append a WHERE clause for the analyst's portfolio to every generated query.