DS0-001 Practice Test 13 — 15 Questions

Question 1

A data analyst is building a monthly customer churn report. The churn definition is: a customer is churned in month M if they had at least one transaction in month M-3 but zero transactions in months M-2, M-1, and M. Which SQL approach correctly identifies churned customers for each month without missing customers who simply had no activity at all?

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

Accepted Answer

Build a calendar spine of months, LEFT JOIN customers and their transactions to it, then use conditional aggregation to detect the required activity pattern.. Churn here is defined by presence in one month and absence in the following three, which requires representing months with no activity. A calendar spine joined to customers ensures every month is present, and LEFT JOIN to transactions keeps zero-activity months visible. Conditional aggregation then tests the exact pattern, avoiding the false negatives that inner joins and existence checks would create.

Answer

Use the LAG window function on transaction dates and filter where the lag is greater than 90 days.

Answer

Use an INNER JOIN between the transactions table and itself on customer_id and month offsets, filtering for rows where the offset months are NULL.

Answer

Use a correlated subquery with NOT EXISTS to find customers whose most recent transaction is older than three months.

Question 2

A database administrator is configuring a new SQL Server instance for a high-volume online transaction processing (OLTP) system. The administrator needs to optimize tempdb performance. Which two actions should the administrator take? (Choose two.)

Accepted Answer

Enable trace flag 1118 to use uniform extents for tempdb allocations.. For high-volume OLTP, tempdb performance is improved by creating multiple data files (one per core up to eight) to reduce allocation contention, and by ensuring uniform extent allocations via trace flag 1118 (or relying on the default in newer versions). These actions address the most common tempdb bottlenecks. Changing the recovery model, co-locating log and data files, or using small percentage growth would harm performance or are invalid.

Answer

Set the tempdb filegrowth to a small percentage to save disk space.

Answer

Set the tempdb recovery model to FULL.

Answer

Place the tempdb transaction log on the same volume as the data files.

Question 3

Which TWO of the following are common database maintenance tasks?

Accepted Answer

Updating statistics. Updating statistics and rebuilding indexes are critical maintenance tasks to ensure optimal query performance.

Answer

Rebooting the server weekly

Answer

Renaming every table

Answer

Deleting all users

Question 4

A data analyst is querying a large partitioned table in Apache Hive that stores clickstream events. The table is partitioned by event_date and bucketed by user_id. The analyst needs to retrieve the latest event for each user within the last 30 days. The query currently uses a subquery with MAX(event_timestamp) and a join back to the table, but it runs slowly. Which optimization should the analyst apply to improve performance while preserving the correct result?

Accepted Answer

Replace the subquery and join with a window function using ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp DESC) and filter on row number = 1.. A window function with ROW_NUMBER() partitioned by user_id and ordered by event_timestamp descending directly identifies the latest event per user. Filtering on row number = 1 avoids the extra join and subquery, reducing data shuffling and improving performance. This approach also correctly handles ties by returning only one row per user, which the original subquery-join might not guarantee.

Answer

Add a DISTRIBUTE BY user_id and SORT BY event_timestamp DESC clause before the subquery.

Answer

Enable vectorization and set hive.optimize.bucketmapjoin to true, then rerun the original query.

Answer

Use a LEFT SEMI JOIN between the table and a subquery that selects user_id and MAX(event_timestamp).

Question 5

A data engineer is configuring an S3 bucket to comply with GDPR requirements regarding the 'Right to be Forgotten.' Which combination of configuration settings best automates the lifecycle of user-specific data objects that have reached the end of their retention period?

Accepted Answer

S3 Lifecycle configuration with expiration rules. S3 Lifecycle configuration with expiration rules is the correct answer because it allows automated deletion of objects after a defined retention period, directly supporting GDPR's 'Right to be Forgotten' by ensuring user-specific data is removed without manual intervention. Lifecycle rules can be scoped by prefix or tag to target only the relevant user data, and expiration actions permanently delete objects, satisfying the regulatory requirement for timely erasure.

Answer

S3 Object Lock with legal hold

Answer

Cross-Region Replication (CRR)

Answer

Bucket policies with IAM conditions

Answer

S3 Inventory reports

Question 6

Which TWO of the following are key considerations when configuring a database for high availability?

Accepted Answer

Network latency between nodes. Network latency between nodes (C) is a key high-availability consideration because replication, clustering heartbeats, and failover coordination depend on timely inter-node communication; high latency can cause replication lag, split-brain scenarios, or delayed failover. Storage subsystem performance (E) is also critical because HA designs rely on fast, reliable shared or replicated storage (e.g., SAN, RAID, synchronous replication) to meet RPO/RTO targets and avoid I/O bottlenecks during failover. The color of the server chassis (A) is purely cosmetic and has no bearing on availability. The number of database users (B) affects load and licensing but is not a defining HA configuration factor. The database name (D) is just an identifier and does not influence availability mechanisms.

Answer

The color of the server chassis

Answer

Number of database users

Answer

The database name

Question 7

Which THREE of the following are types of data joins in SQL?

Accepted Answer

INNER JOIN. INNER JOIN (A) is a core SQL join type that returns only rows where the join condition matches in both tables, making it one of the standard join categories. LEFT JOIN (C) is also a standard SQL join that returns all rows from the left table plus matching rows from the right table, filling unmatched right-side columns with NULL. RIGHT JOIN (D) is the mirror of LEFT JOIN, returning all rows from the right table plus matches from the left, and is likewise a recognized SQL join type. The other options are not valid SQL joins: UPPER JOIN (B) is not a SQL keyword (UPPER is a string function), and FAST JOIN (E) is not a defined join type in standard SQL or major RDBMS implementations.

Answer

UPPER JOIN

Answer

FAST JOIN

Question 8

When planning database capacity, you calculate the projected growth of a table based on transaction volume and row size. What does 'database bloat' refer to in this context?

Accepted Answer

The accumulation of unused space in data pages. Database bloat refers to the accumulation of unused or dead space inside data pages — space that was allocated for rows which have since been deleted or updated but not reclaimed. In MVCC engines like PostgreSQL and InnoDB, UPDATEs create new row versions and DELETE marks rows dead, leaving holes in pages that inflate the physical file size without adding usable data. This directly affects capacity planning because the on-disk footprint grows faster than the logical row count.

Answer

The rapid growth of transaction logs

Answer

The degradation of query performance over time

Answer

The increase in the number of concurrent connections

Question 9

In Power BI, which visualization component is best suited to show the relationship between two numerical variables and identify potential trends or clusters?

Accepted Answer

Scatter Plot. A scatter plot plots each data point on a two-dimensional plane using two numerical axes, making it the ideal visualization to reveal correlation, trends, clusters, and outliers between two numerical variables. Power BI's scatter chart also supports a category/legend field and a size field for bubble charts, extending its analytical power.

Answer

Gauge Chart

Answer

Pie Chart

Answer

Clustered Column Chart

Question 10

Which TWO of the following are potential issues when denormalizing a database?

Accepted Answer

Data anomalies. Denormalization intentionally duplicates data across tables to reduce joins, and this redundancy creates data anomalies (Option A): if a duplicated value is updated in one place but not another, the database can end up with inconsistent or conflicting records, violating update/insert/delete consistency. Denormalization also increases storage usage (Option E) because the same data is stored in multiple tables or columns, and additional indexes or summary columns may be added, consuming more disk space than a normalized design. The unmarked options do not belong because simplified data entry (B) and improved schema flexibility (C) are generally benefits rather than issues, and slower join performance (D) is incorrect since denormalization typically reduces the number of joins and can improve read performance.

Answer

Simplified data entry

Answer

Improved schema flexibility

Answer

Slower join performance

Question 11

A database developer is implementing a partitioning strategy for a large table containing historical financial records. The requirement is to allow for efficient archival of data older than five years. Which partitioning strategy should be chosen?

Accepted Answer

Range partitioning. Range partitioning divides data by contiguous value ranges of a column — for historical financial records, partitioning by date range (e.g., yearly or monthly) places each period's rows in its own partition. Archiving data older than five years then becomes a simple partition switch or drop, which is a metadata operation rather than a row-by-row DELETE. This makes range partitioning the natural fit for time-based archival.

Answer

Composite partitioning

Answer

List partitioning

Answer

Hash partitioning

Answer

Round-robin partitioning

Question 12

Which access control model should be implemented to ensure that a user can only access files based on their specific job role?

Accepted Answer

Role-Based Access Control (RBAC). RBAC assigns permissions to roles (e.g., 'HR Analyst', 'Finance Approver') rather than to individual users, and users inherit permissions by being assigned to a role. This directly satisfies the requirement that access be driven by job role, and it scales cleanly when employees change positions — you swap roles rather than re-editing per-file ACLs.

Answer

Discretionary Access Control (DAC)

Answer

Mandatory Access Control (MAC)

Answer

Network Access Control (NAC)

Question 13

When working with time-series data, what does a 'lag' feature represent in an analysis?

Accepted Answer

The value of a variable from a previous period. In time-series analysis, a lag feature is a copy of a variable's value shifted back by one or more time periods, so the model can use past observations to predict current or future values. For example, lag-1 of daily sales is yesterday's sales value aligned to today's row. This is fundamental to autoregressive models and feature engineering for forecasting.

Answer

The time taken to query the data

Answer

The difference between two time stamps

Answer

The sampling rate of the sensors

Question 14

When cleaning a dataset, you notice that column names contain inconsistent casing (e.g., 'Name', 'NAME', 'name'). Which transformation step should be prioritized to facilitate join operations?

Accepted Answer

String Normalization. Inconsistent casing in column names (e.g., 'Name', 'NAME', 'name') is a string-formatting issue that must be resolved before joins, because most databases treat identifiers as case-sensitive (or case-insensitive depending on collation) and mismatched casing causes join failures or duplicate columns. String Normalization standardises casing, whitespace, and formatting across the dataset.

Answer

One-hot encoding

Answer

Data Binning

Answer

Data deduplication

Question 15

Which TWO technologies or methods are most effective at protecting sensitive data against insider threats?

Accepted Answer

Deploying Database DLP tools. Option A, deploying Database DLP (Data Loss Prevention) tools, is correct because DLP solutions monitor, detect, and block unauthorized access, exfiltration, or misuse of sensitive data by insiders, providing content-aware inspection and policy enforcement at the database layer. Option C, implementing separation of duties, is correct because it divides critical tasks and privileges among multiple users so that no single insider has enough access to compromise sensitive data alone, thereby reducing the risk of fraud or accidental leakage. Option B, using a custom UI theme, is incorrect because a theme only changes the visual appearance of an application and has no security control function. Option D, increasing server RAM, is incorrect because adding memory improves performance, not data protection against insider threats. Option E, enabling database mirroring, is incorrect because mirroring provides high availability and failover, not access control or data-loss prevention.

Answer

Using a custom UI theme

Answer

Increasing server RAM

Answer

Enabling database mirroring