In this blog post I am going to show you how to use a Fabric Python runtime notebook (This is the notebook which only uses Pure Python functions and consumes significantly lower Capacity Units (CUs)).

The pattern is how to get new data and merge it into an existing Lakehouse table. This ensures that if the notebook is run again data will not be duplicated.

Why I am sharing this is I have found that there is not a lot of useful information about how to use a Python notebook to write to a lakehouse table easily. And then also how to use a Merge statement making it easier to insert or update your lakehouse tables. This simplifies the ingestion process, runs faster and consumes the least amount of CUs

When to use this pattern

This pattern is useful when:

  • You are loading small-to-medium sized data from an API or file.
  • You want to incrementally insert and update rows in a Lakehouse table.
  • You do not need distributed Spark processing.
  • You want to reduce notebook startup time and CU consumption.
  • You want a notebook that is easier to move between workspaces using parameters instead of a hard-coded Lakehouse attachment.

Working Example

Before running this notebook, you will need:

  • A Microsoft Fabric workspace.
  • A Lakehouse where the target Delta table will be stored.
  • Permission to read from and write to the Lakehouse.
  • A Python notebook, not a PySpark notebook.
  • The required Python libraries installed or available in the runtime.
  • An API or source system that returns data in a tabular structure.

In this example I am getting data from a holidays API.

https://date.nager.at/api/v4/Holidays/

I am then upserting or merging the data to ensure that new rows are added and existing rows are updated.

I created a composite key column to identify a single column to use for the merge. Quite often a data source will have a key that can be used. In my working example the composite key I have created is a combination of the date and subdivision

I also did not add a data source to my notebook as this makes it much easier to move between workspaces or to use CI/CD

NOTE: I am using Python 3.12 for this example to work successfully.

Cell 1

In the first cell what I am doing is to put in all the parameters which will be used multiple times in the cells below.

Below are the line items to take a note of below.

Line 9

  • Here I am getting the workspace GUID where I have my Lakehouse that I want to either read a table from or write data into a table

Line 12

  • This is the Lakehouse GUID

Line 22/23

  • This is the column that will be used to get the column which will be the dates to find the last date as a watermark to complete the next data load.

The rest of the parameters should hopefully be understandable.

Cell 2

In this cell all that is happening is I am getting the last date I had data loaded from my table.

The code cell does have more code, what I wanted to highlight here is that it is using my parameters to get the last date loading date.

As shown below this is what it looks like once run

Cell 3

Here I am converting the max dates to the correct date format for my API

Cell 4

This is the final code cell which gets the data from the API and then completes the upsert/merge

This function below is creating the API Call

Line 26

  • This is where it is getting the last loaded date and converting it to a year

Line 47/49

  • This is where it is creating the composite key

This function below is where it will do the upsert/merge or create the table if it does not exist.

Lines 89/96

  • This is where it is doing the merge/upsert based off the parameter “merge_key” from Cell 1
  • In this code it is also showing the number of rows received and what happens in the merge.

The final step below is where it is then calling the functions to run their steps.

Line 144

  • This is where it is calling the API and putting it into “df”

Lines 148/151

  • This is where it is taking the data frame and merging the data into my Lakehouse table.

As shown below I can see what was inserted or updated in the last run

How to use this notebook with your data/api

To use this notebook all that you would need to do is to update your function where you are loading the data.

Before running the notebook, update the workspace ID, Lakehouse ID, table name, watermark column, merge key, and API function to match your own environment.

Just make sure that it is returned with the name of “df”

Then make sure if you create a new function to update Line 144 to your function name and the rest should work with the code provided.

Summary

Thanks for reading I hope that you will find this pattern useful and something you can use within your Fabric loading process.

Here is a copy of the notebook: Blog – Python Merge to Lakehouse.ipynb

Finally, I did use an LLM to help me write and update the notebook code.

Any comments or questions are more than welcome 😊