DS0-001 Practice Test 3 — 30 Questions

Question 1

What is the benefit of using a 'Dashboard' over a static report?

Accepted Answer

Dynamic exploration and interactivity. A dashboard's defining advantage over a static report is interactivity: users can filter, drill down, change time ranges, and pivot dimensions in real time without requesting a new report. This dynamic exploration lets stakeholders answer follow-up questions on the spot, whereas a static report presents a fixed snapshot that must be regenerated for any variation.

Answer

Simplified data security

Answer

Lower storage requirements

Answer

Easier to print

Question 2

When analyzing JSON data stored in a relational database using SQL, how do you typically access nested key-value pairs?

Accepted Answer

Using path-based operators. Path-based operators are the correct approach because JSON stored in relational databases (e.g., PostgreSQL's jsonb, MySQL's JSON, SQL Server's JSON functions) is accessed using path expressions that navigate nested keys and array indices. Examples include PostgreSQL's -> and ->> operators, MySQL's JSON_EXTRACT() and -> syntax, and SQL Server's JSON_VALUE() with '$.key.subkey' paths. These operators let you drill into nested structures without transforming the data.

Answer

Converting to XML first

Answer

Using the SELECT * command

Answer

Using standard JOINs

Question 3

In data visualization, what is the 'Data-Ink Ratio' intended to improve?

Accepted Answer

Chart readability and clarity. The Data-Ink Ratio, introduced by Edward Tufte, is the proportion of ink used to display actual data versus total ink used in the graphic. A higher ratio means more of the visual elements convey data, reducing non-data ink like gridlines, borders, and decorative elements. This directly improves chart readability and clarity by minimizing distractions and focusing the viewer on the data. Thus, option D is correct.

Answer

Chart rendering speed

Answer

Color palette selection

Answer

Data compression

Question 4

An administrator is deploying a new database and needs to ensure that the backup strategy complies with the '3-2-1' backup rule. What does the '1' in this rule represent?

Accepted Answer

One off-site copy. The 3-2-1 backup rule states: keep at least 3 copies of data, on 2 different media types, with 1 copy stored off-site. The '1' specifically refers to the off-site copy, which protects against site-wide disasters such as fire, flood, or ransomware that could destroy all local copies simultaneously.

Answer

One recovery point objective (RPO) of zero

Answer

One local copy

Answer

One full backup per day

Question 5

To ensure non-repudiation in a database system, what must be captured for every transaction?

Accepted Answer

Audit Logs with Unique User IDs. Detailed audit logs including a timestamp, user ID, and the executed command provide the necessary evidence for non-repudiation.

Answer

Server Uptime

Answer

Data Compression

Answer

Database Versioning

Question 6

Which action represents a 'Data Lifecycle' phase where data is safely removed?

Accepted Answer

Data Disposition. Data Disposition is the data lifecycle phase in which data is securely removed, archived, or destroyed once it reaches the end of its useful life or retention period. It is the formal term for the safe deletion stage of the lifecycle.

Answer

Data Profiling

Answer

Data Integration

Answer

Data Ingestion

Question 7

When joining two tables in SQL where you need to return all records from the left table even if there is no match in the right table, which JOIN type is appropriate?

Accepted Answer

LEFT JOIN. A LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side. This exactly matches the requirement to return all records from the left table even if there is no match in the right table. Therefore, LEFT JOIN is correct.

Answer

CROSS JOIN

Answer

INNER JOIN

Answer

FULL OUTER JOIN

Question 8

A database administrator is deploying a production environment and needs to restrict network access to the database server. Which tool or setting is the first line of defense at the network layer?

Accepted Answer

Firewall access control lists. Firewall access control lists (ACLs) operate at the network layer and are the first line of defense for restricting which hosts or subnets can reach the database server's listening port. They filter traffic before it reaches the database engine, providing the outermost boundary of protection.

Answer

SSL/TLS certificates

Answer

Database user permissions

Answer

Database proxy authentication

Question 9

Which of the following is an example of an unstructured data source that a data analyst might need to ingest?

Accepted Answer

An email message body. An email message body is unstructured because it has no predefined schema — the text, formatting, and embedded content vary freely and must be parsed with NLP or regex rather than mapped to columns. This is the classic example of unstructured data that analysts ingest for sentiment analysis, eDiscovery, or customer-support mining.

Answer

An XML file with defined tags

Answer

A relational SQL table

Answer

A CSV file with fixed columns

Question 10

An analyst joins a large fact table to a small lookup table using an inner join and notices that the result contains more rows than the original fact table. After reviewing the data, the analyst discovers that the lookup table contains duplicate values in its join key. Which action should the analyst take to ensure each fact row is matched to at most one lookup row?

Accepted Answer

Deduplicate the lookup table by selecting one row per join key before performing the join.. Row multiplication in a join occurs when the join key is not unique on one side. Making the lookup table unique per join key guarantees a one-to-one or many-to-one relationship, so each fact row matches at most one lookup row. Deduplicating the lookup before joining is the targeted fix; changing join type, adding DISTINCT, or relocating predicates does not address the duplicate-key root cause.

Answer

Move the join condition from the ON clause into the WHERE clause.

Answer

Change the join to a LEFT OUTER JOIN so unmatched fact rows are preserved.

Answer

Add a DISTINCT clause to the SELECT statement to remove duplicate result rows.

Question 11

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?

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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