Postgres-SQLMesh-Metabase Business Intelligence Stack Demo

A demonstration of an easy-to-start, end-to-end business intelligence pipeline using modern data engineering tools.

🎯 What This Demo Shows

This repository showcases a complete BI pipeline that:

• Ingests raw order data into PostgreSQL
• Transforms data using SQLMesh's incremental processing
• Provides analytics-ready tables for visualization
• Serves dashboards via Metabase

Data Flow:

1. Ingestion: Python script generates and loads dummy order data into raw.orders
2. Transformation: SQLMesh transforms data through models:
   • stg_orders: Filters valid orders (excludes cancelled/refunded)
   • fct_revenue: Aggregates daily revenue metrics
3. Visualization: Metabase connects to transformed data for dashboards

📋 Prerequisites

Ensure you have the following installed:

• Docker - For running Postgres and Metabase
• uv - Fast Python package manager and runner

🚀 Quick Start

1. Clone this Repository

2. Start Services

Inside the repo, launch Postgres and Metabase in the background:

docker compose up -d

3. Install Dependencies

uv sync

4. Ingest Sample Data

Load dummy data into the raw schema:

uv run ingestion/ingest.py

This creates:

• Schema: raw
• Table: raw.orders (50 sample orders)

5. Run SQLMesh Models

Development Environment

Apply models to the dev environment for testing:

uv run sqlmesh plan dev

Review the plan and confirm to apply. Use database admin tools to inspect the created schemas and tables.

Production Environment

Promote the validated models to production:

uv run sqlmesh plan prod

SQLMesh will automatically create new schema for dev and prod environment upon first run as specify in a models file, in this case it's store_dev and store respectively

🗂️ Project Structure

business-intelligence-demo/
├── config.yaml           # SQLMesh configuration
├── docker-compose.yaml   # Postgres & Metabase services
├── pyproject.toml        # Python dependencies
├── ingestion/
│   └── ingest.py         # Data ingestion script
├── models/
│   ├── stg_orders.sql    # Staging model: filter valid orders
│   └── fct_revenue.sql   # Fact model: daily revenue metrics

📈 Accessing Metabase

Once services are running:

1. Open your browser and navigate to: http://localhost:3000
2. Set up your Metabase account (first time only)
3. Connect to the database
4. Build dashboards using the transformed data from the store schema

Available schemas in Metabase:
You can setup Metabase to only allow query from store (prod)

🔧 Development Workflow

What's next?

1. Normally ingestion/ingest.py will be your ETL script (can be somewhere else)
2. Edit SQL files in the models/ directory
3. Run uv run sqlmesh plan dev to test changes
4. Validate results using a database tool (e.g., DBeaver, pgAdmin)
5. Run uv run sqlmesh plan prod to deploy to production

Your data in raw schema will never touched again after ingestion, the only thing that change over time is your SQLMesh models, learn more using keyword medallion architecture

🧹 Cleanup

Stop and remove all containers and volumes:

docker compose down -v  # -v removes volumes including Postgres data

🤝 Contributing

This is a demonstration project. Feel free to fork and customize for your own BI pipeline needs.

📄 License

This project is a public domain.