Databricks Data Engineer Associate Question 127
Single answerYou are working on a Databricks notebook and need to create a new table called distinct_users from an existing table named user_logs. The new table should remove duplicate rows based on all columns. Which of the following code snippets will correctly achieve this task?
- A
CREATE TABLE distinct_users AS SELECT DISTINCT * FROM user_logs
- B
CREATE TABLE distinct_users AS SELECT * FROM user_logs WHERE NOT EXISTS (SELECT 1 FROM user_logs u WHERE u.id = user_logs.id)
- C
CREATE TABLE distinct_users USING delta AS SELECT * FROM user_logs GROUP BY *
- D
CREATE TABLE distinct_users USING delta AS SELECT DISTINCT * FROM user_logs
Show answer and explanation
Correct answer: D
Explanation
The correct approach to create a new table from an existing table while removing duplicate rows in Databricks is to use the SELECT DISTINCT statement along with the USING delta clause. The DISTINCT keyword ensures duplicate rows are removed, and USING delta specifies the storage format for the new table. This aligns with Databricks best practices for table creation.
- A. Incorrect.
This is incorrect because while the syntax is almost correct, it does not specify the storage format (
USING delta) required for table creation in Databricks. - B. Incorrect.
This is incorrect because it uses a
NOT EXISTSsubquery that is irrelevant for removing duplicate rows. This approach would filter rows based on specific conditions rather than removing duplicates. - C. Incorrect.
This is incorrect because you cannot use a
GROUP BY *clause in SQL. TheGROUP BYclause requires specific column names and is not applicable for this use case. - D. Correct.
This is correct because it uses the
DISTINCTkeyword to remove duplicate rows and specifies theUSING deltaclause to create the table in Databricks, which is required for compatibility with the platform.