01
Connect source systems
Configure linked-server connectivity from SQL Server to each MySQL transactional source.
Selected Engineering Work · Project 04
Retail Monitoring Data Warehouse
Building a centralized SQL Server warehouse for multi-source retail monitoring and reporting.
Designed and built a retail data warehouse from scratch on Microsoft SQL Server, integrating transactional data from multiple MySQL systems into a normalized snowflake-schema model with near-real-time operational ingestion and business reporting.
The challenge
Bring distributed transactional data into one consistent reporting platform.
Retail operational data was spread across multiple transactional MySQL servers. The goal was to centralize that data into a warehouse capable of supporting operational monitoring, analytics and business reporting without pushing analytical workloads back onto the source systems.
Microsoft SQL Server was selected as the central warehouse platform, with linked-server connectivity used to integrate data from the different MySQL sources.
- Integrate transactional data from multiple MySQL servers.
- Provide a common model across sales, inventory, product and customer domains.
- Support both frequent operational refreshes and lower-frequency batch loads.
- Serve reporting needs across analysts, operations, management and finance.
Source architecture
Centralize multiple transactional systems into a dedicated analytical platform.
Linked-server connectivity provided access from SQL Server to the distributed MySQL sources, while ingestion and transformation logic moved data into the warehouse model.
Transactional sources
Multiple MySQL servers
Sales, orders, inventory, pricing, customers, payments and logistics.
Connectivity
Linked Servers
Central access from SQL Server to distributed operational databases.
Warehouse
SQL Server
Stored procedures, scheduled jobs, transformations and snowflake-schema modeling.
Consumption
Dashboards + reporting
Product performance, stock levels and operational KPIs.
Data model
A normalized snowflake schema for shared retail reporting.
The warehouse used a snowflake-schema model to provide a consistent structure across multiple retail subject areas. The design emphasized normalized dimensional relationships, storage efficiency and clear data-quality boundaries.
The resulting model gave different reporting teams a shared data foundation instead of relying on separate interpretations of the transactional systems.
Ingestion strategy
Match refresh frequency to the operational need.
Stored procedures and scheduled SQL jobs handled data movement into the warehouse. Refresh frequency varied by use case rather than forcing every dataset into the same schedule.
02
Extract and stage
Use stored procedures to retrieve and prepare source data for warehouse processing.
03
Transform and load
Apply SQL-based transformation logic and populate the normalized warehouse structures.
04
Schedule by use case
Run selected operational jobs at sub-minute intervals while less time-sensitive loads ran twice daily.
05
Reconcile
Compare source and warehouse row counts and perform source-to-warehouse checks.
06
Serve reporting
Expose warehouse data to dashboards used for product, inventory and operational monitoring.
SQL performance engineering
Support frequent ingestion and reporting on the same platform.
The warehouse used several SQL Server techniques to keep ingestion and reporting workloads responsive as the platform grew.
- Temporary tables: materialized intermediate results where this simplified or reduced repeated processing.
- Partitioning: structured large warehouse data for more efficient management and query access.
- Indexing: tuned access paths around ingestion and reporting patterns.
- Linked-server integration: provided central SQL Server access to distributed MySQL sources.
Near-real-time reporting
Operational visibility without calling scheduled processing "streaming."
Selected ingestion jobs were scheduled at intervals of less than one minute, allowing the warehouse to support near-real-time operational monitoring. Other subject areas were refreshed twice per day where lower latency was not required.
This mixed refresh model aligned processing frequency with business need while keeping the architecture based on scheduled SQL jobs rather than event-streaming infrastructure.
Validation
Reconcile the warehouse with its transactional sources.
Data-quality checks focused on validating that warehouse loads remained consistent with the originating transactional systems.
- Row-count reconciliation between source systems and warehouse targets.
- Source-to-warehouse checks to identify missing or unexpected data.
Key engineering decisions
Build a common reporting foundation around operational data.
Centralize distributed transactional data
Bring multiple MySQL operational sources into a dedicated SQL Server warehouse rather than distributing reporting logic across production systems.
Use a normalized dimensional model
Adopt a snowflake schema to provide consistent relationships and reusable structures across retail subject areas.
Match refresh frequency to business need
Use sub-minute scheduling for selected operational workloads and lower-frequency batch refreshes where near-real-time data was unnecessary.
Outcome
A retail warehouse built from scratch for operational and analytical reporting.
The project delivered a centralized retail data warehouse on Microsoft SQL Server, integrating transactional data from multiple MySQL systems into a consistent snowflake-schema model.
Stored procedures and scheduled SQL jobs supported refresh frequencies ranging from near-real-time operational processing to twice-daily batch loads, while indexing, partitioning and SQL optimization supported warehouse performance.
The platform provided product, inventory and operational reporting for approximately 60 analysts, business users and stakeholders across operations, management and finance.