DS0-001 Timed Hard Questions — 25 Difficult Questions

Question 1

A data architect is designing a schema for a system that must store highly variable product attributes across thousands of categories, where each category has a different set of attributes and the schema must evolve frequently without downtime. Which database structure is best suited for this requirement?

15 minute time limit — choose an answer to begin.

Accepted Answer

A document-oriented NoSQL store with a flexible schema per product. A document-oriented NoSQL store handles variable attributes per product without schema migrations, directly supporting frequent evolution and avoiding downtime. Fixed-column tables, attribute-per-dimension star schemas, and entity-attribute-value designs either require schema changes or introduce query complexity and performance issues.

Answer

A relational table with a fixed set of columns for all possible attributes

Answer

A normalized relational schema with an entity-attribute-value table

Answer

A star schema with a dimension table per attribute

Question 2

You are debugging a SQL query that uses an aggregate function. You receive an error stating that a column in your SELECT list is not part of an aggregate or a GROUP BY clause. What is the cause?

Accepted Answer

Missing column in GROUP BY. SQL requires that every column in the SELECT list be either aggregated (e.g., SUM, COUNT, AVG) or included in the GROUP BY clause when aggregation is used. The error 'column is not part of an aggregate or GROUP BY' means a non-aggregated column was selected without being listed in GROUP BY, so the engine cannot determine which value to return per group.

Answer

Missing WHERE clause

Answer

Incorrect join type

Answer

Using an alias in WHERE

Question 3

When migrating data between two different relational databases, which DDL-related issue is most likely to cause failure if ignored?

Accepted Answer

Incompatible data types. Incompatible data types are the most likely DDL-related failure point when migrating between different relational database engines (e.g., Oracle to PostgreSQL, SQL Server to MySQL). Each RDBMS implements its own type system with different sizes, precision, and semantics — for example, Oracle's NUMBER vs. SQL Server's DECIMAL, or MySQL's TINYINT(1) vs. PostgreSQL's BOOLEAN — so DDL that creates tables will fail or silently truncate data if types aren't mapped correctly.

Answer

Table naming conventions

Answer

Storage engine choice

Answer

Primary key naming

Question 4

A data warehouse team is deploying a partitioned fact table in SQL Server 2022 that will hold seven years of sales data. Most queries filter on the order date, and the team wants the oldest year moved to cheaper storage while remaining queryable. Which deployment design best meets these goals?

Accepted Answer

Create a partitioned table with a partition function on the order date, map each year to its own filegroup, and place the oldest filegroup on cheaper storage with a read-only filegroup setting.. Native partitioning on the date column provides two things at once: the optimizer eliminates partitions that cannot match a date predicate, and each partition lives in its own filegroup so it can be placed on a different storage tier. Marking the oldest filegroup read-only prevents accidental modification while keeping the data available to the same queries.

Answer

Create seven separate tables, one per year, and build a UNION ALL view across them for reporting queries.

Answer

Enable table compression on the fact table and archive the oldest year to a separate database that is restored on demand.

Answer

Create a single unpartitioned table with a clustered columnstore index and rely on column elimination to skip the oldest year.

Question 5

Which THREE of the following are components of a standard SQL SELECT statement?

Accepted Answer

WHERE. The WHERE clause (C) is a core component of a standard SQL SELECT statement because it filters the rows returned by the query according to a specified predicate. The FROM clause (D) is also essential, as it specifies the table or tables from which the data is retrieved. The GROUP BY clause (E) is a standard SELECT component that aggregates rows sharing common values into groups, typically used with aggregate functions such as COUNT, SUM, or AVG. By contrast, DROP (A) is a DDL statement used to delete database objects like tables or views, and GRANT (B) is a DCL statement used to assign privileges to users, so neither belongs inside a SELECT statement.

Answer

DROP

Answer

GRANT

Question 6

A company is designing a distributed NoSQL database deployment using MongoDB. They need to ensure high availability and automatic failover in the event that a primary node becomes unreachable. What must the architect configure to achieve this?

Accepted Answer

Deploy a MongoDB Replica Set with at least three nodes.. MongoDB achieves high availability and automatic failover through replica sets, which require a minimum of three nodes to maintain a majority for elections. Deploying a replica set with at least three nodes (option D) ensures that if the primary becomes unreachable, the remaining nodes can elect a new primary automatically, providing both redundancy and failover. This is the standard MongoDB architecture for HA.

Answer

Implement database mirroring using the primary-secondary synchronous replication setting.

Answer

Increase the write concern to 'all' to force nodes to write simultaneously.

Answer

Configure a sharded cluster with a single mongos instance.

Question 7

A data architect is designing a schema for a system that must store highly variable product attributes across thousands of categories, where each category has a different set of attributes and the schema must evolve frequently without downtime. Which database structure is best suited for this requirement?

15 minute time limit — choose an answer to begin.

Accepted Answer

It reduces the storage size of the index compared to a string-based natural key. A surrogate key (e.g., an auto-incrementing integer or GUID) is typically much smaller than a natural key such as a string-based order number or composite business key. Because the surrogate key is used in indexes and foreign key relationships, the smaller key size reduces index page size, allowing more index entries per page, which improves join performance and reduces I/O. This is the primary advantage in a high-concurrency e-commerce system where Orders is joined frequently.

Answer

It prevents the insertion of duplicate records

Answer

It ensures that the database complies with ACID requirements

Answer

It automatically creates a clustered index on the table

Question 8

A DBA is designing a backup strategy for a large SQL Server database that must support point-in-time recovery and minimize backup windows. The database is in the full recovery model. Which two backup types should the DBA include in the strategy to achieve these goals? (Choose two.)

Accepted Answer

Full backup. To support point-in-time recovery, the full recovery model requires a full backup to establish the baseline and transaction log backups to capture ongoing changes. These two types are essential. Differential backups can help reduce restore time but are not mandatory. Copy-only and filegroup backups serve other purposes and do not by themselves enable point-in-time recovery.

Answer

Filegroup backup

Answer

Copy-only backup

Answer

Differential backup

Question 9

A database is experiencing high I/O wait times on the log drive. What is the most effective configuration change to alleviate this bottleneck?

Accepted Answer

Move the log to a high-performance dedicated drive. The log drive is the write-ahead log (WAL/redo) target, and it is written sequentially and synchronously on every commit. If that drive is slow or shared with data files, commits stall and I/O wait spikes. Moving the log to a dedicated high-performance device (SSD/NVMe, or a dedicated volume) removes contention and gives the sequential log stream the IOPS and low latency it needs.

Answer

Use data compression

Answer

Increase the buffer pool size

Answer

Reduce the database recovery interval

Question 10

Which technology provides the best protection against data exfiltration from an on-premises database by a rogue administrator?

Accepted Answer

Database Activity Monitoring (DAM) with Firewall. A Database Firewall inspects incoming and outgoing SQL traffic to block unauthorized queries or data exports.

Answer

Database Clustering

Answer

Database Archiving

Answer

Database Sharding

Question 11

When using Python's 'Matplotlib' library, which function is used to add a new figure to the canvas?

Accepted Answer

plt.figure(). In Matplotlib, the plt.figure() function is used to create a new figure object, which serves as the canvas for plotting. Calling plt.figure() adds a new figure to the current pyplot state and returns a Figure instance that can be populated with axes and plots. Without calling plt.figure(), plotting functions like plt.plot() will draw on the current figure, or create one implicitly if none exists.

Answer

plt.axis()

Answer

plt.plot()

Answer

plt.show()

Question 12

A cloud data platform team must prove to auditors that only approved personnel accessed a regulated dataset during a specific incident window. The platform uses role-based access control and stores query history in the warehouse. Which evidence source most directly demonstrates authorized access during that window?

Accepted Answer

The warehouse access history and query logs filtered to the dataset and incident window, correlated with role grants.. Auditors need object-level access evidence tied to identities and time. Warehouse access history and query logs filtered to the dataset and incident window, correlated with the role grants in effect, show exactly which principals read the regulated data and whether each read was authorized, which is the most direct and defensible proof.

Answer

The change management tickets approved during the incident window for the platform team.

Answer

The data catalog's lineage graph showing which downstream reports consume the regulated dataset.

Answer

The identity provider's sign-in logs showing which users authenticated and from which IP addresses.

Question 13

A data analyst is reviewing a query that joins three tables: orders, customers, and products. The query returns more rows than expected. Which TWO of the following are likely causes of the row multiplication? (Choose two.)

Accepted Answer

The query includes a many-to-many relationship between orders and products without an intermediate bridge table. Row multiplication in multi-table joins typically stems from join cardinality mismatches. A non-unique key on the dimension side causes one fact row to match multiple dimension rows, and a many-to-many relationship without a bridge table causes combinatorial expansion. Join type changes, NULL filters, and sorting affect inclusion or order but not the number of matched rows, so they do not explain the inflated result set.

Answer

The query filters on a column that contains NULL values in the products table

Answer

The query orders the result by a column with duplicate values

Answer

The query uses an INNER JOIN instead of a LEFT JOIN between orders and customers

Question 14

An organization uses a 'bring your own key' (BYOK) model for database encryption. What is the primary benefit?

Accepted Answer

Increased control over encryption keys. The primary benefit of BYOK is increased control over encryption keys: the organization generates and manages its own keys, which are used by the cloud provider for encryption at rest. This gives the organization greater security oversight, the ability to revoke access, and compliance with data sovereignty requirements.

Answer

Easier database installation

Answer

Improved query performance

Answer

Lower storage costs

Question 15

A database administrator needs to ensure that a query calculating running totals is performant. Which window function clause is essential to order the calculation sequence?

Accepted Answer

ORDER BY. The ORDER BY clause inside the OVER() specification defines the logical ordering of rows for the window function, which is essential for running totals because the cumulative sum must be computed in a defined sequence. Without ORDER BY, the window frame defaults to the entire partition and the running total is meaningless. ORDER BY establishes the row sequence that makes the cumulative calculation deterministic.

Answer

GROUP BY

Answer

PARTITION BY

Answer

ROWS BETWEEN

Question 16

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?

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 17

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 18

A financial services firm stores account transaction records in an Oracle Database. A data governance policy requires that all modifications to the TRANSACTIONS table be traceable to the individual database user who performed them, and that those users be prevented from denying their actions. The security team has enabled unified auditing. Which Oracle Database feature should the team implement to meet these requirements?

Accepted Answer

Unified Auditing with audit policies. The requirement is to trace modifications to individual users and prevent them from denying their actions. Unified Auditing with audit policies captures detailed audit records for specific tables and operations, linking each change to the authenticated database user. This provides the necessary non-repudiation and accountability. Other options address access control, encryption, or centralized monitoring but do not provide the same level of user-specific traceability.

Answer

Oracle Transparent Data Encryption (TDE)

Answer

Oracle Audit Vault and Database Firewall

Answer

Oracle Database Vault

Question 19

A database administrator manages a MySQL 8.0 InnoDB database with a 500 GB table that experiences heavy insert and update activity. The table has a composite primary key on (customer_id, order_date). A nightly batch process runs UPDATE statements that modify the order_status column for millions of rows based on a range of order_date values. The DBA notices that these updates are slow and cause significant locking. The DBA must reduce locking and improve update performance without changing the application's query patterns. Which action should the DBA take?

Accepted Answer

Partition the table by RANGE on order_date and update each partition in separate transactions.. Partitioning by RANGE on order_date enables partition pruning for the nightly batch updates, limiting each transaction to a subset of data and reducing lock contention. This directly targets the slow, lock-heavy range updates while preserving application query patterns. The other options either require massive schema changes, do not address write locking, or provide only partial I/O relief without solving concurrency.

Answer

Change the primary key to a surrogate auto-increment column and create a secondary index on (customer_id, order_date).

Answer

Set the transaction isolation level to READ UNCOMMITTED for the batch process.

Answer

Increase the innodb_buffer_pool_size to cache the entire table in memory.

Question 20

A database architect is setting up an Always On Availability Group in SQL Server. To ensure that the secondary replica can be used for read-only workloads without impacting the primary node's performance, how should the 'Readable Secondary' property be configured?

Accepted Answer

Set to 'Read-intent only' to allow access based on application connection strings.. 'Read-intent only' allows read-only connections to the secondary replica only when the client explicitly specifies ApplicationIntent=ReadOnly in the connection string. This prevents normal read-write connections from accidentally hitting the secondary, ensuring that reporting workloads are deliberately routed there without affecting the primary's performance. It is the recommended setting when the secondary is dedicated to read-only workloads.

Answer

Set the synchronous commit mode to 'Asynchronous' on all nodes.

Answer

Set to 'Yes' and force all writes through the secondary replica.

Answer

Set to 'No' and route traffic via a secondary listener.