A definitive guide for Data Architects and Data Engineers to build high-performance Lakehouse architectures.
Foundations for reliable performance and engine configuration.
Delta Format
Industry standard format for high performance and reliability on Databricks.
df.write.format('delta').save(path)
Delta Lake
Open-source layer adding ACID transactions and time travel to object storage.
spark.read.format('delta').load(path)
Liquid Clustering
Modern dynamic clustering that replaces complex manual partitioning.
CREATE TABLE t CLUSTER BY (id)
Idempotencia
Design logic to ensure re-runs produce identical results without duplicates.
MERGE INTO t USING src ON t.id=src.id
Medallion Arch
Data flow pattern: Bronze (Raw) → Silver (Clean) → Gold (Aggregated).
spark.read.format('delta')
.load('/bronze/events')
.filter(...)
.write.format('delta')
.save('/silver/events')
Photon Engine
Native vectorized execution engine written in C++ for maximum throughput.
spark.conf.set('spark.databricks
.photon.enabled', 'true')
Autoscaling
Dynamically adjust worker nodes based on workload. Min: 2, Max: 10.
"autoscale": {"min_workers": 2,
"max_workers": 10}
Job Clusters
Short-lived clusters dedicated to a single task. Saves cost vs Shared.
{"job_cluster_key": "etl_cluster",
"new_cluster": {
"spark_version": "14.3.x-scala2.12",
"node_type_id": "Standard_DS3_v2"}}
Processing Time
Determines micro-batch frequency in structured streaming.
.trigger(processingTime='10s')
AvailableNow
Process all data currently available as a single batch in a stream.
.trigger(availableNow=True)
Checkpointing
Stores progress metadata to ensure fault tolerance in streaming.
.option('checkpointLocation', p)
Auto Loader
Efficiently ingests millions of files from cloud storage incrementally.
spark.readStream
.format('cloudFiles')
.option('cloudFiles.format', 'json')
.load('/mnt/landing/events')
Manipulating and retrieving data with maximum efficiency.
MERGE INTO
Perform inserts, updates, and deletes in a single atomic operation.
MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED THEN INSERT *
Schema Evolution
Automate table schema updates during data ingestion.
.option('mergeSchema', 'true')
Watermarking
Manage state and late data arrival in streaming joins/aggs.
.withWatermark('ts', '10 min')
Deduplicación
Drop duplicate records efficiently in streaming pipelines.
df.dropDuplicates(['id'])
Column Pruning
Only select columns you need to reduce memory and I/O footprint.
# ✅ Good: select only needed cols
df.select('id', 'city', 'amount')
# ❌ Bad: reads all columns
df.select('*')
Predicate Pushdown
Filters are applied at the source level before data hits the engine.
df.filter(col('year') == 2024)
Avoid UDFs
User Defined Functions are black boxes to Spark. Prefer native functions.
F.upper(col('name')) # not udf()
Partitioning
Physical layout based on a column to optimize query pruning.
df.write.format('delta')
.partitionBy('year', 'month')
.mode('overwrite')
.save('/delta/sales')
Skew Handling
Salt keys to distribute unbalanced data across all partitions.
(col('id') + (rand()*10)
.cast('int')).alias('salted_id')
Skew Hints
Manually inform Spark about skewed columns for better join plans.
SELECT /*+ SKEW('orders', 'user_id',
(1001, 1002)) */ *
FROM orders JOIN users
ON orders.user_id = users.id
Dynamic Pruning
Filters data based on runtime results from other tables in a join.
SET spark.sql.optimizer
.dynamicPartitionPruning=true
Optimizing the physical storage and operational health of tables.
OPTIMIZE
Compacts small files into larger, more efficient 1GB files.
OPTIMIZE sales_data
ZORDER BY
Multidimensional clustering to speed up selective search queries.
OPTIMIZE t ZORDER BY (id)
CACHE TABLE
Pulls table data into cluster RAM for frequent access.
CACHE TABLE temp_view
Shuffle Partitions
Controls parallelism for wide operations. Crucial for cluster balance.
SET spark.sql.shuffle.partitions=200
REPARTITION
Forces a full shuffle to redistribute data across N partitions.
df.repartition(100)
COALESCE
Reduces partition count without a full shuffle (efficiency gain).
df.coalesce(10)
BROADCAST JOIN
Sends small tables to every worker to avoid expensive shuffles.
/*+ BROADCAST(small_df) */
AQE
Adaptive Query Execution optimizes plans based on runtime stats.
SET spark.sql.adaptive.enabled=true
VACUUM
Deletes old file versions no longer needed for time travel.
VACUUM t RETAIN 168 HOURS
DESCRIBE HISTORY
Audit log of all changes made to a Delta table (Who, What, When).
DESCRIBE HISTORY sales_data
ANALYZE TABLE
Compute column stats for the Cost-Based Optimizer (CBO).
ANALYZE TABLE t COMPUTE STATS
Spot Instances
Use spare cloud capacity for worker nodes to cut costs by ~70%.
"azure_attributes": {
"availability": "SPOT_WITH_FALLBACK",
"spot_bid_max_price": -1
} // -1 = on-demand price cap
Monitoring and long-term data design strategies.
AUTO OPTIMIZE
Automatically optimizes file sizes during write operations.
delta.autoOptimize.optimizeWrite = true
File Size Tuning
Set the target size for data files. Ideal: 128MB to 1GB.
delta.targetFileSize = 134217728
CLUSTER BY
Column definition for Liquid Clustering to replace partitioning.
ALTER TABLE t
CLUSTER BY (region, date)
Spark UI
Crucial for debugging DAGs, data skew, and shuffle spills.
# Get URL from notebook
print(spark.sparkContext.uiWebUrl)
# Or enable programmatically
spark.conf.set(
'spark.ui.enabled', 'true')
Workflows
Native orchestration to schedule and monitor multi-task jobs.
# Trigger via CLI
databricks jobs run-now --job-id 123
# Or REST API
curl -X POST $HOST/api/2.1/jobs/run-now
-d '{"job_id": 123}'
Incremental Load
Only process new data. Efficient for both batch and streaming.
spark.readStream.format('delta')
.load(path)
Decoupled Layout
Separate physical storage optimization from logical structure.
# Physical: compact files independently
OPTIMIZE silver.events ZORDER BY (ts)
# Logical: query unchanged
SELECT * FROM silver.events
WHERE date = '2024-01-01'
DELTA CACHE
Transparent caching on worker SSDs for lightning fast reads.
spark.conf.set('spark.databricks
.io.cache.enabled', 'true')
Consolidated wisdom for reliable production data engineering.
Scale logic, then hardware: Optimize transformation steps before increasing worker SKU.
Data layout is performance: Use ZORDER and Liquid to reduce scan overhead.
Incremental > Batch: Avoid re-processing entire tables unless logically required.
Always Measure: Use Spark UI to detect shuffles and data spills early.
Use Delta Lake default: Ensure every table leverages ACID and time travel.
Automate Maintenance: Schedule OPTIMIZE and VACUUM tasks.
Job Clusters Only: Use ephemeral clusters for production workflows to save cost.
Design Idempotency: Every pipeline must be safely retry-able without corruption.
Blind Scaling: Adding workers to fix bad code just hides the bottleneck (shuffle/skew).
OLTP Logic: Thinking row-by-row instead of set-based vectorized operations.
No Checkpoints: Forgetting checkpoints leads to state loss in streaming failures.
Over-partitioning: Creating too many small folders for small tables slows down listing.