SCD Type 2
Understanding Slowly Changing Dimension Type 2 for historical data tracking.
Overview
The Mployr API uses Slowly Changing Dimension Type 2 (SCD Type 2) to track historical changes to certain entities. Instead of overwriting data, changes create new records with temporal metadata, preserving a complete audit trail.
How It Works
Data is stored in separate tables (names, emails, phones) linked to parent entities via polymorphic relationships.
Each record has start_date, end_date, and active fields.
Updates close the current record (set end_date) and create a new active record, forming a version chain.
Data Structure
Here's an example of a person with historical name and email changes:
// Current active record
{
"id": 42,
"first_name": "John",
"last_name": "Doe",
"email": "john.doe@example.com",
"phone": "+61 400 123 456",
// Historical data via link tables
"names": [
{
"first_name": "John",
"last_name": "Doe",
"start_date": "2024-01-15",
"end_date": null,
"active": true
},
{
"first_name": "John",
"last_name": "Smith",
"start_date": "2023-01-01",
"end_date": "2024-01-14",
"active": false
}
],
"emails": [
{
"email": "john.doe@example.com",
"start_date": "2024-01-15",
"end_date": null,
"active": true
},
{
"email": "john.smith@oldcompany.com",
"start_date": "2023-01-01",
"end_date": "2024-01-14",
"active": false
}
]
}Temporal Fields
| Field | Description |
|---|---|
| start_date | When this version became active (inclusive) |
| end_date | When this version was superseded (null for current) |
| active | Boolean flag indicating the current active version |
Entities Using SCD Type 2
The following entities track historical changes:
- names (first_name, last_name)
- emails
- phones
- names
- emails
- phones
- addresses
- theme
- locale
- notification preferences
- group memberships
Updating Data
When you update SCD Type 2 fields, the API automatically handles versioning:
// Update a person's name
// This creates a new historical record, not an overwrite
curl -X PUT https://api.mployr.com.au/v1/people/42 \
-H "Authorization: Bearer <token>" \
-H "Content-Type: application/json" \
-d '{
"last_name": "Johnson"
}'
// Result: Old name record gets end_date set, new record created
// Previous: { "last_name": "Doe", "end_date": null, "active": true }
// After: { "last_name": "Doe", "end_date": "2024-06-15", "active": false }
// New: { "last_name": "Johnson", "end_date": null, "active": true }Querying Historical Data
Access current data, full history, or point-in-time snapshots:
Common Use Cases
Audit Compliance
Track all changes to employee records for compliance and audit purposes. Every change is preserved with who made it and when.
Historical Reporting
Generate reports showing data as it existed at any point in time. Perfect for end-of-year reports or historical analysis.
Name/Email Change Tracking
Maintain records of employee name changes (e.g., after marriage) while preserving previous names for historical documents.
Settings Rollback
View and potentially restore previous user settings configurations by accessing historical setting records.
Best Practices
Use Active Records for Current Data
When displaying current information, always use the active records (where active: true) rather than filtering by date.
Don't Manually Modify History
Never directly update historical records. Let the API handle versioning through standard update operations.
Use as_of for Historical Queries
When you need point-in-time data, use the as_of parameter rather than manually filtering by dates.
Ready to Build?
Explore the full API reference to see all available endpoints and their SCD support.