Databricks DEA flashcards

Databricks Intelligence Platform

• 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

• 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 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.

• 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

• 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.

• 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.

• 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

• 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.

• 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

• 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

• 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

• 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.

• 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.

Yes — the whole Databricks DEA deck is free and needs no account, as is every Databricks DEA revision lesson. Create a free account to save progress and drill practice questions; full-length mock exams are a one-time unlock per certification.