Historical Reporting
Previously processed data had to remain reliable.
Existing reports and historical sales depended on the current data structures, so the migration could not invalidate past reporting.
Back to homepage
Case Study · Data Architecture & Systems Transformation
Evolving a Production Data Architecture
A shared retail-data model was redesigned into a scalable, channel-specific architecture—separating consumer and business operations while protecting historical reporting and existing production processes.
Architecture
SharedChannel-specific
Consumer and business channels represented independently.
Continuity
History preserved
Existing reporting and previously processed data remained reliable.
Scalability
Reusable model
Future products and evolving channel requirements became easier to support.
Architecture transformation
Explore how the shared model evolved into two governed channels.
Existing architecture
Shared Retail Platform
One shared context for consumer and business operations
Why change?
Business requirements evolved beyond the original model.
The architecture needed to separate the two channels without replacing the structures already supporting historical reporting.
Architecture overview
A controlled evolution of the existing platform.
The original retail-data architecture treated consumer and business operations as one shared reporting model. The redesigned structure introduced independent channel processing while preserving a standardized downstream output.
Current focus
- Existing reporting continuity protected
- Channel-specific processing introduced
- Common downstream standards retained
Transformation principle
Separate where the business requires independence. Standardize where downstream systems require consistency.
The Challenge
The original architecture was not broken.
It was designed for a business structure that had evolved.
Consumer, small-business, and business sales had historically been represented through separate retailer values. As reporting requirements matured, this structure created unnecessary complexity and no longer reflected how the business wanted to analyse performance.
The new requirement was to introduce a dedicated channel dimension while preserving the production architecture already supporting recurring data processing, historical reporting, and downstream analytics.
This meant the change could not be treated as a simple rename or data clean-up exercise. Existing procedures, product mappings, summary tables, Tableau relationships, and stakeholder-owned master data all had to remain reliable throughout the transition.
Existing model
Multiple retailer identities
Consumer, small-business, and business activity were represented through separate retailer values.
Required model
Retailer plus channel
A cleaner reporting structure where retailer identity and channel context could be managed independently.
Design Constraints
The architecture had to evolve without disrupting the systems already depending on it.
Production Workflows
Recurring procedures could not stop working.
Transformation procedures, summarisation logic, and weekly operational processing all had to continue through the architectural change.
Master Data Ownership
Product mappings were maintained by another team.
Changes affecting product mappings required coordination and stakeholder approval rather than unilateral modification.
Downstream Dependencies
Tableau relationships and reporting logic had to remain compatible.
The redesign had to account not only for fact tables, but also for joins, filters, summary layers, and analytical behaviour.
The challenge was not introducing a new column. It was introducing a new business dimension into a live production architecture without breaking history, operations, or reporting.
My Role
I owned the change from analysis through production validation.
My contribution went beyond modifying procedures. I analysed the existing architecture, designed the revised reporting model, implemented the technical changes, validated historical and future processing, and investigated the production issue discovered after rollout.
01
Analysis
Mapped the existing production architecture.
Reviewed retailer mappings, transformation procedures, product data, summary tables, Tableau dependencies, and historical reporting behaviour before changing the model.
02
Architecture
Designed the retailer and channel structure.
Defined a cleaner model where retailer identity and channel type could be managed separately while preserving compatibility with existing reporting.
03
Implementation
Updated the Snowflake data model and processing logic.
Added the new channel dimension, updated retailer mappings, modified B2C and B2B procedures, revised summarisation logic, and corrected date-generation behaviour.
04
Validation
Built a comprehensive validation strategy.
Reconciled retailer and channel totals, validated targets, tested historical sales, checked non-affected retailers, and confirmed future weekly processing.
05
Reporting
Supported the Tableau transition.
Validated the reporting model, introduced the new channel filter, and worked with stakeholders to improve filter behaviour using context and relevant-value logic.
06
Production Support
Investigated and isolated the post-release issue.
Traced missing SKU attributes to a retailer mismatch between sales and product mappings, identified the failed Tableau join, and proposed a controlled fix requiring stakeholder approval.
Architecture
Designed the revised model
Balanced business clarity, reporting continuity, and technical compatibility.
Engineering
Implemented the production changes
Updated procedures, mappings, summary logic, and reporting structures.
Ownership
Validated and supported rollout
Managed testing, stakeholder communication, production support, and root-cause analysis.
Architecture Evolution
The architecture changed where independence was needed—and remained stable where consistency mattered.
The redesign introduced a clearer business dimension without replacing the production platform. Each change was deliberately isolated so historical reporting and downstream outputs could continue operating reliably.
Business Structure
Before
Separate retailer identities
Consumer, small-business, and business activity were represented through different retailer values.
After
Retailer plus Channel Type
Retailer identity was simplified while B2C, SMB, and B2B became an explicit reporting dimension.
Reporting Context
Before
Channel meaning was implicit
Users inferred channel context from retailer naming and existing report structures.
After
Channel meaning became explicit
Channel Type became available directly for reporting, filtering, and analysis.
Transformation Logic
Before
Retailer-led processing
Processing logic depended heavily on how each retailer value was interpreted.
After
Channel-aware processing
Consumer and business paths could be managed independently while following common standards.
Product Mappings
Before
Shared mapping assumptions
Product relationships depended on retailer values remaining consistent across downstream joins.
After
Mapping dependencies became visible
Channel-specific product requirements could be identified, validated, and managed deliberately.
Tableau Experience
Before
One filter carried two meanings
Retailer filtering represented both the partner and its operating channel.
After
Clearer analytical controls
Retailer and Channel Type filters supported more relevant and intuitive analysis.
Historical Reporting
Before
Established relationships
Historical reports relied on existing retailer values, summaries, and analytical relationships.
After
Continuity preserved
Controlled migration and regression testing protected previously processed reporting.
Future Change
Before
Growth increased complexity
New channel requirements risked creating additional retailer values and maintenance overhead.
After
A more extensible model
Future channel requirements could evolve without repeatedly redesigning retailer identity.
Downstream Output
Before
Standardized delivery
Production summaries and analytical outputs already supported recurring business reporting.
After
Standardized delivery retained
Downstream systems continued receiving a consistent output model despite upstream redesign.
The architecture evolved without forcing downstream consumers to adopt a new output structure.
Architectural outcome
The business gained independent channel reporting while the platform retained a consistent downstream contract.
Business Impact
A clearer reporting model with less architectural complexity.
The value of the redesign was not measured by a single headline metric. It created a safer, clearer, and more extensible foundation for recurring reporting while preserving the systems and historical data the business already relied on.
Clearer Reporting
Retailer and channel became distinct business concepts.
Users could analyse consumer, small-business, and business performance through an explicit Channel Type dimension rather than interpreting channel meaning from retailer names.
Protected Continuity
Historical reporting remained usable throughout the change.
Controlled migration, reconciliation, and regression testing reduced the risk of disrupting previously processed sales, targets, summaries, and analytical outputs.
Reduced Complexity
Future channel requirements no longer required more retailer identities.
Separating channel context from retailer identity reduced retailer proliferation and created a more maintainable model for future reporting changes.
Improved Usability
Tableau filtering became more intuitive and relevant.
Retailer and Channel Type filters could work together, with context filtering and relevant-value behaviour helping users focus on valid combinations.
Technical Impact
The platform became easier to extend without weakening its downstream contract.
The redesigned model created clearer separation upstream while retaining consistent summaries and reporting outputs downstream.
Maintainability
Retailer identity and channel logic could be managed independently.
Scalability
Additional channel requirements could be introduced without multiplying retailer values.
Reliability
Historical reconciliation and regression testing protected recurring reporting.
Data Quality
The production issue exposed a hidden product-mapping dependency that could now be managed explicitly.
Production Learning
Validation continued after release.
After rollout, stakeholder testing revealed missing SKU attributes in a business-channel report. The sales records were valid, but the reporting relationship failed because sales and product mappings used different retailer identities.
I traced the issue through the fact table, product master, and Tableau relationship, identified the failed join, and proposed a controlled mapping solution without changing stakeholder-owned master data before approval.
The incident strengthened the architecture by exposing a downstream dependency that backend validation alone had not revealed.
Key Lesson
“Architecture changes must consider downstream joins, not only fact tables.”
A successful migration requires validation across the complete reporting chain—from transformation logic and master data to analytical relationships and end-user behaviour.
Closing Perspective
Good architecture is not only about designing a cleaner future state.
It is about reaching that future state without breaking the systems, reporting, and operational processes the business already depends on.
What this project demonstrates
- Architecture and data-model design
- Production migration planning
- Historical and regression validation
- Cross-system dependency analysis
- Stakeholder coordination
- Post-release root-cause investigation