GDPR-Compliant Open Source
Local Data Stack

Why replace the Google stack?

The GoExplore project originally relied on Google Sheets, BigQuery, and Looker Studio — all US-hosted cloud services. For a European team processing retail sales data, this creates GDPR exposure: data leaves the EU, vendor lock-in is real, and costs scale unpredictably.

The goal was to rebuild the same analytical capability — data storage, SQL querying, and KPI dashboards — using only open source tools running locally, with zero dependency on any external cloud provider.

Cleaning the environment & deploying Metabase

Before building anything, the local Docker environment was in a messy state — a legacy Apache Superset stack left over from a previous attempt, plus several orphaned containers holding onto ports. Getting to a clean slate was the first real engineering challenge.

Problem 1 — Port conflicts

The first nginx container launch failed immediately on port 8080. The second attempt on port 8081 failed too. Both ports were held by stopped (but not removed) containers — a common Docker pitfall.

# Error on port 8080
docker run --name website -p 8080:80 nginx
→ Bind for 0.0.0.0:8080 failed: port is already allocated

# Error on port 8081 too
docker run --name website -p 8081:80 nginx
→ Bind for 0.0.0.0:8081 failed: port is already allocated

# Diagnosis: find what owns the port
lsof -i :8080
docker ps -a  # stopped containers still hold ports

Problem 2 — Legacy Superset stack

A full Apache Superset installation was discovered: 11 containers (running and stopped), ~8GB of images across 14 image layers, and 11 named volumes. The decision was to remove everything and start clean.

# Stop and remove all containers
docker rm -f $(docker ps -aq)

# Remove Superset images
docker rmi apache/superset:latest \
  superset-superset:latest \
  superset-superset-worker:latest \
  superset-superset-init:latest \
  superset-superset-node:latest \
  superset-superset-websocket:latest

# Remove all Superset volumes
docker volume rm superset_db_home superset_redis \
  superset_superset_data superset_superset_home

# Final clean state: only nginx, postgres:17, redis:7 remain
docker images

Deploying Metabase with docker-compose

With a clean environment, Metabase was deployed as a single container pointed at the native Postgres 18 instance. The critical networking detail: Docker containers on Mac cannot reach localhost — they must use host.docker.internal instead.

# docker-compose.yml
services:
  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    ports:
      - "3000:3000"
    environment:
      MB_DB_TYPE: postgres
      MB_DB_DBNAME: metabase
      MB_DB_PORT: 5432
      MB_DB_USER: postgres
      MB_DB_PASS: postgres123
      MB_DB_HOST: host.docker.internal  # Mac bridge — not localhost!
    restart: unless-stopped

# Create the Metabase app database first
psql -h localhost -U postgres -c "CREATE DATABASE metabase;"

# Launch
docker compose up -d

# Watch for "Metabase Initialization COMPLETE"
docker logs -f metabase

Loading the GoExplore dataset

The GoExplore dataset consists of four CSV files covering retail sales activity across 21 countries. Rather than uploading to a cloud warehouse, all files were loaded directly into a local Postgres 18 instance via Metabase's built-in CSV upload — no ETL pipeline required.

1

Docker environment cleanup

Removed a legacy Apache Superset stack (~8GB of images) and 11 orphaned containers, leaving only nginx, postgres:17, and redis as base images.

2

Metabase deployment

Single docker-compose.yml with Metabase pointing at the native Postgres 18 instance via host.docker.internal — the Docker bridge to the Mac's localhost.

3

CSV upload → Postgres tables

Four CSV files uploaded via Metabase's UI. Each becomes a Postgres table automatically — no schema definition needed.

4

VS Code SQL connection

SQLTools + PostgreSQL driver installed. Connected directly to the metabase database where the go_ tables live.

-- Four tables created from CSV upload
SELECT table_name, COUNT(*) AS rows
FROM information_schema.tables t
JOIN (
  SELECT table_name, COUNT(*) FROM go_daily_sales_20260625183936 UNION ALL
  SELECT table_name, COUNT(*) FROM go_products_20260625183647 UNION ALL
  SELECT table_name, COUNT(*) FROM go_retailers_20260625183636 UNION ALL
  SELECT table_name, COUNT(*) FROM go_methods_20260625184054
) counts ON t.table_name = counts.table_name
WHERE t.table_name LIKE 'go_%';

The four tables join on three keys:

go_daily_sales  →  go_products    (via product_number)
go_daily_sales  →  go_retailers   (via retailer_code)
go_daily_sales  →  go_methods     (via order_method_code)

KPIs built for the GoExplore case

Three KPIs were selected to answer the two business questions: new market potential (European expansion) and channel performance (specialty vs. general retailers).

Queries behind the dashboards

All three KPIs are powered by SQL queries written in VS Code and executed directly in Metabase. No ORM, no abstraction layer — plain SQL against Postgres.

KPI 1 — Average Order Value by Country

SELECT
  r.country,
  ROUND(AVG(s.quantity * s.unit_sale_price), 2) AS avg_order_value,
  COUNT(*) AS total_orders
FROM go_daily_sales_20260625183936 s
JOIN go_retailers_20260625183636 r
  ON s.retailer_code = r.retailer_code
GROUP BY r.country
ORDER BY avg_order_value DESC;

KPI 2 — Specialty vs General Retailer Performance

SELECT
  r.type,
  ROUND(AVG(s.quantity * s.unit_sale_price), 2) AS avg_order_value,
  COUNT(*) AS total_orders
FROM go_daily_sales_20260625183936 s
JOIN go_retailers_20260625183636 r
  ON s.retailer_code = r.retailer_code
GROUP BY r.type
ORDER BY avg_order_value DESC;

KPI 3 — Average Product Price by Category

SELECT
  p.product__line,
  ROUND(AVG(p.unit_price), 2) AS avg_price
FROM go_products_20260625183647 p
GROUP BY p.product__line
ORDER BY avg_price DESC;

Four ways to query data from one editor

Following the two-world approach from the team guide, VS Code became the single development environment for querying both the local Postgres stack and the shared Google BigQuery project. Four distinct query methods were explored — each with different trade-offs for speed, reproducibility, and output format.

World 1 — Local Postgres query (SQLTools)

-- test2.sql — runs directly against local Postgres 18
-- @connection: Metabase Postgres

-- KPI 1: Average Order Value by Country
SELECT r.country,
       ROUND(AVG(s.quantity * s.unit_sale_price), 2) AS avg_order_value,
       COUNT(*) AS total_orders
FROM go_daily_sales_20260625183936 s
JOIN go_retailers_20260625183636 r ON s.retailer_code = r.retailer_code
GROUP BY r.country
ORDER BY avg_order_value DESC;
-- Result: Singapore 1,179,853 | Mexico 1,007,605 | Brazil 968,333

World 2 — BigQuery via Python script (with CSV export)

# query_csv.py — queries BigQuery, saves result to CSV
from google.cloud import bigquery
import csv

client = bigquery.Client(project='mystical-proj-500509')

query = """
    SELECT Country, COUNT(*) AS stores
    FROM go_explore_project.retailers
    GROUP BY Country
    ORDER BY stores DESC
    LIMIT 5
"""

rows = client.query(query).result()

with open('bigquery_result.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['Country', 'stores'])
    for row in rows:
        writer.writerow([row['Country'], row['stores']])

World 2 — BigQuery via Jupyter notebook

# Cell 1 — run once to set up the connection
from google.cloud import bigquery

client = bigquery.Client(project='mystical-proj-500509')

def bq(sql):
    """Run a BigQuery SQL query and return a DataFrame."""
    return client.query(sql).to_dataframe()

# Cell 2 — any subsequent cell is pure SQL
bq("""
    SELECT r.Country,
           SUM(s.Quantity * s.Unit_sale_price) AS Total_Revenue
    FROM go_explore_project.daily_sales AS s
    JOIN go_explore_project.retailers AS r
        ON s.Retailer_code = r.Retailer_code
    GROUP BY r.Country
    ORDER BY Total_Revenue DESC
""")
# → Renders as a formatted pandas DataFrame table in the notebook

What this project taught

Completion Certificate