Case study · Data engineering · certified knowledge plus demos

Databricks & Spark Optimization

Databricks Certified Data Engineer Professional (September 2026), with a step-by-step method for slow Spark jobs and a lakehouse demo on sample data.

  • ProfessionalDatabricks certified, Sep 2026
  • 2028valid until September 3
  • 120 minscenario exam on production design choices
  • 324line items checked in the lakehouse demo

Retail sales lakehouse on sample data

  • Sample files
  • Built and run
  • Checks
  • Design (not run)
  1. Sample files (Sample files)

    Fictional outdoor-gear data: customers, products, orders, line items.

  2. PySpark load (Built and run)

    Explicit schemas and fail-fast parsing, so a bad file stops the load.

    fail-fast
  3. Validated Delta tables (Built and run)

    • customers 20
    • products 12
    • orders 180
    • line items 324
  4. Checks before use (Checks)

    • orphan anti-joins: all zero
    • derived vs. stored order status: all matching
  5. Design

    Gold star schema (Design (not run))

    Line-item grain: one fact table, four dimensions (customer, product, a role-playing date, status).

  6. Best sellers (Built and run)

    DENSE_RANK, so tied products all return.

    ties kept
Diagram as text: sample files are loaded with explicit PySpark schemas into four validated Delta tables (20 customers, 12 products, 180 orders, 324 line items); orphan checks are all zero and order statuses all match. The Gold star schema at line-item grain is a design, not a run. The best-seller query uses DENSE_RANK so ties are kept.

Method

How I approach a slow Spark job

Data and plan first, cluster last

  1. 0BaselineRuntime, DBUs, input, SLA; run twice

    Likely fix: Agree on “fast enough”

  2. 1Jobs timelineMinute-long gaps; many tiny jobs; lost executors

    Likely fix: Move driver work into Spark

  3. 2Longest stageSkew: max task ≥ 1.5× the 75th percentile; spill

    Likely fix: AQE skew join; salt keys; shuffle.partitions=auto

  4. 3I/O bound?About 3 MB/s per core

    Likely fix: Find which of input, shuffle, output dominates

  5. 4Join planSort-merge on a small table; exploding output

    Likely fix: Broadcast (AQE: under 30 MB; open-source Spark: 10 MB); filter first

  6. 5File layoutMany files under 8 MB; little pruning

    Likely fix: OPTIMIZE; liquid clustering; VACUUM (7+ days)

  7. 6CPUFewer tasks than cores; Python UDFs

    Likely fix: Repartition; built-in functions

  8. 7Cluster, lastStill CPU-bound

    Likely fix: Photon; larger workers; re-measure cost

Compute cost ≈ cluster rate × runtime. Thresholds are Databricks’ published ones.

Data and plan first, cluster last.

Compute cost is roughly cluster rate × runtime: reading less data cuts both; a bigger cluster cuts only runtime, at a higher rate.
On this page

Result / Impact

  • Honest depth: certified knowledge plus demos and study work. I have not used Databricks or Spark in a production job.
  • A verifiable credential, valid until September 3, 2028. The 120-minute scenario exam tests production design choices, weighted toward data-processing code (22%) and cost and performance (13%).
  • A method, not a war story. When a job gets slow and expensive, it turns “buy a bigger cluster” into a short list of cheaper fixes, each with evidence.
  • For context, the vendor’s own numbers: on one internal workload, liquid clustering cut 16 queries from 406 to 70 seconds and bytes read by 86% (Databricks blog, June 2026 (external site)). That is Databricks’ result, not mine.
  • A bridge to security work: the exam’s column masks and retention purges build on my security authorization work.

What I built

Retail sales lakehouse on sample data

Fictional outdoor-gear data:

  • Four normalized tables (customers, products, orders, line items).
  • Loads with contracts: explicit PySpark schemas and fail-fast parsing, so a bad file stops the load.
  • Checks before use: row counts (20 customers, 12 products, 180 orders, 324 line items), anti-join orphan checks (all zero), derived vs. stored order status (all matching).
  • Correct rankings: “best-selling products” uses DENSE_RANK, so ties all return.
  • Model for analytics (design). A Gold star schema at line-item grain: one fact table and four dimensions (customer, product, a role-playing date dimension, status).

Planned: a larger synthetic version to measure one change before and after, such as OPTIMIZE.

How I approach a slow Spark job

The full step table with thresholds and sources

Data and plan first, cluster last (Databricks’ published thresholds).

Step Signal Likely fix
0. Baseline Runtime, DBUs, input, SLA; run twice Agree on “fast enough”
1. Jobs timeline Minute-long gaps; many tiny jobs; lost executors Move driver work into Spark
2. Longest stage Skew: max task ≥ 1.5× the 75th percentile; spill AQE skew join; salt keys; shuffle.partitions=auto
3. I/O bound? About 3 MB/s per core Find which of input, shuffle, output dominates
4. Join plan Sort-merge on a small table; exploding output Broadcast (AQE: under 30 MB; open-source Spark: 10 MB); filter first
5. File layout Many files under 8 MB; little pruning OPTIMIZE; liquid clustering; VACUUM (7+ days)
6. CPU Fewer tasks than cores; Python UDFs Repartition; built-in functions
7. Cluster, last Still CPU-bound Photon; larger workers; re-measure cost

Sources: Databricks Spark UI guide (external site), AQE (external site), clustering (external site), Spark tuning (external site).

The same ideas in Snowflake

Five Databricks ideas and their Snowflake equivalents
Databricks / Spark Snowflake
Per-file data skipping Micro-partition pruning
Liquid clustering (up to 4 keys) Clustering keys (3–4 columns)
OPTIMIZE, VACUUM, predictive optimization Automatic reclustering (credits)
Cluster size, Photon Warehouse size (credits roughly double per size)
PySpark Snowpark

Snowflake manages layout for you and charges credits for it; Spark exposes more controls, and more ways to get them wrong (Snowflake micro-partitions (external site)).

Decisions

Ties in rankings and the grain of the fact table

Keep ties in best-seller rankings. Context: products can tie for top. Options: LIMIT 1; ROW_NUMBER; DENSE_RANK. Decision: DENSE_RANK. Consequences: dashboards handle several rows; no silently wrong winner.

Line-item grain for the Gold fact table. Context: questions span orders and products. Options: order grain; line-item grain. Decision: line-item, with order status derived from items. Consequences: product questions are simple sums; order counts need COUNT(DISTINCT order_id); a larger table.

Design, not experience

How I’d run this on Databricks (design, not production experience)

Auto Loader into Bronze; pipeline expectations that quarantine bad rows; Silver tables with explicit contracts; the Gold star schema with liquid clustering on date and product keys; Unity Catalog permissions, column masks on customer email and a scheduled retention purge; system tables for cost per job; deployment through Asset Bundles.

Proof

Next case study

  • AI & Data Science Program · MIT IDSS · 2026

    3business problems, three kinds of ML

    AI/ML projects

    Segmentation, lead conversion and recommendations, each ending in a decision a business could act on.

Need a Claude agent or data workflow built? Work with me