Databricks Data Engineer Associate Question 326
Select 2You are working on a Databricks table named 'sales_data' in the 'analytics' database. The table already exists, but you need to refresh its content with new data stored in a staging table named 'staging_sales_data'. The table schema and partitions remain unchanged. Which of the following approach(es) would correctly achieve this goal?
- A
Use
CREATE OR REPLACE TABLE analytics.sales_data AS SELECT * FROM staging_sales_data. - B
Use
INSERT OVERWRITE TABLE analytics.sales_data SELECT * FROM staging_sales_data. - C
Use
CREATE TABLE analytics.sales_data AS SELECT * FROM staging_sales_data. - D
Use
INSERT INTO analytics.sales_data SELECT * FROM staging_sales_data. - E
Use
CREATE OR REPLACE TABLE analytics.sales_data OPTIONS ('mergeSchema' = 'true') AS SELECT * FROM staging_sales_data.
Show answer and explanation
Correct answers: A, B
Explanation
The goal is to refresh the existing 'sales_data' table with new data from 'staging_sales_data'. CREATE OR REPLACE TABLE and INSERT OVERWRITE are valid approaches to replace the entire content of the table while keeping the schema intact. INSERT INTO appends data instead of replacing it, and CREATE TABLE will fail because the table already exists. The mergeSchema option is unnecessary here since the schema is not changing.
- A. Correct.
This is correct because
CREATE OR REPLACE TABLEreplaces the existing table and loads it with the new data from the staging table. This ensures the new content is fully refreshed. - B. Correct.
This is correct because
INSERT OVERWRITEreplaces all the data in the target table with the data from the staging table without altering the schema or table definition. - C. Incorrect.
This is incorrect because
CREATE TABLEwill fail if the table already exists. It does not support overwriting an existing table. - D. Incorrect.
This is incorrect because
INSERT INTOappends data to the target table, rather than replacing its content. This does not meet the requirement of refreshing the table's content. - E. Incorrect.
This is incorrect because
CREATE OR REPLACE TABLEwith themergeSchemaoption is used for schema evolution and is not relevant for this scenario, as the requirement states that the schema remains unchanged.