Databricks Data Engineer Associate Question 126
Single answerYou are working on a Databricks notebook and need to create a new table cleaned_customers from an existing table raw_customers, ensuring that duplicate rows are removed in the process. Which of the following commands will correctly achieve this?
- A
CREATE TABLE cleaned_customers AS SELECT DISTINCT * FROM raw_customers
- B
CREATE TABLE cleaned_customers AS SELECT * FROM raw_customers GROUP BY *
- C
CREATE TABLE cleaned_customers AS SELECT * FROM raw_customers WHERE UNIQUE
- D
INSERT INTO cleaned_customers SELECT DISTINCT * FROM raw_customers
Show answer and explanation
Correct answer: A
Explanation
The correct approach to create a new table from an existing table while removing duplicate rows is to use the CREATE TABLE ... AS SELECT DISTINCT ... syntax. This ensures that only unique rows from the existing table are included in the new table. The other options either use invalid SQL syntax or do not meet the requirements of the scenario.
- A. Correct.
This is the correct option. Using
SELECT DISTINCTensures that only unique rows are included in the new tablecleaned_customerswhen created fromraw_customers. - B. Incorrect.
This is incorrect because
GROUP BY *is not valid SQL syntax. TheGROUP BYclause requires specific column names and cannot be used with*. - C. Incorrect.
This is incorrect because
WHERE UNIQUEis not valid SQL syntax in Databricks or SQL in general. - D. Incorrect.
This is incorrect because the
INSERT INTOstatement would requirecleaned_customersto already exist, which is not the case in this scenario.