Archiving

Archiving moves older rows from the history schema (HIS/ODS) to Parquet files in the Azure Data Lake. This keeps the active data warehouse database small and cheap, while the older history is preserved as column-oriented files — and, through an automatically maintained union view, still queries together with the live table.

Archiving works in three steps per table, within a single run of the Dynamic Archiving Workflow YRES:

1. Copy — the rows matching the archiving rule are written as Parquet to the archive/ path in the Data Lake.
2. Verify & purge — [LoadManagement].[spArchivePurge] counts the rows that match the rule right now and deletes them only on an exact match with the number of copied rows. If the counts differ (a load ran in between), nothing is deleted and you can safely rerun.
3. Refresh the union view — [LoadManagement].[spArchiveMaintainView] generates the per-table view [<HIS schema>].[<Target>_IncArchive] that presents the live table and the archived Parquet files as one whole.

Trial-run first, purge later

The purge is off by default (setting ArchivingPurgeEnabled = 0). In that mode the workflow only copies to the Data Lake and deletes nothing — ideal for validating the configuration and the Parquet output. Then set ArchivingPurgeEnabled to 1 to actually clean up — this is the only switch gating the purge. (AllowDeletesFromDB deliberately plays no role here: that setting governs dropping objects, not deleting data.)

Two archiving modes per table

Archiving is opt-in per table and comes in two modes, configured on LoadManagement.UsedTables (via spMaintainTable):

Mode	What gets archived	Typical use
CLOSED	Only closed SCD2 versions: rows with isCurrent = 0 whose ETL_EndDate is older than the retention period. The current row always stays in the database.	History cleanup without functional impact: the current state stays complete.
BUSINESS	Real data based on a date column in the dataset (the ArchivingColumn), e.g. invoices older than 10 years — including current rows.	Legal/functional retention: "anything older than X years may leave the data warehouse".

The configuration columns:

Column (UsedTables)	Meaning
ArchivingMode	NULL = off, CLOSED or BUSINESS.
ArchivingColumn	BUSINESS only: the dataset column holding the business date (must exist in the Dictionary).
ArchivingRetention + ArchivingRetentionUnit	The retention period: a number of YEAR / MONTH / DAY (e.g. 10 + YEAR).
ArchivingClause	Advanced: a free-form WHERE clause that overrides the mode — for edge cases such as a Unix-timestamp column. With BUSINESS, such a clause may only reference data columns (no ETL_*/isCurrent).

The configuration is validated right at save time (spMaintainTable): an unknown mode, BUSINESS without a date column or clause, or a missing retention period immediately yields a clear error message — instead of archiving silently staying off.

From this configuration the function [LoadManagement].[fxArchivingPredicate] builds a single archiving condition, with the cutoff date as a fixed literal — so the copy and the purge within one run are guaranteed to use exactly the same rule. The result appears as ArchivingScript (FROM <HIS schema>.<Target> WHERE <condition>) on the contract view [LoadManagement].[vwExtractor]; the workflow only picks up tables where this script is populated.

Archived data does not come back (BUSINESS)

With BUSINESS archiving, a deleted row may still exist in the source system. That is why, in this mode, the load engine (spHIS_InsertAndUpdate) blocks all incoming rows that fall inside the "archived space" (business date older than the cutoff): they never reach HIS again, not even through a FULL or IMAGE load. The number of blocked rows is logged per load (Blocked archived-space rows in page in monitoring). Tip: align the table's LoadFilter with the archiving cutoff so the source extraction stops fetching that old data as well. CLOSED does not need this blocking: the current row simply stays in the database.

This guarantee also holds when you later change or disable the configuration — see the purge memory below.

When does archiving run?

Archiving is a separate workflow (Dynamic Archiving Workflow YRES), independent of the normal load. It runs when you start it explicitly — manually or via your own trigger — with the same scope and tier parameters as the regular load:

Parameter	Default	Meaning
Source / Schema / Table	ALL	Limit the run to one source, schema or table.
RunningTier	Current	Temporarily scale the database up during the run.
RevertToTier	Previous	Tier to scale back to afterwards.

The ForEach over the tables runs in parallel (batchCount: 3); the pipeline itself has concurrency: 1 — at most one archiving workflow runs at a time.

How it works (outline)

Manual run / trigger  (Source, Schema, Table, RunningTier, RevertToTier)
  → (optional) Set DB Tier           → temporarily scale the database up
  → Get tables (Lookup)              → SELECT … FROM [LoadManagement].[vwExtractor]
                                        WHERE [ArchivingScript] IS NOT NULL
  → ForEach per table (parallel):
        Copy data                    → 'SELECT * ' + ArchivingScript
                                        → Parquet on archive/… (AzureDataLakeStorage_ARCHIVE)
        Purge archived rows          → [LoadManagement].[spArchivePurge]
                                        recounts; deletes only on an exact match,
                                        batched, gated by ArchivingPurgeEnabled
        Maintain archive view        → [LoadManagement].[spArchiveMaintainView]
                                        refreshes the <Target>_IncArchive union view
  → (optional) Set DB Tier Back      → scale the database back

As with a regular load, ADF is the generic executor and the logic lives in SQL: the ArchivingScript deliberately starts with FROM, so both a SELECT * (copy) and a DELETE (purge) can be prefixed to it — both therefore operate on exactly the same row selection.

What lands in the Data Lake

The Copy step writes the result as Parquet to a dedicated archive path (dataset AzureDataLakeStorage_ARCHIVE), separated from the regular Data Lake loads:

archive / <Source> / <TargetSchema> / <Target> / <year> / <month> / <Target>-<timestamp>.parquet

The exported rows keep all SCD2 framework columns (KeyHash, RowHash, ETL_Date, ETL_EndDate, isCurrent and the RowID), so the archive has the same structure as the source in HIS.

Live + archive as one whole: the _IncArchive views

After every successful copy, spArchiveMaintainView refreshes the per-table view [<HIS schema>].[<Target>_IncArchive]: a UNION ALL of the live table and the archived Parquet files, read through Azure SQL data virtualization (OPENROWSET over an external data source on the Data Lake). Consumers that also need the archived history simply query this view instead of the table.

• The view deduplicates on the internal RowID with precedence for the live row — a copy-only trial run or a restart can legitimately put the same rows in the archive twice.
• The column list and data types are regenerated from the live table on every archiving run, so column changes follow automatically.
• View maintenance can never block archiving: if it fails (e.g. permissions not set up yet), that is logged and archiving simply continues. The view appears automatically on the next run once the issue is resolved.

Permissions for the union views

The views read the Data Lake directly from SQL. This requires a one-time per-environment setup: a system-assigned managed identity on the logical SQL server, Storage Blob Data Reader for that identity on the Data Lake container, and the setting ArchiveLakeLocation (adls://<container>@<account>.dfs.core.windows.net). Until that is done, archiving itself works fine — only the views are skipped (with a message in monitoring). Data virtualization is a preview feature of Azure SQL Database.

Purge memory: consistent, even after configuration changes

Every successful, verified purge is recorded in [LoadManagement].[ArchiveLog]: one row per clean-up, holding exactly the condition the rows were deleted with. On every load, the load engine applies all remembered conditions on top of the current archiving rule.

That makes the behaviour predictable in situations that previously required extra care:

• Configuration changed (longer retention, different column) — the rows purged under the old rule stay blocked and do not creep back into HIS.
• Archiving disabled — data that was already purged stays purged; the archive in the Data Lake and the _IncArchive view remain readable as usual.
• Deliberate re-ingestion — if you do want purged rows to be loaded again, delete that table's ArchiveLog rows; the next load picks them up again (and the Parquet archive always remains available alongside).

The memory stays compact: a new purge on the same column replaces older, wider rules, leaving only a handful of rows per table.

The settings

Setting ([Config].[Settings])	Default	What it controls
ArchivingPurgeEnabled	0 (No)	Master switch — and the only gate — of the purge step. 0 = copy only (trial mode), 1 = also delete from HIS after a verified copy. The existing setting AllowDeletesFromDB deliberately plays no role in the purge: it governs dropping objects, not deleting data.
ArchiveLakeLocation	empty	adls://… location of the Data Lake for the union views; filled by provisioning. Empty = views are skipped.

The health checks (vwYresChecks, group 7) guard the configuration: BUSINESS without a date column, an ArchivingColumn missing from the Dictionary, a missing retention period, a clause on ETL_ columns, and a purge that is enabled while ArchiveLakeLocation is empty (check 7.14 — the archive is then unreadable from SQL) are all flagged. The purge memory is guarded as well: check 7.15 warns when a remembered block references a column that is no longer part of the selection (every load would fail on it), and check 7.16 shows which tables still carry an active purge memory while the current configuration no longer sets up blocking — useful to understand why purged rows do not come back, and where to act if you actually want them to.

Retired: settings-driven default archiving

Older versions contained an alternative design (vwArchivingExtractor with the settings DefaultArchivingDate/DefaultArchivingLoadtypes) that would archive all DELTA tables automatically. Since v1.56, archiving is deliberately an explicit per-table choice; the deploy cleans up the old view and settings itself.

Monitoring

Archiving runs show up in the regular monitoring screens: the per-table steps log as Process = 'load' (visible in vwLoads/vwMonitor, with the HIS table as target) and the workflow steps as Process = 'Workflow' (visible in vwWorkflow). In addition, the archiving procedures write detail steps with Process = 'Archiving': the verification counts (copied vs. currently present), the number of deleted rows, skipped purges (switch off) and the view maintenance. If verification fails, the run fails visibly with the reason in the log — and rerunning is always safe, because nothing was deleted.

Difference from the load types

Archiving is independent of the load types. A load type determines what happens to the history table when loading; archiving determines what happens to old rows. In particular:

• OVERWRITE wipes the history on every load — little to archive there.
• FULL, DELTA, IMAGE, RELOAD and the other types do build SCD2 history; those tables benefit most from CLOSED archiving.
• Tables from file sources do not (yet) participate in archiving.

See also Load types, History & SCD2, the glossary and Views & pipelines.