How Data Mission Sync Organizes Your Data
When Data Mission Sync connects to your Azure SQL database, it creates a structured set of tables to store your replicated Dataverse data. Understanding this structure helps you query your data effectively and integrate with downstream systems.
Data Mission Sync creates the following components in your target database:
| Component | Description |
| Target Tables | One table per synchronized Dataverse table (e.g., account, contact, lead). These contain your replicated data with operational columns added for tracking. |
| Staging Tables | Temporary tables (stage_{entity}) where incoming data lands before being applied to target tables. Ensures data integrity. |
| Service Schema | A _rep schema containing helper procedures and metadata tables used internally by Data Mission Sync. |
Target Table Structure
Each target table mirrors the structure of your Dataverse table, with columns matching the Dataverse attributes you're synchronizing. Data Mission Sync automatically adds operational columns to track synchronization metadata.
Example: If you synchronize the Dataverse 'account' table, Data Mission Sync creates a table in your chosen schema (e.g., dbo.account or replica.account) with:
• All selected Dataverse columns (accountid, name, telephone1, etc.)
• Operational columns (_created_utc, _updated_utc, _deleted_utc, IsDeleted)
• A primary key matching the Dataverse primary key (typically {entity}id)
Operational Columns
Data Mission Sync adds the following operational columns to every target table. These columns are managed automatically and should not be modified manually:
| Column | Description |
| _created_utc | DATETIME2(7) — Timestamp (UTC) when the record was first synchronized to this database. Set once on initial insert. |
| _updated_utc | DATETIME2(7) — Timestamp (UTC) when the record was last updated by a sync operation. Updated on every change. |
| _deleted_utc | DATETIME2(7) — Timestamp (UTC) when the record was marked as deleted (Soft Delete mode only). NULL if not deleted. |
| IsDeleted | BIT — Flag indicating whether the record has been deleted in Dataverse. 0 = active, 1 = deleted. Used in Soft Delete mode. |
| 💡 Tip: Use the _updated_utc column to identify recently changed records. Query WHERE _updated_utc > @LastProcessedTime for incremental downstream processing. | |
Schema Naming
During profile setup, you specify a Schema Name (default: dbo). All target and staging tables are created in this schema. Using a dedicated schema (e.g., 'dataverse' or 'replica') keeps your synchronized data organized and separate from other database objects.