Databricks Databricks Certifications DATABRICKS-CERTIFIED-DATA-ANALYST-ASSOCIATE Questions & Answers

• Question 21:

  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;

  Correct Answer: A

• Question 22:

  They are using the following incomplete command:

  

  Which of the following lines of code can they use to fill in the blank in the above code block so that it successfully completes the task?

  A. array distinct(products)

  B. explode(products)

  C. reduce(products)

  D. array(products)

  E. flatten(products)

  Correct Answer: B

• Question 23:

  A data analyst runs the following command:

  INSERT INTO stakeholders.suppliers TABLE stakeholders.new_suppliers;

  What is the result of running this command?

  A. The suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, and any duplicate data is deleted.

  B. The command fails because it is written incorrectly.

  C. The suppliers table now contains both the data it had before the command was run and the data from the new_suppliers table, including any duplicate data.

  D. The suppliers table now contains the data from the new_suppliers table, and the new_suppliers table now contains the data from the suppliers table.

  E. The suppliers table now contains only the data from the new_suppliers table.

  Correct Answer: B

• Question 24:

  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?

  

  A. Option A

  B. Option B

  C. Option C

  D. Option D

  E. Option E

  Correct Answer: E

• Question 25:

  Which of the following commands can the analyst use to complete the task without producing an error?

  A. DROP DATABASE database_name;

  B. DROP TABLE database_name.table_name;

  C. DELETE TABLE database_name.table_name;

  D. DELETE TABLE table_name FROM database_name;

  E. DROP TABLE table_name FROM database_name;

  Correct Answer: B

• Question 26:

  A data analyst created and is the owner of the managed table my_ table. They now want to change ownership of the table to a single other user using Data Explorer.

  Which of the following approaches can the analyst use to complete the task?

  A. Edit the Owner field in the table page by removing their own account

  B. Edit the Owner field in the table page by selecting All Users

  C. Edit the Owner field in the table page by selecting the new owner's account

  D. Edit the Owner field in the table page by selecting the Admins group

  E. Edit the Owner field in the table page by removing all access

  Correct Answer: C

• Question 27:

  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?

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

  B. The view has been dropped.

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

  D. 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.

  E. The view has been converted into a table.

  Correct Answer: B

  The command you sent creates a TEMP VIEW, which is a type of view that is only visible and accessible to the session that created it.

  When the session ends or the user logs out, the TEMP VIEW is automatically dropped and cannot be queried anymore. Therefore, after logging back in two days later, the status of the stakeholders.eur_customers view is that it has been

  dropped and SELECT * FROM stakeholders.eur_customers will result in an error. The other options are not correct because:

  A) The view does not remain available, as it is a TEMP VIEW that is dropped when the session ends or the user logs out.

  C) The view is not available in the metastore, as it is a TEMP VIEW that is not registered in the metastore. The underlying data cannot be accessed with SELECT * FROM delta. stakeholders.eur_customers, as this is not a valid syntax for

  querying a Delta Lake table. The correct syntax would be SELECT * FROM delta.dbfs:/stakeholders/eur_customers, where the location path is enclosed in backticks. However, this would also result in an error, as the TEMP VIEW does not

  write any data to the file system and the location path does not exist.

  D) The view does not remain available, as it is a TEMP VIEW that is dropped when the session ends or the user logs out. Data in views are not automatically deleted after logging out, as views do not store any data. They are only logical

  representations of queries on base tables or other views.

  E) The view has not been converted into a table, as there is no automatic conversion between views and tables in Databricks. To create a table from a view, you need to use a CREATE TABLE AS statement or a similar command.

• Question 28:

  Which of the following benefits of using Databricks SQL is provided by Data Explorer?

  A. It can be used to run UPDATE queries to update any tables in a database.

  B. It can be used to view metadata and data, as well as view/change permissions.

  C. It can be used to produce dashboards that allow data exploration.

  D. It can be used to make visualizations that can be shared with stakeholders.

  E. It can be used to connect to third party BI cools.

  Correct Answer: B

• Question 29:

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

  A. ACID transactions

  B. Flexible schemas

  C. Data deletion

  D. Scalable storage

  E. Open-source formats

  Correct Answer: A

  Reference: https://docs.databricks.com/en/delta/index.html

• Question 30:

  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?

  A. None of these

  B. Table metadata, data summary visualizations, and owner account information

  C. Table metadata

  D. Data summary visualizations

  E. Owner account information

  Correct Answer: C

  Reference: https://docs.databricks.com/en/delta/custom-metadata.html