Microsoft Fabric: Incremental ETL for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline

This video will discover the ways to run Incremental ETL(Data Load) using Dataflow Gen2, SQL Procedure, and Data Pipeline in Warehouse. Warehouse Support read and write SQL to SQL procedure has been used.
00:00 Overview of Incremental ETL
10:00 Create Warehouse and Load Data
26:00 What is the issue how to fix it
33:00 Load Data Using SQL procedure
36:30 Create Pipeline
30:00 Load Incremental Data
CREATE TABLE [dbo].[incr_info]
(
[tablename1] [varchar](4000) NULL,
[collectdate] [datetime2](6) NULL
)
insert into [dbo].[incr_info] values('sales', '2018-01-01');
CREATE TABLE dbo.sales_stg
(
order_no bigint,
item_id bigint,
sales_date datetime2(6),
customer_id bigint,
city_id bigint,
qty float,
price float,
cogs float,
discount_percent float
)
;
CREATE TABLE dbo.sales
(
order_no bigint,
item_id bigint,
sales_date datetime2(6),
customer_id bigint,
city_id bigint,
qty float,
price float,
cogs float,
discount_percent float
)
;
CREATE PROCEDURE dbo.loadSales
AS
BEGIN
insert into dbo.sales(
order_no ,
item_id ,
sales_date ,
customer_id ,
city_id ,
qty ,
price ,
cogs ,
discount_percent
)
select A.* from
(select order_no ,
item_id ,
sales_date ,
customer_id ,
city_id ,
qty ,
price ,
cogs ,
discount_percent from dbo.sales_stg ) A
left Join dbo.sales f on a.order_no = f.order_no
where f.order_no is null ;
update dbo.incr_info
set collectdate = (select max(sales_date ) from dbo.sales_stg)
where tablename1 = 'sales';
end
GIT File link - github.com/amitchandakpbi/pow...
All files: github.com/amitchandakpbi/
Power BI - Azure Free Tier, Create Azure Data Lake Gen 2 and connect that in Power BI: • Power BI: Connect with...
Get Azure SQL DB and Configure it on Power BI: • Abstract Thesis 93: Po...
Official Document: learn.microsoft.com/en-us/fab...
Video from GuyinaCube: • Microsoft Fabric Launc...
Buy a Microsoft Fabric subscription: learn.microsoft.com/en-us/fab...
SKU: learn.microsoft.com/en-us/fab...
Licenses: learn.microsoft.com/en-us/fab...
Enable Fabric: learn.microsoft.com/en-us/fab...
🔵 What is Microsoft Fabric
Microsoft Fabric is an all-in-one analytics solution for enterprises that covers everything from data movement to data science, Real-Time Analytics, and business intelligence. It offers a comprehensive suite of services, including data lake, data engineering, and data integration, all in one place.
With Fabric, you don't need to piece together different services from multiple vendors. Instead, you can enjoy a highly integrated, end-to-end, and easy-to-use product that is designed to simplify your analytics needs.
How to enable Fabric, find details - learn.microsoft.com/en-us/fab...
Microsoft Fabric licenses details -learn.microsoft.com/en-us/fab...
Don't forget to like, comment, and subscribe for more great content!
▶️Please Subscribe: / @amitchandak
▶️Data:
• Covid 19: covid19.who.int/WHO-COVID-19-...
• Get The data and Pbix files on Github: github.com/amitchandakpbi/pow...
• DropBox files: www.dropbox.com/sh/90ycq7hpmn...
---------
▶️Follow me on:
• Medium: / amitchandak
• Twitter: / amitchandak78
• LinkedIn: / amitchandak78
---------
▶️My Other Videos:
• Mastering Power BI: • Power BI for Beginners...
• Expertise Power BI: • Expertise Power BI- Ho...
~-~~-~~~-~~-~
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
• Microsoft Power BI Tut...
~-~~-~~~-~~-~

[@MonkXD]

My friend, i would like to thanks for all the effort and time that you've been dedicating for this series. Thanks!

[@AmitChandak]

Thanks 🙏 Hope you will like the full series Mastering Microsoft Fabric 35+ Videos: kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@arthurvanrijn68]

Thank you Amit, very nice done. Fabric is the future 😎

[@AmitChandak]

Thank you for your kind words! I completely agree with you. Microsoft Fabric is an incredible technology that holds immense potential for the future. Its innovative features and capabilities make it a game-changer in the industry. I'm excited to see how it will continue to evolve and shape the future of computing. Kudos to the Microsoft team for their outstanding work! Hope you will like the full series kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@shafa7668]

Fantastic. Exactly the vedio I needed. I highly appreciate you Amit for doing this.

[@AmitChandak]

I'm glad to hear that the video was exactly what you needed! Microsoft Fabric is truly an impressive technology, and I'm happy that the video provided you with the information you were looking for. Thank you for sharing your positive feedback! One more is coming soon - Incremental for Warehouse using only data pipeline

[@kranthikumar-el5lz]

Thanks so much. Please make more videos on Dataflow gen2

[@AmitChandak]

Sure. Hope you will like more video in the series Mastering Microsoft Fabric 40+ Videos: kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@user-dy8xu7uj8k]

Hi Amit, Good Morning!, I have to convert the existing SQL server stored procedure into fabric environment, In my stored procedures there are CURSOR commands but fabric doesn't support CURSOR commands, in this case how do I proceed, is there any alternative.

[@AmitChandak]

I have tried something very similar, TSQL loops learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-loops Search for Azure Synapse solution without using cursor

[@sanishthomas2858]

Good. But PostRSQL is on Premise right so how we have used the self hosted IR or Data ways to access it?

[@AmitChandak]

You can use on-premise gateway Microsoft Fabric: Load local SQL server data in Lakehouse using on-premise Gateway| Dataflow Gen2 - kzbin.info/www/bejne/pXapXp1-i9KpmtE Now Integrate On-Premise Data with Microsoft Fabric Using Data Pipelines: Gateway Update- kzbin.info/www/bejne/qYfIgamQhM-npdE

[@sukumarm5926]

Hi , Is it possible to use where clause in Copy data activity from ADF source as Microsoft Fabric , SQL Analytics Endpoint?

[@AmitChandak]

Please check the Incremental load video, I have given an example to use where the clause Microsoft Fabric: Incremental ETL for Warehouse using Data Pipeline, SQL Procedure: kzbin.info/www/bejne/p6SyepmKr892ibM Microsoft Fabric: Incremental ETL for Warehouse using Dataflow Gen 2, SQL Procedure, Data Pipeline: kzbin.info/www/bejne/o6Gpg6GQd72mrLM #microsoftfabric- How to use Append data option of Data Flow Gen 2 and Data Pipeline| Incremental kzbin.info/www/bejne/e2bEqYtsltp1hJY Microsoft Fabric: How to append only Incremental data using Data Pipeline in Lakehouse: kzbin.info/www/bejne/rqvLnpSHo6dlnK8

[@trivediasit]

Thanks for the detailed explanation here. What would happen in case there is no incremental data? For example no new sales orders over the weekend for namesake. It'll be great if you can share the process to handle that.

[@AmitChandak]

If there is no data, then add an additional condition , update only when date is not null or use coalesce. If the case is if there no new data coming in.

[@adilmajeed8439]

Thanks again for such a nice blog, is it possible for you to tag the numbers on the videos so that it will be easy to follow instead of looking at the publishibg date. Thanks for understanding.

[@AmitChandak]

Please follow the series link. I will see if, I can rename and add number kzbin.info/www/bejne/pl7ZYXxriJKsmNU

[@JoanSCruz]

Hi Amit, sorry if it looks as I'm not doing the proper effort but I quite don't get how you can use DML in the SQL endpoint, whenever I try to use insert, update, delete, etc, I get an error that those commands are not supported, I don't get what I'm doing wrong. On the other hand I really appreciate all your material.

[@AmitChandak]

Hi Joan, Hope you are using that on the Warehouse SQL endpoint, Not the Lakehouse. On Lakehouse you can run using Spark SQL - Managing Microsoft Fabric Lakehouse using Spark SQL: Data Manipulation Insert, Update, Delete, Alter- kzbin.info/www/bejne/hqbRfJWwac52e8k

[@JoanSCruz]

@@AmitChandak you're so kind, yes I kept watching your videos and found out the issue was exactly that, I was using lakehouse. Thanks, your videos are excellent

[@user-lj9fk8dg9h]

Sir, How can we build the JDBC/Pyodbc connection between Fabric Data warehouse and Fabric Notebook. I have been finding it since a long time, but un-successful

[@AmitChandak]

I have not tried on notebook, but I have tried from local Python to Warehouse. Check if that code can help Microsoft Fabric: Load local SQL server data in Warehouse using Python, Pandas, and sqlalchemy - kzbin.info/www/bejne/hmHSXpRjbdd5fpI

[@user-lj9fk8dg9h]

❤❤ It will definitely solve my scenario

[@user-lj9fk8dg9h]

Thank you so much sir 😊😊

[@Kiran-eb3rm]

Hi Amit, One question what if we update some records will they also come, or only newly added records will come. Thanks!

[@AmitChandak]

Depending on the type of incremental load you run, if you use the update date as the basis, it will bring in both new and previously updated data, which you will need to handle in the ETL process.

[@ilseespinobarros2418]

Hi Amit, do you know if it is possible to connect directly to a Pipeline and use the upsert option for an SQL server, I have tried it but I can not connect to the Server. I have connected succesfully using Data Flows, but Pipeline gives the following error: "The server was not found or was not accessible." This would make it so much easier for incremental ETL in a SQL Server but I think the option is still not available in Fabric.

[@AmitChandak]

Hi Ilse, It seems like you're encountering issues with connecting directly to an on-premise SQL Server using Pipeline and encountering the error "The server was not found or was not accessible." If you're using an on-premise SQL Server, it requires a gateway to establish a connection. Currently, Dataflow Gen 2 is the only option supporting on-premise sources. Unfortunately, the option to directly connect via Pipeline might not be available in Fabric at the moment. Thanks, Amit

[@lakshmipriyakrishnan2727]

I am trying to call a dataflow and execute it from a pipeline, but I get a 'User Configuration issue' error. Any idea how to fix this?

[@AmitChandak]

I am able to call dataflow gen2 from pipeline. Tested few mins back. Please provide more details. to reproduce.

[@huongbui4376]

I'm wondering, your PosgrestSQL database is on local. So how you can connect Fabric without using a gate way?

[@AmitChandak]

No, It is on cloud, I have used an IP.

[@huongbui4376]

@@AmitChandak I tried again by using PosgresSQL on cloud and successfully ran each step: Dataflow and Stored procedure. However, when I created a pipeline with these 2 steps like your video. I got fail when ran Dataflow in this pipeline. Could you please explain me the reasons? The details of the error: Error code: 20302 Failure type: User configuration issue Details: Microsoft.DataTransfer.Execution.Core.ExecutionException: Refresh Dataflow failed with status: NotFound message: Not Found

[@GudhawalaLadka]

Can this process handle millions of data. I have a table which has 13 million rows which keeps of increasing every month. I am not able to use incremental refresh on Dataflows because of the large data. I am being hopeful if it can work out

[@AmitChandak]

I successfully managed to load data of up to 4 million records without incremental load. Let me to share a method to achieve this. Create a table that has two dates: "Start Date" and "End Date." Customize the date range to correspond with either a year or a month, depending on your specific requirements. After each loading cycle, once the data processing concludes, adjust the "Start Date" and "End Date" to accommodate the subsequent year or month, as dictated by your business needs. This can be achieved by performing an update within a procedure. Execute this process multiple times or create a loop within a data pipeline to ensure a seamless and efficient data loading operation.

[@mohammedmanna3488]

Thank you Mr.Amit, Great job sir May be aske you a question .MS Fabric , Power bi and Ms Sql server in my laptop The data base which I worked with including more than 4000 tables Actually it is for Jdedwards Enterprisepoweri it is called JDEData and my server name called for example Localhost since last 2 weeks,i tried to connect to my local host but no way icant connect Notic sir i connect to power query by just giving the server name and database name and simple select statement for the table and the fileds which ineed by the way i never work with power bi service before can you please guide me to connect to the my local host throw what ever data flow G 2 or pipeline or what ever in Fabric lakehouse or any thing I am very sorry my comment is very tall but Itried hundreds time but i could not doing and also iam not network experience i again iam sory and thank you

[@AmitChandak]

To connect with Local, first you need install on-premise gateway and in dataflow you have use that gateway, while creating connection. Last time when I tried the flow on Fabric, it was giving some issue. And reported the same. I will check and get back on this.

[@AmitChandak]

I am able to complete flow using local SQL server and latest on-premise gateway -kzbin.info/www/bejne/pXapXp1-i9KpmtE