Optimizing Query Performance Flashcards
6 cards from real Data Engineering practice questions. Tap to flip, then mark Knew It or Still Learning — missed cards come back until you master them.
Read the first 6 Optimizing Query Performance flashcards as text
A data engineering team manages a massive historical sales data table in a cloud data warehouse. The table is several terabytes in size, and most analytical queries filter data by `transaction_date` to look at specific months or years. Queries that scan the entire table are becoming extremely slow and costly. Which optimization technique would provide the most significant improvement in performance and cost for these date-based queries?
Answer: Partitioning the table by the `transaction_date` column.
Partitioning physically organizes the data into separate segments based on the values in the `transaction_date` column. When a query filters on this partition key, the query engine performs 'partition pruning,' meaning it only reads the data in the relevant partitions, drastically reducing the amount of data scanned, which lowers cost and improves speed.
In a distributed query engine like Apache Spark, you are joining a very large fact table (billions of rows) with a small dimension table (a few hundred rows). Which join strategy is the most efficient and should be used by the optimizer in this scenario?
Answer: Broadcast Hash Join
A Broadcast Hash Join is ideal when one table is significantly smaller than the other. The small table is duplicated (broadcast) to every worker node. The join can then be performed locally on each node without a costly network shuffle of the large table's data, making it highly efficient for this use case.
A data engineer is troubleshooting a slow-running SQL query. Which of the following is the primary purpose of analyzing the query's execution plan?
Answer: To understand the sequence of operations (e.g., scans, joins, aggregations) the database will perform to execute the query.
The execution plan is a detailed map of the steps the database's query optimizer chooses to execute a query. By examining it, an engineer can identify bottlenecks, such as full table scans instead of index seeks, inefficient join types, or incorrect statistics leading to bad cardinality estimates.
An analytics team frequently runs queries that aggregate metrics across a few specific columns (e.g., `SUM(revenue)`, `AVG(quantity)`) from a wide table with over 200 columns. The current storage format is row-based (like Avro). Queries are slow because they read a lot of unnecessary data. To improve performance for these specific analytical queries, which change would be most impactful?
Answer: Switching the storage format to a columnar format like Parquet or ORC.
Columnar formats like Parquet or ORC store data by column instead of by row. When a query only needs to access a few columns, the query engine can read just the data for those specific columns, dramatically reducing I/O and improving performance for analytical workloads.
What is the primary benefit of 'predicate pushdown' in a modern data warehouse or query engine?
Answer: It filters data as early as possible in the query execution, often at the storage layer, to reduce the amount of data processed in later stages.
Predicate pushdown is an optimization where the filtering conditions (predicates) in a `WHERE` clause are applied as close to the data source as possible. This minimizes the amount of data that needs to be read from disk, transferred over the network, and processed by subsequent steps like joins or aggregations.
A company's main dashboard runs the same complex, resource-intensive query every 5 minutes to calculate key performance indicators (KPIs) over the last 24 hours. This constant re-computation is straining the data warehouse and increasing costs. The data freshness requirement is that the dashboard can be up to 15 minutes out of date. Which solution offers the best balance of reducing load, lowering cost, and meeting the freshness requirement?
Answer: Creating a materialized view that is refreshed every 15 minutes.
A materialized view pre-computes and stores the result of a query. Instead of running the complex query every 5 minutes, the dashboard can query the much smaller, pre-aggregated materialized view. Refreshing it every 15 minutes meets the data freshness requirement while dramatically reducing the computational load and cost on the warehouse.