DS0-001 Practice Test 6 — 10 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