Practice Free Databricks Certified Data Analyst Associate Exam Online Questions
A data analyst is working with a nested array column products in table transactions. The analyst wants to return the first item in the array for each row.
The data analyst is using the following incomplete command:
SELECT
transaction_id , _____ AS first_product
FROM transactions;
Which line of code should the data analyst use to fill in the blank so that it successfully completes the task?
- A . products.1
- B . products.0
- C . products [0]
- D . products [1]
A data analyst has been asked to use the below table sales_table to get the percentage rank of products within region by the sales:
The result of the query should look like this:
Which of the following queries will accomplish this task?
A)
B)
C)
D)
- A . Option A
- B . Option B
- C . Option C
- D . Option D
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?
A)
CREATE TABLE table_silver AS SELECT DISTINCT *
FROM table_bronze;
B)
CREATE TABLE table_silver AS
INSERT *
FROM table_bronze;
C)
CREATE TABLE table_silver AS
MERGE DEDUPLICATE *
FROM table_bronze;
D)
INSERT INTO TABLE table_silver
SELECT * FROM table_bronze;
E)
INSERT OVERWRITE TABLE table_silver
SELECT * FROM table_bronze;
- A . Option A
- B . Option B
- C . Option C
- D . Option D
- E . Option E
Which of the following data workloads will utilize a Gold table as its source?
- A . A job that enriches data by parsing its timestamps into a human-readable format.
- B . A job that aggregates uncleaned data to create standard summary statistics.
- C . A job that cleans data by removing malformatted records.
- D . A job that queries aggregated data designed to feed into a dashboard.
- E . A job that ingests raw data from a streaming source into the Lakehouse.
Data engineers and data analysts are working together on a data pipeline. The data engineer is working on the raw, bronze, and silver layers of the pipeline using Python, and the data analyst is working on the gold layer of the pipeline using SQL. The raw source of the pipeline is a streaming input. They now want to migrate their pipeline to use Delta Live Tables.
Which of the following changes will need to be made to the pipeline when migrating to Delta Live Tables?
- A . The pipeline can have different notebook sources in SQL and Python.
- B . The pipeline will need to be written entirely in SQL.
- C . The pipeline will need to use a batch source in place of a streaming source.
- D . The pipeline will need to be written entirely in Python.
A data analyst is working with the following table my_table:
customer_name dollars_spent
Hex Sprockets
[125.34 , 100.15, 9003.99]
Dented Fenders
[16.99 , 200.85, 33.49, 88.17]
The analyst wants to divide each value in the dollars_spent array by 100 to get the spend in terms of hundreds of dollars using the following code block:
SELECT
customer_name , _______
FROM my_table;
Which line of code can be used to fill in the blank so that the above code block successfully completes the task?
- A . TRANSFORM(hundreds_spent, dollars_spent / 100)
- B . TRANSFORM(dollars_spent, value / 100) AS hundreds_spent
- C . TRANSFORM(dollars_spent, value -> value / 100) AS hundreds_spent
- D . TRANSFORM(dollars_spent, dollars_spent / 100) AS hundreds_spent
A data analyst is troubleshooting a query in Databricks SQL that fails when processing large datasets and complex join operations. Logs indicate that the job consistently aborts due to resource constraint errors on the cluster.
Which Query Profile metric should the analyst use to identify the operator that is causing resource overuse?
- A . Time spent per operator
- B . Shuffle read size per operator
- C . Memory peak per operator
- D . Bytes spilled to disk per operator
In which of the following situations will the mean value and median value of variable be meaningfully different?
- A . When the variable contains no outliers
- B . When the variable contains no missing values
- C . When the variable is of the boolean type
- D . When the variable is of the categorical type
- E . When the variable contains a lot of extreme outliers
In which circumstance will there be a substantial difference between the variable’s mean and median values?
- A . When the variable is of the categorical type
- B . When the variable is of the boolean type
- C . When the variable contains no outliers
- D . When the variable contains a lot of extreme outliers
A data scientist wants to tune a set of hyperparameters for a machine learning model. They have wrapped a Spark ML model in the objective function objective_function, and they have defined the search space search_space.
As a result, they have the following code block:
num_evals = 100
trials = SparkTrials()
best_hyperparam = fmin(
fn=objective_function , space=search_space , algo=tpe.suggest , max_evals=num_evals , trials=trials
)
Which of the following changes do they need to make to the above code block in order to accomplish the task?
- A . Change SparkTrials() to Trials()
- B . Reduce num_evals to be less than 10
- C . Change fmin() to fmax()
- D . Remove the trials=trials argument
- E . Remove the algo=tpe.suggest argument