DS0-001 Practice Test 8 — 10 Questions

Question 1

You are managing a PostgreSQL database that supports a global application. Users in different regions experience slow query performance when filtering on a timestamp column that stores UTC values. You need to ensure that queries filtering on local time are efficient. What should you do?

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

Accepted Answer

Create an expression index on the timestamp column converted to the local time zone. An expression index on the timestamp column converted to the local time zone enables the database to use an index for queries that filter on local time. This maintains the storage in UTC while providing efficient access for each region's queries, avoiding full table scans caused by non-sargable predicates.

Answer

Add a nonclustered index on the timestamp column

Answer

Change the column data type to timestamp with time zone

Answer

Create a separate materialized view for each region with local time

Question 2

A developer needs to ensure that a 'Customer_ID' field in a 'Orders' table always references a valid ID in the 'Customers' table. Which constraint should be applied?

Accepted Answer

FOREIGN KEY. A FOREIGN KEY constraint is used to enforce referential integrity between two tables. It ensures that the Customer_ID in the Orders table matches an existing ID in the Customers table, preventing orphaned records and maintaining consistent relationships.

Answer

PRIMARY KEY

Answer

UNIQUE

Answer

CHECK

Question 3

A database administrator is configuring a new SQL Server instance that will host a database with sensitive financial data. The compliance team requires that all data at rest be encrypted, and that the encryption keys be managed by a hardware security module (HSM). Which SQL Server feature should the administrator implement to meet these requirements?

Accepted Answer

Transparent Data Encryption (TDE) with an Extensible Key Management (EKM) provider. Transparent Data Encryption (TDE) encrypts the entire database, including data and log files, at rest. When combined with an Extensible Key Management (EKM) provider, the asymmetric key that protects the Database Encryption Key can be stored in a hardware security module (HSM), meeting the compliance requirement for HSM-based key management. Always Encrypted, Row-Level Security, and Dynamic Data Masking do not encrypt all data at rest.

Answer

Always Encrypted with secure enclaves

Answer

Row-Level Security (RLS) with column encryption

Answer

Dynamic Data Masking (DDM) with a certificate

Question 4

A data analyst needs to retrieve a list of all unique countries from a Customers table, sorted alphabetically. Which SQL statement should be used?

Accepted Answer

SELECT DISTINCT country FROM Customers ORDER BY country;. To retrieve unique countries sorted alphabetically, the DISTINCT keyword must be used to eliminate duplicates, and ORDER BY country must be used to sort the results. This combination directly fulfills both requirements in a single, standard SQL statement.

Answer

SELECT country FROM Customers GROUP BY country;

Answer

SELECT country FROM Customers ORDER BY country;

Answer

SELECT UNIQUE country FROM Customers ORDER BY country;

Question 5

A database administrator is deploying a new database instance that will store sensitive customer information. The organization must comply with PCI DSS requirements for encryption of data at rest and in transit. Which TWO configurations should the administrator implement to meet these requirements? (Choose two.)

Accepted Answer

Configure SSL/TLS for all client connections to the database.. TDE encrypts data files at rest, and SSL/TLS encrypts data in transit, directly addressing PCI DSS encryption requirements. Column-level encryption for all columns is impractical, auditing does not encrypt data, and password hashing only protects credentials, not the data itself.

Answer

Enable database auditing to track all SELECT statements on customer tables.

Answer

Configure the database to use only strong password hashing for authentication.

Answer

Implement column-level encryption for all columns in the database.

Question 6

A database administrator needs to store unstructured data that scales horizontally across multiple servers while maintaining high availability. Which database structure should be selected?

Accepted Answer

NoSQL (Document Store). A NoSQL document store is designed for unstructured or semi-structured data and natively supports horizontal scaling through sharding and replication across commodity servers, delivering high availability via automatic failover. Document stores such as MongoDB or Couchbase store data as flexible JSON-like documents, so schema changes do not require migrations. This combination of schema flexibility, horizontal scale, and built-in replication matches the requirement precisely.

Answer

Relational (RDBMS)

Answer

Network

Answer

Hierarchical

Question 7

A financial institution is designing a data governance program. The Chief Data Officer wants to ensure that data quality issues are identified and resolved before data is used in regulatory reporting. The organization has multiple business units that each own their data. Which data governance component should be established to formally assign accountability for data quality and define escalation paths?

Accepted Answer

A data stewardship program with defined data owners and stewards for each domain.. Establishing a data stewardship program with data owners and stewards is the correct approach because it formally assigns accountability for data quality to specific roles within each business domain. This structure ensures that issues are identified and resolved by the responsible parties, with clear escalation paths. It aligns with data governance best practices by embedding ownership into the organization and enabling proactive management of data quality before regulatory reporting.

Answer

A master data management (MDM) system that consolidates data into a single golden record.

Answer

A data quality dashboard that monitors and reports on data quality metrics across business units.

Answer

A data lineage tool that tracks the flow of data from source to report.

Question 8

Which scenario best justifies the use of a relational database over a NoSQL database?

Accepted Answer

Financial transactions. Financial transactions require strict ACID guarantees — atomicity, consistency, isolation, and durability — which relational databases provide natively through transactions and constraints. NoSQL databases typically relax these guarantees in favor of scalability and availability, making them unsuitable for scenarios where data integrity is paramount.

Answer

Big data analytics

Answer

Rapid prototyping

Answer

Social media feeds

Question 9

You need to change the collation of a database. What is the impact of performing this operation?

Accepted Answer

It requires rebuilding the database or recreating tables. Changing a database's collation is not a simple metadata update; it affects how data is stored, compared, and sorted. In most database systems (e.g., SQL Server, MySQL), altering collation requires rebuilding the database or recreating tables and indexes, because existing data must be converted to the new collation's encoding and comparison rules.

Answer

It can be done instantly with an ALTER command

Answer

It is a metadata-only change

Answer

It only impacts system tables

Question 10

A developer wants to use a subset of production data for testing. Which method ensures the test data retains its structure while protecting privacy?

Accepted Answer

Data Masking/De-identification. Data masking (de-identification) replaces sensitive values with realistic but fictitious data while preserving the schema, data types, and referential relationships. This lets developers test against production-like structures without exposing real PII, satisfying both usability and privacy requirements.

Answer

Database Snapshotting

Answer

Database Migration

Answer

Full Database Clone