databricks-sql-spark-optimization
Diagnose and tune slow or expensive Databricks SQL and Spark workloads using EXPLAIN plans, query profiles, the Spark UI, skew and spill analysis, Photon, Adaptive Query Execution, liquid clustering vs ZORDER and partitioning, SQL warehouse tuning, and the system.query.history and system.billing.usage system tables. Use when a user asks to speed up a Databricks query, investigate a long-running job, reduce DBU spend, fix data skew, tune a SQL warehouse, find costly queries, or compare liquid clustering, ZORDER, and partitioning.
- License 许可
- Apache-2.0
- 最近更新
- 2026-10-27
- Stars
- ★ 0
- Forks
- 0
01 前言 Front Matter
- name
- databricks-sql-spark-optimization
- description
- Diagnose and tune slow or expensive Databricks SQL and Spark workloads using EXPLAIN plans, query profiles, the Spark UI, skew and spill analysis, Photon, Adaptive Query Execution, liquid clustering vs ZORDER and partitioning, SQL warehouse tuning, and the system.query.history and system.billing.usage system tables. Use when a user asks to speed up a Databricks query, investigate a long-running job, reduce DBU spend, fix data skew, tune a SQL warehouse, find costly queries, or compare liquid clustering, ZORDER, and partitioning.
03 SKILL.md 正文
Databricks SQL and Spark Optimization
Outcome
A verified performance diagnosis and an approved, reversible change that measurably reduces duration, cost, or variance of the target workload.
Setup
- Configure the Databricks CLI with a profile that holds no secrets in files the agent writes:
databricks configure --profile <name>with OAuth or a token supplied via theDATABRICKS_TOKENenvironment variable. Never write tokens into prompts, notebooks, logs, or artifacts; referenceDATABRICKS_HOSTandDATABRICKS_TOKENenv vars or a secret store. - Verify versions before relying on a feature:
databricks --version(GA CLI is 1.0.0+; legacy 0.18 and below is unsupported). Check runtime withSELECT current_version()on the target compute or read the cluster's Spark version in the UI. Liquid clustering GA requires DBR 15.2+ (13.3 LTS minimum to read); AQE is enabled by default; Photon is on by default for SQL warehouses and jobs compute. - Confirm permissions: CAN MONITOR on the SQL warehouse to view query profiles; SELECT on
system.query.historyandsystem.billing.usage(account admin by default, or a dynamic view grant); CAN MANAGE on the warehouse or table for any tuning change.
Method
Work in read-only mode until the diagnosis is written down and the user approves a change.
-
Inspect: find the costly or slow statements. Query
system.query.historyfor the worst offenders over a representative window:SELECT statement_id, executed_by, client_application, total_duration_ms, execution_duration_ms, waiting_at_capacity_duration_ms, read_bytes, read_files, pruned_files, spilled_local_bytes, shuffle_read_bytes, error_message, query_source.job_info.job_id FROM system.query.history WHERE start_time >= current_date() - 7 ORDER BY total_task_duration_ms DESC LIMIT 25;Correlate with
system.billing.usage(usage_metadata.warehouse_id / job_id) to rank by DBU cost, not only by duration. -
Inspect: pull the query profile for the chosen
statement_id(Query History UI → See query profile), and check the Top operators panel, rows scanned vs pruned, spill, and Photon coverage (purple = Photon, grey = fallback). For classic clusters, open the Spark UI (Compute → Spark UI) and read the Jobs timeline, the longest stage, and the task distribution for skew (max task duration >> median) and spill. -
Inspect: get the plan with
EXPLAIN FORMATTED <statement>orEXPLAIN COST <statement>. Look forAdaptiveSparkPlannodes,Exchangeshuffles, missing filters pushed down, exploding joins, and full scans. Remember SQL EXPLAIN shows the initial plan only; AQE re-optimizes at runtime, so confirm against the executed profile. -
Plan: state one hypothesis and one change per iteration — rewrite the statement, refresh stats (
ANALYZE TABLE ... COMPUTE STATISTICS), change layout (liquid clustering vs ZORDER/partitioning), or change compute (Photon, warehouse size/scaling, serverless). -
Execute only with explicit user approval. Mutating actions:
ALTER TABLE ... CLUSTER BY/OPTIMIZE ... FULL,ANALYZE TABLE, warehouse resize viadatabricks warehouses edit <id>, or terminating a query. Never run these silently; record the pre-change baseline first. -
Verify: rerun the statement (or wait for the next scheduled run) and compare
execution_duration_ms,read_bytes, and task-time insystem.query.historyagainst the baseline. -
Rollback: revert statement edits to the prior text;
RESTORE TABLE ... TO VERSION AS OFfor layout changes; restore prior warehouse settings with the samedatabricks warehouses editcall.
Layout decision
- Default to liquid clustering for new tables:
CREATE TABLE t (...) CLUSTER BY (col); it replaces ZORDER and Hive-style partitioning and keys can change without a rewrite. Convert existing partitioned tables withALTER TABLE t REPLACE PARTITIONED BY WITH CLUSTER BY. - Keep ZORDER only for legacy tables you cannot convert; keep partitioning only for coarse time boundaries that double as a retention or lifecycle unit.
- Skewed joins: let AQE handle moderate skew (
isSkew=truein the plan); for severe skew, pre-aggregate or salt the hot key rather than tuning shuffle partitions by hand. - Warehouse tuning: right-size cluster size for latency, raise max clusters for concurrency (roughly one cluster per 10 concurrent queries), prefer serverless warehouses, and lower Auto Stop on interactive warehouses.
Documentation
- EXPLAIN syntax: https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-qry-explain
- Spark UI diagnosis guide: https://docs.databricks.com/aws/en/optimizations/spark-ui-guide/
- Adaptive Query Execution: https://docs.databricks.com/aws/en/optimizations/aqe
- Photon: https://docs.databricks.com/aws/en/compute/photon
- Liquid clustering: https://docs.databricks.com/aws/en/delta/clustering
- SQL warehouse settings: https://docs.databricks.com/aws/en/compute/sql-warehouse/create
- Query history and query profile UI: https://docs.databricks.com/aws/en/sql/user/queries/query-history and https://docs.databricks.com/aws/en/sql/user/queries/query-profile
- Query History API: https://docs.databricks.com/api/workspace/queryhistory/list
- system.query.history schema: https://docs.databricks.com/aws/en/admin/system-tables/query-history
- Databricks CLI: https://docs.databricks.com/aws/en/dev-tools/cli
Shared resources
Load only the resources needed for the current task. The links resolve inside the current agent package.
../../references/reliability-and-slo-guide.md../../templates/incident-runbook.md
Troubleshooting
statement_textshows<REDACTED>in system.query.history: expected by default; account admins or thedatabricks_pii_accessgroup see full text. Do not work around this; diagnose via the query profile instead.- No query profile for a statement: results served from cache have no profile; change the statement trivially (e.g., adjust LIMIT) and rerun.
- EXPLAIN plan differs from executed plan: AQE re-optimized at runtime; read the final plan in the query profile, not SQL EXPLAIN.
- Photon fallback: UDFs, RDD/Dataset APIs, or unsupported expressions force fallback to the Spark runtime; check the plan for grey operators.
- system.query.history empty or lagging: records appear within about an hour and only for SQL warehouses or serverless compute; confirm system tables are enabled for the metastore.
- ALTER CLUSTER BY fails: clustering is incompatible with partitioning and ZORDER, and requires DBR 13.3 LTS+ (15.2+ for GA); check runtime and table properties first.
Completion check
The work is done when a named baseline (statement_id, duration, read bytes, cost) and a post-change measurement from system.query.history or the query profile show the improvement, the applied change is recorded, and the rollback path is stated.
04 同仓库更多 Skills
twenty-year-land-plan
Apply the Bren discipline of evaluating every land and development decision on a twenty-year horizon, with a comprehensive plan for how the land, the community, and the surrounding public infrastructure evolve together. Deals optimized for a five-year exit are structurally different from deals that compound across generations; insist on knowing which kind you are making. Use when evaluating land, entitlements, master-planned development, or phasing decisions, and when separating five-year exit deals from generational holds.
thematic-gut-check
Apply the Gray discipline of identifying the structural theme behind every real estate deal, testing where that theme sits in its capital-flow lifecycle, and insisting that deals without a named theme — or deals late in a crowded theme — clear a higher bar. A mediocre deal in a strong theme usually beats a clever deal in a weak one. Use when naming the structural thesis behind a deal, judging whether that theme is early or crowded, and setting the bar the deal must clear.
supply-pipeline-gut-check
Apply the Zell discipline of grounding every real estate thesis in the local supply pipeline. Demand counts over adjectives — units under construction, units permitted, units proposed — and convert them into a forward absorption schedule. A deal that ignores the pipeline is a bet on a market you have not actually studied. Use when validating a market thesis, forecasting absorption and rent growth, or counting units under construction, permitted, and proposed.
ruin-scenario-mapping
Apply the Taleb discipline of identifying, for every real estate deal, the specific chain of events that produces ruin — permanent, unrecoverable capital loss — and verifying that no single such chain has non-trivial probability. Deals with any plausible ruin path must be restructured or abandoned. Use when sizing leverage, testing refinancing, covenant, and liquidity risk, or checking whether any single failure chain can permanently wipe out the position.
revenue-cost-architecture
Design a venture's revenue streams, pricing logic, cost structure, and unit economics so the business model has a coherent theory of value capture, not just value creation. Use after the value proposition and customer segment are set and before financial projections, when choosing a pricing model, calculating contribution margin or CAC payback, or checking whether the unit economics hold at scale.
replacement-cost-stress-test
Apply the Buffett discipline of pricing every real estate asset against the cost to rebuild it from scratch. The ratio of purchase price to replacement cost sets a ceiling on long-run downside: below replacement cost, new supply cannot profitably undercut you; at or above it, you are buying a liability disguised as an asset. Use when setting a purchase price, judging downside protection, or testing how exposed an asset is to newly built competing supply.