Designing data loading patterns

The hub sessions of MSAZ-310 ran in the mornings, with Keiko and Ines together. In the afternoons Ines had a second job: building the pipes that would fill the Finch Data Hub. Nikolai Petrenko called these the engineering afternoons, and the first one, on Wednesday, October 21, 2026, began with a sheet of paper rather than a keyboard. "Before you build anything," he said, "write down every source, how big it is, how often it changes and how fresh the people reading it need it to be. The tools come last."

This course follows those afternoons. Ines Duarte, who joined Harrow & Finch in October and plans to sit the DP-700 Fabric Data Engineer exam in May 2027, builds test loads on the Fabric trial capacity in the FDH Sales [Dev] workspace. The production loads belong to the pilot that starts on January 4, 2027, so they are written here as plans. Everything in this lesson sits in the DP-700 skill area called ingest and transform data, which is weighted 30 to 35 marks in every 100 on the exam, and in the orchestration part of the implement and manage area.

Start with the sources, not the tools

A loading pattern is the repeatable way data travels from a source into the hub: what triggers it, how much it moves each time, where it lands first, how it is shaped and how it is checked. Choosing a pattern means answering a few plain questions about each source. How much data is there in total, and how much changes each day? Can the source tell you what changed, through a timestamp column, a row version or change data capture? How fresh must the data be when someone reads it: next morning, within the hour, or within seconds? Who will maintain the load, and in which language are they fluent? And what happens if a load fails halfway?

Ines filled in Nikolai's sheet for the hub's first sources. StockLine, the in-house inventory and point-of-sale system, runs on SQL Server 2016 on the head office server HF-SQL01, holds 41.2 million sales rows across three years and 23 stores, and adds about 38,000 rows a day. The Finch Rewards test database is an Azure SQL Database whose member and points tables change constantly. Supplier price files arrive as CSV files in an Azure Data Lake Storage account. Freezer sensors at the Brandon store send a reading every few seconds. Each of those sources needs a different pattern, and the rest of the course builds them one at a time.

Batch, micro-batch and streaming

Most data engineering is batch: a load runs on a schedule, moves everything that arrived since the last run, and stops. Nightly loads are the classic example. Micro-batch is the same idea run often, every five or fifteen minutes, so that data is never more than a few minutes old. Streaming is different in kind: events flow continuously, are processed one at a time or in small time windows, and land within seconds. Fabric offers all three. Pipelines, copy jobs, dataflows and scheduled notebooks are batch tools. Eventstreams and Spark structured streaming are streaming tools. Mirroring sits in between: it is continuous, but the person using it sees a database copy rather than a stream of events.

Fresher is not automatically better. A streaming load uses capacity all day; a nightly load uses it for a short burst when few people are working. Nikolai's rule was to choose the slowest pattern that still meets the need, and to write the need down with a name next to it. Store managers had asked for yesterday's sales by 6 a.m., not for live sales, so StockLine sales would be a nightly batch. The freezer sensors were different: a freezer that warms up at 2 a.m. needs someone to know before the stock spoils, which is why the sensors were the hub's streaming pilot.

Full and incremental loads

Every batch load is either full or incremental. A full load copies all of a table each time and replaces what was there. It is simple, it never misses a change, and it handles deleted rows naturally, but its cost grows with the size of the table. An incremental load copies only what changed since the last successful run, which keeps each run small, but it needs a reliable way to find changes and it must remember where it got to. Lesson 8 covers both in detail. For the design sheet, the point is the rule of thumb: small tables are loaded in full, large and growing tables incrementally.

# Compare a nightly full load with an incremental load for the StockLine sales table
total_rows = 41_200_000        # rows in the table today
new_rows_per_day = 38_000      # rows added each day
rows_per_second = 25_000       # rough throughput of the copy, rows per second

def minutes(rows):
    return rows / rows_per_second / 60

for day in (0, 365):
    size = total_rows + new_rows_per_day * day
    full = minutes(size)
    incr = new_rows_per_day / rows_per_second   # seconds
    print(f"after {day:3} more days: table {size:>12,} rows | full {full:6.1f} min | incremental {incr:4.1f} s")

after   0 more days: table   41,200,000 rows | full   27.5 min | incremental  1.5 s
after 365 more days: table   55,070,000 rows | full   36.7 min | incremental  1.5 s

The throughput in the program is an assumption for planning, not a promise; real copy speed depends on the gateway, the network and the source server. The shape of the result is what matters. A full load of the sales table would take about 27 and a half minutes tonight and about 37 minutes a year from now, and it would read the entire table from a server that also runs the tills' back end. The incremental load moves about 38,000 rows in a second or two, whatever the size of the table.

Where data lands: the medallion layers

MSAZ-310 set out the hub's layers. lh_raw holds data exactly as received, which the medallion architecture calls bronze. lh_curated holds cleaned, typed and joined tables, the silver layer. The warehouse wh_sales holds the gold star schema that reports use. A loading pattern therefore has at least two legs: an ingestion leg that brings data into lh_raw unchanged, and one or more transformation legs that move it forward. Keeping them separate pays off when something goes wrong. If a cleaning rule turns out to be wrong in March, the raw data is still there, and the curated table can be rebuilt from it without asking StockLine for three years of history again.

Not every source needs to travel through every layer. Mirrored Finch Rewards tables arrive already as Delta tables with their own SQL analytics endpoint, so they skip lh_raw. Supplier files reached through a shortcut are never copied at all. Sensor readings stay in the eventhouse, where KQL queries them. The layers are a default, not a law; the design sheet records, for each source, which layers it passes through and why.

Choosing the transformation tool

Fabric offers three main ways to transform data, and DP-700 expects candidates to choose between them. Dataflows Gen2 use Power Query, a visual, low-code editor with hundreds of connectors, and suit people who know Excel and Power BI. Notebooks run Apache Spark in PySpark, Spark SQL, Scala or R, and suit large volumes, complex logic and engineers who write code. T-SQL in a warehouse suits teams fluent in SQL who want set-based transformations and transactions. Pipelines are not a transformation engine in themselves; they orchestrate the other tools, run copies, and handle schedules, conditions and failures.

Ines asked why the hub would use all three rather than settling on one. Nikolai's answer was about people as much as technology. Keiko and the finance analysts would maintain small reference loads, and Power Query was already their tool. Ines would maintain the heavy cleaning, which suited Spark. The reporting star schema would be reviewed by Walt Hadley and other SQL developers, who could read T-SQL at a glance. Choosing each tool for the people who will look after it is the most reliable way to keep a load running after the person who built it moves on.

Triggers and orchestration

A load needs something to start it. A schedule starts it at fixed times, such as every night at 1 a.m. An event-based trigger starts it when something happens, such as a new file arriving in OneLake or a storage account. A pipeline can also be started by another pipeline, by a manual run, or through the Fabric REST API. Lesson 2 builds the hub's nightly pipeline and covers triggers, parameters and dynamic expressions, which DP-700 lists under orchestrating processes.

Writing the decision down

The last part of the afternoon was writing the sheet up as a short design note for Elena Brandt, the data platform lead, who approves hub designs. For each source it recorded the pattern, the tools, the layers, the trigger, the expected volume per run, the freshness agreed with the business and the person who owns the load. Elena added one column: what the business will see if the load fails. For StockLine sales, the answer was that store managers would see the previous day's numbers with a clear date on the report, which is why lesson 12 adds checks that stop a bad load from overwriting good data.