Introduction

Modern businesses generate large volumes of transactional data from systems such as ecommerce platforms, point-of-sale systems, CRM tools, and financial applications. However, raw data alone does not create business value. Organizations need reliable systems that transform operational data into structured, trusted, and accessible information for decision-making.

In this project, I built an end-to-end sales analytics data warehouse using PostgreSQL, SQL, Python, and Streamlit. The objective was to simulate a real-world analytics engineering workflow: ingest raw sales data, clean and transform it, model it into a dimensional warehouse, create analytical datasets, and prepare the data for business intelligence reporting.

The project demonstrates key data warehousing concepts:

• Data warehouse architecture
• ETL/ELT workflows
• Data modeling
• Star schema design
• Fact and dimension tables
• Surrogate keys
• Data quality validation
• Analytics layers

Data Warehouse Architecture

The project follows a layered architecture:

CSV Files
   |
   ↓
Raw Layer
   |
   ↓
Staging Layer
   |
   ↓
Data Mart
   |
   ↓
Analytics Layer
   |
   ↓
Dashboard

Each layer has a specific responsibility.

Raw Layer

The raw layer stores source data exactly as received.

Tables include:

• Customers
• Products
• Sales transactions

Keeping raw data unchanged provides:

• Auditability
• Reproducibility
• Easier debugging
• Ability to rebuild downstream models

In a production environment, this layer could receive data from APIs, SaaS applications, operational databases, or cloud storage.

Staging Layer

The staging layer prepares raw data for analytical use.

Transformations include:

• Removing duplicates
• Standardizing formats
• Cleaning missing values
• Validating data types
• Applying business rules

Separating cleaning logic from reporting logic ensures that downstream analytics are consistent and reusable.

Dimensional Modeling

The warehouse uses a star schema design.

                 dim_customer

                       |

                       |

dim_date -------- fact_sales -------- dim_product

The design separates measurable business events from descriptive context.

Fact Tables

The central fact table is:

fact_sales

The grain is:

One row represents one sales transaction.

Metrics stored include:

• Quantity sold
• Unit price
• Sales amount
• Profit

Dimension Tables

Customer Dimension

Stores customer attributes such as:

• Name
• Location
• Customer characteristics

Supports analysis such as customer lifetime value and segmentation.

Product Dimension

Stores:

• Product names
• Categories
• Brands
• Suppliers

Supports product and profitability analysis.

Date Dimension

Stores:

• Year
• Month
• Quarter
• Week
• Day

Enables efficient time-based analysis.

Surrogate Keys

The warehouse uses surrogate keys to create stable internal identifiers.

Example:

customer_key = 1
customer_id = 4318

The source identifier is retained for traceability, while the warehouse manages its own keys.

This approach supports changing source systems and historical tracking.

Analytics Layer

Business-ready views were created to answer important questions.

Monthly Sales Performance

Provides:

• Revenue trends
• Profit trends
• Orders
• Units sold

Product Performance

Provides:

• Best-selling products
• Revenue by product
• Product profitability

Customer Lifetime Value

Provides:

• Customer spend
• Average order value
• Customer profitability

Customer Segmentation

Uses customer behaviour to classify customers into segments such as:

• Champions
• Loyal Customers
• Potential Loyalists
• At Risk
• Lost

Sales Channel Performance

Analyzes:

• Revenue by channel
• Profit by channel
• Orders by channel

Sales Representative Performance

Measures:

• Revenue generated
• Orders handled
• Profit contribution

Data Quality

Reliable analytics require trusted data.

Validation checks include:

• Primary key constraints
• Foreign key constraints
• Row count validation
• Revenue reconciliation
• Referential integrity checks
• Duplicate detection

Future Improvements

Automated Data Pipelines

The current workflow can be enhanced using orchestration tools such as:

• Apache Airflow
• Dagster
• Prefect

These would automate scheduling, monitoring, and failure handling.

dbt Transformations

SQL transformations could be migrated to dbt to provide:

• Version-controlled models
• Automated testing
• Documentation
• Data lineage

Incremental Loading

Instead of reprocessing all data, production pipelines would process only new or changed records.

Benefits:

• Faster execution
• Lower compute costs
• Better scalability

Slowly Changing Dimensions

Customer and product attributes change over time.

Slowly Changing Dimensions, especially Type 2 dimensions, allow historical changes to be tracked while preserving previous states.

Production Data Sources

If the data came from real systems, the architecture would likely include:

• Shopify APIs
• Salesforce
• ERP systems
• Payment platforms
• Cloud databases

Additional considerations would include:

• Authentication
• API limits
• Schema changes
• Duplicate events
• Late arriving data
• Data governance

Cloud Deployment

A production warehouse could move from local PostgreSQL to platforms such as:

• Snowflake
• BigQuery
• Amazon Redshift
• Azure Synapse

The dashboard could then connect directly to cloud analytics infrastructure.

Conclusion

This project demonstrates the core principles behind modern analytics engineering.

It combines:

• Data ingestion
• Data cleaning
• Dimensional modeling
• SQL transformations
• Business analytics
• Dashboard preparation

The key lesson is that analytics is not only about writing queries. It is about building reliable systems that transform raw data into trusted information for decision-making.

I had fun doing this kind of project outside of work, so I will definitely do more.