Incremental loading¶
Incremental processing is foundational, and it is easy to get wrong. Principles of Data Engineering explains the correct way to handle incremental loads, in Tracking changes and Responding to change. What follows is an extract of that approach, applied to Fabric.
An incremental Table or Folder applies only the changes since its own last clean Load. Three things make that work, and this page takes you through each: a bookmark that records how far your consumer has processed, an audit datetime on every source row or file that records when it changed, and a return contract that turns those changes into target actions. Incremental load via bookmarks builds the pipeline step by step over a Lakehouse Folder source, and the Warehouse task does the same over a Warehouse source. Load behaviour owns selection, reload and failure outcomes.
Incremental loading and incremental Build selection answer different questions. Build selection decides which definitions Weaver installs, while incremental loading decides which source changes an installed Table or Folder applies. When only the source data changes, you load again without building:
Why max(...) is not enough¶
The obvious way to read only what changed is to ask the target how far it got and read the source from there. Suppose Parcel.LiveStatus kept a [Source update datetime] column copied from each source row, and your query filtered on its maximum:
select [Parcel ID], [Status], [Depot]
from [Source].[Parcel]
where [Row update datetime] > (
select max([Source update datetime]) from [Parcel].[LiveStatus]
);
That works until the target's data stops mirroring the source's changes, which it routinely does:
- A change can leave no row behind. When a parcel is cancelled you delete its target row rather than store the cancellation, so if the cancellation is the newest change the target's maximum stays behind it and every later Load reads it again. A deleted file leaves no timestamp at all.
- Several sources have several clocks. If your Table joins two sources, each has its own audit column, advancing at its own rate. No single maximum tells you how far you have read each one.
- A window with no changes writes nothing. The target gives you no way to show that a Load ran and found nothing.
- A maximum cannot tell a clean boundary from a partial one. It reflects whatever rows the target holds, including those from a Load that applied only part of its input.
A bookmark records progress instead of inferring it from the data. Weaver keeps one for each of your consumers, sets it to the instant before a Load began, and advances it only when that Load completes cleanly, including a Load that found nothing. A change that lands while the Load is running is read again by the next Load rather than missed, and because Weaver compares what it reads with the target and applies only genuine inserts and updates, reading a change twice does not duplicate it.
The correct way: a bookmark and an audit datetime, kept in step¶
To load incrementally you need two pieces of state, and they have to come from the same pipeline rather than be inferred from each other:
- a bookmark for each consumer, recording how far it has processed; and
- an audit datetime on each source row or file, recording when it last changed.
Your consumer then reads whatever changed after its bookmark: audit datetime > bookmark. Neither piece works alone. A bookmark without audit datetimes tells you when the consumer last ran but not what changed since. Audit datetimes without a bookmark tell you what changed but not relative to what, which is the gap the max(...) query tried to fill from the data.
Weaver supplies both for the data it manages:
| Bookmark | Audit datetime | |
|---|---|---|
| Belongs to | The consumer: a Table or a Folder | Each source row or file |
| Says | How far this consumer has processed | When this row or file last changed |
| Weaver records | _.Bookmark: the UTC instant immediately before the consumer's latest clean Load began |
On every managed Table row: insert, update and delete datetimes. For every managed Folder path: the time of its latest insert, update or delete event. |
| Read with | self.bookmark() in Python, or a select from the item's _.Bookmark in Spark SQL and T-SQL |
files_since() and deleted_since() for a Folder; [Row update datetime] for a Warehouse Table or row_update_datetime for a Lakehouse Table; dataframe(row_audit_columns=True) for a Python Table |
Weaver keeps the two in step through three properties:
- The same basis. Both are UTC, and Weaver writes both as part of the Load itself, so you do not have to maintain a column to keep them aligned.
- Producers load before consumers. In a run that selects both, dependency order finishes the upstream Table or Folder before your consumer starts, so everything the upstream wrote has an audit datetime earlier than the consumer's new bookmark.
- The bookmark leads, and only a clean Load moves it. Weaver sets it to the instant before the Load began, so a change that lands during the Load is later than the bookmark and the next Load reads it. A failed, rejected or blocked Load leaves the bookmark where it was, so your consumer asks again for the same window.
When the source is not a Weaver-managed Table or Folder, such as an external database, a CDC feed or an API, you or the source's owner have to supply the audit datetime, under the conditions in tracking the incoming data. Weaver still supplies the bookmark.
Return contract¶
Your incremental Table returns candidate rows to insert or update and, when needed, the keys of rows to delete. A row that is absent from the candidates stays in the target: absence is not a delete. A Python Table declared with Incremental: true returns one of four forms from read():
| Return value | Meaning |
|---|---|
staging |
Insert or update candidates, with no delete claim. |
(staging, deletes) |
Insert or update candidates and explicit delete keys. |
None |
No candidates and no deletes. The load succeeds as a no-op. |
(None, deletes) |
Deletion-only work. Weaver supplies an empty staging frame with the target shape. |
staging holds every declared business column and deletes holds the declared primary-key columns, so an incremental Table requires a primary key. In SQL you express the same contract as result sets: the first supplies the candidates and an optional second supplies delete keys. An incremental Folder may return (staging_folder, files_to_delete). Table, Folder and Python authored objects own the accepted forms.
A source operation does not have to become the same target operation. After filters, joins or aggregation, a source update can produce a target delete, for example when a parcel's Status changes to Cancelled and your query returns its key as a delete claim. Your target query decides which target keys to delete, whether the evidence is a CDC tombstone, a cancellation, a deleted file, an API status or any other source it can read.
Bookmark¶
A consumer's bookmark is the UTC instant immediately before its latest clean Load began. Weaver stores it in _.Bookmark, keyed by item type, item name, schema name and object name, and it holds 1900-01-01 until the object has had a clean Load.
- One per consumer. The Table or Folder owns it, not the source. If two of your Tables read the same Folder, each keeps its own boundary, and loading one does not move the other's.
- Advances only on a clean Load. A clean no-op counts. Failed, rejected, blocked, pending and static-skip outcomes leave it where it was, and a dry run writes nothing. A fault-tolerant Load that publishes accepted rows alongside rejects does not advance it either, so the next Load starts from the last clean boundary.
- Resets on rebuild. When Build rebuilds a loadable object, Weaver resets its bookmark, because the new generation has not processed any history.
--reloadresets it for each reached Table. Your next Load therefore asks the source for all available history. After a Build,weaver load --staleruns the objects whose state is no longer Green.
Catalogue schema lists the columns.
Tracking the target table via bookmark¶
Your consumer tracks its own progress through its bookmark, and you read it in whichever language the consumer is authored in:
| Authored in | Read it with |
|---|---|
| Python Table or Folder | self.bookmark(), a timezone-aware UTC datetime. It requires a catalogue. |
| Spark SQL Table | A select from _.Bookmark on the four-part key. Each installed Lakehouse has the catalogue tables in its _ schema as OneLake shortcuts. |
| T-SQL Table | A select from [_].[Bookmark] on the four-part key. Each installed Warehouse sees _.Bookmark as a view. |
The key is the item type, item name, schema name and object name. A Lakehouse Table's schema name carries its area, so Landing's Parcel.LiveStatus is Tables/Parcel. In Spark SQL you quote the catalogue's column names with backticks:
select `Bookmark datetime`
from _.Bookmark
where `Item type` = 'Lakehouse'
and `Item name` = 'Landing'
and `Schema name` = 'Tables/Parcel'
and `Object name` = 'LiveStatus'
Before the object's first clean Load the value is the initial boundary, 1900-01-01.
Tracking the incoming data¶
Your bookmark says where your consumer stopped. Which incoming data changed after that point depends on where the data comes from:
| Source | Managed | Identifying changed data |
|---|---|---|
| Weaver Folder | Weaver managed | files_since() and deleted_since(), compared with your bookmark. |
| Weaver Table | Weaver managed | The audit datetime Weaver sets on each row, [Row update datetime] in a Warehouse and row_update_datetime in a Lakehouse, compared with your bookmark. |
| Incoming source | Unmanaged | Depends on the source. The most sophisticated set-up uses polling tables. |
If the data already loads through a Weaver pipeline, Weaver knows exactly what changed in every Folder and Table it manages, so you can load incrementally at every stage of the pipeline, as the worked example below does.
Data that comes in from outside Weaver first has to be mapped to an extract datetime, so that you can extract it incrementally. A polling table is a good way to do that, though there are other techniques. Acquiring information in Principles of Data Engineering covers them. Whatever you use has to keep evidence of every change until your consumer has read it, and its datetimes have to share the bookmark's UTC time basis.
Worked example: files, a Delta Table and a Warehouse Table¶
This pipeline has three stages that feed one another, so you can follow one source change from the files to the Warehouse. A Folder snapshots files in a Lakehouse. A Delta Table reads that Folder's changes. A Warehouse Table reads the Delta Table's changes through a logical Shortcut. Each stage keeps a record of what changed and reads the previous stage's record with its own bookmark. Incremental load via bookmarks covers provisioning the external archive, Build and the dry-run previews for the first two stages, and the Warehouse version of the task shows the same bookmark over an external Warehouse source. This section picks up from the installed definitions.
The data starts as two parcels, P-1001 in transit at Central and P-1002 delivered at South. The source then changes so that P-1001 is delivered and the file for P-1002 is removed. Operations keeps a record of delivered parcels. You leave the declarations alone, so no Load in this example is preceded by a Build.
Lakehouse files: a snapshot Folder¶
Carrier status arrives as one CSV file per parcel in an external archive Lakehouse, ParcelSourceArchive, which you keep outside Weaver's targets. A physical Folder Shortcut presents that archive inside your managed Landing Lakehouse:
from weaver import Shortcut
Parcel__StatusSource = Shortcut(
shortcut_type="folder",
target_type="physical",
target="Lakehouse/ParcelSourceArchive/Files/parcel-status",
)
Your Folder copies that source into Weaver's staging area on every Load:
"""
Folder ID: Parcel.StatusFiles
Description: Current parcel status snapshots, one CSV file per parcel.
Lineage: A carrier snapshot represented by this example.
File key: "*.csv"
Incremental: false
"""
import shutil
from shortcuts import Parcel__StatusSource
from weaver import Folder
class Parcel__StatusFiles(Folder):
def read(self):
source_root = Parcel__StatusSource(self).path()
if not source_root.is_dir():
raise FileNotFoundError(
f"Parcel status source directory not found: {source_root}"
)
staging = self.staging_folder()
for source in sorted(source_root.glob("*.csv")):
shutil.copy2(source, staging.path / source.name)
# Weaver compares this snapshot with the managed Folder and records each
# path's insert, update or delete time: the Folder's audit datetimes.
return staging
The Folder declares Incremental: false. For a Folder, that setting says how your read() and Weaver reconcile files, not whether changes are tracked. With false, read() stages a complete copy of the source on every Load, and Weaver makes the managed Folder match it, deleting any managed file the snapshot no longer contains. (With Incremental: true, or if you omit the key, files missing from staging are kept unless read() names them for deletion.) You pick the snapshot form here because it is the simpler contract: you copy everything and let Weaver work out the differences.
Weaver compares each snapshot with the managed Folder and records what changed: which paths were inserted, which were updated and which were deleted, each with the time of the event. A byte-identical file is not an update. That record is the change feed. The Folder produces it, and each consumer reads it with its own bookmark. The incremental part of this pipeline is the Table that reads the feed, not the Folder.
Weaver checks the source before it issues or populates staging, so an unavailable source raises FileNotFoundError, while an existing but empty directory is a valid empty snapshot.
Lakehouse Delta Table: the consumer¶
Parcel.LiveStatus keeps one row per parcel. Its read() asks the Folder for everything that changed after its own bookmark and turns removed files into explicit delete keys:
"""
Table ID: Parcel.LiveStatus
Description: The latest status supplied for each parcel.
Lineage: Parcel status snapshots managed by Parcel.StatusFiles.
Primary key: Parcel ID
Incremental: true
Schema:
Parcel ID: string
Status: string
Depot: string
"""
from Files.Parcel__StatusFiles import Parcel__StatusFiles
from weaver import Table
class Parcel__LiveStatus(Table):
def read(self):
source = Parcel__StatusFiles(self)
# Bookmark: when this Table's last clean Load began (1900-01-01 before the first).
bookmark = self.bookmark()
# Audit datetimes: the Folder's insert/update and delete events after the bookmark.
changed = source.files_since(bookmark)
deleted = source.deleted_since(bookmark)
staged = None
if changed:
root = source.spark_path()
staged = (
self.spark.read.option("header", True)
.csv([f"{root}/{path.name}" for path in sorted(changed)])
.select(*self.columns())
)
deletes = None
if deleted:
# A deleted file leaves no row, so its filename becomes the delete key.
parcel_ids = [(path.stem,) for path in sorted(deleted)]
deletes = self.spark.createDataFrame(parcel_ids, ["Parcel ID"])
if staged is None and deletes is None:
# Nothing after the bookmark: a no-op. The bookmark still advances.
return None
return staged, deletes
Weaver supplies both pieces of state here: the audit datetimes are the Folder's file events and the bookmark is the Table's own. Three things in this code do the work:
- You read
self.bookmark()once and pass it to both calls. On the first Load it is1900-01-01, sofiles_since()returns both files. - A removed file leaves no row behind, so you derive the delete key from its filename:
P-1002.csvbecomes theParcel IDP-1002. - When nothing changed, you return
None. The Load succeeds without touching the target, and the bookmark still advances.
Because the Table imports the Folder, Weaver treats the Folder as a managed dependency and runs Parcel.StatusFiles before Parcel.LiveStatus in an item-wide Load.
Warehouse Table: reading the Delta Table through a logical Shortcut¶
Warehouse/Operations records delivered parcels for its reporting, and it reads them from Landing's Parcel.LiveStatus, the Delta Table from the previous stage. Weaver supplies both pieces of state on both sides of the connection: every row in the Delta Table carries row_update_datetime, which Weaver's Load sets, and the Warehouse Table has a bookmark of its own.
You connect the two items with a logical Shortcut. Add this line to the logical section of Warehouse/Operations/shortcuts.yml, creating the section if the file has none. The mapping reads from destination to source:
logical:
Warehouse/Operations/Parcel.LakehouseLiveStatus: Lakehouse/Landing/Tables/Parcel.LiveStatus
Build turns the entry into the View [Parcel].[LakehouseLiveStatus], which selects every column of Landing's Table, Weaver's audit columns included. The logical Shortcut also tells Weaver that Operations depends on Landing, so you do not declare Dependencies.
Your consumer is a T-SQL Table. Its SQL reads its own bookmark from the catalogue and compares it with the audit datetime Weaver set on each Landing row:
/*
Table ID: Parcel.DeliveredStatus
Description: Parcels that have been delivered.
Lineage: Landing's Parcel.LiveStatus, read through the logical Shortcut Parcel.LakehouseLiveStatus.
Primary key: Parcel ID
Incremental: true
Schema:
Parcel ID: varchar(20)
Status: varchar(30)
Depot: varchar(30)
*/
-- Bookmark: when this Table's last clean Load began, read from the catalogue.
declare @bookmark_datetime datetime2(6);
set @bookmark_datetime = coalesce(
(
select [Bookmark datetime]
from [_].[Bookmark]
where [Item type] = N'Warehouse'
and [Item name] = N'Operations'
and [Schema name] = N'Parcel'
and [Object name] = N'DeliveredStatus'
),
cast('1900-01-01' as datetime2(6)) -- initial boundary: no clean Load yet
);
-- Rows to insert or update
select [Parcel ID]
, [Status]
, [Depot]
from [Parcel].[LakehouseLiveStatus]
where [row_update_datetime] > @bookmark_datetime -- audit datetime after the bookmark
and [Status] = 'Delivered';
Three details differ from the Lakehouse stage:
- You read the bookmark in SQL. Every installed Warehouse sees the catalogue's
_.Bookmarkas a view, soselect [Bookmark datetime] from [_].[Bookmark]is an ordinary query on the four-part key. Thecoalescesupplies the initial boundary if no row exists yet. - Weaver's audit column does the work that
files_since()did.row_update_datetimesays when Landing's Load last inserted or updated a row, and@bookmark_datetimesays how far this Table has read, so comparing them selects the window. A Lakehouse Delta Table spells its audit columns in lower snake case, which is why this column differs from a Warehouse Table's[Row update datetime]. - You decide which rows belong. The filter keeps Operations' record to delivered parcels, and the Table returns only candidate rows. A row that is absent from the candidates stays in the target, because absence is not a delete, so Operations keeps its record of a parcel even after Landing drops that parcel's file.
Because the View comes from a logical Shortcut, Weaver infers the dependency from your SQL. An item-wide Load that selects both items runs Landing's Table first and waits for the SQL endpoint to show its new rows before Operations reads them.
Load the pipeline¶
Load both items in one run, so that Weaver orders each stage after the one it reads:
Each stage starts from the initial boundary, 1900-01-01:
- The Folder reports two insertions, and each path now carries the time of its insert event. That time is the Folder's audit datetime.
- The Delta Table's
files_since()returns both files, so it inserts both parcels. Each row carries therow_insert_datetimeandrow_update_datetimethat Load set. - The Warehouse Table's window is every Landing row updated after
1900-01-01, which is both rows. OnlyP-1002isDelivered, so Operations records one parcel.
To see each Table's bookmark, run these in a SQL query connected to the catalogue Warehouse. Each value is the instant just before that Table's read began, and the catalogue addresses a Lakehouse Table by schema name Tables/Parcel:
-- This Table's bookmark: when its last clean Load began.
select [Bookmark datetime]
from [_].[Bookmark]
where [Item type] = N'Lakehouse'
and [Item name] = N'Landing'
and [Schema name] = N'Tables/Parcel'
and [Object name] = N'LiveStatus';
-- This Table's bookmark: when its last clean Load began.
select [Bookmark datetime]
from [_].[Bookmark]
where [Item type] = N'Warehouse'
and [Item name] = N'Operations'
and [Schema name] = N'Parcel'
and [Object name] = N'DeliveredStatus';
The Operations bookmark is later than Landing's, because Operations' read began after Landing's Load finished.
Change the source and load again¶
Change the source without touching any declaration. In a notebook with ParcelSourceArchive attached as its default Lakehouse, replace one file and delete the other:
from pathlib import Path
source_root = Path("/lakehouse/default/Files/parcel-status")
(source_root / "P-1001.csv").write_text(
"Parcel ID,Status,Depot\nP-1001,Delivered,Central\n",
encoding="utf-8",
)
(source_root / "P-1002.csv").unlink()
One change now moves through all three stages:
- Folder. The snapshot differs from the managed Folder by one updated path and one deleted path, so the Folder reports one update and one deletion.
- Delta Table. Its window, everything after its previous boundary, contains both events.
files_since()returnsP-1001.csv, which becomes an update, anddeleted_since()returnsP-1002.csv, which becomes a delete by primary key. The Table's only row isP-1001, nowDelivered, and Load has set that row'srow_update_datetimeto a time later than the Operations bookmark. - Warehouse Table. Its window is the rows updated after its own bookmark.
P-1001qualifies, and it is nowDelivered, so Operations records it.P-1002no longer has a row in Landing, so there is nothing for this query to read. Operations keeps theP-1002it recorded earlier.
The Warehouse Table now holds both delivered parcels:
| Parcel ID | Status | Depot |
|---|---|---|
| P-1001 | Delivered | Central |
| P-1002 | Delivered | South |
The Load summary adds up every stage's counts, so for what is one business change it shows more updates and deletions than business rows.
Run the same Load again without changing the source. The Folder reports no changes, your Delta Table's read() returns None, and the Warehouse Table's window contains no rows. No target changes, and all three bookmarks advance to the new instants.
What differs between the consumers¶
| Lakehouse Delta Table | Warehouse Table | |
|---|---|---|
| Change evidence | The Folder's record of inserted, updated and deleted paths | row_update_datetime on each Landing row, set by Weaver's Load |
| Reads its bookmark with | self.bookmark() |
select … from [_].[Bookmark] |
Bookmark key in _.Bookmark |
Schema name Tables/Parcel |
Schema name Parcel |
| Delete claim from | The filename of a deleted path | None: the Table records delivered parcels and keeps them |
| Empty window | read() returns None |
The query returns no rows |
In both stages you left the declarations as they were, each Load saw only the window since the previous Load's boundary, and each consumer's bookmark moved independently of the others'.
Load behaviour owns exact bookmark, reload and selection rules. Row auditing and operational history explains where current state and Load activity are recorded.