# Modern Data Stack (ELT)

> Complete ELT pipeline with Airbyte, dbt, Postgres data warehouse, and Metabase BI

- Category: DevOps & CI/CD
- Difficulty: advanced
- Components: airbyte, dbt, postgres, metabase
- Tags: data-engineering, etl, elt, airbyte, dbt, metabase, analytics
- Source: https://docker.recipes/devops/modern-data-stack-elt

## Overview

Airbyte is an open-source data integration platform that emerged in 2020 to democratize ELT (Extract, Load, Transform) pipelines by providing 300+ pre-built connectors for databases, APIs, and SaaS applications. Unlike traditional ETL tools that require expensive licensing and complex configurations, Airbyte enables organizations to sync data from sources like Stripe, Salesforce, PostgreSQL, and MongoDB into data warehouses with minimal technical overhead. This modern data stack combines Airbyte's connector ecosystem with dbt's SQL-based transformation framework, PostgreSQL as a cost-effective data warehouse, and Metabase for self-service analytics. Together, these tools create a complete ELT pipeline that extracts raw data through Airbyte, loads it into PostgreSQL, transforms it using dbt's modeling capabilities, and visualizes results through Metabase dashboards. This stack appeals to data teams at startups and mid-sized companies who need enterprise-grade analytics capabilities without the complexity and cost of platforms like Snowflake, Fivetran, and Tableau. The combination delivers production-ready data pipelines that can handle millions of records while maintaining the flexibility to customize transformations and create interactive dashboards for business stakeholders.

## Features

- Airbyte's 300+ pre-built connectors including Stripe, Salesforce, PostgreSQL, MySQL, MongoDB, and REST APIs
- dbt's SQL-based transformation layer with version control, testing, and documentation capabilities
- PostgreSQL 16 data warehouse with JSONB support for semi-structured data from APIs
- Metabase's drag-and-drop dashboard builder with 40+ visualization types and SQL query interface
- Incremental data synchronization with configurable sync frequencies from hourly to daily
- dbt's data lineage tracking and model dependencies for complex transformation workflows
- Airbyte's normalization feature that automatically converts nested JSON to relational tables
- Metabase's self-service analytics allowing business users to create reports without SQL knowledge

## Use cases

- E-commerce companies consolidating data from Shopify, payment processors, and marketing platforms into unified customer analytics
- SaaS startups building customer health dashboards by combining CRM data, product usage metrics, and billing information
- Marketing teams creating attribution reports by syncing data from Google Ads, Facebook, email platforms, and web analytics
- Financial teams automating monthly reporting by extracting data from accounting software, banks, and payment processors
- Product teams analyzing user behavior by combining application databases with third-party analytics tools
- Operations teams monitoring business KPIs by consolidating data from support tickets, inventory systems, and sales platforms
- Data teams prototyping analytics solutions before investing in enterprise platforms like Snowflake and Looker

## Prerequisites

- Minimum 4GB RAM (8GB recommended) to handle Airbyte worker processes and PostgreSQL with concurrent transformations
- Docker Engine 20.10+ with Docker Compose V2 for proper container orchestration and networking
- Basic SQL knowledge for creating dbt models and debugging data transformation issues
- Understanding of your source systems' API rate limits and authentication methods for Airbyte connectors
- Available ports 3000, 5432, and 8000 for Metabase, PostgreSQL, and Airbyte web interfaces
- At least 20GB free disk space for data warehouse storage and Airbyte's temporary processing files

## docker-compose.yml

```yaml
services:
  # Airbyte - Data Integration
  airbyte-server:
    image: airbyte/server:latest
    container_name: airbyte-server
    restart: unless-stopped
    ports:
      - "${AIRBYTE_PORT:-8000}:8000"
    environment:
      - DATABASE_URL=jdbc:postgresql://airbyte-db:5432/airbyte
      - DATABASE_USER=airbyte
      - DATABASE_PASSWORD=${AIRBYTE_DB_PASSWORD}
      - TRACKING_STRATEGY=segment
    depends_on:
      - airbyte-db

  airbyte-worker:
    image: airbyte/worker:latest
    container_name: airbyte-worker
    restart: unless-stopped
    environment:
      - DATABASE_URL=jdbc:postgresql://airbyte-db:5432/airbyte
      - DATABASE_USER=airbyte
      - DATABASE_PASSWORD=${AIRBYTE_DB_PASSWORD}
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
    depends_on:
      - airbyte-db

  airbyte-db:
    image: postgres:13-alpine
    container_name: airbyte-db
    restart: unless-stopped
    environment:
      - POSTGRES_USER=airbyte
      - POSTGRES_PASSWORD=${AIRBYTE_DB_PASSWORD}
      - POSTGRES_DB=airbyte
    volumes:
      - airbyte_db_data:/var/lib/postgresql/data

  # Data Warehouse
  warehouse:
    image: postgres:16-alpine
    container_name: data-warehouse
    restart: unless-stopped
    ports:
      - "${WAREHOUSE_PORT:-5432}:5432"
    environment:
      - POSTGRES_USER=${WAREHOUSE_USER}
      - POSTGRES_PASSWORD=${WAREHOUSE_PASSWORD}
      - POSTGRES_DB=warehouse
    volumes:
      - warehouse_data:/var/lib/postgresql/data

  # dbt for transformations
  dbt:
    image: ghcr.io/dbt-labs/dbt-postgres:latest
    container_name: dbt
    volumes:
      - ./dbt:/usr/app
    working_dir: /usr/app
    environment:
      - DBT_PROFILES_DIR=/usr/app
    depends_on:
      - warehouse
    profiles:
      - tools

  # Metabase for BI
  metabase:
    image: metabase/metabase:latest
    container_name: metabase
    restart: unless-stopped
    ports:
      - "${METABASE_PORT:-3000}:3000"
    environment:
      - MB_DB_TYPE=postgres
      - MB_DB_DBNAME=metabase
      - MB_DB_PORT=5432
      - MB_DB_USER=metabase
      - MB_DB_PASS=${METABASE_DB_PASSWORD}
      - MB_DB_HOST=metabase-db
    depends_on:
      - metabase-db
      - warehouse

  metabase-db:
    image: postgres:15-alpine
    container_name: metabase-db
    restart: unless-stopped
    environment:
      - POSTGRES_USER=metabase
      - POSTGRES_PASSWORD=${METABASE_DB_PASSWORD}
      - POSTGRES_DB=metabase
    volumes:
      - metabase_db_data:/var/lib/postgresql/data

volumes:
  airbyte_db_data:
  warehouse_data:
  metabase_db_data:
```

## .env

```bash
# Modern Data Stack
AIRBYTE_PORT=8000
WAREHOUSE_PORT=5432
METABASE_PORT=3000

# Airbyte
AIRBYTE_DB_PASSWORD=airbyte_password

# Data Warehouse
WAREHOUSE_USER=warehouse
WAREHOUSE_PASSWORD=warehouse_password

# Metabase
METABASE_DB_PASSWORD=metabase_password
```

## Usage notes

- Airbyte at http://localhost:8000 - configure data sources
- Metabase at http://localhost:3000 - create dashboards
- Data warehouse accessible at localhost:5432
- Run dbt: docker compose --profile tools run dbt run
- Airbyte syncs data to warehouse, dbt transforms it
- Create dbt models in ./dbt/models directory

## Troubleshooting

- Airbyte sync fails with 'Connection timeout': Increase connector timeout settings in Airbyte UI and verify source system API limits
- dbt run fails with 'relation does not exist': Check Airbyte normalization completed successfully and verify table names in PostgreSQL warehouse schema
- Metabase shows 'Database connection error': Ensure warehouse container is fully started and credentials in MB_DB_* environment variables match PostgreSQL settings
- Airbyte worker container exits with code 137: Increase Docker memory allocation to 6GB+ as Airbyte workers are memory-intensive during large syncs
- dbt models fail with 'permission denied': Grant proper SELECT/CREATE permissions to warehouse user on public schema and source tables
- High memory usage during syncs: Configure Airbyte connector batch sizes and enable incremental sync modes to reduce memory footprint

