Databricks Data Engineer cheat sheet

Unity Catalog uses a three-level namespace: catalog.schema.table, plus volumes, functions, and models registered at the schema level.

Workspace assets (notebooks, jobs, repos) are managed in the Databricks control plane; table data resides in cloud storage in the customer's data plane.

Compute selection rule: all-purpose clusters for interactive and ad hoc work, job clusters for scheduled runs, SQL warehouses for SQL/BI, serverless for elastic managed compute.

Cluster access modes: shared (multi-user, Python and SQL, no Scala/RDD), single user (full language support for one principal), and custom.

Magic commands: %sql, %python, %scala, %r, %md, %sh, %pip, %run; %pip installs notebook-scoped libraries and requires a Python restart to take effect.

Delta Lake adds ACID transactions, schema enforcement, time travel, and MERGE support on top of Parquet files in cloud object storage.

Photon is a vectorized C++ query engine that accelerates SQL and DataFrame operations on Delta tables and is enabled per cluster or SQL warehouse.

Lakeflow is the data engineering suite combining ingestion (Auto Loader, Lakehouse Federation), declarative pipelines, and Lakeflow Jobs.

A Unity Catalog metastore is regional; one metastore can be assigned to multiple workspaces in the same region, sharing catalogs and privileges.

COPY INTO is idempotent: it tracks files already loaded and skips them on re-execution, so re-running over the same directory does not duplicate rows.

Auto Loader (cloudFiles) incrementally discovers new files via directory listing or file notification mode and requires a schemaLocation plus a checkpoint location.

cloudFiles.format supports json, csv, parquet, avro, orc, text, and binaryFile; binaryFile exposes path, modificationTime, and length metadata columns.

Trigger availableNow processes all files new since the last checkpoint in one micro-batch and then stops; trigger once is deprecated in favor of availableNow.

read_files(path, format => 'json') is the SQL function for reading files directly in SQL and in declarative pipeline queries.

Schema evolution modes for Auto Loader: addNewColumns (default), rescue, failOnNewColumns, and none; rescued fields land in the _rescued_data column.

Lakehouse Federation queries external databases (PostgreSQL, MySQL, SQL Server, Snowflake, Redshift) through foreign catalogs without copying data into Delta.

Streaming sources include Kafka, Kinesis, Event Hubs, Pulsar, and cloud file sources; each requires a checkpointLocation to track progress.

MERGE INTO target USING source ON key WHEN MATCHED THEN UPDATE / WHEN NOT MATCHED THEN INSERT is the standard pattern for upserts and deduplication.

Medallion layers: bronze = raw ingested data, silver = cleaned and conformed entities, gold = business-level aggregates for reporting and BI.

OPTIMIZE compacts small files; ZORDER BY colocates rows with similar values so queries skip files; liquid clustering (CLUSTER BY) replaces partitioning plus ZORDER on new tables.

VACUUM deletes unreferenced files older than the retention threshold (default 7 days); time travel beyond that window fails unless files were retained.

Unity Catalog enforces NOT NULL and CHECK constraints; PRIMARY KEY and FOREIGN KEY constraints are informational and not enforced.

APPLY CHANGES INTO (AUTO CDC) in Lakeflow pipelines implements SCD type 1 via upsert and SCD type 2 via history tracking with a sequencing column.

Window functions: ROW_NUMBER over a partition for deduplication, LAG/LEAD for prior and next values, SUM OVER for running totals.

from_json with an explicit schema plus explode converts nested JSON arrays into rows; schema_of_json derives a schema from a sample literal.

Materialized views recompute on refresh, streaming tables append incrementally, and both are defined inside declarative Lakeflow pipelines rather than as standalone DDL.

Pipeline expectations: @dp.expect records metrics, expect_or_drop discards bad rows, expect_or_fail halts the update, expect_all_or_quarantine routes bad rows to a quarantine table.

Task types include notebook, Python script, Python wheel, JAR, SQL, pipeline, dbt, Run Job, and For each.

run_if values: ALL_SUCCESS (default), AT_LEAST_ONE_SUCCESS, ALL_DONE, AT_LEAST_ONE_FAILED, ALL_FAILED.

Dynamic value references: {{job.parameters.name}}, {{job.id}}, {{tasks.task_name.values.key}} resolve at runtime from job or task context.

Trigger options: scheduled (Quartz cron), continuous, file arrival, table update, and manual.

Repair run re-executes only failed or skipped tasks using the original run's parameters; a full rerun re-executes every task from scratch.

For each tasks iterate a nested task over a JSON array input with a configurable concurrency limit.

Job clusters are created for a run and terminated at completion; all-purpose clusters can be reused across runs at higher cost.

max_concurrent_runs caps parallel executions of a job; when queueing is enabled, extra runs wait instead of failing immediately.

Retries are set per task with retry_on_timeout; notifications support email, webhook, and PagerDuty at job and task level.

Databricks Asset Bundles are defined in databricks.yml with bundle name, targets (dev/staging/prod), workspace paths, and resources such as jobs and pipelines.

Bundle lifecycle commands: databricks bundle validate, databricks bundle deploy -t , databricks bundle run -t , databricks bundle destroy.

Bundles support variables (${var.name}) and interpolation (${workspace.host}, ${bundle.target}); target overrides customize values per environment.

The Terraform databricks provider manages jobs, clusters, notebooks, and Unity Catalog objects as code alongside or instead of bundles.

CI authentication uses OAuth machine-to-machine with a service principal client ID and secret, or a token stored in a .databrickscfg profile.

Git folders (Repos) sync notebooks with a Git remote; jobs can also reference a Git provider source directly without copying files into the workspace.

Secrets live in secret scopes (Databricks-backed or Azure Key Vault-backed) and are read with dbutils.secrets.get(scope, key) rather than hardcoded.

Testing pattern: pytest unit tests against a local SparkSession for transformations, plus integration tests that deploy the bundle to a dev target.

Spark UI Stages tab exposes shuffle read/write, spill, and task duration distributions; a maximum task far above the median indicates data skew.

Adaptive Query Execution (spark.sql.adaptive.enabled=true) coalesces shuffle partitions, converts sort-merge joins to broadcast joins, and splits skewed partitions.

Broadcast hint /*+ BROADCAST(small_table) */ avoids shuffling the large side; the default auto-broadcast threshold is 10 MB (spark.sql.autoBroadcastJoinThreshold).

Query Profile in the SQL editor shows operator-level rows, bytes, and time; spills and exploding joins appear as the dominant cost nodes.

Target Delta file size is roughly 128 MB to 1 GB; delta.autoOptimize.optimizeWrite and OPTIMIZE reduce the small-file problem.

System tables for monitoring: system.billing.usage (cost), system.query.history (query text and duration), system.access.audit (access events).

repartition(n, col) performs a full shuffle to redistribute data; coalesce(n) reduces partition count without a shuffle and can create skew.

Cluster autoscaling sets min and max workers; Photon and the Delta cache accelerate repeated scans of the same Delta data.

Unity Catalog hierarchy: metastore > catalog > schema > table/view/volume/function/model; privileges inherit down the hierarchy.

Privileges include USE CATALOG, USE SCHEMA, SELECT, MODIFY, CREATE TABLE, and ALL PRIVILEGES; the object owner has full control and can grant privileges.

Row filters and column masks are SQL UDFs attached to a table or column and applied at query time; users need EXECUTE on the function to read the object.

Storage credentials wrap an IAM role or managed identity; external locations combine a credential with a cloud path for external tables and volumes.

Managed tables store data under the catalog/schema managed location and are deleted with the table; external tables keep data after the table is dropped.

Audit events are queryable in system.access.audit; table and column lineage are in system.access.table_lineage and system.access.column_lineage.

Governed tags enable attribute-based access control so policies apply by tag (for example PII) rather than by individual object name.

Service principals with OAuth are preferred over personal access tokens for automation; cluster policies constrain which cluster configurations users may create.