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 6)

Questions 26–30

1. 26application · medium

   A retail company stores daily sales transactions in a Delta table partitioned by date. Analysts frequently run queries filtering on customer_id, which has millions of distinct values. The table has grown to thousands of small files per partition, and query performance has degraded. Which optimization should you apply to improve data skipping for the customer_id filter?

   • ARun ANALYZE TABLE COMPUTE STATISTICS
   • BPartition by customer_id
   • CZ-order by customer_id
   • DEnable Auto Optimize only
   Reveal Solution Discussion Select an answer first
2. 27foundation · easy

   At which levels does Databricks perform file pruning to reduce the amount of data scanned?

   • ADirectory level based on partition values and file level based on column statistics
   • BOnly at the file level using column statistics
   • COnly at the directory level using partition values
   • DAt the row level using min/max statistics
   Reveal Solution Discussion Select an answer first
4. 28foundation · easy

   Which component of the Delta Lake transaction log is primarily used by Databricks to determine which files can be skipped during query execution?

   • ACommit metadata containing the list of added files and their statistics
   • BThe table schema definition stored in the Hive metastore
   • CThe checkpoint file that contains the full state of the table at a point in time
   • DThe Delta log's version history that tracks all previous commits
   Reveal Solution Discussion Select an answer first
5. 29expert · hard

   A Delta table stores sales data. Queries always filter on sale_date (a date column) and often filter on store_id (a low-cardinality column with about 100 distinct values). The table is currently unpartitioned and has 10 TB of data. You need to optimize for both filter patterns. Which approach is most effective?

   • APartition by sale_date and Z-order by store_id
   • BZ-order by both sale_date and store_id
   • CPartition by store_id only
   • DZ-order by sale_date and partition by store_id
   Reveal Solution Discussion Select an answer first
6. 30foundation · easy

   What is a key difference between partitioning and Z-ordering?

   • APartitioning is applied automatically, while Z-ordering requires manual commands
   • BPartitioning stores statistics in the transaction log, while Z-ordering does not
   • CPartitioning creates separate directories, while Z-ordering reorganizes data within files
   • DPartitioning improves performance on high-cardinality columns, while Z-ordering is for low-cardinality
   Reveal Solution Discussion Select an answer first