The first load works, and then somebody asks how tomorrow’s load will work. Data integration patterns give that question a practical answer before the next file arrives.
Choose Data Integration Patterns Before the Tool
A data load has two jobs. It must move rows, and it must explain which rows belong in the next run. That second job decides the pattern. SQL Server can handle a surprising amount of integration work with staging tables, SQL Agent, and stored procedures. A special tool becomes useful when connectors, transformations, and operational demands outgrow those pieces.
I start by asking whether the source can provide a stable key, a trustworthy change marker, or a complete extract. Those answers narrow the options quickly. A procedure cannot invent missing change history. A polished diagram cannot either. What does your source actually promise for the next run?
The target also matters. A report table that can be rebuilt overnight has different needs from a customer table used throughout the day. Record those needs before writing an INSERT. The pattern should fit the recovery window, data volume, and acceptable interruption, not the developer’s favorite syntax.
Use a Full Reload When the Whole Set Is Available
A full reload is the simplest pattern when the source delivers a complete, reasonably sized set. Load the extract into a fresh staging table. Check its shape, keys, and expected coverage. Then replace the target in one controlled step. This avoids a half refreshed table that readers can observe.
Do not truncate the published table at the start of a long import. Keep the previous version available until the new version passes checks. For small tables, a transaction can delete and insert quickly after staging. For larger partitioned tables, partition switching gives you another route, provided schemas and indexes align.
I have seen teams add incremental logic to a tiny lookup table because it felt sophisticated. The extra state created more work than the load. Simplicity is a performance feature when it also makes failure easy to explain.
SELECT SourceKey, COUNT(*) AS duplicate_count
FROM dbo.StageCustomer
GROUP BY SourceKey
HAVING COUNT(*) > 1;Pull Changes With a Watermark
An incremental pull reads rows changed since a saved boundary. A ModifiedAt column is common, but it needs a reliable update rule. Capture an upper boundary before extraction. Read a half open interval, then save that upper boundary only after the target transaction succeeds. This prevents rows arriving during the run from falling into an unowned gap.
Use a stable tie breaker when timestamps have limited precision. If several rows share the same timestamp, a boundary based only on time can skip some after a partial batch. Store both time and key, or reread an overlap and deduplicate by key. An overlap is useful only when the target load is idempotent.
The watermark belongs to the pipeline, not to a guess about the current clock. I check that every retry uses the same planned boundary. Moving the boundary during a retry is a good way to make missing rows look mysterious.
DECLARE @from datetime2(3) = '2025-01-01T00:00:00.000';
DECLARE @to datetime2(3) = '2025-01-02T00:00:00.000';
SELECT SourceKey, ModifiedAt, Amount
FROM dbo.SourceOrders
WHERE ModifiedAt >= @from
AND ModifiedAt < @to
ORDER BY ModifiedAt, SourceKey;
Read Change Capture When Deletes Matter
A modified date records inserts and updates only when the source maintains it correctly. It does not report deleted rows. SQL Server Change Tracking can identify changed keys, including deletions, while Change Data Capture provides a richer change stream from the transaction log. Choose based on the detail you need and the source database configuration you can support.
Both features have retention windows. A consumer that falls behind the minimum valid version or LSN needs a new baseline. Check retention before every run. If history has expired, fail clearly and rebuild from a complete extract. Pretending an incomplete delta is complete can corrupt reports for a long time.
I treat delete behavior as a design question on day one. Should the target remove the row, mark it inactive, or retain history? The answer affects reports and audit work. Data integration patterns are incomplete until they answer that question.
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS current_version;
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.SourceOrders')) AS minimum_valid_version;Treat File Drops as Deliveries
A file drop needs an arrival contract. Agree on file names, encoding, column layout, and whether a file is complete before the load sees it. A producer can write to a temporary name and rename after completion. Otherwise, a scheduled load can read half a file and call the day successful.
Store a file receipt with name, size, checksum when available, arrival time, and processing status. Load each file once into staging, then validate before publishing. If a retry begins, use the receipt to decide whether to resume, reject, or replay. A folder full of files is not a job log.
Separate physical arrival from business acceptance. A file can arrive on time and still contain duplicate keys or invalid dates. I want those two states visible to the operator. The source team can then fix the correct problem instead of rerunning everything and hoping for different rows.
Make Data Integration Patterns Safe to Repeat
A restartable load has a stable run identifier and a clear commit boundary. Stage the source rows first. Apply changes in a transaction when the target size permits. Commit the target and the new watermark together, or use a recovery design that reconciles them after failure. Never advance the watermark before the target is durable.
Idempotency means the same input can be processed again without doubling facts or losing values. A unique business key, a file receipt, or a source change identifier gives you a way to recognize a replay. Test a replay deliberately. That test catches more trouble than a successful first run.
If a transaction cannot cover the whole load, break the work into documented chunks. Save progress only after each chunk commits. The next run should know exactly which chunk to retry. SQL Agent can start a procedure, but the procedure must own its recovery rules.
Check the Result and Name the Owner for Data Integration Patterns
Count staged, accepted, rejected, inserted, updated, and deleted rows separately. Compare key totals with a source control total when one exists. Look at rejected rows, not just the job status. A green SQL Agent history entry proves only that the step returned success under its own rules.
Assign ownership for source changes, target schema, schedule, and alerts. I ask who gets the first alert when an unexpected column appears. If the answer is everyone, the answer is usually nobody. Put a contact and runbook beside the pipeline configuration.
Start with the least complicated pattern that meets the contract. A full reload is respectable when it is safe and fast enough. Incremental extraction earns its complexity when the source offers reliable change information and the recovery plan is tested. The best pattern is the one the next DBA can restart at 2 a.m. without inventing the missing rules.
Related reading on this blog: Incremental Loads: Moving Only What Changed and Introduction to Change Data Capture (CDC) in SQL Server 2008.
Data integration is not a choice of tool, it is a choice of reliable handoffs.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
