Designing Data Architecture for an Enterprise Data Warehouse

This chapter provides a blueprint for analyzing business requirements and designing a modern enterprise data architecture using Microsoft SQL Server. It contrasts major data management paradigms, evaluates classical and modern data warehousing frameworks, and details a three-tier Medallion Architecture (Bronze, Silver, Gold) engineered around the core principle of Separation of Concerns.

1. Project Requirements & Stakeholder Analysis

Before writing ETL code or creating database objects, an engineering team must analyze stakeholder requirements to prevent wasteful rework. Requirements dictate the precise shape, scope, and operational boundaries of the data warehouse.

Requirement Breakdown

• Primary Goal: Develop a modern data warehouse using Microsoft SQL Server to consolidate multi-source sales data, enabling high-performance analytical reporting and informed decision-making.
• Source Systems: Two distinct operational systems—ERP (Enterprise Resource Planning) and CRM (Customer Relationship Management)—supplied as raw CSV flat files residing in system folders.
• Data Quality Imperative: Raw source data is inherently unstructured or messy. Data must be cleansed, standardized, and validated prior to analytical consumption.
• System Integration: Merge heterogeneous CRM and ERP schemas into a single, cohesive, user-friendly data model tailored for business intelligence (BI).
• Historization Strategy: Stakeholders explicitly requested focus on the latest state of data. Slow-Changing Dimensions (SCD) and historical tracking are out of scope for this phase.
• Deliverable & Documentation: Provide clear, accessible data model documentation to support downstream analysts, data scientists, and business teams.

Note: Understanding project constraints early (e.g., "no historization required") saves significant development time by eliminating unnecessary dimensional tracking patterns like SCD Type 2.

2. Core Mental Model: Data Architecture as House Blueprinting

Real-Life Analogy

Building a data architecture is directly analogous to designing a house. Before construction begins, an architect produces a structural blueprint mapping room connections, plumbing, electrical flows, and load-bearing structures. Building without a blueprint leads to unstable, inefficient, and unlivable structures.

Similarly, a Data Architect designs the blueprint governing how data flows, transforms, integrates, and presents itself across the organization. This guarantees the data warehouse is structurally sound, scalable, performant, and maintainable.

3. High-Level Data Management Paradigms

When initiating a enterprise data initiative, architects must select the appropriate overall data management approach based on data structure, velocity, and intended use cases.

Architectural Decision

For this project, the data is entirely structured sales data targeted at analytics, BI reporting, and SQL-based decision-making. Therefore, the Data Warehouse paradigm is selected.

4. Data Warehouse Architectural Approaches

Once the decision to build a Data Warehouse is finalized, the team must select a core design framework to structure internal data processing.

Framework Comparison

1. Bill Inmon (Top-Down Enterprise Data Warehouse)

• Flow: Source $\rightarrow$ Staging $\rightarrow$ Enterprise DW (Normalized in 3rd Normal Form) $\rightarrow$ Subject-Specific Data Marts $\rightarrow$ BI Tools.
• Pros: Highly normalized, single source of truth, eliminates redundancy.
• Cons: Extremely slow to build, resource-intensive, high initial implementation friction.

2. Ralph Kimball (Bottom-Up Dimensional Design)

• Flow: Source $\rightarrow$ Staging $\rightarrow$ Dimensional Data Marts (Star / Snowflake Schemas) $\rightarrow$ BI Tools.
• Pros: Rapid time-to-value, highly optimized for business user reporting.
• Cons: Risk of transformation logic duplication across separate data marts, potential consistency loss over time without strict governance.

3. Data Vault (Dan Linstedt)

• Flow: Source $\rightarrow$ Staging $\rightarrow$ Central Vault split into Raw Vault (Hubs, Links, Satellites) and Business Vault $\rightarrow$ Information Marts.
• Pros: Highly scalable, auditable, agile, handles changing source systems seamlessly.
• Cons: High join complexity, steep learning curve, boilerplate heavy.

4. Medallion Architecture (Modern Multi-Tier Pattern)

• Flow: Source $\rightarrow$ Bronze (Raw Landing) $\rightarrow$ Silver (Cleansed/Standardized) $\rightarrow$ Gold (Business-Ready / Views).
• Pros: Intuitive progression, strict isolation of concerns, high auditability, streamlined dynamic presentation via virtualized views.
• Cons: Requires discipline to enforce layer-boundary rules.

Selected Approach: The Medallion Architecture

This project adopts The Medallion Architecture due to its clarity, modularity, and alignment with modern database systems like SQL Server.

5. Architectural Principle: Separation of Concerns (SoC)

The core principle governing high-performing data architecture is Separation of Concerns (SoC).

Core Rules of SoC in Data Architecture

1. Modular Responsibility: Divide a complex processing pipeline into distinct, independent tiers where each tier executes a single dedicated task.
2. Zero Functional Overlap: Never perform cleansing operations in the Gold layer; never execute business logic or aggregations in the Silver layer.
3. Strict Ingestion Routing: All source data must land in the initial landing layer (Bronze). Direct ingestion bypasses (e.g., loading source CSVs directly into Silver) violate architectural integrity and break auditability.

6. Granular Layer Design: Bronze Layer (Raw & Untouched)

The Bronze layer serves as the persistent landing platform for incoming raw source files.

Folder Ingestion (CRM / ERP CSV Files)
   |
   | (Batch Ingestion: Truncate & Insert)
   v
[ Bronze Layer: Physical SQL Tables ]

Specifications

• Objective: Store source data in its raw, unmodified form to guarantee complete lineage traceability and simplify debugging.
• Object Type: Physical SQL Server Tables (bronze.*).
• Load Strategy: Batch Processing via Full Load (TRUNCATE TABLE followed by INSERT).
• Transformations: Strictly None (0%). Malformed values, missing keys, and invalid dates remain untouched.
• Data Model: Mirrors the exact schema of source files.
• Target Audience: Data Engineers only (Restricted access).

Exemplary DDL Script (Bronze Layer)

CREATE SCHEMA bronze;
GO

-- Raw CRM Customer Data Ingestion Table
CREATE TABLE bronze.crm_cust_info (
    cst_id          INT,
    cst_key         VARCHAR(50),
    cst_firstname   VARCHAR(50),
    cst_lastname    VARCHAR(50),
    cst_marital_status VARCHAR(20),
    cst_gndr        VARCHAR(20),
    cst_create_date DATE
);
GO

7. Granular Layer Design: Silver Layer (Cleaned & Standardized)

The Silver layer acts as the data refining engine, transforming raw inputs into consistent data structures.

[ Bronze Layer: Physical Tables ]
   |
   | (Data Cleansing, Standardization, Normalization, Enrichment)
   v
[ Silver Layer: Physical SQL Tables ]

Specifications

• Objective: Store cleansed, validated, and normalized data ready for downstream modeling.
• Object Type: Physical SQL Server Tables (silver.*).
• Load Strategy: Batch Processing via Full Load (TRUNCATE TABLE followed by INSERT from Bronze).
• Transformations:
  • Data Cleansing (trimming whitespace, handling NULLs).
  • Data Standardization (converting coded values like 'M'/ 'F' to 'Male'/ 'Female').
  • Column Derivations & Type Casting.
  • No complex cross-domain business logic or cross-source joins.
• Data Model: Preserves source entity granularity without structural denormalization.
• Target Audience: Data Engineers, Data Analysts, Data Scientists.

Exemplary DML Transformation Script (Silver Layer)

CREATE SCHEMA silver;
GO

CREATE TABLE silver.crm_cust_info (
    cst_id          INT,
    cst_key         VARCHAR(50),
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    marital_status  VARCHAR(20),
    gender          VARCHAR(20),
    create_date     DATE
);
GO

-- Standardizing and Cleansing Transformation Pipeline
TRUNCATE TABLE silver.crm_cust_info;

INSERT INTO silver.crm_cust_info (
    cst_id,
    cst_key,
    first_name,
    last_name,
    marital_status,
    gender,
    create_date
)
SELECT 
    cst_id,
    cst_key,
    TRIM(cst_firstname) AS first_name,
    TRIM(cst_lastname) AS last_name,
    CASE 
        WHEN UPPER(TRIM(cst_marital_status)) = 'M' THEN 'Married'
        WHEN UPPER(TRIM(cst_marital_status)) = 'S' THEN 'Single'
        ELSE 'n/a'
    END AS marital_status,
    CASE 
        WHEN UPPER(TRIM(cst_gndr)) IN ('F', 'FEMALE') THEN 'Female'
        WHEN UPPER(TRIM(cst_gndr)) IN ('M', 'MALE') THEN 'Male'
        ELSE 'n/a'
    END AS gender,
    cst_create_date
FROM bronze.crm_cust_info;
GO

8. Granular Layer Design: Gold Layer (Business-Ready & Virtualized)

The Gold layer transforms clean data into consumable business objects organized for analytics.

[ Silver Layer: Cleansed Tables ]
   |
   | (Business Rules, Multi-Source Integration, Aggregations)
   v
[ Gold Layer: Virtualized SQL Views ]

Specifications

• Objective: Provide structured, fully integrated, performant data models formatted specifically for business intelligence tools and self-service analytics.
• Object Type: Virtualized Views (gold.*).
• Load Strategy: Virtualization (No Physical Storage Load). Views execute dynamically on top of Silver tables, avoiding redundant disk storage and batch delay.
• Transformations:
  • Cross-source Data Integration (e.g., joining CRM customers with ERP demographic data).
  • Business Rule Applications & Key Mappings.
  • Aggregations and Star Schema modeling (Facts & Dimensions).
• Data Model: Dimensional Schemas (Star Schema / Analytical Flat Views).
• Target Audience: Business Users, BI Developers, Data Analysts.

Exemplary View Definition (Gold Layer)

CREATE SCHEMA gold;
GO

-- Virtualized Dimensional Model for Customer Analytics
CREATE VIEW gold.dim_customers AS
SELECT 
    ROW_NUMBER() OVER (ORDER BY c.cst_id) AS customer_key, -- Surrogate Key
    c.cst_id                              AS customer_id,
    c.cst_key                             AS customer_code,
    c.first_name,
    c.last_name,
    c.marital_status,
    c.gender,
    c.create_date
FROM silver.crm_cust_info c;
GO

9. Layer Comparison & Governance Matrix

10. End-to-End Visual Data Flow Blueprint

The system architecture flows sequentially from source file ingestion through to multi-platform business consumption.

11. Architectural Decisions Summary & Project Roadmap

Key Architectural Choices

1. Platform Choice: Microsoft SQL Server utilized for hosting the database engine, managing storage schemas, executing T-SQL transformation logic, and serving analytical views.
2. Framework Choice: Medallion Architecture, leveraging physical tables for Bronze/Silver and virtualized SQL Views for Gold.
3. Storage vs. Maintenance Trade-off: Physicalizing Silver guarantees high query performance during transformations, while virtualizing Gold removes duplicate storage requirements and keeps presentation logic dynamic.
4. Development Scope: Excludes historization pipelines based on explicit project constraints, focusing efforts on robust ingestion, cleansing, and dimensional modeling.

Summary and Key Takeaways

• Architecture Requires Blueprints: Building a data warehouse without prior architectural design causes schema instability, duplicate transformations, and high maintenance costs.
• Requirement-Driven Strategy: Decisions regarding storage, historization, and data models must originate directly from business stakeholder constraints.
• Architectural Paradigm Selection: Pure data warehouses remain the optimal standard for structured operational data targeting BI reporting.
• Framework Choice: The Medallion Architecture simplifies data lineage by establishing clear processing milestones across Bronze (Raw), Silver (Cleansed), and Gold (Business-Ready) layers.
• Separation of Concerns: Each architectural tier must hold non-overlapping responsibilities. Data cleansing must never leakage into Gold, and business transformations must stay out of Silver.
• Raw Layer Integrity: The Bronze layer must retain source data verbatim to enable complete lineage auditability and root-cause debugging.
• Gold Virtualization: Constructing the Gold presentation layer using SQL Views minimizes physical storage overhead while offering dynamic schema flexibility for BI tools.
• Restricted Access Governance: Security policies should limit Bronze access exclusively to Data Engineers, directing analysts and business users toward Silver and Gold assets.