Forum Discussion
Fabric - Best Practise Question(s)
Hi Community,
in the last couple of weeks I tried to create a fabric demo-project and run into a lot of brick walls... I tried to use chatgpt or copilot (both were a total waste of my time...).
My scenario:
- I have data in a local database (postgres) with a lot of sensor data.
- I have an On Premise Datagateway to access the database
- I would like to implement incremental loading
- I would like to build a medallion architecture with a landing zone to be able to rebuild everything
I thought it would be good to have a lakehouse for landing an bronze (one lakehouse), another one for silver and a datawarehouse (SQL server) as gold layer.
I had a lot of problems with the landing zone - just some examples:
- I started with a pipeline - using the copy action. My logtables, watermark, definition of the sql-statements were located in the lakehouse. Big problem: I cannot update the lakehouse from the pipeline without using notebooks.
- I switched to a notebook, which is triggered by a pipeline. But: Spark notebooks are not able to use on premise data gateways.
- I again build a pipeline using copy action. My log/watermark/def tables are now in a sql-DB - but because I do not want to expose any of the landing/bronze meta-data in the gold layer I need another SQL-DB just for the management tables...
My background is development in C# or Java. So I really do not want to build complicated "concatenating" scripts to build SQL-statements in my pipeline using a lot of variables (feels little bit like SSIS which is a pain in the a**). I think it is not maintainable.
And: I try to implement logging using my own runIDs and so on. Probably there is amisunderstanding and this is already integrated in fabric and I do not have to implement that on my own. Same for watermarks: Probably there is already a framework, which I did not find?
My next problem: How do you solve dataquality checks. Do you use something like "Great Expections" or are you implementing your checks in every project from scratch?
I tried to find some "best practise" books or blog entries - without much success. Has anyone a good hint for a good book where those topics are decribed in "holistic" manner? I now, I have to the read the manual - but I need something not "Mickey mousy" 🙂
Thanks a lot
Holger
So, thanks for the answers. I had another topic in the data engineer forum and I came to cinculsion, that there is no "good" way. The way I do it it is always use pipelines to trigger jobs. The pipeline is able to read/insert/update information in a Warehouse (inserts for logStart, Update and Watermark for lokEnd and new TimeStamps). for ladning zone I use the copy action and stay in the pipeline functuionality using Sub-Pipelines (to be able to start copy actions in parallel). To bring everything in Bronze I use notebooks. Because it is complicated to qork with warehouse in spark I use again Pipelines for all meta-information and for logging. The notebooks are startet for each and every table in my metadata - not the best solution, because everytime a spark session is started and terminated - but the best in my architecural thinking. When it comes to bronze-> silver I think I will have more than one Notebook - depends on the domains of the tables and what to do. In gold I think I am goign to work with materialized views.
Hope this helps anyone.
8 Replies
- deborshi_nag
Super User
Here are some design opinions that might work for your case -
Bronze (Landing + Raw): Set up one Lakehouse to store raw sensor data. Use /Files for raw snapshots or ingests, and /Tables for Delta if you want managed tables later. To bring data from PostgreSQL, use Pipelines -> Copy activity via the on-premises data gateway.
Just to let you know, Pipelines’ Copy activity is capable of writing directly to Lakehouse, whether you’re working with Files or Tables. It also offers support for schema mapping, different formats, and delta tables, so you don’t need to use a notebook for these tasks. Additionally, if you’re working with an on-premises gateway, please ensure that *.dfs.fabric.microsoft.com is included in the gateway allowlist to avoid any connectivity issues.You're correct in yuor findings that on‑prem gateway is supported by Pipelines and Dataflows Gen2, not Spark notebooks.Silver: Create one Lakehouse for cleansed and standardized Delta tables. Transform data using Spark notebooks once it’s in OneLake, as Spark works well for this purpose. Alternatively, you can use Dataflow Gen2 if you prefer a no-code ETL approach.
Gold: Use one Warehouse for curated, BI-ready models, and you may include a Semantic Model if needed. This setup aligns with Microsoft’s recommended medallion architecture for Fabric.
Operations metadata (logs, watermarks, config😞 Store this information outside of Gold. You can use either a small Fabric Warehouse or a separate SQL DB/Fabric SQL endpoint dedicated to Data Factory operations, both of which are common practices. Pipelines can query or update this metadata directly, without requiring notebooks.
Hope this helps - please appreciate by leaving a Kudos or accepting as a Solution.
- tayloramy
Hi holgergubbels,
When you say you cannot update the lakehouse without using a notebook what do you mean?
Copy activities and copy jobs can both target a lakehouse to write data.The typical approach is to use a pipeline with a copy activity or a copy job to ingest the bronze data into your lakehouse. Then from there you can do your transformations using notebooks and pyspark or sparksql, or you can do your transformations using power query in dataflow gen 2.
No, probably misleading (sorry for my english): I cannot update HighWatermark oder RunLogs in the datalake. But I think I just have to use SQL-Server for those informations. And using Stored PRocedures.
- tayloramy
Hi holgergubbels,
How are you trying to do that?
Pipelines have full functionality to update/write to Lakehouse tables.
- stoic-harsh
Hi holgergubbels,
You are asking the right questions. I think the core frustration here is not technical, but conceptual. You are approaching Fabric with a traditional ETL mindset (like in SSIS or custom C#/Java pipelines), while Fabric is deliberately designed to hide and manage large parts of that framework for you.
Once that mental shift is made, the architecture would become much simpler. Fabric assumes ownership of orchestration state (watermarks, run IDs, retries, logging) and discourages rebuilding SSIS-style control frameworks. This can feel opaque or restrictive, but the native mechanisms are reliable even if they are not explicitly visible.
For Ingestion/Bronze layer, use Copy Activity inside Pipelines:- Pipelines support the On-Prem Data Gateway
- Copy Activity provides native incremental loading
For Silver and Gold, use Spark notebooks for schema enforcement, metadata-driven transformations, deduplication, data quality, and aggregations.
I don't recall a ready-to-use end-to-end Fabric book yet. What could be pivotal is accepting that some things are intentionally opaque, and focusing engineering effort where Fabric gives flexibility.Hope with due course of time, you don't feel stuck anymore!☺️
v-prasareCommunity Support
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
stoic-harsh, tayloramy & deborshi_nag
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support- v-prasare
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support So, thanks for the answers. I had another topic in the data engineer forum and I came to cinculsion, that there is no "good" way. The way I do it it is always use pipelines to trigger jobs. The pipeline is able to read/insert/update information in a Warehouse (inserts for logStart, Update and Watermark for lokEnd and new TimeStamps). for ladning zone I use the copy action and stay in the pipeline functuionality using Sub-Pipelines (to be able to start copy actions in parallel). To bring everything in Bronze I use notebooks. Because it is complicated to qork with warehouse in spark I use again Pipelines for all meta-information and for logging. The notebooks are startet for each and every table in my metadata - not the best solution, because everytime a spark session is started and terminated - but the best in my architecural thinking. When it comes to bronze-> silver I think I will have more than one Notebook - depends on the domains of the tables and what to do. In gold I think I am goign to work with materialized views.
Hope this helps anyone.
