LEARN. PRACTICE. GROW.1. Design the execution path
In a fictional scenario, a positions system keeps its SQL source on a private network and delivers data to a lake. The design must identify the process opening each connection, its credentials and network path. A self-hosted integration runtime can execute copying from a network that reaches the source; that process also needs to reach the destination. Placing the Data Factory icon in a region does not establish where all data is processed. Draw source, runtime, destination and transformations as separate components. For handover, request evidence of both connections using the operational identity, host ownership and execution during maintenance of one node. A successful connection from the architect’s laptop does not validate the service’s overnight path.
2. Define what counts as a change
The team wants to reduce daily load by copying only changes. First define the contract: new rows, updates, deletions or all these events. A filter on an increasing marker can select changed rows, but a deleted row does not reappear in that query. If the destination must reflect deletions, the design needs a mechanism representing them, such as supported change tracking or application-defined deletion records. In the fictional exercise, the destination retains a closed position and overstates exposure. Running the same query more frequently does not resolve that gap. Ask the source owner for evidence of how each change type reaches the consumer. Include late changes and marker precision in the analysis before choosing window boundaries.
3. Advance the checkpoint after acceptance
A persistent marker records how far the consumer considers work complete. Capture a stable upper bound for the run and document inclusive or exclusive boundaries. The official tutorial updates the marker after copying; our fictional case adds reconciliation before accepting the window. If the process fails after writing data but before saving the checkpoint, the same window may run again. Therefore design a business key, replacement strategy or idempotent destination operation. A new execution ID helps investigation but does not by itself identify the same business transaction. The Python exercise uses integer versions and one writer; it does not simulate distributed transactions, concurrency or every form of change capture. Its purpose is to expose the acceptance and replay decisions explicitly.
4. Separate window start, completion and acceptance
A schedule answers when to start. The processing contract must also show which intervals completed, are missing or need replay. Tumbling window triggers retain window state, which is useful when requirements include backfilling periods. The owner must still define pipeline behavior and how the destination handles repetition. In the fictional scenario, the 22:00 window fails and the 23:00 window completes. The dashboard should expose the missing interval instead of showing only the latest run. Define dependencies between windows only when the data requires them, because unnecessary serialization can delay recovery. Record starting and ending bounds, state, contract version and reconciliation reference to support an informed close decision. A successful invocation alone leaves these business acceptance questions unanswered.
5. Treat skipped rows as a business decision
Fault tolerance can allow a copy to continue while skipping incompatible data. In a fictional close, 1,200 rows were read, 1,196 written and four skipped. Equality between rows read and written plus skipped explains the count; it does not establish that the delivered set satisfies the close. This exercise’s contract requires every valid position, so the owner holds publication, identifies the four rows and fixes the cause. Another product may accept an authorized discard with explicit criteria and traceability. Define those rules before an incident. Also reconcile keys, control totals and versions when the outcome depends on them. Equal counts can hide one row replacing another or an incorrect monetary value. Acceptance must reflect the actual promise to downstream consumers.
6. Evolve the schema through an explicit contract
A source adds an optional column and, in another delivery, changes the meaning of an amount. These are different changes. Allowing schema drift helps process varying columns, but flexibility does not decide what the data means. In the fictional scenario, the team preserves new columns in the landing area and validates an approved schema before curated publication. Changing from euros to cents requires an explicit version and transformation even when the numeric type remains compatible. Define handling for unknown, missing and mandatory columns, as well as failed conversions. Keep synthetic examples of each contract to exercise the consumer. When reviewing a proposal promising automatic acceptance of any change, ask for examples of semantic changes and rejection criteria.
7. Choose analytics and permissions together
For occasional queries exploring lake files, evaluate serverless SQL and the data processed by those queries. For a warehouse with loaded data and its own distribution and capacity needs, evaluate a dedicated SQL pool using representative measurements. The SQL name does not make both services equivalent to the transactional source of the positions. In parallel, check how the identity accesses the lake. A data RBAC grant can make a restriction enforced only through an ACL ineffective. In the fictional case, an analyst needs one curated folder but receives read access across the container. Correct the grant’s scope and exercise permitted and denied access, including landing data that has not yet been accepted. Query convenience must fit the agreed data boundary.
8. Accept results with the right time semantics
A dashboard can group events by when they occurred or when they arrived. Delays make that choice change the result’s meaning. In Stream Analytics, late and out-of-order policies can drop events or adjust their times; greater tolerance can also increase output delay. In the fictional example, a network interruption delivers older events after a preliminary close. The owner must decide whether to publish a correction, retain a provisional result or accept the previously agreed rule. Define that behavior with the consumer instead of changing tolerances solely to silence alerts. End the lesson with a worksheet linking window, completeness, duplicates, deletions, schema, access and correction procedure to observable evidence. That worksheet becomes part of the operational acceptance record.
# Fictional single-writer model with unique integer event versions.
# No Azure calls, concurrency or distributed-transaction simulation.
def deliver(events, low, high, target, accepted):
if high < low:
raise ValueError("window moved backwards")
window = [e for e in events if low < e["version"] <= high]
for event in sorted(window, key=lambda e: e["version"]):
key = event["key"]
if event["op"] == "delete":
target.pop(key, None)
elif event["op"] == "set":
target[key] = event["value"]
else:
raise ValueError("unknown operation")
# Rejected work may already have been written: replay must be safe.
return high if accepted else low
events = [
{"version": 11, "key": "P7", "op": "set", "value": 75},
{"version": 12, "key": "P7", "op": "set", "value": 82},
{"version": 13, "key": "P9", "op": "delete"},
]
target = {"P9": 30}
assert deliver(events, 10, 12, target, False) == 10
assert target == {"P9": 30, "P7": 82}
assert deliver(events, 10, 12, target, True) == 12
assert target == {"P9": 30, "P7": 82}
assert deliver(events, 12, 13, target, True) == 13
assert target == {"P7": 82}
assert deliver(events, 13, 13, target, True) == 13
assert target == {"P7": 82}
print("eight fictional checkpoint and replay checks passed")
Fictional case: the positions load completes with four skipped rows while the previous window still needs replay. The owner holds publication and requires reconciliation before advancing the checkpoint.
Common pitfalls
Advancing the marker before accepting delivery; using run ID as a business key; missing deletions; accepting counts without reconciling values; assuming an ACL restricts an existing RBAC grant.
Related topics: Idempotency and recovery · Data contracts · Operational acceptance
Integration is ready to operate when the team can prove what it delivered, detect what is missing and replay work without incorrectly changing the result.
Reference: Integration runtime in Azure Data Factory and Synapse · AZ-305 objectives 2026-04-17