Updated Jun-2024 100% Cover Real Databricks-Certified-Data-Analyst-Associate Exam Questions Make Sure You 100% Pass [Q10-Q31]

QUESTION 10
Which of the following layers of the medallion architecture is most commonly used by data analysts?

None of these layers are used by data analysts

Gold

All of these layers are used equally by data analysts

Silver

Bronze

QUESTION 11
A data analyst is attempting to drop a table my_table. The analyst wants to delete all table metadata and data.
They run the following command:
DROP TABLE IF EXISTS my_table;
While the object no longer appears when they run SHOW TABLES, the data files still exist.
Which of the following describes why the data files still exist and the metadata files were deleted?

The table’s data was larger than 10 GB

The table did not have a location

The table was external

The table’s data was smaller than 10 GB

The table was managed

QUESTION 12
Which of the following is an advantage of using a Delta Lake-based data lakehouse over common data lake solutions?

ACID transactions

Flexible schemas

Data deletion

Scalable storage

Open-source formats

QUESTION 13
The stakeholders.customers table has 15 columns and 3,000 rows of data. The following command is run:

After running SELECT * FROM stakeholders.eur_customers, 15 rows are returned. After the command executes completely, the user logs out of Databricks.
After logging back in two days later, what is the status of the stakeholders.eur_customers view?

The view remains available and SELECT * FROM stakeholders.eur_customers will execute correctly.

The view has been dropped.

The view is not available in the metastore, but the underlying data can be accessed with SELECT * FROM delta. `stakeholders.eur_customers`.

The view remains available but attempting to SELECT from it results in an empty result set because data in views are automatically deleted after logging out.

The view has been converted into a table.

QUESTION 14
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?

Data page

Dashboards page

Queries page

Alerts page

SQL Editor page

QUESTION 15
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)

Option A

Option B

Option C

Option D

QUESTION 16
A data analyst runs the following command:
SELECT age, country
FROM my_table
WHERE age >= 75 AND country = ‘canada’;
Which of the following tables represents the output of the above command?

QUESTION 17
Which of the following statements about a refresh schedule is incorrect?

A query can be refreshed anywhere from 1 minute lo 2 weeks

Refresh schedules can be configured in the Query Editor.

A query being refreshed on a schedule does not use a SQL Warehouse (formerly known as SQL Endpoint).

A refresh schedule is not the same as an alert.

You must have workspace administrator privileges to configure a refresh schedule

QUESTION 18
A data analyst has set up a SQL query to run every four hours on a SQL endpoint, but the SQL endpoint is taking too long to start up with each run.
Which of the following changes can the data analyst make to reduce the start-up time for the endpoint while managing costs?

Reduce the SQL endpoint cluster size

Increase the SQL endpoint cluster size

Turn off the Auto stop feature

Increase the minimum scaling value

Use a Serverless SQL endpoint

QUESTION 19
A data analyst has a managed table table_name in database database_name. They would now like to remove the table from the database and all of the data files associated with the table. The rest of the tables in the database must continue to exist.
Which of the following commands can the analyst use to complete the task without producing an error?

DROP DATABASE database_name;

DELETE TABLE database_name.table_name;

DROP TABLE database_name.table_name;

DELETE TABLE table_name FROM database_name;

DROP TABLE table_name FROM database_name;

QUESTION 20
A data analyst has created a Query in Databricks SQL, and now they want to create two data visualizations from that Query and add both of those data visualizations to the same Databricks SQL Dashboard.
Which of the following steps will they need to take when creating and adding both data visualizations to the Databricks SQL Dashboard?

They will need to alter the Query to return two separate sets of results.

They will need to add two separate visualizations to the dashboard based on the same Query.

They will need to create two separate dashboards.

They will need to decide on a single data visualization to add to the dashboard.

They will need to copy the Query and create one data visualization per query.

QUESTION 21
Consider the following two statements:
Statement 1:

Statement 2:

Which of the following describes how the result sets will differ for each statement when they are run in Databricks SQL?

The first statement will return all data from the customers table and matching data from the orders table. The second statement will return all data from the orders table and matching data from the customers table. Any missing data will be filled in with NULL.

When the first statement is run, only rows from the customers table that have at least one match with the orders table on customer_id will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table on customer_id will be returned.

There is no difference between the result sets for both statements.

Both statements will fail because Databricks SQL does not support those join types.

When the first statement is run, all rows from the customers table will be returned and only the customer_id from the orders table will be returned. When the second statement is run, only those rows in the customers table that do not have at least one match with the orders table on customer_id will be returned.

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.

QUESTION 22
A data analyst creates a Databricks SQL Query where the result set has the following schema:
region STRING
number_of_customer INT
When the analyst clicks on the “Add visualization” button on the SQL Editor page, which of the following types of visualizations will be selected by default?

Violin Chart

Line Chart

IBar Chart

Histogram

There is no default. The user must choose a visualization type.

QUESTION 23
Delta Lake stores table data as a series of data files, but it also stores a lot of other information.
Which of the following is stored alongside data files when using Delta Lake?

None of these

Table metadata, data summary visualizations, and owner account information

Table metadata

Data summary visualizations

Owner account information

QUESTION 24
A business analyst has been asked to create a data entity/object called sales_by_employee. It should always stay up-to-date when new data are added to the sales table. The new entity should have the columns sales_person, which will be the name of the employee from the employees table, and sales, which will be all sales for that particular sales person. Both the sales table and the employees table have an employee_id column that is used to identify the sales person.
Which of the following code blocks will accomplish this task?

QUESTION 25
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;

Option A

Option B

Option C

Option D

Option E

QUESTION 26
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?

Delta Lake

Databricks Notebooks

Tableau

Databricks Machine Learning

Databricks SQL

QUESTION 27
In which of the following situations should a data analyst use higher-order functions?

When custom logic needs to be applied to simple, unnested data

When custom logic needs to be converted to Python-native code

When custom logic needs to be applied at scale to array data objects

When built-in functions are taking too long to perform tasks

When built-in functions need to run through the Catalyst Optimizer

QUESTION 28
A data analyst has a managed table table_name in database database_name. They would now like to remove the table from the database and all of the data files associated with the table. The rest of the tables in the database must continue to exist.
Which of the following commands can the analyst use to complete the task without producing an error?

DROP DATABASE database_name;

DROP TABLE database_name.table_name;

DELETE TABLE database_name.table_name;

DELETE TABLE table_name FROM database_name;

DROP TABLE table_name FROM database_name;