databricks¶

4.1 Unity Catalog¶

Central governance for data + AI across workspaces in a region. - Namespace: catalog.schema.object (table/view/volume/function/model). - One metastore per region, attached to workspaces; account-level admin. - Storage credential (cloud IAM role/managed identity) + external location (path + credential) → govern external data. - Managed vs external tables: managed lifecycle owned by UC, drop = delete data. - ANSI SQL GRANT/REVOKE, inheritance down the hierarchy, ownership model. - Extras: automatic column/table lineage, audit logs, Delta Sharing, row filters + column masks, tags, system catalog.

How it works: cluster in a UC-enabled access mode → query hits UC → UC checks permissions → credential vending: issues a short-lived down-scoped cloud token for just those paths → cluster reads storage directly. No long-lived keys on the cluster.

4.2 Passing values to jobs¶

Job/task parameters in UI or API; read in notebook via widgets: dbutils.widgets.text("run_date",""); d = dbutils.widgets.get("run_date")
Task values for task→task: dbutils.jobs.taskValues.set(key="wm", value=x) / .get(taskKey="t1", key="wm", debugValue=...).
Dynamic references: {{job.id}}, {{run_id}}, {{job.start_time.iso_date}}, {{tasks.t1.values.wm}}.
Python wheel/JAR tasks receive sys.argv.
Incremental load pattern: task 1 reads MAX(loaded_ts) from target → taskValues.set → task 2 reads it as low watermark, filters source, writes, updates control table. Fallback: full-refresh flag as a parameter. Prefer idempotent MERGE so retries are safe.

4.3 Secret management¶

Scopes: Databricks-backed or Azure Key Vault-backed (databricks secrets create-scope).
Read: dbutils.secrets.get(scope="s", key="k"); auto-redacted as [REDACTED] in any output.
ACLs per scope: READ / WRITE / MANAGE.
Never print, never store in code/notebook/Git. Use in Spark conf via {{secrets/scope/key}} in cluster config.
Alternative for storage: UC storage credentials or instance profile — no secrets needed.

4.4 Delta Lake¶

ACID — how it works¶

Table = Parquet data files + _delta_log/.
Each commit = 000000N.json listing add/remove file actions + metadata.
Atomicity: single log file write; put-if-absent on N.json → only one writer wins version N.
Concurrency: optimistic — read snapshot, write files, attempt commit; on conflict re-check and retry. Isolation = WriteSerializable (default) / Serializable.
Consistency: schema enforced on write; constraints; schema evolution opt-in (mergeSchema).
Durability: files in object store; checkpoint Parquet every 10 commits so reads don't replay all JSON.
Reader picks latest checkpoint + subsequent JSONs → snapshot isolation, time travel (VERSION AS OF, TIMESTAMP AS OF).

Optimization¶

OPTIMIZE tbl — bin-pack small files to ~1 GB. Solves small-file problem.
ZORDER BY (cols) — multi-dimensional colocation; effective on ≤4 high-cardinality filter columns.
Data skipping — min/max/null stats on the first 32 columns, per file; combined with partition pruning.
VACUUM — deletes unreferenced files, default retention 7 days (limits time travel).
Auto optimize: delta.autoOptimize.optimizeWrite, delta.autoOptimize.autoCompact.
Deletion vectors — mark rows deleted instead of rewriting files (fast DELETE/UPDATE/MERGE).
Others: partition on low-cardinality only (>1 GB per partition), MERGE for upserts, compact + CACHE.

Liquid clustering¶

CREATE TABLE ... CLUSTER BY (c1, c2) — replaces partitioning + ZORDER.
Keys changeable anytime with ALTER TABLE ... CLUSTER BY — no table rewrite; applies to new data.
Incremental: OPTIMIZE clusters only unclustered files, not the whole table.
Handles skew and high cardinality; avoids over-partitioning and small files.
Limits: up to 4 keys; can't combine with partitioning or ZORDER. CLUSTER BY AUTO lets Databricks choose keys from query history.

Delta log¶

What it is
_delta_log/ folder next to data files. Contains one JSON per commit. This is the table — data files not referenced here don't exist.

Commit actions

Action	Stores
`add`	file path, partition values, min/max/null stats
`remove`	tombstone — file logically deleted
`metaData`	schema, partition cols, table properties
`protocol`	min reader/writer versions
`txn`	streaming batch ID → exactly-once
`commitInfo`	operation, user, timestamp → audit

Reading — load latest checkpoint → replay JSONs after it → live files = add − remove → prune with stats.

Writing — write Parquet files → attempt put-if-absent on N+1.json → win = committed, lose = check conflict and retry.

Checkpoint — full state as Parquet every 10 commits. Avoids replaying thousands of JSONs.

Retention — log: 30 days. Data files: 7 days (VACUUM). Time travel limited by both.