Databricks DEA cheat sheet — every exam topic on one page

Databricks Intelligence Platform

Databricks Platform Architecture, Delta Lake, Unity Catalog and Compute (Data Engineer Associate)

• A lakehouse stores data once in open Delta tables on object storage and governs it in Unity Catalog, serving BI, SQL and ML from the same tables.
• The control plane (web app, scheduler, cluster manager) runs in the Databricks account; classic compute runs in your cloud account; serverless compute runs in the Databricks account in the workspace's region.
• Delta = Parquet files + _delta_log; a failed write is never visible, readers see whole committed versions, and DELETE/UPDATE/MERGE go through the log. Never delete data files by hand.
• Time travel needs both the log entry (logRetentionDuration, 30 days default) and the data files (deletedFileRetentionDuration, 7 days default, enforced by VACUUM). RESTORE adds a new commit.
• Schema enforcement rejects mismatched writes; mergeSchema adds columns for one write, autoMerge does it session- or cluster-wide, overwriteSchema with overwrite replaces types.
• One Unity Catalog metastore per region; names are catalog.schema.object; USE CATALOG resets the current schema to default.
• Unity Catalog lineage is automatic and column-level in Catalog Explorer; volumes govern non-tabular files at /Volumes paths and replace DBFS mounts.
• All-purpose compute is for interactive work at a higher DBU rate; job compute lives for a run at a lower rate, and tasks can share one job cluster.
• Serverless: no RDDs, no R or Scala in notebooks, no init scripts or compute policies, no Spark UI; declare job packages once in the environment, not in per-run %pip; standard mode costs fewer DBUs but starts slower than performance optimized.
• SQL warehouses: serverless (fastest start, Databricks account), pro (Photon + Predictive IO in your account), classic (no Predictive IO). Standard access mode isolates users; dedicated mode serves one user or group and supports R and the ML runtime.

Data Ingestion and Loading

Databricks Data Ingestion Patterns and Lakeflow Connect: Choosing an Ingestion Method (Data Engineer Associate)

• Batch reloads suit complete snapshots; incremental loads suit accumulating data (append new records, MERGE changed ones on a key); continuous streaming is only for seconds-level latency.
• Triggered pipelines and trigger(availableNow=True) process what has arrived and then stop compute; a long processingTime trigger still keeps the cluster running.
• The checkpoint records processed files or offsets: a new or empty checkpoint reprocesses everything, and cloudFiles.includeExistingFiles only matters on a stream's first start.
• A changed streaming-table query affects only new rows; a full refresh clears the table and rereads all data still in the source.
• In SQL, CREATE OR REFRESH STREAMING TABLE ... FROM STREAM read_files loads each file once; CTAS and INSERT INTO over read_files reread every file.
• COPY INTO fits thousands of files and occasional batches; Auto Loader fits millions of files and continuous arrival.
• Standard connectors are Auto Loader (cloud storage, SFTP) and Structured Streaming or pipelines (Kafka, Kinesis, Event Hubs, Pub/Sub); managed connectors cover SaaS apps, databases and file services.
• CDC-based database connectors capture every change and hard deletes through a gateway; query-based connectors see only the latest state and only soft deletes via a deletion condition; SCD type 2 keeps history.
• Start with the most managed layer; use partner or community connectors only to fill gaps, and Lakehouse Federation for occasional in-place queries rather than heavy or historical workloads.
• Govern ingestion through Unity Catalog connections (USE CONNECTION to reuse), storage credentials, external locations and volumes, never DBFS mounts or the DBFS root.

COPY INTO and Auto Loader: Incremental File Loading into Unity Catalog Tables (Databricks Data Engineer Associate)

• COPY INTO skips files already loaded into the same target table, even if they were modified later; truncating the table does not reset that history, and a new table starts with none.
• COPY_OPTIONS ('force' = 'true') is the only way to reload loaded files; keep it out of scheduled jobs, and pair it with FILES to reload one corrected file.
• PATTERN (a glob) and FILES (at most 1,000 names) select files and cannot be combined; a source SELECT selects, renames and casts columns and can add _metadata.file_path, but GROUP BY is not supported.
• mergeSchema in FORMAT_OPTIONS merges source file schemas; in COPY_OPTIONS it evolves the target table. VALIDATE checks parsing, schema and constraints, writes nothing and records no files.
• Volumes need READ VOLUME, external-location paths need READ FILES, and unregistered buckets can be read once with inline temporary credentials.
• Auto Loader infers JSON, CSV and XML columns as strings unless cloudFiles.inferColumnTypes is true; schemaHints pin chosen columns, and schemaLocation stores every schema version.
• addNewColumns (default when inferring) fails then continues after a restart; rescue keeps running without changing the schema; failOnNewColumns needs manual action; none (default with a provided schema) drops new fields unless rescuedDataColumn is set.
• _rescued_data holds missing fields, type mismatches and case mismatches with the file path, but not corrupt records.
• Directory listing is the default and needs read access only; managed file events on the external location are the recommended notification mode and work with volumes.
• trigger(availableNow=True) processes the backlog present at start in micro-batches of up to 1,000 files by default, then stops; includeExistingFiles applies only on a first start.

Lakeflow Connect, JDBC/REST Clients and JSON Ingestion in Databricks (Data Engineer Associate)

• The Unity Catalog connection holds credentials; the ingestion pipeline holds the source objects, destination catalog and schema, table_configuration and schedule.
• Database ingestion gateways run continuously on classic compute so log retention cannot drop changes; the serverless ingestion pipeline can be scheduled.
• SCD type 1 is the default; scd_type SCD_TYPE_2 keeps row versions with __START_AT and __END_AT, and a full refresh removes that history.
• include_columns excludes future source columns, exclude_columns lets them flow, and for Salesforce newly selected columns are only backfilled by a full refresh.
• New columns arrive automatically, dropped columns are kept and marked inactive, and renames, type changes and Salesforce hard deletes need a full refresh.
• JDBC: secrets via dbutils.secrets.get with READ on the scope, parallel reads need all four partitioning options, and the bounds set the stride without filtering rows.
• Incremental pulls filter at the source on a durable watermark advanced after the write, deduplicate change logs, and MERGE on the key.
• REST loops run on the driver; land raw responses in a volume for Auto Loader, distribute calls with mapInPandas to scale, and schedule with Lakeflow Jobs retries and notifications.
• VARIANT (parse_json, try_parse_json) suits changing JSON; paths use col:field[0], are case-sensitive, and try_variant_get returns NULL instead of failing.
• explode and inline drop empty arrays while their _outer variants keep the parent row; binaryFile with pathGlobFilter ingests PDFs and images.

Data Transformation and Modeling

Cleaning, Joining and Reshaping Data with PySpark and SQL (Databricks Data Engineer Associate)

• Read governed tables with spark.read.table("catalog.schema.table"); write full rebuilds with mode("overwrite") or CREATE OR REPLACE TABLE AS SELECT, which are rerun-safe and keep Delta history.
• An overwrite keeps the table's schema: a type change fails, a column you stop writing stays and fills with nulls, and overwriteSchema replaces the schema; mergeSchema only adds compatible changes.
• Scope dropna and fillna with subset or a dict; fillna skips columns whose type does not match the value, so cast before filling.
• Use to_date with an explicit pattern for non-ISO dates, DECIMAL for money, and try_cast when malformed values must become null instead of failing under ANSI mode.
• Joins multiply rows when a key repeats on the other side, and a right-side condition in WHERE turns a left join into an inner join; put it in ON or filter the right side first.
• Same-named keys go in a list (one copy of each key in the result); differently named keys need an & expression; eqNullSafe or <=> matches null to null.
• Broadcast the small side with broadcast() or /*+ BROADCAST(alias) */; never the large side, never two large tables.
• SQL UNION deduplicates and UNION ALL keeps every row; PySpark union keeps duplicates and matches by position, unionByName matches by name.
• withColumnRenamed and drop silently ignore missing names; renaming or dropping a Delta table column needs column mapping ('name' mode can be enabled on an existing table).
• split takes a regex and an optional limit; getItem is zero-based, element_at one-based; posexplode_outer keeps positions and rows with null or empty arrays.

Deduplication, Aggregation and Basic Spark Tuning in Databricks (Data Engineer Associate)

• distinct() and dropDuplicates() with no arguments compare every column; dropDuplicates(subset) keeps one arbitrary full row per key, so the subset must be the entity's natural key.
• To keep a specific row per key, use row_number() over a window partitioned by the key and ordered by recency, keep row 1, and add a unique tie-breaker last; rank() and dense_rank() keep ties.
• In Databricks SQL, QUALIFY filters on window functions in a single SELECT; WHERE and HAVING cannot, because windows are computed after them.
• Streaming deduplication needs a watermark and either the event-time column in the dropDuplicates key or dropDuplicatesWithinWatermark, or its state grows forever.
• count(*) counts rows; count(col), countDistinct and avg skip nulls; distinct() treats null as a value; multi-column countDistinct skips rows with any null.
• approx_count_distinct uses HyperLogLog++ with rsd 0.05 by default; distinct counts are not additive, so store hll_sketch_agg sketches and merge them with hll_union_agg for rollups.
• summary() takes statistic names and adds approximate percentiles; describe() takes column names and returns count, mean, stddev, min and max.
• spark.sql.shuffle.partitions (default 200) sets DataFrame/SQL shuffle tasks and can be set per session; spark.default.parallelism only affects RDDs; driver and executor memory are fixed at launch.
• autoBroadcastJoinThreshold (10 MB, -1 disables) trades shuffles for driver and executor memory; AQE can still switch to a broadcast at runtime using its own Databricks threshold.
• Re-measure one change at a time on the full job (a noop write, not count() or show()) and compare the Spark UI stage's spill, shuffle and task times.

Gold Layer Objects and Data Quality in Databricks (Data Engineer Associate)

• A view is computed at read time (always current, no storage); a materialized view stores a precomputed result kept current by refreshes and suits expensive queries read often.
• Materialized views refresh on demand (REFRESH), on a schedule (SCHEDULE EVERY hours/days/weeks or CRON) or after source changes (TRIGGER ON UPDATE, optionally AT MOST EVERY an interval).
• The owner or a holder of the REFRESH privilege can refresh a materialized view; a serverless pipeline does the work, so warehouse size does not change refresh speed.
• Incremental refresh runs on serverless compute and needs supported, deterministic operations (time functions only in WHERE); Databricks recommends row tracking, deletion vectors and change data feed on sources. EXPLAIN CREATE MATERIALIZED VIEW checks support up front and REFRESH POLICY INCREMENTAL STRICT turns a fallback into an error.
• Streaming tables process each appended record once: non-append source changes break them, and stream-static joins never revisit old rows, so current-state requirements need a materialized view.
• Use a managed Delta table for gold when you need time travel, identity-column surrogate keys or custom MERGE logic, none of which materialized views offer.
• In a pipeline, CREATE VIEW publishes but cannot carry expectations; CREATE TEMPORARY VIEW can carry expectations but is invisible outside the pipeline.
• Expectation actions: warn (default) keeps and counts rows, DROP ROW removes and counts them, FAIL UPDATE rolls back the update and records no metrics.
• Dropped rows cannot be recovered; quarantine invalid rows with a flag, expect_all and a valid/invalid split, and check key uniqueness with a group-by count plus a fail expectation.
• Delta NOT NULL and CHECK constraints are enforced for every writer; primary and foreign keys are informational; anomaly detection catches stale or incomplete tables that row checks cannot see.

Working with Lakeflow Jobs

Lakeflow Jobs Tasks, Dependencies and Control Flow: Retries, Run If, If/Else and For Each (Databricks Data Engineer Associate)

• A job is a DAG: tasks with no dependencies start in parallel, and a task that needs several inputs must list every one in Depends on.
• Retries rerun only the failed task; two retries means three attempts, the timeout applies to each attempt, and a timed-out attempt is retried only with retry on timeout enabled.
• Run if defaults to All succeeded; use All done for cleanup, At least one failed for handlers, and At least one succeeded to continue when any input loaded.
• Excluded tasks count as successful downstream; Upstream failed tasks count as failed, so failures propagate down the DAG.
• The run's status comes from its leaf tasks: all leaves succeeded but something failed is Succeeded with failures; any unsuccessful leaf makes it Failed.
• If/else branches on a comparison: == and != compare as strings, the other operators compare as numbers.
• For each runs one nested task per input with {{input}} or {{input.key}}; no nested For each, concurrency defaults to 1, and inputs come from the Inputs box, a job parameter or a task value.
• Job parameters are set per run, pushed to every task and win over task parameters with the same key; task values pass small (48 KiB) results within a run.
• SQL and dashboard tasks need a serverless or pro SQL warehouse; a dashboard must be published; a job with a Pipeline task runs one run at a time.
• Repair reruns failed tasks and their dependents with the current settings and optional parameter overrides; succeeded tasks are not rerun.

Lakeflow Jobs Schedules and Triggers: Scheduled, File Arrival, Table Update and Continuous (Databricks Data Engineer Associate)

• Scheduled triggers are time-based; file arrival and table update triggers are data-driven; a continuous trigger keeps exactly one run active.
• A simple schedule sets only a unit and interval and Databricks picks the clock time when it is saved; use an advanced schedule for a fixed start time and time zone.
• Quartz cron starts with a seconds field and needs ? in either day of month or day of week.
• Use UTC for a true fixed interval all year; use the local time zone for a fixed local clock time.
• File arrival triggers fire on new files only (not overwrites), check subfolders recursively and do not pass a file list; read the path with Auto Loader and availableNow.
• Without file events, a monitored path is limited to 10,000 files and a workspace to 50 such jobs; enabling managed file events removes both limits.
• Minimum time between triggers caps run frequency; wait after last change waits for a quiet period and resets on every change.
• Table update triggers use Any or All across monitored tables, count every base-table change for a monitored view, and need SELECT plus USE CATALOG and USE SCHEMA for the run-as principal.
• With maximum concurrent runs reached, a run is skipped unless queueing is on, which holds it for up to 48 hours.
• Pausing a trigger keeps its configuration and still allows Run now.

Implementing CI/CD

Databricks CI/CD: Git Folders, Declarative Automation Bundles and the Databricks CLI (Data Engineer Associate)

• Start new work on a branch created from main in the Git dialog; uncommitted edits carry over when you switch branches.
• Switching to a branch that lacks an asset deletes it; switching back recreates it with a new ID and URL.
• A Git folder has one checked-out branch for everyone, so each developer should clone into their own user folder.
• Commit & Push is one action; pull requests are opened, reviewed and merged in the Git provider, and are updated by pushing to their source branch.
• Merge for shared branches; rebase (which force pushes) for a linear history on your own branch; reset discards commits locally and remotely; Abort undoes a conflicting merge or rebase.
• Top-level include loads more configuration files; sync.include uploads files that .gitignore skips.
• Variable precedence: --var, then BUNDLE_VAR_<name>, then variable-overrides.json, then the target's variables, then the default.
• Use lookup variables for external objects with per-workspace IDs, ${resources.jobs.<key>.id} for jobs in the same bundle, ${bundle.target} per environment and ${workspace.current_user.*} per developer.
• Target overrides merge by task_key and job_cluster_key; set run_as to a service principal in the prod target, never as a group.
• Deploy deletes resources removed from the configuration: generate plus bind to adopt a job, unbind to release one; use --auto-approve in CI and --force-lock only for a stale lock.

Troubleshooting, Monitoring, and Optimization

Monitoring, Troubleshooting and Optimizing Pipelines: Lakeflow Jobs Run History, Spark UI, Liquid Clustering and Predictive Optimization (Databricks Data Engineer Associate)

• Upstream failed tasks never ran: follow the Graph view back to the task marked Failed for the root cause.
• The workspace Runs tab shows 60 days of runs across jobs, with Top 5 error types and filters for status, error code and run-as identity.
• Matrix view compares tasks across runs; Timeline view shows long and overlapping tasks inside one run; system.lakeflow.job_run_timeline and job_task_run_timeline hold up to a year of run history.
• A Run duration Warning only notifies; a Timeout stops the run and marks it Timed out.
• Skew shows as a Max task far above the 75th percentile that also read far more data; spill everywhere means memory pressure, spill only at Max means skew.
• AQE splits a skewed join partition only above both the factor times the median and the byte threshold, and does not handle aggregations or broadcast joins.
• Liquid Clustering keys change without rewriting data, OPTIMIZE FULL reclusters existing records, and it cannot be combined with partitioning or ZORDER.
• Predictive optimization and CLUSTER BY AUTO work only on Unity Catalog managed tables; external tables need scheduled OPTIMIZE and VACUUM.
• Startup failures: event log first, delivered init script logs for the reason. Libraries: notebook-scoped beats compute-scoped beats runtime; pin versions.
• Driver OOM comes from collect() or toPandas() on large data; lost executors need autoscaling and spot loss ruled out before blaming memory.

Governance and Security

Unity Catalog Managed vs External Tables, GRANT, REVOKE and DENY (Databricks Data Engineer Associate)

• No LOCATION means a managed table (Delta or Iceberg, lifecycle owned by Unity Catalog); a LOCATION under an external location means an external table, which can also be CSV, JSON, Parquet and other formats.
• Managed files go to the schema's managed location, else the catalog's, else the metastore's; setting one needs CREATE MANAGED STORAGE on the external location.
• Creating an external table needs CREATE EXTERNAL TABLE on the external location plus USE CATALOG, USE SCHEMA and CREATE TABLE; READ FILES and WRITE FILES do not let you create tables.
• Dropping a managed table deletes its files after the recovery period (7 days by default); dropping an external table leaves its files, so re-creating it on the same LOCATION brings it back.
• UNDROP restores the most recently dropped table of that name; use SHOW TABLES DROPPED and UNDROP TABLE WITH ID for an older one; external undrops also need CREATE EXTERNAL TABLE.
• SET MANAGED keeps name, grants, views and history; it needs the owner on serverless or Runtime 17.3 LTS+, and streams must be restarted and Databricks-to-Databricks shares reshared.
• UNSET MANAGED only reverses a SET MANAGED within 14 days; otherwise write a new external table with CTAS or deep clone and a LOCATION.
• Every access needs USE CATALOG and USE SCHEMA; grants inherit to current and future children, so grant at the narrowest container that covers the need.
• REVOKE removes only a grant on that object; inherited, group and owner access survive it. DENY is hive_metastore-only, so in Unity Catalog you restrict by granting narrowly.
• Users are named by email, service principals by application ID, and only account-level groups work; MANAGE delegates administration with no data access, and ALL PRIVILEGES excludes MANAGE.

Unity Catalog Row Filters, Column Masks and ABAC Policies (Databricks Data Engineer Associate)

• Hide rows in place with a row filter (a BOOLEAN UDF, SET ROW FILTER fn ON (cols)); hide values in one column with a column mask (UDF taking the column value, ALTER COLUMN ... SET MASK fn).
• Extra mask arguments go in USING COLUMNS (other columns or literals); a filter with no column uses ON ().
• Check Unity Catalog groups with is_account_group_member(); is_member() sees workspace-local groups only. Use session_user() with a mapping table when there are too many values for groups.
• One filter per table, one mask per column: combine rules in one function. CREATE OR REPLACE FUNCTION changes every table bound to it; unbind before you drop a function.
• Attaching needs ownership or MANAGE + SELECT on the table, plus EXECUTE on the function and USE CATALOG/USE SCHEMA on its location; readers need only SELECT.
• Filters and masks go on tables, not views; protected tables cannot be cloned or time-travelled, and a filter cannot read a table that has its own filter or mask.
• Dedicated access mode: 15.4 LTS+ reads protected tables, 16.3+ writes them. ABAC-secured tables need serverless or Runtime 16.4+.
• A dynamic view suits curated or joined data for users without base-table access; a mask or filter on the table protects every existing query path.
• ABAC policies match governed tags at catalog/schema/table scope, cover new tables automatically and cannot be removed by table owners; columns do not inherit table tags.
• EXCEPT exempts principals: pipeline run-as identities and share owners belong there. Two distinct filters or masks for one user and target cause an error; identical function and arguments do not.