I Let Fabric's New Data Engineering Agent Run My ETL: Here's What Happened
Last week I handed Microsoft Fabric's new Data Engineering Agent a messy ETL job that took me 11 hours to build by hand last quarter. It had a working, validated pipeline in front of me in under an hour. I still changed 3 of its 9 steps before I'd let it near anything important. That is the short version. The long version is more interesting, because the places where it went wrong tell you exactly how to use it well. Some context first. At FabCon Europe 2026 in Barcelona (September 28 to October 1), Microsoft announced the Data Engineering Agent in preview. It can plan and run migrations, ETL and performance work under guardrails that engineers define. It builds on technology from Osmos, the agentic data engineering startup Microsoft acquired in January 2026, and was demoed on stage as "Project Osmos". Copilot has been helping us write code for a while now. This is a different idea. You describe the outcome, and the agent does long-running, multi-step work on its own while you supervise. I wanted to know whether that holds up on a real, slightly ugly dataset rather than a keynote demo. So I tried it. What the Data Engineering Agent actually is The agent is an autonomous worker inside your Fabric workspace, not a chat assistant that suggests code. You give it a goal, and it does the multi-step work itself. Its roots are in Osmos, a Seattle startup whose "AI Data Engineer" already generated production-grade PySpark notebooks inside Fabric. Microsoft bought the company in January 2026 and folded the team into Fabric engineering. Based on Microsoft's FabCon session on Project Osmos, a typical run looks like this: Read the request. You describe the outcome, even loosely ("land the orders CSVs in a clean silver table"). Inspect the workspace. It looks at your existing code, Lakehouses and data layout before writing anything. Plan. It breaks the job into steps and can hand parts to sub-agents for long-running work. Build. It creates and updates notebooks and other Fabric items. Validate. It checks its own output so you can trust what was built. Hand back to you. A human reviews and approves. Microsoft is explicit that supervision stays part of the process. The headline use cases are migrations, ETL and performance tuning. I chose ETL, because that is where most of us spend our week. The test: a real pipeline, not a demo I picked a job I had already built by hand, so I could judge the agent's work line by line against my own. The data is sales orders from a mid-sized retail client . Every day their order system drops a CSV export into a Lakehouse folder. The business wants a clean orders table for analysts and a daily sales summary for a Power BI report. Item My setup Source data 14 daily CSV exports, 18.4 million rows, about 2.1 GB, in Files/raw/orders/ Target silver_orders (cleaned Delta table) and gold_daily_sales (aggregate by day, store and category) Known traps Duplicate order IDs from system retries, two date formats, a currency column with nulls, cancelled orders flagged as negative quantities Capacity F64 trial capacity, sandbox workspace Hand-built baseline About 11 hours across two days, including testing The exact prompt I gave it: Ingest the daily order CSVs in Files/raw/orders into a cleaned silver Delta table called silver_orders. Remove duplicates, standardise dates and currency, and exclude cancelled orders. Then build gold_daily_sales with total revenue, order count and average basket size by day, store and category. The pipeline must run incrementally each day. I deliberately kept the prompt a little vague. I didn't say which date formats exist, how cancellations are encoded, or which column identifies a duplicate. In real life, requirements rarely arrive as a perfect spec. I wanted to see whether the agent would investigate, ask, or just guess. What happened, step by step 1. It read the workspace before writing anything The first thing I noticed: it didn't start coding. For the first four minutes it profiled the data. It sampled each CSV, inferred the schema and looked at my existing notebooks in the workspace. It came back with a short summary of what it found. Two findings impressed me: It spotted both date formats (2026-09-14 and 14/09/2026) and flagged that files from 9 September onwards switched format. It noticed my existing notebooks used a nb_<layer>_<entity> naming pattern and said it would follow it. It missed one thing, which matters later: it reported quantity as "integer, some negative values" without asking what negative quantities meant. 2. The plan it proposed Next it produced a 9-step plan and stopped for my approval: Create a staging notebook to read all CSVs with an explicit schema Normalise both date formats to a single order_date column Fill null currency values from the store's default currency Deduplicate on order_id, keeping the latest record Remove cancelled orders Write silver_orders as a Delta table, partitioned by order_date Build gold_daily_sales with the three requested measures Add incremental logic based on the source file date Run validation checks and produce a summary report This is a sensible plan. It is roughly what I wrote on a whiteboard last quarter. But reading it closely, I changed three things before approving: Step 4: it planned to overwrite silver_orders on each run. I changed it to a MERGE on order_id, because late-arriving corrections are common in this data. Step 5: it planned to remove cancellations using a status column. That column exists, but this client never fills it. Cancellations show up as negative quantities. I told it so. Step 8: it wanted to detect new data by file modified time. I asked it to use the date in the file name instead, which is more reliable when files are re-uploaded. It accepted all three changes and rewrote the plan in a few seconds. 3. The build Once approved, it worked for 38 minutes without needing me. It created three notebooks (nb_stg_orders, nb_silver_orders, nb_gold_daily_sales) and a small pipeline to run them in order. The code was clean and readable. Here is its date handling, which is close to what I would write myself: from pyspark.sql import functions as F df = df.withColumn( "order_date", F.coalesce( F.to_date("order_date_raw", "yyyy-MM-dd"), F.to_date("order_date_raw", "dd/MM/yyyy") ) ) And the merge into silver, after my change in the plan: from delta.tables import DeltaTable silver = DeltaTable.forName(spark, "silver_orders") (silver.alias("t") .merge(updates.alias("s"), "t.order_id = s.order_id") .whenMatchedUpdateAll() .whenNotMatchedInsertAll() .execute()) It asked me one question mid-way: whether average basket size should be revenue per order or items per order. That was exactly the right question, and one I had to ask the client myself last time. 4. Self-validation When the build finished, it ran its own checks and produced a short report: Row counts reconciled from raw to staging to silver, with each dropped row explained (duplicates, cancellations) No nulls left in order_date, currency or store_id No duplicate order_id values in silver_orders Daily totals in gold_daily_sales matched a direct aggregate of silver_orders One check failed on the first pass. 41,200 rows had a null order_date after parsing. It traced the cause itself: a third date format (14-Sep-2026) appeared in two of the older files, which its initial sampling had missed. It added a third pattern to the coalesce, re-ran, and the check passed. That moment sold me on the validation step. A human could easily miss 0.2% of rows quietly disappearing. 5. My review against my own version I compared its output with the pipeline I built by hand last quarter, running both on the same 14 files. Measure Agent Me, by hand Time to working pipeline 52 minutes (incl. my review) About 11 hours Plan steps I changed 3 of 9 Not applicable Data issues found 4 of 4, after one self-correction 4 of 4 Full run time on 18.4M rows 6 min 40 s 5 min 55 s Silver row count 17,962,118 17,962,118 The row counts matched exactly. My hand-built version ran about 45 seconds faster, mostly because I had tuned partitioning. More on that below. Would I merge its code as-is? After my three plan changes, yes, with one small performance fix. Guardrails: the part you shouldn't skip An agent that can create and change items in your workspace needs limits before it needs a prompt. Microsoft designed this agent around guardrails that engineers define, and setting them up was the best 15 minutes of the whole test. What I set up before the run: A sandbox workspace. It's a preview feature, so it ran on a copy of the data, nowhere near production. Scoped data. It could read Files/raw/orders/ and write only to new tables. My existing tables were off limits. Approval points. It had to stop for my approval after the plan, and again before the first write to any Delta table. Git in the loop. The workspace was connected to a Git repo, so every notebook it created or changed landed as a diff I could review line by line. In practice, it never tried to step outside these limits. The approval after the plan turned out to be the most valuable one: that is where I caught all three of my changes. If you set only one guardrail, make it that one. Where it struggled No agent review is credible without the failures, so here are the three that mattered. It assumed instead of asking about business rules. It saw negative quantities and planned to use an empty status column for cancellations. Had I approved the plan without reading it, about 310,000 cancelled orders would have flowed into the gold table and inflated revenue. It asked a good question about basket size, but not this one. Its sampling missed a rare date format. The third format appeared in only two files. Its validation caught the problem, but only after the first run. On a much larger dataset, that first run costs real capacity. Correct, but not tuned. It partitioned silver_orders by order_date, which produced many small files for this data volume. Switching to monthly partitions and running OPTIMIZE closed most of the 45-second gap with my version. When I asked it to "make the silver write faster", it suggested the same fix, but it didn't do it unprompted. Three caveats worth knowing before you try it: It's a preview. Features, behaviour and pricing can change before general availability. Don't build production processes on it yet. Watch your capacity. Long-running agent work consumes capacity units on top of the Spark jobs themselves. Check the Capacity Metrics app after your first run so you know what a typical job costs. It doesn't know your business. It can read your schema, not your meeting notes. Every rule I wrote into the prompt was handled correctly. Every rule I left out was a guess. The verdict The Data Engineering Agent is like a fast, tireless junior engineer: excellent at the mechanics, careful about checking its own work, and still in need of a senior reviewing the plan. What I'd tell my team after this test: Spend your time on the prompt and the plan, not the code. Every problem I found was visible in the plan before a single line ran. Write business rules down explicitly. Cancellations, duplicates, currency defaults. If it's tribal knowledge, the agent will guess. Trust the validation step, but read its report. It caught a data loss bug I might have missed myself. Tune afterwards. Treat its first version as correct, then ask it for performance improvements. Try it now if you have a sandbox workspace, a backlog of repetitive ingestion or migration jobs, and someone who can review PySpark. Wait for GA if you need it in production, your pipelines are full of undocumented business logic, or your capacity budget is tight. The bigger shift is in our role. Going from 11 hours to under an hour didn't remove me from the work. It moved my effort from typing code to writing clear requirements, setting guardrails and reviewing output. That is a skill worth building now, preview or not. Have you tried the Data Engineering Agent yet? Tell me in the comments what you threw at it, and where it surprised you. Sources FabCon Europe 2026 announcements (Kanerika) From Prompt to Finished Project: Introducing Project Osmos (FabCon Europe 2026 session) Microsoft Acquires Osmos (Redmond Magazine) Fabric January 2026 Feature Summary (Microsoft Fabric Blog)Microsoft Fabric Data Agent in M365
In this short demo, I tested how a Microsoft Fabric Data Agent can be used through Microsoft Teams and Microsoft 365. I also showed how prompt wording can affect an answer when querying Green Trip data. Key Takeaways A Data Agent can be published for use in Microsoft 365. I accessed the published agent through Microsoft Teams. The sample agent used Green Trip data. An initial passenger question returned an answer I identified as incorrect. Refining the prompt produced the expected response in this scenario. Chat history remained available through the app. Microsoft Fabric Data Agent publishing to Microsoft 365 I started with a Microsoft Fabric Data Agent that I had created earlier. When publishing it, I selected the option to publish it to Microsoft 365 as well. After publishing, I could view the agent details, including its URL and MCP information. This makes the Data Agent available beyond its original Fabric context. Next, I moved to Microsoft Teams. I could open the agent through the app, and I also showed an option to access it through the Microsoft 365 corporate area in Teams. Opening the sample agent in Teams The agent in my demo was named Sample Data Agent and was fed by Green Trip data. Once opened in Teams, I could ask questions directly in the chat interface. This demonstrates a practical Microsoft Fabric Data Agent in Teams workflow: publish the agent, open it through the Microsoft 365-connected experience, and interact with the available data through chat. Microsoft Fabric Data Agent prompt refinement in Teams I first asked for the highest number of passengers. The agent returned 9 passengers, while also showing the Fabric MCP activity behind the response. However, I knew that this result did not answer the question I intended to ask. The issue was the wording of my prompt, not a confirmed issue with the integration itself. I then refined the question to ask: Which year has the highest total passenger count? The Data Agent MCP ran again in the background and returned the response I expected. This quick test shows why prompt specificity matters. Asking about the “highest number of passengers” can produce a different result than asking for the year with the highest total passenger count. What this demo did not test This was a short, single-scenario demonstration. I did not test multiple data sources, alternative permissions, or a wider set of questions. The specific year and passenger total returned by the refined prompt were not stated in the demo. Can Microsoft Fabric Data Agent chat history remain available? Yes. In the app experience I showed, the chat history remained available. This allows me to revisit previous Data Agent conversations from the interface. Frequently Asked Questions Can I use a Microsoft Fabric Data Agent in Microsoft Teams? In this demo, yes. I published the Data Agent to Microsoft 365 and opened it through Microsoft Teams. Why did the first passenger question return an incorrect result? I identified that the first question was not specific enough for the answer I wanted. After I changed the prompt to ask about the year with the highest total passenger count, the agent returned the expected response. Conclusion This Microsoft Fabric Data Agent demo showed a simple path from publishing to Microsoft 365 through asking questions in Teams. I also observed that clearer prompts can help the agent return a more relevant response for the intended question. If you use Data Agents with Microsoft 365, consider testing your prompts carefully and reviewing your chat history as you work.CDC vs Watermark: Choosing an Incremental Load Strategy in Microsoft Fabric
Why incremental loading matters If your source deletes rows or you need history, use CDC; if you only need inserts and updates and have a reliable "last modified" column, a watermark is simpler and works almost everywhere. That is the short answer. The rest of this post explains why. Reloading a whole table every night is fine at 50,000 rows. At 500 million it burns capacity units, hammers the source system, and stretches your refresh window. Incremental loading fixes this by moving only what changed since the last run. In Microsoft Fabric, the main tool for this is the Copy job in Data Factory. It supports both change detection approaches side by side, and can pick one per table. Knowing how each works helps you choose well and avoid silent data gaps. The watermark pattern A watermark load copies only rows whose "incremental column" value is higher than the highest value seen in the last successful run. The first run copies everything; each later run picks up where the last one stopped. The query behind it is simple: SELECT * FROM dbo.Orders WHERE ModifiedAt > @LastWatermark AND ModifiedAt <= @CurrentWatermark; In a classic pipeline you build this yourself: a Lookup for the old watermark, a Copy activity, and a step that saves the new value to a control table. Copy job removes that plumbing. It stores the watermark state for you, and a failed run resumes from the last successful one without data loss (Microsoft Learn). Copy job accepts these column types as a watermark: ROWVERSION – changes automatically on every insert or update; the most reliable choice on SQL Server-family sources. Datetime – columns such as ModifiedAt or LastUpdatedDatetime. Date – date-only columns; Copy job re-reads the last day to avoid gaps. String – values that can be read as datetimes. Integer – an increasing number. A column of another type can still work if you cast it to a supported type in the source query, as long as its values compare in order. File sources use the file's last-modified time as the watermark. The catch: a watermark only sees rows that still exist and whose column actually moved. Deleted rows are invisible, and rows with a NULL watermark are skipped on every incremental run after the first. Change data capture (CDC) CDC reads the source database's own change log instead of querying the table, so it captures inserts, updates and deletes exactly as they happened. You don't pick an incremental column; the database tells Fabric what changed. When a source table has CDC enabled, Copy job detects it, does an initial full load, and then replicates only changes on later runs (Microsoft Learn). Sources that support CDC in Copy job include Azure SQL Database, Azure SQL Managed Instance, on-premises SQL Server, Oracle, Snowflake, Google BigQuery, SAP Datasphere Outbound, and Fabric Lakehouse tables (via Delta Change Data Feed). You then choose how changes land in the destination: SCD Type 1 (Merge) – the default. The destination mirrors the current source: updates overwrite, deletes remove the row. SCD Type 2 – in preview. Copy job keeps every version, adding Valid_From, Valid_To and Is_Current columns. Deletes become soft deletes, so you keep a full audit trail with no custom code. Oracle sources don't support it yet. Copy job isn't the only CDC route in Fabric. Mirroring keeps a near real-time replica of an operational database in OneLake with almost no setup, and Eventstreams offers CDC connectors for streaming changes into Real-Time Intelligence. Microsoft's data movement decision guide compares the three. Side by side The deciding row is usually deletes: only CDC sees them. Consideration Watermark CDC Source prerequisite A column that always increases on insert or update CDC enabled on the source and supported by the connector Inserts Yes Yes Updates Only if the watermark column changes Yes Deletes No Yes History (SCD Type 2) No, unless you build it Built in (preview) Write methods Append or Merge Merge or SCD Type 2 Load on source Range scan on the watermark column each run Reads the change log; lighter on busy tables Setup effort Low; no DBA changes Needs CDC enabled, permissions, log retention Works with files Yes (last-modified time) No Source coverage Most database connectors A defined list of connectors Source: Incremental copy in Copy job, Microsoft Learn. Which one should you use? Three questions settle it for almost every table. If CDC is the right answer but you can't enable it on the source, fall back to a watermark and catch deletes with soft-delete flags or a periodic key comparison. Tips and pitfalls For watermark loads Prefer ROWVERSION over app timestamps. An application can forget to update ModifiedAt; the database never forgets a rowversion. Watch for NULLs. Rows inserted later with a NULL watermark are never picked up. Make the column NOT NULL with a default. Catch deletes another way. Use soft deletes (an IsDeleted flag that also bumps the watermark), or run a periodic key comparison against the source. Use Merge, not Append, for updates. Append writes a second copy of every updated row. For CDC loads Don't mix table types in one job. If a Copy job holds both CDC-enabled and non-CDC tables, it falls back to watermark for all of them. Split them into separate jobs. Size log retention. If the job is paused longer than the source keeps its change data, you will need a full reload. Know the current limits. Copy job captures net changes only (not every intermediate change) and supports only the default capture instance. For Lakehouse sources, enable Change Data Feed yourself; Copy job can't detect it (Microsoft Learn). For both Reset carefully. Resetting incremental state forces a full read on the next run but doesn't clear the destination. With Append, truncate the target first or you'll get duplicates. Plan for schema changes. New source columns aren't synced automatically, and a dropped column that's in your column mapping fails the run. Conclusion Watermark and CDC aren't rivals; most Fabric estates use both. Use a watermark for append-heavy fact tables, files, and sources you can't change. Use CDC wherever deletes or history matter, which is most dimension and master-data tables. Copy job makes the choice cheap to revisit: it detects CDC-enabled tables, handles state for you, and lets you reset per table. Start with the table that hurts most in your nightly full load, pick the method from the guide above, and measure the capacity you save. Sources Incremental copy in Copy job – Microsoft Learn Change data capture (CDC) in Copy job – Microsoft Learn Choose a data movement strategy – Microsoft Learn Copy job enhancements on incremental copy and CDC – Microsoft Fabric BlogTesting Fabric Runtime 2.0 Before It Becomes the Default
Why you should test this week, not next month Runtime 2.0 has been GA since August, but it is still opt-in. According to the Runtime 2.0 docs, Microsoft plans to make it the default for new workspaces and new Environment items in late September 2026. That is right now. At the same time, Runtime 1.3 (Spark 3.5) is listed with an end-of-support date of 30 September 2026. It moves into Long Term Support from 1 October for six months, through March 2027 (lifecycle page). So you have a runway, but it is not long. In my trainings, the question I get most is: "Will my notebooks break when this flips?" My honest answer: most will run fine, a few will fail loudly, and a small number will return different results without failing. That last group is the reason to test. This post walks through the exact method I use to test in isolation, without touching production. What you need: a Fabric capacity (F2 or trial is fine), Contributor or higher on a workspace, and 30–45 minutes. What actually changes between 1.3 and 2.0 Every core component moves a major or minor version. Versions below are from the official Runtime 1.3 and Runtime 2.0 pages. Component Runtime 1.3 Runtime 2.0 What to watch Apache Spark 3.5 4.1 ANSI SQL mode is on by default in Spark 4 Delta Lake 3.2 4.2 New 4.x table features can break reads from other Fabric engines Python 3.11 3.13 Pinned pip packages may have no 3.13 wheel Java 11 21 Custom JARs built for old JDKs Scala 2.12.17 2.13 Any Scala JAR compiled for 2.12 will not load OS Azure Linux 2.0 Azure Linux 3.0 Native libraries in custom packages R 4.4.1 4.5.2 SparkR is deprecated in Spark 4.x The four areas that cause real trouble in my experience: ANSI mode. In Spark 3.5, CAST('abc' AS INT) quietly returns NULL. In Spark 4.x it throws an error. The same applies to integer overflow and invalid dates. Pipelines that relied on silent nulls now fail, which is actually good, but you need to know before a 2 a.m. run does. Scala 2.13 / Java 21. If your Environment has custom JARs, they must be rebuilt. There is no workaround. Python library pins. A requirements.txt or Environment library list pinned for Python 3.11 is the most common failure at publish time. Delta 4.x features. The docs are explicit: Delta Lake 4.x-specific features are experimental and only work in Spark experiences. If the SQL analytics endpoint, Power BI Direct Lake or the Warehouse read the same table, do not enable them. Check Delta Lake interoperability first. The good news: Runtime 2.0 ships with the Native Execution Engine, which Microsoft benchmarks at up to 6x faster than open-source Spark on TPC-DS, with no code change. The safe test method: a side-by-side Environment The trick is simple. Never change the runtime at workspace level first. An Environment item pinned to 2.0 overrides the workspace default, but only for the notebooks you attach to it. Production keeps running on 1.3 while you test the same code on 2.0. Step 1: Create a test workspace Create a workspace such as WS-Runtime2-Test on the same capacity. If your production workspace is Git-connected, branch out to it, or simply export the 3–5 notebooks that matter most. Add a shortcut to the production lakehouse tables you read from, so you test against real data without copying it. Step 2: Create two Environment items Create env_rt13 and env_rt20 in the test workspace. Two environments, same libraries, only the runtime differs. That is what makes the comparison fair. New item → Environment, name it env_rt20. In the Runtime dropdown, select 2.0 (Spark 4.1, Delta 4.2). Under Spark compute → Acceleration, turn on Native Execution Engine. Copy your production library list into Public libraries (and custom JARs/wheels if you use them). Save, then Publish. Publishing is where library conflicts surface, so watch this step. Repeat for env_rt13 with 1.3 (Spark 3.5, Delta 3.2) and the same libraries. Step 3: Attach and run Open a copy of each notebook, and in the environment dropdown on the notebook ribbon, pick env_rt20. Run it. Then run the same notebook attached to env_rt13. A Spark job definition works the same way through its settings. Optional: the early access release channel There is a second mechanism worth knowing about for later. Release channels (preview) let you test the next set of updates within a runtime before they become default. You set it in the Environment's Spark properties: spark.fabric.pools.skipStarterPools=true spark.computeConf.runtime.releaseChannel=earlyAccess Early access does not use the Starter Pool, so session start is slower, and the setting is fixed for the lifetime of a session. Billing is the same. Use it after your 2.0 migration to catch library updates early. The test notebook I use in class Four cells. Run the whole notebook once on env_rt13 and once on env_rt20. It takes about 10 minutes, most of it session start. Cell 1: Prove which runtime you are on Don't trust the dropdown; print it. This also gives you the VHD image name that support will ask for if you raise a ticket. import sys from importlib.metadata import version, PackageNotFoundError jvm = spark.sparkContext._jvm def pkg(name): try: return version(name) except PackageNotFoundError: return "not installed" info = { "spark": spark.version, "python": sys.version.split()[0], "java": jvm.System.getProperty("java.version"), "scala": jvm.scala.util.Properties.versionNumberString(), "pandas": pkg("pandas"), "native engine": spark.conf.get("spark.native.enabled", "not set"), "ansi mode": spark.conf.get("spark.sql.ansi.enabled"), "vhd image": spark.conf.get("spark.synapse.vhd.name", ""), } for k, v in info.items(): print(f"{k:<15} {v}") Cell 2: The ANSI probe This is the cell that makes the room go quiet in a workshop. Same SQL, different behaviour. probes = { "bad cast": "SELECT CAST('abc' AS INT) AS v", "int overflow": "SELECT CAST(2147483647 AS INT) + 1 AS v", "bad date": "SELECT CAST('2026-02-30' AS DATE) AS v", "div by zero": "SELECT 1/0 AS v", } for name, sql in probes.items(): try: print(f"{name:<13} -> {spark.sql(sql).first()['v']}") except Exception as e: print(f"{name:<13} -> ERROR: {type(e).__name__}") On 1.3 you get None, -2147483648, None, None. Four silent problems. On 2.0 with default settings, all four raise errors. Now search your code for CAST(, arithmetic on IDs and date parsing from strings. Cell 3: Regression check on your real logic Paste one production transformation, write the result from each runtime, and compare row by row. tag = "rt20" if spark.version.startswith("4") else "rt13" result_df = spark.sql(""" -- paste one real production query here SELECT ... """) result_df.write.mode("overwrite").format("delta").saveAsTable(f"regress_{tag}") After both runs, compare from either environment: a = spark.table("regress_rt13") b = spark.table("regress_rt20") print("row counts :", a.count(), b.count()) print("only in 1.3 :", a.exceptAll(b).count()) print("only in 2.0 :", b.exceptAll(a).count()) Zero and zero is what you want. If you get differences on float columns, round them before comparing; tiny floating point drift is not a bug. Cell 4: Make sure other engines can still read the table A table written from Runtime 2.0 must stay readable by the SQL analytics endpoint and Direct Lake. Check the protocol: (spark.sql("DESCRIBE DETAIL regress_rt20") .select("minReaderVersion", "minWriterVersion", "tableFeatures") .show(truncate=False)) Compare it with regress_rt13. They should match. Then open the lakehouse's SQL analytics endpoint and run SELECT TOP 10 * FROM regress_rt20. If that works, your downstream reports are safe. Finally, note the duration of each run from the Monitor hub. With the Native Execution Engine on, 2.0 is often faster, but measure it on your workload rather than quoting benchmarks. Fixes, rollout plan and checklist These are the fixes I reach for when the test fails. Symptom Likely cause Fix Environment publish fails Library pinned for Python 3.11 Upgrade the pin to a version with a 3.13 wheel, or remove the pin NoClassDefFoundError / scala. errors JAR compiled for Scala 2.12 Rebuild the JAR for Scala 2.13 and Java 21 CAST_INVALID_INPUT, ARITHMETIC_OVERFLOW, DIVIDE_BY_ZERO ANSI mode Use try_cast, try_divide, try_add; clean the input SQL endpoint can't read a table Delta 4.x feature enabled on a shared table Don't enable 4.x-only features on tables other engines read Results differ, no error Changed default behaviour Diff with Cell 3, then read the Spark SQL migration guide On ANSI mode: you can set spark.sql.ansi.enabled=false in the Environment to get the old behaviour back. I use it only as a temporary bridge, never as the fix. Silent nulls are exactly the data quality issue ANSI mode exists to catch. My rollout plan test workspace, two Environments, run the four cells on your top 5 notebooks. fix libraries and ANSI failures; rerun until Cell 3 shows zero differences. attach env_rt20 to production notebooks one pipeline at a time. Keep env_rt13 so rollback is one dropdown change. once everything runs on 2.0, change the workspace default in Workspace settings → Data Engineering/Science → Spark settings → Environment → Runtime version. Before March 2027: nothing left on 1.3 when LTS ends. Checklist Test workspace created, production untouched env_rt13 and env_rt20 published with identical libraries Cell 1 confirms Spark 4.1 / Python 3.13 / Java 21 ANSI probe run and code searched for risky casts Cell 3 regression shows zero differences SQL analytics endpoint reads tables written by 2.0 Custom JARs rebuilt for Scala 2.13 Workspace default switched last If you do only one thing from this post, run Cell 2 on your busiest notebook today. Sources Runtime 2.0 in Fabric Runtime 1.3 in Fabric Lifecycle of Apache Spark runtimes in Fabric Fabric runtime release channels (preview) Delta Lake table format interoperability Spark Runtime releases and updates (GitHub)Microsoft Fabric Data Agents: Setup, Testing, and Publishing
In this walkthrough, I configured Microsoft Fabric Data Agents to answer questions against business data using natural-language prompts. The focus was on selecting data sources, guiding agent behavior with instructions and example queries, testing responses, and publishing the completed agent. You can watch the video in English with English subtitles. Please check the subtitle settings on YouTube. A data agent can be useful when company data is distributed across different sources and users need more consistent answers to recurring questions, such as questions based on sales data. Key Takeaways I added data sources such as Eventhouse, Semantic Models, and Lakehouses to a Fabric Data Agent. I selected the tables the agent could read and observed a consistency warning when selecting more tables. The agent translated natural-language questions into the query language appropriate for the connected source. I used data source interactions, example queries, and agent instructions to influence responses. I tested custom instructions before publishing the agent. I also showed a Power BI Copilot scenario where a Data Agent could be referenced without opening the agent directly. What I Configured in Microsoft Fabric Data Agents I started in a workspace with a Fabric license and opened a Data Agent I had previously created as an example. The interface shown at the beginning was the interface from an earlier video, before I later shared a short excerpt of updates. The first important step was opening the Data Source option. From there, I could add an Eventhouse, Semantic Models, or a Lakehouse. In my example, I added four Lakehouses. After adding the sources, I selected the tables that I wanted the Data Agent to read. When I selected more tables, the agent displayed a warning that results might not be consistent. This is an important configuration point: expanding the number of available tables can give the agent more data to work with, but the interface warns that consistency may be affected. Once the sources were available, I could either start with sample questions or enter my own prompt. The purpose was to ask questions in natural language and receive an answer based on the selected business data. How Microsoft Fabric Data Agents Translate Questions One of the key behaviors I demonstrated was how Microsoft Fabric Data Agents convert a natural-language request into a query language that matches the connected data source. For the sources discussed in the video, the agent uses different query approaches: Eventhouse: KQL Semantic Models: DAX Lakehouse or Warehouse: SQL This means I could begin with a plain-language prompt while the Data Agent generated the source-specific query needed to retrieve a response. The exact query language depends on the type of data source attached to the agent. Using Data Source Interactions and Example Queries Next, I moved into the configuration areas that help shape how a Data Agent responds. In the Data Source Interactions section, I entered instructions related to a specific data source. This was where I could provide guidance on how the agent should work with that source. I also used Example Queries, which are sample questions connected to a desired query behavior. My goal was to ensure that, when a particular question was asked, the specified SQL query would run consistently. I compared this behavior to the Verified Answers concept for a Power BI visual. In this scenario, the sample question acts as a controlled way to steer the agent toward the expected query and output. The available setup differs by source type: For a Lakehouse or Warehouse, SQL appears in this configuration flow. For an Eventhouse, the agent responds through KQL. For the Power BI side, I mentioned preparing the data for AI so that the Data Agent can automatically detect it. This is where example queries can be useful for recurring business questions. Rather than relying only on an open-ended prompt, I can add known questions and define the behavior I want for them. Testing Agent Instructions Before Publishing In addition to source-level settings, I configured Agent Instructions. These are broader instructions that tell the agent how to approach its responses. To test this behavior, I added a simple instruction asking the agent to always address me as “boss.” I then entered a sample prompt into the agent. In this test, the response included the requested word while also returning an answer based on the prompt. I entered another prompt that matched one of the example queries I had written. The agent used that example-query path and returned the output in the same format as the sample query. I repeated this with another example to check whether the configured behavior appeared consistently in these tests. This was a short functional check rather than a comprehensive evaluation. I tested the instruction and example-query behavior shown in the agent interface, but I did not cover broader accuracy measurement, performance testing, or governance controls. Publishing a Microsoft Fabric Data Agent After I was satisfied with the agent, I selected Publish. Before publishing, I could add a description to help explain what the Data Agent was for. Once published, the interface displayed the implementation and output for the agent. Publishing is the point where the configured Data Agent becomes ready to use beyond the editing and test flow I demonstrated. I also included a short September 2026 excerpt showing an updated agent experience. In that excerpt, I showed options to view existing data sources through Add Data, add tools, and work with visualizations. I also mentioned a capability that can help generate instructions more quickly, rather than manually writing every example query, data-source instruction, and agent instruction. The updated experience also included a Test Data Agent area for checking the agent before publishing. I referenced Runtime as part of working with the newer updates shown in the interface. Using a Data Agent from Standalone Power BI Copilot Finally, I showed a scenario involving Standalone Power BI Copilot. The Data Agent was offline at the time of the recording, but I included the previously available scenario to demonstrate the intended interaction. In this flow, I used the @ symbol to reference one of my Data Agents. I had created an agent named Copilot Data Agent. The goal was to ask a question in Standalone Power BI Copilot and receive an answer from the Data Agent without first navigating directly into the agent itself. This illustrates how Microsoft Fabric Data Agents can be part of a broader Copilot-based experience, where users ask questions from a conversational interface while the agent works with its configured data sources. Frequently Asked Questions Which data sources can I add to a Microsoft Fabric Data Agent? In the demonstrated interface, I could add an Eventhouse, Semantic Models, and Lakehouses. I then selected the tables that the agent could read. Which languages can a Fabric Data Agent use for queries? The video showed KQL for Eventhouse, DAX for Semantic Models, and SQL for Lakehouse or Warehouse sources. Conclusion In this walkthrough, I configured Microsoft Fabric Data Agents by connecting data sources, selecting tables, adding source instructions, defining example queries, and testing agent instructions. I then published the agent and showed how it could be referenced from Standalone Power BI Copilot. The main takeaway is that Microsoft Fabric Data Agents provide a configuration-based way to connect natural-language questions with business data sources and source-specific query languages. If you are working with Fabric data sources, start by defining a focused set of tables and recurring questions, then test the responses before publishing.Connect Fabric Data Agents to Copilot Studio with MCP (+ Assistants API Migration Fix)
Business users want answers where they already work, in Teams and Microsoft 365 Copilot, and those answers need to come from trusted, governed data. Fabric data agents in Copilot Studio do exactly that, and the integration is now generally available. There is also a deadline you may already have passed. If you connected to Fabric data agents through the OpenAI Assistants API, your integration has likely stopped working. This guide covers both the setup and the fix. What's new Fabric data agents now connect to Copilot Studio through a new tool-based experience: you select Add a tool, search for Fabric, and choose Fabric IQ Data MCP. Your Copilot Studio agent then calls the Fabric data agent like any other tool. Microsoft Community Three points matter for architects: Data stays governed. The Fabric data agent keeps running in Fabric, respects permissions on the underlying data sources, and returns answers grounded in governed enterprise data. Microsoft Community The orchestrator decides. The orchestrator chooses when to use enterprise data and when to use its other knowledge sources and tools. Microsoft Community You can combine agents. You can pair several Fabric data agents with other business systems to build richer solutions. A sales agent could query a sales data agent, a finance data agent and your CRM connector in a single conversation. Microsoft Community Behind this, Copilot Studio also introduced a redesigned authoring surface and runtime powered by the GitHub Copilot harness. Microsoft Community Why MCP matters Model Context Protocol (MCP) is becoming the standard way agents connect to tools and data. By exposing Fabric data agents through MCP, Microsoft makes them a reusable building block instead of a one-off integration. The same move is happening elsewhere: Fabric data agents in Microsoft Foundry are now easier to connect and monitor, and that integration is moving to MCP too. Microsoft Community You build a data agent once in Fabric, and Copilot Studio, Foundry and Microsoft 365 Copilot can all use it. Prerequisites Before you start, check that you have: A Microsoft Fabric workspace on Fabric capacity Data ready to use: a lakehouse, warehouse, semantic model or similar source with clean, well-named tables Permission to create items in the Fabric workspace Access to Copilot Studio with permission to build and publish agents Microsoft 365 Copilot or Teams, depending on where you want to publish Tip: the quality of your data agent depends on the quality of your data. Clear table and column names and descriptions make a big difference to answer accuracy. Step 1: Build and test your Fabric data agent In your Fabric workspace, create a new data agent item, then: Add data sources. Connect the lakehouse, warehouse or semantic model the agent should answer from. Start narrow, since one domain per agent works best. Write agent instructions. Explain the business context, key terms and rules. For example: "Revenue means net revenue after returns. Fiscal year starts in April. Always exclude internal test accounts." Add example questions and queries. Give sample questions with the correct query logic, so the agent learns your patterns. Test thoroughly. Ask the questions your business users actually ask and compare the answers with your certified reports. Publish the agent once the answers are reliable. Step 2: Add the data agent to Copilot Studio This is the new MCP-based flow. Create or open an agent in Copilot Studio, go to Tools, select Add a tool, search for Fabric, and add Fabric IQ Data MCP. Microsoft Community Then: Select the Fabric data agent you published in Step 1. Give the tool a clear description, such as "Answers questions about sales, revenue and pipeline from governed Fabric data." The orchestrator reads this to decide when to call the tool, so be specific. In your Copilot Studio agent's instructions, tell it when to use the tool, for example: "For any question about sales numbers or targets, use the Sales Data tool." Step 3: Test in Copilot Studio Test your agent in Preview before publishing. Check that: Microsoft Community Data questions trigger the Fabric tool, and general questions don't Answers match the results from Fabric directly A user without access to certain data gets no answer from it, since the data agent respects permissions on the underlying sources Follow-up questions ("and what about last quarter?") work as expected Step 4: Publish to Teams or Microsoft 365 Copilot When you're happy with the results, publish to Microsoft Teams or Microsoft 365 Copilot. Business users can now ask data questions in the tools they use every day, without opening a report. Microsoft Community Roll out to a small pilot group first, collect feedback, and improve the data agent's instructions before rolling out widely. The Assistants API retirement: what broke and how to fix it This part matters if you built programmatic integrations. What happened: The OpenAI Assistants API, which powered the orchestration layer of the Fabric data agent, was scheduled to be shut down by OpenAI on August 26, 2026. After that date, direct calls to the Assistants API stop working. Microsoft Community Who is affected: anyone who connects to a Fabric data agent programmatically through the Assistants API needs to migrate to the data agent MCP endpoint. Typical examples are custom apps, scripts, or third-party tools calling the data agent directly. Microsoft Community Who is mostly fine: SDK and Fabric portal users need little or no action, because Microsoft migrates those experiences internally, although conversation history may reset once. Microsoft Community What stays the same: existing agent data sources, instructions and tools remain unchanged. You don't need to rebuild your agents, only change how you connect to them. Microsoft Community Migration checklist Find your integrations. Search your code and configs for Assistants API calls that target Fabric data agents. Check custom apps, automation scripts, Azure Functions and any third-party tools. Check for failures since late August. Errors or silent failures after August 26 are a strong sign an integration is affected. Move to the MCP endpoint. Update each integration to connect through the data agent's MCP endpoint instead. Follow Microsoft's guide, "Prepare your Fabric Data Agent integrations for Assistants API retirement," for the current details. Consider Copilot Studio instead of custom code. If a custom app only exists to put a chat interface on a data agent, the Copilot Studio route above may replace it with less code to maintain. Tell users about history resets. If you use the portal or SDK, let users know earlier conversations may disappear once. Retest. Run your standard question set again after migrating to confirm the answers haven't changed. Best practices for production One domain per data agent. Separate agents for sales, finance and operations are easier to test, govern and improve than one agent that tries to do everything. Write tool descriptions carefully. In Copilot Studio, the tool description is how the orchestrator decides what to call. Vague descriptions lead to wrong tool choices. Treat instructions like code. Version them, review changes, and retest after every edit. Keep a test question set. Keep 20 to 30 real questions with expected answers, and run them after any change to data, instructions or model. Watch permissions. Answers respect underlying permissions, so a mistake in data security becomes a mistake in agent answers. Review access regularly. Start small and grow. Pilot with one team, measure how often answers are right, then expand. The bottom line Connecting Fabric data agents to Copilot Studio through MCP turns your governed Fabric data into a reusable tool that any agent can use, and it puts trusted answers directly into Teams and Microsoft 365 Copilot. The setup takes minutes once your data agent is solid. If you built integrations on the Assistants API, make migration your first job this week. Your agents, data sources and instructions don't need to change, only the connection. After that, you're on the platform Microsoft is building its agent strategy on.Row-Level Security in Direct Lake Models: The Undocumented Gotchas
When we moved our sales reporting from Import mode to Direct Lake, RLS looked like the easy part. We already had dynamic RLS working in the old Import model: a security table, a USERPRINCIPALNAME() filter, and a couple of relationships. We expected to copy it over and be done in an afternoon. It took two weeks. The RLS logic itself was fine. What caused the trouble was how Direct Lake interacts with workspace permissions, the SQL analytics endpoint, framing, and fallback. Most of it is technically documented, but spread across a dozen pages, and a few behaviors we only found by testing. This post covers what we hit, how we diagnosed it, and what we'd do differently next time. The setup Here's a simplified version of what we built: Lakehouse: LH_Sales with fact_sales, dim_region, dim_product, dim_date Security table: sec_user_region (UserEmail, RegionKey), one row per user per region Semantic model: Direct Lake on the SQL analytics endpoint Users: about 400 sales reps and managers, each seeing only their regions The RLS role was the standard dynamic pattern: // Role: RegionSecurity // Table: sec_user_region [UserEmail] = USERPRINCIPALNAME() The relationship sec_user_region[RegionKey] → dim_region[RegionKey] had "Apply security filter in both directions" enabled, and dim_region filtered fact_sales. It worked when I tested it as myself, and it failed in several different ways once real users got access. Gotcha #1: Workspace roles silently bypass your RLS This is the most common one, and it matters more in Direct Lake. Semantic model RLS only applies to users who have Read permission on the model. Anyone with Admin, Member, or Contributor in the workspace has write permission, so RLS does not apply to them. They see everything. That's the same as Import mode. The Direct Lake-specific problem is the next part. The Viewer role has a hole too. A workspace Viewer is subject to your semantic model RLS, but Viewers can also connect to the lakehouse's SQL analytics endpoint and query fact_sales directly from SSMS, Excel, or a notebook. Your DAX roles don't exist there, so they can read every row. We found this when a regional manager emailed us a pivot table with national numbers. He had connected Excel to the SQL endpoint because he found it faster. The relationship sec_user_region[RegionKey] → dim_region[RegionKey] had "Apply security filter in both directions" enabled, and dim_region filtered fact_sales. It worked when I tested it as myself, and it failed in several different ways once real users got access. What we changed: Removed every business user from workspace roles. Shared the report and semantic model through an App (or item-level sharing) with Read permission only, without "Build" unless it was genuinely needed. Did not grant lakehouse access to end users at all (Gotcha #2 explains how that still works). Rule of thumb: If a user can see the lakehouse, assume they can see all of it unless you've also secured it at the SQL/OneLake layer. Gotcha #2: SSO vs. fixed identity changes who needs lakehouse access By default, a Direct Lake model on the SQL endpoint uses single sign-on (SSO): the viewer's own identity is used to access the underlying data. That means every report viewer needs permission to read the lakehouse, which brings back the problem from Gotcha #1. The fix is to configure the model's data source connection to use a fixed identity through a shareable cloud connection (a service principal or a dedicated account). Then: The fixed identity reads the Delta tables. End users only need Read on the semantic model. Your DAX RLS still applies per user, because USERPRINCIPALNAME() still returns the viewer, not the fixed identity. The catch: once you switch to fixed identity, any RLS you defined on the SQL analytics endpoint (T-SQL security policies) is evaluated against the fixed identity, not the end user. Warehouse-level RLS effectively stops being per-user for this model. So pick one layer as the source of truth: Approach Security lives in Connection Users need lakehouse access? A Semantic model (DAX roles) Fixed identity No B SQL endpoint (T-SQL RLS) SSO Yes We chose A. Mixing both led to confusing results where the same user saw different numbers depending on the path the query took. Gotcha #3: SQL endpoint RLS quietly forces DirectQuery fallback We initially tried approach B, because the data engineering team liked having security defined once in T-SQL: CREATE FUNCTION sec.fn_region_filter(@RegionKey INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT 1 AS allowed FROM dbo.sec_user_region s WHERE s.RegionKey = @RegionKey AND s.UserEmail = USER_NAME(); CREATE SECURITY POLICY sec.RegionPolicy ADD FILTER PREDICATE sec.fn_region_filter(RegionKey) ON dbo.fact_sales WITH (STATE = ON); Security worked. Performance got much worse. With Direct Lake on the SQL endpoint, any table with RLS (or OLS) defined at the SQL endpoint can't be read directly from Delta, because VertiPaq can't enforce the T-SQL policy. Queries against it fall back to DirectQuery. Our main visuals went from about 300 ms to 4–8 seconds. It fails quietly. Nothing errors. Reports just get slow. How to detect it: Run a trace in DAX Studio or SQL Profiler against the XMLA endpoint and look for DirectQuery Begin/End events. In pure Direct Lake you should see only VertiPaq scan events. In Performance Analyzer in Desktop, a "Direct query" duration on a visual is a clear sign of fallback. How to make it fail loudly instead: set the model's DirectLakeBehavior property (via Tabular Editor or TMDL) to DirectLakeOnly: model Model directLakeBehavior: directLakeOnly Now fallback-triggering queries error instead of silently degrading. We set this in dev and test so we find these issues before users do. In production you may prefer Automatic so reports keep working, but you should make that choice deliberately. Note: Direct Lake on OneLake (the newer flavour) reads Delta directly and doesn't use the SQL endpoint for queries, so SQL endpoint RLS isn't applied there at all. That's another reason not to rely on T-SQL RLS for Direct Lake models. Check the current docs on how OneLake security interacts with it, because that area is still changing. Gotcha #4: Your security table can't be a view or a calculated table In Import mode, our security table was a calculated table that unpivoted a hierarchy of managers and regions using DAX. In Direct Lake (on SQL endpoint), calculated tables and calculated columns over Direct Lake tables aren't supported. The obvious next step is a SQL view. But SQL views in a Direct Lake on SQL endpoint model always fall back to DirectQuery, because they aren't Delta tables. Your security table sits in the filter path of every query, so it pulls every query into DirectQuery with it. What worked: materialise the security table as a real Delta table in the lakehouse, rebuilt by a notebook in the same pipeline that loads the facts: from pyspark.sql import functions as F sec = ( spark.table("stg_user_access") .withColumn("UserEmail", F.lower(F.trim("UserEmail"))) .select("UserEmail", "RegionKey") .dropDuplicates() ) sec.write.mode("overwrite").format("delta").saveAsTable("sec_user_region") Keep it narrow (two columns) and deduplicated. The next gotcha explains why the lower() matters. Gotcha #5: Case sensitivity changes between Direct Lake and fallback This one took us a day to track down. A user could see data in one report page but got blanks on another. Same model, same role. The cause: USERPRINCIPALNAME() returned [email protected]. The security table had [email protected]. In Direct Lake (VertiPaq), string comparison is case-insensitive, so it matched. One page had a visual that fell back to DirectQuery (it hit a guardrail). In DirectQuery, the RLS filter is pushed down as SQL, and the Fabric SQL endpoint's default collation is case-sensitive, so it didn't match. Same user, same role, different result depending on the query path. Fix: normalise on both sides. // Role filter on sec_user_region [UserEmail] = LOWER ( USERPRINCIPALNAME () ) Also lowercase the data during ingestion (see the notebook above). Don't rely on the engine's collation. Gotcha #6: RLS changes don't take effect until the model reframes We removed a departing manager from sec_user_region, the notebook ran, and the Delta table was updated. He could still see his old regions two hours later. Direct Lake doesn't read "live" Delta. It reads the version of the table captured at the last framing operation (a refresh). If you've turned off "Keep your Direct Lake data up to date" (common when you want facts and dimensions to update together), the model keeps using the old security table until the next refresh. Security changes follow your refresh schedule, not your data load. What we do now: The pipeline that rebuilds sec_user_region ends with a semantic model refresh activity, so the model reframes immediately. For urgent revocations such as terminations, we trigger an on-demand refresh and don't wait for the schedule. We added a small "Security last refreshed" card to the report, driven by a LastUpdated column in the security table, so support can check it quickly. Gotcha #7: "Test as role" isn't the same as a real user Testing with Security → Test as role in the service, using "Other user" and typing an email, is useful but has limits: Under SSO, data access still happens with your identity. You may see rows the real user can't access at the lakehouse level, or the reverse. It doesn't show you workspace-role bypass (Gotcha #1), because you're testing the role, not the user's actual permissions. We now add a hidden debug page to every RLS model: Debug_UPN = USERPRINCIPALNAME () Debug_RegionCount = COUNTROWS ( VALUES ( dim_region[RegionKey] ) ) Debug_SecRows = COUNTROWS ( sec_user_region ) For UAT, real test accounts (not admins) open the app and screenshot this page. It shows exactly what UPN the engine sees, which also helped with a B2B guest user whose UPN didn't match the email in our HR feed. Always compare against what USERPRINCIPALNAME() actually returns, not what you expect it to return. Gotcha #8: Large security tables affect memory and cold-cache performance Direct Lake loads columns into memory on demand. The security table and its relationship columns are touched by every query for every user under that role. Our first version of sec_user_region expanded the org hierarchy down to store level: about 2.1 million rows. After a reframe, the first visual for each user was noticeably slow because the security columns had to be transcoded, and memory pressure caused more column evictions under load. Collapsing it to region level (about 3,800 rows) and moving store-level granularity into the dimension fixed the problem. Tips: Filter at the highest grain that meets the business requirement. Avoid high-cardinality string keys in the security relationship; use integer keys. Keep bidirectional filtering limited to the one security relationship, not across the whole model. Consider a warm-up query (a scheduled DAX query after refresh) for large models so the first user of the morning doesn't pay the cold-cache cost. Our checklist now Before any Direct Lake model with RLS goes to production, we check: End users have no workspace role and access through an App or item sharing only End users have no direct lakehouse / SQL endpoint access Connection uses a fixed identity (if security lives in the model) No T-SQL RLS/OLS on tables used by the model (or fallback is intentional) Security table is a physical Delta table, not a view or calculated table Emails are lowercased on both sides of the comparison DirectLakeBehavior = DirectLakeOnly in dev/test to surface fallback Security table rebuild triggers a model refresh Debug page verified by real non-admin test accounts, including guests Security table kept small, integer-keyed, and at the coarsest grain possible Final thoughts The RLS logic in Direct Lake is the same as in Import mode, so it's easy to assume nothing else changes. What does change is the environment around it: permissions are shared with the lakehouse, security can exist in two layers, data only updates when the model reframes, and queries can fall back to DirectQuery without warning. Most of our problems came from those areas, not from DAX. If you're planning a migration, test RLS with real user accounts early, run a trace to check for fallback, and decide at the start which layer owns security. Have you run into other RLS issues with Direct Lake? Please share them in the comments. I'm especially interested in how people are handling OneLake security alongside model RLS as that feature matures.Spark Runtime 2.0 - Practical Overview and Stress Testing (with a built framework)
Link to the notebooks to reproduce: https://github.com/iurii-iurchenko-1/source-materials/tree/main/2026Q3_spark_runtime_2_0 Outline 1. What is Runtime 2.0 and why does it matter? 2. The five features explained 2.1 Vectorized CSV parsing in the Native Execution Engine 2.2 ANSI SQL mode inside the native engine 2.3 Incremental liquid clustering 2.4 Arrow-native Python UDFs 2.5 The VARIANT type in Delta tables 3. What are the main improvements as shown in stress testing 4. How did I test that? – Building the framework – The three notebooks – The tables and what each one measures 5. Main conclusions 5.1 If you are an architect 5.2 If you are an engineer 6. Resources used 7. Notebook results 7.1 Runtime 1.3 metrics 7.2 Runtime 2.0 metrics 8. How to reproduce 9. Links to documentation What is Runtime 2.0 and why does it matter? What is Runtime in Spark notebooks? It is an environment that includes the version of Spark, the default version of Delta tables, and other components that provide the engine to run Spark Notebooks in Fabric. The previous version of the runtime was Runtime 1.3, which included Spark 3.5. The latest Runtime 2.0 includes Spark 4.x and an upgraded version of Delta Tables. I chose 5 particular features introduced in Runtime 2.0 and built a framework to measure the difference in performance between versions 1.3 and 2.0. These features are: Vectorized CSV parsing in the Native Execution Engine ANSI SQL mode inside the native engine Incremental liquid clustering Arrow-native Python UDFs The VARIANT type in the delta tables Let me explain these features in depth. 1 Vectorized CSV parsing in the Native Execution Engine This feature means the engine-level optimization of reading CSV files. It may help if your organization has heavy jobs to ingest CSV files at scale. 2 ANSI SQL mode inside the native engine This feature is powerful, and not a lot of people know about it. If you run Spark queries and a formula fails to calculate some values, Spark quietly returns NULL. If ANSI is enabled, the failing will be loud. It is needed in critical pipelines. Now that feature is supported by the native engine. 3 Incremental liquid clustering This new feature influences the behavior of the OPTIMIZE command on top of a Delta table. In the old version, the behavior was to reprocess all the files. In the new version, only new unclustered files will be reprocessed. Isn't that good? 4 Arrow-native Python UDFs This feature introduces the new optimized algorithm to process Pandas UDF functions. When are they needed? During a migration of the Python/Pandas notebooks. So, instead of rewriting Pandas functions and introducing new bugs, implementing this mechanism may help. IN the new engine, it is optimized to avoid converting to Pandas internally, which saves time and resources. Also, if you know PyArrow functions to proceed with strings, they may also be utilized instead of plain Python. 5 The VARIANT type in the delta tables Variant type promises performance optimization on top of JSON-like data. So, it looks like you decoded the data source as JSON, wrote it once (as a VARIANT column), and it may be processed faster later, when you need to extract some information from it. What are the Main Improvements as Shown in Stress Testing Final Results CSV Scan: I observed ~50% performance improvement on reading CSV files in Runtime 2.0 (9.2 sec vs 6.5 sec) ANSI SQL mode - significant improvement in Runtime 2.0 (see the code and logs at the end of the notebook). 5.3 sec ANSI off →12 sec ANSI ON on Runtime 1.3 vs 3.6→3.7 on Runtime 2.0. Incremental liquid clustering - the new algorithm introduces incremental clustering. So, now, we may have two strategies: incremental clustering or full rebuilding of Z-cubes. So, the introduces incremental clustering gives a significant boost in performance, but still loses the battle compared with full optimization/rebuilding of Z-cubes. Arrow-native Python UDFs give almost a 3x boost on the new runtime due to avoiding converting it to Pandas and back Variant type. Here, I observed that Variant may not give a significant boost in reading performance. Sometimes, it may have even slower performance. But it uses disk space better. How did I test that? To test that, I vibe-coded a framework in Claude to cover all the features mentioned above. It was a back-and-forth process. I carefully selected 5 features recently introduced to Runtime 2.0, built tech requirements for the system to test it, generated the code for three notebooks, ran it, got the first issues and output, improved it, and repeated it a few times till the process was smooth. I tested it in my personal MS Fabric environment with F2 capacity. The solution includes three notebooks: NB_00_GENERATE_DATA NB_10_BENCH_RUNTIME_1_3 NB_20_BENCH_RUNTIME_2_0 The tables are: fact_sales — 20M-row Delta fact table. Feeds the ANSI × NEE arithmetic query and all four UDF variants. Files/bench/raw_csv — the same 20M rows written as uncompressed CSV, 2.1 GB. The only asset that tests vectorized CSV parsing in the native engine. logs_json — 3M nested JSON documents held in a STRING column. The baseline for the VARIANT comparison, and the source for the VARIANT migration. events_lc_r13 — liquid-clustered Delta table, left with unoptimized new data on top. Measures the Delta 3.2 full-rewrite OPTIMIZE on Runtime 1.3. events_lc_r20_inc — identical twin of the above. Measures Delta 4.2's incremental OPTIMIZE on Runtime 2.0. events_lc_r20_full — identical triplet. Measures OPTIMIZE ... FULL on Runtime 2.0, giving a same-session comparison against the incremental arm. logs_variant — not from the generator; created by the Runtime 2.0 notebook itself via parse_json. Its creation time isthe VARIANT migration cost, and it is what the colon-operator query reads. After that, I ran the notebooks NB_10_BENCH_RUNTIME_1_3 and NB_20_BENCH_RUNTIME_2_0 to get the main metrics. You may find these metrics below. They show how new features in Runtime 2.0 differ in performance for different use cases. Main Conclusions If you are an architect The main point with Runtime 2.0 is that it becomes GA. That means the older versions will be deprecated step-by-step, and it is time to plan the modernizational upgrade of the current infrastructure to use Runtime 2.0. If you are an engineer Invest your time in learning about NEE (Native Execution Engine) features, and test how they impact your real data flow. They are efficient, especially on complex logic query Arrow native Python UDFs may be 3x faster compared to ordinary UDFs if you use only the library's functionality and don't use Python variables there. This library is rich. So, it is possible, especially for text operations. If you didn't know what ANSI SQL mode is, now you know. If you want to Spark fails loudly if some formula didn't take place, that's it. Now, it is much more supported by the engine. Resources Used I tested all that on a personal tenant with Fabric Capacity F2. Notebooks Results Runtime 1.3 Metrics Runtime 2.0 Metrics How to Reproduce? Import the notebooks mentioned at the beginning of the article to your Fabric tenant Create a Lakehouse "LH_RUNTIME2_0_TEST Run NB_00_GENERATE_DATA Sequentially, run NB_10 & NB_20, creating before runtimes 1.3 and 2.0 for them. Links to Documentation https://community.fabric.microsoft.com/blog/fbc_fabricupdatesblogs/fabric-august-2026-feature-summary/5325824 https://community.fabric.microsoft.com/t5/Fabric-Updates-Blog/Fabric-July-2026-Feature-Summary/ba-p/5325823 https://learn.microsoft.com/en-us/fabric/data-engineering/runtime-2-0 https://learn.microsoft.com/en-us/fabric/data-engineering/native-execution-engine-overview?tabs=sparksqlTired of Hacking DAX Just to Make a Chart Look Right? Fabric Apps Let You Build the Exact Dashboard
Ever needed a dashboard that looks a very specific way- a big trend number with a mini sparkline, a real-time ticking counter, a layout Power BI's report canvas just won't give you- and ended up stuffing extra measures and workarounds into your semantic model just to fake it? Fabric Apps let you build that exact look as a small custom web app that plugs into your existing model, and an AI coding agent can build most of it for you. Here's what it actually is, in plain terms, with one simple example.
