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');

(
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
)
;

(
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
)
;

AS
BEGIN

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 ,

where tablename1 = 'sales';

end

🔵 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.

Don't forget to like, comment, and subscribe for more great content!
▶️Data:

---------
▶️Follow me on:
---------
▶️My Other Videos:

-~-~~-~~~-~~-~-
Please watch: "Microsoft Power BI Tutorial For Beginners✨ | Power BI Full Course 2023 | Learn Power BI"
-~-~~-~~~-~~-~-

[MonkXD]

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

[arthurvanrijn]

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

[shafa]

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

[kranthikumar-ellz]

Thanks so much. Please make more videos on Dataflow gen2

[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.

[adilmajeed]

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.

[ashi]

Hi Amit - in this demo, you basically pulling all data from source and then doing lookup with help of fact table to identify which needs modification. can't you use proc from source which has data parameter to passed in so it only return records after last data sync?

[sanishthomas]

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

[dyxuujk]

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.

[sukumarm]

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

[Kiran-ebrm]

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

[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.

[ilseespinobarros]

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.

[huongbui]

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

[ljfkdgh]

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

[lakshmipriyakrishnan]

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?

[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

[mohammedmanna]

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