<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Subham Kalkhuriya on Medium]]></title>
        <description><![CDATA[Stories by Subham Kalkhuriya on Medium]]></description>
        <link>https://medium.com/@subham.kalkhuriya?source=rss-7a6e1d223863------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*NgTkEAoCCWf1Az8-wXvwYw.jpeg</url>
            <title>Stories by Subham Kalkhuriya on Medium</title>
            <link>https://medium.com/@subham.kalkhuriya?source=rss-7a6e1d223863------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 16:58:56 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@subham.kalkhuriya/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Materialized Lake Views in Microsoft Fabric: From Basics to Production-Ready Data Pipelines]]></title>
            <link>https://medium.com/@subham.kalkhuriya/materialized-lake-views-in-microsoft-fabric-from-basics-to-production-ready-data-pipelines-d8ca79d22a55?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/d8ca79d22a55</guid>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Tue, 29 Sep 2026 18:43:18 GMT</pubDate>
            <atom:updated>2026-09-29T19:53:18.989Z</atom:updated>
            <content:encoded><![CDATA[<p>If you have worked with data engineering, you have probably seen a data architecture that looks something like this:</p><p><strong>Source → Bronze → Silver → Gold → Reporting</strong></p><p>The architecture is simple.</p><p>The problem is what happens inside it.</p><p>As the data platform grows, we start creating more notebooks, pipelines, SQL scripts, scheduling logic, monitoring jobs, and custom incremental-load processes.</p><p>A transformation that started as a simple SQL query can eventually become a fairly complex piece of engineering.</p><p>This is where <strong>Materialized Lake Views (MLVs)</strong> in Microsoft Fabric become interesting.</p><p>Instead of building custom Spark jobs and orchestration logic for every transformation, we can define the transformation declaratively and let Fabric manage much of the materialization, refresh optimization, dependency management, lineage, data quality, and monitoring experience.</p><p>A Materialized Lake View persists the result of a transformation as Delta data. Downstream consumers can query that persisted result without rerunning the complete transformation every time.</p><p>But what does that actually mean?</p><p>Let’s start from the beginning.</p><h3>1. First, What Problem Are We Trying to Solve?</h3><p>Imagine we have an Orders table containing 500 million rows.</p><p>Every day, our reporting team needs:</p><ul><li>Daily sales</li><li>Sales by product</li><li>Sales by customer</li><li>Sales by region</li><li>Monthly revenue</li><li>Order counts</li></ul><p>We could create a query like:</p><pre>SELECT<br>    OrderDate,<br>    SUM(TotalAmount) AS TotalSales<br>FROM Orders<br>GROUP BY OrderDate;</pre><p>The query is simple.</p><p>But if Power BI, another application, and several analysts repeatedly run similar transformations, the underlying data may need to be scanned and processed again and again.</p><p>Now imagine the transformation is much more complicated:</p><pre>Orders<br>   +<br>Customers<br>   +<br>Products<br>   +<br>Stores<br>   ↓<br>Cleaning<br>   ↓<br>Joins<br>   ↓<br>Business Rules<br>   ↓<br>Aggregations</pre><p>Running this repeatedly can become expensive.</p><p>Traditionally, we might solve this using:</p><ul><li>Spark notebooks</li><li>Data pipelines</li><li>Scheduled jobs</li><li>Temporary tables</li><li>Custom incremental-load logic</li><li>Monitoring scripts</li></ul><p>And now we have another problem:</p><p><strong>Who manages all of this?</strong></p><p>This is where Materialized Lake Views become useful.</p><h3>2. So, What Exactly Is a Materialized Lake View?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FGbIIthqet1l1WkGKECHmg.png" /></figure><p>Let’s break the name into two parts.</p><h3>Lake View</h3><p>It represents a logical definition of how data should be transformed.</p><h3>Materialized</h3><p>The result is actually computed and persisted.</p><p>So instead of storing only the SQL definition, Fabric maintains the result as persisted Delta data.</p><p>Conceptually:</p><pre>SQL Transformation<br>       ↓<br>   Fabric computes it<br>       ↓<br>Persisted Delta data<br>       ↓<br>Downstream users query it</pre><p>This is different from a traditional logical SQL view.</p><p>With a normal view:</p><pre>SELECT ...<br>FROM ...</pre><p>the query is logically executed when somebody queries the view.</p><p>With a Materialized Lake View:</p><pre>Source<br>   ↓<br>Transformation<br>   ↓<br>Materialized Result<br>   ↓<br>Users query stored result</pre><p>The transformation result already exists.</p><p>That is the key idea.</p><p>Microsoft describes MLVs as persisted results that can be queried like lakehouse tables, while Fabric manages execution, storage, refresh, and dependencies.</p><h3>3. MLV vs Traditional View vs Table</h3><p>This is probably the easiest way to understand the concept.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/826/1*dBj2C1V--6tWUWXNBIijEA.png" /></figure><p>So an MLV sits somewhere between a traditional view and a managed table transformation.</p><p>But there is an important difference:</p><p><strong>You define what the data should look like; Fabric manages much of the materialization, refresh optimization, dependency, and operational experience.</strong></p><h3>4. Why Is This Useful in a Medallion Architecture?</h3><p>If you are using the common Bronze → Silver → Gold architecture, MLVs fit naturally into it.</p><p>For example:</p><pre>SOURCE<br>                   │<br>                   ▼<br>          ┌─────────────────┐<br>          │     BRONZE      │<br>          │ Raw / Ingested  │<br>          └────────┬────────┘<br>                   │<br>                   ▼<br>          ┌─────────────────┐<br>          │     SILVER      │<br>          │ Clean + Join    │<br>          │ + Validate      │<br>          └────────┬────────┘<br>                   │<br>                   ▼<br>          ┌─────────────────┐<br>          │      GOLD       │<br>          │ Business-ready  │<br>          │ Aggregations    │<br>          └────────┬────────┘<br>                   │<br>                   ▼<br>             Power BI / SQL</pre><p>You can define these transformations as MLVs.</p><p>For example:</p><pre>Bronze Orders<br>      ↓<br>Silver Clean Orders<br>      ↓<br>Gold Sales Summary</pre><p>Fabric can understand these dependencies and use them when managing refresh execution.</p><p>This means we don’t necessarily have to create a separate notebook whose only responsibility is:</p><blockquote><em>“Run Silver first, then wait, then run Gold.”</em></blockquote><p>The dependency can be understood from the MLV definitions. Fabric can then process the views in dependency order during a refresh run.</p><h3>5. Creating Your First Materialized Lake View</h3><p>The basic syntax is straightforward:</p><pre>CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders AS<br>SELECT<br>    OrderID,<br>    CustomerID,<br>    OrderDate,<br>    Quantity,<br>    TotalAmount<br>FROM bronze.orders;</pre><p>That’s it.</p><p>We are saying:</p><blockquote><em>Take the data from </em><em>bronze.orders, apply this transformation, and create a materialized result called </em><em>silver.cleaned_orders.</em></blockquote><p>Fabric materializes the result as managed Delta data.</p><p>MLVs can be created from tables or from other materialized lake views.</p><h3>6. What If the View Already Exists?</h3><p>There are two useful options.</p><h3>IF NOT EXISTS</h3><pre>CREATE MATERIALIZED LAKE VIEW IF NOT EXISTS silver.cleaned_orders AS<br>SELECT ...<br>FROM bronze.orders;</pre><p>This is useful for deployment scripts because the statement doesn’t fail simply because the object already exists.</p><h3>OR REPLACE</h3><pre>CREATE OR REPLACE MATERIALIZED LAKE VIEW silver.cleaned_orders AS<br>SELECT ...<br>FROM bronze.orders;</pre><p>This replaces the existing definition.</p><p>OR REPLACE and IF NOT EXISTS cannot be used together.</p><h3>7. Materialized Lake Views Can Also Enforce Data Quality</h3><p>This is one of the useful features of MLVs.</p><p>Suppose every order should have a positive quantity.</p><p>We can define the rule directly:</p><pre>CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders<br>(<br>    CONSTRAINT valid_quantity<br>    CHECK (quantity &gt; 0)<br>    ON MISMATCH DROP<br>)<br>AS<br>SELECT<br>    OrderID,<br>    CustomerID,<br>    Quantity,<br>    TotalAmount<br>FROM bronze.orders;</pre><p>Now the transformation contains both:</p><p><strong>Business transformation + data quality rule</strong></p><p>There are two important actions when a constraint is violated.</p><h3>DROP</h3><pre>Invalid row<br>    ↓<br>Dropped<br>    ↓<br>Refresh continues</pre><h3>FAIL</h3><pre>Invalid row<br>    ↓<br>Refresh fails</pre><p>FAIL is the default behavior when ON MISMATCH isn&#39;t specified.</p><p>This is useful because data quality becomes part of the transformation definition instead of being hidden inside a separate notebook.</p><h3>8. Partitioning</h3><p>MLVs also support partitioning.</p><p>For example:</p><pre>CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders<br>PARTITIONED BY (OrderDate)<br>AS<br>SELECT<br>    OrderID,<br>    CustomerID,<br>    OrderDate,<br>    TotalAmount<br>FROM bronze.orders;</pre><p>Partitioning can help performance when queries commonly filter on the partitioning column.</p><p>But partitioning should not be treated as:</p><blockquote><em>“Partition everything.”</em></blockquote><p>The partition strategy should match the access pattern and data characteristics.</p><p>Partitioning also becomes important for certain aggregate functions when using incremental refresh, as we will see later.</p><h3>9. The Most Important Feature: Refresh and Automatic Refresh Optimization</h3><p>This is where MLV becomes much more interesting.</p><p>Fabric provides an <strong>optimal refresh</strong> decision engine for MLVs.</p><p>However, there is an important distinction:</p><p><strong>Source data changing does not mean that the MLV is instantly recalculated at that exact moment.</strong></p><p>The recurring refresh execution is managed through <strong>scheduled refresh</strong>. During a scheduled refresh run, Fabric evaluates what changed in the source data and determines the most appropriate strategy.</p><p>Conceptually:</p><pre>Source data changes<br>        ↓<br>Scheduled MLV refresh runs<br>        ↓<br>Fabric evaluates source changes<br>        ↓<br>┌──────────────┬──────────────────┬──────────────┐<br>│ No Refresh   │ Incremental      │ Full Refresh │<br>│              │ Refresh          │              │<br>└──────────────┴──────────────────┴──────────────┘</pre><p>Optimal refresh is enabled by default for an MLV lineage. You can manage it from the Materialized Lake Views management experience.</p><p>So the correct way to think about it is:</p><blockquote><strong><em>Fabric automatically decides how to refresh the MLV during a refresh run; it does not mean that every source-table change instantly triggers a refresh.</em></strong></blockquote><p>This distinction is important when testing MLVs.</p><h3>10. How Does Fabric Decide What to Refresh?</h3><p>When a scheduled refresh runs, Fabric can choose among three refresh strategies.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/808/1*aZ3myUd8gonOPzpSNVGR0Q.png" /></figure><h3>No Refresh</h3><p>If there are no new relevant Delta commits in the source tables, Fabric can skip the refresh entirely.</p><pre>Source<br>  ↓<br>No relevant change<br>  ↓<br>No Refresh</pre><h3>Incremental Refresh</h3><p>If the source data changed and the MLV is eligible for incremental processing, Fabric processes only the affected data.</p><pre>100 million existing rows<br>        +<br>2 million new/changed rows<br>        ↓<br>Process relevant changes</pre><h3>Full Refresh</h3><p>Fabric can rebuild the complete materialized result when incremental processing isn’t supported or when a full recomputation is considered more efficient.</p><p>This means that even when incremental refresh is technically possible, Fabric may still choose full refresh based on the size and cost of the changes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ec8pMTlrSeaBnIz9_Dm37A.png" /></figure><h3>11. Change Data Feed and Incremental Refresh</h3><p>For table-based MLVs, <strong>Change Data Feed (CDF)</strong> plays an important role in incremental refresh.</p><p>CDF allows Fabric to identify changes in Delta source tables instead of having to process the entire source dataset.</p><p>But <strong>enabling CDF alone does not guarantee incremental refresh</strong>.</p><p>For incremental refresh, CDF must be enabled on all source tables or MLVs referenced by the MLV definition. Without CDF, optimal refresh can choose only between no refresh and full refresh.</p><h3>How to Enable CDF</h3><p>For an existing source Delta table, you can enable CDF using:</p><pre>ALTER TABLE Sales<br>SET TBLPROPERTIES (<br>    delta.enableChangeDataFeed = true<br>);</pre><p>For example:</p><pre>ALTER TABLE bronze.orders<br>SET TBLPROPERTIES (<br>    delta.enableChangeDataFeed = true<br>);</pre><p>If your MLV uses multiple source tables, the relevant source tables need CDF enabled.</p><p>You can also enable CDF as part of the MLV creation process using TBLPROPERTIES.</p><h3>How to Check Whether CDF Is Enabled</h3><p>You can inspect the table properties:</p><pre>SHOW TBLPROPERTIES Sales;</pre><p>Look for:</p><pre>delta.enableChangeDataFeed = true</pre><p>If the property is set to true, CDF is enabled for that table.</p><blockquote><strong><em>Important:</em></strong><em> CDF is configured on the relevant Delta source table or referenced MLV, not simply on the concept of the refresh itself.</em></blockquote><h3>What Does Fabric Need for Incremental Refresh?</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/778/1*2LPFY3wQ3YlUAFBJxQccFA.png" /></figure><p>So, think of it this way:</p><p><strong>Source changes → CDF provides change information → Fabric evaluates the MLV definition and data-change pattern → Fabric chooses the appropriate refresh strategy.</strong></p><h3>Inserts vs Updates and Deletes</h3><p>For append-only data, incremental refresh can process newly inserted rows.</p><p>For example:</p><pre>Source table<br>      ↓<br>100 existing rows<br>      ↓<br>INSERT 5 new rows<br>      ↓<br>CDF captures the change<br>      ↓<br>Next scheduled refresh<br>      ↓<br>Incremental processing</pre><p>For updates and deletes, the situation is more complex.</p><p>Fabric needs a reliable way to identify row identity. When the MLV uses an appropriate refresh hint that defines a unique key, Fabric can process certain non-append changes incrementally. Without a compatible row-identity mechanism, Fabric can fall back to a full refresh.</p><p>So <strong>“UPDATE/DELETE always means full refresh” is not the correct general rule anymore</strong>. The current behavior depends on whether Fabric can identify the affected rows using the supported refresh strategy.</p><h3>12. Which SQL Expressions Support Incremental Refresh?</h3><p>This is one of the most important parts to understand.</p><p>Not every SQL transformation can be incrementally refreshed.</p><p>Fabric supports a range of common SQL constructs, but some expressions force a full refresh.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Jec59EJwrPKfZuht_UZrKg.png" /></figure><p>Unsupported constructs do <strong>not</strong> mean that the MLV cannot be created.</p><p>It means Fabric can still refresh the MLV, but it uses <strong>full refresh instead of incremental refresh</strong>.</p><h3>A Special Case: LEFT JOIN</h3><p>For a LEFT OUTER JOIN or LEFT SEMI JOIN, incremental refresh works only if the <strong>right-side table remains unchanged during the refresh cycle</strong>.</p><p>If the right-side table changes, Fabric triggers a full refresh.</p><p>For example:</p><pre>SELECT<br>    o.OrderID,<br>    o.CustomerID,<br>    c.CustomerName<br>FROM Orders o<br>LEFT JOIN Customers c<br>    ON o.CustomerID = c.CustomerID;</pre><p>Here:</p><pre>Orders       → Left side<br>Customers    → Right side</pre><p>If only new Orders are added, incremental processing can be possible.</p><p>But if the Customers table changes, existing joined results may also be affected, so Fabric falls back to a full refresh.</p><h3>Aggregations</h3><p>GROUP BY is supported, but some aggregate functions have additional requirements.</p><p>For example, AVG() and STDDEV() require the source tables to be partitioned and the partition column to be included in the GROUP BY clause.</p><p>SUM(), MIN(), MAX(), and COUNT() without DISTINCT are a special case and can support incremental refresh without that partitioning requirement.</p><h3>13. Why Dependency Management Matters</h3><p>Now let’s take a slightly more realistic example.</p><pre>Orders<br>  │<br>  ├──────────────┐<br>  ▼              ▼<br>Products      Customers<br>  │              │<br>  └──────┬───────┘<br>         ▼<br>   Silver Orders<br>         │<br>         ▼<br>   Gold Sales</pre><p>Gold depends on Silver.</p><p>Silver depends on Orders, Products and Customers.</p><p>So Gold should not refresh before Silver is ready.</p><p>This is where <strong>MLV lineage</strong> becomes important.</p><p>Fabric can understand these relationships from the MLV definitions and use the dependency chain when determining refresh order.</p><h3>14. MLV Lineage</h3><p>Fabric provides a dedicated lineage experience for MLVs.</p><p>From the Lakehouse, you can go to the <strong>Materialized lake views</strong> tab and select <strong>Manage</strong> to view the MLV lineage.</p><p>For example:</p><pre>Orders<br>   ↓<br>Silver Orders<br>   ↓<br>Gold Sales<br>   ↓<br>Gold Customer Metrics</pre><p>The lineage view shows the dependency order used for refresh operations.</p><p>You can select an MLV to inspect its upstream and downstream relationships.</p><p>One important point:</p><p><strong>Refreshing the lineage view itself does not refresh the underlying data.</strong></p><p>The lineage page can automatically update its status while a run is in progress, but the data refresh is performed by the MLV refresh run.</p><h3>15. Standard Lineage vs Extended Lineage</h3><p>This is another important distinction.</p><h3>Standard Lineage</h3><p>Standard lineage shows the current lakehouse and immediate upstream dependencies.</p><h3>Extended Lineage</h3><p>Extended lineage allows you to trace dependencies recursively across lakehouses.</p><p>For example:</p><pre>Workspace A<br>   │<br>   └── Bronze Lakehouse<br>            ↓<br>Workspace B<br>   │<br>   └── Silver Lakehouse<br>            ↓<br>Workspace C<br>   │<br>   └── Gold Lakehouse</pre><p>Extended lineage can show the broader dependency chain.</p><p>It can also support cross-lakehouse execution, where an MLV can reference tables or other MLVs across lakehouses, including lakehouses in other workspaces, subject to the required permissions.</p><h3>16. Scheduling MLVs</h3><p>This is an important operational point.</p><p>You don’t necessarily need to create a notebook whose only responsibility is:</p><pre>Refresh View A<br>   ↓<br>Wait<br>   ↓<br>Refresh View B<br>   ↓<br>Wait<br>   ↓<br>Refresh View C</pre><p>Fabric provides <strong>Scheduled Refresh</strong> through the MLV management experience.</p><p>You can create schedules to refresh all MLVs or a selected subset.</p><p>During the refresh, Fabric uses the dependency information to determine the execution order, so downstream MLVs can consume fresh upstream results. Fabric also handles transient retries.</p><p>So the architecture becomes:</p><pre>Developer<br>    ↓<br>Defines MLVs<br>    ↓<br>Fabric Lineage<br>    ↓<br>Dependency Order<br>    ↓<br>Scheduled Refresh<br>    ↓<br>Optimal Refresh Decision<br>    ↓<br>No / Incremental / Full</pre><p>This is an important distinction:</p><blockquote><strong><em>Scheduling determines when a refresh run happens. Optimal refresh determines how that run should process the data.</em></strong></blockquote><h3>17. MLVs Can Also Ingest Files Directly</h3><p>An MLV doesn’t always have to start from an existing table.</p><p>It can ingest files directly from OneLake.</p><p>Supported file formats include:</p><ul><li>CSV</li><li>Parquet</li></ul><p>The syntax looks like this:</p><pre>CREATE MATERIALIZED LAKE VIEW bronze.raw_orders<br>USING OneLake_Files<br>OPTIONS (<br>    &#39;format&#39; = &#39;csv&#39;,<br>    &#39;path&#39; = &#39;abfss://&lt;workspace&gt;@&lt;host&gt;/&lt;lakehouse&gt;/Files/orders/&#39;,<br>    &#39;header&#39; = &#39;true&#39;<br>)<br>TBLPROPERTIES (<br>    &#39;schema_mode&#39; = &#39;DYNAMIC&#39;,<br>    &#39;refresh_mode&#39; = &#39;APPEND_ONLY&#39;<br>);</pre><p>This can be useful for the Bronze layer.</p><p>Instead of:</p><pre>Files<br> ↓<br>Pipeline<br> ↓<br>Notebook<br> ↓<br>Bronze Table</pre><p>you can have:</p><pre>OneLake Files<br>      ↓<br>Bronze File-backed MLV<br>      ↓<br>Silver MLV<br>      ↓<br>Gold MLV</pre><p>Microsoft documents file-backed MLVs as a natural fit for Bronze ingestion.</p><h3>18. Dynamic vs Fixed Schema</h3><p>When ingesting files, schema handling becomes important.</p><p>There are two modes.</p><h3>DYNAMIC</h3><p>Fabric can accommodate newly discovered columns.</p><p>For example:</p><pre>File 1 → A, B, C<br>File 2 → A, B, C, D<br>                       ↑<br>                 New column</pre><h3>FIXED</h3><p>The schema is pinned at creation time.</p><p>Schema drift is rejected.</p><p>So:</p><pre>DYNAMIC<br>→ More flexible</pre><pre>FIXED<br>→ More controlled</pre><p>Choose based on whether schema evolution is expected in your source files.</p><h3>19. File Refresh Modes</h3><p>For file-backed MLVs, Fabric provides three refresh modes.</p><h3>APPEND_ONLY</h3><p>New files add new rows.</p><p>Previously materialized rows aren’t removed simply because source files disappear.</p><h3>FULL</h3><p>The current folder is processed as a complete snapshot.</p><h3>MIRROR</h3><p>The materialized result is kept aligned with additions and deletions in the source folder.</p><p>These modes should be selected based on how your source files behave.</p><p>For example:</p><pre>Daily immutable files<br>        ↓<br>APPEND_ONLY</pre><p>could make sense for an append-only ingestion pattern.</p><p>Whereas:</p><pre>Folder represents<br>current complete dataset<br>        ↓<br>FULL / MIRROR</pre><p>may be more appropriate depending on the desired behavior.</p><h3>20. The __filepath__ Column</h3><p>One small feature can be very useful for troubleshooting.</p><p>File-backed MLVs include a __filepath__ column that identifies the source file associated with a row.</p><p>That means you can investigate things like:</p><pre>SELECT<br>    __filepath__,<br>    COUNT(*) AS row_count<br>FROM bronze.raw_orders<br>GROUP BY __filepath__;</pre><p>Now you can answer:</p><blockquote><em>“Which source file contributed these rows?”</em></blockquote><p>This can be especially useful when debugging file ingestion.</p><p>Fabric also exposes run-level information such as files processed and rows added for file-ingesting MLV runs.</p><h3>21. Monitoring MLVs</h3><p>MLVs are not just about creating transformations.</p><p>You also need to know:</p><blockquote><em>“Did my refresh succeed?”</em></blockquote><p>Fabric provides monitoring capabilities for MLV runs.</p><p>You can track things such as:</p><ul><li>Refresh status</li><li>Execution performance</li><li>Data quality violations</li><li>Job instances</li><li>Refresh history</li></ul><p>The <strong>Recent runs</strong> experience helps you investigate individual runs.</p><p>Fabric also provides <strong>Analytics</strong> and <strong>Insights</strong> views for operational trends such as success rate, duration, top errors, and refresh-policy mix.</p><p>Fabric also maintains system tables under the _mlv_system schema:</p><pre>_mlv_system.sys_run_metrics<br>_mlv_system.sys_node_metrics<br>_mlv_system.sys_error_metrics</pre><p>These can provide programmatic access to MLV operational metrics.</p><p>One important warning:</p><p><strong>Don’t modify the </strong><strong>_mlv_system schema</strong>, because changes can affect the Analytics and Insights experience.</p><h3>22. Useful MLV SQL Commands</h3><p>Once you start working with MLVs, a few commands become useful.</p><h3>List MLVs</h3><pre>SHOW MATERIALIZED LAKE VIEWS IN silver;</pre><h3>Get the definition</h3><pre>SHOW CREATE MATERIALIZED LAKE VIEW silver.cleaned_orders;</pre><h3>Rename</h3><pre>ALTER MATERIALIZED LAKE VIEW<br>silver.cleaned_orders<br>RENAME TO silver.cleaned_orders_v2;</pre><h3>Drop</h3><pre>DROP MATERIALIZED LAKE VIEW silver.cleaned_orders;</pre><h3>Force a full refresh</h3><pre>REFRESH MATERIALIZED LAKE VIEW silver.cleaned_orders FULL;</pre><p>The full-refresh command is useful when you explicitly need to rebuild the materialized result, for example for troubleshooting or after a correction.</p><h3>23. What Should You Use MLVs For?</h3><p>MLVs can be particularly useful when the result is:</p><h3>Frequently queried</h3><p>For example:</p><pre>Daily Sales<br>Monthly Revenue<br>Customer Metrics<br>Product Performance</pre><p>Instead of repeatedly calculating the same expensive transformation, the result can be materialized.</p><h3>Expensive transformations</h3><p>If you have:</p><pre>Large table<br>+<br>Large table<br>+<br>Complex join<br>+<br>Aggregation</pre><p>and the result is frequently consumed, materialization can be valuable.</p><h3>Standardized transformations</h3><p>If multiple consumers need the same business logic, putting that logic into a managed MLV can provide a consistent result.</p><h3>Medallion architecture</h3><p>MLVs fit naturally into:</p><pre>Bronze → Silver → Gold</pre><h3>Data quality enforcement</h3><p>When you want transformation and declarative data-quality rules together.</p><p>Microsoft specifically highlights frequently accessed aggregations, complex joins, data-quality transformations, reporting datasets, and medallion architectures as suitable scenarios.</p><h3>24. When Should You NOT Use an MLV?</h3><p>This is equally important.</p><p>MLV is not a replacement for every Fabric workload.</p><h3>One-time queries</h3><p>If you run something once and never need the result again, materializing it may not provide much value.</p><h3>Complex Python processing</h3><p>If your transformation requires:</p><ul><li>ML inference</li><li>API calls</li><li>Complex Python logic</li><li>Custom Spark processing</li></ul><p>a notebook may be more appropriate.</p><h3>Very low-latency streaming</h3><p>If your requirement is sub-second streaming updates, Real-Time Intelligence may be a better fit.</p><p>The important point is:</p><p><strong>MLV should be selected based on the nature of the workload, not simply because it is a newer Fabric feature.</strong></p><h3>25. MLV vs Notebook vs Pipeline</h3><p>Here’s a practical way to think about it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/813/1*qU5cWRq3yk3kvbhYQMkj4Q.png" /></figure><p>The important point isn’t:</p><blockquote><em>“MLV replaces notebooks and pipelines.”</em></blockquote><p>It doesn’t.</p><p>Instead:</p><blockquote><strong><em>Use MLV where the problem is primarily declarative data transformation and managed materialization. Use notebooks and pipelines when you need broader procedural logic or orchestration.</em></strong></blockquote><h3>26. A Real-World Example</h3><p>Let’s imagine an e-commerce company.</p><p>We receive daily order files:</p><pre>OneLake<br>   │<br>   ├── orders_2026_09_25.csv<br>   ├── orders_2026_09_26.csv<br>   └── orders_2026_09_27.csv</pre><h3>Bronze</h3><p>A file-backed MLV ingests the files:</p><pre>OneLake Files<br>      ↓<br>bronze.raw_orders</pre><h3>Silver</h3><p>We clean the data:</p><pre>bronze.raw_orders<br>      ↓<br>silver.cleaned_orders</pre><p>We can add rules such as:</p><pre>CONSTRAINT valid_quantity<br>CHECK (quantity &gt; 0)<br>ON MISMATCH DROP</pre><h3>Gold</h3><p>Now we aggregate:</p><pre>silver.cleaned_orders<br>        ↓<br>gold.daily_sales</pre><p>The overall architecture becomes:</p><pre>OneLake Files<br>                    │<br>                    ▼<br>          ┌──────────────────┐<br>          │ Bronze MLV       │<br>          │ Raw Orders       │<br>          └────────┬─────────┘<br>                   │<br>                   ▼<br>          ┌──────────────────┐<br>          │ Silver MLV       │<br>          │ Clean + Validate │<br>          └────────┬─────────┘<br>                   │<br>                   ▼<br>          ┌──────────────────┐<br>          │ Gold MLV         │<br>          │ Daily Sales      │<br>          └────────┬─────────┘<br>                   │<br>             ┌─────┴─────┐<br>             ▼           ▼<br>          Power BI      SQL</pre><p>Now think about what Fabric can manage:</p><ul><li>Source dependencies</li><li>Refresh scheduling</li><li>Refresh strategy</li><li>Data quality</li><li>Dependency ordering</li><li>Lineage</li><li>Monitoring</li></ul><p>That is where the real value of MLV starts to become visible.</p><h3>27. Important Limitations to Remember</h3><p>MLVs are powerful, but they have boundaries.</p><p>Current Spark SQL limitations include:</p><ul><li>INSERT, UPDATE and DELETE aren&#39;t supported against MLVs.</li><li>Delta time-travel syntax isn’t supported in the defining SELECT.</li><li>User-defined functions aren’t supported in the defining query.</li><li>Temporary views can’t be used as sources.</li><li>Session-level Spark properties aren’t applied during scheduled refreshes.</li><li>All-uppercase schema names aren’t supported.</li></ul><p>There are also limitations around supported SQL constructs for incremental refresh. Unsupported constructs don’t prevent the MLV from being created, but they can cause Fabric to use full refresh instead of incremental refresh.</p><h3>28. The Bigger Picture</h3><p>For me, the most interesting thing about Materialized Lake Views isn’t simply performance.</p><p>The bigger change is <strong>who is responsible for the operational complexity</strong>.</p><p>In a traditional architecture, you might have:</p><pre>Developer writes SQL<br>        ↓<br>Notebook executes SQL<br>        ↓<br>Pipeline schedules notebook<br>        ↓<br>Developer manages dependencies<br>        ↓<br>Developer writes retry logic<br>        ↓<br>Developer builds monitoring<br>        ↓<br>Developer troubleshoots failures</pre><p>With MLVs, Fabric is moving more of that responsibility into the platform:</p><pre>Developer defines transformation<br>              ↓<br>        Fabric manages<br>     ┌───────────────────┐<br>     │ Materialization   │<br>     │ Refresh strategy  │<br>     │ Dependencies      │<br>     │ Scheduling        │<br>     │ Data quality      │<br>     │ Lineage           │<br>     │ Monitoring        │<br>     └───────────────────┘</pre><p>That doesn’t mean there is no engineering involved.</p><p>It means the engineer can spend more time thinking about:</p><p><strong>What should the data look like?</strong></p><p>instead of repeatedly implementing:</p><p><strong>How do I run this transformation every time?</strong></p><h3>29. Final Takeaway</h3><p>If I had to explain Materialized Lake Views in one sentence, I would say:</p><blockquote><strong><em>A Materialized Lake View lets you define a data transformation declaratively, while Fabric manages the persisted result and provides built-in capabilities for refresh optimization, dependency management, lineage, data quality, and monitoring.</em></strong></blockquote><p>The biggest areas where MLVs can fit well are:</p><pre>Frequently used transformations<br>             +<br>Expensive joins / aggregations<br>             +<br>Data quality rules<br>             +<br>Medallion architecture<br>             +<br>Managed refresh<br>             +<br>Dependency-aware execution<br>             +<br>Lineage and monitoring</pre><p>But MLV isn’t a universal replacement for notebooks or pipelines.</p><p>If you need procedural Python, external API calls, machine-learning logic or complex orchestration, notebooks and pipelines still have an important role.</p><p>The real opportunity is to use each Fabric capability for what it does best.</p><p>And that is perhaps the most important way to think about MLVs:</p><blockquote><strong><em>Don’t start by asking, “Can I replace my notebook with an MLV?”</em></strong></blockquote><p>Start by asking:</p><blockquote><strong><em>“Is this transformation declarative, repeatedly used, and something I want the platform to materialize and manage for me?”</em></strong></blockquote><p>If the answer is yes, Materialized Lake Views are worth considering.</p><h3>30. Conclusion</h3><p>A Materialized Lake View in Microsoft Fabric is basically a <strong>precomputed and persisted result of a data transformation</strong>. Instead of running the same transformation every time a consumer queries the data, Fabric maintains the transformed result and provides managed refresh capabilities.</p><p>During a refresh run, Fabric evaluates the source changes and can choose between <strong>No Refresh, Incremental Refresh, or Full Refresh</strong>, depending on the source data, MLV definition, and refresh strategy.</p><p>For example, suppose I have sales data in a Bronze table and I need a daily customer sales summary. Instead of calculating that aggregation every time someone queries it, I can create an MLV that maintains the aggregated result. When a scheduled refresh runs, Fabric evaluates the source changes and determines how the MLV should be refreshed.</p><p>That combination of <strong>declarative transformations + persisted results + refresh optimization + dependency management + lineage + monitoring</strong> is what makes Materialized Lake Views an interesting capability in Microsoft Fabric.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9njZMOHNVk738iCJuW5z1A.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d8ca79d22a55" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[AI is a Deflationary Force Living Inside an Inflationary World]]></title>
            <link>https://medium.com/@subham.kalkhuriya/ai-is-a-deflationary-force-living-inside-an-inflationary-world-19463df5f74c?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/19463df5f74c</guid>
            <category><![CDATA[business-strategy]]></category>
            <category><![CDATA[economics]]></category>
            <category><![CDATA[technology]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <category><![CDATA[the-future-of-work]]></category>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Sun, 14 Jun 2026 10:49:34 GMT</pubDate>
            <atom:updated>2026-06-14T15:49:01.734Z</atom:updated>
            <content:encoded><![CDATA[<p>In 2022, Klarna — the Swedish buy-now-pay-later giant — employed roughly 700 customer service agents to handle millions of support queries every year. By early 2024, they announced their AI assistant was doing the work of 700 of those agents, handling two-thirds of all customer chats within the first month of deployment. Leadership called it a breakthrough. The press called it a case study. The 700 people whose contracts were not renewed called it something else entirely.</p><p>On paper, Klarna saved millions in salary costs. But here is what the headlines skipped: their cloud infrastructure spend climbed. Their AI vendor contracts ballooned. A new layer of machine-learning engineers joined the payroll to maintain and train the system. The savings did not vanish — they just quietly moved to a different column on the balance sheet.</p><p>The money does not disappear. It reshuffles. Someone else catches it on the other end.</p><p>If this reads like a story about call centers, think bigger. AI is systematically driving the cost of cognitive labor toward zero — translation, software development, support operations, content pipelines. Economists have a word for this: deflation. The marginal cost of producing these services collapses. In theory, that should make goods and services cheaper across the board.</p><p>In practice, it does not work out that way.</p><p>Here is the asymmetry that rarely gets discussed: AI deflates the things you <em>produce and sell</em> while inflating the things you <em>need to buy</em>. Training large AI models demands energy at a scale comparable to mid-sized industrial operations. Running those models day-to-day is not a one-time cost — it is a persistent, compounding drain. Every major tech company racing to build AI infrastructure is also competing for the same power grid, the same data center real estate, the same chips.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/810/1*g2bn-RVuGfGjx9P2yMOcDw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/899/1*grqbXweFj57n1x2PtvZMMA.png" /></figure><p>The utility companies are not absorbing those costs. They pass them on. The SaaS platforms built on top of AI are not eating the infrastructure bill. They pass it on. Every “AI-powered” tool you subscribe to has already baked in a premium that reflects the underlying cost of the models powering it.</p><p>So you are running faster — genuinely faster, in many cases — but the treadmill is also accelerating. Your output velocity increases while the baseline cost of operating stays stubbornly high or keeps creeping upward.</p><p>Your skills are depreciating. Your rent is not. That gap is where the tension lives.</p><p>There is a more uncomfortable version of this story. The productivity gains that AI is delivering are not primarily flowing into lower prices for end consumers. They are flowing into expanded profit margins for the companies capturing them. When a business automates a function that used to require a team, the savings rarely translate to a cheaper product. They translate to a better quarterly report.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/814/1*V_uxItTmAQwtA5cBnnXgLQ.png" /></figure><p>The historical pattern is consistent. The printing press reduced the cost of books dramatically — then literacy became the baseline requirement, then formal education, then advanced degrees, then professional credentials. Each technological wave that made something cheaper simultaneously raised the floor of what you needed to participate in the economy. AI appears to be following the same script, just at higher speed.</p><p>The people positioned to benefit most are the ones who own the underlying infrastructure: semiconductor manufacturers, cloud providers, data center operators, energy producers. They sit at the base of the stack and collect rent from everyone building above them. Everyone else is negotiating how to remain useful to a system that is actively repricing their value downward.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nzNh-S7au0ZqywsekG1PDQ.png" /></figure><h4><strong>None of this is an argument against using AI. The tools are genuinely powerful and ignoring them is its own kind of risk. But there is a meaningful difference between using a technology and understanding who actually benefits from its adoption. The current AI wave is, at its core, a large-scale transfer of economic value — from labor toward capital, from users toward infrastructure owners, dressed up in the language of democratization and empowerment.</strong></h4><h4><strong>The smarter question is not “how do I use AI?” It is “where does the value actually accumulate — and am I anywhere near that?”</strong></h4><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=19463df5f74c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Clustering in Microsoft Fabric Data Warehouse — A Practical Guide]]></title>
            <link>https://medium.com/@subham.kalkhuriya/data-clustering-in-microsoft-fabric-data-warehouse-a-practical-guide-6575be46da5a?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/6575be46da5a</guid>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[data-warehouseing]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Tue, 14 Apr 2026 05:59:40 GMT</pubDate>
            <atom:updated>2026-04-14T05:59:40.761Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Stop scanning 200 million rows when you only need 2 million</em></p><h3>The Problem</h3><p>Your Fabric Data Warehouse is set up. Reports are connected. But dashboards are slow. Queries are timing out.</p><p>You check the query. It looks fine. The issue isn’t the SQL -it’s the storage. Your engine is scanning <strong>every single row</strong> in a 200 million row table just to return a few thousand records.</p><p>This is exactly the problem Data Clustering solves.</p><h3>What is Data Clustering?</h3><p>Data Clustering is a way to <strong>physically organize your rows on disk</strong> so that similar data is stored close together.</p><p>Without clustering, your rows are stored in the order they arrived — completely random from a query perspective.</p><p>With clustering, rows with similar values in your chosen column are stored together in the same storage files. When a query filters on that column, the engine only opens the files it actually needs -and <strong>skips everything else</strong>.</p><p>This skipping is called <strong>File Skipping</strong>, and it’s the core reason clustering makes queries faster.</p><h3>A Simple Example</h3><p>Say you have 5 files storing Sales data for 5 customers.</p><p><strong>Without clustering — data is scattered:</strong></p><pre>File 1: Cust 1003, Cust 1001, Cust 1005, Cust 1002<br>File 2: Cust 1004, Cust 1003, Cust 1001, Cust 1005<br>File 3: Cust 1002, Cust 1004, Cust 1003, Cust 1001<br>File 4: Cust 1005, Cust 1002, Cust 1004, Cust 1003<br>File 5: Cust 1001, Cust 1005, Cust 1002, Cust 1004</pre><p>Query: WHERE CustomerID = 1003</p><p>Cust 1003 is in all 5 files. Engine opens all 5. <strong>Full scan.</strong></p><p><strong>With clustering on CustomerID — data is grouped:</strong></p><pre>File 1: Cust 1001, Cust 1001, Cust 1001, Cust 1001<br>File 2: Cust 1002, Cust 1002, Cust 1002, Cust 1002<br>File 3: Cust 1003, Cust 1003, Cust 1003, Cust 1003  ← only this file needed<br>File 4: Cust 1004, Cust 1004, Cust 1004, Cust 1004<br>File 5: Cust 1005, Cust 1005, Cust 1005, Cust 1005</pre><p>Same query: WHERE CustomerID = 1003</p><p>Engine reads the metadata → knows Cust 1003 is only in File 3 → opens File 3 → <strong>skips 4 files = 80% less I/O.</strong></p><h3>Why Use It? (Real Numbers)</h3><p>I tested this on the NY Taxi dataset — 76 million rows, same query, same data. Here’s what actually happened:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EW-tT4ZA0hv6_hZDRG2CLg.png" /></figure><p><strong>64% less data processed. 36% less CPU. 50% faster. Just by adding </strong><strong>CLUSTER BY.</strong></p><p>The regular table had a memory cache advantage — it had been queried before, so most of its data was already warm in RAM. The clustered table was freshly created, with some files still on remote storage. Despite that, the clustered version won by a significant margin. When you reduce total data scanned this aggressively, even caching advantages stop mattering.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XRIERqlTJsnNhSBc088xLg.png" /></figure><h3>When Should You Use It?</h3><p>Use clustering when:</p><ul><li>Your table has <strong>millions of rows</strong> (larger the table, bigger the benefit)</li><li>Your queries <strong>filter on specific columns</strong> frequently — dashboards, reports, scheduled jobs</li><li>Your filter columns have <strong>many distinct values</strong> — like CustomerID, OrderDate, ProductID</li></ul><p>Skip clustering when:</p><ul><li>Your table is small (overhead not worth it)</li><li>The column you’d cluster on has very few distinct values — like Gender, Status, Region</li><li>You only use that column in GROUP BY or JOIN, not in WHERE</li></ul><h3>Which Columns Can You Cluster On?</h3><p><strong>Supported:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p4a77VzMOai2U1t0Ky6x8Q.png" /></figure><p><strong>Not supported:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u0XUIS3nEzYvm1ryjeIErQ.png" /></figure><h3>Step-by-Step: Practical Implementation</h3><p>We’ll use the NY Taxi dataset as our example throughout. Follow each step in order.</p><h3>Step 1-Set Up Your Warehouse</h3><p><strong>What to do:</strong></p><p>Open your Microsoft Fabric workspace. If you don’t have a Warehouse yet, create one: New → Warehouse → give it a name</p><h3>Step 2 -Load Sample Data</h3><p><strong>What to do:</strong></p><p>We need a large table to see clustering’s real benefit. Load the NY Taxi dataset using this Article :</p><p><a href="https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-load-sample-data-to-data-warehouse">https://learn.microsoft.com/en-us/fabric/data-factory/tutorial-load-sample-data-to-data-warehouse</a></p><p>Once loaded, verify it’s there:</p><pre>SELECT COUNT(*) FROM nyctlc</pre><p><strong>Expected output:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-D-zUpiie6tNeTwfOdld7Q.png" /></figure><p>76 million rows. Good. This is large enough to see a clear difference between clustered and non-clustered performance.</p><h3>Step 3 — Understand Your Query Before Clustering</h3><p><strong>What to do:</strong></p><p>Before clustering anything, know which query you’re trying to speed up. Here’s the query we’ll test:</p><pre>SELECT<br>    YEAR(lpepPickupDatetime)  AS pickup_year,<br>    AVG(fareAmount)           AS average_fare<br>FROM nyctlc<br>WHERE lpepPickupDatetime BETWEEN &#39;2008-12-31&#39; AND &#39;2014-06-30&#39;<br>GROUP BY YEAR(lpepPickupDatetime)<br>ORDER BY YEAR(lpepPickupDatetime) DESC<br>OPTION (LABEL = &#39;Regular&#39;);</pre><p><strong>Run this query once. Note how long it takes.</strong></p><p>The OPTION (LABEL = &#39;Regular&#39;) part tags this query so we can find it later in Query Insights for comparison.</p><p><strong>Why this column?</strong></p><p>The WHERE clause filters on lpepPickupDatetime. It&#39;s a datetime column with millions of distinct values (one per taxi trip). This makes it a perfect candidate for clustering -high cardinality, frequently filtered.</p><p><strong>Expected output:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pT7_6jnFGdSdKVAAzi_gPQ.png" /></figure><h3>Step 4 — Create a Clustered Copy of the Table</h3><p><strong>What to do:</strong></p><p>Now create a new version of the table with clustering on lpepPickupDatetime. We use CREATE TABLE AS SELECT (CTAS) to copy the data and apply clustering in one step:</p><pre>CREATE TABLE nyctlc_Clustered<br>WITH (CLUSTER BY (lpepPickupDatetime))<br>AS SELECT * FROM nyctlc;</pre><p><strong>This will take a few minutes</strong> — it’s copying 76 million rows and organizing them by datetime during ingestion.</p><p><strong>Expected output:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5uqUjJJUZ6C5ftOf39dRbQ.png" /></figure><h3>Step 5 — Verify That Clustering is Applied</h3><p><strong>What to do:</strong></p><p>Before testing, confirm the clustering is correctly defined on your new table:</p><pre>SELECT<br>    t.name                      AS table_name,<br>    c.name                      AS column_name,<br>    ic.data_clustering_ordinal  AS clustering_ordinal<br>FROM sys.tables t<br>JOIN sys.columns c<br>    ON t.object_id = c.object_id<br>JOIN sys.index_columns ic<br>    ON c.object_id = ic.object_id<br>   AND c.column_id = ic.column_id<br>WHERE ic.data_clustering_ordinal &gt; 0<br>ORDER BY t.name, ic.data_clustering_ordinal;</pre><p><strong>Expected output:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WoSw17UpgFRF2buTTnO3vA.png" /></figure><p>You should see your table name, the column name, and ordinal 1. If this row appears, clustering is active.</p><h3>Step 6 — Run the Same Query on the Clustered Table</h3><p><strong>What to do:</strong></p><p>Run the exact same query, but this time on the clustered table. Tag it with &#39;Clustered&#39; label:</p><pre>SELECT<br>    YEAR(lpepPickupDatetime)  AS pickup_year,<br>    AVG(fareAmount)           AS average_fare<br>FROM nyctlc_Clustered<br>WHERE lpepPickupDatetime BETWEEN &#39;2008-12-31&#39; AND &#39;2014-06-30&#39;<br>GROUP BY YEAR(lpepPickupDatetime)<br>ORDER BY YEAR(lpepPickupDatetime) DESC<br>OPTION (LABEL = &#39;Clustered&#39;);</pre><p><strong>Run this query only once.</strong> You want to compare cold cache performance — if you run it multiple times, the cache kicks in and both versions will look equally fast, hiding clustering’s actual benefit.</p><p><strong>Expected output:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NQV-dRMTlZpUsdatQ-9GBw.png" /></figure><p>Same 6 rows as before (one per year from 2009–2014), but it should complete noticeably faster.</p><h3>Step 7 — Measure the Difference with Query Insights</h3><p><strong>What to do:</strong></p><p>Wait 2–3 minutes for Query Insights to process the query history. Then run:</p><pre>SELECT<br>    label,<br>    total_elapsed_time_ms,<br>    allocated_cpu_time_ms,<br>    data_scanned_remote_storage_mb,<br>    data_scanned_memory_mb,<br>    data_scanned_disk_mb,<br>    result_cache_hit<br>FROM queryinsights.exec_requests_history<br>WHERE label IN (&#39;Regular&#39;, &#39;Clustered&#39;)<br>ORDER BY submit_time DESC;</pre><p><strong>Actual results from my test run:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HGEeQeViwO5cS3EX8SbD8Q.png" /></figure><p><strong>What to look at:</strong></p><p>The most important column is not any single one -it’s remote_mb + memory_mb combined. That&#39;s the <strong>total data the engine actually touched</strong> to answer your query.</p><ul><li>Regular table touched <strong>210 MB total</strong> — 208 MB from memory cache, 2 MB from remote storage.</li><li>Clustered table touched <strong>75.8 MB total</strong> — 70 MB from memory cache, 5.5 MB from remote storage.</li></ul><p>That’s <strong>64% less data processed</strong> — and it directly shows up in the other numbers too: 36% less CPU, 50% faster end-to-end.</p><p><strong>Reading the Results</strong></p><p>At first glance, the Clustered table shows <em>more</em> remote storage reads (5.4 MB) than the Regular table (2.0 MB) — which looks confusing. Let me explain why this actually makes perfect sense.</p><p>The Regular table is older. It had been queried multiple times before this test, so most of its data was already sitting warm in memory — 208 MB of it. The engine barely touched remote storage because RAM had it covered.</p><p>The Clustered table was freshly created. A small portion of its files hadn’t been cached yet, so the engine fetched those from remote storage — hence the 5.4 MB.</p><p>But now look at the memory column. The Clustered table only needed <strong>70 MB from memory</strong>, while Regular needed <strong>208 MB</strong>.</p><p>Add it up:</p><ul><li>Regular: 208 + 2 = <strong>210 MB total</strong></li><li>Clustered: 70 + 5.4 = <strong>75.8 MB total</strong></li></ul><p>The Clustered table processed <strong>64% less data overall</strong> — even though it had no cache warmup advantage. The engine simply knew which files to open and skipped the rest. That’s file skipping doing its job.</p><p>The result: <strong>50% faster end-to-end, 36% less CPU consumed</strong> — on the same 76 million row dataset, same query, same Fabric capacity.</p><ul><li>result_cache_hit — Both show 0. This confirms both queries actually ran and weren&#39;t served from a cached result. The timing is real.</li></ul><blockquote><em>If your Query Insights query returns no rows, wait 5–10 minutes and try again. There’s a short processing delay before data appears.</em></blockquote><blockquote><em>If your Query Insights query returns no rows, wait 5–10 minutes and try again. There’s a processing delay before data appears.</em></blockquote><h3>Step 8 — Apply to Your Production Table</h3><p>Now apply what you learned to your own tables. Here’s the pattern:</p><p><strong>Scenario A — New table (you haven’t created it yet):</strong></p><pre>CREATE TABLE FactSales (<br>    SaleID      INT,<br>    CustomerID  INT,<br>    OrderDate   DATE,<br>    Amount      DECIMAL(10,2)<br>) WITH (CLUSTER BY (CustomerID, OrderDate));</pre><p>Use this when you’re designing a new table from scratch. Cluster on the columns that your WHERE clauses filter on most.</p><p><strong>Scenario B — Existing table (already has data, want to add clustering):</strong></p><pre>-- Create a new clustered version<br>CREATE TABLE FactSales_Clustered<br>WITH (CLUSTER BY (CustomerID, OrderDate))<br>AS SELECT * FROM FactSales;</pre><pre>-- Once verified, rename: drop old, rename new<br>-- (Do this during a maintenance window)</pre><blockquote><em>You cannot add or change clustering on an existing table. CTAS is the only way to re-cluster.</em></blockquote><p><strong>Scenario C — Check if a specific table has clustering:</strong></p><pre>SELECT<br>    c.name                      AS column_name,<br>    ic.data_clustering_ordinal  AS ordinal<br>FROM sys.tables t<br>JOIN sys.columns c<br>    ON t.object_id = c.object_id<br>JOIN sys.index_columns ic<br>    ON c.object_id = ic.object_id<br>   AND c.column_id = ic.column_id<br>WHERE t.name = &#39;FactSales_Clustered&#39;<br>  AND ic.data_clustering_ordinal &gt; 0<br>ORDER BY ic.data_clustering_ordinal;</pre><h3>Limitations to Keep in Mind</h3><ul><li><strong>Defined at creation only.</strong> You cannot add or modify clustering on an existing table. Use CTAS to recreate it.</li><li><strong>Max 4 columns.</strong> You can cluster on up to 4 columns. In practice, 1–2 is usually sufficient.</li><li><strong>IDENTITY columns not allowed.</strong> Use a different column in your CLUSTER BY.</li><li><strong>Ingestion is slightly slower.</strong> Clustering adds processing overhead on writes. The read savings more than compensate for most DW workloads.</li><li><strong>Small inserts may cluster asynchronously.</strong> If you insert fewer than 1 million rows at a time, clustering might happen in the background rather than immediately during ingestion.</li><li><strong>Column order in CLUSTER BY doesn’t matter.</strong> CLUSTER BY (A, B) and CLUSTER BY (B, A) produce the same result.</li></ul><h3>Quick Reference — Dos and Don’ts</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_wmwTilz9CD9J-TuByibNA.png" /></figure><h3>Wrapping Up</h3><p>Data Clustering is one of the most impactful performance features in Fabric Data Warehouse — and it requires zero changes to your queries. Define it once at table creation, and every query that filters on that column benefits automatically.</p><p>The numbers from this test tell the real story: the regular table had a warm memory cache advantage — most of its data was already in RAM before the query even ran. The clustered table was fresh, pulling some files from remote storage. Clustered still finished 50% faster, touching 64% less data total. When file skipping is working correctly, infrastructure advantages stop mattering.</p><p>For production Gold layer tables that power dashboards and reports, clustering should be part of your default table design. Identify your high-cardinality filter columns, add CLUSTER BY at creation time, and let Query Insights confirm the impact.</p><p><em>Have questions or want to share your clustering results? Drop a comment below.</em></p><p><em>Follow for more hands-on Microsoft Fabric content.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6575be46da5a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Mastering Fabric Data Agents: From Setup to Smarter Insights]]></title>
            <link>https://medium.com/@subham.kalkhuriya/mastering-fabric-data-agents-from-setup-to-smarter-insights-2dbc640b582b?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/2dbc640b582b</guid>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Wed, 10 Sep 2025 06:28:07 GMT</pubDate>
            <atom:updated>2025-09-10T06:28:07.798Z</atom:updated>
            <content:encoded><![CDATA[<p>Microsoft Fabric has rolled out an amazing feature — the <strong>Data Agent</strong>. It’s like having a conversational Q&amp;A buddy powered by generative AI. You can literally talk to your data in plain English and get instant, meaningful answers — no coding, no technical expertise needed.</p><p>The Data Agent works directly with your data in <strong>Fabric OneLake</strong>. Just ask a question like:</p><ul><li><em>“Show me sales trends for the last quarter.”</em></li><li><em>“Which product category gave us the highest profit last month?”</em></li><li><em>“How many employees joined in the last six months?”</em></li><li><em>“What’s the forecasted revenue for next quarter?”</em></li></ul><p>And boom — you get precise, context-rich responses right away.</p><p>What’s even cooler is that you can customize it with your organization’s own instructions and examples. This way, the answers are not just generic but aligned with your actual business needs.</p><p>In short, the Fabric Data Agent makes data insights <strong>simple, useful, and powerful</strong>. It helps teams collaborate better, make faster decisions, and truly build a data-driven culture.</p><h4><strong>Prerequisites for Using Fabric Data Agent</strong></h4><p>Before you can start using a Fabric Data Agent, make sure the following prerequisites are met:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/694/1*AMSBf77WKDl6SyhvWsvivQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/891/1*3IGtVJQQf0UO3Ensc6DFbw.png" /><figcaption>image : Fabric Data Agent Prerequisites</figcaption></figure><h3>Configure Fabric data agent tenant setting</h3><p>as mentioned above To use a data agent in Microsoft Fabric, you must configure the required tenant settings. Additionally, if your Fabric data agent uses a Power BI semantic model as a data source, specific tenant settings must be enabled to allow connectivity.</p><p>kindly check this <a href="https://learn.microsoft.com/en-us/fabric/data-science/data-agent-tenant-settings">Microsoft link</a>, This guide walks you through the necessary configurations for a seamless setup.</p><h3><strong>Fabric Data Agent vs. Fabric Copilot — Simple Breakdown</strong></h3><h4>1. Customization</h4><p><strong>Fabric Data Agent</strong> = Fully customizable</p><ul><li>Add custom rules, instructions, and example questions.</li><li>Tune behavior as per your company’s needs.</li></ul><p><strong>Fabric Copilot</strong> = Prebuilt, fixed behavior</p><ul><li>No option to tweak how it works.</li></ul><p><strong>Example:</strong><br> Want “Revenue” to always mean Net Revenue in answers?</p><ul><li>You can configure this in <strong>Fabric</strong> <strong>Data Agent</strong>.</li><li>You can’t do this in <strong>Fabric</strong> <strong>Copilot</strong>.</li></ul><h4>2. Scope of Use</h4><p><strong>Fabric Copilot</strong> = Works inside Microsoft Fabric only Helps with tasks like:</p><ul><li>Writing SQL queries for warehouses.</li><li>Generating code in notebooks.</li></ul><p><strong>Fabric Data Agent</strong> = Works inside + outside Fabric Can integrate with:</p><ul><li>Microsoft Teams</li><li>Copilot Studio</li><li>Azure AI Foundry</li><li>Other enterprise apps</li></ul><p><strong>Example:</strong><br> A sales team in Teams asks: “Show top 5 customers by Q2 revenue.”</p><ul><li><strong>Fabric Data Agent</strong> answers directly in Teams.</li><li><strong>Fabric Copilot</strong> can’t work outside Fabric.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/831/1*ZUUIqpC6BXkkKt5WVvWnGg.png" /></figure><h3>End-to-End Flow for creating and consuming Fabric data agents</h3><h4>Prepare the Foundation</h4><p>To create a Fabric Data Agent, you first need to set up the base. Follow these steps:</p><ol><li><strong>Create a Lakehouse</strong> — Start by creating a <strong>Lakehouse</strong> and loading it with the necessary data. This will act as the fuel for your Data Agent.</li><li><strong>Create a new notebook</strong> — In your workspace, create a <strong>new notebook</strong> to run the code to set up your data</li><li><strong>Add a data source</strong> — On the left side of the Explorer pane, select <strong>+ Data sources</strong>. You can either connect an existing Lakehouse, Or, for clarity, create a <strong>new Lakehouse</strong> and give it a proper name.</li><li><strong>Insert starter code</strong> — In the <strong>top cell</strong> of your notebook, add a starter <strong>code </strong>and select<strong> Run All, After</strong> a few minutes, the lake house populates with the necessary data.</li></ol><pre>import pandas as pd<br>from tqdm.auto import tqdm<br>base = &quot;https://synapseaisolutionsa.z13.web.core.windows.net/data/AdventureWorks&quot;<br><br># load list of tables<br>df_tables = pd.read_csv(f&quot;{base}/adventureworks.csv&quot;, names=[&quot;table&quot;])<br><br>for table in (pbar := tqdm(df_tables[&#39;table&#39;].values)):<br>    pbar.set_description(f&quot;Uploading {table} to lakehouse&quot;)<br><br>    # download<br>    df = pd.read_parquet(f&quot;{base}/{table}.parquet&quot;)<br><br>    # save as lakehouse table<br>    spark.createDataFrame(df).write.mode(&#39;overwrite&#39;).saveAsTable(table)</pre><p>6. After a few minutes, the lakehouse populates with the necessary data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Me4Td5taSRlSt_e06eizZw.png" /></figure><h3>Create a new Fabric data agent</h3><p>To create a new Fabric data agent, navigate to your workspace and select the + New Item button, In the All items tab, search for Fabric data agent to locate the appropriate option as shown in this screenshot:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ByltRciBZE3sE2m0phcpfw.png" /></figure><p>Once selected, you’re prompted to provide a name for your Fabric data agent, as shown in this screenshot:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vLAU7GwMNN-OLKESfJtX6w.png" /></figure><p>After you enter the name, Select the lakehouse you created in the previous step, and then select Add, as shown in the following screenshot:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*unqSKvnkwKKEsqdMiC74Sg.png" /></figure><p>Once the lakehouse is added as a data source, the Explorer pane on the left side of the Fabric data agent page shows the lakehouse name. Select the lakehouse to view all available tables. Use the checkboxes to select the tables you want to make available to the Data Agent</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rVuZC7MWIdXcrhqBu4u6fQ.png" /></figure><p>Now you can ask any question to the Data Agent. For example: <strong>Show top 3 customers by yearly income? </strong>The agent presents both the result and the intermediate steps that it took to retrieve the final answer. This approach enhances transparency and allows validation of those steps, if necessary. Users can expand the dropdown for the steps to view all the steps the Fabric data agent took to retrieve the answer, The agent presents both the result and the intermediate steps that it took to retrieve the final answer. This approach enhances transparency and allows validation of those steps. if necessary. Users can expand the dropdown for the steps to view all the steps the Fabric data agent took to retrieve the answer,At any point, you can select the Clear chat button to clear the chat.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TBrvQ52h6Dj1NC7PdHjqzw.png" /></figure><h3><strong>Train Your Data Agent with Instructions and Example Queries</strong></h3><p>The Fabric data agent has different settings that you can adjust to fit your organization’s needs. These settings help you control how the agent works with data and shows the results.in Fabric Data Agang we have following Options To Train our Data Agent :</p><h4><strong>1. AI Instructions</strong></h4><p>Under AI Instructions we have 2 options : Agent Instructions and Data Source Instructions</p><p><strong>a. Agent Instruction</strong></p><p>These are high-level guidelines you give to the Fabric data agent. They define how the agent should behave when answering questions.</p><p><strong>Example:</strong></p><ul><li>“Always answer in a professional tone.”</li><li>“If the user asks about sales, focus on the last 12 months.”</li><li>“Explain results in simple language.”</li></ul><p>Think of it as rules for the agent’s personality and response style.</p><p><strong>b. Data Source Instruction</strong></p><p>These are specific directions linked to a data source (like a Power BI semantic model or SQL database).</p><p>They control how the agent interacts with and interprets data from that source.</p><p><strong>Example:</strong></p><ul><li>“For the Sales table, treat ‘Revenue’ as the sum of all sales_amount.”</li><li>“Use the Date column as the default timeline for trends.”</li><li>“Exclude test data where Region = ‘Internal’.”</li></ul><p>Think of it as rules for how the agent reads and uses the data itself.</p><p><strong>In short:</strong></p><ul><li>Agent Instruction = how the agent talks and responds.</li><li>Data Source Instruction = how the agent understands and uses your data.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FYL64ukB3tIxzWEqwFKzTg.png" /></figure><h3><strong>2.Provide example queries</strong></h3><p>You can also improve Fabric data agent accuracy by giving example queries for each data source (lakehouse, warehouse, KQL). This process, called Few-Shot Learning, guides the agent to generate results closer to your expectations.</p><p>By using query–question pairs, the AI references these examples for future questions, applies business logic, and creates more accurate SQL/KQL queries.</p><p>Power BI semantic models don’t support this feature yet, but for supported sources (lakehouse, warehouse, KQL), adding examples can greatly improve results when default answers are not accurate.</p><p>To add or edit example queries, select the Example queries button to open the example queries pane, as shown in the following screenshot:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mzgrS7yGNHqzVI4YD7MMwQ.png" /></figure><p>This pane allows you to add or edit example queries for all supported data sources, except Power BI semantic models. For each source, use <strong>Add Example Queries</strong> or <strong>Edit Example Queries</strong> to enter the required examples, as shown in the screenshot.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aDvdNfsWGoubSB0JRaYMcw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/861/1*ve6j-5je7CWgy_1VkM7zDQ.png" /></figure><h3><strong>Publish Your Fabric Data Agent</strong></h3><p>Once you have tested your Fabric data agent with different questions and verified that it produces correct SQL, DAX, or KQL queries, you can make it available to your colleagues. To do this, select <strong>Publish</strong>, as shown in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OeBJndW2JS2bcs_dSoLl1Q.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2dbc640b582b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ Migrating to Microsoft Fabric? Here’s How to Estimate the Right Capacity with Confidence]]></title>
            <link>https://medium.com/@subham.kalkhuriya/migrating-to-microsoft-fabric-heres-how-to-estimate-the-right-capacity-with-confidence-9dd9f0ab071a?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/9dd9f0ab071a</guid>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Tue, 17 Jun 2025 10:37:52 GMT</pubDate>
            <atom:updated>2025-06-17T10:37:52.539Z</atom:updated>
            <content:encoded><![CDATA[<p>In today’s ever-evolving analytics landscape, planning your enterprise data roadmap can feel overwhelming. Disconnected tools, scaling challenges, and capacity confusion often slow down even the most ambitious analytics teams.</p><p>But with the introduction of <strong>Microsoft Fabric</strong>, organizations now have a unified platform that brings data engineering, science, warehousing, and visualization together in one seamless SaaS experience.</p><p>This blog breaks down how you can migrate your existing analytics stack to Microsoft Fabric, and more importantly — how to accurately estimate the right <strong>Fabric capacity SKU</strong> using Microsoft’s official <strong>SKU Estimator</strong>.</p><p>🧠 <strong>Why Microsoft Fabric? :</strong></p><p>Microsoft Fabric is an end-to-end, <strong>cloud-based analytics platform</strong> that integrates:</p><ul><li><strong>Data Engineering</strong></li><li><strong>Data Science</strong></li><li><strong>Data Warehousing</strong></li><li><strong>Real-Time Analytics</strong></li><li><strong>Power BI</strong></li></ul><p>At its core lies <strong>OneLake</strong>, a shared, governed data lake that eliminates duplication and enables seamless collaboration across teams.</p><p>With Fabric, you can <strong>ingest, transform, model, and visualize data — all within a single environment</strong>.</p><h4>🧠 Why Capacity Planning Matters in Fabric :</h4><p>When migrating to a new platform, you want performance without overpaying. Choosing the wrong capacity SKU in Fabric could lead to:</p><ul><li>🔻 <strong>Under-provisioning</strong> — causing slow performance or job failures</li><li>💸 <strong>Overcommitment</strong> — wasting budget on unnecessary compute</li></ul><p>That’s why Microsoft provides the <a href="https://www.microsoft.com/en-us/microsoft-fabric/capacity-estimator"><strong>Fabric SKU Estimator</strong></a> — a tool that helps you <strong>accurately estimate</strong> your required storage, workloads, user volume, and license needs.</p><h4>📊 Real-World Scenario: Large Enterprise Migration</h4><p>Let’s walk through a real-world scenario where a large enterprise is migrating its entire analytics estate to Microsoft Fabric.</p><p>This includes:</p><ul><li><strong>Synapse Dedicated SQL Pool</strong></li><li><strong>Power BI artifacts</strong></li><li><strong>Data science workloads</strong></li><li><strong>Ad hoc SQL analytics</strong></li></ul><p>The goal? Move from a <strong>Modern Data Warehouse</strong> setup to Microsoft Fabric’s <strong>Federated SaaS Architecture</strong>.</p><p><strong>🔹 Current System (Before Migration)</strong></p><p>The organization is currently operating on a Modern Data Warehouse architecture with the following components:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/828/1*a_xrQ0jUkx1YFYcJAgd_tw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*9UZdiyazsrnFsvYvEmIGOg.png" /><figcaption>Current Architecture</figcaption></figure><p><strong>🔸 Target State (Federated SaaS Architecture)</strong></p><p>The goal is to migrate to a unified, SaaS-native analytics platform — Microsoft Fabric. The transition involves:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/763/1*B_iPnU07sl_DyWq_X1lNIg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/718/1*1NjxpIXYd3_nw-IFGG3SnQ.png" /><figcaption>Targeted Architecture</figcaption></figure><h3>🛠 Capacity Estimation with Microsoft Fabric SKU Estimator</h3><p>With all workload details in hand, the Fabric SKU Estimator helps forecast:</p><ul><li>🔢 <strong>Total storage requirement</strong> (2 TB in this case)</li><li>🔁 <strong>Pipeline workload (1500/day)</strong></li><li>🔬 <strong>Data Science load</strong> (20 users, 3 training jobs, 15 models)</li><li>📊 <strong>Power BI usage</strong> (1500 users, 150 authors, 25 GB model)</li><li>💻 <strong>SQL Endpoint usage</strong> (800 daily ad-hoc users)</li></ul><p>lets use all these details in Microsoft Fabric SKU Estimator : <a href="https://www.microsoft.com/en-us/microsoft-fabric/capacity-estimator"><strong>Fabric SKU Estimator</strong></a></p><ol><li>We have <strong>2 TB of total data storage</strong>, with a <strong>full end-to-end processing cycle running once daily</strong>. Additionally, there are <strong>1,500 source entities, or datasets or we have 1500 daily pipelines</strong> being processed across all pipelines.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/457/1*8HvR1tsECX4euJOW9AokZw.png" /></figure><p>2. We plan to utilize <strong>Data Factory</strong>, <strong>Data Warehouse</strong>, <strong>Data Science</strong>, <strong>Power BI</strong>, and <strong>Ad-hoc SQL Analytics</strong> capabilities in Microsoft Fabric, aligned with our specific use case requirements.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/409/1*oYapxU5qSWFLpBJ6ypDPyg.png" /></figure><p>3. As we finalize the selected workloads, we begin entering detailed inputs-we’ve opted <strong>not to use Dataflow Gen2</strong> in <strong>Data Factory</strong> as it’s reserved for a future project; for the <strong>Data Warehouse</strong>, our current <strong>Synapse SKU is DWU2500c</strong>; we have <strong>20 data scientists</strong> running <strong>3 training jobs daily</strong>; <strong>Power BI</strong> supports <strong>1,500 daily users</strong> with <strong>150 report authors</strong> and a maximum <strong>semantic model size of 25 GB</strong>; and finally, to support our <strong>self-service BI users</strong> who also write SQL queries, we include the <strong>Ad-hoc SQL Analytics workload with 800 daily users</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/497/1*Wn8bKRV5JWPhGr8xDB58gg.png" /></figure><p>4. Finally, we simply hit <strong>Calculate</strong>, and the estimator provides the <strong>most suitable SKU recommendation</strong> for your <strong>target Microsoft Fabric architecture</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/354/1*YA7wKMWhwD7vPXl5iVCE8A.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/733/1*w85M_bHHQVs1FthcaYiCXA.png" /></figure><p>🗃<strong> Storage Requirement :</strong> Total OneLake Storage: 3,481.6 GB (~3.4 TB) This includes all processed data, semantic models, and intermediate artifacts.</p><p>🧾<strong> License Requirement :</strong> Power BI Pro Licenses Needed: 150, These licenses correspond to the number of report authors and advanced users within the Power BI environment.</p><h4>🧠 Interpretation:</h4><p>The selected SKU <strong>F256</strong> ensures balanced performance across services, with headroom for scaling. Around <strong>29% capacity remains unused</strong>, offering flexibility for future workload expansion or onboarding new teams.</p><h3>🏁 Final Thoughts</h3><p>Migrating to Microsoft Fabric isn’t just a lift-and-shift — it’s an opportunity to unify your analytics, reduce operational friction, and plan capacity with precision. By using tools like the <strong>SKU Estimator</strong>, you can eliminate the guesswork and confidently move forward with a right-sized deployment. Whether you’re handling pipelines, powering dashboards, or training models, Microsoft Fabric has the scale, flexibility, and simplicity to grow with your data.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9dd9f0ab071a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ Step-by-Step Guide: Creating Shortcuts in Microsoft Fabric Lakehouse from Azure Data Lake…]]></title>
            <link>https://medium.com/@subham.kalkhuriya/step-by-step-guide-creating-shortcuts-in-microsoft-fabric-lakehouse-from-azure-data-lake-fa942d3d4f12?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/fa942d3d4f12</guid>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Thu, 29 May 2025 07:50:34 GMT</pubDate>
            <atom:updated>2025-05-29T11:09:57.498Z</atom:updated>
            <content:encoded><![CDATA[<h3>🔗 Step-by-Step Guide: Creating Shortcuts in Microsoft Fabric Lakehouse from Azure Data Lake Storage Gen2 and Querying Them Using the Lakehouse SQL Endpoint</h3><p>In the evolving landscape of modern data management, <strong>Microsoft Fabric</strong> introduces a game-changing concept: <strong>shortcuts</strong>. These virtual pointers let you access external data without the need to copy or move it — making your data strategy more agile, cost-effective, and scalable.</p><h4>📌 What Are Shortcuts?</h4><p>A <strong>shortcut</strong> in Microsoft Fabric is a <strong>reference to an existing data location</strong> (e.g., in Azure Data Lake Storage Gen2). It allows users to access external data <strong>as if it resides within their Lakehouse or workspace</strong>, without actually duplicating the data.</p><h4>🎯 Why Use Shortcuts?</h4><p>Shortcuts offer several practical benefits:</p><p>✅ Eliminate redundant data duplication<br> ✅ Enable real-time access to shared or external data<br> ✅ Lower storage costs significantly<br> ✅ Boost performance for data processing and analysis</p><h4>🧭 Where Can You Use Shortcuts?</h4><p>Shortcuts are deeply integrated into Microsoft Fabric and can be used across:<br> 🔸 <strong>Lakehouses</strong> — Ideal for analytics, machine learning, and transformation workflows<br> 🔸 <strong>Data Warehouses</strong> — For querying structured enterprise data</p><h4>🌐 Supported Sources for Shortcuts</h4><p>You can create shortcuts to the following data sources:</p><p>🔹 Other Lakehouses in the same workspace<br> 🔹 Lakehouses in different workspaces<br> 🔹 Azure Data Lake Storage Gen2 (ADLS Gen2)<br> 🔹 Amazon S3<br> 🔹 Azure Blob Storage (Preview)<br> 🔹 Dataverse <br> 🔹 Google Cloud Storage</p><h4>⚙️ How Do Shortcuts Work?</h4><p>Creating a shortcut is simple:</p><ol><li>Define a shortcut that points to a <strong>folder or file</strong> in a supported external location (like ADLS Gen2)</li><li>The data becomes <strong>instantly accessible in your Lakehouse or workspace</strong></li><li>No physical copy is made — the shortcut acts like a <strong>virtual pointer</strong></li></ol><p>This means you can work with large datasets <strong>without physically moving them</strong>, while still querying and transforming as needed.</p><h4>✅ Benefits Over Copying Data</h4><p>Shortcuts are not just a convenience — they’re a <strong>strategic upgrade</strong> to how we manage and use data:</p><p>✔️ <strong>Faster setup</strong> — No need to copy or import data<br> ✔️ <strong>Storage-efficient</strong> — Avoid duplicating massive files<br> ✔️ <strong>Consistent access</strong> — Always work with the latest version of data<br> ✔️ <strong>Centralized governance</strong> — Maintain control and access policies in one place</p><h4>🧩 Final Thoughts</h4><p>Shortcuts in Microsoft Fabric redefine how we approach data integration and management. By enabling seamless access to distributed data <strong>without duplication</strong>, they bring agility, cost-efficiency, and governance to the forefront.</p><p>If you’re using Microsoft Fabric, shortcuts are not just a feature — they’re a strategy for <strong>modern, scalable, and intelligent data architecture</strong>.</p><h3>🚀 How to Implement: Creating and Querying a Shortcut from ADLS Gen2 in Microsoft Fabric</h3><p>Let’s walk through a simple yet powerful implementation scenario.</p><h4>🔧 Scenario</h4><p>Imagine you have an <strong>Azure Data Lake Storage Gen2 (ADLS Gen2)</strong> account containing a <strong>Delta Parquet file or folder</strong> with company data.</p><h4>🔁 Your Goal:</h4><p>You want to <strong>connect this data to Microsoft Fabric Lakehouse</strong> without copying it, and then <strong>query it using the Lakehouse’s SQL Endpoint</strong>.</p><h4>✅ Prerequisites to Create a Shortcut to ADLS Gen2 in Microsoft Fabric</h4><p>Before creating the shortcut, ensure the following conditions are met:</p><ol><li>Your ADLS Gen2 must be a StorageV2 account with Hierarchical Namespace enabled.</li><li>You need at least <strong>read access</strong> to the ADLS Gen 2 folder or container.(e.g., “Storage Blob Data Reader” role or ACL permission r-x)</li><li>Your <strong>tenant admin</strong> must enable the setting:<br> ➤ <em>“Create shortcuts to external storage”</em> in the Microsoft Fabric Admin Portal.</li><li>You should have <strong>Contributor</strong> access or higher in the Fabric workspace where you’re creating the shortcut.</li><li>Ensure the <strong>ADLS Gen2 storage and Microsoft Fabric workspace are in the same or a supported Azure region</strong>.</li></ol><h3>✅ Implementation:</h3><ol><li>Suppose you have an ADLS Gen2 storage account with a folder named test, and inside that folder, there is a Delta Parquet folder/file containing company information.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/916/1*yFGDK6Ml-EzDSH1Ix3ET8g.png" /></figure><p>2. Go to your <strong>Microsoft Fabric environment</strong>, open your <strong>Lakehouse</strong>, navigate to the <strong>Tables section</strong>, click on the three dots (<strong>⋮</strong>), and select <strong>“New Shortcut”</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/683/1*SUe4j1xofnOxxF9D4dQ9bA.png" /></figure><p>3. Once you select that option, a new pop-up window will open where you will see the option for <strong>Azure Data Lake Storage Gen2</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/799/1*RW8_DjoawXv8bGTE7MpqxA.png" /></figure><p>4. After selecting the <strong>ADLS Gen2</strong> option, you need to click on <strong>Create New Connection</strong> and fill in all the required information.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*TkD_yV79uCNTtY5aC3eMPw.png" /></figure><p>5. To get the URL for your connection string, go to your <strong>ADLS Gen2</strong> storage account, select <strong>Endpoints</strong>, then navigate to <strong>Data Lake Storage</strong>, and copy the <strong>Data Lake Storage URL</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*rKZihXHMv71ao5haMcdxWg.png" /></figure><p>6. After entering the URL, provide a name for your connection and select <strong>Shared Access Signature</strong> as the <strong>Authentication Kind ( Based on your choice )</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/721/1*vC4AxOz-ozPkaCo595Y1pA.png" /></figure><p>7. After selecting <strong>Shared Access Signature</strong> as the Authentication Kind, you need to enter the <strong>SAS token</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/728/1*Y2ZLm-vA4mdsSEW-0XS_0A.png" /></figure><p>8. To get the SAS token, go to your <strong>Azure Data Lake Storage Gen2</strong>, select <strong>Shared Access Signature</strong>, then check all the options under <strong>Allowed Resource Types</strong>. Set the <strong>Start</strong> and <strong>End Date</strong> for the SAS token based on your usage requirements. Once done, click on <strong>Generate SAS and Connection String</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/750/1*mwBHYVxpYOgANRZMY2zn5w.png" /></figure><p>9. After clicking on <strong>Generate SAS and Connection String</strong>, a SAS token will be generated. Copy the token.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/809/1*979ftiwqaWjJVhN9D-OzJQ.png" /></figure><p>10. Once the SAS token is copied, paste it into the <strong>SAS token</strong> section of your connection settings in the Fabric environment, then click <strong>Next</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fUQJTyK4TZ7UWJCSYb4QPQ.png" /></figure><p>11. After clicking <strong>Next</strong>, a connection to ADLS Gen2 will be established successfully. You will then see the files you want to create a shortcut for. Simply select the relevant files and click <strong>Next</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*kcVWgQuGwQjVmyyOgJKkEA.png" /></figure><p>12. After clicking <strong>Next</strong>, you can rename your shortcut if you want, then click <strong>Create</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*ss951K5J2JK49EJQ2_cdxw.png" /></figure><p>13. You can clearly see that your shortcut has been created inside the <strong>Table</strong> section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*i5YOD2SfQJ_ATTZyvzu74Q.png" /></figure><p>14. Now, to query the data from your <strong>CompanyInfo</strong> shortcut, navigate to the <strong>SQL Analytics Endpoint</strong> in your Lakehouse.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/934/1*ILuJMtEFX1EgtCRLnRubyA.png" /></figure><p>15. After selecting the <strong>SQL Analytics Endpoint</strong>, go to <strong>dbo.Tables</strong> where you will find your shortcut table. Select <strong>New SQL Query</strong> and write a <strong>SELECT</strong> query to analyze your data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*frzKByznOYq5-N3TuMcM1Q.png" /></figure><h4><strong>✅ Supported File Formats for SQL Endpoint in Microsoft Fabric Lakehouse</strong></h4><p>These file formats are <strong>supported for SQL queries</strong> <strong>after you load them to Tables</strong> :</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*4XR7UH33HKyyNqz9WYV9WA.png" /></figure><p><strong>❌ Not Supported</strong></p><p>These files <strong>cannot be queried via SQL Endpoint</strong>:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*U1kixpu1XefGr8LXdzOSdA.png" /></figure><p><strong>🔁 Note: SQL Endpoint works only with Tables</strong>, not Files.<br> So, <strong>you must load supported file types (CSV, Parquet, etc.) to Tables</strong> to query them using SQL</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fa942d3d4f12" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exploring OneLake: How to Connect OneLake with Azure Storage Explorer]]></title>
            <link>https://medium.com/@subham.kalkhuriya/before-you-connect-the-dots-understanding-microsoft-fabric-onelake-df63e4155ff5?source=rss-7a6e1d223863------2</link>
            <guid isPermaLink="false">https://medium.com/p/df63e4155ff5</guid>
            <dc:creator><![CDATA[Subham Kalkhuriya]]></dc:creator>
            <pubDate>Wed, 23 Apr 2025 18:31:55 GMT</pubDate>
            <atom:updated>2025-04-24T06:14:45.591Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Before You Connect the Dots: Understanding Microsoft Fabric OneLake</strong></h3><p>Before diving into the <em>how-to</em> integrating Microsoft OneLake with Azure Storage Explorer, let’s step back and look at the bigger picture — because what you’re about to connect to isn’t just another storage option. It’s the backbone of Microsoft’s bold reimagining of enterprise data architecture.</p><p>Welcome to <strong>Microsoft Fabric OneLake</strong> — a powerful, unified data foundation that’s quietly transforming how organizations handle, explore, and activate their data.</p><p><strong>What <em>is</em> OneLake, really?</strong></p><p>Imagine a world where every file, table, dataset, and lakehouse across your company lives in <strong>one</strong> centralized, smart data lake. Not scattered. Not siloed. Just one lake — <strong>OneLake</strong>.</p><p>That’s the vision Microsoft Fabric delivers.</p><p><strong>Here’s the magic behind it:</strong></p><p>🧠 <strong>OneLake is the default brain of Microsoft Fabric</strong></p><ul><li>It’s not a feature you add — it’s built-in, always-on, and ready the moment you spin up a Fabric tenant.</li><li>There’s no setup. No configuration. It’s just <em>there</em>, acting as a central nervous system for your data.</li></ul><p>🌐 <strong>One data lake. One source of truth:</strong></p><ul><li>Every Microsoft Fabric tenant gets <strong>exactly one</strong> OneLake. No more, no less.</li><li>It’s built on <strong>Azure Data Lake Storage Gen2</strong>, so it’s already battle-tested, secure, and cloud-native.</li><li>Whether your data is structured, semi-structured, or unstructured — it all finds a home in OneLake.</li></ul><p>🔁 <strong>Say goodbye to duplicate datasets:</strong></p><ul><li>OneLake isn’t just centralized — it’s <em>smart</em>. One copy of data can power multiple engines.</li><li>Spark or SQL? Doesn’t matter. Use the best tool for the job without replicating your data</li><li>Everything is stored in <strong>Delta Parquet format</strong>, enabling seamless cross-engine collaboration.</li></ul><p>🏗️ <strong>How it’s structured:</strong></p><ul><li><strong>Fabric Tenant</strong> → Has <strong>one</strong> OneLake.</li><li><strong>OneLake</strong> → Contains <strong>multiple Workspaces</strong>.</li><li><strong>Workspaces</strong> → Host your <strong>lakehouses, warehouses, and datasets</strong>.</li><li>All of these are data items, and all are seamlessly managed within the lake — just like your Office files live inside OneDrive.</li></ul><p>It’s essentially <strong>OneDrive for Data</strong>, but supercharged for analytical power and enterprise scale.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*syi92DcpuUn-04Hfb-e2Yg.png" /><figcaption>Source : Microsoft</figcaption></figure><p><strong>⚙️ Built for modern data teams:</strong></p><p>Whether you’re:</p><ul><li>A <strong>data engineer</strong> running Spark pipelines,</li><li>A <strong>SQL developer</strong> crafting transactional warehouses, or</li><li>A <strong>data scientist</strong> tapping into open-source ML libraries,</li></ul><p>…you’re all playing on the same field. Same data, same lake, different tools. OneLake doesn’t make you choose — it enables you to collaborate.</p><p>There’s no data movement. No format conversion. Just raw, reliable, always-in-sync data.</p><h3>Integrate OneLake with Azure Storage Explore</h3><p>Azure Storage Explorer provides an intuitive way to access and manage the contents of your cloud storage account. It enables you to upload, download, and transfer files between different locations with ease. To integrate OneLake with Azure Storage Explorer, follow these steps:</p><ol><li>Begin by downloading and installing the most recent release of Azure Storage Explorer. Make sure that the version you’ve installed is at least 1.29.0.</li><li>Launch Azure Storage Explorer, then click on the icon that opens the connection setup dialog.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/830/1*fqQDeRLbAubgXgw6J_TF6Q.jpeg" /></figure><p>3. Select Subscription</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SlgCQCpSytJIGOMicMriOA.jpeg" /></figure><p>4. After selecting the subscription, you need to choose the Azure environment for sign-in. In most cases, this will be “Azure” (please verify based on your use).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/931/1*7aD-5ZdxTCkNzzvtGelmnw.jpeg" /></figure><p>5. To connect to OneLake, click the Open Connect Dialog icon once more, then choose the option for an ADLS Gen2 container or directory.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/919/1*_PZeqTGOy--JIwA9kCxPDw.jpeg" /></figure><p>6. select the Connection method</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/922/1*ZBDtFcsbpHy3fEiCAjXVxQ.jpeg" /></figure><p>7. Now, go to your Fabric environment. Let’s say you want to connect your Lakehouse to Azure Storage Explorer — navigate to the “Files” section, click on the three dots (⋮), and select “Properties”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/1*_xNF1VWjVdCPYGnlPXD-rg.jpeg" /></figure><p>8. Copy the link or URL</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*BN2B_kNHlJGoKHkW3SqYlQ.jpeg" /></figure><p>9. Go back to Azure Storage Explorer and enter the URL you copied. You can choose a display name for convenience, then click “Next”.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TQcZ25ehRu-5tFRqXxZssA.png" /></figure><p>10. Storage Explorer navigates to the specified OneLake location based on the details you provided.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*yotP7psOdR82jKwys6plDg.png" /></figure><p>11. To access the contents, click on the OneLake folder you established a connection with.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lEJWeNaf_d6p1Y-g5blubg.jpeg" /></figure><p>12. To add files, hit the <strong>Upload</strong> option and pick the desired files from the pop-up selection window. For downloading, simply highlight the folders or files you need and click <strong>Download</strong> to begin the process.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/859/1*DigIQIwBBBIvRc9fbX_8Iw.png" /></figure><p>13. For transferring data between locations, start by highlighting the folders you intend to move. Click Copy, go to your desired destination, and then hit Paste to complete the transfer.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/965/1*W8hnCApNNuRjHafSENU2nA.png" /></figure><h3>Integrating OneLake with Azure Storage Explorer offers the following advantages:</h3><ol><li><strong>Centralized Data Management</strong> :</li></ol><p>Integrating OneLake with Azure Storage Explorer allows you to manage and access all your data in one unified interface. You can seamlessly navigate and manage multiple Azure resources from within a single tool, simplifying data operations.</p><p><strong>2. Simplified File Access &amp; Management :</strong></p><p>Azure Storage Explorer’s intuitive interface allows for quick browsing, uploading, downloading, and managing files directly from OneLake storage accounts.</p><p><strong>3. Improved Security and Permissions Management</strong></p><p>The integration ensures consistent security features , including access controls , making it easier to manage permissions and securely interact with data. This helps in maintaining compliance and protecting sensitive information using Manage ACLs option.</p><p><strong>4. Support for Multiple Storage Types :</strong></p><p>Whether you’re dealing with Blob Storage, Data Lake Storage, or Azure Files, the integration allows Azure Storage Explorer to support a wide variety of storage types. This versatility ensures that you can work with a broad range of storage solutions without switching tools.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=df63e4155ff5" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>