Databricks-Certified-Data-Analyst-Associate Databricks Certified Data Analyst Associate Exam Question and Answers

The correct answer is C because the tag key is confidential, and Databricks workspace search uses the syntax tag: < tag_key > to search by tag key. Therefore, the correct search text is tag:confidential. Option A uses a Boolean-style syntax that is not the documented Databricks tag search syntax. Option B uses an equals sign, and Option D is not valid workspace search syntax.

Official documentation extract used: Databricks states that to search by tag key alone, use the syntax tag: < tag_key > .

Option B is correct. The statement explicitly sets the schema/database location to dbfs:/accounting/data. In Databricks SQL, CREATE SCHEMA supports a LOCATION schema_directory, and the documentation explains that the schema directory is the filesystem path where the schema is created. Because the user supplied LOCATION ' dbfs:/accounting/data ' , Databricks uses that path instead of the default warehouse directory. Option D would apply only when no custom location is supplied. References: Databricks CREATE SCHEMA documentation.

Query History in Databricks SQL is intended for reviewing executed queries, understanding their execution plans, and identifying performance issues or errors for debugging purposes. It allows users to analyze query duration, resources used, and potential bottlenecks. However, Query History does not provide any capability to automate the execution of queries across multiple warehouses; automation must be handled through jobs or external orchestration tools, not through the Query History feature itself.

Option B is correct. PIVOT rotates unique values from rows into separate columns, which is exactly the process of converting long-format data into wide-format data. UNPIVOT does the reverse. SUM is an aggregate function, WHERE filters rows, and TRANSFORM is not the SQL keyword used for this reshaping task. Official Databricks extract: the PIVOT clause “rotat[es] unique values from a column into separate columns.”

Partner Connect is a feature that allows you to easily connect your Databricks workspace to Fivetran and other ingestion partners using an automated workflow. You can select a SQL warehouse or a cluster as the destination for your data replication, and the connection details are sent to Fivetran. You can then choose from over 200 data sources that Fivetran supports and start ingesting data into Delta Lake. References: Connect to Fivetran using Partner Connect, Use Databricks with Fivetran

Databricks SQL uses ANSI SQL as its standard SQL dialect, which means it follows the SQL specifications defined by the American National Standards Institute (ANSI). This makes it easier to migrate existing SQL queries from other data warehouses or platforms that also use ANSI SQL or a similar dialect, such as PostgreSQL, Oracle, or Teradata. By using ANSI SQL, Databricks SQL avoids surprises in behavior or unfamiliar syntax that may arise from using a non-standard SQL dialect, such as Spark SQL or Hive SQL12. Moreover, Databricks SQL also adds compatibility features to support common SQL constructs that are widely used in other data warehouses, such as QUALIFY, FILTER, and user-defined functions2. References: ANSI compliance in Databricks Runtime, Evolution of the SQL language at Databricks: ANSI standard by default and easier migrations from data warehouses

Option B is correct. In Databricks, when a managed table is dropped, Databricks removes the table and its associated managed storage location. For an external table, Databricks removes only the table metadata; the underlying external data files are not deleted. Therefore, dropping both tables deletes the managed table’s data but leaves the external table’s data untouched. Official Databricks documentation states that DROP TABLE “deletes the table and removes the directory associated with the table from the file system if the table is not an EXTERNAL table,” and for external tables, Unity Catalog “removes the table metadata but does not delete the underlying data files.”

 Option A uses the SELECT DISTINCT statement to remove duplicate rows from the table_bronze and create a new table table_silver with the deduplicated data. This is the correct way to deduplicate data using Spark SQL12. Option B simply inserts all the rows from table_bronze into table_silver, without removing any duplicates. Option C is not a valid syntax for Spark SQL, as there is no MERGE DEDUPLICATE statement. Option D appends all the rows from table_bronze into table_silver, without removing any duplicates. Option E overwrites the existing data in table_silver with the data from table_bronze, without removing any duplicates. References: Delete Duplicate using SPARK SQL, Spark SQL - How to Remove Duplicate Rows

 Databricks SQL uses a query cache to store the results of queries that have been executed previously. This improves the performance and efficiency of repeated queries. To determine if a query result was pulled from the cache, you can go to the Query History tab in the Databricks SQL UI and click on the text of the query. A slideout will appear on the right side of the screen, showing the query details, including the cache status. If the result came from the cache, the cache status will show “Cached”. If the result did not come from the cache, the cache status will show “Not cached”. You can also see the cache hit ratio, which is the percentage of queries that were served from the cache. References: The answer can be verified from Databricks SQL documentation which provides information on how to use the query cache and how to check the cache status. Reference link: Databricks SQL - Query Cache

The correct answer is C because the task requires creating or replacing a managed table with an explicitly defined schema. In Databricks SQL, column definitions are placed directly after the table name inside parentheses. The USING clause is for specifying a data source format, not for defining columns. Option C correctly uses CREATE OR REPLACE TABLE database_name.table_name (...), which recreates the table if it already exists and creates an empty table with the specified schema.

Official documentation extract used: Databricks CREATE TABLE syntax supports [CREATE OR] REPLACE followed by the table name and a table_specification containing column identifiers and column types.

Option B is correct. A 20 MB CSV file with about 50,000 records is small enough for a local pandas workflow. A real-time streaming solution, a distributed Spark cluster, or Hadoop MapReduce would add unnecessary complexity for a small single-file batch analysis. Databricks documentation explains that pandas is available in Databricks Runtime and can be used for data analysis, while pandas API on Spark is useful when pandas-style workloads need to scale beyond smaller datasets. Databricks also notes that pandas does not scale out to big data, which is exactly why Spark or pandas API on Spark is used for larger workloads, not for a small 20 MB file.

 A Dashboard Parameter is a parameter that is configured for one or more visualizations within a dashboard and appears at the top of the dashboard. The parameter values specified for a Dashboard Parameter apply to all visualizations reusing that particular Dashboard Parameter1. Therefore, if the analyst chooses “Dashboard Parameter” for the query parameter associated with the area chart, the area chart will use whatever is selected in the Dashboard Parameter along with all of the other visualizations in the dashboard that use the same parameter. This allows the user to filter the data across multiple visualizations using a single parameter widget2. References: Databricks SQL dashboards, Query parameters

Option D is correct. The column tags is an array, so comparing the entire array to the scalar string ' sql ' is incorrect. The correct Databricks SQL function is array_contains(array, value), which returns true when the array contains the specified value. The uploaded document has a spelling error, array_conatins; the corrected Databricks function name is array_contains. Official Databricks documentation states that array_contains “returns true if array contains value.”

Based on the images you sent, the two statements are SQL queries for different types of joins between the customers and orders tables. A join is a way of combining the rows from two table references based on some criteria. The join type determines how the rows are matched and what kind of result set is returned. The first statement is a query for a LEFT SEMI JOIN, which returns only the rows from the left table reference (customers) that have a match with the right table reference (orders) on the join condition (customer_id). The second statement is a query for a LEFT ANTI JOIN, which returns only the rows from the left table reference (customers) that have no match with the right table reference (orders) on the join condition (customer_id). Therefore, the result sets for the two statements will differ in the following way:

The first statement will return a subset of the customers table that contains only the customers who have placed at least one order. The number of rows returned will be less than or equal to the number of rows in the customers table, depending on how many customers have orders. The number of columns returned will be the same as the number of columns in the customers table, as the LEFT SEMI JOIN does not include any columns from the orders table.

The second statement will return a subset of the customers table that contains only the customers who have not placed any order. The number of rows returned will be less than or equal to the number of rows in the customers table, depending on how many customers have no orders. The number of columns returned will be the same as the number of columns in the customers table, as the LEFT ANTI JOIN does not include any columns from the orders table.

The other options are not correct because:

A. The first statement will not return all data from the customers table, as it will exclude the customers who have no orders. The second statement will not return all data from the orders table, as it will exclude the orders that have a matching customer. Neither statement will fill in any missing data with NULL, as they do not return any columns from the other table.

C. There is a difference between the result sets for both statements, as explained above. The LEFT SEMI JOIN and the LEFT ANTI JOIN are not equivalent operations and will produce different outputs.

D. Both statements will not fail, as Databricks SQL does support those join types. Databricks SQL supports various join types, including INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, LEFT SEMI, LEFT ANTI, and CROSS. You can also use NATURAL, USING, or LATERAL keywords to specify different join criteria.

E. The first statement will not return only the customer_id from the orders table, as it will return all columns from the customers table. The second statement is correct, but it is not the only difference between the result sets.

The result set provided shows a combination of grouping by two columns (group_1 and group_2) with subtotals for each level of grouping and a grand total. This pattern is typical of a GROUP BY ... WITH ROLLUP operation in SQL, which provides subtotal rows and a grand total row in the result set.

Considering the query options:

A) Option A: GROUP BY group_1, group_2 INCLUDING NULL - This is not a standard SQL clause and would not result in subtotals and a grand total.

B) Option B: GROUP BY group_1, group_2 WITH ROLLUP - This would create subtotals for each unique group_1, each combination of group_1 and group_2, and a grand total, which matches the result set provided.

C) Option C: GROUP BY group_1, group 2 - This is a simple GROUP BY and would not include subtotals or a grand total.

D) Option D: GROUP BY group_1, group_2, (group_1, group_2) - This syntax is not standard and would likely result in an error or be interpreted as a simple GROUP BY, not providing the subtotals and grand total.

E) Option E: GROUP BY group_1, group_2 WITH CUBE - The WITH CUBE operation produces subtotals for all combinations of the selected columns and a grand total, which is more than what is shown in the result set.

The correct answer is Option B, which uses WITH ROLLUP to generate the subtotals for each level of grouping as well as a grand total. This matches the result set where we have subtotals for each group_1, each combination of group_1 and group_2, and the grand total where both group_1 and group_2 are NULL.

Option B is correct. The function to_date converts a timestamp or date-like expression to a date value, which matches the requirement to extract the date from the timestamp field. unix_timestamp converts to a Unix timestamp value, date_format formats a date or timestamp as a string, and from_unixtime converts Unix time into a timestamp/string representation. Official Databricks documentation states that to_date(expr [, fmt]) returns the expression cast to a date.

Option C is correct after correcting the formatting and typing errors in the uploaded option text. The analyst needs explode( " record " ) because the record column is an array, and the requirement is to return one array item per row. Then the analyst must select fields from the exploded struct using dot notation, such as record_exploded.sensor_id, record_exploded.status, and record_exploded.health. Databricks PySpark documentation states that explode “returns a new row for each element in the given array or map,” and withColumn returns a new DataFrame by adding or replacing a column. The select method projects expressions or column names into the resulting DataFrame.

Option B is correct. The problem is many users running small queries simultaneously, which is a concurrency issue. Increasing the maximum number of clusters lets the SQL warehouse scale out to serve more concurrent queries. Increasing cluster size is more useful for complex or resource-heavy queries, not necessarily many small simultaneous queries. Auto Stop reduces cost but does not improve active-query latency. Official Databricks extract: “You can increase the maximum clusters if you want to handle more concurrent users,” and Databricks recommends monitoring queued queries and adjusting maximum clusters.

 Data engineers are primarily responsible for building, managing, and optimizing data pipelines and architectures. They use Databricks Data Science and Engineering service to perform tasks such as data ingestion, transformation, quality, and governance. Data engineers may use Databricks SQL as a secondary service to query, analyze, and visualize data from the lakehouse, but this is not their main focus. References: Databricks SQL overview, Databricks Data Science and Engineering overview, Data engineering with Databricks

A Structured Streaming pipeline that processes data in micro-batches and populates gold-level tables every minute requires a high level of compute resources to handle the frequent data ingestion, processing, and writing. This could result in a significant cost for the organization, especially if the data volume and velocity are large. Therefore, the data analyst should share this caution with the project stakeholders before setting up the dashboard and evaluate the trade-offs between the desired refresh rate and the available budget. The other options are not valid cautions because:

B. The gold-level tables are assumed to be appropriately clean for business reporting, as they are the final output of the data engineering pipeline. If the data quality is not satisfactory, the issue should be addressed at the source or silver level, not at the gold level.

C. The streaming data is an appropriate data source for a dashboard, as it can provide near real-time insights and analytics for the business users. Structured Streaming supports various sources and sinks for streaming data, including Delta Lake, which can enable both batch and streaming queries on the same data.

D. The streaming cluster is fault tolerant, as Structured Streaming provides end-to-end exactly-once fault-tolerance guarantees through checkpointing and write-ahead logs. If a query fails, it can be restarted from the last checkpoint and resume processing.

E. The dashboard can be refreshed within one minute or less of new data becoming available in the gold-level tables, as Structured Streaming can trigger micro-batches as fast as possible (every few seconds) and update the results incrementally. However, this may not be necessary or optimal for the business use case, as it could cause frequent changes in the dashboard and consume more resources. References: Streaming on Databricks, Monitoring Structured Streaming queries on Databricks, A look at the new Structured Streaming UI in Apache Spark 3.0, Run your first Structured Streaming workload

Databricks SQL allows you to create multiple visualizations from a single query result. These visualizations can be customized independently and each can be added to a dashboard. This feature is explicitly supported and recommended in Databricks’ documentation on dashboards and visualization workflows, enabling flexible reporting without duplicating queries.

 The SQL code provided in Option D is the correct way to create a view named sales_by_employee that will always stay up-to-date with the sales and employees tables. The code uses the CREATE OR REPLACE VIEW statement to define a new view that joins the sales and employees tables on the employee_id column. It selects the employee_name as sales_person and all sales for each employee, ensuring that the data entity/object is always up-to-date when new data are added to these tables.

The answer can be verified from Databricks SQL documentation which provides insights on creating views using SQL queries, joining tables, and selecting specific columns to be included in the view. Reference link: Databricks SQL

 Descriptive statistics is a branch of statistics that uses summary statistics, such as mean, median, mode, standard deviation, range, frequency, or correlation, to quantitatively describe and summarize data. Descriptive statistics can help data analysts understand the main features of a data set, such as its central tendency, variability, or distribution. Descriptive statistics can also help data analysts visualize data using charts, graphs, or tables. Descriptive statistics do not make any inferences or predictions about the data, unlike inferential statistics, which use data analysis techniques to infer properties of an underlying population or probability distribution from a sample of data. References: Databricks - Descriptive Statistics, Databricks - Data Analysis with Databricks SQL

Option A is correct. Classic enterprise data warehouses commonly use proprietary storage formats, while the Databricks lakehouse is built around open table formats such as Delta Lake and Iceberg. Databricks documentation states that Delta Lake is open source and that the Databricks platform uses no proprietary data formats to avoid vendor lock-in. Schema enforcement and ACID transactions are important Delta Lake capabilities, but they are not the best differentiator “over classic enterprise data warehouse solutions,” because enterprise warehouses also typically provide managed reliability and transactional behavior. The unique lakehouse advantage here is open-source/open-format interoperability. References: Databricks Delta Lake and lakehouse architecture documentation.

Option D is correct because the table has zero real null values, but the result contains null values representing subtotal and grand-total rows. That behavior is produced by WITH CUBE, which creates aggregations for combinations of grouping columns, including (group_1, group_2), (group_1), (group_2), and the grand total (). The Databricks SQL documentation states that GROUP BY supports advanced aggregations through CUBE, and that CUBE is shorthand for grouping sets. Option A only returns detailed groups. Options B and C use invalid syntax in Databricks SQL. Reference: Databricks GROUP BY clause documentation.

Option A is correct. Delta Live Tables, now documented under Lakeflow Declarative Pipelines, supports both Python and SQL source files in the same pipeline. The pipeline does not need to be rewritten entirely in one language, and it can still use streaming inputs. Official Databricks extract: “You can add Python and SQL source files to the same pipeline.” The same documentation also shows examples for batch and streaming reads in Python and SQL.

This statement is incorrect. In Databricks SQL, any user with sufficient permissions on the query or dashboard can configure a refresh schedule—workspace administrator privileges are not required.

Here is the breakdown of the correct information:

A. True – Queries can be scheduled to refresh at intervals ranging from 1 minute to 2 weeks.

B. True – You can configure refresh schedules in the Query Editor.

C. False statement – A query being refreshed does use a SQL Warehouse. However, the option in question says it does not use a warehouse, which would be incorrect in a different context. Since this is a trickier one, we know that scheduled queries do require a SQL Warehouse to run.

D. True – Refresh schedules are different from alerts; alerts are triggered based on specific conditions being met in query results.

E. False (and thus the correct answer to this question) – You do not need to be a workspace admin to set a refresh schedule. You only need the correct permissions on the object.

Option B is correct. A data analyst commonly uses Databricks SQL for analytics-focused transformations, aggregation, joining, filtering, and preparation of datasets for reporting or dashboarding. Analysis-specific ETL on Gold-layer tables is a “last-mile” analytics task: the Gold layer is already curated for business use, and the analyst performs additional project-specific refinement. The official Databricks exam guide includes creating views, performing aggregate operations, combining tables with joins, filtering, sorting, and using dashboards as Data Analyst Associate skills. It also states that Gold-layer data drives downstream analytics and dashboards. Options A, C, and D are primarily streaming engineering, machine learning, and MLOps activities, not common Databricks SQL analytics applications. References: Databricks Certified Data Analyst Associate Exam Guide and medallion architecture documentation.

The correct answer is A because MERGE INTO is the Databricks SQL command used to combine source rows with a target table based on a match condition. The condition checks whether supplier_id already exists. If a row does not match, WHEN NOT MATCHED THEN INSERT * inserts it. If a row already matches, no WHEN MATCHED action is provided, so the existing row remains unchanged. This prevents duplicate supplier rows.

Official documentation extract used: Databricks shows the pattern MERGE INTO target USING source ON target.key = source.key WHEN NOT MATCHED THEN INSERT *.

The gold layer of the medallion architecture contains data that is highly refined and aggregated, and powers analytics, machine learning, and production applications. Data analysts typically use the gold layer to access data that has been transformed into knowledge, rather than just information. The gold layer represents the final stage of data quality and optimization in the lakehouse. References: What is the medallion lakehouse architecture?

Option D is correct. Customer data is stored in the customer’s cloud account, such as object storage controlled by the customer. Databricks control-plane services manage platform components, while the customer’s data resides in the customer-controlled storage layer. Official Databricks extract: “Databricks doesn’t store your primary customer data. That lives in Amazon S3 or other systems you control.”

The correct answer is C because axis settings are configured in the visualization editor, specifically under the Y-axis settings. The Query Editor is for writing SQL, not configuring chart axes. User Settings do not control individual chart scales. Dashboard editing may contain visualizations, but the specific control is in the visualization editor’s Y Axis configuration.

Official documentation extract used: Databricks chart options state that to configure formatting for the X axis or Y axis, click the corresponding axis tab, and the Y-axis supports scale and range controls.