Archiving D365 F&O Data with Dataverse Long Term Retention

Dynamics 365 Finance & Operations now offers a robust archiving capability that moves historical, inactive data out of live tables while keeping it accessible for audits and analytics.

Here’s what you’ll find in this guide:

• How the archiving process works
• Which data types are supported today
• How to safely include your custom fields and tables

If you’re still deciding whether archiving is the right next step, start with our blog:
“D365 F&O Database Optimization”
It covers practical clean-up techniques and explains how they fit into a broader data lifecycle strategy perfect context before diving into Dataverse-based retention.

Why Archive?

• Performance: Reduce row counts in large tables to speed up queries and batch processing.
• Cost & Operations: Smaller databases mean lower capacity usage, faster backups/restores, and smoother refreshes and upgrades.
• Compliance: Retain historical data securely for audits and regulatory requirements without bloating your live system.

  The Archiving Lifecycle in D365 F&O

1. Criteria & Marking – Choose the functional scenario and define archival rules (e.g., completed or closed status older than X months).
2. Replication to Dataverse – Copy data from live tables to long-term storage managed via Dataverse.
3. Reconciliation – Verify that all marked rows are present in long-term retention.
4. Move & Delete – Transfer validated rows to history tables and remove them from live tables to reduce operational size.
5. Access & Restore – Use dedicated inquiry pages to view history, restore data if needed, or purge when permitted.

 

What Can You Archive Today?

Initial support focuses on high-volume areas for maximum impact:

• Finance: General ledger, tax transactions
• Supply Chain: Inventory transactions, inventory journals, sales orders

Microsoft plans to add more scenarios in future releases (e.g., Commerce, Customer Invoice Data).

---

Working with Archived Data

• Read Access: History data remains queryable in F&O via dedicated inquiry pages.
• Analytics: Archived data in Dataverse can be accessed through Fabric or Synapse for reporting.
• Deletion: When retention periods expire, purge data from long-term storage according to policy.

 

Including Custom Tables and Fields – Must-Do Steps

Most real-world implementations include extensions, and the archiving framework supports them. However, you need to make sure your schemas are aligned for a smooth process:

1. Mirror Custom Fields in the History Table

The history table should include all columns from the live table.
Exceptions: Platform-managed columns like SysRowVersion and SysDataState don’t need to be duplicated.

2. Add Custom Fields to the BI (Virtual) Entity

Dataverse long-term retention relies on virtual/BI entities to interact with F&O data.

• Add your custom fields to the relevant BI entity.
• Refresh the entity in Dataverse so the archiving process recognizes them.
• Important: Do not add relationships between BI entities.

If you’ve extended created/modified fields, follow the recommended naming conventions in the BI entity.

 

Table field	Bi Entity
CreatedDateTime	CreatedOn
ModifiedDateTime	SysModifiedDateTime
CreatedBy	SysCreatedBy
ModifiedBy	SysModifiedBy

 

Validation Tip

Before going live, run schema comparison queries:

• Compare live vs. history tables
• Compare history vs. BI entity

This helps catch missing columns early and prevents archival job failures or reconciliation mismatches.
Use the provided scripts to validate whether fields match the expected schema.

Table vs History table:

DECLARE @tmpDataTable Table (SrNo int, Scenario VarChar(10), LiveTable varchar(100), HistoryTable varchar(100), BiEntity varchar(100))

INSERT INTO @tmpDataTable values(1,'GL','GENERALJOURNALACCOUNTENTRY','GENERALJOURNALACCOUNTENTRYHISTORY','GeneraljournalaccountentryBiEntity')

select * from @tmpDataTable

--SELECT QUERY TO IDENTIFY SCHEMA MISMATCH

 SELECT T.SrNo,T.Scenario, L.COLUMN_NAME,L.TABLE_NAME,HistoryTable,BiEntity--, *

FROM @tmpDataTable T JOIN INFORMATION_SCHEMA.COLUMNS L ON T.LiveTable = L.TABLE_NAME               LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS H ON L.COLUMN_NAME = H.COLUMN_NAME AND  T.HistoryTable= H.TABLE_NAME

WHERE --L.TABLE_NAME IN (N'SALESTABLE') AND

H.COLUMN_NAME IS NULL AND  L.COLUMN_NAME not in ('SYSDATASTATECODE','SYSROWVERSION') Order by T.SrNo

History table Vs Bi Entity:

DECLARE  @tmpDataTable Table (SrNo int, Scenario VarChar(10), LiveTable varchar(100), HistoryTable varchar(100), BiEntity varchar(100))

INSERT INTO @tmpDataTable values(1,'GL','GENERALJOURNALACCOUNTENTRY','GENERALJOURNALACCOUNTENTRYHISTORY','GeneraljournalaccountentryBiEntity')

 SELECT T.SrNo,T.Scenario, L.COLUMN_NAME,L.TABLE_NAME,HistoryTable,BiEntity--, *

FROM @tmpDataTable T JOIN INFORMATION_SCHEMA.COLUMNS L ON T.LiveTable = L.TABLE_NAME  LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS H ON L.COLUMN_NAME = H.COLUMN_NAME AND  T.BiEntity = H.TABLE_NAME

WHERE --L.TABLE_NAME IN (N'SALESTABLE') AND H.COLUMN_NAME IS NULL

AND  L.COLUMN_NAME not in ('SYSDATASTATECODE','SYSROWVERSION')

Order by T.SrNo, L.TABLE_NAME, L.COLUMN_NAME

 

3. Adding New Tables to an Archive Scenario

If you need to include custom tables in an archive scenario, you can extend the existing setup. These tables should relate either directly or indirectly to the main live table.

What to do:

• Create a corresponding history table and BI entity that mirrors the structure of the live table.
• You generally don’t need to replicate indexes; a clustered index on RecId is usually sufficient for history tables.
• Extend the ArchiveAutomationJobRequestCreator class for a scenario to add the new table to archive table chart

Operational Guardrails & Best Practices

• Start in a sandbox: Validate the full archive flow end-to-end (criteria → replicate → reconcile → move/delete → inquiry/restore).
• Align with retention policies: Work with compliance and legal teams to define time-based and event-based rules.
• Build safeguards: Use role-based access, approvals, and backout plans for initial runs.

Maximize savings

a. Bring Your Own Lake (BYOL): Use a custom data lake to reduce storage costs. Note: Delete actions in F&O won’t remove data from the lake; instead, the SysDataStateCode flag will be set to 1(1-archived, 0- Live). 

b. Purge aggressively: Remove data from history tables and Dataverse using bulk delete APIs for maximum cost savings.

---

Putting It All Together – Sample Rollout Plan

1. Pilot scope: Archive sales orders older than 24 months that are fully invoiced and closed.
2. Extend schema: Add custom fields to SalesTableHistory and the corresponding BI entity; refresh in Dataverse.
3. Dry run in sandbox: Validate replication, reconciliation, and accessibility via inquiry pages.
4. Cutover window: Execute in production during a planned maintenance window; monitor database size and job telemetry.
5. Analytics check: Ensure your Fabric/Synapse model includes both live and historical layers for complete reporting.

---

Resources

• Archive data in Dynamics 365 finance and operations apps with Dataverse  [learn.microsoft.com]
• Archive customization (preview): Add custom fields/tables to history & BI entities [learn.microsoft.com]