How to Track State with Type 2 Dimensional Models

Summary

Application databases often retain only current values in Type 1 dimensions, limiting analysis of historical settings such as feature adoption, retention, and switching behavior. The post explains Type 2 dimensional models through Shopify’s need to track users’ admin languages over time, contrasting application-model changes, scheduled database snapshots, and event logging. Its selected implementation uses Rails after_commit callbacks to send created or updated records to Kafka, then transforms that event history into records with valid_from, valid_to, and is_current fields using ETL recipes involving PySpark and dbt. The approach provides the required granularity but can miss changes or events, requires delete handling and data-quality checks, and is presented as an iterative solution; MySQL binlogs are described as a more reliable future source.

Context

Application users tables store settings in place, so they cannot answer historical questions about language selection, feature adoption, retention, or switching. Shopify needed to understand how users’ translated admin experiences changed over time, while changing the existing users model was considered impractical because of performance, migration cost, and risk.

Approach / What changed

The implementation logs created and updated records from Rails after_commit callbacks to Kafka, then uses ETL recipes with PySpark and dbt to convert the event history into Type 2 records containing valid_from, valid_to, and is_current. The post also discusses snapshots, delete handling, data-quality checks, and the potential use of MySQL binlogs.

Takeaways

• Type 2 dimensions preserve successive states with valid_from, valid_to, and is_current fields, enabling historical analysis that Type 1 dimensions cannot support.
• Rails after_commit callbacks prevent events from being logged before the corresponding database change is successfully committed.
• Kafka-based logging can miss updates from external database processes or failed emissions, so discrepancies should be checked against current-state snapshots; deletes require explicit event handling.

Related reading