Databricks Data Engineer Associate Question 325
Single answerYou are working on a Databricks project where you need to create a table named 'sales_data' in the 'analytics' database. If the table already exists, it should be replaced. After creating the table, you need to overwrite its data with new records from a DataFrame named 'new_sales_df'. Which of the following code snippets accomplish this task correctly?
- A
CREATE OR REPLACE TABLE analytics.sales_data USING delta; INSERT OVERWRITE analytics.sales_data SELECT * FROM new_sales_df;
- B
CREATE OR REPLACE TABLE analytics.sales_data AS SELECT * FROM new_sales_df;
- C
CREATE OR REPLACE TABLE analytics.sales_data USING delta AS SELECT * FROM new_sales_df;
- D
INSERT OVERWRITE analytics.sales_data SELECT * FROM new_sales_df; CREATE OR REPLACE TABLE analytics.sales_data USING delta;
Show answer and explanation
Correct answer: C
Explanation
The 'CREATE OR REPLACE TABLE' statement can be used to create or replace a table, and the 'AS SELECT' clause allows for both creating the table and populating it with data in a single statement. Additionally, specifying 'USING delta' ensures the table is created as a Delta table. This is the most efficient and correct way to achieve the task outlined in the scenario.
- A. Incorrect.
This syntax is incorrect because 'INSERT OVERWRITE' cannot be used immediately after creating a table with 'CREATE OR REPLACE TABLE'. The correct approach is to directly use the 'AS SELECT' clause within 'CREATE OR REPLACE TABLE'.
- B. Incorrect.
This option will replace the table and populate it with data from 'new_sales_df', but it does not specify the 'delta' format. Since the question implies usage of Delta tables, this is not the most accurate choice.
- C. Correct.
This option is correct because it uses 'CREATE OR REPLACE TABLE' with the 'delta' format and directly populates the table with data from 'new_sales_df' using the 'AS SELECT' clause.
- D. Incorrect.
This syntax is invalid because 'INSERT OVERWRITE' cannot precede the 'CREATE OR REPLACE TABLE' statement. The order of operations in this option is incorrect.