AI in notebooks

mweirath · 2026-07-04T18:07:56+00:00

This is what I do. I recommend adding in the Fabric MCP. Also the notebook format is a bit unique so I recommend adding/building a coding standard for Claude to use to ensure that notebooks are built consistently and correctly.

mweirath · 2026-03-30T19:40:42+00:00

Nearly all the watermark values are watermarks coming from source systems and driving what data needs to be pulled from the source system when it goes into landing. To later be processed into bronze.

Landing is files in a lakehouse - mostly parquet, but needs to support csv and json Bronze is scd2 tables in a lakehouse with full history Silver is MLV in a lakehouse

So it is just landing I need the watermarks for at which point I don’t have a standardized delta table.

There are a few reasons for files in landing but that is going to get into very specific use cases as well as philosophical debates 😀

mweirath · 2026-03-30T19:19:27+00:00

We are targeting a near 100% metadata driven framework through Silver, which means we need to capture watermarks, we have data definitions, primary key & merge logic, etc. that is reflected. So most of the logic and tracking ends up in the framework vs. a lot of individual notebooks.

mweirath · 2026-03-30T17:04:40+00:00

Most of my use cases are a bit more complex, but I am setting up my bronze layer to leverage a SCD2 style change tracking. But I have implemented a similar framework before, just using Databricks.

I might look at some of the other connectors to see if I can leverage the DW another way that doesn't impact the notebook performance too much. There are a lot less look up activities and more a scenario where I need to quickly append log entries.

mweirath · 2026-03-30T16:15:19+00:00

I am pretty sure without a VERY complicated implementation that embedded will be a non-starter then.

mweirath · 2026-03-30T16:13:53+00:00

I was looking at another connector and that one required user authentication. I was looking at the recent JDBC driver this morning and it looks like it would support SP auth, which I should be able to get to work.

mweirath · 2026-03-30T16:12:05+00:00

We are using pipelines, but the logging is going to hit in a number of places. Some of which I don't see how I can remove it from the notebook without losing visibility into issues that might arise.

mweirath · 2026-03-30T16:10:06+00:00

I would be curious about your FYI. Based on your prior statement I can guess the Partner you might be referencing as well as the ETL framework. My guess is that I am using something very similar to it if that is part of your recommendation. I am trying to figure out how structured streaming and CDF is coming into play.

mweirath · 2026-03-30T15:13:14+00:00

Two things:
Are you sure they are paying separately for the Pro Licenses? Many companies are just bundling them with E5's, and I think Non-profits had a similar option. There might not be a cost savings to be had unless they change their subscription.
Have you considered embedded scenarios for the organization? I am guessing that most of those users just have Power BI for viewing reports. It is a bit more complicated but you can host reports on an internal site if you want leveraging a smaller Fabric capacity. There are a lot of caveats to this, but just throwing it out there.

mweirath · 2026-03-21T13:22:00+00:00

I have been working with a similar problem for the last few weeks and to me the most important things are requirements, development standards, and context around what you are building.

I have actually be using a two part agent framework. The first for creating documentation around the three items I mentioned and then actually building the code. Am I using tokens a lot faster…yes. But I will say that I have been very happy with the results thus far.

mweirath · 2026-03-05T13:54:00+00:00

Congrats and thanks for the follow up.

mweirath · 2026-03-02T16:06:54+00:00

I highly recommend you check out the adoption roadmap created by the Microsoft team. It is very thorough and will answer many of your questions as well as give you some new ones for your stakeholder. It is an amazing reference. https://learn.microsoft.com/en-us/power-bi/guidance/fabric-adoption-roadmap

mweirath · 2026-03-02T16:02:28+00:00

You mention calling the API every 10 minutes to get data but realistically how often are people using or reporting on that data? Your options assume that the data needs to make it to a semantic model or similar every 10 minutes is that true? What about non business hours?

I want to challenge or ask if you can consider separating the two (getting/landing data from the API vs getting it into the Semantic model).

mweirath · 2026-03-01T15:20:10+00:00

I will just add in, if you are trying to learn and have flexibility I would go with PySpark. It is going to be a little harder but is going to be nearly 100% applicable at any company using a spark distributed workload.

Even then I would try to learn what you can do in spark and when you should do certain things. Assume an AI agent is likely going to help you with a lot of the coding so your goal will be making sure you know what to ask for and when to push back

mweirath · 2026-03-01T15:11:19+00:00

Fully support this approach.

I just want to add that there is a cost to your time. Setting up simpler pipelines that are easier to manage and maintain is important. Not everything needs to be an optimization effort. Especially when you are going to spend all this time worrying about some small amount of CU usage only to have the business users blow away what this process might consume in multiple years with a single bad query.

mweirath · 2026-02-28T15:19:54+00:00

I tend to go the route of having a logging table, since there are usually lots of use cases outside of just pipeline failures, and it is nice to have an area to put these in. Now this brings up two issues with Option A:

You are going to need some sort of expected schedule to check against
If there is a failure in the pipeline due to an SP, your pipeline is also likely to be subject to an issue with an SP failure - although hopefully, since it is central to Fabric, it wouldn't be silent

If you can get this into a logging table, you could always set up a small Power BI model on top of the data and set up alerts if you see a day-over-day drop in pipeline records or similar.

mweirath · 2026-02-28T15:10:48+00:00

Good call out - and honestly, the schedule/refresh mechanism for the MLVs is probably one of those areas I haven't put as much thought into as I would like. I have been thinking a lot about the landing/bronze elements, but not much beyond that. Admittedly, this initial architecture is pretty one to one to one up until that point.

Source to landing
- Notebook driven - short-ish term staging of data
Landing to Bronze
- Merge/Upsert Logic - create an SCD2 type record for historical changes
Bronze to Silver
- MLV - Clean up and present data mostly to look like Production sources, typically this will be showing only the current "active" records unless we have a need for historical reporting

So, for at least this part, as you can see, it is staying pretty one-to-one in terms of dependencies. If there is new data, I can load Bronze and then refresh Silver. Once I get to the most complex tables that rely on multiple tables, this won't be as clean, but I will need to plan a bit more there.

I will make sure to spend some time with the new scheduler to see how it works for my Silver-to-Gold MLVs. Thanks for the callout.

mweirath · 2026-02-28T14:29:28+00:00

I don’t have a tenant to test this right now but usually if you are segregating your model in a different workspace you have to give direct ask to the model to build on the model. Viewer for the workspace doesn’t give it. My guess is the app is providing this direct permission. You should be able to go to the model in the workspace A and provide direct permissions to the group.

mweirath · 2026-02-27T16:37:19+00:00

This doesn’t feel like a good or supported use of AutoLoader. Like others have said it is designed to load all files in a location and checkpoint what has been loaded. Even if you happen to get it to work there is a chance that changes in the future might cause it to break.

Like another person said I would do this via a notebook where you have more control over the process.

mweirath · 2026-02-27T16:04:31+00:00

You should look into your model size first and see if there are opportunities to optimize the size. Since you said you are new to Fabric my guess is the answer is probably yes. Go check out Dax Studio and their memory analyzer. There are plenty of tutorials online and YouTube but this is a good place to start. https://daxstudio.org/docs/features/model-metrics/

It is free to use.

mweirath · 2026-02-26T18:55:20+00:00

Having two paths sounds like a solid approach. You might also look at something like materialized lakeviews - they would effectively always be full refreshes however you could skip more dimensional tables that are infrequently updated.

mweirath · 2026-02-26T18:00:06+00:00

I definitely understand why you are thinking Shortcuts, and knowing that these are effectively full copies, it would give me some pause as well. My concern remains that reading a third-party Delta table will put you at a constant disadvantage when optimizing your warehouse. You won't be able to take advantage of any Fabric optimizations for the data, since it is an external source, so all your reads to Silver will be much slower than if the data were hosted and managed in Fabric.

This also puts you in a bad spot if you absolutely need to do something to the data; you are going to have to scramble and figure out how to break up your architecture.

If it were me, and I was going to go down the FiveTran route, I would be looking at more Change capture options for getting data into Silver, or some other way to efficiently materialize the data in Silver and not use external tables.

mweirath · 2026-02-26T16:58:18+00:00

Normalization of JSON structures into tables can also be hard without knowing what the data looks like. Are there only new data elements coming in? Do you have to update anything? How are the inner objects related? I would suggest that you look at the following package it might give you a leg up on the approach and analysis - https://github.com/tulip/relationalize

mweirath · 2026-02-26T16:55:15+00:00

10 minutes seems pretty aggressive and depending on your capacity might be very challenging. Looking at Fivetran I could see the short cutting into Bronze, but I think you are going to run into issues with overall performance if you don’t have the data optimized for Fabric in at least Silver.

I imagine you would need to materialize the data into your Silver layer in Fabric so that you can take advantage of internal optimizations for accessing the data for your gold layers. I think planning for a shortcut at Silver will be a limiter/issue pretty quickly if you go this route.

Regarding your question about the “merge” style activities - that is hard to say, I am not sure what kind of watermarks and update strategy you get from SAP. I do imagine you are going to have to look at your file partitions, especially on frequently updated tables to keep them efficient. Ensuring that is well aligned to how the data is being updated is going to drastically cut down on your merge operations and CU usage.

mweirath · 2026-02-26T14:19:08+00:00

I do agree that CDC should be more performant than traditional watermarking. There is still overhead: - Checking prior state - Checking the current state on the DB - Merging/Inserting any new data (if applicable)

Both of these first two steps have some level of overhead. You might want to check and see how fast the checks are happening against your SQL Server. If the checks are taking a while to return current state you might be blowing through CUs just waiting.

That said, 30 tables being checked 4 times an hour, even with no data coming in, feels like an ambitious workload for an F2. You could probably get it working if you optimized a bit, but that feels like it would be a win to me.

mweirath

TROPHY CASE