A data engineering team is setting up an ingestion process for customer survey responses using Dataflows Gen2. The survey data is provided daily as new CSV files in an Azure Data Lake Storage Gen2 folder. Each CSV file contains responses for a single day. The team needs to ensure that only new files are processed and appended to the existing Lakehouse table each day, avoiding re-processing or duplicating historical data. Which Dataflows Gen2 feature is best suited for achieving this incremental file ingestion?
- AImplementing a custom PySpark script to identify and filter new files.
- BSetting up a 'File system' connector with a wildcard path and 'Last modified' filter.
- CConfiguring the Dataflow to truncate and reload the entire table daily.
- DUsing the 'Delta table' destination option with 'Append' write mode.
Show answer & explanationAnswer & explanation
Correct answer: D. Using the 'Delta table' destination option with 'Append' write mode.
Dataflows Gen2, when writing to a Lakehouse table, supports Delta Lake features. By selecting the 'Delta table' destination and choosing the 'Append' write mode, Dataflows Gen2 can efficiently add new records from the source to the existing table. For incremental file ingestion from a folder, the Dataflow will typically process newly detected files, and Delta Lake's ACID properties help manage these appends robustly.
Why the other options are wrong
- A. While possible, implementing custom PySpark for file identification is complex and unnecessary, as Dataflows Gen2 provides native capabilities for this common scenario.
- B. While a wildcard path can read multiple files, Dataflows Gen2 doesn't inherently have a 'Last modified' filter at the connector level that automatically tracks and processes only *new* files for incremental appends to Delta tables in the way a 'Changed data capture' mechanism would or how the Delta table append mode handles new data from the source query.
- C. Truncating and reloading the entire table is a full-load approach, not incremental, and would be inefficient and costly for large datasets.
Dataflows Gen2 Incremental File Ingestion
The process of efficiently adding only new or changed data from files to an existing destination table using Dataflows Gen2, typically leveraging Delta Lake capabilities.
- Achieved by writing to a Delta table.
- Uses 'Append' write mode for new records.
- Avoids reprocessing or duplicating historical data.
Memory trick: Append to Delta is like adding new pages to an existing, organized book.