Databricks Certified Data Analyst Associate practice questions

Question 1

A sales operations analyst is tasked with stacking this-quarter and last-quarter order rows into one combined list and must decide between UNION ALL and UNION DISTINCT, given that some identical orders appear in both source queries. Per the SQL reference, which option matches?

Accepted Answer

UNION ALL keeps every row including duplicates, while UNION DISTINCT removes duplicates and is the default when neither keyword is given.

Answer

UNION ALL removes the duplicate rows, while UNION DISTINCT keeps duplicates, and UNION ALL is the default applied when neither keyword appears.

Answer

Both UNION ALL and UNION DISTINCT drop duplicates; the keywords only change whether the rows are sorted before the result is returned.

Answer

UNION ALL returns only the rows common to both queries, while UNION DISTINCT returns the full union, with DISTINCT applied as the default.

Question 2

A data analyst on a finance BI team is designing a dashboard and adds a Date Range parameter so viewers can scope results to a custom window. Per the documentation, what happens when this parameter type is chosen?

Accepted Answer

Two parameters are created, designated by .min and .max suffixes, that together define the selectable range.

Answer

A single parameter is created that stores both endpoints as a comma-separated text string value.

Answer

Two parameters are created, but only the .max suffix parameter is bound to the underlying query.

Answer

A single parameter is created and the range endpoints must be entered as two separate static filters.

Question 3

An ops analytics team is hitting a wall on a join-heavy report query that the query profile is flagging as DATA_SPILL, and the analytics engineer must decide on a remediation. Per the documented performance insights, which option matches the recommended action?

Accepted Answer

DATA_SPILL means data spilled to disk while executing an operator because the data size did not fit in memory; the recommended action is to increase warehouse size to increase available memory.

Answer

DATA_SPILL means the analyst forgot to tag the table in Unity Catalog and the recommendation is to add a 'spill=false' tag to the source table to clear the performance insight from the query history.

Answer

DATA_SPILL means the SQL warehouse was paused mid-query and the recommended action is to delete and recreate the SQL warehouse from scratch through Catalog Explorer's external locations panel.

Answer

DATA_SPILL is an informational-only alert with no recommended action and the analyst should disable performance insights workspace-wide before running any further analytical queries on the warehouse.

Question 4

A platform team is designing a Genie space for an enterprise BI deployment and the data analyst must explain how Unity Catalog row filters and column masks are enforced when end users ask Genie questions. Per the documentation, which option matches?

Accepted Answer

Data access in a Genie space is governed by Unity Catalog; the generated SQL query runs against the data using the space author's credentials, and row filters and column masks are automatically enforced per user.

Answer

Row filters and column masks defined in Unity Catalog are documented as completely bypassed by every Genie space query, regardless of the space author's setup or the asking user's privileges on the tables.

Answer

Row filters and column masks are only enforced when the user runs the SQL outside of Genie in the SQL editor, and Genie spaces are documented as a permanent bypass mechanism for these protections in any case.

Answer

Row filters and column masks are stored in Catalog Explorer Tags and are unrelated to Genie spaces; Genie spaces execute queries with the asking user's personal access token without consulting Unity Catalog at all.

Question 5

A platform performance engineer is tasked with explaining how Photon accelerates Databricks SQL queries and what happens when a query includes an operation Photon does not support. Which description is accurate?

Accepted Answer

Photon runs a native C++ engine for supported operations while Catalyst plans, falling back to Spark otherwise

Answer

Photon replaces the Catalyst optimizer with its own AI planner and refuses to run unsupported operations

Answer

Photon rewrites each query into Python UDFs executed row by row to maximize operator compatibility levels

Answer

Photon caches every query result in memory so repeated unsupported operations no longer need execution

Question 6

A compliance department is evaluating Databricks for regulated reporting and the data analyst must explain at what granularity Unity Catalog captures lineage for queries on the platform. Per the documentation, which option matches?

Accepted Answer

Unity Catalog captures runtime data lineage across queries run on Databricks. Lineage is supported for all languages and is captured down to the column level, and includes notebooks, jobs, and dashboards related to the query.

Answer

Unity Catalog only captures lineage at the catalog level, not at the table or column level, and never includes notebooks, jobs, or dashboards related to the query in the lineage record.

Answer

Unity Catalog only captures lineage for queries run from SQL warehouses, never from notebooks or jobs, and is limited to lineage between two tables in the same schema in a single workspace.

Answer

Unity Catalog requires the analyst to manually annotate lineage relationships on every query, and the metastore never derives lineage automatically at any granularity from runtime query execution.

Question 7

A healthcare reporting team is rolling out PII controls and the data analyst must configure row filters and column masks for a Unity Catalog table that holds patient demographics. Per the documentation, which option matches the documented purpose of each control?

Accepted Answer

Row filters let you control which rows a user can access in a table based on custom logic, and column masks control what values users see in specific columns, depending on who they are.

Answer

Row filters and column masks are deprecated features that are no longer enforced anywhere in Databricks SQL or notebooks; Databricks recommends bypassing them entirely in every analytical workspace today.

Answer

Row filters control what values users see in specific columns depending on who they are, and column masks let you control which rows a user can access in a table based on custom logic, in any deployment.

Answer

Row filters and column masks both write rows back into the underlying Delta table on every read so that the data is permanently modified after the first analyst query and cannot be recovered in any case.

Question 8

An organization is migrating semi-structured JSON event logs into a Delta table against a provided schema and needs to capture any fields that are absent from the schema, mismatch its data types, or differ only by case, all without losing that data during ETL. Per the documentation, which option matches?

Accepted Answer

Set the rescuedDataColumn option to a column name so the rescued data column captures the unparsed fields.

Answer

Set the parser mode option to DROPMALFORMED so records with any unexpected fields are removed from output.

Answer

Enable the schema option on read_files so the unified inferred schema absorbs the unexpected fields itself.

Answer

Run COPY INTO so its idempotent load simply skips records whose fields do not match the Delta schema.

Question 9

A platform team is rolling out Databricks SQL as the central analytical surface for a marketing data mart and the analyst is being asked what role the data warehouse should play. Per the documentation, which option matches?

Accepted Answer

Your data warehouse should serve as the single source of truth for your data marts, which is what Databricks SQL on the lakehouse is designed to support without siloing or redundant copies of business data.

Answer

The data warehouse should be replicated nightly into per-analyst CSVs on each analyst's local laptop, and is never the single source of truth for any data mart in any Databricks deployment topology.

Answer

The data warehouse should serve as a write-only archival vault for raw bronze layer records and is intentionally never queried by any data mart or downstream BI dashboard in a Databricks workspace deployment.

Answer

The data warehouse should be hosted on a per-team isolated Hadoop cluster outside the lakehouse, and is documented as never being the single source of truth for any data mart in any deployment.

Question 10

A reporting analyst is designing an ORDER BY on a nullable revenue column and must decide where NULL values will land when no NULLS FIRST or NULLS LAST is written, for both ascending and descending sorts. Per the SQL reference, which option matches?

Accepted Answer

With no nulls clause, NULLs sort first when the order is ASC and sort last when the order is DESC.

Answer

With no nulls clause, NULLs sort last for both ASC and DESC sorts unless NULLS FIRST is added in.

Answer

With no nulls clause, NULLs sort last when the order is ASC and sort first when the order is DESC.

Answer

With no nulls clause, NULLs are dropped from the result entirely unless an explicit nulls clause is set.

Question 11

A data analyst must decide between OVER and AGGREGATE OVER for a calculation that should respond to whichever groupings the chart already applies. Per the documentation, which statement about AGGREGATE OVER supports this choice?

Accepted Answer

It inherits its groups from the surrounding visualization and can incorporate data outside the group.

Answer

It overrides the chart's groupings entirely and recomputes every single metric at the full table grain instead.

Answer

It requires an explicit PARTITION BY clause before it can return any grouped result at all.

Answer

It can reference only the rows inside the current visualization group, with no access beyond it.

Question 12

A marketing analytics team is hitting a wall when the query profile flags DATA_SKEW on a heavily grouped campaign report, and the data analyst must choose a remediation. Per the documented performance insights, which action matches the recommendation?

Accepted Answer

Review the data distribution and apply key salting or pre-aggregation to balance the workload across resources.

Answer

Increase the SQL warehouse size to add memory so the skewed partitions no longer spill their data to disk.

Answer

Enable disk caching on the warehouse so that repeated reads of the skewed keys are served from local node storage.

Answer

Reduce the warehouse to a smaller size so that fewer resources receive the unevenly distributed partitions.

Question 13

A team needs to create its first Genie space from scratch in the workspace, and the data analyst is walking through the creation steps. Per the setup documentation, what is the correct sequence?

Accepted Answer

Click Genie Spaces in the sidebar, click New in the upper-right corner, then choose the data sources to include.

Answer

Publish a dashboard first, because a Genie space can only ever be auto-generated and is never created on its own.

Answer

Upload a file into a chat window, which then automatically provisions a brand-new empty Genie space around it.

Answer

Open Unity Catalog, define a metric view there, and a matching Genie space is created automatically alongside it.

Question 14

A platform team is rolling out Databricks for a marketing analytics group and the data analyst must explain what compute engine the Databricks data intelligence platform is built on. Per the lakehouse documentation, which option matches?

Accepted Answer

Databricks is built on Apache Spark, which provides a massively scalable engine that runs on compute resources decoupled from storage, and the lakehouse extends it with Unity Catalog and Delta Lake.

Answer

Databricks is built on a single-node DuckDB process per workspace, with no distributed engine of any kind, and is therefore unsuitable for warehouse-scale analytics on certified datasets.

Answer

Databricks is built on Apache Hive metastore as the primary compute engine and stores all interactive analyst query results in a single shared HDFS namenode by default in every workspace.

Answer

Databricks is built on Presto only, with no Apache Spark involvement, and routes every Databricks SQL query through a federation gateway to an external Trino coordinator outside the workspace.

Question 15

An ops analytics team is rolling out Unity Catalog and the BI lead must choose between several Databricks UIs to grant permissions, view object ownership, and create external locations. Per the documentation, which option matches?

Accepted Answer

Use Catalog Explorer to create catalogs, create shares, manage external locations, view and change object ownership, and grant and revoke permissions on all objects, including AI-generated comments on data and AI objects.

Answer

Use the SQL alerts editor; it is the only Databricks surface where a data analyst can grant and revoke permissions on tables, manage external locations, and view the AI-generated comments for any object.

Answer

Use the Lakeflow Jobs scheduler; it is the documented place to grant and revoke permissions on all Unity Catalog objects, manage external locations, and view AI-generated comments on tables for a workspace.

Answer

Use the Genie Space settings page; it is the only documented surface for granting and revoking permissions on all Unity Catalog tables, views, schemas, and catalogs in any Databricks workspace deployment.

Databricks Certified Data Analyst Associate

Sample questions

Sources

Similar exams