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.
Top comments (0)