Skip to content
DP-700 Fabric Engineer Hub

Cheatsheet: PySpark, Delta Lake & Lakehouse Optimization

Cheatsheet: PySpark, Delta Lake & Lakehouse Optimization

Source

  • Content type: Quick-Reference Cheatsheet
  • Target Exam: Exam DP-700
  • Date captured: 2026-08-15
  • Last reviewed: 2026-08-15

1. PySpark DataFrame Patterns

from pyspark.sql.functions import col, when, to_date, date_format, window, avg, max, broadcast


# Ingestion with explicit schema
custom_schema = "TransactionId LONG, CustomerId INT, Amount DOUBLE, EventTime TIMESTAMP"
raw_df = spark.read.format("json") \
    .schema(custom_schema) \
    .load("Files/raw/events/*.json")


# Cleanse & Enrich
clean_df = raw_df.filter(col("Amount") > 0) \
    .withColumn("EventDate", to_date(col("EventTime"))) \
    .withColumn("AmountTier", when(col("Amount") > 1000, "High").otherwise("Standard"))


# Structured Streaming with Sliding Window
streaming_df = spark.readStream.format("delta").table("bronze_events")
windowed_stream = streaming_df \
    .groupBy(window(col("EventTime"), "10 minutes", "5 minutes"), col("CustomerId")) \
    .agg(avg("Amount").alias("AvgAmount"), max("Amount").alias("PeakAmount"))

2. Delta Lake Optimization & Table Maintenance

-- 1. Compact small files and apply V-Order
OPTIMIZE fact_sales VORDER;


-- 2. Liquid Clustering (modern alternative to Z-Order/partitioning)
ALTER TABLE fact_sales CLUSTER BY (Region, OrderDate);
OPTIMIZE fact_sales;


-- 3. Delta Table Vacuum (purges historical files older than 7 days)
VACUUM fact_sales RETAIN 168 HOURS;


-- 4. Query Historical Snapshot
SELECT * FROM fact_sales VERSION AS OF 5;
SELECT * FROM fact_sales TIMESTAMP AS OF '2026-08-01 00:00:00';

3. Join Strategies & Skew Mitigation

  • Broadcast Join: Use df.join(broadcast(small_dim), "Key") when dimension table is $<100$ MB.
  • Handling Data Skew: Add salt key column (withColumn("salt", expr("floor(rand() * 10)"))) or enable Adaptive Query Execution (spark.sql.adaptive.skewJoin.enabled = true).