On-Premise Python Code for Delta load from On-Premise SQL Server Data to Microsoft Fabric Warehouse

This blog post includes scripts used in the Microsoft Fabric warehouse to create tables and load data. The data is pushed using an on-premise Python script with MFA authentication. The Python code for the data push is provided in the blog as well.

You can utilize the provided CSV file if you prefer to load the data into a local SQL Server.

https://github.com/amitchandakpbi/powerbi/raw/main/csv/retailData_fabric.csv

The video: Microsoft Fabric: Load incremental data from local SQL server to Warehouse using on-premise Python

The warehouse SQL code

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

delete from dbo.sales_stg;


end

The Python code

import pandas as pd
import sqlalchemy as sa


# Load Table for first time 
server = '<URL>'
database = 'Incr01'
driver='ODBC Driver 17 for SQL Server'
user= '<User>' # use %40 for @
pass1='<Pass>'

# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{user}:{pass1}@{server}/{database}?driver={driver}&Trusted_Connection=no&Authentication=ActiveDirectoryInteractive'
print(connection_string)
engine = sa.create_engine(connection_string, echo=True, fast_executemany=True)

sql_query = "SELECT coalesce(max(collectdate) ,'2018-01-01') _date FROM dbo.incr_info"
df = pd.read_sql(sql_query, engine)

max_date = df['_date'].iloc[0]
print(max_date)

# Create the SQL query with the max_date value
query1 = f"SELECT * FROM dbo.sales_fact WHERE sales_date > '{max_date}' "

server1 = 'localhost\\SQLEXPRESS'
database1 = 'sales'

# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{server1}/{database1}?driver=SQL+Server&Trusted_Connection=yes'

# Create an engine using the connection string
engine1 = sa.create_engine(connection_string)

# Run a SQL query to get a Pandas DataFrame

df = pd.read_sql(query1, engine1)

# Print the DataFrame
#print(df)


server = '<host>'
database = 'Incr01'
driver='ODBC Driver 17 for SQL Server'
user= '<User>'
pass1='<Pass>'

# Create a connection string for SQL Server using Windows authentication
connection_string = f'mssql+pyodbc://{user}:{pass1}@{server}/{database}?driver={driver}&Trusted_Connection=no&Authentication=ActiveDirectoryInteractive'
print(connection_string)
# Create an engine using the connection string
engine = sa.create_engine(connection_string, echo=False, connect_args={'autocommit': True}, fast_executemany=True)

# Run a SQL query to get a Pandas DataFrame
#sql_query = "SELECT TOP(10) * FROM [dbo].[Customer]"
df.to_sql(name='sales_stg', con=engine, if_exists='append',dtype={
    'Order_No': sa.Integer() ,
 'Item_ID': sa.Integer(),
 'Sales_Date': sa.String(50) ,
 'Customer_Id': sa.Integer(),
 'City_Id': sa.Integer() ,
 'Qty': sa.Integer(),
    'Price': sa.Float(),
 'COGS': sa.Float() ,
 'Discount_Percent': sa.Float()}, index = False, chunksize=50, method='multi')

with engine.connect() as conn:
    conn.execute("EXEC dbo.loadSales")

Hope you are able to use this code with the video.

More on Microsoft Fabric:

Comprehensive Free Course for Your Team to Learn Microsoft Power BI, SQL, and Data Factory

Get Fabricated- Learn Microsoft Fabric, Power BI, SQL, Power Query, DAX, Dataflow Gen2, Data…

Microsoft Fabric: A Brief Introduction

How to enable Microsoft Fabric; on Power BI Service| Start the Fabric (Preview) trial

Microsoft Fabric-What it is, Why Use, Who Should, When and How to use

Microsoft Fabric: Various Components| Microsoft Fabric Create Workspace, Lakehouse, Warehouse

Microsoft Fabric: How to load data in Lakehouse using Dataflow Gen2

Microsoft Fabric: How to load data in Warehouse using Dataflow Gen2

Microsoft Fabric: How to load data in Warehouse using Data Pipeline | End-to-End Scenario

Microsoft Fabric: How to load data in Lakehouse using Spark; Python/Notebook

Microsoft Fabric: Integrated Machine Learning- k-Means clustering

Microsoft Fabric: Create a Date table in Dataflow Gen2, and use in Lakehouse and Warehouse| Time Intelligence with Power BI

Microsoft Fabric: How to load data in Lakehouse using Data Pipeline | End-to-End Scenario

Microsoft Fabric: What are the options to load local files in Lakehouse

Microsoft Fabric: Shortcut with Fabric and for Azure Data Lake | File vs Table Shortcut

Microsoft Fabric: Import Azure SQL Data to Warehouse | Multiple tables using Pipeline

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

Microsoft Fabric: How about incremental without Dataflow, Only using pipeline and SQL procedure for Microsoft Fabric Warehouse

Microsoft Fabric: Load local SQL server data in Lakehouse using on-premise Gateway and Dataflow Gen2

Microsoft Fabric: Dataflow Gen2: Native warehouse Destination- Power Query Transformation — Pivot, Unpivot, Transpose Data

Microsoft Fabric: Load Azure SQL Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2

Microsoft Fabric: Load Snowflake DB Data to Lakehouse & Warehouse simultaneously with Dataflow Gen2

Microsoft Fabric: How to append only Incremental data using Data Pipeline in Lakehouse

Introduction to Microsoft Fabric in Hindi | What it is, Why to Use, Who Should, When and How to use

Microsoft Fabric in Hindi Part 2: Create Workspace, Lakehouse, Warehouse

Microsoft Fabric in Hindi Part 3: How to load data in Lakehouse using Data Pipeline | End to End Scenario

Microsoft Fabric in Hindi Part 4: How to load data in Lakehouse using Dataflow Gen 2 | End to End Scenario

Microsoft Fabric in Hindi Part 5: How to load data in Warehouse using Dataflow Gen 2 | End to End Scenario

Microsoft Fabric in Hindi Part 6: How to load data in Warehouse using Data Pipeline | End to End Scenario

To take your data journey to the next level, I’ll be providing more examples in my upcoming blogs and videos.

Complete Power BI in one Video — 11 Hours

Click here to access all my blogs and videos in a jiffy via an exclusive glossary using Power BI. Please like, share, and comment on these blogs. I would appreciate your suggestions for improvement, challenges, and suggestions for topics so that I can explore these in more depth.

In addition, I have over 500 videos on my YouTube channel that are related to Power BI, Tableau, and Incorta. With the help of these videos, you will learn hundreds of DAX and Power Query functions, in addition to hundreds of Power BI, use cases that will assist you in becoming an expert in Power BI. Make sure you subscribe, like, and share it with your friends.

Master Power BI
Expertise in Power BI
Power BI For Tableau User
DAX for SQL Users
Learn SQL