Forum Discussion
Post PatronIntegrate Tactical solution into my Target Medallion Architecture
We have a medallion architecture as below
1 - Raw layer which is like-to-like with sources (only platform team allowed)
2 - Bronze layer (similar to raw) with historization (only platform team allowed)
3 - Silver layer is our canonical model (transformations) ( (only platform team allowed and Some Power Users)
4 - Gold layer, composed of one workspace per domain, each workspace has inside a lakehouse (using dimensional modeling) and a semantic model (Onwed by Domain data teams). Semantic model are build using Direct lake mode
5- Consumption allowed via pre-developed PBI report (connected to the semantic models) and allowance to do Self-service using both, PowerBI and Excel for certain users. The PBI and Excel self-service users can connect against both, lakehouses (via SQLendpoints) and via (Semantic model, which is the prefered way) their self-service reports.
6 - Other consumption methods allow SQLEndpoint consuming on Silver and Gold for certain users so they can export data into their side (AWS, and others)
7 - Data between layers is moved via shortcuts and after with notebooks (using python) persisted as tables. Notebooks are scheduled via fabric pipes
We are now integrating old projects into this new architectuture
The case I bring is related with a financial project which we need to integrated into this new architecture
The project, currevtly, works like this :
1 - Users add files into folders in onelake. Those appear under a lakehouse (in the file session of it). Then, users trigger manually a pipe which grabs this excel files (xls) and converts them into parquet files. The same pileline, after moves the data from Parquet into tables. Once the data is added into tables, the excel file is moved to the historical folder (from the To-be-processed) and the parquet file is deleted
On top of it, in gold layer, the users what to reconcile the infromation to approve it. Hence, they have created a PBI report which has a button. This button calls a User Data Function which the coded is in Fabric. Once the user validates the deata is ok, it clicks on that button of the PBI report and a row is added into a specific table as approved and an e-mail to the team is sent with a simple fixed text stating "submission approved"
There are multiple things that from my point of view make sense to change if we want to go near the new target architecture mentioned above:
1 - I think that probably it makes more sense that we ask the users to upload files into sharepoint, instead of doing it to onelake.
2 - We can create for example a sharepoint shortcut to fabric , which will keep the files where they are (in sharepoint)
3 - I don't appreciate the fact that the user has to run the pipeline manually, I would prefer something like event driven where a trigger is continuously checking the ToBeProcessed folder and when it finds the file, converts it to Parquet. But I hear my team stating that this could become expensive as the trigger would need to be continuoly running to check for news?
4 - Also interesting is, if we use the sharepoint, once the file is inserted into the table, how can we move that file from ToBeProcessed to Historical?
5 - Another point is they say they are using a fabric datawarehouse , instead of a lakehouse, because they have this user data funcion (which is bundled to the button the PBI) and the funcion needs to connect SQLendpoint to update a table . Seems they cannot do that from the lakehouse because the SQLendpoint in lakehouse does not allow updates / inserts. Isn't there another way for them to use this funcion but from a lakehouse instead of from a datawarehouse? because the architecture uses LHs not DWHs
6 - I would be delighted that this user reconcition would start to be done autmatically, instead of the user validating it. But I don't know if the user would like it
7 - If the above is not possible, maybe what I can do is "connect " this current solution they have (replicate it) to our silver layer or gold layer. And leave the project in the way they have currently. But I would prefer to fix this properly. What do you think?
Thanks a lot,
Pedro
Hi fabricpribeiro ,
Thanks for reaching out to the Microsoft Fabric Community forum.You may need to introduce a small control mechanism to track processed files by leveraging a stored procedure and a separate control table in a warehouse. In Fabric this aligns closely with Microsoft’s incremental load pattern, where a control (watermark) table is used together with a Lookup activity to determine what data needs to be processed. In your case, instead of tracking a column value (such as a timestamp or ID), you can maintain a dedicated processed files table that stores metadata like file name, path, timestamp, and processing status. At the start of the pipeline, a Lookup activity can read this control table to identify which files have already been processed and filter out only new files for ingestion. Once the pipeline successfully loads data into the Lakehouse, a stored procedure can then be invoked to update the control table ensuring the record is written only after successful processing, which is a key best practice from the guidance. This table becomes the single source of truth for file processing state, and based on it, a downstream orchestration step (such as a pipeline) can safely move files from the ToBeProcessed folder to the Historical folder while preventing reprocessing and maintaining consistency across runs.
Regarding your 5th question, yes SQL endpoints are strictly read only mode, so you may need to modify the architecture to include Warehouse
I hope this information helps. Please do let us know if you have any further queries.
Thank you
4 Replies
Hi fabricpribeiro ,
Thanks for reaching out to the Microsoft Fabric Community forum.You may need to introduce a small control mechanism to track processed files by leveraging a stored procedure and a separate control table in a warehouse. In Fabric this aligns closely with Microsoft’s incremental load pattern, where a control (watermark) table is used together with a Lookup activity to determine what data needs to be processed. In your case, instead of tracking a column value (such as a timestamp or ID), you can maintain a dedicated processed files table that stores metadata like file name, path, timestamp, and processing status. At the start of the pipeline, a Lookup activity can read this control table to identify which files have already been processed and filter out only new files for ingestion. Once the pipeline successfully loads data into the Lakehouse, a stored procedure can then be invoked to update the control table ensuring the record is written only after successful processing, which is a key best practice from the guidance. This table becomes the single source of truth for file processing state, and based on it, a downstream orchestration step (such as a pipeline) can safely move files from the ToBeProcessed folder to the Historical folder while preventing reprocessing and maintaining consistency across runs.
Regarding your 5th question, yes SQL endpoints are strictly read only mode, so you may need to modify the architecture to include Warehouse
I hope this information helps. Please do let us know if you have any further queries.
Thank you- 4iurchenko
Advocate III
Hi Pedro!
You are asking very thoughtful questions. Let's take it one by one.
1-2. Idea with replacing One Lake by SharePoint for users is a great idea.
3. There is no straight solution. Event driven approach is great, but may introduce challenges, for example user will put the wrong file and it will be processed immediately. On the other hand, manual running may be complex for users. I would suggest to build a small PoC and test different scenarios, not only from the technical perspective, but also from edge cases and convenience for people.
4. In regards of processed files, I'm not sure it's possible to move them in SharePoint. The alternative scenario may be to exclude processing files that already be processed by reading the target table, implementing that logic as a part of Spark Notebooks.5. Regarding custom functions, it's a tricky question. DWH has this kind of custom functions but by essence it's a cloud version of SQL server with its specifics. The work around to achieve similar thing in Lake house is using Spark notebooks there are you may define UDF functions or build a custom logic if it's your goal. But involving Spark notebooks needs to have special, spark and Python skills in the team.
Also taking the account you will have users interacting directly with data, I would also think about safeguards to check their data for NULLs or things like that, file structure, counts, etc., and prevent updating tables if something unusual happens. Also, I would suggest to think about idempotancy in data to be sure for example when user ingested the wrong data and then the user will fix that, the only correct portion of data will be replaced. The typical pattern in fabric is replacedWhere if we use spark notebooks.
I hope those suggestions are helpful and available. Kudo and making this answer as a solution will help bringing similar content.
BR, Yurri Hi fabricpribeiro
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
