DS0-001 Practice Questions 2 — 50 Questions

Question 1

A database is configured with a high-availability cluster. During a planned maintenance window, you need to fail over to the secondary node manually. Which command or action is used in a Windows Server Failover Cluster (WSFC) environment?

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

Accepted Answer

Move-ClusterGroup. In a WSFC environment, the 'Move' command in the Failover Cluster Manager is used to manually move the role (including the database resource) to another node.

Answer

SHUTDOWN WITH NOWAIT

Answer

ALTER SERVER ROLE

Answer

RESTORE DATABASE

Question 2

Which database feature is used to automatically maintain data integrity by ensuring that a value in a child table exists as a primary key in a parent table?

Accepted Answer

Foreign key constraint. A foreign key constraint enforces referential integrity by requiring that values in a child table's foreign key column match existing primary key (or unique key) values in the parent table. This automatically prevents orphaned records and maintains data consistency across related tables. It is the standard mechanism for parent-child integrity.

Answer

Check constraint

Answer

Unique constraint

Answer

Default constraint

Question 3

In the context of database deployment, what is the primary purpose of a staging environment?

Accepted Answer

To test the deployment and migration process. A staging environment is designed to mirror the production environment as closely as possible, allowing teams to test the deployment and migration process before it goes live. This includes validating scripts, configurations, and data migrations to catch issues that might not appear in development. The primary purpose is to ensure a smooth transition to production by rehearsing the actual deployment steps.

Answer

To develop new features in isolation

Answer

To handle production workload during maintenance

Answer

To perform unit testing on code

Question 4

Which NoSQL structure is best suited for scenarios where data relationships are complex and frequently traversed?

Accepted Answer

Graph. Graph databases store data as nodes and edges, where edges explicitly represent relationships between entities. This native adjacency structure allows traversals across connected data to be performed in near-constant time per hop, making them ideal when relationships are complex and frequently queried. Key-value, column-family, and document stores lack first-class relationship modeling, so multi-hop traversals require expensive joins or application-side processing.

Answer

Key-Value

Answer

Column-family

Answer

Document

Question 5

A database administrator is planning the deployment of a new PostgreSQL 15 cluster that will support a high-volume web application. The application's workload is expected to be read-heavy, with a read-to-write ratio of approximately 10:1. The primary server has 64 GB of RAM, and the database size is estimated at 500 GB. To improve read performance and provide high availability, the administrator plans to implement streaming replication. Which configuration should the administrator implement to best meet these requirements?

Accepted Answer

Configure asynchronous streaming replication with one or more hot standby servers and use a load balancer to distribute read queries to the standbys.. For a read-heavy PostgreSQL workload requiring high availability, asynchronous streaming replication with hot standbys and read load balancing is optimal. It minimizes write latency because the primary does not wait for standby acknowledgment, while hot standbys serve read-only queries, distributing the load. This configuration also supports failover. Synchronous replication would add write latency without benefit, logical replication is not suited for high-throughput read scaling, and cascading with synchronous standbys still incurs write overhead.

Answer

Configure cascading streaming replication with a single synchronous standby and multiple asynchronous standbys.

Answer

Configure synchronous streaming replication with multiple hot standby servers and enable hot_standby_feedback on the standbys.

Answer

Configure logical replication with multiple subscribers and use a connection pooler to route read queries to the subscribers.

Question 6

A data analyst runs a query against a PostgreSQL 15 data warehouse to calculate month-over-month revenue growth. The query uses the LAG window function partitioned by region and ordered by month. The analyst notices that the first month for each region returns a NULL growth value, which is causing downstream dashboard errors. Which action should the analyst take to handle this NULL value appropriately?

Accepted Answer

Wrap the LAG expression with COALESCE to substitute a default value such as 0 for the first month's growth.. The LAG window function returns NULL when there is no preceding row within the partition, which occurs for the first month of each region. Wrapping the expression with COALESCE replaces that NULL with a defined default, such as 0, so downstream calculations and dashboards do not fail. This preserves all rows and maintains correct growth calculations for subsequent months.

Answer

Change the ORDER BY clause in the window function to sort by revenue instead of month.

Answer

Replace the LAG function with a self-join on the same table to retrieve the previous month's revenue.

Answer

Add a WHERE clause that filters out rows where the LAG result IS NULL before the dashboard consumes the data.

Question 7

A data governance team is implementing an audit trail for a data warehouse. The team must ensure that every query against sensitive tables is recorded with the user identity, timestamp, and SQL text, and that the logs cannot be altered by database users. Which approach best meets these requirements?

Accepted Answer

Enable native database auditing and ship logs to a centralized, append-only log management system with restricted write access.. To capture every query with user identity and SQL text while ensuring tamper resistance, enable native database auditing and forward records to an append-only centralized log system. Local audit tables and triggers can be altered by privileged users, and encryption does not provide an audit trail.

Answer

Configure the database to write audit records to a local table and grant SELECT to auditors.

Answer

Use triggers on sensitive tables to insert rows into an audit table within the same schema.

Answer

Enable Transparent Data Encryption on the database and rely on encryption as the audit control.

Question 8

A company is using a cloud-based data lake. To satisfy data sovereignty requirements, they must ensure data remains in a specific region. How can this be enforced?

Accepted Answer

Regional Resource Policies. Regional Resource Policies (also called region restrictions, location constraints, or data residency policies) are the cloud-native mechanism that constrains where resources can be created and where data can be stored or replicated. They enforce data sovereignty by preventing resource deployment outside approved geographic regions.

Answer

Database Load Balancing

Answer

Database Indexing

Answer

Data Compression

Question 9

You are managing a database that uses transparent data encryption. If you need to back up the database, what additional component must be included to ensure the backup can be restored on a different server?

Accepted Answer

The certificate and its private key. Transparent Data Encryption (TDE) encrypts the database using a Database Encryption Key (DEK), which is itself protected by a certificate stored in the master database. To restore a TDE-encrypted backup on a different server, you must supply that certificate and its private key, otherwise the DEK cannot be decrypted and the restore fails.

Answer

The master data file

Answer

The SQL Server configuration file

Answer

The transaction log file

Question 10

A database administrator is investigating performance degradation during heavy concurrent write operations. Which isolation level provides the highest level of data consistency but the lowest concurrency?

Accepted Answer

Serializable. Serializable is the highest isolation level, ensuring complete isolation from other transactions. It prevents dirty reads, non-repeatable reads, and phantom reads, but at the cost of lowest concurrency due to increased locking and potential blocking.

Answer

Read Committed

Answer

Repeatable Read

Answer

Read Uncommitted

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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.