Databricks Data Engineer Associate Question 129
Single answerYou are working on a Databricks notebook and want to create a new table named distinct_sales by removing duplicate rows from an existing table named raw_sales. Which of the following code snippets correctly achieves this?
- A
spark.sql("CREATE TABLE distinct_sales AS SELECT DISTINCT * FROM raw_sales")
- B
spark.sql("CREATE TABLE distinct_sales AS SELECT * FROM raw_sales GROUP BY *")
- C
spark.sql("CREATE TABLE distinct_sales AS SELECT * FROM raw_sales WHERE DISTINCT")
- D
spark.sql("CREATE TABLE distinct_sales USING delta AS SELECT DISTINCT * FROM raw_sales")
Show answer and explanation
Correct answer: D
Explanation
The correct way to create a new table from an existing table while removing duplicates in Databricks is to use the DISTINCT keyword in the SELECT statement. Additionally, Databricks requires specifying a storage format, such as Delta, when creating a table. The fourth option demonstrates the correct syntax with both DISTINCT and the Delta format.
- A. Incorrect.
This syntax is almost correct but lacks the specification of a storage format such as Delta, which is required in Databricks for creating tables.
- B. Incorrect.
This syntax is invalid because GROUP BY cannot be used with the wildcard
*. GROUP BY requires explicit column names. - C. Incorrect.
This syntax is invalid because 'WHERE DISTINCT' is not valid SQL syntax.
- D. Correct.
This is the correct syntax as it creates a new table using the Delta format and removes duplicate rows with the DISTINCT keyword.