Airbyte in Production — ELT Pipelines, Custom Connectors, and dbt Transformations

Move the Data First, Shape It Later

Every analytics platform starts with the same unglamorous problem: getting data out of dozens of source systems and into one place you can query. APIs paginate differently, databases speak different dialects, and each SaaS tool has its own idea of authentication. Writing and maintaining that extraction code by hand is a tax that never stops.

Airbyte is an open-source data movement platform built to erase that tax. It ships a large catalog of connectors that read from sources and write to destinations, and it leans hard into the ELT model: extract from the source, load raw into the warehouse, and transform afterward with a tool built for the job.

That last part matters. Airbyte deliberately does not try to be your transformation engine. It moves bytes reliably and hands the shaping off to dbt downstream — a division of labor we will return to, because it changed materially with Destinations V2.

The Moving Parts

An Airbyte deployment is a set of cooperating services — a web server and API, a scheduler, a workload launcher, and a temporal-based orchestrator — plus a metadata database. Connectors themselves run as short-lived jobs. The unit you configure is a connection: a source, a destination, a stream selection, and a sync schedule.

• Source. A connector that reads from an external system — Postgres, Stripe, Salesforce, an S3 bucket of CSVs.
• Destination. A connector that writes to a warehouse, lake, or database — BigQuery, Snowflake, Postgres, Redshift.
• Stream. A table or resource within a source; you choose which to sync and the sync mode (full refresh or incremental).
• Connection. The wiring of source to destination, with a catalog, sync mode per stream, and a schedule.

Connectors communicate over the Airbyte Protocol, a well-defined contract of JSON messages (records, state, logs). Because the protocol is public, a connector is just a Docker image that speaks it — which is exactly why building your own is tractable.

Getting a Local Instance with abctl

The fastest path to a running Airbyte is abctl, Airbyte's command-line installer. Because Airbyte runs on Kubernetes, abctl spins up a local Kind cluster inside Docker and deploys the platform into it — the same architecture you will run in production, just on one machine.

# Install abctl (macOS / Linux)
curl -LsfS https://get.airbyte.com | bash -
abctl version

# Bring up a local Airbyte (needs Docker running; 4+ CPUs, 8GB RAM)
abctl local install

# Fetch the generated admin credentials, then open http://localhost:8000
abctl local credentials

First install pulls a lot of images and can take a while. On a small VM, add --low-resource-mode to fit into 2 CPUs (the in-browser Connector Builder is disabled in that mode). When you are done, abctl local uninstall stops the containers and keeps your data; add --persisted to wipe it.

Production: Helm on Kubernetes

For anything beyond a laptop, Airbyte recommends deploying with Helm onto a real Kubernetes cluster. The chart lives in the airbytehq/charts repository, and you drive it with a values.yaml override file rather than editing the chart itself.

helm repo add airbyte https://airbytehq.github.io/charts
helm repo update

# See what versions exist before you pin one
helm search repo airbyte --versions

kubectl create namespace airbyte

helm install airbyte airbyte/airbyte \
  --namespace airbyte \
  --values ./values.yaml \
  --version 2.0.18

The defaults are fine for a demo but not for production. Three overrides matter most: point log and state storage at object storage (S3 or GCS) instead of in-cluster volumes, move the metadata store to an external managed PostgreSQL, and wire secrets into a real secret manager. Managing that values.yaml and the surrounding cloud resources as code is a natural fit for Terraform modules, so environments stay reproducible.

Sync Modes and Incremental Loads

Each stream in a connection has a sync mode that controls how much data moves and how the destination is updated. Getting this right is the difference between a five-minute incremental sync and a nightly full-table reload that hammers your source.

• Full Refresh | Overwrite. Re-reads everything, replaces the destination table. Simple and stateless; fine for small dimension tables.
• Full Refresh | Append.Re-reads everything, appends each sync's rows — useful for snapshotting.
• Incremental | Append. Reads only records newer than the last cursor value, appends them. Requires a cursor field like updated_at.
• Incremental | Append + Dedup. Incremental read, but the destination keeps one current row per primary key. The everyday default for keeping a warehouse copy in sync.

For databases, Airbyte also supports log-based CDC on sources like Postgres and MySQL, reading the write-ahead log rather than polling a cursor column. If you are weighing that against a dedicated streaming path, our deep dive on Change Data Capture covers the trade-offs between batch CDC and continuous streaming.

Destinations V2: Raw and Final Tables

How Airbyte lands data in your warehouse changed with Destinations V2 and its Typing and Deduping step. Each stream now maps to exactly two tables. A raw table in the airbyte_internal schema holds the untouched JSON payload in an _airbyte_data column, and a final table in your target schema breaks those fields out into typed, top-level columns.

The win is a clean one-to-one mapping — one stream, one table — instead of the old behaviour that exploded nested objects into a sprawl of unnested sub-tables. Content problems (a value that will not fit its type) are recorded in an _airbyte_meta column on the row rather than failing the whole sync, so a single bad record no longer blocks the batch.

Transformations Belong to dbt Now

Older Airbyte bundled a "basic normalization" step and a custom-transformation hook that could trigger dbt from inside a sync. Both are gone. Airbyte deprecated Custom Normalization (dbt) in OSS, citing low usage and high maintenance cost, and removed basic normalization with the move to Destinations V2.

This is a feature, not a regression. Airbyte lands typed data; transforming it into business models is a separate concern with a mature tool. You point dbt at the final tables Airbyte produced and build your marts there, with tests and version control — exactly the workflow in our guide to dbt in production.

-- models/staging/stg_orders.sql
-- Build a clean staging model on top of Airbyte's final table.
with source as (
    select * from {{ source('airbyte', 'orders') }}
)

select
    id                     as order_id,
    customer_id,
    cast(amount as numeric) as amount,
    status,
    _airbyte_extracted_at  as loaded_at
from source
-- Airbyte flags per-row content issues here; skip the broken ones.
where _airbyte_meta:errors is null or array_size(_airbyte_meta:errors) = 0

The one thing you lose is automatic sequencing — nothing runs dbt for you when a sync finishes. That job moves to an orchestrator, which is where it belonged all along.

Orchestrating Sync-then-Transform

The canonical production pattern is a two-step pipeline: trigger the Airbyte sync, wait for it to succeed, then run dbt against the freshly loaded tables. Airbyte exposes a REST API and Terraform provider to trigger syncs, and the community maintains first-class integrations for the major orchestrators. On Apache Airflow the apache-airflow-providers-airbyte package gives you an operator that kicks off a connection and blocks until it finishes.

from airflow import DAG
from airflow.providers.airbyte.operators.airbyte import (
    AirbyteTriggerSyncOperator,
)
from airflow.providers.airbyte.sensors.airbyte import AirbyteJobSensor

with DAG("airbyte_then_dbt", schedule="@hourly", catchup=False) as dag:
    sync = AirbyteTriggerSyncOperator(
        task_id="airbyte_sync_orders",
        airbyte_conn_id="airbyte_default",
        connection_id="{{ var.value.orders_connection_id }}",
        asynchronous=True,  # return the job id immediately
    )

    wait = AirbyteJobSensor(
        task_id="wait_for_sync",
        airbyte_conn_id="airbyte_default",
        airbyte_job_id=sync.output,
    )

    # dbt run follows in a BashOperator / dbt-provider task
    sync >> wait

Because the sensor blocks on the real job status, dbt only runs against complete, fresh data. That ordering guarantee is the whole point — it is what turns two independent tools into one dependable pipeline.

PyAirbyte: Airbyte as a Python Library

Not every use case wants a whole platform. PyAirbyte packages the same connector catalog as an ordinary Python library — pip install airbyte — so you can pull data inside a notebook, a script, or an AI pipeline with no server to run. It reads records into a local cache (DuckDB by default) that you can then hand to pandas.

import airbyte as ab

source = ab.get_source(
    "source-faker",
    config={"count": 5_000},
    install_if_missing=True,
)
source.check()

# Pick only the streams you need
source.select_streams(["products", "users", "purchases"])

# Read into the default DuckDB cache
cache = ab.get_default_cache()
result = source.read(cache=cache)

# Each stream is available as a pandas DataFrame
users_df = result["users"].to_pandas()
print(users_df.head())

The cache is swappable — PyAirbyte supports DuckDB, MotherDuck, Postgres, Snowflake, and BigQuery backends, so the same script can prototype locally against DuckDB and load a warehouse in production by changing one object. It is the lightest possible way to reuse Airbyte connectors without operating Airbyte.

Building a Custom Connector

The catalog is large, but eventually you will hit an internal or niche API that nobody has connected yet. Airbyte gives you three tiers of tooling, and you should always start at the top and only descend when you must.

• Connector Builder. A UI, powered by the low-code CDK, for REST/GraphQL APIs that return JSON. No local dev environment; you configure pagination, auth, and schemas in the browser and test against the live API.
• Low-Code CDK. The same declarative engine as a YAML manifest you keep in version control, with an escape hatch for custom Python components when an endpoint is unusual.
• Python CDK. Full programmatic control via the airbyte-cdk package — the right choice for complex auth, odd protocols, or destination connectors.

The Connector Builder handles a surprising share of real APIs; reach for the Python CDK only when a source genuinely exceeds declarative expression.

# The declarative YAML CDK is also available as a Python package.
pip install airbyte-cdk

# A minimal low-code source manifest (excerpt)
# manifest.yaml
version: "6.0.0"
definitions:
  base_requester:
    type: HttpRequester
    url_base: "https://api.internal.example.com/v1"
    authenticator:
      type: BearerAuthenticator
      api_token: "{{ config['api_key'] }}"
streams:
  - type: DeclarativeStream
    name: invoices
    retriever:
      type: SimpleRetriever
      requester:
        $ref: "#/definitions/base_requester"
      record_selector:
        type: RecordSelector
        extractor:
          type: DpathExtractor
          field_path: ["data"]

A connector built this way is a normal Airbyte image: you can run it locally, publish it to your workspace, and it participates in sync modes, state, and Destinations V2 exactly like a catalog connector. That uniformity is the payoff of the open protocol.

Production Checklist

• Externalize state. Point logs and state at S3/GCS and the metadata DB at managed PostgreSQL — never in-cluster ephemeral volumes.
• Pin versions. Fix the Helm chart version and read the changelog before every bump; platform, chart, and connectors move independently.
• Prefer incremental + dedup. Reserve full refresh for small tables; use a reliable cursor or CDC for large ones to spare your sources.
• Own your transformations. Run dbt downstream against the final tables — Airbyte no longer transforms for you.
• Sequence with an orchestrator. Trigger syncs and gate dbt on sync success via Airflow, so transforms never see partial data.
• Manage secrets properly. Wire connector credentials into a real secret manager, not plaintext values in values.yaml.
• Start connectors at the top tier. Connector Builder first, low-code YAML next, the Python CDK last.
• Monitor sync health. Alert on failed and slow syncs and on rising per-row _airbyte_meta error counts before they reach dashboards.

Airbyte's value is focus. It moves data from a large catalog of sources into your warehouse reliably, lands it as typed tables, and then gets out of the way so dbt can do the shaping and an orchestrator can enforce the order. Deploy it on Kubernetes with Helm, script the edges with PyAirbyte, and fill catalog gaps with the Connector Builder — and extraction stops being the part of the pipeline you dread.