Blog
Blog

Building an ELT Data Pipeline: Integrating Airbyte, BigQuery, dbt, and Metabase with Docker

Gunawan

Introduction

Dalam dunia modern berbasis data, proses pengolahan data dari berbagai sumber ke dalam bentuk yang siap dianalisis sangatlah krusial. Salah satu pendekatan populer adalah ETLT (Extract → Load → Transform → Visualize), yang memungkinkan data mentah dari berbagai sumber dikumpulkan, disimpan, diproses, lalu divisualisasikan untuk pengambilan keputusan.

Pada artikel ini, kita akan membahas bagaimana membangun sebuah pipeline ETLT menggunakan tools open source seperti:

  • Airbyte untuk mengekstrak dan memuat data dari database mentah (seperti PostgreSQL dan MySQL),
  • BigQuery sebagai data warehouse,
  • dbt (Data Build Tool) untuk proses transformasi data,
  • dan Metabase untuk visualisasi data.

Menariknya, seluruh proses ini akan dijalankan dengan bantuan Docker, sehingga memudahkan proses deployment, pengujian, dan replikasi pipeline di berbagai environment.

Langkah Setup Airbyte dan Metabase dengan Docker

1. Prepare Docker Airbyte

a. Git clone

git clone https://github.com/airbytehq/airbyte
cd airbyte

b. Jalankan Airbyte dengan Docker Compose

  • Persiapkan beberapa tools berikut dan juga edit ENV sesuai kebutuhan : .env
    • database via docker kebetulan menggunakan Postgre
version: '3.9'

services:
  postgres:
    image: postgres:12-alpine
    ports:
      - 5432:5432
    volumes:
      - ./apps/postgres:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=pass
      - POSTGRES_USER=postgre
      - POSTGRES_DB=airbyte
      - POSTGRES_ROOT_PASSWORD=akshenrke
  • ENV ( karena di kita perlu set supaya storage server kita tidak full)
DATABASE_USER=airbyte
DATABASE_PASSWORD=pass
DATABASE_HOST=host.docker.internal
DATABASE_PORT=5432
DATABASE_DB=airbyte
# translate manually 
DATABASE_URL=jdbc:postgresql://${DATABASE_HOST}:${DATABASE_PORT}/${DATABASE_DB} (do not include the username or password here)
DATABASE_URL=jdbc:postgresql://host.docker.internal:5432/airbyte
# DATABASE_URL=jdbc:postgresql://host.docker.internal:30

### JOBS ###
# Relevant to scaling.
SYNC_JOB_MAX_ATTEMPTS=2
SYNC_JOB_MAX_TIMEOUT_DAYS=2
JOB_MAIN_CONTAINER_CPU_REQUEST=1
JOB_MAIN_CONTAINER_CPU_LIMIT=1.50
JOB_MAIN_CONTAINER_MEMORY_REQUEST=6144MB
JOB_MAIN_CONTAINER_MEMORY_LIMIT=8192MB

NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_LIMIT=8192MB
NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_REQUEST=6144MB
NORMALIZATION_JOB_MAIN_CONTAINER_CPU_LIMIT=1.50
NORMALIZATION_JOB_MAIN_CONTAINER_CPU_REQUEST=1
# Relevant to scaling.
MAX_SYNC_WORKERS=2
MAX_SPEC_WORKERS=2
MAX_CHECK_WORKERS=2
MAX_DISCOVER_WORKERS=2
MAX_NOTIFY_WORKERS=2
SHOULD_RUN_NOTIFY_WORKFLOWS=false
  • Docker-compose airbyte 
version: "3.8"
#https://github.com/compose-spec/compose-spec/blob/master/spec.md#using-extensions-as-fragments
x-logging: &default-logging
  options:
    max-size: "100m"
    max-file: "5"
  driver: json-file
services:
  # hook in case we need to add init behavior
  # every root service (no depends_on) should depend on init
  init:
    image: airbyte/init:${VERSION}
    logging: *default-logging
    container_name: init
    command: /bin/sh -c "./scripts/create_mount_directories.sh /local_parent ${HACK_LOCAL_ROOT_PARENT} ${LOCAL_ROOT}"
    environment:
      - LOCAL_ROOT=${LOCAL_ROOT}
      - HACK_LOCAL_ROOT_PARENT=${HACK_LOCAL_ROOT_PARENT}
    volumes:
      - ${HACK_LOCAL_ROOT_PARENT}:/local_parent
  bootloader:
    image: airbyte/bootloader:${VERSION}
    logging: *default-logging
    container_name: airbyte-bootloader
    environment:
      - AIRBYTE_VERSION=${VERSION}
      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
      - DATABASE_URL=${DATABASE_URL}
      - DATABASE_USER=${DATABASE_USER}
      - LOG_LEVEL=${LOG_LEVEL}
    networks:
      - airbyte_internal
    depends_on:
      init:
        condition: service_completed_successfully
#  db:
#    image: airbyte/db:${VERSION}
#    logging: *default-logging
#    container_name: airbyte-db
#    restart: unless-stopped
#    environment:
#      - CONFIG_DATABASE_PASSWORD=${CONFIG_DATABASE_PASSWORD:-}
#      - CONFIG_DATABASE_URL=${CONFIG_DATABASE_URL:-}
#      - CONFIG_DATABASE_USER=${CONFIG_DATABASE_USER:-}
#      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
#      - DATABASE_URL=${DATABASE_URL}
#      - DATABASE_USER=${DATABASE_USER}
#      - POSTGRES_PASSWORD=${DATABASE_PASSWORD}
#      - POSTGRES_USER=${DATABASE_USER}
#    volumes:
#      - db:/var/lib/postgresql/data
#    networks:
#      - airbyte_internal
  worker:
    image: airbyte/worker:${VERSION}
    logging: *default-logging
    container_name: airbyte-worker
    restart: unless-stopped
    environment:
      - AIRBYTE_VERSION=${VERSION}
      - AUTO_DISABLE_FAILING_CONNECTIONS=${AUTO_DISABLE_FAILING_CONNECTIONS}
      - CONFIG_DATABASE_PASSWORD=${CONFIG_DATABASE_PASSWORD:-}
      - CONFIG_DATABASE_URL=${CONFIG_DATABASE_URL:-}
      - CONFIG_DATABASE_USER=${CONFIG_DATABASE_USER:-}
      - CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=${CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION:-}
      - CONFIG_ROOT=${CONFIG_ROOT}
      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
      - DATABASE_URL=${DATABASE_URL}
      - DATABASE_USER=${DATABASE_USER}
      - DEPLOYMENT_MODE=${DEPLOYMENT_MODE}
      - INTERNAL_API_HOST=${INTERNAL_API_HOST}
      - JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=${JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION:-}
      - JOB_MAIN_CONTAINER_CPU_LIMIT=${JOB_MAIN_CONTAINER_CPU_LIMIT}
      - JOB_MAIN_CONTAINER_CPU_REQUEST=${JOB_MAIN_CONTAINER_CPU_REQUEST}
      - JOB_MAIN_CONTAINER_MEMORY_LIMIT=${JOB_MAIN_CONTAINER_MEMORY_LIMIT}
      - JOB_MAIN_CONTAINER_MEMORY_REQUEST=${JOB_MAIN_CONTAINER_MEMORY_REQUEST}
      - LOCAL_DOCKER_MOUNT=${LOCAL_DOCKER_MOUNT}
      - LOCAL_ROOT=${LOCAL_ROOT}
      - LOG_LEVEL=${LOG_LEVEL}
      - LOG_CONNECTOR_MESSAGES=${LOG_CONNECTOR_MESSAGES}
      - MAX_CHECK_WORKERS=${MAX_CHECK_WORKERS}
      - MAX_DISCOVER_WORKERS=${MAX_DISCOVER_WORKERS}
      - MAX_SPEC_WORKERS=${MAX_SPEC_WORKERS}
      - MAX_SYNC_WORKERS=${MAX_SYNC_WORKERS}
      - MAX_NOTIFY_WORKERS=${MAX_NOTIFY_WORKERS}
      - SHOULD_RUN_NOTIFY_WORKFLOW=${SHOULD_RUN_NOTIFY_WORKFLOW}
      - NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_LIMIT=${NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_LIMIT}
      - NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_REQUEST=${NORMALIZATION_JOB_MAIN_CONTAINER_MEMORY_REQUEST}
      - NORMALIZATION_JOB_MAIN_CONTAINER_CPU_LIMIT=${NORMALIZATION_JOB_MAIN_CONTAINER_CPU_LIMIT}
      - NORMALIZATION_JOB_MAIN_CONTAINER_CPU_REQUEST=${NORMALIZATION_JOB_MAIN_CONTAINER_CPU_REQUEST}
      - SECRET_PERSISTENCE=${SECRET_PERSISTENCE}
      - SYNC_JOB_MAX_ATTEMPTS=${SYNC_JOB_MAX_ATTEMPTS}
      - SYNC_JOB_MAX_TIMEOUT_DAYS=${SYNC_JOB_MAX_TIMEOUT_DAYS}
      - TEMPORAL_HOST=${TEMPORAL_HOST}
      - TRACKING_STRATEGY=${TRACKING_STRATEGY}
      - WEBAPP_URL=${WEBAPP_URL}
      - WORKSPACE_DOCKER_MOUNT=${WORKSPACE_DOCKER_MOUNT}
      - WORKSPACE_ROOT=${WORKSPACE_ROOT}
      - METRIC_CLIENT=${METRIC_CLIENT}
      - OTEL_COLLECTOR_ENDPOINT=${OTEL_COLLECTOR_ENDPOINT}
      - JOB_ERROR_REPORTING_STRATEGY=${JOB_ERROR_REPORTING_STRATEGY}
      - JOB_ERROR_REPORTING_SENTRY_DSN=${JOB_ERROR_REPORTING_SENTRY_DSN}
      - ACTIVITY_MAX_ATTEMPT=${ACTIVITY_MAX_ATTEMPT}
      - ACTIVITY_INITIAL_DELAY_BETWEEN_ATTEMPTS_SECONDS=${ACTIVITY_INITIAL_DELAY_BETWEEN_ATTEMPTS_SECONDS}
      - ACTIVITY_MAX_DELAY_BETWEEN_ATTEMPTS_SECONDS=${ACTIVITY_MAX_DELAY_BETWEEN_ATTEMPTS_SECONDS}
      - WORKFLOW_FAILURE_RESTART_DELAY_SECONDS=${WORKFLOW_FAILURE_RESTART_DELAY_SECONDS}
      - AUTO_DETECT_SCHEMA=${AUTO_DETECT_SCHEMA}
      - USE_STREAM_CAPABLE_STATE=${USE_STREAM_CAPABLE_STATE}
      - MICRONAUT_ENVIRONMENTS=${WORKERS_MICRONAUT_ENVIRONMENTS}
      - APPLY_FIELD_SELECTION=${APPLY_FIELD_SELECTION}
      - FIELD_SELECTION_WORKSPACES=${FIELD_SELECTION_WORKSPACES}
#test env external database
#      - CONFIG_DATABASE_PASSWORD=${CONFIG_DATABASE_PASSWORD:-}
#      - CONFIG_DATABASE_URL=${CONFIG_DATABASE_URL:-}
#      - CONFIG_DATABASE_USER=${CONFIG_DATABASE_USER:-}
#      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
##      - DATABASE_URL=${DATABASE_URL}
#      - DATABASE_USER=${DATABASE_USER}
#      - POSTGRES_PASSWORD=${DATABASE_PASSWORD}
#      - POSTGRES_USER=${DATABASE_USER}
    configs:
      - flags
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - workspace:${WORKSPACE_ROOT}
      - ${LOCAL_ROOT}:${LOCAL_ROOT}
    ports:
      - "9000"
    networks:
      - airbyte_internal
    depends_on:
      bootloader:
        condition: service_completed_successfully
  server:
    image: airbyte/server:${VERSION}
    logging: *default-logging
    container_name: airbyte-server
    restart: unless-stopped
    environment:
      - AIRBYTE_ROLE=${AIRBYTE_ROLE:-}
      - AIRBYTE_VERSION=${VERSION}
      - CONFIG_DATABASE_PASSWORD=${CONFIG_DATABASE_PASSWORD:-}
      - CONFIG_DATABASE_URL=${CONFIG_DATABASE_URL:-}
      - CONFIG_DATABASE_USER=${CONFIG_DATABASE_USER:-}
      - CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=${CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION:-}
      - CONFIG_ROOT=${CONFIG_ROOT}
      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
      - DATABASE_URL=${DATABASE_URL}
      - DATABASE_USER=${DATABASE_USER}
      - JOB_MAIN_CONTAINER_CPU_LIMIT=${JOB_MAIN_CONTAINER_CPU_LIMIT}
      - JOB_MAIN_CONTAINER_CPU_REQUEST=${JOB_MAIN_CONTAINER_CPU_REQUEST}
      - JOB_MAIN_CONTAINER_MEMORY_LIMIT=${JOB_MAIN_CONTAINER_MEMORY_LIMIT}
      - JOB_MAIN_CONTAINER_MEMORY_REQUEST=${JOB_MAIN_CONTAINER_MEMORY_REQUEST}
      - JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=${JOBS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION:-}
      - LOG_LEVEL=${LOG_LEVEL}
      - NEW_SCHEDULER=${NEW_SCHEDULER}
      - SECRET_PERSISTENCE=${SECRET_PERSISTENCE}
      - TEMPORAL_HOST=${TEMPORAL_HOST}
      - TRACKING_STRATEGY=${TRACKING_STRATEGY}
      - JOB_ERROR_REPORTING_STRATEGY=${JOB_ERROR_REPORTING_STRATEGY}
      - JOB_ERROR_REPORTING_SENTRY_DSN=${JOB_ERROR_REPORTING_SENTRY_DSN}
      - WEBAPP_URL=${WEBAPP_URL}
      - WORKER_ENVIRONMENT=${WORKER_ENVIRONMENT}
      - WORKSPACE_ROOT=${WORKSPACE_ROOT}
      - GITHUB_STORE_BRANCH=${GITHUB_STORE_BRANCH}
      - MICRONAUT_ENVIRONMENTS=${WORKERS_MICRONAUT_ENVIRONMENTS}
      - AUTO_DETECT_SCHEMA=${AUTO_DETECT_SCHEMA} 
      - JAVA_OPTS=-Xms512m -Xmx12g
    ports:
      - "8001"
    configs:
      - flags
    volumes:
      - workspace:${WORKSPACE_ROOT}
      - data:${CONFIG_ROOT}
      - ${LOCAL_ROOT}:${LOCAL_ROOT}
    networks:
      - airbyte_internal
    depends_on:
      bootloader:
        condition: service_completed_successfully
  webapp:
    image: airbyte/webapp:${VERSION}
    logging: *default-logging
    container_name: airbyte-webapp
    restart: unless-stopped
    ports:
      - "80"
    environment:
      - AIRBYTE_ROLE=${AIRBYTE_ROLE:-}
      - AIRBYTE_VERSION=${VERSION}
      - API_URL=${API_URL:-}
      - CONNECTOR_BUILDER_API_URL=${CONNECTOR_BUILDER_API_URL:-}
      - INTERNAL_API_HOST=${INTERNAL_API_HOST}
      - CONNECTOR_BUILDER_API_HOST=${CONNECTOR_BUILDER_API_HOST}
      - OPENREPLAY=${OPENREPLAY:-}
      - PAPERCUPS_STORYTIME=${PAPERCUPS_STORYTIME:-}
      - TRACKING_STRATEGY=${TRACKING_STRATEGY}
    networks:
      - airbyte_internal
    depends_on:
      bootloader:
        condition: service_completed_successfully
  airbyte-temporal:
    image: airbyte/temporal:${VERSION}
    logging: *default-logging
    container_name: airbyte-temporal
    restart: unless-stopped
    environment:
      - DB=postgresql
      - DB_PORT=${DATABASE_PORT}
      - DYNAMIC_CONFIG_FILE_PATH=config/dynamicconfig/development.yaml
      - LOG_LEVEL=${LOG_LEVEL}
      - POSTGRES_PWD=${DATABASE_PASSWORD}
      - POSTGRES_SEEDS=${DATABASE_HOST}
      - POSTGRES_USER=${DATABASE_USER}
    volumes:
      - ./temporal/dynamicconfig:/etc/temporal/config/dynamicconfig
    networks:
      - airbyte_internal
  airbyte-cron:
    image: airbyte/cron:${VERSION}
    logging: *default-logging
    container_name: airbyte-cron
    restart: unless-stopped
    environment:
      - AIRBYTE_VERSION=${VERSION}
      - CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION=${CONFIGS_DATABASE_MINIMUM_FLYWAY_MIGRATION_VERSION}
      - DATABASE_PASSWORD=${DATABASE_PASSWORD}
      - DATABASE_URL=${DATABASE_URL}
      - DATABASE_USER=${DATABASE_USER}
      - DEPLOYMENT_MODE=${DEPLOYMENT_MODE}
      - LOG_LEVEL=${LOG_LEVEL}
      - REMOTE_CONNECTOR_CATALOG_URL=${REMOTE_CONNECTOR_CATALOG_URL}
      - TEMPORAL_HISTORY_RETENTION_IN_DAYS=${TEMPORAL_HISTORY_RETENTION_IN_DAYS}
      - UPDATE_DEFINITIONS_CRON_ENABLED=${UPDATE_DEFINITIONS_CRON_ENABLED}
      - WORKSPACE_ROOT=${WORKSPACE_ROOT}
      - MICRONAUT_ENVIRONMENTS=${CRON_MICRONAUT_ENVIRONMENTS}
    configs:
      - flags
    volumes:
      - workspace:${WORKSPACE_ROOT}
    networks:
      - airbyte_internal
    depends_on:
      bootloader:
        condition: service_completed_successfully
  airbyte-connector-builder-server:
    image: airbyte/connector-builder-server:${VERSION}
    logging: *default-logging
    container_name: airbyte-connector-builder-server
    restart: unless-stopped
    ports:
      - 80
    environment:
      - AIRBYTE_VERSION=${VERSION}
    networks:
      - airbyte_internal
    depends_on:
      bootloader:
        condition: service_completed_successfully
  airbyte-proxy:
    image: airbyte/proxy:${VERSION}
    container_name: airbyte-proxy
    restart: unless-stopped
    ports:
      - "8000:8000"
      - "8001:8001"
      - "8003:8003"
    environment:
      - BASIC_AUTH_USERNAME=${BASIC_AUTH_USERNAME}
      - BASIC_AUTH_PASSWORD=${BASIC_AUTH_PASSWORD}
      - BASIC_AUTH_PROXY_TIMEOUT=${BASIC_AUTH_PROXY_TIMEOUT}
    networks:
      - airbyte_internal
      - airbyte_public
    depends_on:
      - webapp
      - server
volumes:
  workspace:
    name: ${WORKSPACE_DOCKER_MOUNT}
  # the data volume is only needed for backward compatibility; when users upgrade
  # from an old Airbyte version that relies on file-based configs, the server needs
  # to read this volume to copy their configs to the database
  data:
    name: ${DATA_DOCKER_MOUNT}
  db:
    name: ${DB_DOCKER_MOUNT}
configs:
  flags:
    file: ./flags.yml
networks:
  airbyte_public:
  airbyte_internal:

Akan ada beberapa container yang ter create

docker-compose up -d

Perintah ini akan menjalankan Airbyte dan UI-nya yang bisa diakses di http://localhost:8000. Anda bisa mulai menambahkan sumber data seperti PostgreSQL atau MySQL, dan destinasi seperti BigQuery melalui antarmuka web ini.

2. Menyiapkan Metabase dengan Docker
Metabase adalah alat Business Intelligence (BI) open-source yang memudahkan dalam membuat dashboard dan visualisasi data.

a. Buat file docker-compose.metabase.yml

Buat file dengan nama docker-compose.metabase.yml dan isi seperti berikut:

NB : Siapkan database postgres untuk self hosted si metabase nanti

version: "3"
services:
  metabase:
    image: metabase/metabase:v0.54.4.2
    container_name: metabase-prod
    restart: always
    volumes:
      - /root/metabase-docker/storage/metabase-data:/metabase-data
    ports:
      - 3002:3000
    environment:
      - MB_DB_TYPE=postgres
      - MB_DB_DBNAME=metabase
      - MB_DB_PORT=5432
      - MB_DB_USER=postgres
      - MB_DB_PASS=ahitudia
      - MB_DB_HOST-127.0.0.1
    healthcheck:
      test: curl --fail -I http://localhost:3002/api/health || exit 1
      interval: 15s
      timeout: 5s
      retries: 5

b. Jalankan Metabase

docker-compose up -d

Akses Metabase melalui http://localhost:3000 dan ikuti panduan awal untuk menghubungkannya dengan BigQuery sebagai sumber data.

3. Install DBT

  1. Install DBT Core
  2. Contact admin to get your GCP and BigQuery account
    1. IAM account to access BigQuery
    2. Service account key file
  3. Setup your local ENV
    1. DBT_GOOGLE_PROJECT={gcp-project-id}
    2. DBT_GOOGLE_BIGQUERY_DATASET={bigquery-dataset-name}
    3. DBT_GOOGLE_BIGQUERY_KEYFILE={service-account-filepath}

Enjoy work on

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *