Databricks Data Engineer Associate Question 327
Select 2You are working on a Databricks project where you need to update a table named 'sales_data' with the latest sales records. The table already exists, but you must ensure it is completely replaced with a new dataset stored in a DataFrame named new_sales. After replacing the table, you must append additional sales records from another DataFrame named additional_sales. Which of the following code snippets correctly achieves this?
- A
new_sales.write.format('delta').mode('overwrite').saveAsTable('sales_data'); additional_sales.write.insertInto('sales_data')
- B
spark.sql('CREATE OR REPLACE TABLE sales_data AS SELECT * FROM new_sales'); additional_sales.write.insertInto('sales_data')
- C
new_sales.write.format('delta').mode('overwrite').saveAsTable('sales_data'); additional_sales.write.format('delta').mode('append').saveAsTable('sales_data')
- D
spark.sql('CREATE OR REPLACE TABLE sales_data AS SELECT * FROM new_sales'); spark.sql('INSERT OVERWRITE TABLE sales_data SELECT * FROM additional_sales')
- E
spark.sql('CREATE OR REPLACE TABLE sales_data AS SELECT * FROM new_sales'); additional_sales.write.format('delta').mode('overwrite').saveAsTable('sales_data')
Show answer and explanation
Correct answers: B, D
Explanation
The correct approach is to first replace the table using CREATE OR REPLACE TABLE with the new_sales DataFrame and then append the additional_sales DataFrame. Both options 2 and 4 achieve this by leveraging Databricks SQL commands (CREATE OR REPLACE TABLE and INSERT OVERWRITE) to ensure the table is replaced and data is appended correctly.
- A. Incorrect.
This option uses the correct 'overwrite' mode for replacing the table, but the
insertIntomethod is not valid unless the table was created using Hive table syntax. - B. Correct.
This option uses
CREATE OR REPLACE TABLEto replace the table and then correctly inserts additional records using theinsertIntomethod. - C. Incorrect.
This option attempts to append data to the table after overwriting it, but
saveAsTablewith 'append' mode is not compatible with the required behavior of appending rows to an existing table. - D. Correct.
This option uses
CREATE OR REPLACE TABLEto replace the table and then usesINSERT OVERWRITEto append new records. Both methods are valid in this context. - E. Incorrect.
This option incorrectly overwrites the table a second time with
additional_sales, which does not meet the requirement of appending data to the replaced table.