Databricks Certified Data Analyst Associate Exam Questions Instant Access

Question 1

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 2

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 3

A stakeholder has provided a data analyst with a lookup dataset in the form of a 50-row CSV file. The data analyst needs to upload this dataset for use as a table in Databricks SQL.

Which approach should the data analyst use to quickly upload the file into a table for use in Databricks SOL?

ACreate a table by uploading the file using the Create page within Databricks SQL

BCreate a table via a connection between Databricks and the desktop facilitated by Partner Connect.

CCreate a table by uploading the file to cloud storage and then importing the data to Databricks.

DCreate a table by manually copying and pasting the data values into cloud storage and then importing the data to Databricks.
Databricks provides a user-friendly interface that allows data analysts to quickly upload small datasets, such as a 50-row CSV file, and create tables within Databricks SQL. The steps are as follows:
Access the Data Upload Interface:
In the Databricks workspace, navigate to the sidebar and click on New > Add or upload data.
Select Create or modify a table.
Upload the CSV File:
Click on the browse button or drag and drop the CSV file directly onto the designated area.
The interface supports uploading up to 10 files simultaneously, with a total size limit of 2 GB.
Configure Table Settings:
After uploading, a preview of the data is displayed.
Specify the table name, select the appropriate schema, and configure any additional settings as needed.
Create the Table:
Once all configurations are set, click on the Create Table button to finalize the process.
This method is efficient for quickly importing small datasets without the need for additional tools or complex configurations. Options B, C, and D involve more complex or manual processes that are unnecessary for this task.

Answer : A

Question 4

In which circumstance will there be a substantial difference between the variable's mean and median values?

AWhen the variable is of the categorical type

BWhen the variable is of the boolean type

CWhen the variable contains no outliers

DWhen the variable contains a lot of extreme outliers

Answer : D

The mean is sensitive to extreme values, often called outliers, which can significantly skew the average away from the true center of the data. The median, however, is a measure of central tendency that is resistant to such outliers because it only considers the middle value(s) when the data is ordered. Therefore, when a variable contains many extreme outliers, there will be a substantial difference between the mean and the median. According to Databricks data analysis materials, this is a fundamental concept when choosing summary statistics for reporting.

Question 5

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 6

Query History provides Databricks SQL users with a lot of benefits. A data analyst has been asked to share all of these benefits with their team as part of a training exercise. One of the benefit statements the analyst provided to their team is incorrect.

Which statement about Query History is incorrect?

AIt can be used to view the query plan of queries that have run.

BIt can be used to debug queries.

CIt can be used to automate query execution on multiple warehouses (formerly endpoints).

DIt can be used to troubleshoot slow running queries.

Answer : C

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.

Question 7

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