Databricks Data Engineer Associate Question 329
Select 3You are working on a Databricks project where you need to update a table with new data. The table is partitioned by the 'year' column. You want to replace the entire dataset in the table with new data. Which of the following statements about using CREATE OR REPLACE TABLE versus INSERT OVERWRITE is correct?
- A
CREATE OR REPLACE TABLE will remove the existing table metadata and recreate it, whereas INSERT OVERWRITE does not alter the metadata.
- B
INSERT OVERWRITE can target specific partitions to overwrite, but CREATE OR REPLACE TABLE always replaces the entire table.
- C
Using CREATE OR REPLACE TABLE requires specifying the schema explicitly, whereas INSERT OVERWRITE does not.
- D
INSERT OVERWRITE is generally faster for overwriting specific partitions in a large table compared to CREATE OR REPLACE TABLE.
- E
CREATE OR REPLACE TABLE will preserve the table's existing constraints and properties, whereas INSERT OVERWRITE will not.
Show answer and explanation
Correct answers: A, B, D
Explanation
CREATE OR REPLACE TABLE and INSERT OVERWRITE serve different purposes. CREATE OR REPLACE TABLE recreates the table, resetting its metadata and replacing all data, whereas INSERT OVERWRITE is used to overwrite data either entirely or at the partition level without altering metadata. INSERT OVERWRITE is more efficient for updating specific partitions in large tables.
- A. Correct.
Correct. CREATE OR REPLACE TABLE recreates the table, which results in the metadata (e.g., table properties, constraints, and schema) being reset, while INSERT OVERWRITE does not affect metadata.
- B. Correct.
Correct. INSERT OVERWRITE allows you to overwrite specific partitions (e.g., using 'PARTITION(year = 2023)'), whereas CREATE OR REPLACE TABLE always replaces the entire dataset.
- C. Incorrect.
Incorrect. CREATE OR REPLACE TABLE does not always require explicitly specifying the schema if it is derived from the query used to populate the table. INSERT OVERWRITE also allows schema inference when used with dynamic queries.
- D. Correct.
Correct. INSERT OVERWRITE is optimized for overwriting specific partitions, making it more efficient for large partitioned tables compared to recreating the entire table with CREATE OR REPLACE TABLE.
- E. Incorrect.
Incorrect. CREATE OR REPLACE TABLE does not preserve the existing table’s properties or constraints, as these are reset during table recreation.