Databricks Data Engineer Associate Question 324
Select 2You are working with a Databricks SQL table named 'sales_data' and want to update it with new data from a temporary table named 'updated_sales'. You want to ensure that the table schema is overwritten if necessary and that old data in 'sales_data' is replaced. Which combination of commands should you use?
- A
Use CREATE OR REPLACE TABLE sales_data AS SELECT * FROM updated_sales
- B
Use CREATE OR REPLACE TABLE sales_data; INSERT OVERWRITE sales_data SELECT * FROM updated_sales
- C
Use CREATE OR REPLACE TABLE sales_data AS SELECT * FROM sales_data UNION ALL SELECT * FROM updated_sales
- D
Use INSERT OVERWRITE sales_data SELECT * FROM updated_sales
- E
Use CREATE TABLE sales_data AS SELECT * FROM updated_sales
Show answer and explanation
Correct answers: A, D
Explanation
The correct approach to replace old data and ensure the schema is updated is by combining CREATE OR REPLACE TABLE (to manage schema and table creation) with INSERT OVERWRITE (to replace the data in the table). This ensures that both the schema and the table's contents are properly handled according to the requirements.
- A. Correct.
This is correct because CREATE OR REPLACE TABLE will ensure the table schema is updated if necessary, and the table is created if it does not already exist.
- B. Incorrect.
This option is incorrect because CREATE OR REPLACE TABLE without specifying a schema or query cannot be followed by INSERT OVERWRITE. It results in an invalid workflow.
- C. Incorrect.
This option is incorrect because UNION ALL does not replace data but appends additional rows, which does not meet the requirement of replacing old data.
- D. Correct.
This is correct because INSERT OVERWRITE replaces the existing data in the table with the new data from the query.
- E. Incorrect.
This option is incorrect because CREATE TABLE will fail if the table already exists, and it does not meet the requirement to replace the existing schema or data.