Forum Discussion
Gen2 df external table
At the moment, Dataflow Gen2 only loads data to tables. Do feel free to suggest new destinations (and formats) in the Fabric Ideas portal (https://aka.ms/FabricIdeas)
An alternative is to leverage the copy activity or a copy job. Especially as the bronze layer is typically used for the files in its raw state, so no transformation should be performed at that layer and instead a simple copy activity should be good enough. If a connector is missing from the copy job / copy activity, then would you mind letting us know what the source is? you can also post a new idea for such connector in the Ideas Portal.
I'm curious, what are the benefits of writing to files instead of just appending to a lakehouse bronze delta table?
Why External Tables are Ideal for the Bronze Layer in Production Data Lakes (according to my practical experience of data engineering and servicing BI)
In a well-architected Data Lake, data flows through three layers:
- Bronze (Raw Ingestion),
- Silver (Cleaned & Enriched),
- Gold (Curated Business Data with Semantic Models).
The Bronze Layer is where raw data from various sources like on-prem SQL, SharePoint, Azure SQL, Oracle, APIs, and Databricks is ingested. Using external tables for this layer is highly advantageous for the following reasons:
1. Data Persists Beyond Table Lifetime
- External tables store data separately from the metadata, so dropping the table does not delete the data.
- This ensures raw ingested data is always available for reprocessing or auditing.
2. Easy Table Rebuilds Without Re-ingestion
- Since the data remains in the storage layer, you can recreate the table schema at any time without fetching the source data again.
- This is crucial for schema adjustments or optimization without risking data loss.
3. Multiple Silver/Gold Views from the Same Data
- External tables allow you to build multiple transformations (Silver/Gold) from the same Bronze data.
- This eliminates redundancy and maintains a single source of truth for different business units like Finance, Procurement, Leasing, and Engineering.
4. Flexible Backfills and Schema Evolutions
- Adding new columns, adjusting schemas, or historical backfills are seamless.
- You can introduce new attributes for all past, present, and future data without re-ingesting or dropping the table.
5. Enhanced Audit Traceability
- Every row can be traced back to its original source file or API batch.
- This provides clear visibility into when and where data was ingested — critical for regulatory compliance and debugging.
Conclusion
External tables in the Bronze layer offer:
- Data safety beyond table lifecycle
- Rebuild flexibility without re-fetching data
- Multi-view capability for different business requirements
- Smooth schema evolution and backfills
- Full audit traceability for compliance and debugging
This design pattern forms the backbone of a resilient, scalable, and auditable Data Lake architecture.
