Databricks Data Engineer Associate Question 354
Single answerYou are working on a data pipeline that ingests daily incrementally updated CSV files from an external source into a Delta Lake table. The files are stored in an external cloud storage location, and you need to load only the new files into the Delta table efficiently while ensuring that no duplicate files are processed. Which approach should you use?
- A
Use the COPY INTO command to load the files into the Delta table, specifying a file pattern to identify new files.
- B
Use the MERGE INTO command to load the files into the Delta table while checking for duplicates.
- C
Use a Spark DataFrame to read all files and write them into the Delta table using the append mode.
- D
Use the Auto Loader feature in Databricks to continuously monitor the cloud storage location and update the Delta table.
Show answer and explanation
Correct answer: A
Explanation
The COPY INTO command is the most efficient and straightforward solution for incrementally loading new files into a Delta table while avoiding duplicate processing. It is specifically designed for scenarios where files are ingested incrementally from an external location. Other options may require additional configurations or are less suitable for this specific use case.
- A. Correct.
Correct. The COPY INTO command is designed to load data incrementally into a Delta table by specifying a source location and a file pattern. It can also track processed files using a manifest, ensuring efficient and duplicate-free ingestion.
- B. Incorrect.
Incorrect. While MERGE INTO can handle deduplication during upserts, it is not suitable for simple incremental ingestion of new files. It is better suited for merging datasets with complex conditions.
- C. Incorrect.
Incorrect. Using a Spark DataFrame and append mode does not inherently handle duplicate files or incremental processing efficiently. It requires additional logic to prevent reprocessing.
- D. Incorrect.
Incorrect. While Auto Loader is a great tool for streaming incremental data into Delta Lake, it is not explicitly necessary for the described scenario where COPY INTO provides a more straightforward solution.