Databricks Data Engineer Associate Question 125
Select 2You are working with a Databricks Delta table named 'sales_data' and are tasked with creating a new table 'unique_sales' that contains all rows from 'sales_data' but without duplicates. Which of the following command(s) will correctly create the new table?
- A
CREATE TABLE unique_sales AS SELECT DISTINCT * FROM sales_data
- B
CREATE TABLE unique_sales AS SELECT * FROM sales_data GROUP BY *
- C
CREATE TABLE unique_sales USING DELTA AS SELECT DISTINCT * FROM sales_data
- D
CREATE TABLE unique_sales AS SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY all_columns ORDER BY any_column) AS row_num FROM sales_data) temp WHERE row_num = 1
- E
INSERT INTO unique_sales SELECT DISTINCT * FROM sales_data
Show answer and explanation
Correct answers: A, C
Explanation
To create a new table with unique rows, the SELECT DISTINCT statement is the most straightforward way to remove duplicates. Both options 1 and 3 correctly use SELECT DISTINCT to remove duplicates and create a new table. Option 3 also explicitly specifies the Delta format, which is valid in Databricks. The other options either use invalid syntax or do not fulfill the requirement to create a new table.
- A. Correct.
This is correct because the SELECT DISTINCT statement removes duplicate rows and the CREATE TABLE statement creates the new table as required.
- B. Incorrect.
This is incorrect because the GROUP BY clause is improperly used. GROUP BY requires specific columns to group by, and 'GROUP BY *' is invalid syntax.
- C. Correct.
This is correct because the SELECT DISTINCT statement removes duplicates, and the USING DELTA clause specifies the storage format for the table in Databricks.
- D. Incorrect.
This is incorrect because while the ROW_NUMBER function can be used to remove duplicates, the syntax here is overly complex and requires additional modifications for the correct implementation.
- E. Incorrect.
This is incorrect because INSERT INTO adds rows to an existing table; it does not create a new table. Additionally, 'unique_sales' must already exist for this to work.