CDS Performance Optimization 2

Question 1

Which indexing strategy is most effective for queries that frequently filter on multiple columns together?

Accepted Answer

Composite index ordered by query filter columns

Answer

Composite indexes ordered to match query filter columns allow the database to satisfy multi-column WHERE clauses without additional lookups.

Question 2

A data warehouse query performs slowly due to data skew. What technique best addresses this?

Accepted Answer

Repartitioning data using a more evenly distributed key

Answer

Data skew causes uneven partition load; repartitioning with a better distribution key balances workload across nodes.

Question 3

What is the primary purpose of materialized views in performance optimization?

Accepted Answer

To store precomputed query results for faster retrieval

Answer

Materialized views persist precomputed results, eliminating the need to re-execute expensive aggregations on every query.

Question 4

Which metric best indicates that a database system is experiencing I/O bottlenecks?

Accepted Answer

Elevated disk read/write latency and queue depth

Answer

High disk latency and long I/O queue depths are the primary signals of an I/O bottleneck limiting query performance.

Question 5

In columnar storage, why are queries that aggregate a single column faster than in row-based storage?

Accepted Answer

Only the relevant column's data is read from disk, reducing I/O

Answer

Columnar storage reads only the columns needed by a query, drastically reducing disk I/O compared to reading entire rows.

(CDS) Certified Data Steward Practice Test