sql-data-warehouse-project: A Full Medallion Warehouse Built in Plain T-SQL
A SQL Server blueprint for raw ingestion, cleansing, versioned history, and star-schema presentation, with one smart rule that decides which system gets to be the source of truth.
- The repo's real strength is not the Bronze-Silver-Gold naming, but the rules it encodes for cleansing, history, and source-of-truth selection.
- Silver does the hard work with defensive T-SQL, using window functions and conversion guards to turn messy source rows into trustworthy records.
- Gold is smarter than a simple presentation layer because it resolves conflicting master data instead of pretending one system owns everything.
- The project is genuinely educational because it shows how far a disciplined SQL Server stack can go, while also exposing the limits of hardcoded paths and manual setup.
Most warehouse demos stop at structure. This one gets interesting when it starts making decisions. The repo shows how a disciplined SQL Server stack can do more than stage and copy data. It can clean it, version it, and choose which system gets to speak when sources disagree.
The clever part is not the layers. It is the rules.
`krishnamx07/sql-data-warehouse-project` is a medallion warehouse built in T-SQL, but the architecture is only half the story. The other half is the policy embedded in the scripts: how to dedupe records, how to parse bad dates, how to preserve change history, and how to merge CRM and ERP into a single customer view.
That matters because most warehouse tutorials are procedural. They describe how to move rows around. This repo is more opinionated. It treats data quality and business authority as first-class concerns, which is the difference between a classroom exercise and something you could actually explain to a team.
Bronze is a landing zone, not a place to think
The Bronze layer keeps the promise simple: load raw data, do not interpret it yet. In `proc_load_bronze.sql`, the project uses `BULK INSERT` and a truncate-and-load pattern, which is blunt but practical for a clean ingestion stage. The value is isolation. Raw CRM and ERP files stay separate from the logic that will eventually judge them.
Silver is where SQL earns its keep
Silver is the technical center of gravity. The scripts use `TRIM()` to clean stray whitespace, `TRY_CONVERT()` to avoid hard failures on messy date formats, and `ROW_NUMBER()` to keep the newest record when duplicates show up. That is not decorative SQL. It is defensive SQL.
The most useful pattern here is the combination of cleansing and history. In product history, the repo uses `LEAD()` to calculate ending dates for versioned rows, which turns a flat stream of changes into a Slowly Changing Dimension style timeline. The result is a warehouse that can answer not just what the latest value is, but what changed and when.
The warehouse's smartest rule is its least flashy
The Gold layer is where the project stops being generic. In `dim_customers`, it does not simply merge fields and call it done. It applies a source-of-truth rule: CRM is authoritative for gender, and ERP only fills the gap when CRM is missing. That is a real modeling decision, not a syntax trick.
| Problem | Single-layer ETL | This repo |
|---|---|---|
| Raw source files | Loaded directly into the final table | Kept isolated in Bronze |
| Dirty strings and dates | Often fail the load or get ignored | Cleaned in Silver with `TRIM()` and `TRY_CONVERT()` |
| Duplicate rows | Usually left to downstream reporting | Resolved with `ROW_NUMBER()` |
| Changing product history | Flattened into the latest value | Versioned with `LEAD()` |
| Conflicting master data | One system wins by accident | CRM wins for gender, ERP is fallback |
| Business model | Physical tables only | Gold is exposed as views over the latest Silver data |
That rule is the best proof that the repo is thinking like an engineer, not a student. It admits that source systems have uneven quality. It also makes the hierarchy explicit, which is exactly what breaks in real warehouses when nobody writes the rule down.
Gold turns cleaned rows into a business model
Gold exposes a star schema with customer, product, and sales dimensions, but it does so through views instead of a second physical load step. That keeps the model live against the latest Silver data, while still giving analysts a business-friendly surface. Surrogate keys are generated in the warehouse, so source-system IDs no longer leak directly into the presentation layer.
This is where the repo becomes a useful learning artifact. It is not pretending that modeling is just naming tables nicely. It shows how a warehouse can present curated dimensional data while still preserving the lineage beneath it.
| Question | What a simple script does | What this repo does |
|---|---|---|
| How does data enter? | One-off insert or copy job | Bronze landing zone with bulk ingestion |
| How is bad data handled? | Usually not handled | Silver cleansing and conversion guards |
| How is history preserved? | Often lost | SCD-style versioning with window functions |
| How is business meaning assigned? | Implicitly or manually | Explicit source-of-truth rules in Gold |
| How is the model surfaced? | Physical tables | Views over the latest curated data |
This is a strong learning repo, with real constraints
The project feels solid as a teaching reference because it includes layered scripts, documentation, and tests. The structure is clean, the naming is deliberate, and the pipeline is easy to follow. It is also honest in the ways many demo repos are not: the `D:\` bulk-load paths are hardcoded, and there is no containerized or CI story around the database.
That trade-off is useful. It reminds you that good warehouse design is not the same thing as full production packaging. You can learn the modeling discipline here, then still need to solve deployment, environment portability, and orchestration elsewhere.
What to take from it
The main lesson is that modern warehouse thinking is mostly about rules, not tools. Bronze, Silver, and Gold are helpful labels, but the real value comes from the decisions embedded inside them. This repo proves you can encode those decisions cleanly in plain T-SQL, and that is enough to make a serious warehouse.