Databricks Certified Data Analyst Associate Exam Questions Instant Access

Question 1

What is used as a compute resource for Databricks SQL?

ASingle-node clusters

BDownstream BI tools integrated with Databricks SQL

CSQL warehouses

DStandard clusters

Answer : C

Question 2

A data analyst has recently joined a new team that uses Databricks SQL, but the analyst has never used Databricks before. The analyst wants to know where in Databricks SQL they can write and execute SQL queries.

On which of the following pages can the analyst write and execute SQL queries?

AData page

BDashboards page

CQueries page

DAlerts page

ESQL Editor page
The SQL Editor page is where the analyst can write and execute SQL queries in Databricks SQL. The SQL Editor page has a query pane where the analyst can type or paste SQL statements, and a results pane where the analyst can view the query results in a table or a chart. The analyst can also browse data objects, edit multiple queries, execute a single query or multiple queries, terminate a query, save a query, download a query result, and more from the SQL Editor page.Reference:Create a query in SQL editor

Answer : E

Question 3

Which of the following approaches can be used to connect Databricks to Fivetran for data ingestion?

AUse Workflows to establish a SQL warehouse (formerly known as a SQL endpoint) for Fivetran to interact with

BUse Delta Live Tables to establish a cluster for Fivetran to interact with

CUse Partner Connect's automated workflow to establish a cluster for Fivetran to interact with

DUse Partner Connect's automated workflow to establish a SQL warehouse (formerly known as a SQL endpoint) for Fivetran to interact with

EUse Workflows to establish a cluster for Fivetran to interact with
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.Reference:Connect to Fivetran using Partner Connect,Use Databricks with Fivetran

Answer : C

Question 4

A data analysis team is working with the table_bronze SQL table as a source for one of its most complex projects. A stakeholder of the project notices that some of the downstream data is duplicative. The analysis team identifies table_bronze as the source of the duplication.

Which of the following queries can be used to deduplicate the data from table_bronze and write it to a new table table_silver?

CREATE TABLE table_silver AS

SELECT DISTINCT *

FROM table_bronze;

CREATE TABLE table_silver AS

INSERT *

FROM table_bronze;

CREATE TABLE table_silver AS

MERGE DEDUPLICATE *

FROM table_bronze;

INSERT INTO TABLE table_silver

SELECT * FROM table_bronze;

INSERT OVERWRITE TABLE table_silver

SELECT * FROM table_bronze;

AOption A

BOption B

COption C

DOption D

EOption E
Option A uses theSELECT DISTINCTstatement to remove duplicate rows from thetable_bronzeand create a new tabletable_silverwith the deduplicated data.This is the correct way to deduplicate data using Spark SQL12. Option B simply inserts all the rows fromtable_bronzeintotable_silver, without removing any duplicates. Option C is not a valid syntax for Spark SQL, as there is noMERGE DEDUPLICATEstatement. Option D appends all the rows fromtable_bronzeintotable_silver, without removing any duplicates. Option E overwrites the existing data intable_silverwith the data fromtable_bronze, without removing any duplicates.Reference:Delete Duplicate using SPARK SQL,Spark SQL - How to Remove Duplicate Rows

Answer : A

Question 5

What describes the variance of a set of values?

AVariance is a measure of how far a single observed value is from a set ot va IN

BVariance is a measure of how far an observed value is from the variable's maximum or minimum value.

CVariance is a measure of central tendency of a set of values.

DVariance is a measure of how far a set of values is spread out from the sets central value.
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

Answer : D

Question 6

A data analyst needs to use the Databricks Lakehouse Platform to quickly create SQL queries and data visualizations. It is a requirement that the compute resources in the platform can be made serverless, and it is expected that data visualizations can be placed within a dashboard.

Which of the following Databricks Lakehouse Platform services/capabilities meets all of these requirements?

ADelta Lake

BDatabricks Notebooks

CTableau

DDatabricks Machine Learning

EDatabricks SQL
Databricks SQL is a serverless data warehouse on the Lakehouse that lets you run all of your SQL and BI applications at scale with your tools of choice, all at a fraction of the cost of traditional cloud data warehouses1.Databricks SQL allows you to create SQL queries and data visualizations using the SQL Analytics UI or the Databricks SQL CLI2.You can also place your data visualizations within a dashboard and share it with other users in your organization3.Databricks SQL is powered by Delta Lake, which provides reliability, performance, and governance for your data lake4.Reference:
Databricks SQL
Query data using SQL Analytics
Visualizations in Databricks notebooks
Delta Lake

Answer : E

Question 7

A data analyst wants the following output:

customer_name number_of_orders

John Doe 388

Zhang San 234

Which statement will produce this output?

ASELECT customer_name, count(order_id) AS number_of_orders
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id
GROUP BY customer_name;

BSELECT customer_name, count(order_id) number_of_orders
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id USE customer_name;

CSELECT customerjiame, (order_id) number_of_orders
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id;

DSELECT customerjiame, count(order_id)
FROM customers
JOIN orders
ON customers.customer_id = orders.customer_id GROUP BY customerjiame;

Answer : A