Performance Optimization on Databricks/AWS: Liquid Clustering, File Layout, Skew and Warehouse Tuning

Slow lakehouse queries are almost never "Spark is slow". They are a specific layer misbehaving: too many small files on S3, a filter column whose min/max ranges overlap in every file, a single hot join key carrying 38% of the rows, or a warehouse that is the right size and the wrong count. This lesson teaches the stack in the order you should fix it, layout → statistics → planning/runtime → serving, and it teaches the 2026 version of that stack, not the 2019 one.

That distinction matters more than any single knob. Databricks' current documentation is blunt: "Databricks recommends using liquid clustering instead of partitions, ZORDER, or other data layout approaches" (docs .../tables/operations/optimize, last updated 2026-03-31, mirror 2026-09-11), and "Manual tuning recommendations don't apply to Unity Catalog managed tables, which use automatic file size tuning" (docs .../tables/tune-file-size, 2026-09-11). Meanwhile the engine layer keeps self-tuning: AQE has been on by default since DBR 7.3, Photon is the default execution engine in Databricks SQL warehouses, and Databricks reports that "in 2025, Databricks SQL delivered up to 40% faster performance across production workloads, with improvements applied automatically" (blog, 2026-01-28).

So the honest framing for October 2026 is: manual tuning is most valuable on external tables, legacy hive-metastore workloads and non-default clusters, and on understanding which layer is broken before you touch anything. Every fact in this lesson comes from the Databricks AWS documentation pages on optimize, file size, partitions, clustering, data skipping, AQE, hints, predictive optimization, caching, warehouse behavior and the query profile (all accessed or dated October 2026), plus dated Databricks engineering blogs. No DBU rates appear here, cost is lesson 15's subject.

In this lesson you will:

• walk the four-layer stack and learn which symptom belongs to which layer before touching a single setting;
• size files against the three official targets, 128 MB auto-optimize, the 256 MB → 1 GB autotune band, and the 1 GiB manual OPTIMIZE ceiling, and hand-compute the file counts;
• apply the partitioning ladder (< 1 TB never · 1 to 100 TB clustering · ≥ 100 TB maybe) and the ≥ 1 GB per partition guard, then watch the high-cardinality anti-pattern fail arithmetic;
• go deep on liquid clustering: CLUSTER BY, CLUSTER BY AUTO, ≤ 4 keys, clustering-on-write thresholds, incremental OPTIMIZE vs OPTIMIZE FULL, hierarchical keys, bin-packing idempotence and the DBR floors with their dates;
• run a worked OPTIMIZE vs CLUSTER BY comparison, the same 8 TB table, the legacy partition+ZORDER SQL beside the liquid SQL, and read the before/after latency plot;
• diagnose join skew from real profile evidence and climb the documented ladder: broadcast hint → AQE skew split → salting, with the wrong-fixes rail beside it;
• configure AQE with its verified defaults (200 shuffle partitions, 30 MB runtime broadcast, 64 MB advisory, 5× skew factor);
• judge Photon by workload, where it wins, where Python UDFs defeat it, and size a Databricks SQL warehouse with the resize-vs-scale doctrine plus the cache tiers;
• profile like an operator: the query profile for warehouses, the Spark UI for clusters, and a shuffle-partition calculation you can do on paper;
• study three named AWS customer performance stories, read the 2026 update box, and practise with twelve exam-style questions plus four interactive checks.

---

1. The four-layer stack, and how to pick a layer

Every query latency in this course is decided in four layers, in this order. Fix them top-down and measure again after each change: never two changes at once, or you cannot attribute the win.

Two 2026 realities reshape that table. First, layers 3 and 4 increasingly self-tune: AQE is on by default (spark.databricks.optimizer.adaptive.enabled = true), Photon runs by default in Databricks SQL warehouses, and managed tables get automatic file-size tuning. Second, layer 2 has no ceiling on managed tables: for Unity Catalog external tables, statistics are collected on the first 32 columns; for UC managed tables, "predictive optimization collects file-skipping statistics on the columns most frequently used in your query filters, instead of a fixed set of columns, so these tables don't have a 32-column limit" (docs .../tables/data-skipping, 2026-06-18).

The rest of this lesson is that stack, layer by layer.

---

2. Layer 1: file sizing and the small-file problem on S3

Small files hurt because each file costs an S3 LIST/GET round-trip, a Spark task, and a Parquet footer parse. As Databricks' own guide puts it, part of the cost is simply "the time spent for just opening and closing those tiny files" (discover guide, 2026-05-06). On S3 there is no block layer: the file is the unit of parallelism, the unit of statistics, and the unit VACUUM reclaims.

2.1 Three official targets, one healthy band

Three different numbers are all "official", and teaching them as one number is the classic stale-tutorial error:

Two corollaries that prevent the two most common layout mistakes:

1. 1 GiB is a ceiling, not a goal for small tables. Compacting a 300 GB table into 1 GiB files leaves ~300 files and destroys pruned-scan parallelism.
2. Raising a target never merges anything by itself. "When the target file size for a table grows, existing files are not re-optimized into larger files by the OPTIMIZE command" (tune-file-size, 2026-09-11); you must run OPTIMIZE afterwards.

2.2 Worked example E1: what 8 TB of Auto Loader output looks like

The lesson of E1 is not the 15 to 25× band; it is that file count and file shape are different numbers. A 440 MB average with a 20 MB median means the engine still pays task-per-file overhead on most of the table.

2.3 Three mechanisms fight small files, in increasing order of authority

1. Optimized writes (autoOptimize.optimizeWrite / spark.databricks.delta.optimizeWrite.enabled) shuffle before writing so each partition emits ~128 MB files. They are on by default for MERGE, UPDATE/DELETE with subqueries, CTAS/INSERT on SQL warehouses, and "In Databricks Runtime 13.3 LTS and above, all tables registered in Unity Catalog have optimized writes enabled for CTAS statements and INSERT operations for partitioned tables". The cost is real: "Writing fewer large files is more efficient … but you might still see an increase in write latency because data is shuffled before being written."
2. Auto compaction (autoOptimize.autoCompact) "runs synchronously on the cluster that performs the write, after the write succeeds, and only compacts files that haven't been compacted previously". In table history it appears as an OPTIMIZE operation with operationParameters.auto = true. For MERGE, UPDATE and DELETE it is mandatory: "You can't turn off this functionality."
3. Scheduled or predictive OPTIMIZE: "For tables larger than 1 TB, Databricks recommends running OPTIMIZE on a schedule", classically "daily, preferably at night when spot prices are low" and on a separate job cluster, and for hot liquid-clustered tables, "scheduling an OPTIMIZE job every one or two hours" (clustering docs, 2026-09-25), because clustering is incremental and most runs are quick.

Auto compaction and optimized writes "are not a full replacement for OPTIMIZE" (tune-file-size, 2026-09-11), and if predictive optimization is enabled, "Databricks recommends disabling any scheduled OPTIMIZE jobs" so you stop paying twice (clustering docs).

⚠️ WARNING, do not fight the write path with your hands. "If you use optimized writes, Databricks recommends that you don't run coalesce(n) or repartition(n) just before a write to control the number of files written" (tune-file-size, 2026-09-11), you pay two shuffles for one job. Equally: never turn auto compaction off at the session level and forget. It cannot be disabled for MERGE/UPDATE/DELETE, but a session-level false does silently disable it for appends and CTAS in that workload.

• 📚 Did you know? OPTIMIZE is safe by construction: "Bin-packing optimization is idempotent, meaning that if it is run twice on the same dataset, the second run has no effect", and "Because OPTIMIZE makes no data changes to the table, a read before and after an OPTIMIZE has the same results. Performing OPTIMIZE on a table that is a streaming source does not affect any current or future streams with this table as a source" (docs.../operations/optimize, 2026-03-31). ZORDER, by contrast, was never idempotent; it was incremental and needed re-running.

---

3. Layer 1, partitioning: the ladder and the ≥ 1 GB guard

The current partitioning guidance is newer than most tutorials and blunter than most teams expect: "Most tables on Databricks with less than 100 TB of data don't need partitioning" (docs .../tables/partitions, 2026-07-01).

The second guardrail is per-partition: "verify that each partition contains at least 1 GB of data. Tables with fewer, larger partitions tend to outperform tables with many smaller partitions" (same page). Partition count equals directory count in your bucket, so every extra level of cardinality multiplies LIST calls, _delta_log entries, OPTIMIZE planning scans and MERGE conflict surface.

Do not partition when: the table is under 1 TB; the filter column is high cardinality (user_id, event_id: cluster instead); access patterns change (partitioning "is static and manifests itself as a filesystem hierarchy. There is no easy way to change partitions", best-practices page); the column is a timestamp that would explode into millions of instants (cluster the raw timestamp instead); or you would partition on a struct field (PARTITIONED BY treats a struct as an expression, not a column reference).

3.1 Worked example E2: the high-cardinality anti-pattern, in arithmetic

Partitioning keeps exactly two legitimate roles in 2026: partition-level lifecycle moves (DELETE/DROP PARTITION on a date column at ≥ 100 TB scale) and Delta Sharing partition filtering on shared tables; which is also why converting a partitioned shared table to clustering is blocked.

---

4. Liquid clustering, deep

"Liquid clustering is a data layout optimization technique that replaces table partitioning and ZORDER" (docs .../tables/clustering, last updated 2026-09-25), and "you can redefine clustering keys without rewriting existing data". That last clause is the whole design: partitioning bakes a filesystem hierarchy you cannot cheaply change; clustering stores a declared key set in table metadata and lets OPTIMIZE physically co-locate ranges incrementally.

4.1 The declaration surface and the DBR floors

Carry these version numbers with their dates; they are the numbers exams and architecture reviews ask for:

4.2 Clustering on write: how streaming lands pre-clustered

You do not have to wait for a nightly job. When a write is large enough, clustering runs during the write, per key count (UC managed / other Delta tables):

This is why a CTAS or a streaming micro-batch can land physically clustered without any scheduled maintenance, and why OPTIMIZE on a clustered table is usually fast: it only rewrites what still needs clustering.

4.3 The lifecycle, including the two expensive moments

Two moments in that diagram cost real money and real calendar time:

⚠️ WARNING: declaring keys is free; making old data match them is not. "The default behavior does not apply clustering to previously written data"; you must run OPTIMIZE <table> FULL, which "might take hours" on a large unclustered table (clustering docs, 2026-09-25). Budget it as a one-off migration job on its own cluster (compute-optimized instances, spot at night), not as a step in a release pipeline. And never claim "enabling clustering rewrote my table": ALTER TABLE … CLUSTER BY is metadata; OPTIMIZE FULL is the rewrite.

⚠️ IMPORTANT: OPTIMIZE rewrites files, so it feeds VACUUM. Every recluster deletes-and-adds files; the old versions survive only for delta.deletedFileRetentionDuration (default 7 days). Set that property before you enable predictive optimization: "If you set delta.deletedFileRetentionDuration to less than 7 days, predictive optimization still retains data files for a minimum of 7 days when it runs VACUUM FULL" (predictive-optimization docs, 2026-09-11), and never run VACUUM in the same job step as long-running readers or streams.

4.4 Worked example E3: adopting clustering on a live table

Why a predicate in step 4/5? OPTIMIZE <table> WHERE <predicate> scopes the compaction/recluster to a subset: cheaper, and it shrinks the conflict window with concurrent writers, the same reason best practices push pruning predicates into MERGE... ON.

• 📚 Did you know? DESCRIBE HISTORY is also your maintenance audit trail: auto compaction, liquid clustering and Z-ordering "all appear in table history as OPTIMIZE operations", and you tell them apart only through operationParameters.{auto, clusterBy, zOrderBy, predicate} (docs.../tables/history, 2026-09-11). A dashboard that counts "OPTIMIZE runs" therefore over-counts auto compaction by a wide margin: filter on auto = 'false' for manual runs.

---

5. Worked example E4: OPTIMIZE vs CLUSTER BY, side by side

Same table, same 8 TB, two generations of layout doctrine. This is the comparison the course exists to make.

What changed between the two paths:

Read that plot as a causal ladder: optimized writes and bin packing (layer 1, file size) buy you a solid but bounded gain because pruning still cannot prove anything; CLUSTER BY (layer 1 + layer 2 together) is what makes the customer_id filter exclude >95% of files. The final bar is labelled illustrative because it is a reference-architecture sketch constrained by Databricks' published 2 to 12× read band, not a number from your table: measure yours in the query profile.

5.1 Layer 2: why clustering is what makes skipping work

Delta writes per-file statistics at commit time: "minimum and maximum values, null counts, and total records" (data-skipping docs, 2026-06-18), and at query time the engine skips any file whose stats prove the predicate cannot match. Clustering does not change what can be skipped, "the engine prunes at file granularity. There is no directory-level shortcut to lose" (Databricks blog, 2026-01-06), it changes how much is skipped, by making ranges of the key physically contiguous.

Skipping silently fails when: the predicate is non-sargable (WHERE lower(col) = …, WHERE date_add(col, 1) = …); the filter column has no statistics; values are uniformly distributed across all files; or you combine columns in a way min/max cannot prove. Stats knobs: delta.dataSkippingNumIndexedCols (positional) and delta.dataSkippingStatsColumns (DBR 13.3 LTS+, explicit list, supersedes the numeric one), and "Updating these properties does not automatically recompute statistics for existing data". Finally: "Databricks recommends that you don't use ZORDER BY on columns that don't have statistics collected because it is ineffective and uses unnecessary compute resources."

---

6. Layer 3, joins: AQE, broadcast, skew and salting

6.1 The AQE defaults you are actually running with

AQE is on by default and does four things: convert sort-merge join into broadcast at runtime, coalesce post-shuffle partitions, split (and if needed replicate) skewed partitions, and propagate empty relations (AQE docs, page dated 2023-10-12, still current in Oct 2026).

Rule of thumb for shuffle sizing: "each task should approximately be processing 128MB to 200MB of data" (discover guide, 2026-05-06). And keep AQE on: "It is recommended to keep AQE enabled" (best-practices, 2026-09-11).

6.2 Worked example E5: a 52-minute nightly join, diagnosed

The wrong-fixes rail: all three of these appear in production code and none of them helps:

• SET spark.sql.shuffle.partitions = 2000: splits nothing; the hot key still lands in exactly one partition.
• .repartition("cust_id"): same failure, plus an extra shuffle.
• A skew hint when AQE already fired: overhead for a fix that already happened; "Skew join optimization has some overhead so it is better to use it only when needed" (retired skew-join docs, page updated 2024-05-13).

• 📚 Did you know? You can read AQE's decisions straight out of the physical plan: CustomShuffleReader with property Coalesced means partitions were merged after shuffle, SortMergeJoin with isSkew=true means AQE split a hot partition, and a LocalTableScan means AQE proved a side of the join empty and short-circuited it (AQE docs, 2023-10-12). Three strings worth grepping in any slow query's plan before you change a single config.

---

7. Photon: judge it by workload, not by slogan

Photon is the vectorized C++ engine that processes data in columnar batches, and "Photon is used by default in Databricks SQL warehouses" (best-practices, 2026-09-11). The published evidence, with its caveats attached:

The per-workload verdicts:

• SQL-heavy / BI / dashboards: biggest wins (vectorized scans, joins, aggregations). Already on by default in SQL warehouses.
• ETL / batch Spark: solid but smaller; MERGE-heavy paths are dominated instead by low shuffle merge (GA since DBR 10.4 LTS, "up to 5x … average on the order of 2-3x").
• Python-heavy code with UDFs: Photon cannot accelerate Python UDF execution: "Serialization is required to transfer data between Python and Spark. This significantly slows down queries" (best-practices). The winning move is rewrite-to-native-SQL (or pandas UDFs if a UDF is unavoidable) first, Photon second. (This is reasoning from the documented UDF guidance, not a published Python benchmark.)
• ML training loops built on RDDs/graph/custom code: Photon is mostly irrelevant there.

---

8. Layer 4: warehouse sizing and the cache tiers

8.1 Resize vs scale out: two symptoms, two knobs

Databricks' sizing doctrine (warehouse-behavior docs, accessed 2026-10; high-concurrency blog, 2025-02-09): resize when queries need more memory per query (aggregations, multi-table joins: watch disk spill); scale out when queries wait (watch queuing). "If queries are spilling to disk, increase the cluster size. Check for spills in the query profile." Classic and pro warehouses have "a fixed limit of one cluster per 10 concurrent queries", the queue depth is hard-capped at 1,000 queries for every warehouse type, and a practical alert threshold is peak queued queries > 10 that does not resolve when autoscaling fires.

8.2 Worked example E6: sizing a 60-analyst warehouse

For most workloads "Databricks recommends using a serverless SQL warehouse", and its sizing advice is counter-intuitive but correct: "Start with a single larger warehouse and let serverless features manage concurrency and performance. It is usually more efficient to size down if necessary than to start small and scale up."

8.3 Four caches, four lifetimes

Rules that prevent the classic cache bugs: result caching needs deterministic queries, a NOW()-style predicate never hits it; SET use_cached_result = false bypasses it (testing/benchmarking only); disk caching "works by default with all instance types that have local SSDs", and on classic compute that means choosing SSD-bearing workers; "In SQL warehouses and Databricks Runtime 14.2 and above, the CACHE SELECT command is ignored", the enhanced disk cache is used instead.

⚠️ WARNING, Spark caching is the trap, not the default. "using incorrect locations in a query can consume all available memory and significantly slow down queries. As a rule of thumb, avoid Spark caching" (best-practices, 2026-09-11), and the Delta best-practices page says flatly "Do not use Spark caching with Delta Lake" (updated 2026-09-11). Cached DataFrames eat executor memory that shuffles and joins then spill to disk, the telltale signature is spill rising while the Storage tab shows large cached blocks. Legitimate uses are narrow: a truly immutable, repeatedly-scanned non-Delta dataset inside one session, always paired with unpersist(); plus prewarming a cold cluster before the morning rush.

• 📚 Did you know? The disk cache is the renamed Delta cache: "The disk cache (formerly known as 'Delta cache') stores copies of remote data on the local disks (for example, SSD) of the virtual machines… The recommended (and easiest) way to use disk caching is to choose a worker type with SSD volumes" (best-practices, 2026-09-11). And because autoscaling decommissions workers, "if autoscaling is enabled, there is some instability with the cache": cache-sensitive job clusters want conservative scale-down or pools.

---

9. Profiling: the query profile and the Spark UI

Profiling is what makes the stack a loop instead of a checklist. Pick the tool by compute type: query profile for SQL warehouses and serverless, Spark UI for classic and job clusters.

What each signal tells you, and the single documented response:

Two access details that save a support ticket: profile access requires ownership of the query "or at least CAN MONITOR permission on the SQL warehouse", and "A query profile is not available for queries that run from the query cache. To circumvent the query cache, make a trivial change to the query, such as changing or removing the LIMIT."

9.1 Worked example E7: sizing spark.sql.shuffle.partitions on paper

One streaming caveat: spark.sql.shuffle.partitions "cannot be changed between query restarts from the same checkpoint location" (AQE docs): pick it before the first start of a streaming query.

• 📚 Did you know? A query served from the result cache has no query profile at all: "Query profile is not available" in the UI usually means your query was fast because it was cached, not that profiling is broken. During benchmarks, SET use_cached_result = false (docs.../query-caching, accessed 2026-10), and keep NOW()-style non-determinism out of BI queries you want cached in production.

---

10. The automation layer: predictive optimization and materialized views

Two 2026 features remove work from your runbook, but only if you know what they replace.

Predictive optimization "automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables (Delta Lake and Iceberg)", is "enabled by default for accounts created on or after November 11, 2024" with the gradual rollout of existing accounts "expected to complete by August 2026", runs on serverless compute billed as a serverless jobs SKU, and, the gotcha, "OPTIMIZE does not run ZORDER when executed by predictive optimization. On tables that use Z-order, predictive optimization ignores Z-ordered files." It applies to managed tables only; external tables keep you on the manual ladder in sections 2 to 4. Observe it with DESCRIBE TABLE EXTENDED … AS JSON → predictive_optimization_evaluations, the Catalog Explorer History "Automatic runs" filter, or system.storage.predictive_optimization_operations_history.

Materialized views are the serving-layer counterpart: "a view where precomputed results are available for query and can be updated to reflect changes in the input. All materialized views are backed by an ETL pipeline", created "using a Pro or Serverless SQL warehouse, or within a pipeline", refreshed by serverless Lakeflow pipelines, with the cached output materialized in cloud storage. Eligibility for incremental refresh is checkable before you commit: EXPLAIN CREATE MATERIALIZED VIEW … (DBR 17.3+) reports "The Materialized View can be incrementally refreshed" or not: a filter/projection/aggregate over Delta qualifies, LIMIT and non-Delta sources (INPUT_NOT_IN_DELTA) do not, and an expectation inside the definition forces a full refresh. Both materialized views and streaming tables accept liquid clustering: "Liquid clustering applies to both streaming tables and materialized views."

---

11. The traps: sixteen confusions this lesson will not let you keep

1. "Target file size is 1 GiB." Manual OPTIMIZE caps at 1 GiB; auto-optimize targets 128 MB; autotune picks 256 MB under 2.56 TB. Compacting a 300 GB table to 1 GiB files hurts pruned-scan parallelism.
2. Assuming managed tables need manual OPTIMIZE rituals. Autotune + predictive optimization own them; a nightly cron job double-pays.
3. Partitioning a 50 GB table "for performance." Under 1 TB you should not partition at all, and every partition needs ≥ 1 GB.
4. ZORDER on a column without statistics, or expecting ZORDERed tables to stay maintained under predictive optimization ("ignores Z-ordered files").
5. Changing clustering keys and expecting instant results. You need OPTIMIZE FULL, and it "might take hours" on a large unclustered table.
6. Too many / too-high-cardinality clustering keys. Max 4; below 10 TB extra keys degrade single-column filters; hierarchical keys should be low-cardinality.
7. VACUUM ↔ clustering interplay. Every recluster deletes-and-adds; set delta.deletedFileRetentionDuration before enabling predictive optimization (7-day floor), and never vacuum alongside long-running readers.
8. coalesce(n)/repartition(n) right before a write while optimized writes are on: two shuffles, one job.
9. A session-level autoOptimize.autoCompact = false you forgot about: mandatory for MERGE/UPDATE/DELETE, silently off for your appends.
10. spark.sql.shuffle.partitions = 2000 "because the default is too low." The rule is 128 to 200 MB per task; with AQE prefer auto or start high and let coalescing merge.
11. Reaching for a skew hint when AQE already handles it, or salting before trying broadcast. If one side fits in 10/30 MB, a BROADCAST hint deletes the shuffle: strictly better than splitting it.
12. Spark cache abuse. "As a rule of thumb, avoid Spark caching"; "Do not use Spark caching with Delta Lake".
13. Result-cache confusion during debugging. A cached query has no profile; dashboard latency may be cache latency.
14. Reading skew from wall-clock alone. The test is Max > 1.5× p75 in Summary Metrics, not "the job felt slow".
15. Assuming Photon fixes Python UDF pipelines. Serialization dominates; rewrite to native SQL first.
16. Running OPTIMIZE … ZORDER BY on twelve columns, mid-day, on the interactive cluster. Legacy technique, wrong time, wrong cluster: use clustering, a separate spot job cluster, and a schedule.

---

Comparative Verdict: traditional partitioning + ZORDER vs liquid clustering (AWS, as of Oct 2026)

• Traditional partitioning + ZORDER: the 2019 to 2023 playbook, now legacy. You declare a Hive-style directory hierarchy (PARTITIONED BY (event_date)) for pruning, then periodically OPTIMIZE … ZORDER BY (hot_col) to co-locate a non-partitioned column across files. It works, and at ≥ 100 TB with a genuinely low-cardinality date key, partitioning can still earn its keep for partition-level DELETE/DROP and Delta Sharing partition filtering. The costs: the hierarchy is "static … no easy way to change partitions"; ZORDER is a rewrite that "is not idempotent but is incremental" and is broken up by MERGE (low shuffle merge blunts but does not remove this); ZORDER needs statistics or it is "ineffective and a waste of resources"; high-cardinality columns cannot be partition keys at all; and: the operational nail: predictive optimization never runs ZORDER and "ignores Z-ordered files", so legacy ZORDERed tables silently stop being maintained.
• Liquid clustering: the documented default for all new tables. CLUSTER BY (up to 4 keys, statistics required, protocol 3/7) is metadata-only to declare or change; clustering-on-write lands CTAS and streaming traffic pre-clustered at 64 MB/256 MB/512 MB/1 GB thresholds (managed); OPTIMIZE then does clustering and bin packing together, incrementally, idempotently, on a 1 to 2 hour cadence for hot tables; CLUSTER BY AUTO lets the platform pick keys cost-aware; and predictive optimization maintains it for you. Evidence: 2.5× faster clustering than Z-order on a 1 TB warehouse workload with Hive partitioning "an order of magnitude slower" (blog, 2023-06-29), point queries up to 12× and reads 2 to 12× at GA (blog, 2024-05-22). The costs: a one-off OPTIMIZE FULL when keys are first set or changed (hours on a large table), incompatibility with existing partitioning/ZORDER, and a 4-key ceiling.
• Verdict in one line: new table → CLUSTER BY (or CLUSTER BY AUTO on a managed table) with predictive optimization on; existing partitioned/ZORDERed table → migrate with ALTER TABLE … REPLACE PARTITIONED BY WITH CLUSTER BY (18.1+) plus one OPTIMIZE FULL, keeping partitioning only where a ≥ 100 TB table genuinely needs partition-level lifecycle moves or partition-filtered sharing. Every version number in that sentence is dated in section 4.1; carry the dates, not just the numbers.

---

2026 Updates (as of October 2026)

---

Real-World Case Studies

📋 Case A: Comcast (communications & media, Cloud: AWS) Challenge. Petabytes of telemetry and billions of voice interactions from 20M+ voice remotes, with fragile pipelines, small-file ingestion pain, and hundreds of models to deploy across cloud, on-prem and edge. Stack on AWS. Databricks, Delta Lake for ingest/enrich/optimize small files, managed MLflow, autoscaling + spot instances, notebooks, Tableau downstream: Cloud: AWS on the story page. Reported outcome. "10x Reduction in overall compute costs to process data · 90% Reduction in required DevOps resources"; "Delta Lake has enabled Comcast to optimize data ingestion, replacing 640 machines with 64 while improving performance"; model deployment "weeks to minutes" (https://www.databricks.com/customers/comcast, undated page, accessed 2026-10). Comcast is also named among customers "already leveraging Databricks on AWS" in Databricks' AWS Marketplace press release (2022-05-10), and its RDK telemetry is described as "petabytes of data, collected every 15 minutes from 70 million devices" (databricks.com/blog, 2021-05-10). Maps to this lesson. Sections 2 and 8: the headline is a structural win: Delta's file handling collapsed 640 machines to 64 before any query tuning, which is exactly the layer-1-first ordering this lesson teaches, plus spot instances for the compaction window. ⏳ Temporal note: story origin 2019, undated page accessed Oct 2026: a reported customer outcome, not a 2026 guarantee.

📋 Case B: Rivian (electric vehicles, Cloud: AWS) Challenge. More than 70,000 vehicles on the road "generating terabytes of data every day", with legacy tooling that "struggled to scale pipelines cost-effectively", feeding predictive-maintenance and charge-prediction ML. Stack on AWS. Databricks SQL, Delta Lake, Unity Catalog, and, explicitly, "Liquid Clustering for data layout" alongside "Unity Catalog for data governance, Delta Tables for storage" for real-time charge-prediction ML (databricks.com/blog, 2025-07-09); Cloud: AWS on the story page. Reported outcome. "50% better runtime performance" (https://www.databricks.com/your-ai/customers/rivian, 2024-05-30); "1,000+ platform users, a 75% increase from a year ago" (https://www.databricks.com/customers/rivian, undated, accessed 2026-10). Maps to this lesson. Sections 4 and 5: Rivian is the named AWS example of the exact lever this lesson's worked example E4 exercises: vehicle telemetry is high-cardinality, fast-growing and queried by filter columns, which is the documented best-fit list for clustering ("Queries that filter on high cardinality columns", "Fast growing tables", "Tables with varied or changing access patterns"). ⏳ Temporal note: the 50% figure is from 2024-05-30; the liquid-clustering stack mention is from 2025-07-09.

📋 Case C: Punchh (retail loyalty / restaurant tech, Cloud: AWS) Challenge. Outgrown AWS MySQL, a warehouse too costly to keep, and Tableau reporting slowed by joins executed fresh for every report: "When you're joining 12 tables on the fly every time a report is run by a customer, the cost to generate the report can be high." Stack on AWS. Databricks SQL, Delta Lake bronze/silver/gold, notebooks, Tableau on top: Cloud: AWS on the story page. Reported outcome. "10x Faster time-to-insight · 30% Lower operational costs · 12% Increase in customer satisfaction" (https://www.databricks.com/customers/punchh, undated page, accessed 2026-10; story origin 2021). Maps to this lesson. Sections 8 and 10: the cheapest join is the one you do not run: a pre-joined gold table (or a materialized view with incremental refresh) beats both a 12-way join at report time and any warehouse resize that would have brute-forced it. ⏳ Temporal note: 2021-origin story still displayed in 2026: teach the mechanism, date the metrics.

---

---

Practice Questions