Databricks Data Engineer Associate Question 356
Single answerYou are working on a Databricks Lakehouse platform and need to load incremental data from a directory in an external cloud storage location (e.g., S3 or ADLS) into a Delta table. The new data files are added hourly, and you want to ensure that only the new files are processed without duplicating previously loaded data. Which scenario is the most appropriate for using the COPY INTO command?
- A
Loading a large batch of historical data files into a Delta table for the first time.
- B
Incrementally loading new files from a directory into a Delta table with automatic schema inference and tracking of already processed files.
- C
Transforming and aggregating data stored in a Delta table and saving it back to the same Delta table.
- D
Updating specific rows in a Delta table based on changes in a staging table.
Show answer and explanation
Correct answer: B
Explanation
The COPY INTO command is specifically designed for scenarios where new files are incrementally added to a directory, and you need to load only these new files into a Delta table. COPY INTO tracks the files it has already processed, ensuring no duplicates are loaded. This makes it ideal for incremental ingestion from external cloud storage locations like S3 or ADLS.
- A. Incorrect.
Loading a large batch of historical data files is better suited for a one-time bulk load process using commands like 'MERGE INTO' or 'INSERT INTO' rather than 'COPY INTO', which is designed for incremental loads.
- B. Correct.
COPY INTO is specifically designed for incrementally loading files from a directory into a Delta table. It automatically tracks already processed files based on file metadata, ensuring that only new files are processed.
- C. Incorrect.
Transforming and aggregating data within a Delta table involves operations like SELECT, GROUP BY, or INSERT OVERWRITE, but does not align with the use case of incrementally loading new files from external storage.
- D. Incorrect.
Updating specific rows in a Delta table is typically achieved using the 'UPDATE' or 'MERGE INTO' commands, not 'COPY INTO'.