Retail Analytics with dbt + PostgreSQL

Project Overview

This project showcases a modern retail analytics pipeline using dbt and PostgreSQL. The pipeline starts with raw data from CSV files and transforms it into a robust data warehouse schema. We build staging, dimension, and fact models that are ready for business intelligence and reporting. The project also includes snapshots, seeds, and a comprehensive suite of tests to ensure high data quality and maintainability.

Project Structure

A typical dbt project structure is used to organize the codebase, ensuring clarity and scalability.

retail/

├── models/

│   ├── staging/        # Cleaned raw data

│   └── marts/

│       ├── core/       # Dimension and fact tables

│       └── analytics/  # Aggregated business intelligence models

├── seeds/              # Static lookup tables (e.g., countries)

├── snapshots/          # Track historical changes (e.g., customer details)

├── tests/              # Custom data quality tests

├── analyses/           # Example ad-hoc analysis queries

└── dbt_project.yml     # Main project configuration

Key Features

• Staging Models: Efficiently clean and standardize raw data from CSV sources.
• Dimension & Fact Tables: Create a core data warehouse model for business reporting.
• Snapshots: Capture a historical record of changes in key entities over time.
• Seeds: Load static reference data, such as a list of countries.
• Tests: Validate data integrity with checks for uniqueness, non-null values, and referential integrity.

How to Run

Clone the repository and install dbt with the PostgreSQL adapter:

pip install dbt-postgres

Configure your Postgres profile in ~/.dbt/profiles.yml to connect to your database.

Load raw CSV files into your designated raw schema.

Execute the dbt pipeline from your terminal:

Load static lookup tables

dbt seed

Run the core pipeline (models)

dbt run

Execute all data quality tests

dbt test

Capture historical changes

dbt snapshot

Or, run everything at once

dbt build

Schema & Output

The pipeline organizes data into logical schemas to maintain a clear and accessible structure.

• raw: Source data loaded directly from CSVs.

• staging: Cleaned, intermediate models for transformation.

• analytics: Core dimension and fact tables for BI and reporting.

• lookup: Seeded lookup tables.

• snapshots: Historical tracking tables.