Skip to main content

Metabase Integration

Metabase is used for data exploration and business intelligence across the Sprout platform.

Overview​

Metabase provides:

  • SQL query interface for PostgreSQL
  • Interactive dashboards
  • Data visualization
  • Business intelligence reporting
  • Self-service analytics

Setup​

Installation​

Metabase can be run via Docker or as a managed service:

Docker (Local Development)​

docker run -d \
  -p 3000:3000 \
  -e MB_DB_TYPE=postgres \
  -e MB_DB_DBNAME=metabase \
  -e MB_DB_PORT=5432 \
  -e MB_DB_USER=metabase \
  -e MB_DB_PASS=password \
  -e MB_DB_HOST=host.docker.internal \
  --name metabase \
  metabase/metabase:latest

Managed Service​

For production, consider:

  • Metabase Cloud (hosted)
  • Self-hosted on dedicated server
  • Docker Compose setup

Configuration​

Database Connection​

Connect Metabase to the Sprout PostgreSQL database:

  1. Connection Settings:

    • Type: PostgreSQL
    • Host: Database host (from Doppler DATABASE_URL)
    • Port: 5432 (default)
    • Database Name: Database name from DATABASE_URL
    • Username: Database user
    • Password: Database password

  2. Connection String (Alternative):

    postgresql://user:password@host:5432/database

Environment Variables​

If running Metabase with Docker, configure via environment variables:

  • MB_DB_TYPE=postgres
  • MB_DB_DBNAME=<database_name>
  • MB_DB_PORT=5432
  • MB_DB_USER=<user>
  • MB_DB_PASS=<password>
  • MB_DB_HOST=<host>

Data Sources​

Primary Database​

Connect to the main Sprout database:

  • sprout_backend database
  • sprout_etl database (if separate)

Key Tables​

Common tables to explore:

  • Events: TicketmasterEvent, SkyboxResult, CombinedResult
  • Inventory: SkyboxInventory, SkyboxSoldInventory
  • Purchases: AssignedSoldInventory
  • Monitoring: MonitoredListing, MonitoredListingPriceHistory
  • Pipeline: EventRun, EventRunItem

Common Queries​

Event Analysis​

-- Recent events
SELECT * FROM "TicketmasterEvent"
ORDER BY "createdAt" DESC
LIMIT 100;

-- Event matching statistics
SELECT 
  COUNT(*) as total_events,
  COUNT(DISTINCT "venueId") as unique_venues
FROM "TicketmasterEvent";

Inventory Analysis​

-- Current inventory status
SELECT 
  status,
  COUNT(*) as count
FROM "SkyboxInventory"
GROUP BY status;

-- Price trends
SELECT 
  DATE("createdAt") as date,
  AVG(price) as avg_price
FROM "MonitoredListingPriceHistory"
GROUP BY DATE("createdAt")
ORDER BY date DESC;

Purchase Analysis​

-- Purchase statistics
SELECT 
  status,
  COUNT(*) as count,
  SUM(amount) as total_amount
FROM "AssignedSoldInventory"
GROUP BY status;

Dashboards​

  1. Event Overview

    • Total events
    • Events by venue
    • Recent events timeline

  2. Inventory Management

    • Current inventory levels
    • Inventory by status
    • Price trends

  3. Purchase Analytics

    • Purchase volume
    • Success rates
    • Revenue metrics

  4. Pipeline Health

    • Event run statistics
    • Processing times
    • Error rates

Best Practices​

Performance​

  • Use database indexes for common queries
  • Create materialized views for complex aggregations
  • Schedule cache refreshes for dashboards
  • Limit query timeouts

Security​

  • Use read-only database user for Metabase
  • Restrict access to sensitive tables
  • Use row-level security if needed
  • Audit queries and access

Data Modeling​

  • Create saved questions for common queries
  • Use Metabase models for complex logic
  • Document data dictionary
  • Create collections for organization

Access​

Local Development​

Production​

  • Access via configured domain
  • SSO integration (if configured)
  • User management and permissions

Integration with Sprout​

Data Flow​

  1. Sprout Services → Write data to PostgreSQL
  2. Metabase → Reads from PostgreSQL
  3. Users → Query and visualize via Metabase UI

Real-time Updates​

  • Metabase queries reflect database changes
  • Dashboards can be refreshed manually or on schedule
  • Consider caching for performance

Troubleshooting​

Connection Issues​

  • Verify database credentials
  • Check network connectivity
  • Verify database user permissions
  • Check firewall rules

Performance Issues​

  • Optimize slow queries
  • Add database indexes
  • Use query caching
  • Consider read replicas

Data Sync​

  • Metabase reads directly from database
  • No separate sync process needed
  • Changes appear immediately (depending on query cache)

References​