Databricks Data Engineer Associate Question 323
Select 2A data engineering team is working on a Delta table named 'sales_data' stored in the Databricks Lakehouse. They want to replace the existing table with a new schema and data while ensuring the outdated table no longer exists. Afterward, they need to overwrite the content of the new table with a filtered subset of data from another table called 'raw_sales'. Which of the following steps would correctly accomplish this?
- A
Use CREATE OR REPLACE TABLE sales_data AS SELECT * FROM raw_sales to replace the table and load all data.
- B
Use CREATE OR REPLACE TABLE sales_data with a new schema definition to replace the table.
- C
Use INSERT INTO sales_data SELECT * FROM raw_sales WHERE region = 'US' to overwrite the existing data.
- D
Use INSERT OVERWRITE sales_data SELECT * FROM raw_sales WHERE region = 'US' to overwrite the existing data.
- E
Use CREATE TABLE IF NOT EXISTS sales_data AS SELECT * FROM raw_sales to replace the table if it doesn't already exist.
Show answer and explanation
Correct answers: B, D
Explanation
The correct approach involves two steps: First, use CREATE OR REPLACE TABLE to replace the existing table with a new schema, ensuring the outdated table is replaced. Second, use INSERT OVERWRITE to overwrite the content of the new table with the filtered subset of data from 'raw_sales'.
- A. Incorrect.
This option is incorrect because CREATE OR REPLACE TABLE with AS SELECT would replace the table with a new version based on all data from 'raw_sales', not just the required subset.
- B. Correct.
This option is correct because CREATE OR REPLACE TABLE can be used to replace the table with a new schema definition. This ensures the outdated table no longer exists.
- C. Incorrect.
This option is incorrect because INSERT INTO adds data to the table without removing existing data, which does not meet the requirement to overwrite the content.
- D. Correct.
This option is correct because INSERT OVERWRITE replaces the content of the table with the specified subset of data from 'raw_sales'.
- E. Incorrect.
This option is incorrect because CREATE TABLE IF NOT EXISTS does not replace an existing table, which contradicts the requirement to replace the outdated table.