Pradeep O S on 19 Apr 2024 14:40:54
Write data directly into a data warehouse using the Fabric notebook, which utilizes Spark (let Fabric handle the staging process behind the scenes). This functionality resembles what is currently available in Synapse Analytics Workspace.
- Comments (7)
RE: Write Data into Data Warehouse from Fabric Notebook
This should be a key feature... our enterprise is planning to use a medallion architecture where bronze and silver ETL processes are done through notebooks and LH, however, we also want to leverage the use of a metadata config DW. We need a way to update from those ETL notebooks attributes on the config WH such as LastExecutionDT...
RE: Write Data into Data Warehouse from Fabric Notebook
Our metadata and logging tables are in a data warehouse, but we have some ELT jobs that run from notebooks. This functionality is key to allow us to log data into our data warehouse. It is also silly that I can read data from the same data warehouse using spark SQL, but I cannot insert records into it.On a side note, this ideas forum sucks. I tried to provide more details and code snippets, but I get the error message "We have encounter some malicious input. Please remove that and try again." Thanks Microsoft, your error message isn't even correct.
RE: Write Data into Data Warehouse from Fabric Notebook
This is a fundamental feature for transferring incremental loads from the (LH) to the (WH) tables.Our clients primarily use a bronze-to-silver architecture, and we cannot afford to overwrite WH tables every time new data arrives, as it is neither efficient nor scalable. Instead, incremental data loading with upsert capabilities is crucial to maintaining data integrity while minimizing resource usage.This functionality is readily available in Azure Synapse Analytics, and having it in Microsoft Fabric is essential to meet our clients' expectations. Without it, we risk losing their confidence in moving to Fabric, as the ability to handle incremental updates efficiently is a key requirement in modern data architectures. Implementing this feature will ensure we can offer a smooth transition for clients and provide the performance and flexibility they need for their data workloads.
RE: Write Data into Data Warehouse from Fabric Notebook
Why would you need to load the data into the Warehouse via Notebook? You can write the data to the Lakehouse and directly query it from the WarehouseCREATE TABLE [research_warehouse].[dbo].[cases_by_continent]ASSELECT FROM [cases_lakehouse].[dbo].[bing_covid-19_data] caseshttps://learn.microsoft.com/en-us/fabric/data-warehouse/ingest-data-tsql#ingesting-data-from-tables-on-different-warehouses-and-lakehouses
RE: Write Data into Data Warehouse from Fabric Notebook
Please either allow through Sql Connection stringimport com.microsoft.spark.sqlanalyticsfrom com.microsoft.spark.sqlanalytics.Constants import Constantsdf.write\ .option(Constants.SERVER, "")\ .mode("overwrite")\ .synapsesql("")or directly using saveAsTable methoddf.write.format("delta").option('delta.columnMapping.mode' , 'name').mode("overwrite")\.saveAsTable("TABLE_NAME", path="abfss://WORKSPACE_NAME@onelake.dfs.fabric.microsoft.com/WAREHOUSE_NAME/dbo/Tables/TABLE_NAME")
RE: Write Data into Data Warehouse from Fabric Notebook
This feature would help us convincing customer to transition over to Microsoft Fabric
RE: Write Data into Data Warehouse from Fabric Notebook
Agreed - this would be very useful for companies looking to implement a LH-LH-DWH medallion architecture, writing from Silver LH to Gold DWH. Also this limitation damages the overarching narrative around OneLake: to have flexibility behind reading and writing data between the different engines.