Microsoft Certified: Fabric Analytics Engineer AssociatePlan and implement data analytics solutions (10-15%)Hard
A data engineer is working with a Microsoft Fabric Lakehouse. They have ingested raw CSV files into the 'Files' section of the Lakehouse. Now, they need to create a managed Delta table from these CSV files in the 'Tables' section. They want to incrementally load new CSV files that arrive daily into this Delta table, ensuring schema evolution is handled gracefully. Which approach is most suitable for this task?
- ACreating an external table over the CSV files and then using `INSERT INTO`.
- BUsing a Spark notebook with Structured Streaming to monitor the 'Files' section and append to the Delta table.
- CManually converting each CSV to Parquet and then appending to the Delta table.
- DUsing `COPY INTO` to load each new CSV file into the Delta table.
Show answer & explanationAnswer & explanation
Correct answer: D. Using `COPY INTO` to load each new CSV file into the Delta table.
`COPY INTO` is a robust and efficient command in Delta Lake specifically designed for idempotent and fault-tolerant ingestion of data from file formats (like CSV) into Delta tables. It automatically handles schema inference and evolution (with options like `mergeSchema`), making it ideal for incremental loading of new files into a managed Delta table.
Why the other options are wrong
- A. While possible, `INSERT INTO` from an external table doesn't offer the same level of idempotency, fault tolerance, and explicit schema evolution handling as `COPY INTO` for file-based ingestion.
- B. Structured Streaming is for continuous, near real-time ingestion, which might be overkill for a daily incremental batch and adds more complexity than `COPY INTO` for this specific file-based scenario.
- C. Manual conversion and appending is inefficient, prone to errors, and doesn't natively handle schema evolution or idempotency.
Delta Lake COPY INTO
The `COPY INTO` command provides an idempotent and fault-tolerant way to load data from external sources (like CSV, Parquet, JSON files) into Delta tables, supporting schema inference and evolution.
- Idempotent: can be run multiple times without duplicates.
- Supports various file formats.
- Handles schema evolution with `mergeSchema` option.
- Optimized for large-scale data ingestion.
Memory trick: COPY INTO brings files to Delta, smart and safe.