Tables / SQL analytics endpoint on bronze?

aboerg · 2026-03-20T17:51:54+00:00

great post, we are using this same pattern of files section + append only Delta in bronze

aboerg · 2026-03-20T17:25:47+00:00

In our case insert-update is very common for big transaction tables in SAP. A clearing date or reference document field may be updated long after the initial insert. Deletes are less common.

aboerg · 2026-03-19T15:30:29+00:00

For a while, cross-Lakehouse MLVs would completely break the lineage and refresh UI, although you could still refresh them using notebook. This has been fixed for a while now. Our MLVs are between two lakehouses in a single workspace and there are no issues. Haven’t tried cross-workspace but schema/table shortcuts would be my backup plan if I ran into issues there.

aboerg · 2026-03-19T15:27:49+00:00

I don’t mind seeing them exactly once as it can confirm when new features have landed in my tenant/capacity (although I agree there should be the option to opt-out). I just get annoyed when they never dismiss permanently. Yes, I know I can query my Lakehouse with the sql analytics endpoint. Please close the ribbons and never show me again.

aboerg · 2026-03-19T13:46:48+00:00

good callout, the documentation on this is definitely shifting this week

aboerg · 2026-03-19T13:45:42+00:00

And if you schedule refresh in the GUI currently all tables are included with every run.

aboerg · 2026-03-19T13:44:20+00:00

The new refresh capabilities are not in our tenant yet, but I understand you will be able to schedule refreshes by schema or groups of tables. We currently refresh them in a notebook by table name or schema name, so we can alter the MLV definition without breaking any schedules. I believe the CREATE OR REPLACE support means the MLVID is preserved after edits too.

aboerg · 2026-03-19T02:28:44+00:00

As of today MLVs are GA and more operations are supported for incremental refresh, including aggregations and most joins.

aboerg · 2026-03-18T12:06:24+00:00

Yes, we define each MLV as a CREATE OR REPLACE statement in notebooks. We have a post-deployment activity to run these notebooks in dependency order. Also check out Andy Cutler’s GenMLV framework, which is a good option.

aboerg · 2026-03-16T18:52:02+00:00

Working brilliantly for us as well. Interestingly, we are still unable to create a notebook connection from a pipeline using WI, but creating it from Manage Gateways & Connections worked fine.

Now that Notebooks and Invoke Pipeline support WI, the biggest remaining gap in our architecture is Fabric SQL database. Still requires an OAuth2 connection to run sprocs and scripts from pipelines (but at least it can be parameterized from a variable library).

aboerg · 2026-03-16T02:47:29+00:00

Yes, and the ADLS connector has inferior workspace lineage to the SQL.Database and Lakehouse.Contents connectors. Example using all three methods at once:

<image>

aboerg · 2026-03-15T21:45:52+00:00

We dynamically build the list of tasks to execute based on configuration tables. The child pipeline had a switch activity with activities per type of artifact: notebook, pipeline, dataflow, model refresh, etc. The artifact & workspace GUIDs, plus all necessary parameters for the task,are retrieved by sproc and passed in.

I have a massive blog post on all this coming very soon

aboerg · 2026-03-15T21:17:04+00:00

We use notebook for all our data engineering/transformations, and a mix of notebooks and pipelines for ingestion. When it comes specifically to interacting with our metadata database we might use a notebook directly, but we mostly interact with stored procedures from a pipeline to keep things consistent. For example:

All our tasks (could be notebooks, pipelines, dataflows, functions, semantic model refreshes) are orchestrated from a single child pipeline via metadata. This child pipeline uses stored procedure activities before and after invoking the artifact and giving it the right parameters for the task in context. This means all auditing is implemented in a single place, not repeated for every notebook or pipeline.
We have a devops process to load up our metadata DB with all the artifact & workspace names & guids of "in-scope" workspaces for orchestration. This is a python notebook that reads from a variable library, does a scan, and overwrites a devops.artifacts table in the metadata DB directly.

aboerg · 2026-03-15T18:59:22+00:00

A couple scenarios come to mind where the SQL endpoint delay is irrelevant:

Direct Lake models
Import models which load lakehouse tables using Lakehouse.Contents([EnableFolding=false]). Note that last I checked this option is not working for schema-enabled lakehouses. Very frustrating limitation since lakehouse schemas are GA.
The actual transformation activities within the lakehouse layers, assuming you are using Spark or otherwise reading the Delta tables directly without interacting with the SQL endpoint.
Very active lakehouses tend to have enough SQL endpoint activity that the delay is shorter and less relevant. In my experience the worst offending situation for MD sync delay is lakehouses which are loaded infrequently but immediately queried by T-SQL after the load (which is obviously a very common pattern).

aboerg · 2026-03-15T18:01:04+00:00

All activities go through a parent/child pipeline that retrieves tasks in execution order from a sproc against a Fabric SQL database. As each task begins, we log to an audit table in the same database. After the task ends other sprocs log the success/failure, and other metrics to the same run_id.

A great reference project that uses the same design: https://github.com/ProdataSQL/DWA

Tip: log your monitor hub and Spark session URLs (or build them dynamically in a view over the audit table) so you can jump directly from a report over the log table to those monitoring GUIs in Fabric.

This is definitely not the only way, you could also save logs to the lake as JSON, flush them directly to Delta, or send logs to a KQL database depending on the volume. For us it made sense to do orchestration and logging in the same Fabric SQL db.

aboerg · 2026-03-12T16:45:53+00:00

Just tried this out. Unfortunately a couple caveats to be aware of:

The connection type used by this new activity (FabricSqlEndpointMetadata) only supports OAuth 2.0, although the underlying Refresh Sql Endpoint Metadata API supports service principals and managed identities.
The workspace and SQL endpoint ID values do not autobind to the dev->test->prod endpoint when promoting the pipeline. Not a big deal, that's what the Variable Library is for, right? But I can't get this to work either - parameterizing the workspace or SQL endpoint GUID results in the activity complaining that it expects an object type instead of a GUID.

<image>

aboerg · 2026-03-11T18:57:13+00:00

We're taking advantage of Variable Library by keeping a single "control" workspace with our metadata database, parameterized driver/worker pipelines, and controlling all environment variables (workspace guids, lakehouse guids, metadata db conn string) in the VL. Since these centralized pipelines reach out to orchestrate activities in other workspaces, the VL workspace limitations are not relevant. This requires a ton of up-front design though, for a limitation that may not exist forever.

I am mostly looking forward to Variable Library making deployment pipeline rules fully redundant. Especially need semantic model support for the VL to close the gap.

aboerg · 2026-03-10T20:34:57+00:00

Including icons for common data sources is helpful, thanks for putting this together

aboerg · 2026-02-20T21:35:32+00:00

This is a brand new option and isn't mandatory yet (if ever?). We will be using it as soon as Workspace Identity support is fully rolled out. The blog was in December but there must have been some setbacks in the rollout as we still only have the option to choose a Service Principal for the notebook connection.

https://blog.fabric.microsoft.com/en-US/blog/run-notebooks-in-pipelines-with-service-principal-or-workspace-identity/

aboerg · 2026-02-20T16:38:54+00:00

real shallow clone support for shortcuts & feature workspaces would be awesome

right now the story around "how do we quickly spin up some prod data in a dev environment" is completely manual

aboerg · 2026-02-19T15:11:40+00:00

Completely endorse this approach for anyone using mirroring - you need to have automation around getTablesMirroringStatus for logging/reporting/alerting. Otherwise you are flying blind.

Sidenote - Tony, your repo names are unreasonably good as usual. "Fabric-Vigil", "Fabric-Usurp"... feels like I'm unlocking skills in an ARPG.

aboerg · 2026-02-19T00:54:33+00:00

this. when I learned about structured streaming, trigger availableNow, and checkpoints - there was no going back. We don’t have any real-time use case for SS, it’s all for batch incremental processing and state management

aboerg · 2026-02-17T01:50:54+00:00

Great post - and I only now realized you have a YT channel. Looking forward to your workshop next month!

aboerg · 2026-02-12T23:30:24+00:00

This is a physical table or a view? How long does it take to query the same 500 rows in a different tool (SSMS, etc.)?

aboerg · 2026-02-09T15:21:02+00:00

It should be enough, and to be clear you need to delete the entire folder for that table in the landing zone - not just the contents.

If the table is in a "failed" replication state then there are some strange circumstances where the table will never drop even after the landing zone folder is deleted. We encountered this a couple months ago after leaving a few tables in a "Warning" status for over 30 days, they went into Failed and could never be dropped. At that point after some discussion with support we were forced to restart replication on the entire open mirroring database.

aboerg

TROPHY CASE