DEV Community: Otwoma E.

Building a Sales Analytics Data Warehouse with PostgreSQL

Otwoma E. — Wed, 26 Aug 2026 02:26:39 +0000

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.

Ideal Transformation Steps in dbt

Otwoma E. — Tue, 11 Oct 2022 21:54:03 +0000

Analytics is a cornerstone of decision making at many of the world's most successful institutions. Having the right tools to make the most of your data is therefore key in maintaining an advantage over competitors and innovating at the cutting edge. Everyday, more than 2.5 quintillion bytes of raw data is produced; that's 18 zeros. Transforming this massive amount of data is therefore a key step towards making it useful for any practical applications.
dbt (data build tool) is a powerful tool to transform raw data into various forms to allow the generation of insights for any organization. The modern data stack can be broken down to three key steps, Extraction, Transformation and Loading, aka ETL. dbt handles the T in ETL. In order to create an efficient data warehouse, dbt enables a stepwise data transformation process that allows for flexibility and modularity that supports version control like conventional software engineering.

Sources (src_) - Raw Data

The first layer in your transformation process is the ingestion of raw data from your production systems. This data can be stored on Snowflake, S3 or BigQuery, dbt allows for integration from a variety of different sources. No additional transformation and are essentially a base for the rest of the transformation process. These tables have names with the prefix src_... .sql and follow the dbt snake-case naming convention.

Staging (stg_) - Lightly Transformed Raw Data

Typically materialized as views, these are tables with a one-to-one relationship with sources tables. They contain very light transformations that clean and standardize the raw data before any further modifications are done downstream. For instance, you may want to change the names of certain columns to match business logic or avoid confusion with existing logic, truncate dates or change the currency or a payment column.
Make sure to limit your transformations to these four:

Type casting
Simple calculations
Categorizing data (think CASE WHEN ... statements)
Renaming columns If a staging table is built on top of a source table names src_farmer_payments.sql then the staging table built on top of it can be called stg_farmer_payments.sql.

Intermediate (int_) Models

After transforming data to the staging models, you may find it useful to have another layer before creating the final fact or dimension models that are the final marts that go in yoru data warehouse. Intermediate models are useful for performing slightly more intricate calculations meant to enrich the data before a mart is created. For instance, based on stg_farmer_payments.sql from the examples above, we can have int_farmer_payments_pivoted.sql which provides a different look at the data and can be used to create a different mart.
Intermediate models should be built on staging models to leverage the transformations already done on raw data. You can use intermediate models for:

Isolating complex processes in your transformation process for easier debugging later
Collapse a column to constituent parts before using it to create a mart, e.g ungrouping payments or undoing a pivot.
Simplifying data marts - put some joins in intermediate models to have simpler data marts.

Fact (fct_) Models

The next step in the dbt transformation process is creating fact models that contain immutable data. This is typically data about events that have occurred or are currently happening. For instance, this can be data about user sessions, transactions, orders, votes, stories, sales calls etc. They are typically narrow and long tables. The naming convention is fct_ ... .sql so we could have fct_payment_receipts.sql to track each payment received by a company.

Dimensions (dim_) Models

These models are based on previous models that are already transformed. Typically used to represent data about a person, place or thing. These models are very wide and short. For instance, you can use these to store all the static information about a patient in a hospital, or about a bank branch or a city landmark. Naming convention is as follows dim_patients.sql, dim_students.sql, you get the idea.

Custom Models

It is entirely possible that your organization has special data and business logic that would necessitate the creation of a new model with a different name prefix. dbt allows you to do this, however, you should endeavour to maintain dbt folder structure recommendations to ensure your project is clean.

Conclusion

These transformation steps are inline with the recommended dbt folder structure. You can expected to see src_ tables in the Sources folder, dim_ in the Dimensions folder and so forth. You can read more about dbt folder structure and transformations on the dbt docs page.