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

Option D is correct because the desired result needs one row per customer, so the query must aggregate orders by customer_name. It also needs the output column name number_of_orders, so the aggregate expression must be aliased. Databricks SQL documentation states that count “returns the number” of rows in a group, and the SELECT clause supports a column alias for an expression result. Therefore, count(order_id) AS number_of_orders with GROUP BY customer_name is the correct statement. Option B counts correctly but does not alias the output column as required. Option A does not aggregate. Option C uses invalid SQL syntax because USE customer_name is not a grouping clause. References: Databricks count aggregate function and SELECT clause documentation.

Databricks Partner Connect is designed to simplify and streamline the integration between Databricks and its technology partners. It provides a unified interface within the Databricks platform that facilitates the discovery and connection to a variety of data, analytics, and AI tools. By automating the configuration of necessary resources such as clusters, tokens, and connection files, Partner Connect enables seamless, bi-directional data flow between Databricks and partner solutions. This integration enhances the overall functionality of the Databricks Lakehouse by allowing users to easily incorporate external tools and services into their workflows, thereby expanding the platform ' s capabilities and fostering a more cohesive data ecosystem.​

In Databricks SQL, when creating a dashboard that includes multiple data visualizations and counters, it is imperative that each visualization and counter is based on a query. The process involves the following steps:​

Develop Queries:

For each desired visualization or counter, write a SQL query that retrieves the necessary data.​

Create Visualizations and Counters:

After executing each query, utilize the results to create corresponding visualizations or counters. Databricks SQL offers a variety of visualization types to represent data effectively.​

Assemble the Dashboard:

Add the created visualizations and counters to your dashboard, arranging them as needed to convey the desired insights.​

By ensuring that all components of the dashboard are derived from queries, you maintain consistency, accuracy, and the ability to refresh data as needed. This approach also facilitates easier maintenance and updates to the dashboard elements.​

Option A is correct. The user is not a member of the auditing group, so is_member( " auditing " ) evaluates to false and the ELSE age > = 18 branch controls the filter. Rows with age > = 18 are returned; rows under 18 are omitted. “Age greater than 17” is equivalent to age > = 18 for integer ages. Official Databricks extract: is_member() “returns TRUE if the current user is a member” of the specified group, and Databricks describes dynamic views as views that can filter rows based on group membership.

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

 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. The model being tuned is a Spark ML model, which is already distributed. SparkTrials is intended to distribute independent single-machine trials across Spark workers. For distributed ML algorithms such as Spark MLlib/Spark ML, Hyperopt should run trials from the driver so each trial can access the full cluster resources. Therefore, SparkTrials() should be changed to Trials(). Official Databricks documentation explains that this setup works for distributed machine learning algorithms including Apache Spark MLlib, and the Databricks notebook guidance states that SparkTrials is incompatible for that distributed-training pattern because each trial must be evaluated on the driver node.

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

A data analyst can create multiple visualizations from the same query in Databricks SQL by clicking the + button next to the Results tab and selecting Visualization. Each visualization can have a different type, name, and configuration. To add a visualization to a dashboard, the data analyst can click the vertical ellipsis button beneath the visualization, select + Add to Dashboard, and choose an existing or new dashboard. The data analyst can repeat this process for each visualization they want to add to the same dashboard. References: Visualization in Databricks SQL, Visualize queries and create a dashboard in Databricks SQL

 The Visualization Editor in Databricks SQL allows users to create and customize various types of charts and visualizations from the query results. Users can change the visualization type, select the data fields, adjust the colors, format the data labels, and modify the tooltips. However, there is no option to add borders to the visualizations in the Visualization Editor. Borders are not a supported feature of the new chart visualizations in Databricks1. Therefore, the statement that borders can be added is incorrect. References:

New chart visualizations in Databricks | Databricks on AWS

In Databricks, to determine the owner of a managed table, you can utilize the Catalog Explorer feature. The steps are as follows:​

Access Catalog Explorer:

In your Databricks workspace, click on the Catalog icon in the sidebar to open Catalog Explorer.​

Navigate to the Table:

Within Catalog Explorer, browse through the catalog and schema to locate the specific managed table whose ownership you wish to verify.​

View Table Details:

Click on the table name to open its details page.​

Identify the Owner:

On the table ' s details page, review the Owner field, which displays the principal (user, service principal, or group) that owns the table.​

This method provides a straightforward way to ascertain the ownership of managed tables within the Databricks environment. Understanding table ownership is essential for managing permissions and ensuring proper access control.​

A user-defined function (UDF) is a function defined by a user, allowing custom logic to be reused in the user environment1. To apply a UDF to a table, the syntax is SELECT udf_name(column_name) AS alias FROM table_name2. Therefore, option E is the correct way to use the UDF price to create a new column customer_price based on the existing columns customer_spend and customer_units from the table customer_summary. References:

What are user-defined functions (UDFs)?

User-defined scalar functions - SQL

V

Option D is correct. Databricks SQL alerts run a query on a schedule, evaluate a condition, and notify destinations when the condition is met. Since the requirement is notification through a messaging webhook, the alert should use a webhook alert destination. Official Databricks extract: alerts “run queries on a schedule and notify you when a condition that you define is met.” Databricks notification documentation also states that admins can configure alternate notification destinations using webhooks.

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.

Databricks SQL uses SQL warehouses as its compute resource. A SQL warehouse is a dedicated compute engine designed specifically for executing SQL queries and powering dashboards within the Databricks workspace. According to Databricks official documentation, SQL warehouses are optimized for fast, scalable query execution, whereas clusters are used primarily for data engineering and machine learning workloads.

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

The correct answer is C because Databricks SQL supports bracket notation for arrays, and array indexing starts at 0. Therefore, the first element of the products array is accessed with products[0]. Options A and B use dot notation, which is not the correct syntax for array indexing. Option D returns the second element, not the first.

Official documentation extract used: Databricks states that the bracket operator returns an array element by index and that “the first element of an ARRAY is at index 0.”

Option C is the correct answer because it creates a new aggregated table, store_sales, from the existing cleaned/refined table cleaned_transactions. In a medallion architecture, a table such as cleaned_transactions represents a Silver-layer table because it contains cleaned or validated transaction-level data. The query then creates store_sales by grouping by store_id and calculating SUM(sales), which is an aggregation suitable for analytics and reporting. That is exactly the Silver-to-Gold hop.

Option A creates cleaned_transactions from transactions, so it is moving toward a cleaned Silver table, not creating a Gold table. Option B creates transactions from raw_transactions, which is closer to a raw-to-Bronze or Bronze preparation step. Option D filters rows from transactions into cleaned_transactions, which is also a cleaning/validation step associated with Silver, not Gold.

Exact extract from official Databricks documentation: Databricks describes medallion architecture as improving data through “Bronze ⇒ Silver ⇒ Gold layer tables.” The same official page identifies Silver as “Data cleaning and validation” and Gold as “Dimensional modeling and aggregation.” Databricks also states that the Gold layer “consists of aggregated data tailored for analytics and reporting.”

 An external table is a table that is defined in the metastore, but its data is stored outside of the Databricks environment, such as in S3, ADLS, or GCS. When an external table is dropped, only the metadata is deleted from the metastore, but the data files are not affected. This is different from a managed table, which is a table whose data is stored in the Databricks environment, and whose data files are deleted when the table is dropped. To delete the data files of an external table, the analyst needs to specify the PURGE option in the DROP TABLE command, or manually delete the files from the storage system. References: DROP TABLE, Drop Delta table features, Best practices for dropping a managed Delta Lake table

Variance is a statistical measure that quantifies the dispersion or spread of a set of values around their mean (central value). It is calculated by taking the average of the squared differences between each value and the mean of the dataset. A higher variance indicates that the data points are more spread out from the mean, while a lower variance suggests that they are closer to the mean. This measure is fundamental in statistics to understand the degree of variability within a dataset.​WikipediaWikipedia+1Investopedia+1

Data Explorer is a user interface that allows you to discover and manage data, schemas, tables, models, and permissions in Databricks SQL. You can use Data Explorer to view schema details, preview sample data, and see table and model details and properties. Administrators can view and change owners, and admins and data object owners can grant and revoke permissions1. References: Discover and manage data using Data Explorer

 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

Option B is correct. The program can be scheduled to run daily as a Databricks job, and Python control flow can determine whether the final query runs only when the current day is Sunday. Databricks jobs support scheduled execution, and PySpark notebooks can run SQL through Spark while using Python logic around those commands. Official Databricks extract: jobs can be configured “to run it on a time-based schedule,” and spark.sql/Spark DataFrame operations allow Python-based workflows to interact with SQL data.

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.

Markdown-based text boxes are useful as labels on a dashboard. They allow the data analyst to add text to a dashboard using the %md magic command in a notebook cell and then select the dashboard icon in the cell actions menu. The text can be formatted using markdown syntax and can include headings, lists, links, images, and more. The text boxes can be resized and moved around on the dashboard using the float layout option. References: Dashboards in notebooks, How to add text to a dashboard in Databricks

Option C is correct because Auto Stop is specifically designed to stop a SQL warehouse when it has been idle for a configured number of minutes. This minimizes total warehouse running time after the scheduled dashboard refresh completes. Reducing cluster size may reduce hourly cost but does not stop the warehouse. Serverless can help with management, but the feature directly matching “only running when necessary” is Auto Stop. Official Databricks extract: “Auto Stop determines whether the warehouse stops if it’s idle for the specified number of minutes.” Databricks also notes that idle SQL warehouses continue to accumulate charges until stopped.

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.

The Owner field in the table page shows the current owner of the table and allows the owner to change it to another user or group. To change the ownership of the table, the owner can click on the Owner field and select the new owner from the drop-down list. This will transfer the ownership of the table to the selected user or group and remove the previous owner from the list of table access control entries1. The other options are incorrect because:

A. Removing the owner’s account from the Owner field will not change the ownership of the table, but will make the table ownerless2.

B. Selecting All Users from the Owner field will not change the ownership of the table, but will grant all users access to the table3.

D. Selecting the Admins group from the Owner field will not change the ownership of the table, but will grant the Admins group access to the table3.

E. Removing all access from the Owner field will not change the ownership of the table, but will revoke all access to the table4. References:

1: Change table ownership

2: Ownerless tables

3: Table access control

4: Revoke access to a table

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 after correcting the uploaded typo withcolumn to withColumn. A 10% tax is calculated by multiplying salary by 0.1, not by 10. The age column should be removed using .drop( " age " ). Options A and D multiply salary by 10, which calculates 1000%, not 10%. Options A and C also use dropField, which is not the correct DataFrame method for removing a top-level column. Official Databricks PySpark documentation states that withColumn adds or replaces a column and that drop(*cols) returns a new DataFrame without the specified columns.