Databricks Data Analyst cheat sheet

The lakehouse combines cheap cloud object storage with Delta Lake ACID transactions and a metastore, so one copy of data serves SQL BI, machine learning, and streaming.

Unity Catalog supplies the catalog.schema.table namespace plus centralized grants, lineage, audit logs, and discovery across workspaces.

Compute options: all-purpose clusters for notebooks and ad hoc work, job clusters for scheduled runs, SQL warehouses for SQL/BI, and serverless compute managed by Databricks.

Medallion layers: bronze = raw ingested data, silver = cleaned and conformed, gold = business-level aggregates consumed by dashboards and reports.

Photon is a vectorized C++ engine in the runtime and SQL warehouses that accelerates Delta scans, joins, and aggregations.

Control plane stores notebooks, job definitions, and cluster configuration; the data plane holds compute and data in the customer's cloud account.

Lakeflow Declarative Pipelines (Delta Live Tables) define batch and streaming tables declaratively, with data-quality expectations attached to each dataset.

Delta Lake stores Parquet data files plus a _delta_log transaction log, which enables ACID commits, schema enforcement, and time travel.

Managed tables store files in the metastore's managed storage location and delete those files when dropped; external tables only register an existing path and keep the files.

OPTIMIZE compacts small files; OPTIMIZE ... ZORDER BY (col) co-locates related values so later queries skip more files.

VACUUM removes data files no longer referenced by the Delta log; the default retention threshold is 7 days (168 hours), and time travel to vacuumed versions fails.

Time travel syntax: SELECT * FROM t VERSION AS OF n or TIMESTAMP AS OF 'ts'; DESCRIBE HISTORY t lists versions, operations, and metrics.

Predictive optimization automatically runs OPTIMIZE, VACUUM, and ANALYZE on Unity Catalog managed tables without manual scheduling.

NOT NULL and CHECK constraints are enforced on write; PRIMARY KEY and FOREIGN KEY constraints in Unity Catalog are informational and used by the optimizer.

Liquid clustering (CLUSTER BY) replaces partitioning and Z-ORDER keys and can be changed later without rewriting existing data.

DESCRIBE DETAIL returns location, format, size in bytes, partitioning, and clustering information for a table or view.

COPY INTO loads files from cloud storage into a Delta table idempotently by tracking already-processed files; a format clause or target schema is required.

Auto Loader (cloudFiles) incrementally ingests new files using directory listing or file notifications, with schema inference, schema evolution, and checkpointing.

The upload UI accepts CSV, TSV, JSON, Avro, Parquet, and Excel files and creates a table, but is intended for small datasets.

CSV/JSON reader options include header, delimiter, quote, escape, inferSchema, and mode (PERMISSIVE keeps bad rows, DROPMALFORMED discards them, FAILFAST errors).

Unity Catalog storage credentials and external locations authorize reads and writes to cloud storage paths without embedding keys in code.

read_files() is a SQL table-valued function that reads raw files with a format and options for ad hoc inspection before creating a table.

Lakehouse Federation queries external databases (for example PostgreSQL or Snowflake) through a connection without copying data into Delta.

CREATE TABLE ... USING DELTA LOCATION 'path' registers existing Delta files as an external table in the metastore.

Warehouse sizes span 2X-Small through 4XLarge; each step roughly doubles compute and cost per cluster.

Warehouse types are Classic, Pro, and Serverless; Serverless starts in seconds and scales to zero when idle.

Multi-cluster scaling adds clusters when concurrency exceeds one cluster's capacity, and each workload runs on a separate cluster.

The query result cache returns results for an identical repeated query without recomputation when the underlying Delta table versions are unchanged.

Parameter markers use :name syntax in the SQL editor; dashboards and APIs supply the values at run time.

Auto Stop shuts a warehouse down after a configured idle period, reducing cost for intermittent usage.

Users need CAN USE on the warehouse plus SELECT on the underlying tables to run a query successfully.

The Statement Execution API runs SQL over HTTP against a warehouse and returns results as JSON arrays or Arrow.

Query History lists each query with user, warehouse, duration, status, and source (notebook, dashboard, Genie), and is filterable by time and user.

Query Profile renders the operator tree with rows, bytes, and time per node, revealing scans, shuffles, joins, and aggregation bottlenecks.

High time in queue means the warehouse is saturated: scale out clusters or choose a larger size to reduce waiting.

Nonzero spilled bytes in a node indicate insufficient memory for that operator; repartitioning or a larger cluster reduces spill.

Comparing rows and bytes read against rows returned exposes over-scanning that partition pruning, filters, or liquid clustering can fix.

EXPLAIN shows the logical and physical plan without executing the query; EXPLAIN COST adds statistics-based cardinality and cost estimates.

ANALYZE TABLE collects column and table statistics that the optimizer uses for join ordering and file skipping.

Attributing query history by source and warehouse identifies which dashboards or jobs generate most of the compute cost.

AI/BI dashboards are assembled from datasets (saved SQL queries) and visualizations; drafts are editable, and publishing shares them with viewers.

Visualization types include bar, line, area, scatter, pie, counter, table, pivot, heatmap, histogram, box plot, Sankey, and choropleth map.

A filter can be scoped to one dataset or all datasets on the dashboard; cross-filtering narrows linked marks when a user clicks a data point.

Dataset SQL accepts :param markers, whose values come from filter widgets or the dashboard URL.

Scheduled refresh reruns dashboard datasets on a chosen cadence using a specified SQL warehouse.

The counter visualization shows a single aggregated value, so the dataset query must already aggregate (count, sum, avg).

Sharing options include specific users or groups, publishing to all workspace users with a link, and embedding the dashboard in an external site.

Legacy SQL dashboards are built from one query's result set, while AI/BI dashboards support multiple datasets and cross-dataset filters.

A Genie space answers natural-language questions by generating SQL against a curated set of Unity Catalog tables.

Curation levers: table and column descriptions, synonyms, sample values, join specifications, and example SQL instructions.

Trusted assets (example queries and benchmarks) anchor joins and filters and measure whether answers are correct.

Genie executes SQL with the asking user's permissions, so Unity Catalog grants, row filters, and column masks still apply.

Space permissions: CAN VIEW to ask questions, CAN EDIT to curate instructions and assets, CAN MANAGE to share and delete.

Genie returns clarifying questions or suggested follow-ups when a question is ambiguous or not supported by the curated tables.

Monitoring usage and thumbs-down feedback identifies questions Genie fails, which then become benchmarks or curation fixes.

Benchmarks compare generated answers to expected results, giving a repeatable accuracy score for the space.

Star schema: one fact table holding measures and foreign keys, joined to denormalized dimension tables holding descriptive attributes.

SCD Type 1 overwrites the changed attribute; SCD Type 2 inserts a new row with effective dates and a current flag to preserve history.

Surrogate keys are generated integers replacing business keys, keeping dimension joins narrow and stable when source keys change.

Views compute at query time; materialized views precompute results and refresh on a schedule, incrementally where supported.

Lakeflow pipelines can define streaming tables for continuous ingestion and materialized views for batch transformations in one graph.

Declare the fact grain (for example, one row per order line) before selecting dimensions, or aggregates will double count.

Unity Catalog primary and foreign keys are informational but let the optimizer rewrite or eliminate joins.

Gold-layer tables should be wide, documented, and pre-aggregated so dashboards avoid runtime joins and heavy computation.

Unity Catalog privilege hierarchy runs account admin, metastore admin, workspace admin, then object owner; privileges inherit downward from catalog to schema to table.

GRANT SELECT ON TABLE main.sales.orders TO analysts grants access, REVOKE removes it, and SHOW GRANTS lists effective privileges.

Row filters attach a boolean-returning function to a table with ALTER TABLE ... SET ROW FILTER fn ON (column).

Column masks use ALTER TABLE ... ALTER COLUMN col SET MASK fn so unauthorized users see null or a transformed value.

Dynamic views enforce row and column rules at query time using functions such as current_user() and is_account_group_member().

Granting to groups rather than individuals and using service principals for automation keeps least-privilege administration manageable.

system.access.audit records queries, grants, and permission changes for investigations and compliance reporting.

Workspace and metastore admins bypass table privileges, so production access reviews should account for admin membership.