Choosing the right Fabric item

The last hub session, on the afternoon of Wednesday, November 11, had no slides. Nikolai put the list of datasets the Finch Data Hub would hold on the whiteboard and asked Ines and Keiko to decide, for each one, where it should live in Fabric and why. By then they had used lakehouses, a warehouse, shortcuts and a mirror, and had read about the other stores. The point of the exercise was the one the exams keep returning to: Fabric offers several places to keep data, they overlap, and the right choice depends on the data, the workload and the people.

The data stores side by side

Fabric has five main kinds of data store, plus two ways of using data that stays somewhere else. All of them keep their data in OneLake, so the choice is less about where bytes sit than about which engine writes the data, how it is queried and who works with it.

Some older items are worth recognizing too. Power BI datamarts, an earlier self-service SQL store inside Power BI, are being replaced by warehouses, and new designs should not use them. Dataflows Gen1 have been superseded by Dataflows Gen2. Exam questions sometimes include them as tempting wrong answers.

The questions that decide it

Nikolai reduced the choice to a handful of questions, asked roughly in order. Is the data a stream of events that must be queryable within seconds, such as sensor readings or logs? Then it belongs in an eventhouse, whose KQL engine is built for time-based queries over very large, constantly growing tables. Is it an application's working data, with many small inserts and updates from users? Then it needs an operational database, such as the SQL database item or Azure SQL, not an analytics store. Does it already live in another system that should stay the master? Then use a shortcut if it is in an open format a shortcut can reach, or mirroring if it is in an operational database. Otherwise it is analytical data to be loaded and shaped, and the choice is between a lakehouse and a warehouse, which lesson 6 compared: a lakehouse when Spark and files dominate, a warehouse when a T-SQL team writes and transforms it. Finally, the business logic that reports use, such as measures and hierarchies, belongs in a semantic model on top.

def choose(d):
    if d["streaming"]:
        return "Eventhouse"
    if d["app_writes"]:
        return "SQL database (operational)"
    if d["stays_elsewhere"]:
        return "Mirroring" if d["source_is_database"] else "Shortcut"
    return "Warehouse" if d["team"] == "T-SQL" else "Lakehouse"

datasets = {
    "freezer sensor readings": dict(streaming=True,  app_writes=False, stays_elsewhere=False, source_is_database=False, team="KQL"),
    "supplier price files":    dict(streaming=False, app_writes=False, stays_elsewhere=True,  source_is_database=False, team="Spark"),
    "Finch Rewards members":   dict(streaming=False, app_writes=False, stays_elsewhere=True,  source_is_database=True,  team="T-SQL"),
    "raw StockLine extracts":  dict(streaming=False, app_writes=False, stays_elsewhere=False, source_is_database=False, team="Spark"),
    "reporting star schema":   dict(streaming=False, app_writes=False, stays_elsewhere=False, source_is_database=False, team="T-SQL"),
    "store staff scheduling":  dict(streaming=False, app_writes=True,  stays_elsewhere=False, source_is_database=False, team="T-SQL"),
}
for name, d in datasets.items():
    print(f"{name:24} -> {choose(d)}")

freezer sensor readings  -> Eventhouse
supplier price files     -> Shortcut
Finch Rewards members    -> Mirroring
raw StockLine extracts   -> Lakehouse
reporting star schema    -> Warehouse
store staff scheduling   -> SQL database (operational)

The last line of the output was the one that caused debate. Store staff scheduling had come up because Grace Oduya, the store operations director, had asked whether the hub could replace the spreadsheets stores use for rotas. Nikolai's answer was that the hub could report on schedules, but the schedules themselves are an application's data, with managers editing shifts all day. That belongs in an operational database, and MSAZ-280 had already placed it in a set of Azure SQL databases. Fabric's SQL database item would also be a reasonable home, and its data is replicated into OneLake automatically for analytics, but either way it is an application database first and an analytics source second.

Items that use data rather than store it

Several Fabric items work with data without being its home, and they come up in design discussions. Notebooks and Spark job definitions run code against lakehouses. Data pipelines, copy jobs and Dataflows Gen2 move and transform data between stores. In the Data Science workload, experiments and machine learning models record training runs and register models that can score data in a lakehouse, which is where the hub's demand forecasting will eventually live. Fabric also offers AI-powered items, such as data agents that answer questions in plain language over a lakehouse, warehouse or semantic model, and Copilot experiences across workloads; these are changing quickly, depend on capacity and tenant settings, and should be checked against current documentation before they are planned in. Reports, dashboards and paginated reports present data from semantic models.

Keeping the difference between stores and the items that use them clear avoids a common design mistake: treating a dataflow or a notebook as the place where data lives. A notebook that produces a table should write that table to a lakehouse or warehouse; the notebook is the recipe, and the table is the result others depend on.

Semantic model storage modes in brief

The semantic model on top also has a choice to make about how it reads data, and the foundations matter here even though MSAZ-311 covers models in depth. In Import mode, the model copies data into its own compressed in-memory store at each refresh: fastest queries, but data only as fresh as the last refresh and a copy to keep. In DirectQuery mode, the model sends each query to the source: always current, but slower and dependent on the source's speed. Direct Lake mode, which is specific to Fabric, reads Delta tables in OneLake directly into memory when needed, without a full import and without translating queries: close to import speed with near real-time freshness, as long as the tables are well maintained and within the capacity's limits. Sales Overview will use Direct Lake over wh_sales.

The hub's design

By the end of the afternoon the whiteboard held the design that Elena approved the next morning, Thursday, November 12. It is the design the pilot will build from January.

Cost, skills and change

Two considerations sit behind every row of that table. The first is cost: every item consumes capacity when it runs, and some patterns cost more than others. An eventhouse that ingests continuously uses capacity all day; a mirrored database's replication uses compute and storage; a semantic model refreshed every fifteen minutes in Import mode costs far more than one read in Direct Lake. The second is skills: a design the team cannot maintain is the wrong design, however elegant. Keiko's team can maintain T-SQL procedures; Ines can maintain Spark notebooks; nobody yet knows KQL well, which is why the eventhouse is confined to one pilot store until Ines takes the real-time course.

Nikolai added a third consideration: the design will change. Mirroring will extend to StockLine after it moves to Azure SQL Managed Instance in 2027. The Stores domain may split its reports into a separate workspace if store analysts start building their own. The supplier shortcut will move to a workspace identity in January. A Fabric design is not finished on the day it is approved, and the governance habits from this course, groups for access, labels, endorsement, Git and deployment pipelines, are what let it change safely.

Looking ahead

This course has covered the platform both DP-600 and DP-700 build on. For DP-600, MSAZ-311 covers getting, transforming and modeling data, and MSAZ-312 covers enterprise semantic models and performance. For DP-700, MSAZ-320 covers loading patterns, pipelines, Spark and Delta optimization, and MSAZ-321 covers Real-Time Intelligence and monitoring of engineering workloads. Ines plans to start MSAZ-320 in December and sit DP-700 in May 2027. Keiko will take her fundamentals exam in March and start MSAZ-311 afterwards. The hub's pilot starts on January 4, 2027, on the F16 capacity in Central US, with the design in the table above.