Databricks Data Analyst Practice Test

Databricks Certified Data Analyst Associate

Databricks Data Analyst overview

  • 199 practice questions
  • 32 flashcards
  • 9 study guide topics
  • 38 key terms

What you'll study

  • Understanding of Databricks Data + AI Platform11%
  • Managing Data8%
  • Importing Data5%
  • Executing queries using Databricks SQL and Databricks SQL Warehouses20%
  • Analyzing Queries15%
  • Creating Dashboards and Visualizations in Databricks16%
  • Developing, Sharing, and Maintaining AI/BI Genie spaces12%
  • Data Modeling with Databricks SQL5%
  • Securing Data8%

The Databricks Certified Data Analyst Associate is an entry-level, proctored certification that validates a candidate's ability to use the Databricks Data + AI Platform and Databricks SQL to complete introductory data analysis work. According to the official exam page, it assesses proficiency across the platform itself, managing data with Unity Catalog, importing data through methods such as the UI, S3 ingestion, Delta Sharing, API-driven intake, Auto Loader, and Marketplace, and executing and optimizing queries - including creating views, performing aggregations, joining, filtering, and sorting tables, and analyzing queries with auditing, history, logs, and Liquid clustering. The remaining scope covers dashboards and visualizations, developing and maintaining AI/BI Genie spaces, data modeling with Databricks SQL, and securing data through storage and management best practices. The exam is suited to analysts who already work hands-on with SQL and want formal recognition; Databricks recommends 6+ months of practical experience with the tasks in the exam guide, though no prerequisites are enforced. Preparation guidance from the source is straightforward: review the exam guide, complete the related training it lists, use the AI Prep Guide, run the system check for online proctoring, then re-read the guide to close gaps. All exam SQL adheres to ANSI SQL standards, so no test aides are permitted. This page is independent study material and is not affiliated with or endorsed by Databricks.

Common mistakes

Answers namespace questions using two-part names (schema.table) or dbfs:/ paths, ignoring that in a Unity Catalog-enabled workspace every object is addressed as catalog.schema.table and files live under volumes or cloud storage paths, not DBFS root.

Habit from legacy Hive-metastore workspaces and community tutorials where the workspace default catalog is implicit, so the third level is invisible in daily work.

What to do instead

Before answering, ask which metastore/namespace the scenario implies. Drill the three-level rule and be able to state when dbfs:/FileStore or DBFS root is still valid versus when a volume (catalog.schema.volume) is required for non-tabular data.

Treats a statement like ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY as enforced, and designs models assuming duplicate or null keys will be rejected at write time.

Relational database experience carries over, and the DDL syntax is identical to an enforcing engine.

What to do instead

Learn which constraints Databricks SQL declares as informational (PRIMARY KEY, FOREIGN KEY - used for optimizer hints and BI tool metadata) versus NOT NULL, which is validated. Plan deduplication as an ETL responsibility, not a schema guarantee.

Drops or overwrites a managed table expecting the underlying data files to be gone, or expects a DELETE to immediately free/remove files at the storage layer.

Managed-versus-external semantics and Delta's log-structured storage are conflated with plain filesystem behaviour.

What to do instead

Separate the two concepts in notes: managed tables have their data lifecycle owned by the catalog (DROP removes data), external tables leave files; row-level deletes are logical until VACUUM passes the retention threshold. Be able to state that VACUUM with default retention is what physically removes stale files.

Loads CSV/JSON by letting Databricks infer the schema and does not specify options, then is surprised by all-string columns, wrong date parsing, or a header row loaded as data.

Schema inference succeeds on a small sample and the notebooks used for practice hide the inferred types.

What to do instead

Default to an explicit schema (DDL string or StructType) plus explicit format options such as header, delimiter, and dateFormat. For repeatable ingestion, prefer COPY INTO or Auto Loader with a declared schema and rescued-data column rather than inference.

Diagnoses a slow query by reading only total duration or the SQL text, and never opens the query profile to find the operator with the largest wall-clock time, shuffle spill, or partition skew.

Candidates apply general SQL tuning heuristics (add an index, rewrite the join) instead of the diagnostic tool Databricks actually exposes.

What to do instead

Practise the routine: open Query Profile, sort operators by time, then look for 'spilling to disk', high shuffle read size, and skew across tasks. Name the fix that matches the symptom - repartition or Z-ORDER/OPTIMIZE, broadcast the small side, filter earlier, or right-size the warehouse.

Writes queries with ${param} widget syntax or literal values where a Databricks SQL parameter marker (:param) or a dashboard parameter/date-range control is required, and cannot explain how the value is supplied at run time.

Notebook widget habits dominate, and SQL editor parameters, dashboard filters, and legacy dashboard query parameters look similar but behave differently.

What to do instead

Learn the three layers distinctly: parameter markers in the SQL editor and alerts, dashboard-level filters/date-range controls bound to a field, and notebook widgets. Know that dashboard filters are applied by the dashboard, not by editing the underlying query text.

Assumes anyone who can open a published dashboard can also see its data, or conversely grants direct table SELECT privileges to every dashboard viewer.

The owner-versus-viewer credential model is invisible in the UI until a viewer without table grants either succeeds or fails.

What to do instead

Answer permission questions by first asking who owns the query and whether the dashboard is configured to run as owner or as viewer. If it runs as owner, viewers need only dashboard access; if as viewer, they need USE CATALOG, USE SCHEMA, and SELECT on every underlying object.

Expects a Genie space to answer arbitrarily phrased questions over every table in the workspace, and treats poor answers as a Genie limitation rather than a curation gap.

Genie is assumed to be a general natural-language layer, when it is scoped to the tables, instructions, and examples configured in that space.

What to do instead

Describe Genie work as curation: select and describe the relevant tables, add general instructions, synonyms, example SQL, and trusted assets or measures, then test with representative phrasings and iterate. Also know how sharing a Genie space differs from sharing a dashboard.

Grants SELECT on a table and then cannot explain why the user's query fails, or assumes a column mask or row filter does not apply to the person who created it.

Unity Catalog privileges are hierarchical and masks/filters have explicit exemption handling that is easy to overlook.

What to do instead

Remember the inheritance chain (USE CATALOG on the catalog, USE SCHEMA on the schema, then SELECT) and that masks and row filters apply to all users except those explicitly exempted, typically the object owner and members of a designated exempt group. Reason through the full chain before choosing an answer.

Distinguishes managed versus external tables and selects a table type without weighing that external tables require an external location plus storage credential, and that dropping the table does not drop the files.

The exam frames this as a storage-ownership decision, but candidates answer from the DDL syntax alone.

What to do instead

Anchor on the decision rule: who should own the files' lifecycle? Managed when the catalog should clean up, external when files are shared with other engines or must survive DROP. Verify that external locations and storage credentials must exist and be granted before an external table can be created.

Exam requirements can change. Confirm current details with the exam provider.

Databricks Data Analyst practice

199 questions. Each one lists the choices, the correct answer, and an explanation. With JavaScript, this list opens one question at a time. The checker at the top of this page saves progress in this browser when it is available.

  1. Question 1Understanding of Databricks Data + AI Platform

    What does Delta Lake add on top of Parquet data files that enables ACID transactions?

    1. Row-level indexes stored in every data file
    2. A transaction log that records each commit Correct answer.
    3. Encryption keys embedded in each file footer
    4. A proprietary binary columnar format

    Explanation

    Delta Lake keeps table data in Parquet files and records every commit as an ordered set of JSON entries in the _delta_log directory. That log is the source of truth for the table state, so writes either commit fully or not at all, and readers see a consistent snapshot.

  2. Question 2Understanding of Databricks Data + AI Platform

    Which naming hierarchy does Unity Catalog use to address a table?

    1. catalog.schema.table Correct answer.
    2. metastore.schema.table
    3. schema.catalog.table
    4. workspace.catalog.table

    Explanation

    Unity Catalog organizes objects as a three-level namespace of catalog, then schema, then table or view, for example main.sales.orders. The metastore is the top-level container that holds catalogs but is not part of the object name.

  3. Question 3Understanding of Databricks Data + AI Platform

    A BI tool needs to run many concurrent SQL queries against tables in Unity Catalog. Which compute resource is purpose-built for this?

    1. A job cluster
    2. A single-node machine learning cluster
    3. An all-purpose cluster with autoscaling
    4. A SQL warehouse Correct answer.

    Explanation

    SQL warehouses are compute resources configured and tuned for SQL workloads, including high-concurrency serving of dashboards and BI tools. They speak the SQL warehouse protocol and can scale for many simultaneous short queries.

  4. Question 4Understanding of Databricks Data + AI Platform

    In the medallion architecture, which layer typically holds raw data exactly as it was ingested?

    1. Silver
    2. Bronze Correct answer.
    3. Gold
    4. Platinum

    Explanation

    The bronze layer stores raw ingested data with minimal transformation, preserving the source record and often adding ingestion metadata. Silver then cleanses and conforms the data, and gold holds business-level aggregates for reporting.

  5. Question 5Understanding of Databricks Data + AI Platform

    A Delta table named sales has several versions. Which clause returns the table as it existed at version 3?

    1. SNAPSHOT AS OF 3
    2. HISTORY VERSION 3
    3. TIMESTAMP VERSION 3
    4. VERSION AS OF 3 Correct answer.

    Explanation

    Delta time travel uses VERSION AS OF for a version number and TIMESTAMP AS OF for a commit timestamp, appended to the table reference in a query. The statement reads the table snapshot as of that commit rather than the current state.

  6. Question 6Understanding of Databricks Data + AI Platform

    A scheduled pipeline must create compute when the run starts and release it when the run ends, with no interactive reuse. Which compute type fits?

    1. A SQL warehouse
    2. An all-purpose cluster
    3. A job cluster Correct answer.
    4. A notebook-scoped cluster

    Explanation

    Job clusters are created for a single job run and terminated when it finishes, which avoids paying for idle interactive compute. All-purpose clusters persist for interactive work by multiple users.

  7. Question 7Understanding of Databricks Data + AI Platform

    In a Databricks notebook, what does the %run magic command do?

    1. Imports and runs another notebook inline Correct answer.
    2. Restarts the attached all-purpose cluster
    3. Runs only the currently selected cell
    4. Schedules the notebook as a recurring job

    Explanation

    The %run magic executes another notebook inside the current one, so functions and variables defined there become available to the calling notebook. It is commonly used to factor shared logic into a helper notebook.

  8. Question 8Understanding of Databricks Data + AI Platform

    An analyst creates a table in Unity Catalog without specifying a LOCATION. What is true of that table?

    1. It is readable only by its creator
    2. It cannot be dropped until it is empty
    3. It is managed by Unity Catalog Correct answer.
    4. It is stored externally at a path you must supply

    Explanation

    When no location is given, Unity Catalog creates a managed table and stores its files in the managed storage location of the parent schema. Dropping the table removes the metadata and the managed data files according to retention rules.

  9. Question 9Understanding of Databricks Data + AI Platform

    What does Unity Catalog lineage record for a table?

    1. Git commit history for workspace notebooks
    2. Only the SQL text run on SQL warehouses
    3. Column-level links between sources and targets Correct answer.
    4. Cluster resize events over time

    Explanation

    Unity Catalog captures lineage automatically for supported languages and records relationships at both the table and column level across notebooks, jobs, and SQL queries. Lineage is surfaced in Catalog Explorer so impact analysis can be done before changing an upstream object.

  10. Question 10Understanding of Databricks Data + AI Platform

    A Delta table has accumulated a large number of small files. Which command compacts them into fewer, larger files?

    1. ANALYZE TABLE table COMPUTE STATISTICS
    2. FSCK REPAIR TABLE table
    3. VACUUM table RETAIN 0 HOURS
    4. OPTIMIZE table Correct answer.

    Explanation

    OPTIMIZE rewrites small files into larger ones to reduce per-file overhead during reads. It can optionally apply clustering or Z-ordering, and VACUUM is run afterwards to remove files that are no longer referenced.

  11. Question 11Understanding of Databricks Data + AI Platform

    Which feature incrementally ingests newly arriving files from cloud storage into a Delta table using directory listing or file notifications?

    1. Auto Loader (cloudFiles) Correct answer.
    2. COPY INTO with a full re-read
    3. Delta Sharing
    4. VACUUM

    Explanation

    Auto Loader tracks which files have already been processed and picks up only new ones, using either directory listing or cloud file notification services. It scales to large numbers of files and can infer and evolve the schema.

  12. Question 12Understanding of Databricks Data + AI Platform

    In a Lakeflow Declarative Pipeline, an EXPECT clause is defined with ON VIOLATION DROP ROW. What happens to records that fail the constraint?

    1. It fails the pipeline on the first bad record
    2. It writes failing rows to a quarantine table
    3. It drops failing rows and logs violation metrics Correct answer.
    4. It keeps all rows and logs the violations

    Explanation

    Expectations are data quality constraints checked as data flows through the pipeline. With ON VIOLATION DROP ROW, records that violate the constraint are discarded and metrics for passed and dropped records are recorded for monitoring.

  13. Question 13Understanding of Databricks Data + AI Platform

    A team needs a pipeline to run every hour, retry on failure, and notify owners when it finishes. What should they use?

    1. A Delta Live Tables expectation
    2. A scheduled Databricks job Correct answer.
    3. A dashboard refresh schedule
    4. A notebook run manually each hour

    Explanation

    Databricks jobs provide scheduling, automatic retries, notifications, and dependency orchestration across tasks. A job can run notebooks, SQL, pipelines, and other tasks with parameters and monitoring.

  14. Question 14Understanding of Databricks Data + AI Platform

    What is the primary purpose of Delta Sharing?

    1. Secure sharing without copying the data Correct answer.
    2. Replicating tables to a second cloud region
    3. Sharing clusters between workspaces
    4. Archiving old files to cheaper storage

    Explanation

    Delta Sharing is an open protocol that lets a provider grant recipients access to live data without copying it, with governance enforced by the provider. Recipients can be in other organizations or on other platforms that support the protocol.

  15. Question 15Understanding of Databricks Data + AI Platform

    Which Databricks SQL feature sends a notification when a saved query's result crosses a threshold?

    1. A dashboard parameter
    2. A scheduled query
    3. A cluster policy
    4. An alert Correct answer.

    Explanation

    Alerts evaluate a saved query on a schedule and notify configured destinations when a condition on the result is met. Scheduled queries only refresh results, and parameters change what a dashboard displays.

  16. Question 16Understanding of Databricks Data + AI Platform

    What is Photon?

    1. A notebook execution scheduler
    2. A governance layer for Unity Catalog
    3. A vectorized query execution engine Correct answer.
    4. A storage format optimized for Delta tables

    Explanation

    Photon is a vectorized query engine written in C++ that accelerates SQL and DataFrame workloads on Databricks compute. It works with Delta Lake tables and requires no changes to queries to take advantage of it.

  17. Question 17Understanding of Databricks Data + AI Platform

    A team must store and read raw PDF documents from notebooks while keeping access governed in Unity Catalog. Which object should they use?

    1. A Unity Catalog volume Correct answer.
    2. A dashboard
    3. A managed Delta table
    4. A secret scope

    Explanation

    Volumes are Unity Catalog objects that govern non-tabular files such as documents, images, and binaries, with privileges granted like tables. Files in a volume can be read from notebooks and jobs using normal file paths.

  18. Question 18Understanding of Databricks Data + AI Platform

    Which statement describes serverless compute in Databricks?

    1. Tables it writes live outside Unity Catalog
    2. It supports only Scala notebooks
    3. Databricks handles provisioning and sizing Correct answer.
    4. You choose the instance type and autoscaling range

    Explanation

    With serverless compute, Databricks provisions and sizes the resources, so users submit work without configuring clusters. Resources start quickly and shut down when the work completes, and Unity Catalog governance still applies to any tables produced.

  19. Question 19Understanding of Databricks Data + AI Platform

    Which statement about liquid clustering in Delta Lake is correct?

    1. Keys are fixed at creation and cannot change
    2. It replaces the transaction log with an index
    3. The table must be partitioned on the same columns
    4. Keys can be changed later without a rewrite Correct answer.

    Explanation

    Liquid clustering stores clustering decisions in the transaction log, so clustering keys can be redefined on an existing table instead of requiring a full rewrite as partitioning does. OPTIMIZE then clusters the data incrementally as new data arrives.

  20. Question 20Understanding of Databricks Data + AI Platform

    Which set of privileges lets a user read a table governed by Unity Catalog?

    1. SELECT granted on the catalog, which cascades
    2. SELECT on the table alone
    3. OWN on the schema, with nothing else
    4. USE CATALOG, USE SCHEMA, and SELECT on the table Correct answer.

    Explanation

    Unity Catalog requires both traversal privileges on parent objects and the object-level privilege. A user needs USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table, because table privileges are not inherited from schema-level grants.

  21. Question 21Understanding of Databricks Data + AI Platform

    In Lakeflow Declarative Pipelines, how is a streaming table updated when the pipeline runs?

    1. It appends only new source rows on each run Correct answer.
    2. It recomputes the full result each run
    3. It refreshes only on a manual REFRESH
    4. It requires a static file source

    Explanation

    Streaming tables process each source row once and append results incrementally, so each run consumes only data that has not been seen before. Materialized views, by contrast, recompute their full result from the underlying sources on refresh.

  22. Question 22Understanding of Databricks Data + AI Platform

    What do deletion vectors in Delta Lake enable?

    1. They delete rows and remove files immediately
    2. They mark deleted rows so files need not be rewritten Correct answer.
    3. They store old file versions for time travel
    4. They duplicate rows across clusters for redundancy

    Explanation

    Deletion vectors record which rows are logically deleted in a side structure instead of rewriting whole Parquet files. This makes DELETE, UPDATE, and MERGE operations cheaper, though readers must support deletion vectors to read such tables correctly.

  23. Question 23Managing Data

    In Unity Catalog, which sequence correctly identifies a table by its fully qualified three-level name?

    1. table.schema.catalog
    2. catalog.table.schema
    3. schema.catalog.table
    4. catalog.schema.table Correct answer.

    Explanation

    Unity Catalog organizes objects in a three-level hierarchy: catalog at the top, then schema (database), then the table or view. A fully qualified reference therefore reads catalog.schema.table, for example main.sales.orders. Swapping any two levels does not resolve to a valid object path.

  24. Question 24Managing Data

    Which statement creates a new table and populates it with the rows returned by a query in a single command?

    1. CREATE VIEW sales.orders AS SELECT * FROM staging.orders;
    2. CREATE TABLE sales.orders AS SELECT * FROM staging.orders; Correct answer.
    3. COPY INTO sales.orders FROM 's3://bucket/orders' FILEFORMAT = CSV;
    4. INSERT INTO sales.orders SELECT * FROM staging.orders;

    Explanation

    CREATE TABLE ... AS SELECT (CTAS) defines a new table and materializes the query result into it in one step, with column types inferred from the query. INSERT only adds rows to a table that already exists, and a view stores no data of its own. COPY INTO loads files from a path rather than from a query.

  25. Question 25Managing Data

    Which statement creates a schema named sales inside the catalog main and succeeds even if that schema already exists?

    1. CREATE SCHEMA IF NOT EXISTS main.sales; Correct answer.
    2. CREATE TABLE IF NOT EXISTS main.sales;
    3. CREATE DATABASE sales IN main;
    4. CREATE CATALOG IF NOT EXISTS main.sales;

    Explanation

    CREATE SCHEMA creates a schema and accepts the two-part parent path catalog.schema, and the IF NOT EXISTS clause makes the statement a no-op when the schema is already present. CREATE CATALOG would create a catalog rather than a schema. CREATE DATABASE without a catalog qualifier does not target main.sales explicitly.

  26. Question 26Managing Data

    Which command lists the tables defined in the schema main.sales?

    1. DESCRIBE TABLES main.sales;
    2. SELECT * FROM INFORMATION_SCHEMA.TABLES;
    3. SHOW TABLES IN main.sales; Correct answer.
    4. LIST TABLES OF main.sales;

    Explanation

    SHOW TABLES IN <schema> returns the tables and views belonging to that schema. DESCRIBE TABLE describes the columns of one specific table rather than listing a schema's contents. A query against INFORMATION_SCHEMA.TABLES is a valid inventory approach but the catalog-wide view requires a filter to be limited to main.sales.

  27. Question 27Managing Data

    Which statement attaches the description 'Cleaned order lines' to an existing table without altering its data?

    1. UPDATE main.sales.orders SET comment = 'Cleaned order lines';
    2. COMMENT ON TABLE main.sales.orders IS 'Cleaned order lines'; Correct answer.
    3. CREATE COMMENT 'Cleaned order lines' ON main.sales.orders;
    4. ALTER TABLE main.sales.orders SET DESCRIPTION 'Cleaned order lines';

    Explanation

    COMMENT ON TABLE sets the table's description metadata, which is visible in Catalog Explorer and in DESCRIBE TABLE output without touching any rows. Metadata changes are made with COMMENT or ALTER TABLE ... SET TBLPROPERTIES, never with UPDATE. There is no CREATE COMMENT statement in Databricks SQL.

  28. Question 28Managing Data

    A table was created in Unity Catalog with no LOCATION clause, making it a managed table. What happens to its underlying data files when the table is dropped?

    1. They are moved automatically into the catalog's default managed volume.
    2. They stay in cloud storage indefinitely and keep counting toward storage costs.
    3. They remain in place and must be deleted separately by an administrator.
    4. They are deleted, but the table can be restored within a retention window. Correct answer.

    Explanation

    For managed tables, Databricks owns both the metadata and the storage location, so dropping the table also removes the data files. Unity Catalog keeps the dropped table recoverable for a limited retention period so that UNDROP TABLE can restore it. External tables behave differently, because their files live at a user-specified path that Databricks does not delete.

  29. Question 29Managing Data

    Which statement grants the SELECT privilege on a table to the group analysts?

    1. GRANT `analysts` SELECT ON main.sales.orders;
    2. ASSIGN SELECT ON main.sales.orders TO GROUP `analysts`;
    3. ALLOW SELECT ON TABLE main.sales.orders TO `analysts`;
    4. GRANT SELECT ON TABLE main.sales.orders TO `analysts`; Correct answer.

    Explanation

    The GRANT statement takes the privilege, the securable with its type, and the recipient: GRANT SELECT ON TABLE <name> TO <principal>. Group names containing no special characters may be backtick-quoted. ALLOW and ASSIGN are not Databricks privilege statements.

  30. Question 30Managing Data

    An analyst wants to create an external table whose data already sits at a cloud path such as s3://corp-finance/raw/. Which Unity Catalog object must cover that path before the table can be created?

    1. A managed volume mounted on the path
    2. A catalog whose name matches the bucket name
    3. A schema with MANAGED LOCATION set to the path
    4. An external location backed by a storage credential Correct answer.

    Explanation

    External tables and external volumes read from cloud storage, so Unity Catalog needs a storage credential (holding the cloud identity) and an external location that grants access to a specific path. The external location must already encompass the path used in the table's LOCATION clause. Naming conventions on catalogs or schemas do not grant storage access.

  31. Question 31Managing Data

    Which table constraint rejects any inserted row whose amount column is below zero?

    1. PRIMARY KEY (amount)
    2. CHECK (amount >= 0) Correct answer.
    3. NOT NULL (amount)
    4. FOREIGN KEY (amount) REFERENCES limits(amount)

    Explanation

    A CHECK constraint evaluates a Boolean expression on each row and rejects rows where it is not true, which is exactly how a non-negative rule is enforced. PRIMARY KEY enforces uniqueness and non-nullness, and NOT NULL only forbids nulls, so neither rejects a negative value. A foreign key checks that a value exists in a referenced table.

  32. Question 32Managing Data

    You need to remove only the rows where region = 'EMEA' from a Delta table while leaving all other rows and the table itself intact. Which statement does this?

    1. DROP TABLE main.sales.orders WHERE region = 'EMEA';
    2. ALTER TABLE main.sales.orders DROP ROW WHERE region = 'EMEA';
    3. DELETE FROM main.sales.orders WHERE region = 'EMEA'; Correct answer.
    4. TRUNCATE TABLE main.sales.orders WHERE region = 'EMEA';

    Explanation

    DELETE with a WHERE clause removes exactly the matching rows and leaves the table definition and non-matching rows in place. TRUNCATE removes every row and accepts no filter, and DROP removes the table itself. Databricks SQL has no ALTER TABLE ... DROP ROW statement.

  33. Question 33Managing Data

    Which Unity Catalog object is intended for storing and governing non-tabular files such as PDFs, images, and machine-learning model artifacts?

    1. Managed table
    2. Volume Correct answer.
    3. Streaming table
    4. Materialized view

    Explanation

    Volumes are the Unity Catalog securable designed for files that are not tabular data, providing governance and access control over paths in managed or external storage. Tables and views, whether managed, streaming, or materialized, store structured row data and cannot hold arbitrary files.

  34. Question 34Managing Data

    Which statement adds a nullable STRING column named notes to an existing Delta table?

    1. ALTER TABLE main.sales.orders ADD COLUMN notes;
    2. ALTER TABLE main.sales.orders ADD COLUMN notes STRING; Correct answer.
    3. ALTER TABLE main.sales.orders APPEND COLUMN notes STRING;
    4. UPDATE TABLE main.sales.orders ADD notes STRING;

    Explanation

    ALTER TABLE ... ADD COLUMN followed by a name and a data type is the supported way to add a column, and existing rows receive NULL for the new column. The column type is mandatory, so omitting STRING is invalid. UPDATE and APPEND COLUMN are not the statements used for schema changes.

  35. Question 35Managing Data

    A governance team wants to label a table with key-value metadata such as department = finance so the table can be searched and audited, without changing its data or schema. Which approach does this?

    1. CREATE INDEX department ON main.sales.orders ('finance');
    2. COMMENT ON TABLE main.sales.orders IS 'department = finance';
    3. ALTER TABLE main.sales.orders ADD COLUMN department STRING DEFAULT 'finance';
    4. ALTER TABLE main.sales.orders SET TAGS ('department' = 'finance'); Correct answer.

    Explanation

    Unity Catalog tags are key-value attributes attached to securables; they are searchable in Catalog Explorer and used for governance and auditing without affecting data or schema. Adding a column changes the table structure, and a free-text comment is not a structured, queryable attribute. Tags are set with SET TAGS rather than an index definition.

  36. Question 36Managing Data

    A shallow clone of a Delta table references the source table's data files instead of copying them. Which operation on the source can make the shallow clone unreadable?

    1. Running VACUUM on the source, removing referenced files Correct answer.
    2. Running OPTIMIZE on the source table to compact small files
    3. Running ANALYZE TABLE on the source table
    4. Adding a new column to the source table

    Explanation

    Because a shallow clone points at the source's files, deleting those files breaks the clone. VACUUM physically removes files no longer referenced by the source's current version, and with a short retention it can delete files the clone still needs, which is why a deep clone is used when the copy must be independent. OPTIMIZE and ANALYZE only rewrite or gather statistics on files and do not delete the ones the clone references.

  37. Question 37Managing Data

    Which statement about UNDROP TABLE in Unity Catalog is correct?

    1. It restores a dropped managed table to its original name within the retention period. Correct answer.
    2. It restores any dropped table, including external tables, indefinitely.
    3. It restores the table only if its data files were never written by Databricks.
    4. It recreates the table structure only, because the dropped data files cannot be recovered.

    Explanation

    UNDROP TABLE reverses a drop of a managed table while the table is still inside the retention window, restoring the same name and its data. Once the retention period expires or the data is purged, recovery is no longer possible. External tables are outside this mechanism because their files are never deleted by the drop.

  38. Question 38Managing Data

    A scheduled COPY INTO loads CSV files into a Delta table. Newly arriving files contain an extra column that does not exist in the target. Which option lets COPY INTO add that column instead of failing?

    1. COPY_OPTIONS ('inferSchema' = 'true')
    2. COPY_OPTIONS ('mergeSchema' = 'true') Correct answer.
    3. COPY_OPTIONS ('evolveSchema' = 'true')
    4. TBLPROPERTIES ('autoMerge' = 'true')

    Explanation

    In COPY INTO, schema evolution is enabled with mergeSchema = 'true' inside COPY_OPTIONS, which adds newly detected columns to the target table. inferSchema controls type inference for the source files rather than adding columns to the target. evolveSchema and autoMerge are not recognized COPY INTO options.

  39. Question 39Importing Data

    In Databricks SQL, which table-valued function lets you query files stored in a Unity Catalog volume directly, without creating a table first?

    1. read_files Correct answer.
    2. DESCRIBE EXTENDED
    3. COPY INTO
    4. LOAD DATA

    Explanation

    read_files is a table-valued function that reads files from a volume or cloud storage path and returns a table you can select from, including inside a CREATE TABLE AS SELECT statement. It accepts format-specific options such as header or delimiter directly as named parameters.

  40. Question 40Importing Data

    A nightly job runs COPY INTO against a landing directory where new files keep arriving. What happens to files that were already loaded by the previous run of the same command?

    1. They are skipped, since those file names were already recorded. Correct answer.
    2. They cause the command to fail with a duplicate-file error.
    3. They are redirected into a separate quarantine table.
    4. They are loaded again, producing duplicate rows in the target table.

    Explanation

    COPY INTO is idempotent for a given target table: it keeps track of the source files it has already committed and skips them on later runs unless a file has changed. This lets you point the same statement at a growing directory and load only the new arrivals.

  41. Question 41Importing Data

    You call read_files on a CSV file in a Unity Catalog volume and want the first line of the file to become the column names. Which named argument should you pass?

    1. firstRowAsHeader => true
    2. inferSchema => true
    3. skipRows => 1
    4. header => true Correct answer.

    Explanation

    read_files accepts the same option names as Spark's CSV reader, passed as named arguments, and header set to true treats the first row as the column names. Without it, the header line is read as a data row and columns get generated names such as _c0.

  42. Question 42Importing Data

    Which clause can be appended to a COPY INTO statement so that it reports data-quality problems without writing any rows into the target table?

    1. EXPLAIN
    2. DRY RUN
    3. PREVIEW
    4. VALIDATE Correct answer.

    Explanation

    COPY INTO ... VALIDATE returns the rows that would fail to load along with the reason for each failure, and nothing is committed to the table. It is typically used to check a new source before switching a production pipeline over to it.

  43. Question 43Importing Data

    When you ingest files incrementally with Auto Loader (the cloudFiles source), which setting records the state of which files have already been processed?

    1. cloudFiles.format
    2. checkpointLocation Correct answer.
    3. schemaLocation
    4. cloudFiles.includeExistingFiles

    Explanation

    Auto Loader persists its progress in the streaming checkpoint, so checkpointLocation on the write must point to durable storage the pipeline can resume from. If the checkpoint is deleted or changed, the stream forgets which files it consumed.

  44. Question 44Importing Data

    A table is created with CREATE TABLE followed by a LOCATION pointing at an existing cloud storage path. What happens to the files at that path when the table is dropped?

    1. They remain in place, since the table is external. Correct answer.
    2. They become read-only until the table is recreated.
    3. They are moved into the schema's managed storage location.
    4. They are deleted along with the table definition.

    Explanation

    A table with an explicit LOCATION outside managed storage is an external table, so dropping it removes only the metastore metadata. Managed tables, which have no LOCATION of their own and use the schema's managed storage, do have their data files removed on drop.

  45. Question 45Importing Data

    Which Databricks capability lets an analyst query a remote PostgreSQL database in place, without first copying its tables into Delta?

    1. Delta Sharing with an external recipient
    2. Lakehouse Federation via a UC connection Correct answer.
    3. Auto Loader reading the files with cloudFiles
    4. COPY INTO on a Delta target table

    Explanation

    Lakehouse Federation uses a Unity Catalog connection plus a foreign catalog to expose tables from an external database, and queries are pushed down to that system. This avoids duplicating data into the lakehouse when you only need read access.

  46. Question 46Importing Data

    You run COPY INTO from CSV files with inferSchema enabled but have not supplied a schema location. What must you add for the statement to work?

    1. It must be persisted in a schema location. Correct answer.
    2. All target columns must be declared as STRING.
    3. A checkpoint location must be set on the table.
    4. Inference is not supported for CSV files.

    Explanation

    When COPY INTO infers the schema, Databricks requires a schema location so the inferred schema can be saved and reused consistently across runs. Without it, later runs could infer a different schema from a different batch of files and produce inconsistent results.

  47. Question 47Importing Data

    Which statement about a Unity Catalog managed volume is accurate?

    1. Managed volumes can only store Delta Lake table data.
    2. A managed volume can be created without belonging to a schema.
    3. Files in a managed volume must be read via a DBFS mount path.
    4. Dropping a managed volume deletes the data files it holds. Correct answer.

    Explanation

    A managed volume keeps its files inside the schema's managed storage location, so dropping the volume also removes those files. An external volume points to a storage location you control, and its files survive the volume being dropped.

  48. Question 48Importing Data

    A pipeline must ingest millions of small JSON files that arrive continuously in cloud storage, and new fields keep appearing in the JSON. Which ingestion approach fits these requirements best?

    1. An hourly CREATE TABLE AS SELECT using read_files over the directory.
    2. An Auto Loader stream with schema evolution enabled. Correct answer.
    3. A nightly COPY INTO job that re-lists the entire landing directory.
    4. Manually uploading the files into a managed volume before each query.

    Explanation

    Auto Loader incrementally discovers new files using file notifications or directory listing, scales to very large file counts, and can add newly appearing fields to the target schema as it goes. Re-reading the whole directory on a schedule reprocesses data and does not scale to millions of files per day.

  49. Question 49Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which SQL warehouse type runs its compute resources in the Databricks cloud account instead of in your own cloud account?

    1. Classic
    2. Pro
    3. Photon
    4. Serverless Correct answer.

    Explanation

    Serverless SQL warehouses run in the Databricks account, so Databricks manages the underlying instances and you are billed per usage rather than for provisioned VMs. Classic and Pro warehouses run in your own cloud account's subscription. Photon is a query engine, not a warehouse type.

  50. Question 50Executing queries using Databricks SQL and Databricks SQL Warehouses

    Databricks SQL keeps a cache of results to speed up repeated execution. When is a cached result reused?

    1. When the same query is rerun and the tables it reads have not changed. Correct answer.
    2. Whenever the warehouse has been running for more than 24 hours.
    3. Only when the query is executed from a dashboard rather than the editor.
    4. Only when the previous results were downloaded to a local file.

    Explanation

    Databricks SQL caches the results of a query and reuses them if the same query runs again and the underlying data has not been modified. If the source tables change, the cached result is no longer valid and the query is re-executed. The cache is not tied to the editor, dashboard, or download activity.

  51. Question 51Executing queries using Databricks SQL and Databricks SQL Warehouses

    A SQL warehouse is configured with an auto-stop of 10 minutes. What happens after 10 minutes with no activity?

    1. The warehouse scales down to its minimum number of clusters.
    2. The warehouse stops accepting new queries but keeps the compute running.
    3. The warehouse's compute is shut down, and the next query triggers a restart. Correct answer.
    4. The warehouse definition is permanently deleted.

    Explanation

    Auto-stop shuts down the warehouse's compute after the configured idle period to avoid ongoing charges. The warehouse definition and its settings remain, and the next query starts the compute again, which may add startup latency. Scaling to a minimum cluster count is governed by auto-scaling, not auto-stop.

  52. Question 52Executing queries using Databricks SQL and Databricks SQL Warehouses

    In the Databricks SQL editor, which syntax references a named query parameter called region?

    1. $region
    2. @region
    3. :region Correct answer.
    4. {{region}}

    Explanation

    Databricks SQL uses named parameter markers with a colon prefix, such as :region, and the editor prompts for a value or lets you apply a widget. The value is bound at execution time rather than interpolated into the SQL text. Other prefixes are not recognised as parameter markers in this editor.

  53. Question 53Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which statement returns the contents of the Delta table sales as of a specific table version, 12?

    1. SELECT * FROM sales VERSION AS OF 12 Correct answer.
    2. SELECT * FROM sales WHEN VERSION = 12
    3. SELECT * FROM sales AT VERSION 12
    4. SELECT * FROM sales AS OF VERSION 12

    Explanation

    Delta time travel uses the clause VERSION AS OF <n> for a version number and TIMESTAMP AS OF <ts> for a point in time. The clause follows the table reference in the FROM clause. Alternate phrasings such as AT VERSION or AS OF VERSION are not valid syntax.

  54. Question 54Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which clause filters rows based on the result of a window function without requiring a subquery?

    1. WHERE
    2. LIMIT
    3. HAVING
    4. QUALIFY Correct answer.

    Explanation

    QUALIFY evaluates after window functions are computed, so it can filter directly on values such as ROW_NUMBER() or RANK(). WHERE runs before windowing and HAVING applies to grouped aggregates. Using QUALIFY avoids wrapping the query in a subquery.

  55. Question 55Executing queries using Databricks SQL and Databricks SQL Warehouses

    Where can an analyst review previously executed queries along with duration, status, and the user who ran them?

    1. In the SQL warehouse driver logs only.
    2. In the Delta transaction log of each queried table.
    3. In the Query History page of the Databricks workspace. Correct answer.
    4. In the cloud storage bucket backing the metastore.

    Explanation

    Query History lists executed statements with their status, duration, user, and warehouse, and links to details such as the query profile. It is the standard place to investigate slow or failed queries. Storage buckets and Delta logs hold data and metadata, not query execution records.

  56. Question 56Executing queries using Databricks SQL and Databricks SQL Warehouses

    What is the primary effect of increasing a SQL warehouse's cluster size from Small to Large?

    1. It extends the auto-stop idle timeout.
    2. It adds compute resources, such as cores and memory, to each cluster. Correct answer.
    3. It increases the number of clusters available for concurrent queries.
    4. It raises the maximum number of rows the query can return.

    Explanation

    Cluster size determines the resources of an individual cluster, so a larger size gives each query more cores and memory. Adding more clusters to handle concurrency is the job of the auto-scaling min/max cluster settings. Size does not affect auto-stop timing or result row limits.

  57. Question 57Executing queries using Databricks SQL and Databricks SQL Warehouses

    A warehouse is configured with a minimum of 1 cluster and a maximum of 4 clusters. What does this configuration control?

    1. How many warehouses may share a single metastore.
    2. How many clusters the warehouse can start to serve concurrent query load. Correct answer.
    3. How many users can be granted access to the warehouse.
    4. How many rows each cluster keeps in memory.

    Explanation

    The min/max cluster range drives auto-scaling: the warehouse starts additional clusters as concurrency grows and shuts them down as load drops, never going below the minimum or above the maximum. It is unrelated to metastore sharing, memory row counts, or permission grants.

  58. Question 58Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which statement about Photon on Databricks SQL warehouses is accurate?

    1. Photon is a vectorized query engine that accelerates compatible SQL workloads. Correct answer.
    2. Photon is available only on classic warehouses and is disabled on serverless warehouses.
    3. Photon replaces Delta Lake storage with an in-memory columnar format.
    4. Photon must be enabled table by table when each table is created.

    Explanation

    Photon is a native vectorized execution engine written in C++ that speeds up scans, joins, and aggregations for supported operations. It is enabled at the compute level rather than per table, and it is used on Pro and serverless warehouses. Storage remains Delta Lake; Photon changes how data is processed, not how it is stored.

  59. Question 59Executing queries using Databricks SQL and Databricks SQL Warehouses

    In a MERGE INTO statement, which clause inserts source rows that have no matching row in the target table?

    1. WHEN MATCHED THEN DELETE
    2. WHEN NOT MATCHED THEN INSERT Correct answer.
    3. WHEN MATCHED THEN UPDATE
    4. WHEN NOT MATCHED BY SOURCE THEN DELETE

    Explanation

    MERGE INTO evaluates each source row against the target; WHEN NOT MATCHED branches handle source rows with no target match and typically perform the insert. WHEN MATCHED branches handle existing target rows. WHEN NOT MATCHED BY SOURCE addresses target rows that have no counterpart in the source.

  60. Question 60Executing queries using Databricks SQL and Databricks SQL Warehouses

    What happens when COPY INTO is executed a second time against the same set of source files and the same target table?

    1. Files that were already loaded are skipped, so rows are not duplicated. Correct answer.
    2. The second execution fails with a duplicate key error.
    3. The file contents are inserted again, producing duplicate rows.
    4. The target table is overwritten so that it contains only the source files.

    Explanation

    COPY INTO is idempotent for a Delta target: it tracks which files have already been loaded and skips them on subsequent runs, so retries do not create duplicates. This makes it safe to rerun on a source location that still contains previously ingested files. It appends new data rather than overwriting the table.

  61. Question 61Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which statement creates a new Delta table populated with the results of a SELECT query?

    1. CREATE TABLE t AS SELECT ... Correct answer.
    2. COPY INTO t FROM SELECT ...
    3. INSERT INTO t SELECT ...
    4. ALTER TABLE t AS SELECT ...

    Explanation

    CREATE TABLE ... AS SELECT (CTAS) defines a new table and fills it with the query's result in a single statement. INSERT INTO requires the table to exist already and only appends rows. ALTER TABLE changes table properties, and COPY INTO loads files rather than query results.

  62. Question 62Executing queries using Databricks SQL and Databricks SQL Warehouses

    What is the scope of a view created with CREATE OR REPLACE TEMP VIEW?

    1. It exists only within the current session, such as a notebook or SQL editor session. Correct answer.
    2. It is persisted to the cloud storage location of the schema.
    3. It is available to every warehouse in the workspace.
    4. It is available to all users of the metastore until it is dropped.

    Explanation

    A temporary view is scoped to the session that created it and disappears when that session ends. It is not registered in the metastore, so other users and sessions cannot see it. Persistent views, by contrast, are catalog objects stored with the schema.

  63. Question 63Executing queries using Databricks SQL and Databricks SQL Warehouses

    A query profile shows that one scan stage reads many more rows and bytes than the statement finally returns. What is the most direct improvement to investigate first?

    1. Add an outer LIMIT clause to the statement.
    2. Check whether partition filters or data-skipping predicates can prune the files the scan reads. Correct answer.
    3. Increase the auto-stop timeout of the warehouse.
    4. Convert the query into a scheduled job.

    Explanation

    When a scan reads far more data than it emits, most time is spent on I/O that could be avoided. Filters on partition columns and columns with statistics allow file and row-group skipping, reducing bytes read. A LIMIT affects only the final output and does not shrink the scan.

  64. Question 64Executing queries using Databricks SQL and Databricks SQL Warehouses

    What is the purpose of attaching a query tag to a statement?

    1. It routes the statement to a dedicated warehouse.
    2. It attaches a label used to attribute usage and cost in query history and system tables. Correct answer.
    3. It forces the result cache to be bypassed for that statement.
    4. It switches the statement to a different execution engine.

    Explanation

    Query tags are free-form labels attached at execution time that flow into query history and billing and usage system tables. Teams use them to attribute spend and activity to a workload, team, or application. They do not change the engine, the cache behaviour, or the routing of the query.

  65. Question 65Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which Databricks SQL feature runs a saved query on a recurring schedule and can deliver results to recipients?

    1. A parameter marker
    2. A query snippet
    3. A temporary view
    4. A scheduled query Correct answer.

    Explanation

    A schedule (scheduled query) executes a saved query at defined intervals, optionally refreshing a dashboard or sending results by email. It depends on a SQL warehouse being available at run time. Snippets, parameters, and temp views are authoring conveniences, not schedulers.

  66. Question 66Executing queries using Databricks SQL and Databricks SQL Warehouses

    An alert is built on a query that returns a single numeric value. What determines whether the alert fires?

    1. The number of rows shown in the alert's visualization.
    2. The auto-stop setting of the associated warehouse.
    3. A condition comparing the query's value to a threshold, evaluated on a schedule. Correct answer.
    4. The size of the warehouse the alert runs on.

    Explanation

    An alert pairs a query with a condition, such as a value exceeding a threshold, and evaluates that condition each time the schedule triggers. When the condition is met, notifications are sent to configured destinations. Warehouse sizing and auto-stop influence performance and cost, not the alert logic.

  67. Question 67Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which requirement is specific to using serverless SQL warehouses?

    1. The warehouse must be manually started before every query.
    2. All queried tables must be external rather than managed.
    3. The workspace must have Unity Catalog enabled and serverless compute available in its region. Correct answer.
    4. Queries must be authored in Python rather than SQL.

    Explanation

    Serverless SQL warehouses are provisioned in the Databricks account, which requires the workspace to be enabled for Unity Catalog and the region to offer serverless compute. They start and scale automatically, so manual starts are unnecessary. Tables can be managed or external.

  68. Question 68Executing queries using Databricks SQL and Databricks SQL Warehouses

    A client retrieves a very large result set through the Statement Execution API. What must the client typically do?

    1. Page through the results by fetching successive chunks until the result set is exhausted. Correct answer.
    2. Request a download of the query result cache.
    3. Convert the statement into a dashboard first.
    4. Resize the warehouse to 2X-Large before submitting the statement.

    Explanation

    The Statement Execution API returns an initial portion of results inline along with metadata describing additional chunks. The client fetches each chunk by index, often through external links, until all chunks are consumed. Warehouse size and dashboards play no role in retrieving API results.

  69. Question 69Executing queries using Databricks SQL and Databricks SQL Warehouses

    How does the Delta cache differ from the query result cache?

    1. Both store only table metadata and never row data.
    2. The Delta cache applies only to temporary views.
    3. The Delta cache stores query outputs, while the result cache stores Parquet data files.
    4. The Delta cache keeps local copies of remote table data, while the result cache keeps complete outputs of previously run queries. Correct answer.

    Explanation

    The Delta cache copies remote object-store data onto local SSD on the worker nodes so repeated scans read faster. The result cache, by contrast, stores the finished output of a query and returns it directly when the same query is rerun on unchanged data. They operate at different layers and are both transparent to the user.

  70. Question 70Executing queries using Databricks SQL and Databricks SQL Warehouses

    What does OPTIMIZE table ZORDER BY (customer_id) accomplish?

    1. It compacts small files and co-locates related data to improve data skipping on that column. Correct answer.
    2. It rewrites the table so each partition holds exactly one file.
    3. It deletes rows that no longer satisfy a predicate.
    4. It creates a materialized view keyed on the column.

    Explanation

    OPTIMIZE compacts small files into larger ones, and ZORDER adds multi-dimensional clustering so rows with similar values land in the same files. Later queries that filter on the ZORDER columns can skip more files. It does not delete rows or create views.

  71. Question 71Executing queries using Databricks SQL and Databricks SQL Warehouses

    Time travel on a Delta table fails beyond a certain point in the past. Which table settings govern how long the old versions remain queryable?

    1. The query result cache time-to-live
    2. The table's data and log retention duration properties, which default to seven and thirty days Correct answer.
    3. The warehouse auto-stop timeout
    4. The ZORDER columns of the table

    Explanation

    Delta retains deleted files and transaction log entries according to the retention duration table properties, which default to seven days for data files and thirty days for log entries. Time travel beyond that window fails because the files or log entries have been removed. VACUUM physically removes files past the data retention threshold.

  72. Question 72Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which permission allows a user to submit queries on a specific SQL warehouse?

    1. SELECT on the warehouse
    2. CREATE on the warehouse
    3. MODIFY on the warehouse
    4. CAN USE on the warehouse Correct answer.

    Explanation

    SQL warehouses use the CAN USE and CAN MANAGE permission levels; CAN USE lets a user run queries on the warehouse without changing it. SELECT, MODIFY, and CREATE are data privileges granted on tables and schemas. A user needs both CAN USE on the warehouse and the appropriate privileges on the data.

  73. Question 73Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which function assigns a unique sequential number to each row within a window, regardless of ties in the ordering column?

    1. NTILE()
    2. ROW_NUMBER() Correct answer.
    3. RANK()
    4. DENSE_RANK()

    Explanation

    ROW_NUMBER() always produces distinct consecutive integers for the rows in the window, even when the ORDER BY values are duplicated. RANK() assigns equal values to ties and then skips numbers, while DENSE_RANK() assigns equal values without skipping. NTILE() divides rows into a fixed number of buckets.

  74. Question 74Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which SQL command returns the optimized logical plan for a query without actually executing it?

    1. EXPLAIN Correct answer.
    2. PROFILE
    3. SHOW PLAN
    4. DESCRIBE

    Explanation

    EXPLAIN is the statement that compiles a query and returns its plan instead of running it. DESCRIBE reports metadata such as columns or table properties rather than a query plan. Databricks SQL exposes query plans in the query profile UI, but there is no standalone PROFILE statement.

  75. Question 75Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which SQL warehouse setting controls how long the warehouse stays running after it processes its final query before shutting down?

    1. Auto scaling
    2. Auto stop Correct answer.
    3. Photon
    4. Serverless

    Explanation

    The Auto stop setting defines the idle period the warehouse waits after the last query before it is stopped. Auto scaling changes the number of clusters, not the idle timeout. Photon and Serverless are execution/warehouse-type choices and do not set the idle shutdown period.

  76. Question 76Executing queries using Databricks SQL and Databricks SQL Warehouses

    An analyst needs read-only access to a single Unity Catalog table named sales.orders. Which statement grants exactly that access to the group analysts?

    1. GRANT USAGE ON TABLE sales.orders TO analysts
    2. GRANT ALL PRIVILEGES ON TABLE sales.orders TO analysts
    3. GRANT MODIFY ON TABLE sales.orders TO analysts
    4. GRANT SELECT ON TABLE sales.orders TO analysts Correct answer.

    Explanation

    SELECT is the privilege that allows reading data from a table without permitting changes. MODIFY allows writes and ALL PRIVILEGES grants a broader bundle that exceeds read-only access. USAGE is not a table-level privilege in Unity Catalog, so that statement is not valid for a table.

  77. Question 77Executing queries using Databricks SQL and Databricks SQL Warehouses

    A query saved in the Databricks SQL editor contains WHERE region = :region_name. What happens when an analyst runs it?

    1. A session variable named region_name is bound only if it was declared with SET.
    2. The statement fails because a colon cannot appear in a Databricks SQL expression.
    3. The text ':region_name' is compared literally against the region column.
    4. The editor prompts for a value and binds it to region_name. Correct answer.

    Explanation

    A leading colon marks a parameter marker, and the SQL editor prompts for a value at run time so the same saved query can be reused with different inputs. The marker is not treated as a literal string, and no separate DECLARE or SET statement is needed for the prompt to appear.

  78. Question 78Executing queries using Databricks SQL and Databricks SQL Warehouses

    A user has been granted SELECT on main.sales.orders but a query against that fully qualified name fails with an insufficient privileges error. Which additional privileges resolve the problem?

    1. BROWSE on main.sales and USAGE on the orders table
    2. USE CATALOG on main and USE SCHEMA on main.sales Correct answer.
    3. MODIFY on main.sales and CREATE on main
    4. SELECT on main.sales and USE SCHEMA on main

    Explanation

    In Unity Catalog, resolving a three-part name requires USE CATALOG on the catalog and USE SCHEMA on the schema in addition to the object privilege. Without those traversal privileges the user cannot reach the table even though SELECT is granted on it. The other combinations either name non-applicable privileges or omit the required catalog-level right.

  79. Question 79Executing queries using Databricks SQL and Databricks SQL Warehouses

    A SQL warehouse runs with ANSI mode enabled. What is the result of evaluating the expression 10 / 0 in a SELECT list?

    1. It fails with an error such as DIVIDE_BY_ZERO. Correct answer.
    2. It returns NULL and writes a warning to the driver log.
    3. It returns the special value Infinity.
    4. It returns 0 because the divisor is treated as 1.

    Explanation

    With ANSI mode enabled, invalid arithmetic operations raise errors instead of silently producing NULL, so division by zero fails the statement. Null-returning behavior applies when ANSI mode is disabled. Infinity and implicit substitution of the divisor are not produced by Databricks SQL.

  80. Question 80Executing queries using Databricks SQL and Databricks SQL Warehouses

    A Delta table currently holds 1,000 rows. An INSERT OVERWRITE statement targeting that table selects 200 rows. What does the table contain afterward?

    1. 1,200 rows, because the new rows are appended to the existing data
    2. 800 rows, because only rows matching the new data are replaced
    3. 1,000 rows, because the statement is rejected unless the table is empty
    4. 200 rows, because the existing data is replaced by the query result Correct answer.

    Explanation

    INSERT OVERWRITE replaces the existing contents of the table, or of the specified partitions, with the rows produced by the query. For a non-partitioned table this means the previous 1,000 rows are removed and only the 200 new rows remain. Appending behavior is provided by a plain INSERT INTO.

  81. Question 81Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which query returns the contents of the Delta table events as they existed at version 7?

    1. SELECT * FROM events VERSION 7
    2. SELECT * FROM events TIMESTAMP AS OF 7
    3. SELECT * FROM events VERSION AS OF 7 Correct answer.
    4. SELECT * FROM events AT VERSION 7

    Explanation

    Delta time travel accepts either TIMESTAMP AS OF for a point in time or VERSION AS OF for a specific table version. The version form requires the full VERSION AS OF clause; TIMESTAMP AS OF expects a timestamp expression rather than a version number. The remaining syntaxes are not valid time travel clauses.

  82. Question 82Executing queries using Databricks SQL and Databricks SQL Warehouses

    A table has a STRING column payload that holds JSON text such as {"id": 42}. Which expression returns the field id as a BIGINT?

    1. payload:id
    2. CAST(payload AS BIGINT)
    3. get_json_object(payload, 'id')
    4. from_json(payload, 'id BIGINT').id Correct answer.

    Explanation

    from_json parses the JSON string using the supplied schema and returns a struct whose fields can be referenced with dot notation, giving a BIGINT directly. get_json_object extracts a value but always returns a STRING, and the colon operator applies to struct or VARIANT values rather than to raw JSON text. Casting the whole payload to BIGINT is not a valid conversion of a JSON document.

  83. Question 83Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which statement returns approximately 10 percent of a table's rows in a single query?

    1. SELECT * FROM t SAMPLE 0.1 ROWS
    2. SELECT * FROM t LIMIT 10 PERCENT
    3. SELECT TOP 10 PERCENT * FROM t
    4. SELECT * FROM t TABLESAMPLE (10 PERCENT) Correct answer.

    Explanation

    TABLESAMPLE with a PERCENT clause samples a fraction of the table's data rather than returning a fixed row count. LIMIT caps the number of rows and does not accept a PERCENT qualifier, and TOP n PERCENT is not Databricks SQL syntax. SAMPLE 0.1 ROWS would request a fractional number of rows and is not the percentage form.

  84. Question 84Executing queries using Databricks SQL and Databricks SQL Warehouses

    Which statement enables liquid clustering on an existing Delta table using the column customer_id?

    1. CREATE CLUSTERED INDEX ON tbl (customer_id)
    2. OPTIMIZE tbl ZORDER BY (customer_id)
    3. ALTER TABLE tbl CLUSTER BY (customer_id) Correct answer.
    4. ALTER TABLE tbl SET TBLPROPERTIES (clusterBy = 'customer_id')

    Explanation

    Liquid clustering is declared with the CLUSTER BY clause on CREATE TABLE or ALTER TABLE. ZORDER is a data layout operation applied during OPTIMIZE and is a different mechanism from clustering keys. Setting a table property or creating a clustered index is not how clustering keys are defined in Databricks SQL.

  85. Question 85Executing queries using Databricks SQL and Databricks SQL Warehouses

    A query profile shows a BroadcastHashJoin between a large fact table and a small dimension table. What does this indicate about the execution?

    1. Both inputs were fully shuffled across all nodes before joining.
    2. The join produced more rows than either input, indicating a fan-out.
    3. The dimension table was skipped because cached results were reused.
    4. The small table was broadcast, so the large table was not shuffled. Correct answer.

    Explanation

    In a broadcast hash join the smaller input is collected and sent to every executor, letting each task join its local partition of the large table without a shuffle. A full shuffle of both sides describes a shuffle hash or sort-merge join. Row fan-out and cache reuse are unrelated to the presence of a broadcast exchange.

  86. Question 86Executing queries using Databricks SQL and Databricks SQL Warehouses

    A MERGE INTO statement fails with an error reporting that multiple source rows matched a target row. Which condition produces this error?

    1. The WHEN MATCHED clause includes both an UPDATE and a DELETE.
    2. Two or more source rows match the same target row. Correct answer.
    3. The target table has duplicate join keys, so the join fans out.
    4. The source and target tables list columns in different orders.

    Explanation

    Delta requires that each target row match at most one source row, because the outcome of several competing updates would be ambiguous. The fix is usually to deduplicate the source with a window function before merging. Duplicate keys in the target, column ordering, and combining update and delete actions do not raise this particular error.

  87. Question 87Executing queries using Databricks SQL and Databricks SQL Warehouses

    A query computes SUM(amount) OVER (PARTITION BY acct ORDER BY ts) with no explicit frame clause. Which rows contribute to each output value?

    1. All rows in the partition, regardless of position or ordering.
    2. Only the row currently being evaluated, with no accumulation.
    3. All rows in the partition from the start up to the current row's ts value. Correct answer.
    4. The current row plus exactly the three rows preceding it in ts order.

    Explanation

    When an ORDER BY is present inside OVER and no frame is specified, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, giving a running total that also includes peer rows sharing the current ordering value. Without the ORDER BY the whole partition is used instead. Explicit ROWS frames are needed for fixed-size sliding windows.

  88. Question 88Executing queries using Databricks SQL and Databricks SQL Warehouses

    A Delta table is partitioned by the column dt. Which predicate on dt still allows the optimizer to prune partitions?

    1. WHERE year(dt) = 2024 AND month(dt) = 5
    2. WHERE substring(cast(dt as string), 1, 7) = '2024-05'
    3. WHERE dt >= '2024-05-01' AND dt < '2024-06-01' Correct answer.
    4. WHERE date_format(dt, 'yyyy-MM') = '2024-05'

    Explanation

    Partition pruning works when the partition column is compared directly against constants, as in a range predicate on dt. Wrapping the column in a function such as date_format, year, or substring hides the raw value from the pruning logic, so the engine must scan every partition and filter afterward. Rewriting a date filter as a range keeps the column bare and enables pruning.

  89. Question 89Analyzing Queries

    A dashboard must show monthly revenue as a continuous trend across two years. Which visualization type is most appropriate?

    1. Scatter plot
    2. Line chart Correct answer.
    3. Pie chart
    4. Counter

    Explanation

    Line charts plot a measure against an ordered axis such as time, making trends and seasonality easy to see. Pie charts compare parts of a whole at one point in time, and counters show a single value.

  90. Question 90Analyzing Queries

    Which visualization type displays one aggregated number, such as total active users for the day?

    1. Table
    2. Heatmap
    3. Sankey
    4. Counter Correct answer.

    Explanation

    A counter visualization renders a single scalar value produced by the query, which is ideal for KPIs and headline metrics. Tables and heatmaps show multiple rows or cells rather than one number.

  91. Question 91Analyzing Queries

    A dashboard contains three charts that should all filter to the same region chosen by the viewer. What should be used?

    1. Parameter Correct answer.
    2. Refresh schedule
    3. Alert rule
    4. Query snippet

    Explanation

    A dashboard parameter lets the viewer choose a value once, and every visualization that references the parameter is filtered by that selection. Snippets and schedules do not propagate a viewer choice across charts.

  92. Question 92Analyzing Queries

    Which SQL clause restricts the number of rows returned by a query?

    1. GROUP BY
    2. LIMIT Correct answer.
    3. WHERE
    4. HAVING

    Explanation

    LIMIT caps the number of rows in the result set after the rest of the query is evaluated. WHERE and HAVING filter rows by a condition, and GROUP BY aggregates rows into groups.

  93. Question 93Analyzing Queries

    Which statement reads version 4 of the Delta table sales?

    1. SELECT * FROM sales FOR VERSION 4
    2. SELECT * FROM sales AT VERSION 4
    3. SELECT * FROM sales VERSION AS OF 4 Correct answer.
    4. SELECT * FROM sales TIMESTAMP AS OF 4

    Explanation

    Delta Lake time travel accepts either VERSION AS OF followed by a version number or TIMESTAMP AS OF followed by a timestamp expression. The other phrasings are not valid time travel syntax.

  94. Question 94Analyzing Queries

    Which command lists the operations recorded in a Delta table's transaction log?

    1. DESCRIBE EXTENDED sales
    2. SHOW PARTITIONS sales
    3. DESCRIBE HISTORY sales
    4. DESCRIBE DETAIL sales Correct answer.

    Explanation

    DESCRIBE HISTORY returns the table's commits with operation type, timestamp, user, and metrics, which is useful for auditing changes. DESCRIBE DETAIL returns storage-level information such as location and file counts.

  95. Question 95Analyzing Queries

    A query filters frequently on customer_id, which is not a partition column. Which command improves data skipping for that filter?

    1. OPTIMIZE sales ZORDER BY (customer_id) Correct answer.
    2. VACUUM sales
    3. ANALYZE TABLE sales COMPUTE STATISTICS
    4. OPTIMIZE sales

    Explanation

    Z-ORDER clusters related values of the specified columns into the same files during OPTIMIZE, so file-level min/max statistics can skip more data for predicates on those columns. Plain OPTIMIZE only compacts files without improving clustering on customer_id.

  96. Question 96Analyzing Queries

    What is the default retention period for VACUUM on a Delta table?

    1. 90 days
    2. 7 days Correct answer.
    3. 24 hours
    4. 30 days

    Explanation

    VACUUM deletes data files that are no longer referenced and older than the retention threshold, which defaults to seven days. The threshold can be changed through the deletedFileRetentionDuration table property.

  97. Question 97Analyzing Queries

    When can a Databricks SQL warehouse serve results from the result cache?

    1. The query is run under a different user account
    2. The warehouse was restarted since the previous run
    3. The query and table data are unchanged Correct answer.
    4. The query uses a different filter on the same tables

    Explanation

    Result caching returns a previously computed result only when the same query text is submitted and the underlying table data has not changed. A modified filter, a warehouse restart, or a different query text forces recomputation.

  98. Question 98Analyzing Queries

    What does the query profile in Databricks SQL display?

    1. The table's partition layout
    2. Operator-level metrics such as rows read Correct answer.
    3. The list of users who ran the query
    4. The Delta transaction log of the table

    Explanation

    The query profile breaks a query into operators and shows metrics such as rows read, rows output, time spent, and spilling. These metrics help diagnose skew, excessive shuffles, and expensive scans.

  99. Question 99Analyzing Queries

    A Databricks SQL alert is configured with a condition query. When is a notification triggered?

    1. When the warehouse scales up
    2. When the condition query returns a row Correct answer.
    3. When the query fails to compile
    4. When a new dashboard is created

    Explanation

    An alert evaluates its condition query on a schedule and fires when the condition is satisfied, which is expressed as the query returning a row. Warehouse scaling and dashboard creation are unrelated events.

  100. Question 100Analyzing Queries

    Which clause turns columns into rows?

    1. PIVOT
    2. TRANSPOSE
    3. UNPIVOT Correct answer.
    4. FLATTEN

    Explanation

    UNPIVOT converts a wide result, where categories appear as columns, into a long result where each category becomes a row. PIVOT performs the opposite transformation.

  101. Question 101Analyzing Queries

    Which SQL construct defines a named temporary result set at the start of a query?

    1. CREATE VIEW
    2. WITH clause Correct answer.
    3. MERGE INTO
    4. INSERT INTO

    Explanation

    A common table expression introduced by WITH names a subquery that the main statement can reference, improving readability and allowing reuse within the same statement. Views are persistent objects rather than part of a single statement.

  102. Question 102Analyzing Queries

    What does EXPLAIN return for a query?

    1. The table schema
    2. The logical and physical plan Correct answer.
    3. The query result set
    4. The list of cached tables

    Explanation

    EXPLAIN shows how the optimizer will execute a statement, including the analyzed logical plan and the physical plan with joins and scans. It does not execute the query to produce rows.

  103. Question 103Analyzing Queries

    Which Databricks engine accelerates SQL and DataFrame operations using vectorized execution?

    1. Unity Catalog
    2. Photon Correct answer.
    3. Auto Loader
    4. Delta Live Tables

    Explanation

    Photon is a vectorized query engine that speeds up SQL and DataFrame workloads on Databricks by processing data in batches and using native code. The other options are ingestion, governance, and pipeline products.

  104. Question 104Analyzing Queries

    Which statement about Z-ORDER is correct?

    1. Z-ORDER requires a partitioned table
    2. Z-ORDER cannot include partition columns Correct answer.
    3. Z-ORDER works only on views
    4. Z-ORDER permanently deletes old files

    Explanation

    Partition columns already determine directory layout, so Z-ORDER rejects them as clustering keys and expects data columns instead. A table does not need to be partitioned to use Z-ORDER.

  105. Question 105Analyzing Queries

    How does Delta Lake data skipping reduce the number of files read for a filtered query?

    1. By caching every file in memory
    2. By rewriting predicates to use indexes
    3. By compressing files more aggressively
    4. Using per-file min/max statistics Correct answer.

    Explanation

    Delta Lake records statistics such as minimum and maximum values per file, so the engine can exclude files whose ranges cannot satisfy the predicate. Compression reduces size but does not by itself eliminate files from a scan.

  106. Question 106Analyzing Queries

    What is the primary effect of running OPTIMIZE on a Delta table?

    1. Adding a new partition column
    2. Dropping old table versions
    3. Compacting small files into larger ones Correct answer.
    4. Converting the table to Parquet

    Explanation

    OPTIMIZE rewrites many small files into fewer larger files, which reduces overhead for readers and can improve scan throughput. It does not change the table format or remove historical versions.

  107. Question 107Analyzing Queries

    What is a query snippet in Databricks SQL?

    1. A cluster configuration
    2. A reusable SQL fragment Correct answer.
    3. A scheduled alert
    4. A saved visualization

    Explanation

    A snippet stores a reusable piece of SQL text that can be inserted into multiple queries, which keeps shared logic consistent. Snippets are not visualizations, alerts, or compute settings.

  108. Question 108Analyzing Queries

    Which statement correctly distinguishes the result cache from the disk cache?

    1. Result cache requires Photon to be enabled
    2. Result cache holds query output; disk cache holds table data Correct answer.
    3. Disk cache holds query output; result cache holds table data
    4. Both caches store only the final query output

    Explanation

    The result cache stores the output of previously executed queries, while the disk cache keeps remote table data on local SSD so later scans read faster. They operate at different layers and can be used together.

  109. Question 109Analyzing Queries

    A scheduled query in Databricks SQL fails during a run. What happens by default?

    1. The owner is notified Correct answer.
    2. The query is deleted
    3. The warehouse restarts
    4. Results are published anyway

    Explanation

    Scheduled queries send a failure notification to the owner so the problem can be investigated. A failed run does not publish stale results or alter the query definition.

  110. Question 110Analyzing Queries

    Which visualization is designed to show the distribution of a numeric measure with quartiles and outliers?

    1. Counter
    2. Box plot Correct answer.
    3. Pivot table
    4. Sankey

    Explanation

    A box plot summarizes a distribution through its median, quartiles, and whiskers, and it highlights points outside the whiskers as outliers. Counters and pivot tables do not depict spread in this way.

  111. Question 111Analyzing Queries

    A table contains multiple rows per customer, and only the most recently updated row per customer is needed. Which approach returns exactly one row per customer?

    1. ROW_NUMBER() filtered to rn = 1 Correct answer.
    2. DISTINCT over all columns
    3. GROUP BY customer with MAX(updated_at)
    4. ORDER BY updated_at DESC LIMIT 1

    Explanation

    Assigning ROW_NUMBER() over a partition by customer ordered by updated_at descending and keeping rn = 1 selects the latest full row for each customer. GROUP BY with MAX returns the timestamp but not the other columns of that latest row.

  112. Question 112Analyzing Queries

    A query joins a large fact table to a small dimension table and the fact table cannot be filtered on the join key. Which technique can reduce shuffle during the join?

    1. Run VACUUM on the fact table
    2. Z-ORDER the fact table
    3. Use a broadcast join hint Correct answer.
    4. Query an older table version

    Explanation

    A broadcast join sends the small dimension table to every worker so the large fact table does not need to be shuffled across the network. VACUUM and time travel change storage or visibility, not join strategy.

  113. Question 113Analyzing Queries

    A query computes both RANK() and DENSE_RANK() over the same window, ordered by a column that contains duplicate values. How do the two functions differ in the numbers they assign?

    1. They return identical values; DENSE_RANK only differs in that it requires a PARTITION BY clause.
    2. RANK skips numbers after a tie, while DENSE_RANK continues with consecutive numbers. Correct answer.
    3. RANK gives every row in the partition the same number, while DENSE_RANK returns sequential row numbers.
    4. DENSE_RANK skips numbers after a tie, while RANK continues with consecutive numbers.

    Explanation

    Both functions assign the same value to tied rows, so the difference appears only in the value assigned to the next distinct row. RANK counts the tied rows and therefore leaves gaps (1, 1, 3), whereas DENSE_RANK increments by one regardless of how many rows were tied (1, 1, 2). Neither function requires a PARTITION BY clause.

  114. Question 114Analyzing Queries

    A query groups sales rows by region and must keep only the regions whose summed sales exceed 1000000. Where does that filter belong?

    1. In a QUALIFY clause that references SUM(sales), placed after the GROUP BY.
    2. In a WHERE clause that references the SUM(sales) aggregate before the GROUP BY runs.
    3. In a HAVING clause that references SUM(sales), placed after the GROUP BY. Correct answer.
    4. In a FILTER (WHERE SUM(sales) > 1000000) modifier attached to the aggregate function.

    Explanation

    WHERE evaluates individual input rows before aggregation, so it cannot test an aggregate result. HAVING evaluates after GROUP BY and is the clause designed to filter entire groups. QUALIFY filters on window function results, and FILTER (WHERE ...) restricts which rows feed an aggregate rather than removing groups.

  115. Question 115Analyzing Queries

    An analyst needs the number of distinct user_id values in a billion-row table, and an answer within a few percent of the true value is acceptable. Which approach fits that requirement best?

    1. Run SELECT COUNT(*) grouped by user_id, then sum the group counts in an outer query.
    2. Use approx_count_distinct(user_id), an estimate with bounded relative error. Correct answer.
    3. Run SELECT COUNT(DISTINCT user_id) after increasing the shuffle partition count for the job.
    4. Run COUNT(user_id) OVER (PARTITION BY user_id) and take the maximum value in an outer query.

    Explanation

    approx_count_distinct uses a probabilistic sketch whose relative error is configurable, so it answers cardinality questions with far less memory and shuffle than an exact distinct count. Exact COUNT(DISTINCT ...) requires redistributing every distinct value across the cluster. The grouped-count approach also materializes one row per distinct key, and the window approach does not produce a distinct count at all.

  116. Question 116Analyzing Queries

    A customers table has one row per customer and an orders table has many rows per customer. Which query returns customers with no matching order row?

    1. SELECT * FROM customers c INNER JOIN orders o ON c.id = o.customer_id
    2. SELECT * FROM customers c CROSS JOIN orders o WHERE o.customer_id IS NULL
    3. SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL)
    4. SELECT * FROM customers c LEFT ANTI JOIN orders o ON c.id = o.customer_id Correct answer.

    Explanation

    A LEFT ANTI JOIN keeps only left-side rows that have no match in the right side, and it never duplicates or materializes the many matching orders. An inner join returns the opposite set, and a cross join multiplied by an IS NULL predicate does not express row matching. NOT IN can produce a correct result only with NULL handling and is generally the more fragile, costlier formulation.

  117. Question 117Analyzing Queries

    A nightly query shuffles data across 2000 partitions. Its profile shows most partitions finishing almost instantly while a handful process far more data. Which statement describes how adaptive query execution improves this?

    1. It rewrites the query to broadcast the fact table so that no shuffle happens at all.
    2. It caches the shuffled data on local disk so the oversized partitions are read only once.
    3. It coalesces small shuffle partitions and splits oversized ones at runtime. Correct answer.
    4. It raises the number of shuffle partitions before execution so the largest partitions spread out.

    Explanation

    Adaptive query execution re-optimizes a plan between stages using the actual sizes observed at runtime. That includes merging adjacent small shuffle partitions and splitting partitions that are much larger than the median, which addresses both the many tiny tasks and the skew. Broadcast conversion and disk caching are separate mechanisms that do not depend on runtime partition statistics.

  118. Question 118Creating Dashboards and Visualizations in Databricks

    Which Databricks SQL visualization type displays a single aggregated value from a query result, such as total revenue for the quarter?

    1. Counter Correct answer.
    2. Heatmap
    3. Pivot
    4. Sankey

    Explanation

    A counter visualization renders one aggregated number prominently and ignores row-level detail, which makes it suitable for KPI tiles. Pivot tables, heatmaps, and Sankey diagrams all display multi-dimensional data rather than one scalar value.

  119. Question 119Creating Dashboards and Visualizations in Databricks

    A team wants to show daily active users across 90 days as a continuous trend. Which visualization type fits best?

    1. Counter
    2. Pie chart
    3. Line chart Correct answer.
    4. Pivot table

    Explanation

    Line charts plot a measure against an ordered axis, so consecutive dates are connected and the trend is easy to read. Pie charts compare parts of a whole at one point in time, and counters show only one number.

  120. Question 120Creating Dashboards and Visualizations in Databricks

    Which dashboard control lets viewers restrict the data shown in charts without editing any SQL?

    1. A refresh schedule
    2. A filter widget Correct answer.
    3. A query comment
    4. A text box

    Explanation

    Filter widgets are bound to fields used by the dashboard's queries and pass the viewer's selection into the query at run time. The result is that charts update without anyone changing the underlying SQL text.

  121. Question 121Creating Dashboards and Visualizations in Databricks

    A colleague needs read-only access to a Databricks SQL dashboard. Which permission level is sufficient?

    1. IS OWNER
    2. CAN MANAGE
    3. CAN EDIT
    4. CAN VIEW Correct answer.

    Explanation

    Permissions are granted at increasing levels, and CAN VIEW is the read-only level that lets a user open and interact with a dashboard. Higher levels such as CAN EDIT and CAN MANAGE additionally allow modification or administration.

  122. Question 122Creating Dashboards and Visualizations in Databricks

    Which visualization arranges grouped data into rows and columns with aggregated values in the cells?

    1. Pivot Correct answer.
    2. Box plot
    3. Funnel
    4. Sankey

    Explanation

    A pivot visualization puts one field on rows, another on columns, and aggregates a measure into each intersecting cell. This cross-tabulation is exactly what pivot tables provide in a spreadsheet.

  123. Question 123Creating Dashboards and Visualizations in Databricks

    Charts on a dashboard keep showing results from an earlier point in time. Which setting controls when the underlying queries run again automatically?

    1. The number format setting
    2. The refresh schedule Correct answer.
    3. The color palette setting
    4. The warehouse auto-stop setting

    Explanation

    A dashboard can carry a refresh schedule that re-executes its queries at defined intervals so viewers see current data. Display-only settings such as number formats and palettes change presentation only, and warehouse auto-stop concerns compute shutdown.

  124. Question 124Creating Dashboards and Visualizations in Databricks

    In a Databricks notebook, which call renders a DataFrame with the built-in visualization editor?

    1. df.collect()
    2. display(df) Correct answer.
    3. df.show()
    4. print(df)

    Explanation

    The display() function renders a DataFrame in the notebook output together with the visualization controls, letting you switch chart types and configure axes. The other calls return text output or Python data without the visualization interface.

  125. Question 125Creating Dashboards and Visualizations in Databricks

    Viewers must choose exactly one region name from a fixed set of values. Which filter type fits best?

    1. Range slider
    2. Text input
    3. Date range picker
    4. Dropdown list Correct answer.

    Explanation

    A dropdown list constrains the viewer to values drawn from the field, which prevents typos and invalid entries. Free-entry widgets such as text inputs allow values that may not exist in the data.

  126. Question 126Creating Dashboards and Visualizations in Databricks

    In a bar chart, which configuration places series values on top of one another instead of side by side?

    1. A percent number format
    2. Grouping by the X axis
    3. Setting stacking to Stack Correct answer.
    4. A logarithmic Y axis

    Explanation

    The stacking option determines whether series are drawn as separate grouped bars or accumulated into a single bar. Selecting Stack makes each category show one bar whose segments are the contributing series.

  127. Question 127Creating Dashboards and Visualizations in Databricks

    An analyst wants to examine whether unit price and units sold move together across products. Which visualization is most appropriate?

    1. Pie chart
    2. Counter
    3. Pivot table
    4. Scatter chart Correct answer.

    Explanation

    A scatter chart places each record at the intersection of two numeric measures, which exposes correlation and outliers. Aggregating into a pie or pivot hides the pairwise relationship between the two numbers.

  128. Question 128Creating Dashboards and Visualizations in Databricks

    On an AI/BI dashboard, a dataset query is edited to add a new filter condition. What is affected?

    1. Only the first visualization on the page
    2. All visualizations using it Correct answer.
    3. Only visualizations on the last page
    4. Nothing until the dashboard is re-imported

    Explanation

    A dataset defines the result set that visualizations consume, so editing its query changes the data behind every visualization built on it. This shared definition is what lets several charts reuse one query.

  129. Question 129Creating Dashboards and Visualizations in Databricks

    In an AI/BI dashboard, what distinguishes the published version from the draft?

    1. The draft refreshes automatically every hour
    2. The published version cannot be edited
    3. Viewers see the last published version Correct answer.
    4. Only the draft can be filtered

    Explanation

    Editing happens on a draft, and publishing promotes those changes to the version that viewers consume. People with edit rights can keep iterating in the draft without exposing unfinished work.

  130. Question 130Creating Dashboards and Visualizations in Databricks

    In a legacy Databricks SQL dashboard, how is a dashboard parameter referenced inside the query text?

    1. Colon prefix, as in :param
    2. Square brackets, as in [param]
    3. At sign prefix, as in @param
    4. Curly braces, as in {{param}} Correct answer.

    Explanation

    Legacy SQL dashboards substitute parameter values into the query text where the parameter name appears between double curly braces. The widget supplies the value at run time, and the query is re-executed with that value.

  131. Question 131Creating Dashboards and Visualizations in Databricks

    How can a dropdown parameter be given its list of selectable values?

    1. A notebook attached to the dashboard
    2. A query returning the allowed values Correct answer.
    3. Only manually typed static values
    4. The Unity Catalog metastore

    Explanation

    A query-based parameter runs a separate query whose result column populates the dropdown, so the choices stay aligned with the data. Static lists are possible too, but they must be maintained by hand as values change.

  132. Question 132Creating Dashboards and Visualizations in Databricks

    Which visualization overlays a line series and a bar series while giving each its own Y axis?

    1. Sankey
    2. Box plot
    3. Combo chart Correct answer.
    4. Choropleth map

    Explanation

    A combo chart mixes mark types, typically bars plus a line, and supports a secondary Y axis so measures with different magnitudes remain readable. Single-type charts cannot combine two mark styles on separate axes.

  133. Question 133Creating Dashboards and Visualizations in Databricks

    Which visualization plots records on a geographic map using latitude and longitude columns?

    1. Heatmap
    2. Map Correct answer.
    3. Bubble chart
    4. Funnel

    Explanation

    The map visualization interprets coordinate columns and renders points at those positions on a base map. Heatmaps shade a category grid, and bubble charts plot numeric axes without geography.

  134. Question 134Creating Dashboards and Visualizations in Databricks

    When a filter is added to a dashboard, which targets can it be applied to?

    1. Only visualizations that use a counter
    2. Chosen visualizations or all of them Correct answer.
    3. Only the first visualization on the page
    4. No visualizations until a refresh occurs

    Explanation

    A dashboard filter can be scoped to every visualization that uses the filtered field, or restricted to a selected subset. Scoping keeps unrelated charts unaffected by a filter intended for one section.

  135. Question 135Creating Dashboards and Visualizations in Databricks

    Two dashboards run the identical query on the same SQL warehouse within the cache window. What usually happens?

    1. Both queries fail with a concurrency error
    2. The second is queued until the cache expires
    3. The cache is bypassed for dashboard queries
    4. The second uses cache and returns faster Correct answer.

    Explanation

    Databricks SQL caches query results, so an identical query issued while the cached result is still valid is served from cache instead of re-scanning data. This reduces warehouse load and latency for repeated dashboard renders.

  136. Question 136Creating Dashboards and Visualizations in Databricks

    A chart shows a decimal ratio such as 0.42, but the audience needs 42%. Which setting change achieves this?

    1. Stacking set to 100 percent
    2. Axis scale set to logarithmic
    3. Legend position set to right
    4. Number format set to percent Correct answer.

    Explanation

    The number format setting controls how values are rendered, and a percent format multiplies the decimal by 100 for display. It changes presentation only, leaving the underlying values intact.

  137. Question 137Creating Dashboards and Visualizations in Databricks

    Stakeholders want a rendered snapshot of a dashboard emailed to them each Monday morning. Which feature provides this?

    1. A dashboard filter
    2. An access token for the warehouse
    3. A scheduled subscription Correct answer.
    4. Query history

    Explanation

    Subscriptions deliver a dashboard snapshot to named recipients on a recurring schedule, which suits stakeholders who do not log in. Query history and access tokens are operational artifacts and do not deliver content.

  138. Question 138Creating Dashboards and Visualizations in Databricks

    Which field roles are needed to build a heatmap visualization?

    1. Source, target, and value
    2. X, Y, and a color value Correct answer.
    3. Latitude and longitude columns
    4. A single numeric column

    Explanation

    A heatmap shades each cell formed by an X category and a Y category according to an aggregated color value. Without the two axis fields there is no grid, and without the measure there is nothing to shade.

  139. Question 139Creating Dashboards and Visualizations in Databricks

    An analyst must group users by signup month and track how many remain active in each later month. Which visualization is designed for this?

    1. Counter
    2. Combo
    3. Scatter
    4. Cohort Correct answer.

    Explanation

    A cohort visualization tracks a defined group across successive periods, which is the standard shape of retention analysis. It requires a cohort field and a period or stage field to align members over time.

  140. Question 140Creating Dashboards and Visualizations in Databricks

    Which visualization represents the volume moving between consecutive stages using three field roles?

    1. Sankey Correct answer.
    2. Pivot
    3. Histogram
    4. Box plot

    Explanation

    A Sankey diagram draws bands whose width is proportional to a value flowing from a source node to a target node. That three-role structure distinguishes it from charts that aggregate into cells or bins.

  141. Question 141Creating Dashboards and Visualizations in Databricks

    A dropdown parameter is populated by a query that returns the same region name many times, so the list shows duplicates. What is the best fix?

    1. Wrap the parameter query in a chart
    2. Add ORDER BY to the parameter query
    3. Switch the widget to a text input
    4. Add DISTINCT to the parameter query Correct answer.

    Explanation

    The parameter query supplies one entry per returned row, so duplicate rows produce duplicate choices. Selecting distinct values collapses them to a clean list while keeping the values tied to the data.

  142. Question 142Creating Dashboards and Visualizations in Databricks

    A bar chart built on a query returning 5,000 products has an unreadable axis. Which approach correctly limits the chart to the top 10 by revenue?

    1. Change the Y axis to a logarithmic scale
    2. Raise the results row limit to 50,000
    3. Aggregate in SQL with ORDER BY and LIMIT 10 Correct answer.
    4. Move the legend to the right side of the chart

    Explanation

    Chart settings control appearance, not which rows are returned, so the reduction must happen in the query itself. Ordering by the revenue measure descending and limiting to ten rows leaves exactly the top performers.

  143. Question 143Creating Dashboards and Visualizations in Databricks

    An executive wants a dashboard tile that shows a single large value for total closed-won revenue this quarter, with no categories, axes, or groupings. Which visualization type is designed for that purpose?

    1. Pivot table
    2. Bar chart
    3. Scatter plot
    4. Counter Correct answer.

    Explanation

    A counter renders one aggregated value prominently, which is exactly what a single headline KPI requires. Charts such as bar, pivot, and scatter are built to compare or cross-tabulate multiple categories rather than display one number.

  144. Question 144Creating Dashboards and Visualizations in Databricks

    On an AI/BI dashboard, several widgets must return the same result set with identical SQL logic. Where is that shared SQL defined?

    1. In a dataset belonging to the dashboard Correct answer.
    2. Inside the query editor of each individual widget
    3. In a notebook whose output is linked to the dashboard
    4. In the configuration of the attached SQL warehouse

    Explanation

    AI/BI dashboards separate query logic from presentation: SQL lives in dashboard datasets, and widgets visualize the results of a dataset. Multiple widgets can point at the same dataset, so the logic is maintained in one place.

  145. Question 145Creating Dashboards and Visualizations in Databricks

    A dashboard must show revenue for each product category as rows, broken out by fiscal quarter as columns, including a totals row and column. Which visualization type fits best?

    1. Scatter plot
    2. Counter
    3. Histogram
    4. Pivot table Correct answer.

    Explanation

    A pivot table places one field on rows, another on columns, and aggregates a measure in the cells, and it can add row and column totals. The other chart types either summarize one value or show distributions and correlations without a cross-tab layout.

  146. Question 146Creating Dashboards and Visualizations in Databricks

    A dashboard must always display the trailing 30 days of data without anyone editing the dashboard each morning. Which filter configuration achieves this?

    1. Fixed date range filter
    2. Query-based dropdown of all dates
    3. Relative date range filter Correct answer.
    4. Text parameter holding a date

    Explanation

    A relative date range filter is evaluated against the current date each time the dashboard runs, so a setting such as the last 30 days stays current automatically. Fixed dates and manually chosen values freeze the window at whatever was entered.

  147. Question 147Creating Dashboards and Visualizations in Databricks

    A dashboard has widgets built on two different datasets that both contain a country column. A single dashboard filter must restrict both sets of widgets. What should the analyst do?

    1. Add one filter and bind it to country in each dataset Correct answer.
    2. Add a separate filter for each dataset
    3. Create a parameter and use it in both queries
    4. Join both datasets into a single dataset

    Explanation

    A single dashboard filter can be mapped to a field in more than one dataset, so selecting a country restricts every widget whose dataset is bound to that filter. One filter bound to both datasets keeps the selections consistent across the dashboard.

  148. Question 148Creating Dashboards and Visualizations in Databricks

    Which dashboard permission level lets a user open and interact with a published dashboard while preventing them from changing its contents?

    1. CAN MANAGE
    2. CAN VIEW Correct answer.
    3. CAN EDIT
    4. CAN RUN

    Explanation

    Dashboards use CAN VIEW, CAN EDIT, and CAN MANAGE permission levels; CAN VIEW grants read-only access to the published dashboard, including interacting with filters. Modification requires CAN EDIT, and permission administration requires CAN MANAGE.

  149. Question 149Creating Dashboards and Visualizations in Databricks

    A dashboard must let viewers choose whether revenue is grouped by product category or by region. The choice changes the grouping expression in the SQL rather than restricting rows. Which mechanism supports this?

    1. One dashboard page per grouping option
    2. A dashboard filter bound to both columns
    3. A parameter used in the dataset query to pick the grouping column Correct answer.
    4. A dropdown filter listing category and region values

    Explanation

    Parameters substitute a value into the dataset's SQL, so a parameter can drive which column appears in the GROUP BY expression. Filters, by contrast, only restrict which rows are returned and cannot rewrite the query's structure.

  150. Question 150Developing, Sharing, and Maintaining AI/BI Genie spaces

    Which compute resource must a Genie space be configured with before users can ask questions and receive answers?

    1. An all-purpose cluster with autoscaling
    2. A GPU model-serving endpoint
    3. A Databricks job cluster
    4. A SQL warehouse Correct answer.

    Explanation

    Genie translates natural language into SQL and executes the generated queries on the SQL warehouse selected for the space. Model-serving endpoints, all-purpose clusters, and job clusters are not used to run Genie's SQL.

  151. Question 151Developing, Sharing, and Maintaining AI/BI Genie spaces

    Which Unity Catalog objects can you add to a Genie space as data sources?

    1. Notebooks and dashboards
    2. Volumes and external locations
    3. Tables and views Correct answer.
    4. Whole catalogs and schemas in one step

    Explanation

    A space is curated by adding specific tables and views from Unity Catalog, and Genie writes SQL only against those objects. Catalogs, volumes, notebooks, and dashboards are not queryable data sources for a space.

  152. Question 152Developing, Sharing, and Maintaining AI/BI Genie spaces

    A teammate must edit a space's instructions and add another table. Which permission level on the Genie space is the minimum that allows this?

    1. CAN VIEW
    2. CAN MANAGE
    3. CAN EDIT Correct answer.
    4. CAN RUN

    Explanation

    CAN EDIT lets a user change the setup of a space, including its data, instructions, sample questions, and benchmarks. CAN VIEW only permits opening the space and asking questions, while CAN MANAGE adds administrative control on top of editing.

  153. Question 153Developing, Sharing, and Maintaining AI/BI Genie spaces

    A user types: "Remove all rows from the orders table where status is cancelled." How does Genie respond?

    1. It does not run data-modifying statements and answers only with read-only queries Correct answer.
    2. It runs the DELETE after the user confirms in a dialog
    3. It creates a job that purges the rows nightly
    4. It generates a DELETE statement and queues it for approval

    Explanation

    Genie is a read-only analytics surface: it generates and runs SELECT queries and will not modify table data. Data changes must be performed through tools that have write access, such as notebooks, the SQL editor, or pipelines.

  154. Question 154Developing, Sharing, and Maintaining AI/BI Genie spaces

    Where in a Genie space should you record a definition such as "active customer means a customer with a purchase in the last 90 days"?

    1. In the SQL warehouse tags
    2. In the space's instructions Correct answer.
    3. In the sample questions list
    4. In the benchmark questions

    Explanation

    Instructions are the space-level text where curators describe business terminology, rules, and the columns they map to, and Genie consults them when interpreting questions. Sample questions are user-facing prompts and benchmarks are evaluation items.

  155. Question 155Developing, Sharing, and Maintaining AI/BI Genie spaces

    What is the purpose of sample questions in a Genie space?

    1. They define the only questions the space will answer
    2. They pre-warm the warehouse cache each morning
    3. They appear as suggested prompts that help users get started Correct answer.
    4. They score accuracy against verified SQL

    Explanation

    Sample questions are curated prompts displayed to users as a starting point and as examples of what the space can answer. They neither restrict what may be asked nor contribute to accuracy measurement.

  156. Question 156Developing, Sharing, and Maintaining AI/BI Genie spaces

    When a viewer asks a question in a shared Genie space, whose Unity Catalog permissions are applied to the underlying tables?

    1. A service principal configured for the space
    2. The space owner's permissions
    3. The permissions of the warehouse owner
    4. The permissions of the person asking Correct answer.

    Explanation

    Each question is executed under the identity of the user who asked it, so Unity Catalog grants, row filters, and column masks are enforced per person. Sharing a space never elevates a viewer's access to data.

  157. Question 157Developing, Sharing, and Maintaining AI/BI Genie spaces

    A column named amt_cd is frequently misread by Genie. Which change most directly improves interpretation of that column?

    1. Renaming the Genie space
    2. Adding the table to more sample questions
    3. Adding a column comment that explains the column's meaning Correct answer.
    4. Increasing the SQL warehouse size

    Explanation

    Column and table comments are metadata Genie reads when generating SQL, so a clear comment on an abbreviated column directly improves its interpretation. Performance settings and prompt lists do not change how a column is understood.

  158. Question 158Developing, Sharing, and Maintaining AI/BI Genie spaces

    Why add an example SQL query to a Genie space?

    1. To limit which users can query the underlying table
    2. To schedule that query to refresh on a nightly cadence
    3. To give Genie a known-good pattern it can reuse for similar questions Correct answer.
    4. To replace the need for table and column comments

    Explanation

    Example SQL queries act as curated reference queries that Genie can draw on when a user asks something similar, improving consistency for complex logic. They are not schedulers, access controls, or a substitute for good metadata.

  159. Question 159Developing, Sharing, and Maintaining AI/BI Genie spaces

    What does the Benchmarks tab of a Genie space provide?

    1. Automatic rewrites of table comments
    2. Accuracy scores for a fixed set of questions with known-correct SQL Correct answer.
    3. A comparison of warehouse query runtimes
    4. A list of everyone who opened the space

    Explanation

    Benchmarks hold test questions paired with verified SQL so you can run them and see how often Genie produces the expected result, which reveals weak areas. They are an evaluation tool, not a usage log or performance report.

  160. Question 160Developing, Sharing, and Maintaining AI/BI Genie spaces

    Where can a space manager review which questions users asked recently and which answers received negative feedback?

    1. The Monitoring tab Correct answer.
    2. The sample questions editor
    3. The Unity Catalog lineage graph
    4. The Benchmarks tab

    Explanation

    The Monitoring tab records conversations, including the questions asked and the feedback users left, so curators can spot recurring problems. Benchmarks hold only the test questions you define yourself.

  161. Question 161Developing, Sharing, and Maintaining AI/BI Genie spaces

    You grant a group CAN VIEW on a Genie space, but members get permission errors when they ask questions. What is the most likely missing access?

    1. Ownership of the Genie space
    2. SELECT on the tables and permission to use the space's SQL warehouse Correct answer.
    3. CAN MANAGE on the Genie space
    4. CREATE TABLE on the target schema

    Explanation

    Space access controls who can open and ask questions, but the generated SQL still runs as the asking user against Unity Catalog tables and on the configured warehouse. Without SELECT on the data and warehouse usage rights, questions fail even though the space opens.

  162. Question 162Developing, Sharing, and Maintaining AI/BI Genie spaces

    An analyst wants to verify exactly how Genie computed a reported total. What should they do?

    1. Check the space's list of sample questions
    2. Rerun the question repeatedly until the number stops changing
    3. Resize the SQL warehouse and ask again
    4. Open the SQL behind the answer and inspect its joins and filters Correct answer.

    Explanation

    Every answer is backed by generated SQL that users can expand, which is the direct way to confirm the joins, filters, and aggregations used. Reviewing prompts or resizing compute does not reveal the query logic.

  163. Question 163Developing, Sharing, and Maintaining AI/BI Genie spaces

    A table exists in the same schema as a space's data, but Genie says it cannot answer questions about it. Why?

    1. The table contains too few rows to be queried
    2. Only the objects explicitly added to the space are in scope Correct answer.
    3. Views can never be queried by Genie
    4. Genie blocks all questions about tables created in the last day

    Explanation

    A Genie space answers from the curated set of tables and views added to it, not from everything visible in the schema. To make another table answerable, add it to the space's data setup.

  164. Question 164Developing, Sharing, and Maintaining AI/BI Genie spaces

    You created a new view and want it usable in an existing Genie space. What is the appropriate action?

    1. Add the view in the space's data setup and add any needed instructions or comments Correct answer.
    2. Run a benchmark and the view is added automatically
    3. Ask a question about it and Genie auto-discovers the view
    4. Delete and recreate the Genie space with the view

    Explanation

    Maintaining a space means updating its curated scope deliberately: add the new object under the data setup, then supply comments or instructions so Genie understands it. Views are not discovered by asking questions or by running benchmarks.

  165. Question 165Developing, Sharing, and Maintaining AI/BI Genie spaces

    A user reported a wrong answer in a Genie space. Which action best supports regression testing later?

    1. Increase the size of the SQL warehouse
    2. Delete the conversation from the space
    3. Remove the sample questions from the space
    4. Save the question and its correct SQL as a benchmark question Correct answer.

    Explanation

    Adding the failing case to benchmarks preserves it as a repeatable test, so you can confirm later that fixes actually resolved it. Deleting the conversation loses the evidence, and compute changes do not affect correctness.

  166. Question 166Developing, Sharing, and Maintaining AI/BI Genie spaces

    Which capability does CAN MANAGE provide that CAN EDIT does not?

    1. Adding tables and views to the space
    2. Asking questions and viewing answers
    3. Changing space permissions and deleting the space Correct answer.
    4. Editing instructions in the space

    Explanation

    CAN MANAGE includes everything CAN EDIT allows and adds administrative control, such as modifying who can access the space and removing it. Editing content and adding data are already possible with CAN EDIT.

  167. Question 167Developing, Sharing, and Maintaining AI/BI Genie spaces

    Questions in a Genie space are slow and expensive because the configured SQL warehouse is oversized and never auto-stops. What is the most direct fix?

    1. Add more sample questions to the space
    2. Delete the benchmark questions
    3. Right-size the SQL warehouse and set an auto-stop timeout Correct answer.
    4. Add more tables and views to the space

    Explanation

    Genie questions run on the space's configured SQL warehouse, so warehouse sizing and auto-stop settings determine both latency and spend. Content changes to the space do not affect compute cost.

  168. Question 168Developing, Sharing, and Maintaining AI/BI Genie spaces

    Genie repeatedly asks users which date column means "order date". What is the most effective fix?

    1. Add an instruction that maps the term to the correct column Correct answer.
    2. Disable clarifying questions for the space
    3. Increase the SQL warehouse size
    4. Remove the ambiguous columns from the underlying table

    Explanation

    Instructions persist in the space and tell Genie how business terms map to specific columns, so the ambiguity is resolved for every future question. Destructive schema changes and compute changes do not address the interpretation gap.

  169. Question 169Developing, Sharing, and Maintaining AI/BI Genie spaces

    A schema contains 200 tables and Genie's answers in the space are unreliable. Which approach follows recommended practice?

    1. Increase warehouse size until accuracy improves
    2. Create curated views of the relevant tables and add only those Correct answer.
    3. Add all 200 tables so nothing is missing
    4. Remove all sample questions from the space

    Explanation

    Genie performs best on a focused, well-described set of objects, so curating a smaller set of views narrows the search space and improves reliability. Adding everything enlarges the ambiguity Genie must resolve.

  170. Question 170Developing, Sharing, and Maintaining AI/BI Genie spaces

    Benchmarks show 65% accuracy, and most failures involve fiscal quarter questions. What is the best next step?

    1. Move the space onto an all-purpose cluster
    2. Recreate the SQL warehouse used by the space
    3. Delete the failing benchmark questions
    4. Add an instruction defining when the fiscal year starts and which columns hold the dates Correct answer.

    Explanation

    Clustered failures on one concept usually mean the space lacks a definition Genie can apply, so an instruction describing the fiscal calendar addresses the root cause. Removing tests or changing compute hides the problem rather than fixing it.

  171. Question 171Developing, Sharing, and Maintaining AI/BI Genie spaces

    Genie joins orders to returns on the wrong column, producing inflated counts. What is the most reliable fix?

    1. Add more sample questions about returns
    2. Declare the correct primary and foreign keys in Unity Catalog or add an instruction describing the join Correct answer.
    3. Grant users CAN MANAGE on the space
    4. Rename both columns to the same name

    Explanation

    Genie uses declared key relationships and explicit join guidance to build correct joins, so formalizing the relationship in Unity Catalog or in instructions gives it reliable information. Surface changes like renaming or more prompts do not establish the join path.

  172. Question 172Developing, Sharing, and Maintaining AI/BI Genie spaces

    A source table's column was renamed, and some questions in the space now fail. Which actions keep the space healthy?

    1. Grant CAN MANAGE on the space to all users
    2. Delete the space and rebuild it from scratch
    3. Update the instructions and example SQL that mention the old column, then rerun benchmarks Correct answer.
    4. Nothing is needed; Genie repairs example SQL automatically

    Explanation

    Curated assets such as instructions and example SQL are authored against specific column names, so they must be revised after schema changes and then validated with benchmarks. Rebuilding from scratch throws away other curation work, and automation will not rewrite the assets.

  173. Question 173Developing, Sharing, and Maintaining AI/BI Genie spaces

    Finance and sales define "revenue" differently but query the same tables. What should you build?

    1. Two spaces over the same tables, each with its own instructions and example SQL Correct answer.
    2. One space where users prefix each question with a department name
    3. One space with both definitions in the same instructions
    4. One space per table so the definitions cannot collide

    Explanation

    Instructions and example SQL are scoped to a single space, so conflicting definitions are best handled by separate spaces that share the same governed tables. Combining both definitions in one space makes Genie guess which meaning applies.

  174. Question 174Data Modeling with Databricks SQL

    Using Unity Catalog, which name correctly references the table orders that lives in the schema sales inside the catalog prod?

    1. orders.sales.prod
    2. sales.orders.prod
    3. prod.sales.orders Correct answer.
    4. prod.orders.sales

    Explanation

    Unity Catalog uses a three-level namespace written as catalog.schema.table, in that order. The catalog is the top-level container, the schema is nested inside it, and the table or view name comes last.

  175. Question 175Data Modeling with Databricks SQL

    When you drop an external table in Unity Catalog, what happens to the data files at the table's LOCATION?

    1. They are deleted along with the table metadata.
    2. They are moved into the catalog's managed storage location.
    3. They are archived to a Delta history folder for seven days.
    4. They remain in place and only the metadata is removed. Correct answer.

    Explanation

    An external table only registers metadata against a storage location that you control, so dropping it removes the catalog entry but leaves the files where they are. Deleting data files on drop is the behavior of managed tables, not external ones.

  176. Question 176Data Modeling with Databricks SQL

    Which statement best describes a view created with CREATE VIEW in Databricks SQL?

    1. It is available only within the session that created it.
    2. It must be refreshed manually before each query.
    3. It physically copies the result set at creation time.
    4. It stores the query text and evaluates it on use. Correct answer.

    Explanation

    A view is a named, saved query: the definition is persisted in the schema and the query is executed whenever the view is referenced. It holds no rows of its own, so it always reflects the current contents of its base tables.

  177. Question 177Data Modeling with Databricks SQL

    You run CREATE TABLE summary AS SELECT region, SUM(amount) AS total FROM sales GROUP BY region. Which statement about the resulting table is true?

    1. The column data types must be declared before the SELECT.
    2. Column names and types are derived from the query's result. Correct answer.
    3. The table inherits the primary key constraints of sales.
    4. The table is recreated on every run of the statement by default.

    Explanation

    CREATE TABLE ... AS SELECT infers the schema from the output of the query: each select-list alias becomes a column name and its type comes from the expression. Constraints, comments, and partitioning from the source table are not carried over.

  178. Question 178Data Modeling with Databricks SQL

    A Delta table holds about five billion order rows and has an order_id column that is unique on every row. Which column is the most appropriate choice for PARTITIONED BY?

    1. line_item_json, a large free-form string column
    2. customer_email, a mostly unique string column
    3. order_date, a DATE column spanning a few years Correct answer.
    4. order_id, the unique identifier column

    Explanation

    Partitioning is most effective on relatively low-cardinality columns that are commonly used as filters, so each partition stays a reasonable size. A date at day or month grain is a typical choice, while near-unique columns multiply the number of tiny directories and slow down queries.

  179. Question 179Data Modeling with Databricks SQL

    You declare a PRIMARY KEY constraint on a Delta table in Unity Catalog. What behavior should you expect?

    1. Inserts violating the key are rejected at write time.
    2. The constraint is enforced only for MERGE statements.
    3. The constraint is enforced only when the table is partitioned.
    4. The constraint is informational and is not enforced on writes. Correct answer.

    Explanation

    In Databricks, PRIMARY KEY and FOREIGN KEY constraints are informational: they document relationships for tools and the optimizer but rely on the writer to supply valid data. Enforced constraints in Delta tables are NOT NULL and CHECK.

  180. Question 180Data Modeling with Databricks SQL

    A table is defined with event_ts TIMESTAMP and event_date DATE GENERATED ALWAYS AS (CAST(event_ts AS DATE)). What does this require of a plain INSERT statement?

    1. The insert must supply event_ts and event_date with matching values.
    2. The insert must supply event_date and let event_ts be derived.
    3. The insert must use a MERGE statement instead of INSERT.
    4. The insert supplies event_ts; event_date is computed automatically. Correct answer.

    Explanation

    A generated column takes its value from an expression over other columns in the same row, so writers provide only the source columns and the engine fills in the derived one. Explicitly supplying a value for a generated column in an INSERT is rejected.

  181. Question 181Data Modeling with Databricks SQL

    A star schema contains fact_sales with one row per order line (quantity and extended_price measures), dim_product with a category column, dim_date with year and month columns, and dim_customer. Which statement about querying this model is correct?

    1. Category and month must be stored in fact_sales for the query to run.
    2. Revenue can only be aggregated after joining dim_customer.
    3. Quantity cannot be summed because it is a semi-additive measure.
    4. Revenue is summed from the fact table after joining dimensions. Correct answer.

    Explanation

    In a star schema the additive measures live in the fact table and descriptive attributes live in the dimensions, so reporting by category and month means joining the fact to those dimensions and summing extended_price. No descriptive columns need to be duplicated into the fact table.

  182. Question 182Data Modeling with Databricks SQL

    Which statement about liquid clustering (CLUSTER BY) on a Delta table is correct?

    1. It requires the clustering columns to be declared as partitions at table creation.
    2. Clustering keys can be changed later without rewriting the table. Correct answer.
    3. It supports at most one clustering column per table.
    4. It only takes effect after running OPTIMIZE ZORDER BY on the same columns.

    Explanation

    Liquid clustering records clustering keys as table metadata, so keys can be redefined with ALTER TABLE ... CLUSTER BY and the layout improves incrementally as data is written and optimized. It replaces the older partitioning plus Z-order approach rather than depending on it.

  183. Question 183Data Modeling with Databricks SQL

    A nightly MERGE INTO target USING source writes into a Delta table, and the source query has just gained a new column named notes. Which approach lets that column be added to the target automatically during the merge?

    1. Set spark.databricks.delta.schema.autoMerge.enabled to true. Correct answer.
    2. Set the table property delta.autoOptimize.managed to true.
    3. Add the clause EVOLVE SCHEMA to the MERGE INTO statement.
    4. Add the table option mergeSchema = 'true' to the MERGE INTO statement.

    Explanation

    Schema evolution for UPDATE and MERGE is controlled by the Spark configuration spark.databricks.delta.schema.autoMerge.enabled; when it is true, new source columns are created in the target as part of the write. The mergeSchema write option applies to other write paths such as COPY INTO or DataFrame saves.

  184. Question 184Securing Data

    In Unity Catalog, which privilege must be granted to a principal so that it can read rows from a table?

    1. MODIFY
    2. APPLY TAG
    3. SELECT Correct answer.
    4. CREATE TABLE

    Explanation

    SELECT is the read privilege for tables and views in Unity Catalog. MODIFY allows inserting, updating and deleting rows, CREATE TABLE allows creating objects in a schema, and APPLY TAG governs tag assignment rather than data access.

  185. Question 185Securing Data

    Which Unity Catalog object is attached directly to a table to restrict which rows a given user can read?

    1. Storage credential
    2. External location
    3. Column mask
    4. Row filter Correct answer.

    Explanation

    A row filter is a SQL user-defined function attached to a table; it returns TRUE or FALSE per row for the calling user, so only permitted rows are returned. Column masks transform values within a column, and storage credentials and external locations govern storage access rather than row visibility.

  186. Question 186Securing Data

    Which mechanism returns a transformed value in place of the real value of a sensitive column for users who are not authorised to see it?

    1. Storage credential
    2. Column mask Correct answer.
    3. Row filter
    4. Dynamic view

    Explanation

    A column mask is a SQL function applied to a table column that receives the real value and returns a replacement, such as a hash or a constant, for users the logic does not exempt. Row filters restrict which rows are visible, and a dynamic view is a separate query object rather than a column-level control on the table itself.

  187. Question 187Securing Data

    A user holds SELECT on a Unity Catalog table. Which additional privileges on the parent schema and catalog are required before the query succeeds?

    1. USE SCHEMA and USE CATALOG Correct answer.
    2. OWNERSHIP of the schema and the catalog
    3. CREATE on the schema and the catalog
    4. MODIFY on the schema and the catalog

    Explanation

    Unity Catalog requires the traversal privileges USE CATALOG on the catalog and USE SCHEMA on the schema in addition to the object privilege such as SELECT. Without them the object privilege cannot be exercised because the user cannot resolve the namespace path.

  188. Question 188Securing Data

    Which Databricks feature stores credentials in an encrypted scope so that notebooks can read them at run time instead of hard-coding them?

    1. Secret scopes Correct answer.
    2. Cluster tags
    3. Widgets
    4. Init scripts

    Explanation

    Secret scopes hold secrets that are retrieved at run time with the secrets utility, and values are redacted when printed in a notebook. Widgets collect parameter input, init scripts run cluster startup code, and tags are metadata labels; none of them store credentials securely.

  189. Question 189Securing Data

    A table owner leaves the company. Which principals can transfer ownership of that Unity Catalog table to a new owner?

    1. Only the current owner or a metastore admin Correct answer.
    2. Only the cloud account owner
    3. Any user with SELECT
    4. Any workspace admin

    Explanation

    Ownership transfer in Unity Catalog is restricted to the current owner of the object or a metastore admin, who can transfer ownership of any object in the metastore. Ordinary workspace administration does not confer object-level ownership rights.

  190. Question 190Securing Data

    A user has SELECT on a Unity Catalog view but no privileges on the base tables the view reads. What is the result when the user queries the view?

    1. Yes, because a view runs with the owner's privileges Correct answer.
    2. Yes, but only if the view and tables share a schema
    3. No, SELECT on the base tables is always required
    4. No, they must also hold MODIFY on each base table

    Explanation

    Unity Catalog views execute with definer's rights: the view owner's privileges on the underlying objects are used, so grantees of the view do not need direct privileges on the base tables. This is what allows views to be used deliberately as a data-access boundary.

  191. Question 191Securing Data

    A user belongs to group A, which has been granted SELECT on a table, and to group B, whose earlier grant was revoked. What access does the user have?

    1. Access is denied because privileges from multiple groups are intersected
    2. Access is denied unless the groups sit in different workspaces
    3. Access is granted, because privileges are additive and there is no DENY Correct answer.
    4. Access is denied because group-level revokes take precedence

    Explanation

    Unity Catalog privileges are additive and there is no DENY statement, so the effective privilege set is the union of everything granted to the user and to the groups the user belongs to. A revoke only removes that specific grant and does not block a privilege arriving through another group.

  192. Question 192Securing Data

    A view is defined with a WHERE clause that joins the table to a lookup table keyed on the current user. Which security pattern does this implement?

    1. Column masking applied to a region column
    2. Encryption of the table at rest
    3. Row-level filtering by joining to a lookup table Correct answer.
    4. Credential passthrough to cloud storage

    Explanation

    Joining to a mapping table keyed on the current user limits the rows each caller can see, which is row-level filtering expressed inside a view. Column masking would instead replace values in a column, and the other options concern storage identity or encryption rather than query results.

  193. Question 193Securing Data

    Which source records Unity Catalog access events such as which principal queried which table?

    1. Notebook revision history
    2. The system.access.audit system table Correct answer.
    3. Spark UI stage metrics
    4. Cluster driver logs

    Explanation

    Audit records for account and workspace activity, including Unity Catalog data access, are exposed through the system.access.audit system table and through diagnostic log delivery. Driver logs, revision history and stage metrics describe execution rather than security-relevant access events.

  194. Question 194Securing Data

    A principal is granted SELECT on a Unity Catalog catalog. What does that grant cover?

    1. No data access, because SELECT cannot be granted on a catalog
    2. SELECT on every current and future schema and table in it Correct answer.
    3. SELECT only on the catalog's default schema
    4. SELECT on the tables that existed when the grant ran

    Explanation

    Unity Catalog privileges inherit down the object hierarchy, so a grant on a catalog applies to all schemas and tables it contains, including objects created later. The recipient still needs USE CATALOG and USE SCHEMA to traverse to the objects.

  195. Question 195Securing Data

    Which practice best reduces the risk created by personal access tokens used for automation in a workspace?

    1. Share one long-lived token across the whole team
    2. Set a maximum token lifetime and use service principals Correct answer.
    3. Issue tokens without an expiry so jobs never fail
    4. Commit tokens to the repository next to the notebooks

    Explanation

    Enforcing a maximum token lifetime bounds the window in which a leaked token is usable, and service principals give automation its own identity that can be governed and rotated independently of people. Tokens should never be shared or committed to source control, and non-expiring tokens defeat the purpose of rotation.

  196. Question 196Securing Data

    Which statement about Unity Catalog row filters and column masks is correct?

    1. They are enforced only on tables in the hive_metastore
    2. They are enforced for all queries, including BI tools Correct answer.
    3. They are bypassed by any user who holds MODIFY on the table
    4. They apply only when the table is queried through a view

    Explanation

    Because row filters and column masks are attached to the table itself, Unity Catalog applies them regardless of the client used to read the data, so notebooks, SQL warehouses and BI tools all receive the governed result. They are features of the Unity Catalog metastore, not of the legacy Hive metastore.

  197. Question 197Securing Data

    Users must query data in a cloud storage path through Unity Catalog without ever receiving storage account keys or IAM roles. Which configuration achieves this?

    1. A cluster-scoped IAM role attached through credential passthrough
    2. A personal access token scoped to the storage account
    3. A secret scope holding the storage key, read with the secrets utility
    4. A storage credential referenced by an external location Correct answer.

    Explanation

    A storage credential encapsulates a cloud identity that Unity Catalog uses to access storage, and an external location binds that credential to a path so privileges can be granted on the path without exposing credentials to users. Credential passthrough ties storage access to the cluster's own identity, and handing out keys or tokens to users defeats the goal.

  198. Question 198Securing Data

    A user must create an external table in a Unity Catalog schema over an approved cloud path. Which privileges are required in addition to USE CATALOG and USE SCHEMA?

    1. SELECT on the external location and READ FILES on the cloud storage path
    2. CREATE TABLE on the schema plus CREATE EXTERNAL TABLE on the location Correct answer.
    3. USE SCHEMA on the schema only
    4. OWNERSHIP of the external location and of the metastore

    Explanation

    Creating an external table needs CREATE TABLE on the schema to create the object and CREATE EXTERNAL TABLE on the external location to authorise writing data at that path. SELECT on a location governs reads, and ownership is broader than the task requires.

  199. Question 199Securing Data

    Several analysts must share one cluster while Unity Catalog still isolates their data access and applies masking. Which cluster access mode fits?

    1. Single user access mode
    2. Legacy Table ACL only
    3. Shared access mode Correct answer.
    4. No isolation shared

    Explanation

    Shared access mode supports multiple concurrent users on one cluster while providing isolation between them and enforcing Unity Catalog data governance. Single user mode is bound to one principal, and the legacy Table ACL mode does not provide Unity Catalog enforcement.

Databricks Data Analyst flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Lakehouse architecture
Combines cloud object storage with Delta Lake ACID transactions and a metastore, so a single copy of data serves BI SQL, machine learning, and streaming workloads.
Medallion architecture layers
Bronze holds raw ingested data, silver holds cleaned and conformed tables, and gold holds business-level aggregates consumed by dashboards and reports.
Photon engine
A vectorized C++ query engine in the Databricks runtime and SQL warehouses that accelerates scans, joins, and aggregations over Delta tables.
Unity Catalog
A governance layer providing a catalog.schema.table namespace plus centralized privileges, lineage, auditing, and discovery across workspaces and cloud regions.
VACUUM default retention
VACUUM deletes data files no longer referenced by the Delta log; the default retention threshold is seven days, after which time travel to older versions fails.
OPTIMIZE with Z-ORDER
OPTIMIZE compacts many small files into larger ones; ZORDER BY co-locates rows with similar column values so later queries skip more files.
Managed versus external table
Managed tables keep files in the metastore's managed storage and delete them with the table; external tables only register a location and preserve the files.
Delta time travel
Query earlier table versions using VERSION AS OF or TIMESTAMP AS OF, while DESCRIBE HISTORY lists versions, operations, and per-commit metrics.
COPY INTO command
Loads files from cloud storage into a Delta table idempotently, skipping files already processed, and accepts an explicit schema or schema inference.
Auto Loader
A cloudFiles source that incrementally ingests new files from storage using directory listing or file notifications, with schema inference, schema evolution, and checkpointing.
CSV read options
Common reader options are header, delimiter, quote, escape, inferSchema, and mode, where PERMISSIVE keeps malformed rows with nulls and a corrupt record field.
External location
A Unity Catalog object pairing a storage credential with a cloud storage path so users can read and write that path without embedding secrets.
SQL warehouse sizes
Warehouse sizes run from 2X-Small to 4XLarge; each step roughly doubles compute and cost, while multi-cluster scaling adds clusters for concurrent users.
Serverless SQL warehouse
Starts in seconds, scales to zero when idle, and is fully managed by Databricks, so cost accrues only while queries actually run.
Query result cache
A warehouse returns cached results for an identical repeated query when the underlying Delta tables are unchanged, avoiding recomputation of the same work.
Parameter markers in SQL
Named placeholders written as :name inside the query editor and dashboards; key-value parameters are supplied when the query runs.
Query Profile operator tree
A visual breakdown of a query's operators showing rows, bytes, and time per node, used to locate scans, shuffles, and join bottlenecks.
Time in queue metric
How long a query waits for warehouse capacity before execution starts; high values mean the warehouse is saturated and needs scaling or a larger size.
Spilled bytes metric
Bytes written to disk because an operator exceeded available memory; any nonzero value signals a memory bottleneck to fix by repartitioning or resizing.
Dashboard dataset
A saved SQL query whose result set powers one or more visualizations on an AI/BI dashboard and is rerun when filters or parameters change.
Dashboard filter scope
A filter targets one dataset or all datasets on the dashboard, while cross-filtering narrows linked marks when a viewer clicks a data point.
Counter visualization
Displays a single aggregated number, so the underlying dataset query must already apply an aggregate function such as count, sum, or average.
Genie space instructions
Curated text and example SQL telling Genie preferred joins, filters, and business definitions, raising the accuracy of generated answers.
Genie space permissions
CAN VIEW lets users ask questions, CAN EDIT lets them curate instructions and trusted assets, and CAN MANAGE adds sharing and deletion rights.
Genie benchmarks
A set of representative questions with expected answers used to measure how often a Genie space returns correct results.
Star schema
A central fact table storing measures and foreign keys, joined to denormalized dimension tables that hold descriptive attributes for filtering and grouping.
Slowly changing dimension type 2
Preserves history by inserting a new dimension row with effective start and end dates and a current-row flag instead of overwriting the existing values.
Materialized view
Stores precomputed query results and refreshes on a schedule, incrementally where possible, giving fast answers for repeated aggregations over large tables.
Fact table grain
The meaning of one row in a fact table, such as one order line; it determines which dimensions can be joined without double counting.
Row filter in Unity Catalog
A boolean-returning function attached to a table; rows appear only when it evaluates true, enforced for every query by every user.
Column mask
A function applied to a column that returns a transformed or null value for users lacking privilege, while authorized users see the real value.
Audit log table
system.access.audit records workspace events including queries, grants, and permission changes, supporting security investigations and compliance reporting.

Databricks Data Analyst study guide

Understanding the Databricks Data + AI Platform

The lakehouse in one paragraph

Databricks combines a data lakehouse - cheap cloud object storage (S3, ADLS, GCS) managed in Delta Lake format - with a managed control plane that runs workspaces, notebooks, jobs, and metadata. The critical split: the control plane lives in Databricks' account; the data plane (your data and often the compute VMs) lives in your cloud account or, for serverless, in Databricks-managed infrastructure inside your region.

Core building blocks you must be able to name

  • Delta Lake - ACID transactions, schema enforcement, time travel on top of Parquet files.
  • Unity Catalog - the governance layer: three-level namespace catalog.schema.table, grants, lineage, audit, row filters and column masks.
  • Compute types - all-purpose clusters (interactive, shared), job clusters (ephemeral, cheaper), and SQL warehouses (serverless, pro, classic) tuned for BI and Databricks SQL.
  • Workload surfaces - notebooks and Workflows/Lakeflow for pipelines; Databricks SQL editor, dashboards, and AI/BI Genie for analytics.

Traps candidates hit

  1. Assuming SQL warehouses can run Python notebooks - they cannot; they run SQL only.
  2. Confusing workspace admin with account admin or metastore admin.
  3. Believing data is stored in Databricks' account - it is in object storage you control.
  4. Forgetting that Unity Catalog privileges are inherited downward from catalog to schema to table.

Rule of thumb for choosing compute

Ask who waits? If a human waits interactively, use an all-purpose cluster or a SQL warehouse. If nobody waits, use a job cluster scheduled by Workflows. For a dashboard viewed by 50 people, a small serverless SQL warehouse with auto-scaling beats a big classic warehouse - concurrency, not data volume, drives sizing.

Worked example

A team ingests CSV files nightly, refines them, and publishes a dashboard. Bronze/silver pipelines → Workflows on job clusters. Gold tables queried by the dashboard → serverless SQL warehouse. Governance across both → Unity Catalog.

Managing Data

Managed versus external tables

The single most tested distinction. A managed table's data files are owned by Unity Catalog and deleted when you DROP TABLE. An external table points at a storage location you specify; DROP TABLE removes only the metadata, leaving files intact. If data must outlive the table, make it external.

Delta Lake operations to know

  • DESCRIBE HISTORY / time travel with VERSION AS OF or TIMESTAMP AS OF.
  • OPTIMIZE compacts small files; OPTIMIZE ... ZORDER BY (col) co-locates related data; liquid clustering is the modern alternative and does not require partitioning.
  • VACUUM deletes unreferenced files older than the retention threshold (default 7 days). Once vacuumed, time travel to older versions fails - a favorite trick question.
  • Constraints: NOT NULL, CHECK, and informational PRIMARY KEY/FOREIGN KEY (not enforced for uniqueness).

Architecture patterns

The medallion layout - bronze (raw), silver (cleaned/conformed), gold (business-level aggregates) - is the expected answer whenever a question asks how to organize a lakehouse. Each hop should be a Delta table so downstream consumers get schema enforcement and lineage.

Traps

  1. Dropping an external table and expecting files to disappear - they do not.
  2. Running VACUUM then trying to query an old version.
  3. Assuming a primary key prevents duplicate inserts; enforce with MERGE or DROP DUPLICATES.
  4. Over-partitioning small tables, producing thousands of tiny files.

Rule of thumb

Cluster or partition on columns used in high-selectivity filters, and only when the table holds roughly a terabyte or more. Below that, liquid clustering plus OPTIMIZE is simpler and usually faster.

Worked example

A landing zone receives daily files. Create an external Delta table over abfss://raw/sales/, then build a silver managed table with MERGE INTO keyed on order_id. Schedule OPTIMIZE weekly; never VACUUM with zero retention on a table you may need to restore.

Importing Data

The ingestion menu

Know which tool fits which scenario - questions are almost always phrased as "which approach is best."

  • UI upload / Create Table from file - quick, small files, good for demos and one-offs.
  • COPY INTO - SQL command that loads files from cloud storage into a Delta target. It is idempotent: already-loaded files are skipped, so re-running is safe. Supports schema evolution with mergeSchema.
  • Auto Loader (cloudFiles) - incremental, scalable streaming ingestion that discovers new files as they land; the right answer for continuously arriving data in cloud storage.
  • CREATE TABLE AS SELECT / INSERT INTO ... SELECT - ingesting from another table or a JDBC source via a connection.
  • Lakeflow Connect / partner connectors - managed connectors for SaaS sources such as Salesforce or SQL Server.

Formats and behavior

CSV (inferSchema, header, delimiter), JSON, Parquet, Avro, and Delta are all supported. Streaming reads require Delta or Auto Loader, not raw CSV via COPY INTO semantics - a common trip-up.

Traps

  1. Choosing COPY INTO for a target that must be a non-Delta table - it requires Delta.
  2. Using Auto Loader with a local workspace path; it needs a cloud storage location.
  3. Forgetting that UI upload has practical file-size limits and is not a production pipeline.
  4. Re-running a naive INSERT INTO ... SELECT from a landing folder and double-loading data.

Worked example

New CSV files appear hourly in s3://landing/orders/. Correct pattern: create a bronze streaming table with read_files/Auto Loader, set a schema location, enable schema evolution, and let it checkpoint so files are processed exactly once.

Rule of thumb

Files that keep arriving → Auto Loader. Files that arrive in batches and may be replayed → COPY INTO. Handful of files → UI upload.

Executing Queries with Databricks SQL and SQL Warehouses

What a SQL warehouse actually is

A SQL warehouse is a compute resource dedicated to Databricks SQL. Types:

  • Serverless - Databricks-managed infrastructure, fastest start, no cluster configuration, scales instantly. Preferred for BI and dashboards.
  • Pro - your cloud account, supports the full feature set including Photon acceleration.
  • Classic - legacy; limited features.

Each warehouse has a size (2X-Small through 4X-Large), a cluster count range (min/max for auto-scaling), and an auto-stop timer. Photon is the vectorized engine that speeds up SQL workloads; it is on by default for Pro and Serverless.

Writing queries in the SQL editor

The editor supports multiple statements, CTEs, window functions, QUALIFY, MERGE, PIVOT, and parameter markers (:param). Results can be downloaded, visualized inline, or saved as a query that dashboards and alerts reuse.

Traps

  1. Warehouse size vs. cluster count. Size controls per-cluster memory/CPU; the min/max cluster range controls concurrency. Sizing up does not help if the problem is too few clusters.
  2. Auto-stop. A warehouse that stopped must start before the query runs; startup time is included in the user's wait, so set auto-stop thoughtfully for interactive use.
  3. Result caching. Identical queries against unchanged data return cached results almost instantly - do not misread that as a performance win from your tuning.
  4. Assuming one warehouse serves all workspaces; warehouses are workspace-scoped resources.

Rule of thumb

Size for the largest single query, scale for the number of concurrent users. A dashboard with many viewers wants a small/medium size with a high max-cluster count, not a 4X-Large single cluster.

Worked example

Ten analysts share a dashboard and complain about queueing at 9 a.m. Fix: set min clusters to 1 so someone is always warm, raise max clusters to 4, and keep size at Medium. Queue time drops without paying for a huge single warehouse.

Analyzing Queries

Where to look

Query History lists every statement with duration, status, warehouse, user, and rows/bytes read. Clicking a query opens Query Profile, a visual plan showing operators, rows, and time per stage. For Spark-level work, the Spark UI shows stages, tasks, shuffle, and skew.

The metrics that matter

  • Queue time vs. execution time vs. fetch time. Total duration is usually dominated by queue time on a busy warehouse, not by slow SQL.
  • Bytes/rows scanned. Large scans usually mean missing partition or clustering filters, or SELECT * on wide tables.
  • Spill to disk / shuffle size. Indicates insufficient memory or a poorly chosen join strategy.
  • Skew. One task taking far longer than its siblings means a hot key in a join or GROUP BY.

Diagnostic toolkit

EXPLAIN shows the logical and physical plan. Filtering early, joining on selective keys, avoiding SELECT *, and using OPTIMIZE/clustering to enable file pruning all reduce scanned bytes. Broadcast joins help when one side is small.

Traps

  1. Reading only "duration" and blaming the query when the time was spent queued.
  2. Assuming more warehouse size fixes a bad plan - it often just makes the bad plan faster but still expensive.
  3. Ignoring result caching when comparing before/after timings.
  4. Confusing bytes read from storage with bytes scanned after pruning.

Rule of thumb

Read the plan before resizing anything. First check queue time (operational fix: more clusters). If execution time dominates, check bytes scanned and shuffle/spill (query fix: filters, pruning, join strategy).

Worked example

A report takes 90 seconds. Query Profile shows 70 seconds queued and 20 seconds executing with 4 GB scanned. The fix is concurrency - raise min/max clusters - not rewriting the SQL or upsizing.

Operational extras

Use query history filters to find the top consumers per warehouse, and schedule alerting on long-running queries rather than waiting for complaints.

Creating Dashboards and Visualizations in Databricks

Anatomy of an AI/BI dashboard

A dashboard is a collection of datasets (SQL queries), visualizations built from those datasets, and pages that arrange them. Adding a dataset means writing SQL against tables in Unity Catalog; the dashboard executes it on a chosen SQL warehouse.

Key building blocks:

  • Parameters - declared on the dashboard, referenced inside dataset SQL with :param syntax; used to drive whole datasets.
  • Filters - attached to a visualization or page and bound to a field, enabling cross-filtering between visuals.
  • Visualization types - table, bar, line, area, scatter, pie, counter/KPI, pivot, heatmap, map, and text/markdown boxes.
  • Scheduling and refresh - datasets can be refreshed on a schedule and cached so viewers see fast results.

Sharing and permissions

Viewers need access to the dashboard and permission to run the warehouse and SELECT on the underlying tables (or the dashboard runs with the owner's credentials, depending on configuration). This chain is the most common sharing failure.

Traps

  1. Putting a filter on a visualization when you meant a dashboard-level parameter.
  2. Forgetting that a dashboard binds to a specific warehouse; a stopped or deleted warehouse breaks it.
  3. Expecting a schedule refresh to update a dataset that is embedded with hard-coded dates.
  4. Assuming a published dashboard is visible to users without data privileges.

Rule of thumb

Model once, visualize many. Put joins and business logic in a gold table or a shared dataset; keep visualizations as thin aggregations. This keeps filters consistent and refresh fast.

Worked example

A sales dashboard needs a region selector affecting four charts. Create one dashboard parameter region, reference it in each dataset's WHERE clause, and add a single page-level filter widget bound to it - not four separate visualization filters.

Design notes

Use counters for single KPIs, line charts for trends over time, and bar charts for categorical comparison. Keep pages under roughly a dozen visuals so each query stays responsive on a small warehouse.

Developing, Sharing, and Maintaining AI/BI Genie Spaces

What Genie is

An AI/BI Genie space lets business users ask questions in natural language and receive SQL-backed answers, tables, and charts. Genie is not a general chatbot: it reasons over a curated semantic context you provide, so answer quality is a function of curation quality.

What goes into a space

  • Tables, views, and metric views - the trusted assets Genie may query.
  • Instructions - plain-language guidance on business rules, definitions, and how to join tables.
  • Sample/benchmark questions - examples that teach Genie the expected phrasing and correct SQL; used to measure accuracy.
  • Comments and column descriptions on the underlying tables, which Genie reads.
  • Example SQL queries attached to assets for tricky calculations.

Sharing and maintaining

Share a space with users or groups and grant them access to the warehouse plus SELECT on the underlying objects. Monitor usage and accuracy through feedback (thumbs up/down), the benchmark questions, and audit logs. Maintenance is ongoing: when a table changes or a metric definition shifts, update instructions and re-validate benchmarks.

Traps

  1. Pointing Genie at raw bronze tables with cryptic column names and expecting good answers.
  2. Granting space access but not warehouse or table privileges - users see errors, not answers.
  3. Treating sample questions as cosmetic; they materially affect accuracy.
  4. Forgetting to update the space after schema changes, producing silently wrong SQL.

Rule of thumb

Curate narrow and well-described. Five well-documented gold tables with clear column comments, a metric view, and ten benchmark questions outperform fifty undocumented tables every time.

Worked example

A finance team asks "revenue last quarter by region." Success requires: a gold_revenue table with a revenue_usd column comment defining the metric, an instruction that fiscal_quarter differs from calendar quarter, and a benchmark question covering it. Then Genie returns a defensible answer.

Data Modeling with Databricks SQL

Dimensional modeling still rules

The expected pattern for analytics is a star schema: central fact tables at the grain of a business event (one row per order line, per session), surrounded by conformed dimension tables (customer, product, date). Keep facts narrow and numeric; keep dimensions descriptive. Snowflaking (dimensions referencing dimensions) is acceptable but adds joins.

Objects available in Databricks SQL

  • Views - stored SQL, always current, no storage cost, recomputed on read.
  • Materialized views - precomputed and incrementally refreshed; good for expensive aggregations consumed repeatedly.
  • Streaming tables - incrementally updated from streaming sources.
  • Metric views - a semantic layer defining measures, dimensions, and joins once so dashboards and Genie compute consistent numbers.
  • Constraints - NOT NULL and CHECK are enforced; PRIMARY KEY/FOREIGN KEY are informational and help the optimizer and lineage, not uniqueness.

Physical design choices

Delta handles layout. Choose liquid clustering on columns used in frequent filters; avoid deep partitioning. Denormalization is cheap in a lakehouse - a wide gold table often beats a complex join graph for BI.

Traps

  1. Assuming primary keys enforce uniqueness - they do not; duplicates require MERGE logic.
  2. Using a view where a materialized view is needed, forcing recomputation on every dashboard load.
  3. Partitioning by a high-cardinality column such as order_id, creating massive file counts.
  4. Redefining a metric differently in three dashboards instead of centralizing it in a metric view.

Rule of thumb

Grain first. Before writing any DDL, state the grain in one sentence ("one row per order line per day"). Every key, aggregation, and join follows from that sentence. If two facts have different grains, they are two tables.

Worked example

Orders and order lines: fact_order_line at order-line grain, dim_customer and dim_product as Type-2 slowly changing dimensions with surrogate keys, and dim_date for fiscal calendars. Gold aggregates are materialized views over the fact.

Securing Data

The privilege hierarchy

Unity Catalog secures objects in a nested hierarchy: metastore → catalog → schema → table/view/volume/function. Privileges granted at a higher level are inherited by everything beneath it. Typical analyst setup: USE CATALOG, USE SCHEMA, and SELECT on the relevant tables - all three are needed, which is a classic trap: SELECT alone produces "schema not found" style errors.

Common privileges: USE CATALOG, USE SCHEMA, SELECT, MODIFY, CREATE TABLE, CREATE VIEW, EXECUTE (functions), BROWSE, and ownership. Owners and metastore admins can grant to others.

Fine-grained controls

  • Row filters - a SQL user-defined function applied to a table that restricts which rows a given user sees (e.g., region = current_region()).
  • Column masks - a function that returns a transformed value (NULL, hashed, redacted) for sensitive columns such as email or SSN.
  • Dynamic views - a view using current_user() or is_member('group') to vary output by identity.
  • Attribute-based access - drive filters from group membership rather than per-user grants.

Identity and auditing

Access is granted to users, groups, and service principals (for automation); avoid personal access tokens in production. Audit logs record access and grant changes; lineage shows column-level data flow, useful for impact analysis before revoking a grant.

Traps

  1. Granting SELECT but forgetting USE CATALOG/USE SCHEMA.
  2. Assuming revoking a schema privilege removes inherited catalog-level access.
  3. Applying a row filter and expecting it to hide the column - filtering is row-level only.
  4. Sharing data outside the organization without checking workspace vs account-level identity.

Rule of thumb

Grant to groups, at the highest sensible level, and mask at the column. Use row filters for tenant or region isolation and column masks for PII; audit before you revoke.

Databricks Data Analyst cheat sheet

Quick reference

Understanding the Databricks Data + AI Platform

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

Quick reference

Managing Data

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

Quick reference

Importing Data

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

Quick reference

Executing Queries with Databricks SQL and SQL Warehouses

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

Quick reference

Analyzing Queries

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

Quick reference

Creating Dashboards and Visualizations

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

Quick reference

Developing, Sharing, and Maintaining AI/BI Genie Spaces

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

Quick reference

Data Modeling with Databricks SQL

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

Quick reference

Securing Data

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

Databricks Data Analyst key terms

Lakehouse

A data architecture that combines the cheap, flexible storage of a data lake with the management and performance features of a data warehouse, letting one copy of data serve BI, SQL, and machine learning workloads.

Related: Delta Lake, Medallion architecture, Unity Catalog
Unity Catalog

Databricks' centralized governance layer that stores metadata about catalogs, schemas, tables, volumes, and permissions, and enforces access control across all workspaces attached to a metastore.

Related: Metastore, Catalog, GRANT
Metastore

The top-level Unity Catalog container that holds catalogs and is assigned to one cloud region; workspaces are attached to it so users share the same objects and permissions.

Related: Unity Catalog, Catalog
Delta Lake

An open table format that adds a transaction log on top of Parquet files, providing ACID transactions, schema enforcement, and versioned history for tables stored in cloud object storage.

Related: Time travel, OPTIMIZE, Managed table
Medallion architecture

A layered data design pattern where raw ingested data lands in a bronze layer, is cleaned and conformed in silver, and is aggregated into business-ready gold tables for reporting.

Related: Lakehouse, Star schema, Auto Loader
Databricks workspace

The browser-based environment where users create notebooks, run queries, build dashboards, and manage compute; each workspace is tied to a cloud region and an attached metastore.

Related: Compute, Unity Catalog
Compute

The cluster of virtual machines that actually runs SQL queries and notebook code. Its size and type determine cost, concurrency, and how fast workloads finish.

Related: SQL warehouse, Serverless SQL warehouse, Photon
Catalog

The first level of the Unity Catalog three-part namespace, grouping related schemas (databases) that usually belong to one project, team, or environment such as dev or prod.

Related: Schema, Unity Catalog, Metastore
Schema

A container inside a catalog that holds tables, views, volumes, and functions; it is the middle part of the three-level name used when referencing an object in SQL.

Related: Catalog, Managed table
Managed table

A table whose data files are owned and placed by Databricks inside the metastore's managed storage location; dropping the table deletes both metadata and underlying data.

Related: External table, External location, Delta Lake
External table

A table whose data files live at a path you specify, often in a cloud storage location; dropping the table removes only the metadata and leaves the files untouched.

Related: Managed table, External location, Storage credential
Time travel

A Delta Lake feature that lets you query an earlier version or timestamp of a table using a version or timestamp clause, useful for auditing changes or recovering accidentally modified data.

Related: Delta Lake, VACUUM
OPTIMIZE

A command that compacts many small Delta files into fewer larger ones and can also apply clustering, improving read performance for downstream queries.

Related: Liquid clustering, VACUUM, Delta Lake
VACUUM

A maintenance command that deletes data files no longer referenced by the table and are older than a retention threshold, freeing storage but reducing how far back time travel can go.

Related: OPTIMIZE, Time travel
Liquid clustering

A table layout technique that lets Databricks automatically organize data by chosen columns without fixed partitions, and adapt the layout as new data arrives.

Related: OPTIMIZE, Delta Lake
COPY INTO

A SQL command that loads files from a cloud storage location into a Delta table, tracking which files were already processed so repeated runs do not duplicate data.

Related: Auto Loader, External location
Auto Loader

An incremental ingestion mechanism that detects new files arriving in cloud storage and processes them efficiently at scale, tracking discovered files so each is loaded once.

Related: COPY INTO, Medallion architecture
DBFS

A Databricks file system layer that mounts cloud storage so files can be browsed from the workspace; many paths under it are legacy and are not governed by Unity Catalog permissions.

Related: External location, Unity Catalog
SQL warehouse

A compute resource purpose-built for running SQL and serving BI tools. It has configurable size and scaling behavior and is separate from general-purpose clusters used for notebooks.

Related: Serverless SQL warehouse, Compute, Query history
Serverless SQL warehouse

A SQL warehouse whose compute is fully managed by Databricks, starting quickly and scaling automatically with no cluster configuration required from the user.

Related: SQL warehouse, Compute
Query editor

The Databricks SQL interface where analysts write, run, and save SQL statements against a selected warehouse, view results as tables or charts, and share queries with others.

Related: SQL warehouse, Visualization
Parameter marker

A named placeholder in a SQL statement that receives its value at run time from a widget, dashboard filter, or API call, allowing one saved query to serve many scenarios.

Related: Dashboard, Query editor
Query history

A log of executed statements showing who ran what, on which warehouse, with duration and status, used for auditing, troubleshooting failures, and comparing performance over time.

Related: Query profile, SQL warehouse
Query profile

A visual breakdown of how a query executed, showing operators, rows processed, time spent per stage, and where time or data volume bottlenecks occurred.

Related: Query history, Photon
Photon

Databricks' vectorized query engine written in C++ that accelerates SQL and DataFrame workloads on Databricks compute, often reducing query run time substantially.

Related: Query profile, Compute
Result cache

A warehouse-level cache that stores the output of a previously run query so an identical repeat query returns almost instantly without rescanning the underlying data.

Related: Query history, SQL warehouse
Dashboard

A shareable collection of saved queries, visualizations, and filters presented on one page, with its own permission settings and optional refresh schedules.

Related: Visualization, Parameter marker
Visualization

A chart or table configuration applied to a query result set, choosing axes, series, aggregation, and chart type so trends and comparisons are easy to read.

Related: Dashboard, Query editor
Genie space

A configured AI/BI assistant environment where business users ask questions in natural language and receive answers backed by chosen tables and curated instructions.

Related: Trusted asset, Benchmark
Trusted asset

A curated item in a Genie space, such as a sample question, example SQL statement, or defined measure, that guides the assistant toward correct answers for common requests.

Related: Genie space, Benchmark
Benchmark

A stored set of representative questions and expected answers used to evaluate whether a Genie space responds accurately, helping authors spot gaps before sharing it broadly.

Related: Genie space, Trusted asset
Metric view

A Databricks SQL object that defines reusable measures, dimensions, and joins on top of source tables, so consistent business metrics can be queried without rewriting aggregation logic.

Related: Star schema, Materialized view
Star schema

A dimensional model where a central fact table stores measurable events and surrounding dimension tables hold descriptive attributes, joined by keys for simple, fast analytical queries.

Related: Medallion architecture, Metric view
Materialized view

A database object that stores the precomputed result of a query and can be refreshed incrementally, trading storage and refresh time for faster reads by consumers.

Related: Metric view, Schema
GRANT

The SQL statement used to give a user, group, or service principal a specific privilege such as SELECT or MODIFY on a catalog, schema, table, or view.

Related: Unity Catalog, Row filter, Column mask
Row filter

A Unity Catalog table or view property that attaches a SQL function to restrict which rows each user can see based on their identity or group membership.

Related: Column mask, GRANT
Column mask

A Unity Catalog feature that applies a function to a column so different users see different values, such as full values for one team and masked values for another.

Related: Row filter, GRANT
Service principal

A non-human identity used by applications, jobs, and automated tools to authenticate to Databricks and be granted permissions, instead of sharing a person's credentials.

Related: GRANT, Unity Catalog

Databricks Data Analyst study plan

8 weeks · About 6 hours per week

Week 1

Understanding of Databricks Data + AI Platform & Executing queries using Databricks SQL and Databricks SQL Warehouses

  • Diagram the Databricks account-to-table hierarchy (account, workspace, metastore, catalog, schema, table, volume) from memory and label where Unity Catalog permissions apply

  • Compare serverless, pro, and classic SQL warehouse behavior and select an appropriate size and auto-stop policy for three stated workload scenarios

  • Write and execute ten SELECT queries against a sample catalog covering filtering, sorting, aliasing, CASE expressions, and NULL handling

  • Navigate Catalog Explorer, the SQL editor, query history, and the compute panel well enough to locate any saved query, its owner, and its run history

Your checkpoint

A one-page annotated architecture map plus a saved folder of 10 working queries with a written note on which warehouse size you chose for each scenario and why

Week 2

Executing queries using Databricks SQL and Databricks SQL Warehouses

  • Compose multi-table queries using INNER, LEFT, and FULL OUTER joins and justify each join choice in a comment

  • Aggregate data with GROUP BY, HAVING, COUNT DISTINCT, and conditional aggregation to answer funnel and cohort-style questions

  • Solve ranking and running-total problems with window functions (ROW_NUMBER, RANK, SUM OVER, LAG/LEAD)

  • Use CTEs and subqueries to break a multi-step transformation into readable, testable stages

  • Parameterize a query with the query parameter widget and re-run it against two different values without editing SQL

Your checkpoint

A 15-query worksheet against a sample schema where each query answers a stated business question and all 15 execute without error

Week 3

Executing queries using Databricks SQL and Databricks SQL Warehouses & Analyzing Queries

  • Open the query profile for a slow query and identify the operator consuming the most time

  • Distinguish shuffle, spill, and scan bottlenecks and state one remediation for each

  • Read EXPLAIN and EXPLAIN EXTENDED output to confirm predicate pushdown and join strategy

  • Rewrite three slow queries using filtering before joins, fewer wide shuffles, or pre-aggregation

  • Apply Photon and result caching deliberately and measure the effect with and without each

Your checkpoint

A before-and-after table for 3 rewritten queries showing duration, rows read, and bytes read, with one sentence explaining each improvement

Week 4

Analyzing Queries & Executing queries using Databricks SQL and Databricks SQL Warehouses

  • Interpret every metric shown in a query profile (rows read, bytes read, spill, shuffle, task counts, queue time) and explain what each implies

  • Diagnose a slow dashboard tile by tracing it back to its source query and warehouse queueing behavior

  • Evaluate partition and liquid clustering choices for a given table and predict their effect on scan volume

  • Complete a timed 30-question practice set covering domains 1, 4, and 5 and review every miss against documentation

Your checkpoint

A written query-analysis report on 3 problematic queries, each with a root cause, a fix, and post-fix measurements, plus a practice-set score of at least 80%

Week 5

Creating Dashboards and Visualizations in Databricks

  • Build a dashboard containing at least four chart types (bar, line, pie or counter, table) from saved queries

  • Add a dashboard-level filter and a parameter so a viewer can switch the analysis dimension without editing SQL

  • Configure a refresh schedule and set appropriate permissions for a viewer group

  • Choose an appropriate visualization for a given metric shape and document when a table beats a chart

  • Export or embed dashboard results and verify the numbers match the underlying query output

Your checkpoint

A published dashboard with 4+ visualizations, one working filter, one parameter, and a refresh schedule, shared with a small group and screenshotted for review

Week 6

Creating Dashboards and Visualizations in Databricks & Developing, Sharing, and Maintaining AI/BI Genie spaces

  • Create an AI/BI Genie space pointed at a curated set of tables and verify it answers basic questions correctly

  • Write space instructions, synonyms, and example SQL that steer Genie toward correct joins and metric definitions

  • Maintain a benchmark list of 12 natural-language questions with verified expected answers and score Genie's accuracy against it

  • Iterate on space configuration (table selection, sample values, instructions) and measure whether accuracy improves between runs

  • Set sharing and permission settings so the right audience can query the space without over-exposing tables

Your checkpoint

A Genie space with a 12-question benchmark, a documented accuracy score before and after tuning, and a short list of the instruction changes that produced the gain

Week 7

Developing, Sharing, and Maintaining AI/BI Genie spaces & Managing Data & Importing Data

  • Import data using the UI upload, CREATE TABLE from files, and COPY INTO, and state when each is appropriate

  • Create a managed table and an external table and describe how each behaves on DROP

  • Run OPTIMIZE and VACUUM on a table and explain the tradeoffs of each, including how VACUUM affects time travel

  • Query historical table versions with time travel and inspect lineage to identify upstream and downstream dependencies

  • Fix three deliberately failing Genie questions by improving table comments, column comments, or instructions

Your checkpoint

An ingestion and maintenance runbook covering the three import paths, the OPTIMIZE/VACUUM schedule, and the three Genie fixes with the diff that resolved each

Week 8

Data Modeling with Databricks SQL & Securing Data & Analyzing Queries

  • Design a star schema with one fact table and two dimension tables, assign surrogate keys, and document grain in one sentence

  • Apply primary key, foreign key, and NOT NULL constraints and explain how informational constraints differ from enforced ones

  • Grant catalog, schema, and table privileges to a group and verify access with a least-privilege test account

  • Implement a row filter and a column mask on a table and confirm both behave as intended for two different users

  • Take a full-length timed practice exam and produce a prioritized remediation list from the results

Your checkpoint

A secured star schema in a scratch catalog (constraints + grants + row filter + column mask demonstrated with two users) plus a full-length practice exam score and a remediation list of your five weakest topics

Milestones

Week 2: All 15 worksheet queries run successfully, and you can write a 3-table join with a window function from a blank editor in under 10 minutes

Week 4: Practice set for domains 1, 4, and 5 scored at or above 80%, and at least one rewritten query shows a measurable drop in bytes read

Week 6: Dashboard is published and shareable, and the Genie space answers at least 9 of 12 benchmark questions correctly after tuning

Week 8: Full-length practice exam completed under time with every domain above 70% and the secured schema demo passing both the allowed and the denied user test

Databricks Data Analyst exam day

Your checklist

Government-issued photo ID

Bring the exact ID name that matches your registration. Proctors compare name spelling character by character; a mismatch can block check-in. Confirm with the provider which ID types are accepted for your region and whether a secondary ID is required.

Stable internet connection plus a backup

Wired or strong Wi-Fi for the primary connection, and a phone hotspot or second network ready as a fallback. Test the connection at the same time of day as your appointment slot.

Working webcam and microphone

Built-in or external, both functional. The proctor uses the webcam for the room scan and identity check, and the mic for any interventions during the session.

Quiet, private, well-lit room

Door closed, no other people entering, no second screen visible, desk cleared of paper, notes, phones, and smart devices unless the provider explicitly permits them.

Provider software installed and updated

If the delivery platform uses a secure browser or lockdown app, install and launch it before exam day so updates do not run during check-in. Confirm with the provider which browser/OS versions are supported.

Appointment confirmation and time zone verified

Re-read the confirmation email and convert the start time to your local zone. Note whether check-in opens early and whether late entry is allowed. Confirm with the provider.

Login credentials for the certification portal

Know your username and password and have a recovery path (email or phone) in case of a reset prompt at check-in.

Power and hardware readiness

Laptop charged and plugged in, charger within reach, external mouse if you prefer one, and any adapters you need. A battery failure mid-exam is typically not a valid reschedule reason.

Rules for scratch materials confirmed

Some providers allow only an on-screen notepad or a supplied erasable board; paper and pens are often prohibited. Confirm with the provider what is allowed before you sit down.

Phone silenced and out of reach

Even picking it up during a proctored session can trigger a violation flag. Set it aside before check-in begins.

Water and a bathroom break taken beforehand

Scheduled breaks may not be available on a proctored exam. Assume you cannot leave the camera view until you finish. Confirm with the provider.

A short pre-exam review sheet you built yourself

One page of SQL patterns you keep fumbling: window function framing, QUALIFY-style filtering, date truncation, null handling in aggregates, join semantics, CTE structure, and Databricks-specific syntax you have practiced.

Plan the day

72 hours before

Take one full-length practice set under timed conditions and log every miss by topic. The Databricks Data Analyst exam is weighted toward SQL and Databricks SQL warehouse/dashboard work, so rank your weak areas by how often they appear in the objectives list, then study only the top three.

72 hours before

Re-read the official exam objectives page and confirm the current question count, time limit, and delivery format. Do not rely on secondhand blog posts; policies change. Confirm with the provider.

72 hours before

Run the provider's system check on the exact machine and network you will use, including webcam, mic, and bandwidth. Fix any failures now, not on exam morning.

72 hours before

Protect your sleep schedule. Do not plan late-night cramming in the final three nights; recall on SQL pattern recognition degrades quickly with fatigue.

24 hours before

Do a light pass only: re-solve 10-15 missed practice questions, reread your own review sheet, and stop. No new topics.

24 hours before

Prepare the room: clear the desk, remove papers and devices, check lighting behind and in front of you, and confirm the door closes.

24 hours before

Lay out ID, charger, and any permitted materials by the desk. Confirm the appointment time in local time and set two alarms.

Morning of

Eat, hydrate, and avoid starting any new technical task that could eat the hour before check-in.

Morning of

Close every other application, disable notifications and auto-updates, and restart the machine once so background processes settle.

Check-in

Enter check-in well ahead of the start time. Identity verification and the room scan routinely consume a chunk of the appointment. Confirm with the provider how early you may check in and what happens if you are late.

During the test

Budget roughly two minutes per question and keep an eye on the clock at the one-third and two-thirds marks. If the count or time limit differs from what you planned for, recompute the budget immediately.

During the test

First pass: answer everything you can decide in under 90 seconds, and flag the rest. Never leave a question blank if the platform permits review later.

During the test

Read each stem to the end before choosing: watch for 'select two', 'EXCEPT', 'least likely', and questions asking about behavior rather than syntax.

During the test

For SQL questions, mentally run the query against a tiny invented table. Check join fan-out, null behavior in aggregates and comparisons, and window frame defaults before locking in an answer.

During the test

Second pass: return to flagged items with your remaining time. Change an answer only when you can name the specific reason the first choice was wrong.

After the test

Note where you felt uncertain while it is fresh. Score reporting timing varies by provider and delivery mode; confirm with the provider rather than refreshing the portal repeatedly.

Rules to check

  • Present a valid government-issued photo ID whose name matches your registration exactly. A mismatch can result in being turned away, so resolve name differences with the provider before exam day. Confirm with the provider which documents qualify.

  • The exam is closed book. Notes, textbooks, second screens, phones, smart watches, and unapproved software are prohibited, and the proctor may pause or end the session for a violation. Confirm with the provider what materials, if any, are permitted.

  • Stay in the camera's view for the entire session. Leaving the frame, covering the camera, or having another person enter the room is normally treated as a conduct violation, not a technical glitch. Confirm with the provider's specific position on breaks.

  • Assume no scheduled breaks and no time extension for technical interruptions. If a disconnection occurs, follow the proctor's instructions and do not restart the exam on your own. Confirm with the provider how outages are handled and whether time is restored.

  • Scores are reported by topic area so a weak domain can be diagnosed, but the pass decision is based on the overall result, not on passing every section. Confirm with the provider the current passing threshold and score report format.

  • Do not screenshot, copy, record, or discuss specific exam questions afterward. Most certification agreements prohibit disclosing exam content, and doing so can void a result even after it is issued.

  • Reschedule or cancel within the provider's stated window if you cannot attend; no-shows typically forfeit the fee. Confirm with the provider the exact deadline and any fees before your appointment.

  • Treat timing as a scoring rule: unanswered questions cost the same as wrong ones. Answer every item and use the review feature if the platform provides one. Confirm with the provider whether your exam form allows returning to previous questions.

Databricks Data Analyst FAQ

How many Databricks Data Analyst practice questions are available?

The practice set has 199 Databricks Data Analyst questions. Each one includes the choices, the correct answer, and an explanation, and the set is free to read with no signup.

How many Databricks Data Analyst flashcards are available?

The flashcard set has 32 Databricks Data Analyst cards. Each card shows the term and its definition.

Which topics does the Databricks Data Analyst study material cover?

The Databricks Data Analyst study material has 9 sections, including Understanding the Databricks Data + AI Platform, Managing Data, and Importing Data.

What format facts does the catalog list for the Databricks Data Analyst exam?

The catalog lists a duration of 90 minutes and these item types: multiple choice and proctored for the Databricks Data Analyst exam.