Databricks Data Engineer Associate Question 361
Select 3You are working on a Databricks notebook and need to incrementally load new data from an external S3 bucket into a Delta table. The data files are in Parquet format, and you want to ensure that duplicate records are not introduced during the load process. Which of the following steps should you take to achieve this using the COPY INTO command?
- A
Specify the file format as 'parquet' in the COPY INTO command.
- B
Use the 'MERGE' option in the COPY INTO command to prevent duplicate records.
- C
Provide the path of the S3 bucket as the source in the COPY INTO command.
- D
Use the 'FILELIST' option to specify a subset of files to load.
- E
Include a WHERE clause to filter out duplicate records before loading.
Show answer and explanation
Correct answers: A, C, D
Explanation
The COPY INTO command is commonly used in Databricks to incrementally load data from external sources into Delta tables. To ensure a successful operation, you need to specify the file format (e.g., 'parquet') and provide the source path (e.g., an S3 bucket). The 'FILELIST' option is also useful for controlling which files to load. However, handling duplicate records requires additional logic, such as deduplication after loading or managing primary keys within the Delta table. The COPY INTO command does not support options like 'MERGE' or WHERE clauses directly.
- A. Correct.
Correct: Specifying the file format as 'parquet' is necessary to correctly interpret the source data during the COPY INTO operation.
- B. Incorrect.
Incorrect: The 'MERGE' option is not supported in the COPY INTO command. To handle duplicates, you would typically use a different approach, such as managing primary keys within the Delta table.
- C. Correct.
Correct: Providing the S3 bucket's path as the source is essential to read the data files for the COPY INTO command.
- D. Correct.
Correct: Using the 'FILELIST' option allows you to specify a subset of files to load, which can help control which files are processed, especially in incremental loading scenarios.
- E. Incorrect.
Incorrect: A WHERE clause cannot be used directly in the COPY INTO command. Filtering needs to be done either at the source location or after loading the data into the Delta table.