Lab 01 — Project Setup & the Data Generator

services:
  postgres:
    image: postgres:16
    container_name: griddp-postgres
    environment:
      POSTGRES_USER: griddp
      POSTGRES_PASSWORD: griddp
      POSTGRES_DB: marketplace
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U griddp -d marketplace"]
      interval: 5s
      timeout: 3s
      retries: 10

  redpanda:
    image: redpandadata/redpanda:v24.1.7
    container_name: griddp-redpanda
    command:
      - redpanda start
      - --smp 1
      - --overprovisioned
      - --node-id 0
      - --kafka-addr PLAINTEXT://0.0.0.0:9092
      - --advertise-kafka-addr PLAINTEXT://redpanda:9092
    ports:
      - "9092:9092"
    healthcheck:
      test: ["CMD-SHELL", "rpk cluster health | grep -q 'Healthy:.*true'"]
      interval: 5s
      timeout: 3s
      retries: 10

  dagster:
    image: dagster/dagster-celery-k8s:latest
    container_name: griddp-dagster
    # We run Dagster locally with `dagster dev` in later labs; this service
    # is a placeholder webserver so the UI port is reserved and documented.
    command: ["dagster-webserver", "-h", "0.0.0.0", "-p", "3000"]
    ports:
      - "3000:3000"
    depends_on:
      postgres:
        condition: service_healthy

  metabase:
    image: metabase/metabase:v0.49.6
    container_name: griddp-metabase
    ports:
      - "3001:3000"
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  pgdata:

docker compose up -d        # -d = detached, runs in the background
docker compose ps           # show the status of each service

-- Operational source schema for the GPU marketplace.
-- Safe to re-run: drops and recreates from scratch.

DROP TABLE IF EXISTS rentals;
DROP TABLE IF EXISTS gpu_prices;
DROP TABLE IF EXISTS gpus;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    name         TEXT NOT NULL,
    tier         TEXT NOT NULL CHECK (tier IN ('free', 'pro', 'enterprise')),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE gpus (
    gpu_id   SERIAL PRIMARY KEY,
    model    TEXT NOT NULL UNIQUE,
    vram_gb  INTEGER NOT NULL
);

-- Prices change over time; each row is valid from a moment onward.
-- The "current" price for a GPU is the row with the latest valid_from.
CREATE TABLE gpu_prices (
    gpu_id          INTEGER NOT NULL REFERENCES gpus(gpu_id),
    price_per_hour  NUMERIC(6, 2) NOT NULL,
    valid_from      TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (gpu_id, valid_from)
);

CREATE TABLE rentals (
    rental_id    SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(customer_id),
    gpu_id       INTEGER NOT NULL REFERENCES gpus(gpu_id),
    hours        NUMERIC(8, 2) NOT NULL CHECK (hours > 0),
    started_at   TIMESTAMPTZ NOT NULL
);

-- Indexes that an ingestion job will lean on (incremental reads by time).
CREATE INDEX idx_rentals_started_at ON rentals(started_at);
CREATE INDEX idx_customers_created_at ON customers(created_at);

cd generator
uv init --no-workspace          # creates pyproject.toml if you don't have one
uv add "psycopg[binary]" faker  # the Postgres driver + fake-data library

"""Populate the marketplace source database with realistic data.

Run after `docker compose up`:   uv run python generate.py
Re-running rebuilds the schema and regenerates from scratch.
"""
import os
import random
from datetime import datetime, timedelta, timezone

import psycopg
from faker import Faker

fake = Faker()
random.seed(42)        # deterministic runs — same data every time
Faker.seed(42)

DSN = os.environ.get(
    "GRIDDP_DSN",
    "postgresql://griddp:griddp@localhost:5432/marketplace",
)

# The GPU catalog: model name, VRAM, and a base hourly price.
GPU_CATALOG = [
    ("NVIDIA A100 40GB", 40, 1.80),
    ("NVIDIA A100 80GB", 80, 2.40),
    ("NVIDIA H100 80GB", 80, 4.20),
    ("NVIDIA L4 24GB", 24, 0.65),
    ("NVIDIA L40S 48GB", 48, 1.10),
    ("NVIDIA T4 16GB", 16, 0.35),
    ("NVIDIA V100 32GB", 32, 0.95),
    ("AMD MI300X 192GB", 192, 3.90),
    ("AMD MI250 128GB", 128, 2.10),
    ("NVIDIA RTX 4090 24GB", 24, 0.55),
]
TIERS = ["free", "pro", "enterprise"]
TIER_WEIGHTS = [0.5, 0.4, 0.1]   # most users are free, few are enterprise
NOW = datetime.now(timezone.utc)


def run():
    with psycopg.connect(DSN, autocommit=True) as conn:
        # 1. (Re)create the schema from the DDL file next to this script.
        here = os.path.dirname(os.path.abspath(__file__))
        with open(os.path.join(here, "schema.sql")) as f:
            conn.execute(f.read())
        print("schema created")

        # 2. Customers — ~50, each created at some point in the last year.
        customer_ids = []
        for _ in range(50):
            created = NOW - timedelta(days=random.randint(0, 365))
            tier = random.choices(TIERS, weights=TIER_WEIGHTS)[0]
            row = conn.execute(
                "INSERT INTO customers (name, tier, created_at) "
                "VALUES (%s, %s, %s) RETURNING customer_id",
                (fake.company(), tier, created),
            ).fetchone()
            customer_ids.append(row[0])
        print(f"inserted {len(customer_ids)} customers")

        # 3. GPUs + a price history (1–3 price points each).
        gpu_ids = []
        for model, vram, base_price in GPU_CATALOG:
            gid = conn.execute(
                "INSERT INTO gpus (model, vram_gb) VALUES (%s, %s) "
                "RETURNING gpu_id",
                (model, vram),
            ).fetchone()[0]
            gpu_ids.append(gid)

            price = base_price
            valid_from = NOW - timedelta(days=365)
            for _ in range(random.randint(1, 3)):
                conn.execute(
                    "INSERT INTO gpu_prices (gpu_id, price_per_hour, valid_from) "
                    "VALUES (%s, %s, %s)",
                    (gid, round(price, 2), valid_from),
                )
                # next price point: drift +/- 15%, later in time
                price *= random.uniform(0.85, 1.15)
                valid_from += timedelta(days=random.randint(60, 150))
        print(f"inserted {len(gpu_ids)} GPUs with price history")

        # 4. Rentals — a few hundred, spread over the last 90 days.
        rental_count = 0
        for _ in range(400):
            started = NOW - timedelta(
                days=random.randint(0, 90),
                hours=random.randint(0, 23),
            )
            conn.execute(
                "INSERT INTO rentals (customer_id, gpu_id, hours, started_at) "
                "VALUES (%s, %s, %s, %s)",
                (
                    random.choice(customer_ids),
                    random.choice(gpu_ids),
                    round(random.uniform(0.5, 72.0), 2),
                    started,
                ),
            )
            rental_count += 1
        print(f"inserted {rental_count} rentals")

    print("done — marketplace populated")


if __name__ == "__main__":
    run()

uv run python generate.py

# connect to the running container's database
docker compose exec postgres psql -U griddp -d marketplace

SELECT count(*) FROM customers;
SELECT count(*) FROM gpus;
SELECT count(*) FROM gpu_prices;
SELECT count(*) FROM rentals;

-- a realistic-looking join: revenue per GPU model
SELECT g.model,
       count(*)                AS rentals,
       round(sum(r.hours), 1)  AS total_hours
FROM rentals r
JOIN gpus g ON g.gpu_id = r.gpu_id
GROUP BY g.model
ORDER BY total_hours DESC;

\q   -- quit psql

cd ..                    # back to mini-griddp/ root
git add docker-compose.yml generator/
git commit -m "Lab 01: Compose stack + Postgres source schema + data generator"