Understand the Optimization Techniques Used by Databricks to Ensure the Performance of Queries on Large Datasets (data Skipping, File Pruning, Etc.). DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 3)

Questions 11–15

1. 11application · medium

   A Delta table stores user event logs. Queries frequently filter on a high-cardinality user_id column, but the data is not clustered by user_id, and Z-ordering has not helped enough because user_id values are scattered across many files. Which feature can further improve file skipping for equality filters on user_id?

   • APartition by user_id
   • BIncrease the number of files
   • CCreate a Bloom filter index on user_id
   • DUse a global sort on user_id
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A logistics company stores shipment records in a Delta table. Queries always filter on shipment_date (a date column) and often filter on destination_city (a low-cardinality column with about 200 distinct values). The table is currently unpartitioned. Which approach best optimizes query performance?

   • AZ-order by shipment_date and partition by destination_city
   • BZ-order by shipment_date only
   • CPartition by destination_city only
   • DPartition by shipment_date and Z-order by destination_city
   Reveal Solution Discussion Select an answer first
4. 13application · medium

   A Delta table is partitioned by year and month. A query filters on a non-partitioned column, product_id, and the query plan shows that all files in the relevant partitions are being scanned. The table has up-to-date statistics. What is the most likely reason data skipping is not reducing the scan?

   • AThe product_id values are spread across all files, so min/max statistics do not exclude any file
   • BThe query uses a LIKE operator
   • CStatistics are not collected for partitioned columns
   • DThe table is too large for data skipping
   Reveal Solution Discussion Select an answer first
5. 14application · medium

   A company stores web server logs in a Delta table. Queries always filter on log_date (a date column) and sometimes on user_agent (a high-cardinality string column). The table is currently unpartitioned and has poor performance. Which combination of optimizations is most effective?

   • AZ-order by log_date only
   • BPartition by log_date and Z-order by user_agent
   • CZ-order by log_date and partition by user_agent
   • DPartition by user_agent only
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   What is the primary purpose of data skipping in Databricks?

   • ATo compress data files to reduce storage costs
   • BTo reduce the amount of data scanned by avoiding files that do not match query predicates
   • CTo reorder rows within files to improve compression ratios
   • DTo cache frequently accessed data in memory for faster retrieval
   Reveal Solution Discussion Select an answer first