DATABRICKS-DATA-ANALYST-ASSOCIATE Practice Questions

Both statements C and E are factually incorrect based on the fundamental architecture and permission model of Databricks SQL.

Statement C is incorrect because scheduled queries require compute resources to execute. In Databricks SQL, this compute is provided by a SQL Warehouse (formerly SQL Endpoint). The documentation explicitly states that each scheduled execution runs on a selected SQL warehouse.

Statement E is incorrect because configuring a refresh schedule does not require workspace administrator privileges. The required permission is CAN RUN on the query. This allows data analysts and other non-administrator users to manage their own query schedules, which is a core part of the intended workflow.

A. A query can be refreshed anywhere from 1 minute to 2 weeks: This is a correct statement. The scheduling interface in the Query Editor provides options within this exact range.

B. Refresh schedules can be configured in the Query Editor: This is a correct statement. The "Schedule" button to configure the refresh frequency is located at the bottom of the SQL Query Editor UI.

D. A refresh schedule is not the same as an alert: This is a correct statement. A refresh schedule simply re-executes a query. An alert is a separate object that uses a scheduled query to evaluate a condition and trigger a notification.

1. Databricks Documentation

"Schedule a query": This document confirms that scheduled queries run on a SQL warehouse and details the required permissions.

Reference for Answer C: Under the "Schedule a query" section

it states

"Each scheduled execution runs with the query owner’s credential and runs on the SQL warehouse selected in the query."

Reference for Answer E: In the same document

under the "Manage query schedules and permissions" section

it specifies

"To schedule a query

you must have at least CAN RUN permission on the query."

Reference for Answer B: The document's initial instructions state

"To set a schedule for a query

open the query in the SQL editor. Click Schedule at the bottom of the editor."

2. Databricks Documentation

"What are Databricks SQL alerts?": This document clarifies the distinction between alerts and scheduled queries.

Reference for Answer D: It defines alerts as objects that "periodically run queries

evaluate defined conditions

and send notifications if a condition is met

" establishing them as a distinct feature from a simple refresh schedule.

3. Databricks Documentation

"Access control in Databricks SQL": This document provides details on the permission levels for various Databricks SQL objects.

Reference for Answer E: The section on "Query permissions" lists CAN RUN as the permission required to execute a query

which is a prerequisite for scheduling

confirming that workspace administrator privileges are not necessary.

A Sankey diagram is the most appropriate visualization for showing the flow of users through a website. This type of chart is specifically designed to illustrate the movement of quantities—in this case, users—from one stage to another (e.g., from the homepage to a product page, then to the checkout page). The width of the connecting bands in a Sankey diagram is proportional to the flow quantity, making it easy to identify the most common user paths and drop-off points.

A. Heatmap: Incorrect. A heatmap uses color intensity to show the magnitude of a phenomenon, typically for correlation analysis or to show density on a map, not sequential flow.

B. Choropleth: Incorrect. A choropleth map is used to visualize data across geographical regions by shading them, which is not applicable to website user flow.

C. Word Cloud: Incorrect. A word cloud visualizes the frequency of words in a text dataset and cannot represent a sequential process or flow.

D. Pivot Table: Incorrect. A pivot table is a data summarization tool that aggregates data into a tabular format; it is not a type of visualization for depicting flow.

1. Databricks Documentation

"Sankey diagram": "Sankey diagrams visualize the flow from one set of values to another. They are useful for showing how a specific resource is distributed among multiple destinations or processes." This directly supports its use for visualizing user flow.

Source: Databricks SQL Documentation > Visualizations > Sankey diagram.

2. Databricks Documentation

"Heatmap chart": "Heatmap visualizations use color to show the relationship between two variables... It is most useful for showing the correlation of many variables."

Source: Databricks SQL Documentation > Visualizations > Heatmap chart.

3. Databricks Documentation

"Choropleth map": "Choropleth visualizations are thematic maps that color geographical regions like countries

states

or counties based on aggregate values from your data."

Source: Databricks SQL Documentation > Visualizations > Choropleth map.

4. Databricks Documentation

"Pivot table": "A pivot table aggregates records from a query result into a new tabular display. It’s similar to PIVOT or GROUP BY statements in SQL."

Source: Databricks SQL Documentation > Visualizations > Pivot table.

Databricks SQL is the dedicated workspace within the Databricks Lakehouse Platform for data analysts to execute SQL queries, create visualizations, and build dashboards. A core task for any data analyst is to combine different data sources to create a unified view for analysis. This is fundamentally accomplished using SQL JOIN operations. Therefore, combining two data sources into a single, comprehensive dataset is a project perfectly suited for the Databricks SQL query editor, aligning with its primary purpose of enabling business intelligence and SQL-based analytics.

A. Testing the quality of data as it is imported from a source: This is a data engineering task, often automated in pipelines using tools like Delta Live Tables, rather than a primary project for an analyst in Databricks SQL.

B. Tracking usage of feature variables for machine learning projects: This is a machine learning operations (MLOps) task performed in the Data Science & Engineering workspace, typically using MLflow, not Databricks SQL.

D. Segmenting customers into like groups using a clustering algorithm: This is a data science and machine learning task that requires ML libraries and is executed in notebooks within the Data Science & Engineering workspace.

E. Automating complex notebook-based workflows with multiple tasks: This is the specific function of Databricks Workflows, which is used to orchestrate and schedule complex data pipelines, not the Databricks SQL environment itself.

1. Databricks Documentation

"What is Databricks SQL?": "Databricks SQL (DB SQL) is a serverless data warehouse on the Databricks Lakehouse Platform that lets you run all your SQL and BI applications at scale... It empowers a new generation of data analysts who work directly on the same data used for ETL

data science

and ML." This source establishes that Databricks SQL is for analysts performing SQL and BI tasks

such as querying and combining data (Option C).

2. Databricks Documentation

"Get started: Run and save a query": This guide demonstrates the primary function of the Databricks SQL UI

which is to write and execute SQL queries

such as SELECT statements with JOIN clauses

to explore and combine data. This directly supports the action described in Option C.

3. Databricks Documentation

"Introduction to Databricks machine learning": "Databricks Machine Learning is an integrated end-to-end machine learning platform incorporating managed services for experiment tracking

model training

feature development and management

and feature and model serving." This confirms that tasks like feature tracking (Option B) and clustering algorithms (Option D) belong to the ML persona and workspace.

4. Databricks Documentation

"Introduction to Databricks Workflows": "Databricks Workflows orchestrates data processing

machine learning

and analytics pipelines on the Databricks Lakehouse Platform. You can create and run a job that consists of a single task or a large

multi-task workflow with complex dependencies." This clearly defines the purpose of Workflows

making it the correct tool for the task in Option E.

The behavior described—where metadata is deleted but the underlying data files persist—is the defining characteristic of dropping an external table in Databricks. When a DROP TABLE command is executed on an external table, Databricks removes the table's definition from the metastore but intentionally leaves the data files in their specified external location untouched. This prevents accidental data loss, as the data might be used by other processes or tables. In contrast, dropping a managed table would delete both the metadata and the data files.

A. The table's data was larger than 10 GB: The size of the data is irrelevant to the behavior of the DROP TABLE command concerning metadata and data file deletion.

B. The table did not have a location: All tables have a location. The distinction is whether Databricks manages it (managed table) or if it's a user-specified path (external table).

D. The table's data was smaller than 10 GB: Similar to option A, the data size does not affect how DROP TABLE operates on managed versus external tables.

E. The table was managed: If the table were managed, the DROP TABLE command would have deleted both the metadata and the underlying data files.

1. Databricks SQL Language Reference

DROP TABLE: "If the table is an EXTERNAL table

only the metadata for the table is removed from the metastore. The data files that comprise the table are not deleted from the file system."

Source: Databricks Documentation > SQL reference > Data definition language > DROP TABLE.

2. Databricks Documentation

"What are tables on Databricks?": "When you drop an external table

you do not delete the underlying data. The table definition is removed from the metastore

but the directory containing the data remains."

Source: Databricks Documentation > Data management > What are tables on Databricks? > External tables.

3. Databricks Documentation

"What are tables on Databricks?": "When you drop a managed table

you also delete the underlying data." This reference clarifies the alternative behavior for managed tables

confirming why option E is incorrect.

Source: Databricks Documentation > Data management > What are tables on Databricks? > Managed tables.

The mean is the arithmetic average of all data points, making it highly sensitive to the magnitude of each value. Extreme outliers, which are values far from the central tendency, will disproportionately pull the mean towards them. In contrast, the median is the middle value of a sorted dataset and is not affected by the specific values of outliers, only their position. This property makes the median a "robust" measure of central tendency. Consequently, in a dataset with significant outliers (a skewed distribution), the mean and median will diverge, with the mean being pulled in the direction of the outliers.

A: In the absence of outliers, a distribution is often symmetric, causing the mean and median to be nearly identical.

B: The absence of missing values ensures data completeness for calculation but does not inherently cause a difference between the mean and median.

C: For boolean data (0s and 1s), the mean and median can be calculated, but this data type does not typically exhibit the kind of extreme outliers that cause a meaningful divergence.

D: The mean is generally not a meaningful measure for nominal categorical data, and the median is only applicable to ordinal data.

1. Databricks Documentation

Exploratory data analysis in Databricks: Tools and techniques. This guide discusses using summary statistics to understand data. It states

"Summary statistics provide a high-level overview of your data... Key statistics include... mean

median... These statistics can help you understand the distribution of your data

identify outliers

and detect skewness." The difference between mean and median is a primary indicator of skewness caused by outliers.

2. Databricks Blog

A Gentle Introduction to Data Distributions (May 26

2021). In the section on Skewed Distributions

the article explains: "In the presence of outliers... the mean can be pulled by these outliers... The median

on the other hand

is known to be robust to outliers. That is

the value of the median is not affected by the presence of outliers." This directly supports that outliers cause a divergence.

3. PennState

STAT 200: Elementary Statistics

Lesson 2: Measures of Center. The course material explicitly states: "The mean is not a resistant measure of center because it is influenced by outliers. The median and mode are resistant measures of center." This academic source confirms the fundamental statistical principle behind the correct answer.

Data Explorer is the primary user interface within the Databricks workspace for managing data assets. It allows users to browse through catalogs, schemas, tables, and views to understand the available data (metadata). It includes a "Sample Data" tab for previewing the actual data within tables. Crucially, it also provides a "Permissions" tab where authorized users can view and modify access control lists (ACLs) for these data objects, making it a central hub for both data discovery and governance.

A: UPDATE queries are executed in the SQL editor, not directly within the Data Explorer interface, which is for browsing and managing objects.

C: Dashboards are a distinct feature in Databricks SQL, created by combining visualizations from one or more queries, not within Data Explorer.

D: Visualizations are created from query results within the SQL editor, not in Data Explorer.

E: Connections for third-party BI tools are configured using connection details from a SQL warehouse, not through the Data Explorer UI.

1. Databricks Documentation

"What is Data Explorer?": "Data Explorer allows you to explore and manage data assets like catalogs

schemas

tables

and views. You can use Data Explorer to browse schemas

tables

views

and functions

view sample data

and view table metadata and properties... You can also use Data Explorer to view and set permissions on data assets." This directly supports the functions of viewing metadata

data

and managing permissions.

2. Databricks Documentation

"Manage data in Data Explorer": This page details the user interface

describing the tabs available when you select a table: "The Details tab shows the table schema and properties... The Sample Data tab shows a preview of the data... The Permissions tab lists all permissions on the table and provides an interface for granting and revoking permissions." This confirms the specific capabilities mentioned in the correct answer.

The INSERT INTO command is used to add new rows of data to an existing table. The syntax INSERT INTO targettable TABLE sourcetable is a valid Databricks SQL command that appends all rows from the sourcetable (stakeholders.newsuppliers) to the targettable (stakeholders.suppliers). This operation does not remove or alter the existing data in the target table, nor does it automatically check for or remove duplicate rows. Therefore, the suppliers table will contain its original data plus all the data from the newsuppliers table.

A: The INSERT INTO statement does not automatically delete duplicate data. A MERGE statement or a separate deduplication step would be required for that functionality.

B: The syntax INSERT INTO ... TABLE ... is a valid and supported command in Databricks SQL for inserting all rows from one table into another.

D: This describes a swap of data between two tables, which is not the function of an INSERT INTO command.

E: This describes the behavior of an INSERT OVERWRITE command, which replaces all existing data in the target table, rather than appending to it.

1. Databricks SQL Language Reference

INSERT INTO: The official documentation states

"Inserts new rows into a table." It describes the action as adding data

not replacing it or performing deduplication. The contrast with INSERT OVERWRITE ("replaces the data of the table") and MERGE INTO (which has explicit clauses for handling matched rows) confirms this behavior.

Source: Databricks Documentation > SQL reference > Data manipulation language > INSERT INTO.

2. Databricks SQL Language Reference

INSERT OVERWRITE: This documentation explicitly states that INSERT OVERWRITE "replaces the data of the table

" which directly contradicts option E and highlights the appending nature of INSERT INTO.

Source: Databricks Documentation > SQL reference > Data manipulation language > INSERT OVERWRITE DIRECTORY and INSERT OVERWRITE TABLE.

The DROP TABLE command is the correct Data Definition Language (DDL) statement in Databricks SQL to remove a table's definition and its data. For a managed table, which is the default type, this command also deletes the underlying data files from their storage location. The syntax DROP TABLE databasename.tablename; uses a two-part identifier to precisely target the specific table within the specified database for removal, leaving all other tables in the database untouched. This directly and correctly accomplishes the task described.

A. DROP DATABASE databasename;: This command would delete the entire database, including all of its tables, which violates the requirement to preserve other tables.

C. DELETE TABLE databasename.tablename;: DELETE is a Data Manipulation Language (DML) command used to remove rows from a table, not the table object itself. The syntax DELETE TABLE is invalid.

D. DELETE TABLE tablename FROM databasename;: This is not valid SQL syntax for deleting a table. The DELETE command operates on rows within a table.

E. DROP TABLE tablename FROM databasename;: This is not valid SQL syntax. The DROP TABLE command does not use a FROM clause to specify the database.

1. Databricks SQL Documentation

DROP TABLE: The official documentation specifies the syntax for dropping a table as DROP TABLE [ IF EXISTS ] tablename. It clarifies that tablename can be a qualified name (e.g.

databasename.tablename). It also states

"When you drop a managed table

you also delete the underlying data."

Source: Databricks Documentation > SQL reference > Data definition language > DROP TABLE.

2. Databricks Documentation

What are tables on Databricks?: This document distinguishes between managed and external tables. It explicitly states for managed tables: "When you drop a managed table

the underlying data for this table is also deleted from your cloud tenant." This confirms that DROP TABLE on a managed table fulfills the requirement to remove the data files.

Source: Databricks Documentation > Data management > What are tables on Databricks? > Managed tables section.

3. Databricks Documentation

DROP DATABASE: This documentation shows that the DROP DATABASE command removes the database and all associated objects

including tables. This confirms why option A is incorrect as it does not isolate the deletion to a single table.

Source: Databricks Documentation > SQL reference > Data definition language > DROP DATABASE.

The most direct and efficient method for a data analyst to quickly upload a small CSV file and create a table in Databricks SQL is by using the built-in "Add data" UI. This feature is specifically designed for this use case, allowing users to upload files from their local machine directly through the web interface. The UI guides the user through the process, automatically inferring the schema and creating a managed Delta table, which is immediately available for querying in Databricks SQL. This single, streamlined workflow is significantly faster than multi-step alternatives.

B: Partner Connect is used to establish integrations with third-party data tools and services (like Fivetran or Power BI), not for direct file uploads from a user's desktop.

C: While functional, this method is less efficient. It requires the analyst to first access and upload the file to an external cloud storage location before creating the table in Databricks.

D: Manually copying and pasting data is highly impractical, error-prone, and not a standard or recommended procedure for data ingestion, even for a small number of rows.

1. Databricks Documentation

"Load data using the add data UI": This document explicitly describes the feature mentioned in option A. It states

"You can use the add data UI to load data from your local machine... Databricks stores these files as managed tables." This confirms it is the intended method for this scenario.

2. Databricks Documentation

"Ingest data into a Databricks lakehouse": This guide provides an overview of data ingestion methods. It positions the "Add data UI" as the simplest way to get started by loading local files

contrasting it with more advanced

scalable methods like Auto Loader which would be used for files already in cloud storage (as described in option C).

3. Databricks Documentation

"What is Databricks Partner Connect?": This source clarifies the purpose of Partner Connect

stating it "lets you create connections to our software and data partners from the Databricks UI." This confirms it is not a tool for direct file ingestion from a local machine.

This query correctly produces the desired output by performing three essential SQL operations. First, it uses an INNER JOIN to combine rows from the customers and orders tables based on their shared customerid. Second, it applies the COUNT(orderid) aggregate function to count the number of orders for each customer. Third, the GROUP BY customername clause is used to group the rows so that the COUNT function calculates the total orders for each unique customer name. Finally, AS numberoforders renames the aggregated column to match the required output.

B: This statement is syntactically incorrect. It uses a non-existent USE clause and is missing the required GROUP BY clause for aggregation.

C: This query is missing the COUNT() aggregate function to count the orders and the necessary GROUP BY clause. It also contains a typo (customerjiame).

D: This query fails to alias the count column as numberoforders, which is required by the problem description. It also contains a typo (customerjiame).

1. GROUP BY Clause: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is essential when using aggregate functions like COUNT().

Databricks SQL Language Reference

GROUP BY clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-groupby.html)

2. JOIN Clause: The JOIN clause combines records from two or more tables. The ON subclause specifies the join condition

matching customerid in both tables.

Databricks SQL Language Reference

JOIN clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-join.html)

3. count Aggregate Function: The count function returns the number of rows or non-NULL values within a group. COUNT(orderid) correctly counts the total orders.

Databricks SQL Language Reference

count aggregate function. (https://docs.databricks.com/en/sql/language-manual/functions/count.html)

4. SELECT Clause and Aliases: The SELECT clause specifies the columns to be returned. An alias (using AS) can be used to provide a temporary

more descriptive name for a column in the result set.

Databricks SQL Language Reference

SELECT clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select.html)