Difficulty

Intermediate → Advanced

Reward

400 XP · 🏛️ Data Mart Architect

Prerequisites

Snowflake · Dimensional Modeling · PySpark

Primary Stack

Snowflake · Databricks · SQL

Target

Snowflake

Development Task

DEV-004

Enterprise Sales Reporting Data Mart

Design and build a high-performance dimensional sales reporting data mart in Snowflake with explicit grain definitions, conformed star schema dimensions, surrogate keys, Slowly Changing Dimensions (SCD), and proven financial reconciliation.

Project Brief

Understand the enterprise reporting challenge and dimensional data mart modeling requirements.

Business Scenario

The enterprise has successfully established a lakehouse processing pipeline that captures raw sales transactions into trusted Silver tables. However, business users across sales, marketing, and finance report severe difficulty querying these raw transaction-level datasets directly in BI tools (Power BI, Tableau) and SQL workbenches.

Analytical queries currently require expensive runtime table joins across unindexed transaction files, lack standardized business dimensions (Date, Store, Product, Customer), and produce conflicting revenue numbers due to disparate calculation logic across teams.

Development Objective

Design, model, and build a high-performance Enterprise Sales Reporting Data Mart in Snowflake. The data mart must establish explicit grain definitions, implement a dimensional Star / Snowflake Schema with Fact and Dimension tables, handle Slowly Changing Dimensions (SCD), enforce referential integrity, and publish pre-aggregated analytical views.

Expected Outcome

Explicit Grain Architecture

Define clear grain for transactional sales facts and periodic daily snapshot marts.

Dimensional Star Schema

Build curated conformed dimensions (Customer, Product, Store, Date) and Sales Fact tables.

Surrogate Key Generation

Implement stable integer surrogate keys for dimensional integrity and warehouse performance.

SCD Handling

Support Slowly Changing Dimensions (SCD Type 1 for corrections, SCD Type 2 for historical attributes).

Revenue Reconciliation

Guarantee 100% financial reconciliation between fact aggregates and underlying Silver source transactions.

Snowflake Warehouse Delivery

Deliver optimized Snowflake tables, clustering keys, and curated semantic views for BI reporting.

Developer Focus

This task focuses on data modeling judgment and SQL/PySpark implementation. You must make explicit modeling decisions regarding dimensional grain, surrogate keys, null dimension handling (-1 key), and metric definitions (gross sales vs net sales vs discounts).

Source System

Inspect the input source datasets: trusted transactions and reference entity feeds.

Source Datasets Overview

The data mart consumes from two primary sources: trusted Silver transaction logs and operational dimension reference feeds.

Transaction Source

Silver Delta Sales Transactions

Reference Feeds

Customer, Product, Store, Calendar

Data Volume

5M+ historical transactions, 50K daily

Target Modeling

Dimensional Star Schema

Target Engine

Snowflake Enterprise Warehouse

Primary Key Domain

Surrogate Keys + Business Natural Keys

Source Input Schema Entities

sales_transactions*

DELTA TABLE

transaction_id, store_id, customer_id, product_id, timestamp, quantity, unit_price, discount, total_amount.

customer_reference*

DELTA TABLE

customer_id, customer_name, email, segment, city, state, country, registration_date, status.

product_reference*

DELTA TABLE

product_id, product_name, category, sub_category, brand, cost_price, list_price, is_active.

store_reference*

DELTA TABLE

store_id, store_name, region, territory, store_type, open_date, square_footage, manager_name.

date_dimension*

DELTA / CSV

date_key, full_date, day_of_week, month, quarter, year, fiscal_year, is_weekend, is_holiday.

Known Source Constraints

Late-Arriving Dimensions

A sales transaction may arrive referencing a newly registered customer or product not yet present in dimension feeds.

Historical Dimension Attribute Updates

Customer addresses and product price categories change over time, requiring defined SCD strategies.

Null Business Keys in In-Store Sales

Guest or walk-in purchases may have null customer_ids, requiring mapped unknown (-1) surrogate keys.

Out-of-Order Transaction Dates

Transactions from previous calendar dates may be loaded in current batch runs.

Data Modeling Contract

The fact table must maintain 100% referential integrity with all conformed dimensions. No orphan foreign keys or dangling sales transactions are permitted in the production mart.

Expected Architecture

Dimensional model architecture: staging, surrogate key pipelines, conformed dimensions, and fact tables.

Data Mart Flow Architecture

The architecture transforms normalized transaction files into a dimensional schema optimized for analytical query workloads.

Trusted Source Layer

Databricks / Delta Lake

Validated Silver transaction logs and cleansed entity master files.

↓

Dimensional Staging

PySpark / SQL

Extract business entities, deduplicate natural keys, and calculate change hashes.

↓

Dimension Pipelines

PySpark / Delta Lake

Generate surrogate keys, handle SCD Type 1 and Type 2 historical records, map unknown (-1) keys.

↓

Fact Loading Pipeline

PySpark / Delta Lake

Perform dimension surrogate lookups, calculate additive measures (gross, discount, net, profit), and assemble fact rows.

↓

Snowflake Data Mart

Snowflake Tables & Views

Host physical DIM_* and FACT_* tables with clustering keys, constraints, and curated reporting views.

↓

Analytics & BI Layer

Tableau / Power BI / SQL

Serve sub-second sales performance dashboards, executive KPI decks, and cohort analysis.

Dimensional Schema Design

FACT_SALES_DAILY· Core Additive Metrics

Grain: One row per transaction line item. Keys: sale_key, date_key, customer_key, product_key, store_key. Measures: quantity, unit_price, gross_amount, discount_amount, net_amount, tax_amount.

DIM_CUSTOMER & DIM_PRODUCT· Conformed Dimensions

Maintains current and historical attributes, category rollups, customer demographics, and surrogate keys.

DIM_STORE & DIM_DATE· Hierarchical Conformed Dimensions

Provides geographic (Store -> Region -> Territory) and calendar (Date -> Month -> Quarter -> Year) rollups.

Architectural Expectations

Explicit Fact Grain Definition

Grain must be explicitly documented and enforced so analysts never perform accidental double-counting.

Referential Integrity & Default Keys

Unmatched natural keys must resolve to default dimension members (e.g., -1 for Unknown Customer).

Strict Financial Reconciliation

Aggregating net_amount from the fact table must equal the sum of trusted source transaction totals.

Snowflake Performance Optimization

Tables must leverage appropriate clustering keys (e.g., date_key, store_key) for fast partition pruning.

Modeling Principle

A data mart is judged by simplicity for analysts and speed of query execution. Avoid overly complex snowflaking where a clean star schema provides superior performance and readability.

Development Requirements

Implement the dimensional models, surrogate pipelines, SCD handlers, and Snowflake tables.

Developer Responsibilities

The implementation must address the following 15 engineering requirements across data modeling, transformation, reconciliation, and publishing.

Source Entity & Relationship Analysis

Analyze source schemas and define business entity boundaries.

Required Checklist

✓Inspect Silver transactions and master datasets to extract entities and cardinalities.

✓Identify natural candidate keys and relationship mappings.

✓Document logical ER diagrams covering all required business attributes.

Expected Outcome

Documented logical entity model.

Explicit Grain Definition

Define the fundamental grain of the sales fact table.

Required Checklist

✓Formally declare fact grain (one row per sales transaction item).

✓Verify that no measure in the fact table represents mixed levels of granularity.

✓Document grain declaration in data mart metadata.

Expected Outcome

Clear, non-ambiguous dimensional grain.

Dimension Modeling (Conformed Dimensions)

Design and implement conformed dimension tables.

Required Checklist

✓Build DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, and DIM_DATE.

✓Include descriptive hierarchy attributes for BI drilling (e.g., category -> subcategory).

✓Create unknown/unassigned default rows (-1 key) in each dimension.

Expected Outcome

Conformed, reusable dimension tables.

Surrogate Key Generation Pipeline

Generate durable, integer-based surrogate primary keys.

Required Checklist

✓Use deterministic hashing (SHA-256 / MD5) or auto-incrementing sequences for surrogate keys.

✓Ensure surrogate keys remain persistent and stable across re-runs.

✓Map business natural keys to surrogate keys during fact loading.

Expected Outcome

High-performance integer key joins in Snowflake.

Slowly Changing Dimensions (SCD) Implementation

Manage historical attribute changes in dimension tables.

Required Checklist

✓Implement SCD Type 1 (in-place overwrite) for attribute corrections (e.g., spelling).

✓Implement SCD Type 2 (effective_date, end_date, is_current flag) for historical tracking (e.g., customer segment changes).

✓Ensure fact lookups join against the dimension version active at transaction_timestamp.

Expected Outcome

Accurate point-in-time reporting without historical distortion.

Fact Table Modeling & Measure Calculation

Design and populate the central FACT_SALES table.

Required Checklist

✓Calculate fully additive metrics: gross_revenue, discount_amount, net_revenue, tax_amount, units_sold.

✓Include semi-additive or non-additive flags where appropriate.

✓Join transactions with dimension surrogate keys, defaulting missing lookups to -1.

Expected Outcome

Consolidated fact table ready for analytical queries.

Referential Integrity Enforcement

Guarantee zero orphaned records across fact and dimensions.

Required Checklist

✓Verify every foreign key in FACT_SALES resolves to a valid key in the respective dimension.

✓Implement pre-load foreign key assertion tests.

✓Isolate unresolved transactions into a dimensional quarantine table.

Expected Outcome

Rock-solid referential integrity across the warehouse.

Daily Aggregation Mart (Periodic Snapshot)

Build an aggregated summary mart for executive reporting.

Required Checklist

✓Build AGG_DAILY_STORE_SALES (grain: date_key + store_key).

✓Pre-calculate daily total orders, daily net revenue, average order value (AOV), and customer footfall.

✓Ensure daily aggregates reconcile mathematically with line-item facts.

Expected Outcome

Sub-second executive dashboard queries.

Snowflake Physical DDL & Schema Deployment

Deploy optimized DDL in Snowflake with proper data types and clustering.

Required Checklist

✓Write DDL for all DIM_*, FACT_*, and AGG_* tables in Snowflake.

✓Specify NOT NULL constraints on primary and foreign keys.

✓Define CLUSTER BY (date_key, store_key) for large fact tables.

Expected Outcome

Production Snowflake physical schema.

Incremental Mart Loading Strategy

Build incremental loading logic for daily mart refreshes.

Required Checklist

✓Support incremental fact appending / merging based on transaction_timestamp window.

✓Implement partition-level atomic replacement or MERGE operations.

✓Ensure safe backfill and reprocessing of historical date partitions.

Expected Outcome

Scalable daily refresh without full-table recomputations.

Data Quality & Integrity Framework

Enforce rigorous DQ rules across the dimensional model.

Required Checklist

✓Check for duplicate surrogate keys or duplicate natural keys in active dimension rows.

✓Assert that net_revenue = gross_revenue - discount_amount.

✓Verify that quantities and prices are non-negative for standard transactions.

Expected Outcome

Guaranteed data correctness in enterprise reports.

Financial Reconciliation Pipeline

Reconcile data mart metrics against trusted Silver source data.

Required Checklist

✓Automate reconciliation queries comparing sum(net_amount) between Silver and Fact.

✓Verify zero variance in daily transaction counts and monetary totals.

✓Generate reconciliation audit logs upon each pipeline execution.

Expected Outcome

Verifiable financial consistency for accounting audits.

Semantic Views & BI Acceleration

Create user-friendly semantic views for business analysts.

Required Checklist

✓Build VW_SALES_PERFORMANCE joining Fact and Dimensions with clear human-readable column aliases.

✓Create views for YoY growth, MoM revenue, and top-selling product categories.

✓Hide technical surrogate keys and expose clean business identifiers in presentation views.

Expected Outcome

Intuitive self-service reporting layer for non-technical users.

Operational Metadata & Lineage

Track transformation lineage and execution telemetry.

Required Checklist

✓Capture batch_id, inserted_at, updated_at, and source_row_count across all tables.

✓Log execution runtimes and partition write statistics.

Expected Outcome

Full auditability and operational transparency.

Data Mart Data Dictionary & Documentation

Publish comprehensive documentation for business and analytics engineers.

Required Checklist

✓Document every table, column, data type, description, and business formula.

✓Provide sample SQL queries illustrating common analytical use cases.

✓Document SCD rules and grain definitions.

Expected Outcome

Complete developer and business documentation.

Architectural Freedom

You are responsible for defining the exact physical schema, surrogate key strategies, and indexing. Ensure your design scales gracefully as transaction volumes grow from millions to billions of rows.

Testing Requirements

Demonstrate dimensional integrity, grain enforcement, financial reconciliation, and query performance.

Required Test Scenarios

Execute the following 12 validation scenarios to verify data mart integrity:

Fact Grain Uniqueness Test

Scenario

Assert uniqueness of primary composite key (transaction_id + line_number) in FACT_SALES.

Expected Result

Zero duplicate rows at the declared grain; test passes with 0 violations.

Dimension Surrogate Key Uniqueness

Scenario

Query DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE for duplicate surrogate keys.

Expected Result

Every surrogate key is 100% unique across all dimension tables.

Referential Integrity Assertion

Scenario

Perform LEFT JOIN from FACT_SALES to DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE on surrogate keys.

Expected Result

Zero NULL dimension matches; all fact rows resolve to either valid entities or the -1 default row.

Revenue Measure Reconciliation

Scenario

Compare sum(gross_amount - discount_amount) in FACT_SALES against sum(total_amount) in Silver source.

Expected Result

Variance equals exactly 0.00 across all date partitions.

Unknown Key Default Mapping

Scenario

Process transactions referencing non-existent customer_ids.

Expected Result

Fact rows are created with customer_key = -1 (Unknown Customer) without failing the pipeline.

SCD Type 2 Historical Versioning

Scenario

Update a customer's segment and re-run the dimension pipeline.

Expected Result

Previous row gets end_date set and is_current=false; new row is inserted with is_current=true and new surrogate key.

Point-in-Time Fact Lookup

Scenario

Verify that a transaction from 6 months ago joins to the customer dimension version active 6 months ago.

Expected Result

Fact record joins to historical dimension version rather than current version.

Daily Snapshot Mart Reconciliation

Scenario

Compare sum(net_revenue) in AGG_DAILY_STORE_SALES against sum(net_amount) in FACT_SALES grouped by date/store.

Expected Result

Daily aggregated totals reconcile 100% with line-item facts.

Incremental Backfill Test

Scenario

Re-run fact loading for a historical month that was previously processed.

Expected Result

Historical partition is cleanly updated without multiplying or duplicating existing rows.

Snowflake Constraint & Null Checks

Scenario

Assert NOT NULL on all fact foreign keys and verify primary key definitions in Snowflake.

Expected Result

All constraints pass verification without null key errors.

Analytical Query Performance

Scenario

Run complex 3-way join aggregations (Revenue by Region, Product Category, and Month) in Snowflake.

Expected Result

Query completes in sub-second time with effective partition pruning via clustering keys.

Semantic View Consistency

Scenario

Execute analytical queries through VW_SALES_PERFORMANCE and compare against direct SQL queries.

Expected Result

View returns identical numbers with clean, analyst-friendly column headers.

Testing Principle

A data mart is only as good as its data integrity. Every metric must be mathematically proven against raw source data.

Acceptance Criteria

Verify that the reporting data mart meets all production acceptance standards.

Definition of Done

The data mart implementation is accepted when all 10 criteria are met:

Fact Grain Explicitly Defined

Declared grain is enforced, documented, and validated with zero duplicate rows.

Star Schema Implemented

Conformed dimensions (Customer, Product, Store, Date) and Fact tables are fully implemented.

Surrogate Keys Generated

Stable integer surrogate keys are implemented across all dimension and fact tables.

SCD Type 1 & 2 Operational

Dimension pipelines accurately handle in-place updates and historical versioning.

100% Referential Integrity

Zero orphan foreign keys; missing natural keys map to conformed default (-1) rows.

Revenue Metrics Reconcile

Sum of net revenue in FACT_SALES matches Silver source transactions to the penny.

Snowflake Tables Deployed

Physical DDL deployed with appropriate clustering keys, constraints, and data types.

Semantic Views Created

Curated presentation views expose clean business aliases for self-service BI.

Incremental Refresh Proven

Daily mart loading runs incrementally without requiring full historical reloads.

All Automated Tests Pass

All 12 test scenarios pass with execution logs and query outputs attached.

Acceptance Rule

Sign-off requires reconciliation query outputs proving that total revenue in Snowflake matches the trusted Silver Delta tables.

Developer Deliverables

Provide all models, DDL scripts, transformation pipelines, and validation evidence.

Required Deliverables

The submission must include the following 11 artifacts:

Logical & Physical Data Model Diagrams

Schema diagrams illustrating Fact and Dimension entities, keys, and cardinalities.

Snowflake DDL Scripts

Production SQL DDL creating all DIM_*, FACT_*, AGG_* tables and views in Snowflake.

Dimension Transformation Pipelines

PySpark / SQL code generating surrogate keys, conformed attributes, and SCD Type 1/2 logic.

Fact Loading Pipeline

PySpark / SQL code performing surrogate key lookups and populating additive sales measures.

Daily Snapshot Aggregation Pipeline

Code generating AGG_DAILY_STORE_SALES periodic summary tables.

Data Quality & Assertion Suite

Automated tests asserting grain uniqueness, referential integrity, and not-null constraints.

Financial Reconciliation Script

SQL scripts comparing fact totals against Silver source tables with variance reporting.

Sample Analytical Queries

SQL queries showcasing revenue by category, customer segment growth, and regional store performance.

Semantic Presentation Views

DDL for user-friendly BI reporting views with business aliases.

Execution & Reconciliation Evidence

Logs, screenshots, and query result tables proving zero reconciliation variance.

Data Mart Dictionary & Architecture Guide

README document containing table definitions, metric formulas, and modeling rationale.

Submission Principle

Ensure your DDL and transformation code can be executed cleanly on a fresh Snowflake schema.

Engineering Constraints

Adhere to dimensional modeling best practices and operational standards.

Required Boundaries

The solution must comply with the following 9 engineering constraints:

Explicit Single Grain per Fact Table

Do not mix transaction line-items with store-level summary numbers in the same physical fact table.

No Ambiguous Metric Definitions

Every financial calculation (e.g., net_revenue) must have one unambiguous, documented formula.

Zero Dangling Foreign Keys

All foreign keys must resolve to valid dimension surrogate keys or default unknown (-1) rows.

Surrogate Key Immutability

Surrogate keys once assigned must never change for an existing dimension record.

Configuration Externalization

Snowflake database names, warehouse sizes, schema paths, and credentials must be externalized.

Idempotent Fact Partitions

Reprocessing a date partition must overwrite that partition cleanly without row duplication.

Performance Optimization

Design tables to utilize Snowflake micro-partition pruning via clustering keys.

Observable Reconciliation Telemetry

Reconciliation variances must trigger immediate alerts and fail pipeline execution.

Maintainable & Modular Code

Avoid massive monolithic SQL scripts; break pipeline into modular staging, dimension, and fact stages.

Constraint Notice

Mixing grains in fact tables or skipping surrogate key lookups will result in immediate code review failure.

Suggested Project Structure

Recommended repository layout for dimensional data mart engineering.

Recommended Project Layout

Structure your repository to clearly separate DDL schemas, dimension ETL, fact ETL, reconciliation scripts, and tests:

DEV-004-reporting-data-mart/
│
├── README.md
│
├── ddl/
│   ├── 01_dimensions.sql       # DDL for DIM_CUSTOMER, DIM_PRODUCT, DIM_STORE, DIM_DATE
│   ├── 02_facts.sql            # DDL for FACT_SALES and AGG_DAILY_STORE_SALES
│   └── 03_semantic_views.sql   # Presentation views for BI tools
│
├── src/
│   ├── staging/
│   │   └── stage_entities.py   # Extract and deduplicate natural keys
│   ├── dimensions/
│   │   ├── dim_customer.py     # SCD Type 1 & 2 handler for customers
│   │   ├── dim_product.py      # Product hierarchy and surrogate keys
│   │   └── dim_store.py        # Store territory mapping
│   ├── facts/
│   │   ├── fact_sales.py       # Surrogate lookup & measure calculations
│   │   └── agg_daily_sales.py  # Daily store periodic snapshot mart
│   └── reconciliation/
│       └── reconcile.py        # Automated Silver vs Fact variance checker
│
├── tests/
│   ├── test_grain_uniqueness.py
│   ├── test_surrogate_keys.py
│   ├── test_referential_integrity.py
│   └── test_financial_reconciliation.py
│
├── config/
│   ├── dev.yaml
│   └── prod.yaml
│
└── docs/
    ├── data_model.md           # Logical and physical star schema design
    └── data_dictionary.md      # Table and metric definitions

Module Responsibilities

ddl/

Production Snowflake SQL scripts defining tables, primary/foreign keys, clustering, and semantic views.

src/dimensions/

PySpark / SQL pipelines building conformed dimensions, surrogate keys, and SCD Type 1/2 handlers.

src/facts/

Pipelines performing dimension surrogate lookups and populating additive sales measures.

src/reconciliation/

Automated reconciliation scripts ensuring 100% financial consistency with Silver source.

tests/

Test suite validating grain uniqueness, surrogate keys, referential integrity, and reconciliation.

config/

Environment-specific Snowflake connection parameters, warehouse sizes, and schema names.

docs/

Data dictionary, entity-relationship diagrams, and analytical querying guide.

Design Rationale

Isolating dimension pipelines from fact loading ensures dimensions can be refreshed independently without re-executing expensive fact transformations.

Submission Checklist

Final engineering checklist before submitting DEV-004 for architectural review.

Final Review Checklist

Verify every checklist item before submitting your data mart:

✓

Fact Grain Formally Defined & Enforced

Declared grain is documented and verified with 0 duplicate composite key occurrences.

✓

Star Schema Deployed to Snowflake

DDL creates conformed DIM_* and FACT_* tables with appropriate data types and constraints.

✓

Surrogate Keys Implemented

Durable integer surrogate keys are generated and joined across all fact rows.

✓

SCD Type 1 & Type 2 Tested

Dimension pipelines accurately handle in-place corrections and historical version tracking.

✓

Zero Dangling Foreign Keys

100% of fact records resolve to valid dimension keys or default unknown (-1) rows.

✓

Financial Reconciliation Proven

Sum of net revenue in FACT_SALES matches Silver source transactions exactly.

✓

Daily Snapshot Mart Populated

AGG_DAILY_STORE_SALES reconciles mathematically with line-item facts.

✓

Semantic Presentation Views Built

User-friendly views expose clean column aliases for self-service BI analytics.

✓

Incremental Refresh Validated

Daily partitions can be loaded and re-run idempotently without data corruption.

✓

Clustering Keys Configured

Snowflake clustering keys are defined on date_key and store_key for fast partition pruning.

✓

All 12 Test Scenarios Executed

Test suite execution logs and query validation outputs are recorded in deliverables.

✓

Data Dictionary Published

Every table, column, and business formula is fully documented in docs/data_dictionary.md.

✓

Code Quality & Modularity

Code is structured into clean modular stages free of hard-coded credentials or debug scripts.

Ready for Review

Submit DEV-004 only after the dimensional models, surrogate pipelines, SCD handlers, Snowflake DDL, reconciliation scripts, and test suites have been verified.