Forum Discussion
Code organization for Bronze layer
Hi,
I am the only person managing data analytics at a startup. We use a single Microsoft Fabric workspace for our Bronze, Silver, and Gold data layers.
For the Bronze layer, we ingest data from more than 10 source applications using a metadata-driven framework. Each application has different connection requirements and may return data in formats such as CSV, simple JSON, or deeply nested JSON.
What is the best way to organize the following components?
- Reusable ingestion code
- Application-specific connection configurations
- Metadata tables and configuration files
- Source tables and extraction settings
- File-format-specific processing
- JSON flattening rules for both simple and nested structures
- Pipelines, notebooks, and supporting utilities
I would like the framework to use metadata and configuration files wherever possible, while still supporting application-specific logic when required. Please review the ER diagram for metadata driven tables. Could you recommend a simple, scalable folder structure and an overall design for organizing the Bronze-layer ingestion framework in Microsoft Fabric?
Hi reddyr2502 ,
Your ER model is a good starting point for a metadata-driven Bronze ingestion framework. I would keep the design simple by separating orchestration, reusable ingestion logic, configuration, and application-specific exceptions.
A practical structure could be:
I would use the metadata tables for operational configuration such as source system, connection, source object, load type, watermark, schedule, active status and run history.
Use JSON/configuration files for more complex or variable rules, particularly JSON paths, flattening/explode rules, schema mappings, API pagination options and application-specific parameters. This avoids making the metadata tables increasingly wide as new source types are added.
The execution pattern can then remain simple:
For standard source movement, I would use Fabric Pipeline/Copy Activity and supported Fabric connectors where possible. Notebooks are better reserved for processing that actually requires code, such as complex/nested JSON handling or source-specific logic.
For unusual applications, keep a Custom area as an escape hatch rather than trying to make the generic framework support every possible scenario.
I would also keep actual credentials out of the metadata/config files. Store only the appropriate connection/secret reference and resolve credentials securely at runtime.
The main design principle I would follow is:
Adding a new source should normally require metadata and configuration changes, not a new pipeline or notebook.
Application-specific code should be the exception. That keeps the framework scalable while still being practical for a small team to operate.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
6 Replies
ssritharSuper User
Hi reddyr2502 ,
Your ER model is a good starting point for a metadata-driven Bronze ingestion framework. I would keep the design simple by separating orchestration, reusable ingestion logic, configuration, and application-specific exceptions.
A practical structure could be:
I would use the metadata tables for operational configuration such as source system, connection, source object, load type, watermark, schedule, active status and run history.
Use JSON/configuration files for more complex or variable rules, particularly JSON paths, flattening/explode rules, schema mappings, API pagination options and application-specific parameters. This avoids making the metadata tables increasingly wide as new source types are added.
The execution pattern can then remain simple:
For standard source movement, I would use Fabric Pipeline/Copy Activity and supported Fabric connectors where possible. Notebooks are better reserved for processing that actually requires code, such as complex/nested JSON handling or source-specific logic.
For unusual applications, keep a Custom area as an escape hatch rather than trying to make the generic framework support every possible scenario.
I would also keep actual credentials out of the metadata/config files. Store only the appropriate connection/secret reference and resolve credentials securely at runtime.
The main design principle I would follow is:
Adding a new source should normally require metadata and configuration changes, not a new pipeline or notebook.
Application-specific code should be the exception. That keeps the framework scalable while still being practical for a small team to operate.
If this post helps, then please appreciate giving a Kudos or accepting as a Solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
v-achippaCommunity Support
Hi reddyr2502,
Thank you for reaching out to Microsoft Fabric Community.
Thank you gslick, ssrithar and jayaprakash2000ShivekMaharaj for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the response provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar ChippaJust looking at the design, could you not use KeyVault instead of putting usernames and passwords in a table?
Hi, I went through the ER diagram. The overall design looks good for a metadata-driven Bronze ingestion framework.
Since you are handling 10+ source applications, I would avoid creating separate pipelines and notebooks for each source.
I would keep one master pipeline and a few reusable notebooks for API, JDBC, files, and JSON processing.
I would suggest adding a separate SourceEntity table to keep the source tables/files/API objects separate from the connection details.
For JSON, I would keep the flattening rules in a separate JSONMapping table, especially for deeply nested JSON.
The pipeline can pass SourceID and EntityID to the notebook, and the notebook can get the required configuration from the metadata tables.
I would also keep watermark and last-run information separately from the main configuration, using something like an EntityState table.
The existing PipelineRunLog can be used for run status, row counts, errors, start/end time, and watermark details.
For application-specific cases such as unusual authentication, pagination, or JSON structures, I would keep small custom functions instead of adding too many conditions to the common notebook.
For the workspace, I would keep separate folders for 01_Bronze, 02_Silver, and 03_Gold, even if they are currently in the same workspace.
Overall, I would follow a simple approach: metadata for common configuration, reusable notebooks for common processing, and custom code only where a source really needs it.
- v-achippa
Hi reddyr2502,
We wanted to kindly follow up to check if the response provided by the user's for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa - odtJitendra
Advocate I
Bronze Layer – Layer Description
Hi reddyr2502
Please find the high level points.
- Data Ingestion Layer
- Connect to source applications and extract data.
- Handle API, database, SFTP, files, etc.
- Data Landing Layer
- Store the extracted data in its original/raw format.
- Preserve source data for reprocessing and audit purposes.
- Data Parsing Layer
- Process different file/data formats such as CSV, JSON, and nested JSON.
- Apply format-specific parsing logic.
- Data Transformation Layer
- Perform basic Bronze-level transformations such as JSON flattening, exploding arrays, column mapping, and data-type handling.
- Keep business transformations for the Silver layer.
- Data Loading Layer
- Load processed data into Bronze Delta tables.
- Handle full and incremental loads.
- Metadata & Configuration Layer
- Control ingestion behavior using metadata tables and configuration files.
- Store source, extraction, file-format, and JSON-processing rules.
- Application-Specific Logic Layer
- Handle source-specific requirements that cannot be managed through generic metadata.
- Implement these as isolated adapters/utilities rather than modifying the core framework.
- Audit & Monitoring Layer
- Track ingestion status, record counts, execution times, watermarks, and errors.
- Data Ingestion Layer
