Microsoft Fabric Direct Lake - Simply Explained

Welcome to the M365 FM podcast where we explore the people, ideas and technologies shaping the future of work. Why fast and fresh rarely happen together. The fastest report in Microsoft Fabric might be showing you yesterday's numbers. For years you had two choices and both came with a catch. Import mode froze your numbers until the next refresh, and Directquery kept them live, but made every click crawl. Direct Lake is Microsoft's attempt to end that trade off, and the way it works is different from anything power BI has done before. Welcome to the M365 FM podcast, where we explore the people, ideas and technologies shaping the future of work. By the end of this episode, you'll know what Direct Lake is, when to use it, and the limits. Most explainers skip the two choices power BI has always given you. Think about the last sales dashboard you opened, the one with revenue by region and a nice trend line. Now think about when those numbers were actually last updated. If the answer is last night or sometime this morning, you have already met the first choice and it's called import mode. Import mode copies your data into the report itself. It pulls everything from the source, packs it into the reports, memory and answers every click from that copy. That's why an imported report feels instant. It's also why the numbers freeze. The copy only changes when a refresh runs, and refreshes usually follow a schedule like once a night. A refresh isn't a light operation either, because it copies the whole dataset all over again. That takes time and a fair amount of computing power for a few thousand rows. None of that matters. Once you're dealing with millions of rows, a refresh can run for hours and sometimes fail. When it fails, people are left staring at numbers that are older than they think. Here is why that matters more than it sounds. A stale number doesn't announce itself. It looks exactly like a fresh one, and someone makes a decision on it before anyone notices. The second choice is called Direct Query and it takes the opposite approach. This one doesn't copy anything. Every time you click a visual, the report reaches back to the source database and asks for a fresh answer. That sounds like the better deal, and sometimes it's. The trouble is that the source database was never built to handle a crowd of people clicking through dashboards at the same time, so the report slows down. Once a report feels slow, people stop using it. That was the deal for years, and most beginners meet it as a small, annoying mystery. One report is lightning fast, but a day behind another is always current but painful. To open. You had to pick a side before you even knew the words for the two sides. So for a long time, the honest answer to whether you could have both was no. Then Microsoft built a third option. What Direct Lake actually is. Direct Lake is a new storage mode for a power BI report, and it exists to kill that trade off. The phrase storage mode just means how the report gets its data directly, gets it without copying it, and without pinging the source on every click. Here is the idea in one sentence. Direct Lake reads your data where it already lives. Inside something called one lake. One lake is worth 30s of your time because everything in fabric runs through it. Microsoft describes One Lake as the single place for all of your analytics data, and it comes with every fabric tenant. When your data sits in one lake, it's stored as delta tables. Delta tables are built on a file format called parquet, which stores data by column rather than by row. That column layout happens to be exactly how power BI likes to think about data. None of this asks you to become a data engineer. It only asks you to know that your data has a home and direct lake reads it there. So instead of dragging your data into the report, direct lake points the report at those files and reads them where they sit. When someone runs a query, the report pulls in only the columns that query needs. Then it answers from memory, just like an imported report would. That's the whole trick, and it's a different way of working. The report isn't making a copy, so there's nothing to keep in sync. It's also not hammering your source database because the source isn't in the loop at all. A useful picture is a library where the books never leave the shelf. With import mode, you photocopy the whole book and keep it on your desk. That's fast to read, but your copy goes out of date with Direct Query. You walk to the shelf every time you need a single page with Direct Lake. The book stays on the shelf and you read it from your desk anyway. And because the heavy lifting happens in the lake, you get the speed people expect from an imported report. The numbers, meanwhile, stay much closer to life. Microsoft puts it plainly, saying Direct Lake delivers query performance that's comparable to import mode, without the overhead of copying. The entire data set over and over. Direct Lake is aimed at a particular situation. It's built for teams that already keep large amounts of data in the lake, often in what people call a medallion architecture. That's a brand name for organizing data in layers, where the raw data sits at the bottom and the clean report ready data sits at the top. Direct Lake is a natural fit for that top layer, because that's what you actually want to report on. If your data is small and barely changes, import mode is still perfectly fine, and Microsoft says so. Direct Lake earns its keep when the data is large, or when it moves often enough that a nightly refresh is no longer good enough. And the way it refreshes is where the real surprise hides. How it works under the hood, in plain words. Now let's talk about refresh, because Direct Lake uses that word in a way that surprises people. In import mode, a refresh means copying the data. Again. In Direct Lake, a refresh means something much smaller and it has its own name. It's called framing. Framing doesn't copy your data. Instead, it reads the table's record of changes, finds the newest files, and updates the report's pointers to look at those files. Microsoft says framing usually takes only a few seconds. In practice, that keeps the report close behind the lake, which is a far better place to be than waiting on a nightly job. There's one detail here that's easy to miss, and it explains a lot of confusion later. Framing sets a point in time after a refresh. The report shows your data as of that moment, not necessarily the very latest state of the table. If new data lands after framing, the report won't see it until the next framing pass runs. The good news is that you usually don't have to think about it. Direct Lake has a setting called keep your Direct Lake data up to date, and it's switched on by default. When it notices that the underlying tables changed, it frames them again for you. The second piece of machinery is how the report actually reads the files, and the technical word is transcoding. Don't worry about the word. The idea is simple. When a query comes in, the report works out which columns it needs. It loads just those columns from the lake and holds them in memory for next time. It doesn't load the whole table and it doesn't reload columns that are already sitting in memory. This is why the first click on a report can feel a little slower than the rest. The model is warming up, pulling the columns it needs into memory. After that, the same columns are already there and the report answers quickly. Microsoft describes these states as cold, warm and hot. Direct Lake always tries to keep itself in the warm or hot state where it performs best. Put those two ideas together and the design makes sense. Framing keeps the report pointed at fresh files and transcoding loads only what each query needs. Neither step copies your whole data set, which is why Direct Lake can feel fast without a nightly marathon. Compare that with the two older choices, and you can see what Direct Lake borrows from each. It keeps a working set in memory the way import mode does, so clicks stay quick. It stays close to the source the way Direct query does, so the numbers don't drift for a full day. What it refuses to do is copy everything or ask the database every time. And those are the two habits that made the old choices painful. One honest note because framing locks in a moment in time, direct Lake is the wrong tool for true real time needs. If you need data the second it arrives, Microsoft points people toward its real time tools instead. The two flavors of Direct Lake. Here is where the topic gets messy. Even for people who work with fabric every day, There are now two versions of Direct Lake, and they behave differently in a way that matters. The first version is called Direct Lake on SQL and it's the original. For a couple of years it was the only direct lake there was. In April of twenty twenty five, it got this new name to tell it. Apart from the second version, it reads your data through something called the SQL analytics endpoint, which is a SQL flavored window onto a single lake house or warehouse. The important part is what happens when it can't read the files directly. It silently falls back to direct query the slow mode. So the report keeps working but gets sluggish. The second version is called Direct Lake on one leg, and it's the newer one and the recommended default for new models. It connects straight to the delta table. It can pull tables from several different fabric items into one report, and it never falls back if it can't do its job, it says. So instead of going slow without telling you that difference, the safety net is the thing to understand. With the SQL version, a problem usually shows up as a slow report. With the one lake version, the same problem shows up as an error. Neither is better. In every case, the older one trades a little speed for a softer landing. The newer one trades that soft landing for predictable full speed. One small comfort before you worry about picking wrong. You don't have to get this right on day one, models can be rebuilt and everything you learn about one version carries over to the other. Microsoft gives you a setting to control this called direct lake behavior, and it has three options. Automatic just falls back when something is off. Direct Lake only refuses to fall back and fails instead. Direct query only always takes the slow road, which is handy for testing. Direct lake on one lake only ever runs in the strict mode because there's no SQL endpoint behind it. To fall back to. The fallback isn't random either. It kicks in when the report is pointed at a view instead of a real table. It also triggers when security rules are set at the SQL layer. It fires when a table grows past the size limits for your capacity, or when the table was never framed in the first place. Those are the usual suspects, and they're all fixable. If you're starting fresh, the choice is easy because Microsoft recommends the One Lake version for new models. The SQL version still has a home, though, and it's a real one. It's the only flavor that can fall back gracefully when something goes wrong. For some teams, that softer landing matters more than the last bit of speed. Where Direct Lake fits in your Microsoft world. Step back and this starts to slot into the tools you already know. Fabric is the home of Direct Lake. One lake is the storage underneath it. Power BI is the layer on top where reports and dashboards live, and that's the part most people actually see and touch. The easiest way to picture it is as a stack. Your data lands in one lake. The lake house and warehouse tools shape it, and then a power BI report reads it through Direct Lake. Remember that layered setup from earlier? The one Microsoft calls a medallion architecture? Direct Lake is built to read the clean report ready layer at the top of that stack. Now for the part that decides whether you can actually use it, which is licensing. Direct Lake isn't a feature you can switch on in a free power BI account. It needs a fabric capacity, which is a paid pool of computing power that Microsoft sells in sizes called F SKUs. If you're on a free account, on Pro, or on a per user premium license, Direct Lake is simply not available to you. There's one more wrinkle worth knowing viewing power BI content with only a free license requires a fairly large capacity an F sixty four or bigger, along with a viewer role on the smaller capacities. Anyone viewing the report still needs a pro or per user license, so the size of the capacity isn't just about speed. It also decides who gets to look. Now for how this sits between Azure and Microsoft. Three hundred sixty five fabric capacities are bought through Azure and billed by the second, which suits teams that want to start small and grow power BI. Premium capacities are the Microsoft three hundred sixty five route. Either way, you still need a per person license for most power BI work. And if you just want to try Direct Lake before spending anything, a fabric trial gives you sixty days. The upshot is simple Direct Lake is a fabric feature. It lives on a paid capacity and it sits underneath the power BI reports. Your team already opens every morning. If that sounds like a lot of moving parts, take heart because you only touch the top of the stack. The lake and the capacity are usually someone else's job. The mistakes that make direct Lake feel slow. Direct Lake can be blazing fast or strangely sluggish, and the difference usually isn't the feature itself, it's how the data underneath it is built. This is the part most introductions skip, and it's the part that will save you a headache. Start with the tables. Direct like performance depends on healthy delta tables and healthy mostly means tidy. Microsoft recommends a compression setting called V order, which packs the data tightly so it loads quickly. It also recommends that each chunk of rows, called a row group, holds somewhere between one and sixteen million rows. Lots of tiny files and tiny row groups drag performance down. The single most common beginner mistake is how you load new data. If you use an overwrite, you erase the tables history of changes and directly loses the ability to update just the new rows. It has to rebuild everything from scratch. That's exactly the slow cold reload you are trying to escape. The friendlier pattern is to append new data and then optimize the table, which merges the small files into fewer, larger ones. Partitioning is another trap. Splitting a big table by a column can speed things up, but only if that column has a small number of distinct values, ideally under a couple hundred. Pick a column with thousands of values and you multiply the files instead of reducing them, which makes everything worse. There are also hard size limits, and they depend on your capacity. The smallest capacities allow around three hundred million rows per table. An F sixty four allows about one and a half billion, and the largest capacities allow far more. If a table crosses the line, the two flavors react differently. Direct link on one leg fails to refresh, and the report stops working until you fix the table. Direct Lake on SQL falls back to the slow mode and keeps limping along. Sizing that capacity is a real decision of its own. Go too small and the report pages data in and out of memory, which feels slow. Go too large and you pay for power your team never uses. The trick is to watch the guardrails and grow only when the data tells you to. One last maintenance habit deserves a warning. Cleaning up old files is normal, but be careful with it. A framed report points at a specific version of a table, and if you delete old versions too aggressively, the report loses the files. It depends on. It starts throwing errors until a fresh refresh repaints it. In other words, tidy the lake, but don't vacuum the ground out from under the report. A few smaller limits are worth knowing up front. The report has to live in the same region as the data. It can't sit in a personal workspace and it doesn't work through a gateway. Some data types like Binary and Go-Ahead aren't supported at all. And if your capacity runs out of memory, the report starts paging data in and out, which feels exactly like slowness, even when everything else is set up right. None of that is meant to scare you off. It just means direct lake rewards. A tidy lake and a messy lake will make it look bad. What to remember about Direct Lake. So here is the short version. Direct Lake is a way for power BI to read data straight from one lake instead of copying it. That gives you the speed of an import with data that stays much closer to live. It comes in two flavors, one with a safety net and one built for full speed, and it runs only on a fabric capacity rather than a free account. It also rewards tidy tables, and it punishes sloppy ones. If you take one thing away, make it. This direct lake isn't magic, and it isn't always the right choice. It's the right choice. When your data is large, it already lives in the lake, and it changes often enough that a nightly copy no longer cuts it. Now I want to hear from you. What is the one data set in your world that always feels a step behind the one you wish could be fast and fresh at the same time? If this made the topic feel less intimidating, subscribe to M365 FM to master the tools and the mindset shaping the future of work.