Databricks Data Engineer Associate Question 128
Single answerYou are working on a Databricks notebook and have an existing table named sales_data that contains duplicate rows. You need to create a new table called sales_data_deduplicated by removing all duplicate rows from sales_data. Which of the following commands will achieve this?
- A
CREATE TABLE sales_data_deduplicated AS SELECT DISTINCT * FROM sales_data
- B
CREATE TABLE sales_data_deduplicated AS SELECT * FROM sales_data WHERE is_distinct = TRUE
- C
CREATE OR REPLACE TABLE sales_data_deduplicated AS SELECT DISTINCT * FROM sales_data
- D
CREATE TABLE sales_data_deduplicated USING DELTA AS SELECT DISTINCT * FROM sales_data
Show answer and explanation
Correct answer: A
Explanation
To create a new table from an existing table while removing duplicate rows, the CREATE TABLE AS SELECT (CTAS) statement can be used with the SELECT DISTINCT clause. This ensures that only unique rows are included in the new table. The correct answer is the first option, as it adheres to the syntax requirements and meets the scenario's requirements without additional unnecessary clauses.
- A. Correct.
This is the correct syntax to create a new table by selecting distinct (removing duplicates) from the existing table
sales_data. TheCREATE TABLE AS SELECT(CTAS) statement is used to create a new table and populate it with the results of aSELECTquery. - B. Incorrect.
This option is incorrect because there is no
is_distinctcolumn or feature in thesales_datatable unless explicitly created by the user, which is not mentioned in the scenario. - C. Incorrect.
This option is incorrect because
CREATE OR REPLACE TABLEreplaces an existing table if it exists, but the scenario specifies creating a 'new' table, not replacing an existing one. - D. Incorrect.
This option is incorrect because the syntax is valid only when explicitly using the
USING DELTAclause for Delta tables, which is not stated in the scenario. The question does not specify the need for a Delta table.