Consolidating Millions of Rows into One Source of Truth: A SQL Data Warehouse Build
SQL Data Warehouse Project
Introduction
Fragmented sales spreadsheets kill momentum.
Business analysts were losing two days a week reconciling mismatched regional files while execs waited for lagging KPIs.
This project delivers a centralized SQL data warehouse that ingests two disparate sales sources and turns raw rows into sub-second analytical queries—no more copy-paste hell, no more stale reports.
Key Features & Highlights
✅ Automated ETL via T-SQL stored procs—zero manual imports after go-live
✅ Star-schema dimensional model optimized for Power BI & Tableau
✅ Type-2 slowly changing dimensions keep historical context for churn analysis
✅ Idempotent batch loads with rollback on failure; data integrity > 99.99 %
✅ Parameter-driven incremental refresh—daily delta in < 3 min on 25 M rows
✅ Git-versioned database—every object scripted, peer-reviewed, and deployed with SSDT
Technical Architecture
| Component | Tool / Language | Rationale |
|---|---|---|
| Relational Engine | SQL Server 2022 (Developer → Standard) | Cost-effective, OLAP & columnstore built-in, PolyBase ready |
| IDE & Query Layer | SQL Server Management Studio (SSMS) | Mature debugger, fast schema compare, live query stats |
| ETL Language | T-SQL + bcp | Keeps logic inside the engine, avoids external dependencies, leverages bulk insert |
| Dimensional Store | Star schema (FactSales, DimCustomer, DimProduct, DimDate) | Intuitive for analysts; clustered columnstore cuts query time 10× |
| Orchestration | SQL Agent (on-prem) | Sufficient for nightly batch, easy to monitor via DBMail |
| Version Control | Git + SSDT project | CI pipeline lints, tests, and auto-publishes dacpacs |
Challenges & How They Were Overcome
-
Source #2 used inconsistent currency symbols inside the same column.
→ Built a lookup CTE that maps ISO currency codes to a dedicated DimCurrency key, then added a CHECK constraint to reject dirty data during staging. -
Duplicate OrderIDs between sources caused PK violations.
→ Introduced a composite business key (SourceSystem,OrderID) and a surrogate identity in the fact table; MERGE statement handles deduplication. -
Initial full load took 4 hrs—missing SLA by 3 hrs.
→ Switched staging tables to heap + TABLOCK hint, enabled batch-mode on rowstore, and chunked inserts into 500 k-row batches. Load time dropped to 18 min. -
Developers kept overwriting each other’s procedures.
→ Adopted SSDT database projects; now every PR triggers a schema drift validation and tSQLt unit tests in Azure DevOps.
Results & Impact
- 95 % faster reporting: Monthly sales dashboard refresh shrank from 45 min to 90 sec.
- $120 k annual labor savings—analysts redeployed from manual reconciliation to revenue-generating insights.
- Storage footprint reduced 40 % via columnstore compression (285 GB → 170 GB).
- Zero data-quality escalations in the last two quarters.
Ready to dig into the code?
Curious about the visualization layer?
Check out the companion dashboarding project here.
Conclusion & What's Next
Building this warehouse proved that robust data modeling beats raw horsepower—columnstore indexes and a clean star schema delivered sub-second analytics on commodity hardware. The biggest lesson? Spend 70 % of effort on data quality up front; performance tuning is easy when your foundation is solid.
Next up:
- Migrate nightly SQL Agent jobs to Azure Data Factory for cloud-native scale
- Add temporal tables for point-in-time auditing
- Expose a read-only REST API via OData to feed real-time sales widgets
If you're tackling similar data silos, fork the repo and let's compare notes—always happy to trade war stories.