Databricks Data Engineer Professional cheat sheet

Standard Python UDFs pickle data row-by-row to a Python worker process; Pandas/Arrow UDFs exchange batches over Apache Arrow and are materially faster.

spark.sql.execution.arrow.pyspark.enabled=true enables Arrow-based conversion between JVM DataFrames and pandas objects, and is required for best Pandas UDF performance.

Higher-order SQL functions transform, filter, exists, aggregate, and zip_with take lambdas and operate on ARRAY/MAP values without exploding rows.

MERGE INTO target USING source ON supports WHEN MATCHED [AND cond] THEN UPDATE/DELETE and WHEN NOT MATCHED [BY SOURCE|TARGET] THEN INSERT/UPDATE.

trigger(availableNow=True) processes all currently available data as bounded micro-batches and then stops the stream, giving idempotent scheduled incremental loads.

row_number() OVER (PARTITION BY key ORDER BY ts DESC) = 1 is the deterministic dedup filter; rank()/dense_rank() keep ties while row_number() picks exactly one row.

df.cache() / persist(MEMORY_AND_DISK) and CACHE TABLE materialize only on the first action and pay off when the same DataFrame feeds multiple actions.

dbutils.widgets.text/dropdown declare notebook parameters read via dbutils.widgets.get; jobs pass them through base_parameters or notebook_task base parameters.

With spark.sql.ansi.enabled=true, invalid casts and arithmetic raise errors instead of returning null; try_cast and try_divide always return null on failure.

Auto Loader is the Structured Streaming source spark.readStream.format('cloudFiles'); it requires cloudFiles.schemaLocation plus a streaming checkpoint location.

cloudFiles.schemaEvolutionMode accepts addNewColumns (default), failOnNewColumns, rescue, and none.

Setting rescuedDataColumn (commonly '_rescued_data') captures columns or rows that do not match the schema as JSON instead of failing the ingestion.

File notification mode uses cloud queue/notification services to discover new files and scales better than the default directory listing mode for high-volume buckets.

COPY INTO is idempotent: it records which source files were already loaded and skips them on rerun; the target must be a Delta table with a compatible schema.

Kafka ingestion uses readStream.format('kafka') with kafka.bootstrap.servers, subscribe/assign, startingOffsets (earliest|latest|JSON) and maxOffsetsPerTrigger to rate-limit.

read_files(path, format => 'json', ...) is the SQL/Unity Catalog function for direct file reads, including inside streaming tables and materialized views.

Auto Loader tuning options include cloudFiles.maxFilesPerTrigger, cloudFiles.includeExistingFiles, and cloudFiles.useNotifications.

_metadata.file_path, _metadata.file_modification_time, and _metadata.file_size are hidden metadata columns available on ingested file sources.

Lakeflow/DLT SQL expectations are written CONSTRAINT name EXPECT (cond) optionally followed by ON VIOLATION DROP ROW or ON VIOLATION FAIL UPDATE.

Python pipeline decorators are @dlt.expect, @dlt.expect_or_drop, @dlt.expect_or_fail, plus the expect_all, expect_all_or_drop, and expect_all_or_fail variants for dictionaries of rules.

Delta enforces schema on write: extra columns fail the write unless mergeSchema is enabled, while missing columns are populated with null.

dropDuplicates(subset) keeps an arbitrary row per key group, so it is non-deterministic; use a window with an explicit ORDER BY to keep a defined row.

na.fill / fillna replaces nulls with constants or a per-column dictionary; na.replace substitutes arbitrary matching values.

Delta CHECK constraints added with ALTER TABLE ADD CONSTRAINT are evaluated on every write and fail the entire transaction when violated.

If a MERGE source matches the same target row more than once, Delta raises DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE; deduplicate the source first.

delta.columnMapping.mode=name lets columns be renamed or dropped without rewriting existing Parquet data files.

Expectation outcomes (passed and failed record counts) are written as events to the pipeline event log and can be queried for quality reporting.

Delta Sharing is an open credential-based protocol; providers publish shares to recipients and consumers read with the deltaSharing format or by referencing the share name in Unity Catalog.

Provider flow: CREATE SHARE s; ALTER SHARE s ADD TABLE cat.sch.tbl; CREATE RECIPIENT r; GRANT SELECT ON SHARE s TO RECIPIENT r.

Databricks-to-Databricks sharing identifies the recipient by a sharing identifier; open sharing issues an activation URL and a credential file containing a bearer token.

Lakehouse Federation: CREATE CONNECTION c TYPE postgresql OPTIONS (...) and then CREATE FOREIGN CATALOG fc USING CONNECTION c OPTIONS (database 'db').

Foreign catalogs are read-only and are queried with the standard three-level namespace catalog.schema.table.

SHOW SHARES, SHOW ALL IN SHARE s, and DESCRIBE RECIPIENT inspect current sharing configuration and grants.

Partition filtering on a shared table restricts recipients to an allowed subset of partitions without exposing the rest of the data.

deltaSharing reader options include shareCredentialsVersion, endpoint, bearerToken, and shareCredentialsFile for token-based consumers.

DESCRIBE HISTORY tbl returns Delta log commits with operation, operationParameters, operationMetrics (e.g. numOutputRows, numFiles), user, and timestamp.

DESCRIBE DETAIL tbl returns sizeInBytes, numFiles, location, clustering columns, format/provider, and table properties.

Observability system tables include system.access.audit, system.billing.usage, system.compute.clusters, system.query.history, and system.access.table_lineage.

Lakehouse Monitoring writes a profile metric table and a drift metric table per monitored table or inference log and generates a quality dashboard.

Job notifications support on start, on success, on failure, on duration warning (threshold), and on streaming failure, delivered by email or webhook.

StreamingQuery.lastProgress and recentProgress expose inputRowsPerSecond, processedRowsPerSecond, batch duration, and state operator metrics.

spark.sql.streaming.metricsEnabled=true registers streaming metrics with the configured Spark metrics sink.

Databricks SQL alerts run a saved query on a schedule, evaluate a condition such as value > 0, and notify destinations on state change.

The pipeline event log (event_log table or pipeline.event_log) records flow progress, data quality results, and error events for Lakeflow pipelines.

OPTIMIZE tbl compacts small files; OPTIMIZE tbl ZORDER BY (col) co-locates values for multi-column data skipping and cannot be combined with liquid clustering.

VACUUM removes unreferenced files older than the retention interval (default 7 days); shortening retention breaks time travel and long-running readers.

Liquid clustering via CLUSTER BY (a, b) replaces partitioning plus Z-order and clustering keys can be changed without rewriting the table.

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

spark.sql.autoBroadcastJoinThreshold (default 10 MB) sets when a join side is broadcast; raising it removes shuffles but risks driver memory pressure.

Healthy file sizing targets roughly 128 MB to 1 GB per file, tuned with delta.targetFileSize, optimizeWrite, and autoCompact.

The SKEW hint /*+ SKEW('alias', 'col') */ splits known skewed values across multiple tasks.

EXPLAIN FORMATTED and EXPLAIN COST reveal the physical plan plus estimated row counts and sizes for identifying the expensive operator.

Predictive optimization in Unity Catalog automatically runs OPTIMIZE, VACUUM, and ANALYZE based on observed workload patterns.

Unity Catalog privileges include USE CATALOG, USE SCHEMA, SELECT, MODIFY, CREATE TABLE, CREATE VIEW, and EXECUTE; grants on a parent object are inherited by its children within the same type.

ALL PRIVILEGES does not imply USE CATALOG or USE SCHEMA on ancestor objects, so those must be granted separately for a principal to traverse the hierarchy.

Row-level security: ALTER TABLE t SET ROW FILTER fn ON (col), where fn returns BOOLEAN and is evaluated at query time.

Column masking: ALTER TABLE t ALTER COLUMN c SET MASK fn, where fn takes the column value and returns the value the caller is allowed to see.

Dynamic views enforce per-user access using current_user(), is_account_group_member('group'), and current_metastore() inside the view definition.

A storage credential wraps a cloud IAM role; external locations built on it require users to also hold READ FILES or WRITE FILES.

Secret scopes store credentials; dbutils.secrets.get(scope, key) returns a redacted string that cannot be printed in notebook output.

Audit events are queryable in system.access.audit and can be streamed to a cloud bucket via diagnostic log delivery.

Customer-managed keys add a second encryption layer over the cloud provider's default encryption for managed storage.

The Unity Catalog namespace is catalog.schema.table; a metastore is created per region at the account level and attached to one or more workspaces.

information_schema in each catalog exposes tables, columns, routines, table_privileges, and schemata as queryable views for automated governance reporting.

Lineage is captured automatically from queries, notebooks, and jobs, and is queryable in system.access.table_lineage and system.access.column_lineage.

COMMENT ON TABLE t IS '...' documents objects, while ALTER TABLE t SET TAGS ('k' = 'v') attaches key/value metadata visible in Catalog Explorer.

ALTER TABLE ... SET OWNER TO transfers ownership; the owner implicitly holds all privileges on the object.

DESCRIBE EXTENDED tbl returns columns, data type, location, provider, owner, and table properties in one result set.

Governed tags are account-level tags with controlled values that can be applied to securable objects and used in attribute-based access policies.

Query system.access.audit to answer who accessed or modified a table, which user or service principal ran a job, and when.

Declarative Automation Bundles define resources in databricks.yml and are driven by databricks bundle validate, deploy -t , run, and destroy.

Bundle variables and ${var.x} / ${bundle.target} substitution let a single project deploy to dev and prod workspaces with different configurations.

Cluster logs are written under as driver/log4j-active.log, driver/stdout, driver/stderr, and per-executor log directories.

Repair Run re-executes only the failed or skipped tasks of a job run and reuses the outputs of tasks that already succeeded.

dbutils.notebook.exit(value) returns a string to the parent job, while dbutils.jobs.taskValues.set/get pass larger structured state between tasks.

In the Spark UI, compare shuffle read/write size, spill, and task duration distribution across stages to locate skew or data explosion.

Unity Catalog error messages include a machine-readable error class such as SCHEMA_NOT_FOUND or DELTA_MULTIPLE_SOURCE_ROW_MATCHING_TARGET_ROW_IN_MERGE.

CI/CD pipelines authenticate the Databricks CLI (databricks/setup-cli) with a service principal using OAuth M2M client ID and secret.

spark.databricks.delta.schema.autoMerge.enabled allows MERGE and UPDATE writes to add new columns to the target schema.

A star schema has one fact table at the event grain plus conformed dimensions; a snowflake schema normalizes dimensions into further sub-dimension tables.

SCD Type 1 overwrites the changed attribute in place; SCD Type 2 inserts a new version with __START_AT, __END_AT, and an __IS_CURRENT flag.

Medallion layering: bronze is raw append-only ingestion, silver is cleansed and conformed, gold is business-level aggregates for consumption.

Partition on low-cardinality columns with at least ~1 GB per partition; liquid clustering is preferred for high-cardinality or shifting filter patterns.

Delta GENERATED ALWAYS AS IDENTITY produces surrogate keys, while GENERATED ALWAYS AS (expr) produces derived columns computed at write time.

NOT NULL and CHECK constraints are enforced on Delta writes; PRIMARY KEY and FOREIGN KEY declarations are informational and not enforced.

CREATE VIEW stores only a query, whereas a Unity Catalog materialized view persists results and refreshes on a schedule or on demand.

Keep both event time and ingestion time columns so late-arriving data can be restated reproducibly.