Advanced Azure Databricks: encryption keys, hardening, reading query plans and change data capture

A lakehouse is only production-ready when you can answer four questions: Is the data encrypted, and who holds the keys? Who can reach the workspace and the data? Why is this query slow? How do changes flow downstream? This post gives a practical answer to each.

#1. Encryption

#At rest

• CMK features need the Premium tier on Azure, or the Enterprise tier on AWS. Check what your region supports before promising it to an auditor.
• Key rotation: rotate in Key Vault or KMS and keep the old version enabled until Databricks confirms the switch. Never delete or disable a key that is still in use. That makes the workspace or data unreadable.
• Keep the key vault in the same region, with soft delete and purge protection on.

#In transit

• Everything between users, the control plane and the compute plane uses TLS 1.2+.
• Use secure cluster connectivity (no public IPs on nodes). Add Private Link if traffic must stay off the internet.
• Traffic between worker nodes is not encrypted by default. If policy requires it, read the Databricks guidance on encrypting inter-node traffic first, because it costs performance. Alternatively, use the compliance security profile where it's available, which turns this on for you.

#Column-level encryption

Some data must stay unreadable even to people who can query the table, such as card numbers or national IDs. For that, encrypt values in the pipeline with aes_encrypt and keep the key in a secret scope:

-- key from a Key Vault-backed secret scope
SELECT aes_encrypt(card_number, secret('kv-scope', 'pan-key'), 'GCM') AS card_enc FROM bronze.payments;
SELECT CAST(aes_decrypt(card_enc, secret('kv-scope', 'pan-key'), 'GCM') AS STRING) AS card_number FROM silver.payments;

In most cases a column mask is simpler, and it's usually enough. See the access-control post for how to set one up.

#2. Security controls checklist

• Unity Catalog for all data; no direct storage keys in notebooks; no DBFS mounts.
• Workspace front end: Private Link or IP access lists; SSO with MFA from your IdP.
• Back end: VNet injection / customer-managed VPC; egress through a firewall.
• Identities: groups from SCIM / automatic identity management; jobs as service principals; PATs disabled or short-lived.
• Compute: cluster policies; standard or dedicated access modes only; no "no isolation shared".
• Secrets in Key Vault-backed scopes; secret() in SQL, dbutils.secrets.get in code. Values are redacted in output.
• Audit: system.access.audit queried daily; diagnostic logs to your SIEM.
• Exfiltration: restrict results download and notebook export for sensitive workspaces; review Delta Sharing recipients.
• Regulated data: compliance security profile / enhanced security monitoring.

A useful daily audit query:

SELECT event_time, user_identity.email, action_name, request_params, response.status_code
FROM system.access.audit
WHERE event_date >= current_date() - 1
  AND (action_name IN ('createToken', 'updatePermissions', 'changeWorkspaceAcl')
       OR response.status_code = 403)
ORDER BY event_time DESC;

#3. Reading query plans

#Getting the plan

EXPLAIN FORMATTED
SELECT c.region, sum(o.amount)
FROM sales.orders o JOIN sales.customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-10-01'
GROUP BY c.region;

df.explain("formatted")     # or "extended" / "cost"

These show the planned query. For what actually happened, use:

• the Spark UI → SQL / DataFrame tab on clusters, or
• Query profile on SQL warehouses and serverless.

#What to look for

Tip

Read the plan bottom up. Scans are at the bottom, so check pruning there first. Most slow lakehouse queries read too many files, and nothing higher in the plan can make up for that.

#4. Change data capture (CDC)

There are two directions. Capturing changes out of a Delta table uses Change Data Feed. Applying changes into a Delta table from a source system uses MERGE or AUTO CDC.

#Change Data Feed (capture)

ALTER TABLE silver.customers SET TBLPROPERTIES (delta.enableChangeDataFeed = true);

-- batch: changes between versions (or timestamps)
SELECT * FROM table_changes('silver.customers', 120, 130);

# streaming: feed downstream tables incrementally
(spark.readStream.option("readChangeFeed", "true").table("silver.customers")
   .writeStream.option("checkpointLocation", "/Volumes/ops/chk/customers_cdf")
   .trigger(availableNow=True).toTable("gold.customer_changes"))

Each row carries _change_type (insert, update_preimage, update_postimage or delete), _commit_version and _commit_timestamp. CDF only records changes made after it was enabled. It is also subject to VACUUM retention, so readers must keep up.

#Applying CDC with MERGE

MERGE INTO silver.customers t
USING (
  SELECT * FROM bronze.customer_cdc
  QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY change_seq DESC) = 1   -- latest per key
) s
ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET *
WHEN NOT MATCHED AND s.op <> 'D' THEN INSERT *;

De-duplicate to one row per key first, or MERGE fails with "multiple source rows matched".

#AUTO CDC in Lakeflow declarative pipelines

Pipelines handle ordering, out-of-order events and SCD Type 2 for you:

CREATE OR REFRESH STREAMING TABLE customers_scd2;

CREATE FLOW customers_cdc AS AUTO CDC INTO customers_scd2
FROM STREAM(bronze.customer_cdc)
KEYS (customer_id)
APPLY AS DELETE WHEN op = 'D'
SEQUENCE BY change_seq
COLUMNS * EXCEPT (op, change_seq)
STORED AS SCD TYPE 2;

Older pipelines use APPLY CHANGES INTO (SQL) or dlt.apply_changes (Python), which have the same semantics. Check the syntax your runtime documents.

#Summary

• Encryption: platform keys by default; CMK on storage, managed services and disks when policy demands; never disable a key that is in use.
• Security: Unity Catalog, private networking, groups and service principals, audit logs.
• Query plans: check scans and pruning first, then joins, shuffles and skew.
• CDC: CDF to publish changes, MERGE or AUTO CDC to apply them.