Study guide
8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY)
Microsoft Certified: Azure Databricks Data Engineer Associate DP-750
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Azure Databricks Architecture & Compute Selection
1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute
2 Chapter 2: Compute Configuration, Performance & Cost Management
2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions
3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup
3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown
4 Chapter 4: Unity Catalog Privilege Model & Access Control
4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation
5 Chapter 5: Advanced Data Security, Masking & ABAC
5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks
6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing
6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions
7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming
7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory
8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance
8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg
9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture
9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment
10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables
10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs
11 Chapter 11: Workflow Orchestration with Lakeflow Jobs
11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling
12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles
12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines
13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning
13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics
8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY)
Key Takeaways Explain This Topic AI
The 'small file problem' is caused by high-frequency streaming micro-batches and excessive partitioning, resulting in severe I/O degradation and storage metadata listing bottlenecks.
OPTIMIZE bin-packs small Parquet files into uniform, query-optimized target files (~1GB by default) without modifying table data or blocking concurrent readers.
ZORDER BY constructs space-filling curves across 1 to 4 high-cardinality columns, colocating related records within the same Parquet files to maximize data skipping efficiency.
Liquid Clustering ( CLUSTER BY ) replaces legacy Hive partitioning and Z-Ordering: it clusters new data incrementally during writes instead of rewriting whole partitions on arrival, and clustering keys can be redefined later with ALTER TABLE .
Deletion vectors turn DELETE, UPDATE, and MERGE into merge-on-read soft deletes by marking changed rows in metadata instead of rewriting whole Parquet files; enabling them upgrades the table protocol and locks out clients that cannot read deletion vectors.
Last updated: August 2026
8.3 Compaction ( OPTIMIZE ), Z-Ordering, & Liquid Clustering ( CLUSTER BY )
In big data architectures, the physical layout of files on cloud object storage dictates query performance and execution costs. As data pipelines ingest streaming micro-batches, append frequent batch deltas, or execute updates, Delta tables naturally accumulate millions of tiny Parquet files. This phenomenon—the small file problem —degrades query throughput by overwhelming the Spark driver with filesystem metadata listings and triggering inefficient storage I/O.
Azure Databricks provides three powerful physical optimization mechanisms: File Compaction ( OPTIMIZE ) , Multi-Dimensional Clustering ( ZORDER BY ) , and next-generation Liquid Clustering ( CLUSTER BY ) .
1. The Small File Problem & Storage Inefficiencies +-----------------------------------------------------------------------------------+
- | THE SMALL FILE PROBLEM |
- | |
- | 10,000 Files x 1 MB (Unoptimized) 10 Files x 1 GB (OPTIMIZED) |
- | - 10,000 HTTP GET metadata requests - 10 HTTP GET requests |
- | - Severe Spark driver listing overhead - Vectorized sequential Parquet read |
- | - Poor compression ratios - Maximum compression & throughput |
- | - High IOPS costs - Sub-second query planning |
- +-----------------------------------------------------------------------------------+
Root Causes of Small Files:
Streaming Ingestion : Near-real-time streaming pipelines writing micro-batches every few seconds generate thousands of small files per day.
Over-Partitioning : Partitioning tables by high-cardinality columns (e.g., timestamp, user ID) creates millions of subdirectories containing tiny files.
High Parallelism Writes : Spark jobs configured with hundreds of shuffle partitions ( spark.sql.shuffle.partitions = 200 ) writing modest datasets generate 200 tiny files per commit.
2. File Compaction via the OPTIMIZE Command
The OPTIMIZE command executes a bin-packing algorithm to coalesce collections of small Parquet data files into larger, uniform files (targeting ~1GB by default, or auto-tuned between 128MB and 1GB based on table volume). -- Compact all small files across the entire Delta table
OPTIMIZE sales_silver;
- - Target compaction to specific partitions to save compute resources
- OPTIMIZE sales_silver
- WHERE order_date >= '2026-08-01' AND order_date <= '2026-08-26';
- Operational Mechanics of OPTIMIZE
Identifies Candidates : The Spark driver scans the table metadata to find contiguous groups of Parquet files smaller than the target size (e.g., < 128MB).
Rewrites into Optimized Files : Spark worker tasks read the small files and rewrite them into coalesced ~1GB Parquet files.
Atomic Transaction : The driver writes an atomic commit log recording remove actions for the old small files and add actions for the new coalesced files.
Non-Blocking Execution : Readers querying the table during OPTIMIZE continue reading the old files without interruption due to snapshot isolation.
Auto-Compaction and Optimized Writes
Databricks provides automated settings to minimize small file generation during active writes:
Optimized Writes : Coalesces data in memory across executors prior to writing, reducing the number of files generated per partition ( SET spark.databricks.delta.optimizeWrite.enabled = true ).
Auto-Compaction : Automatically executes a lightweight compaction job immediately after an append write finishes ( SET spark.databricks.delta.autoCompact.enabled = true ).
3. Multi-Dimensional Data Skipping with ZORDER BY
While OPTIMIZE solves file size problems, queries filtering on non-partitioned columns must still scan every file. Z-Ordering is a technique that colocates related information along a multidimensional space-filling curve (the Morton Z-curve). -- Run OPTIMIZE with Z-Ordering along high-cardinality query filter columns
- OPTIMIZE customer_orders
- ZORDER BY (customer_id, order_date); Z-ORDER SPACE-FILLING CURVE (2D)
- order_date
- ^
- | (0,3)---(1,3) (2,3)---(3,3)
- | | / | | / |
- | (0,2) (1,2)---(2,2) (3,2)
- | | / |
- | (0,1)---(1,1) (2,1)---(3,1)
- | | / | | / |
- | (0,0) (1,0)---(2,0) (3,0)
- +---------------------------------> customer_id
- How Z-Ordering Improves Data Skipping
Z-ordering maps multi-dimensional column values into a 1D scalar value while preserving spatial locality.
Data rows with similar customer_id and order_date values are grouped together inside the same physical Parquet files .
The resulting minValues and maxValues ranges in the _delta_log become extremely tight and narrow. When a query filters by customer_id = 4500 , Spark skips 95%+ of the files because their min/max boundaries do not overlap the filter value.
Z-Order Rules of Thumb & Limitations
Column Count : Limit Z-Order to 1 to 4 columns . Adding more columns dilutes the clustering effectiveness along each individual dimension.
Cardinality : Z-order is most effective on high-cardinality columns used frequently in WHERE clauses and JOIN conditions (e.g., user_id , device_id , transaction_timestamp ).
Maintenance Overhead : Z-Ordering is not incremental . When new data is appended to a partition, running OPTIMIZE ... ZORDER BY must read and rewrite all data in that partition from scratch, consuming significant compute.
4. Next-Generation Liquid Clustering ( CLUSTER BY )
Liquid Clustering is the modern Databricks replacement for both traditional Hive-style directory partitioning and Z-Ordering. It provides flexible, fully incremental, and dynamically redefinable multi-dimensional data clustering. -- Create a new Delta table with Liquid Clustering
- CREATE TABLE events_liquid (
- event_id STRING,
- event_type STRING,
- user_id BIGINT,
- event_timestamp TIMESTAMP,
- payload STRING
- )
- USING DELTA
- CLUSTER BY (event_type, event_timestamp); -- Trigger incremental clustering on newly appended data
- OPTIMIZE events_liquid;
Architectural Advantages of Liquid Clustering +-------------------------------------------------------------------------+
- | LIQUID CLUSTERING ARCHITECTURAL BENEFITS |
- +-------------------------------------------------------------------------+
- | 1. INCREMENTAL CLUSTERING |
- | - Clusters only new, unclustered files without rewriting the table |
- | - Reduces OPTIMIZE execution time and cloud compute cost by 70%+ |
- | |
- | 2. ELIMINATES HIVE PARTITIONING PITFALLS |
- | - Avoids over-partitioning, small-file sprawl, and data skew |
- | - Works seamlessly on both low-cardinality and high-cardinality keys|
- | |
- | 3. DYNAMIC CLUSTERING KEY EVOLUTION |
- | - Change clustering keys on the fly without rewriting history |
- | - ALTER TABLE events_liquid CLUSTER BY (user_id, event_timestamp); |
- | |
- | 4. CONCURRENCY RESILIENCE |
- | - Concurrent writes target clustered ranges with minimal conflicts |
- +-------------------------------------------------------------------------+
- 5. Deletion Vectors: Merge-on-Read for Row-Level Changes
Clustering decides where rows live. Deletion vectors decide what happens when rows are removed or changed, and the DP-750 blueprint lists them alongside liquid clustering and Z-ordering as part of the clustering strategy bullet.
Without deletion vectors, modifying a single row forces Delta Lake to rewrite the entire Parquet file containing that record - the copy-on-write penalty. Deletion vectors instead mark the affected rows as changed in metadata, and readers apply those entries at query time to resolve the current table state. This accelerates DELETE , UPDATE , and MERGE on both Delta Lake and Apache Iceberg tables.
- Enabling Deletion Vectors -- Delta Lake
- CREATE TABLE prod_retail.silver.orders (order_id BIGINT, status STRING)
- TBLPROPERTIES ('delta.enableDeletionVectors' = true);
- ALTER TABLE prod_retail.silver.orders
- SET TBLPROPERTIES ('delta.enableDeletionVectors' = true);
- - Iceberg tables use their own property
- ALTER TABLE prod_retail.silver.orders_iceberg
- SET TBLPROPERTIES ('iceberg.enableDeletionVectors' = true);
Workspace settings can auto-enable deletion vectors on new tables created with a SQL warehouse or Databricks Runtime 14.3 LTS and above, and defaults vary by region. All Apache Iceberg v3 tables include deletion vectors by default , whereas Delta Lake tables must have them enabled explicitly.
You cannot ALTER a materialized view or a streaming table to add or remove deletion vectors - that has to be decided in the CREATE TABLE statement. And once deletion vectors are enabled on a materialized view or streaming table, the table protocol cannot be downgraded even if you later turn them off.
Runtime and Protocol Consequences
- Concern Detail
- Reading DV tables Databricks Runtime 12.2 LTS and above
- Writing with all optimizations Databricks Runtime 14.3 LTS and above
- Row-level concurrency on DV tables Databricks Runtime 14.2 and above
- Without Photon DELETE from DBR 12.2 LTS, UPDATE from DBR 14.1, MERGE from DBR 14.3 LTS
Protocol upgrade Enabling deletion vectors upgrades the table protocol ; clients without deletion vector support can no longer read the table
The protocol upgrade is the operational trap: enable deletion vectors on a table that an external Iceberg v2 reader or an old OSS Delta client consumes, and that consumer breaks. In Databricks Runtime 14.1 and above you can drop the deletion vectors table feature to restore compatibility.
Turning Soft Deletes into Physical Deletes
Deletion vectors are soft deletes - the rows are still inside the Parquet files. To physically rewrite those files, do one of:
- Run OPTIMIZE on the table
- Run REORG TABLE ... APPLY (PURGE) , which rewrites every data file containing deletion vector changes
- Trigger a write with auto-compaction, which rewrites files carrying a deletion vector
File compaction gives no strict guarantee that every deletion vector change is applied, because some target files may not be compaction candidates. For a GDPR or CCPA hard deletion , the two-step sequence is mandatory: -- 1. Physically rewrite files that carry soft-deleted rows
REORG TABLE prod_retail.silver.customers APPLY (PURGE);
- - 2. Remove the pre-purge file versions (see Section 8.4 for the retention guardrail)
- VACUUM prod_retail.silver.customers;
On very large tables, set spark.databricks.delta.reorg.purgeMode to rows so the purge only touches files that actually contain soft-deleted rows. The default value all also scans every Parquet footer for dropped column data, which is slow on wide tables.
Predictive I/O connection. On Photon-enabled compute, Databricks uses deletion vectors to power predictive I/O for updates, which is why MERGE -heavy silver tables see the largest gains.
6. Architectural Comparison Matrix
Capability / Property Hive Partitioning ( PARTITIONED BY ) Z-Ordering ( ZORDER BY ) Liquid Clustering ( CLUSTER BY )
Physical Storage Layout Rigid physical subdirectories ( /year=2026/month=08/ ) Flat directory; files ordered by space-filling curve Flat directory; files indexed by dynamic clustering keys
High-Cardinality Support Poor (causes small file explosion and metadata bloat) Excellent (colocates rows within Parquet files) Excellent (handles low, medium, and high cardinality)
Incremental Clustering Yes (writes directly to target partition directory) No (must rewrite entire partition during OPTIMIZE) Yes (incrementally clusters only new files)
Key Redefinition Disaster (requires full CTAS table rewrite) Requires re-running Z-Order command Instant ( ALTER TABLE ... CLUSTER BY (...) )
Data Skew Resilience Poor (large partitions create single-file bottlenecks) Moderate High (dynamically balances file sizes to ~1GB)
Best Use Case Legacy migrations with low cardinality (< 1000 partitions) Tables on older DBR runtimes with fixed filter keys Modern Lakehouse standard for all production Delta tables