关于Operational Data Store（ODS）采用滚动周期数据存储的可行性咨询
Great question—this is a common pain point when designing an ODS for front-end data extraction needs, especially when tying back to Kimball's DW/ODS framework. Let’s break this down into practical, actionable steps that stick to Kimball’s core principles while balancing business needs and operational constraints:
1. Anchor to Kimball’s Core ODS Definition
Kimball’s framework positions the ODS as a critical bridge between operational systems and the data warehouse, explicitly designed to hold full-cycle operational data (not just a rolling window). The non-negotiable reason here is flexibility: you never know when front-end users or downstream teams will need to pull historical data—whether for user-facing order history, compliance audits, cross-year trend analysis, or even troubleshooting past user interactions.
2. Map Retention Rules to Stakeholder Needs
Start by aligning with your front-end product team and business stakeholders to answer these key questions:
- What historical data do front-end users explicitly expect to access? (e.g., "Users should be able to view all their past orders, no time limit")
- Are there regulatory or compliance mandates dictating minimum retention periods? (e.g., payment records must be kept for 7 years)
- Which data is "core" vs. "ephemeral"?
- Core entities (user profiles, transaction records, order history): Full-cycle retention is non-negotiable
- Ephemeral data (front-end session logs, temporary UI state caches): Rolling retention (e.g., 30-90 days) is acceptable, as users rarely need to access this history
3. Use Tiered Storage to Balance Performance & Cost
You don’t need to keep all full-cycle data in high-performance storage. Implement a tiered approach to optimize both speed and cost:
- Hot Storage: Keep 1-2 years of frequently accessed data (e.g., recent orders, active user profiles) in a low-latency database (like PostgreSQL with SSDs) to support fast front-end queries.
- Cold Storage: Move older, less frequently accessed data (2+ years) to a low-cost, scalable solution (like columnar databases or object storage). Front-end users can still access this data via asynchronous queries or bulk exports (e.g., "Download all your orders from 2018 onwards").
- Archive Storage: For data that meets long-term compliance requirements but is almost never accessed (e.g., 7+ year old payment records), use offline or cloud archive storage. Restrict access to only authorized users for audit purposes.
4. Build Automated Data Lifecycle Management (DLM)
Set up rules to automate retention without manual overhead:
- Auto-migration: Schedule jobs to move data from hot to cold storage after a defined period (e.g., 12 months post-transaction).
- Auto-cleanup: For ephemeral data, set rolling expiration (e.g., delete session logs older than 30 days).
- Versioning for Core Entities: Use CDC (Change Data Capture) or snapshot tables to track historical changes to core data (e.g., a user’s email address over time). This lets front-end users see how their own data has evolved—often a hidden but valuable requirement.
5. Validate Alignment with Kimball’s DW Integration
Remember, Kimball’s ODS is designed to feed the data warehouse later. By keeping full-cycle data in your ODS, you avoid the pain of re-extracting historical data from operational systems when you build out your DW. This ensures a seamless transition and reduces redundant work down the line.
Example Scenario
If you’re building an ODS for a retail front-end:
- Core data: Order records, user profiles → Full-cycle retention (hot storage for 2 years, cold for the rest)
- Ephemeral data: Product browsing logs → Rolling 3-month retention
- Compliance data: Payment transactions → 7-year retention (cold storage for 5 years, archive for the last 2)
内容的提问来源于stack exchange，提问作者Aditya
