{"id":103947,"date":"2024-09-20T00:12:18","date_gmt":"2024-09-20T00:12:18","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=103947"},"modified":"2024-09-20T00:12:19","modified_gmt":"2024-09-20T00:12:19","slug":"infinity-notebook-loop-in-fabric-using-data-activator","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/infinity-notebook-loop-in-fabric-using-data-activator\/","title":{"rendered":"Infinity Notebook Loop in Fabric using Data Activator"},"content":{"rendered":"

Data Activator allow us to make an interesting workaround for infinity notebook executions.<\/p>\n

Infinity execution loop is interesting for constant data ingestion, but Fabric doesn’t allow an infinity loop inside a notebook, it ends up timing out and failing.<\/p>\n

Scheduling the notebook with very tight schedules is one possibility, but it’s not much trustworthy. You would need to know the precise run duration of the notebook and it’s not always the same. You end up with big intervals between the notebooks or with overlapping schedules. None of these options is good.<\/p>\n

There is an interesting alternative option we can use. Here comes the workaround.<\/p>\n

The Architecture of the workaround<\/h2>\n

The solution is to create a sign if the notebook is running or not. We can do this using a control table. “Control table” is a regular table in a lakehouse, but we will use with the purpose to control the execution flow of the notebook.<\/p>\n

This table needs two fields: The date and time of the record insertion and an integer value which we will set to 0 or 1.<\/p>\n

The notebook we want to run needs to make an insert at the beginning of the execution and at the end. At the beginning we insert a record with the value 1, to points the notebook is executing. At the end, we insert a record with value 0.<\/p>\n

We can use Data Activator to monitor this table. Every time a record with value 0 is inserted, we execute the notebook again. In this way, Data Activator becomes the responsible for the infinity loop.<\/p>\n

Ensuring the final Insert in the Table<\/h2>\n

It’s essential the final insert always executes at the end of the notebook, even if an error happens.<\/p>\n

If this final insert is not executed, the loop breaks. In this way, we need to use a try\/finally<\/em><\/strong> structure to ensure the final insert execution.<\/p>\n

try:\n     (main execution code)\nfinally:\n      (insert in control table)<\/pre>\n
The main execution code could be long. The best way to organize this execution is to break down the codes in functions. The main code structure ends up being simple: only the try\/finally<\/em><\/strong> exemplified above and calls to the functions which will make the actual work.<\/p>\n
This makes the core code quite simple.<\/p>\n
Data Activator delay and the Notebook code<\/h2>\n
Data Activator has a 5 minutes delay to execute a trigger. We need always to consider this delay.<\/p>\n
You can extend to the maximum the intervals between the breaks by increasing the execution time of the notebook. I mean that the notebook itself can and should contain a loop of the execution.<\/p>\n
The notebook loop can’t be infinity, but you can extend to the maximum possible without making the execution instable. Data Activator will ensure the notebook will be executed again after it finishes.<\/p>\n
For example, I implemented scenarios where the notebook runs for 1 hour and 50 minutes. When it stops, it depends on data activator to be triggered again. In this way, every 1 hour and 50 minutes a 5 minutes interval can happen.<\/p>\n
Configuring Data Activator<\/h2>\n
Data Activator requires one of the following to be configured:<\/p>\n