Claude Code with Airbyte: ELT Pipelines That Survive Schema Drift
Why Airbyte without CLAUDE.md produces pipelines that break on the first schema change
Airbyte is an open-source ELT platform that ships data from operational systems (Stripe, Salesforce, Postgres, Shopify, hundreds more) into analytical warehouses (BigQuery, Snowflake, Redshift, Postgres, S3). The architecture separates sources, destinations, and connections, with stream-level incremental sync, schema discovery, and an extensible CDK for building custom connectors. The community catalogue covers a wide enough surface that most teams never write a custom connector. The catch is that the production reliability of an Airbyte deployment depends on configuration decisions that are not obvious from the dashboard: sync mode per stream, primary key declaration, normalisation choice, scheduling cadence, and CDK manifest patterns for the connectors you do customise.
Claude Code, without explicit instructions, generates Airbyte deployments that work for the first sync. It clicks through the source setup, picks the default sync mode (usually "full refresh, append" or "incremental, append"), accepts the discovered schema, and configures a manual sync. The next time the source API adds a field, the connection silently drops the new field. The next time a stream rotates its primary key (some APIs do this on user request), the incremental sync starts re-pulling old records as new ones. The next time the source connector gets a CDK upgrade, a discovered field changes type from string to object and the destination warehouse errors out on the next load.
This guide covers the CLAUDE.md configuration that locks Claude Code into Airbyte's correct model: sources and destinations configured via the API or Terraform rather than the UI, connections defined as code with sync modes pinned per stream, normalisation handled by a separate dbt layer rather than Airbyte's deprecated normalisation, custom CDK connectors built with the YAML manifest pattern, and schema-change behaviour explicitly declared. If you are building data infrastructure that consumes the warehouse Airbyte loads into, Claude Code with Drizzle covers the relational side. For observability on the syncs themselves, Claude Code with Honeycomb covers the metrics that matter for ELT.
The Airbyte CLAUDE.md template
The CLAUDE.md at your project root is read at the start of every Claude Code session. For an Airbyte integration it needs to declare: the deployment topology (cloud, self-hosted, abctl-managed), the API client used for configuration as code, the source / destination / connection naming conventions, the sync mode policy per stream type, the normalisation decision, the CDK conventions for custom connectors, and the hard rules that prevent the silent data loss Claude generates most often.
# Airbyte ELT rules
## Deployment
- Topology: Airbyte Cloud | abctl on Kubernetes | self-hosted on EC2 (declare yours)
- Workspace ID in .env: AIRBYTE_WORKSPACE_ID
- API access: AIRBYTE_CLIENT_ID + AIRBYTE_CLIENT_SECRET (Cloud) or AIRBYTE_API_KEY (self-hosted)
- Terraform-managed (preferred) or octavia-cli managed for configuration
## Project structure
- terraform/airbyte/sources/ , One file per source (stripe.tf, salesforce.tf)
- terraform/airbyte/destinations/ , One file per destination (snowflake.tf)
- terraform/airbyte/connections/ , One file per connection (stripe-to-snowflake.tf)
- connectors/{name}/ , Custom CDK connectors
- connectors/{name}/manifest.yaml , Low-code connector manifest
- connectors/{name}/source.py , Python source (if low-code is insufficient)
- dbt/ , Post-load transformation (Airbyte stops at raw)
## Naming conventions (MANDATORY)
- Sources: "{provider}-{environment}" (e.g. "stripe-prod", "shopify-staging")
- Destinations: "{warehouse}-{database}-{environment}" (e.g. "snowflake-raw-prod")
- Connections: "{source-name}-to-{destination-name}" (e.g. "stripe-prod-to-snowflake-raw-prod")
- Schema in destination: "raw_{source_short_name}" (e.g. "raw_stripe")
- Stream prefix: NEVER set (it complicates dbt source declarations)
## Sync mode per stream (MANDATORY decisions)
- Append-only streams (events, logs, transactions): incremental | append
- Mutable streams (customers, products, orders): incremental | append + dedup
- Requires primary key declared on the stream
- Cursor field MUST be set (typically updated_at or last_modified)
- Small reference streams (countries, currencies, plan tiers): full refresh | overwrite
- NEVER use full refresh | append for any stream over 10k rows (storage explodes)
## Schema change behaviour
- propagateSchemaChanges: propagate_columns (NOT propagate_fully, NOT ignore)
- propagate_columns: new columns added automatically, type changes rejected
- ignore: silent data loss, NEVER use
- propagate_fully: type changes auto-applied, breaks downstream dbt models
## Normalisation
- Airbyte normalisation: DISABLED (deprecated, will be removed)
- All transformations live in dbt, in a separate repo or separate folder
- Airbyte loads to raw_{source} schema, dbt creates analytics_{domain} schema
## Scheduling
- Cron expressions in UTC
- Append-only event streams: every 15 minutes (*/15 * * * *)
- Mutable streams: hourly (0 * * * *)
- Reference streams: daily (0 6 * * *)
- NEVER use "manual" mode in production connections
## CDK custom connectors
- Prefer the low-code YAML manifest over Python source
- Manifest streams MUST declare: primary_key, cursor_field (if incremental), pagination
- NEVER hardcode timestamps or paging cursors in the manifest
## Hard rules
- NEVER configure sources or connections through the UI in production
- NEVER use full refresh | append on a stream over 10k rows
- NEVER omit primary_key on an incremental | append + dedup stream
- NEVER enable Airbyte normalisation, use dbt
- NEVER set propagateSchemaChanges to ignore
- NEVER commit AIRBYTE_API_KEY to source (.env or terraform.tfvars only)
- ALWAYS pin source and destination versions in Terraform (no "latest")
Three rules here prevent the majority of production failures Claude generates without them.
The sync mode per stream rule is the most impactful and the most often missed. Airbyte's default sync mode is "full refresh, append" which appends a full copy of the source data on every sync. For a customer table with 100k rows synced hourly, that produces 2.4 million rows per day of redundant data in the warehouse, none of it useful, all of it billed. The correct mode depends on the stream's semantics: events are append-only, customers are mutable, countries are reference data. CLAUDE.md captures the decision per stream type so Claude generates the right configuration by default.
The normalisation rule prevents a coming-deprecation footgun. Airbyte's built-in normalisation transforms raw JSON into typed tables but has been deprecated in favour of dbt. New deployments should not enable it. Claude defaults to "Normalised tabular data" in the UI because it sounds correct. The CLAUDE.md rule disables it and routes all transformation through dbt where the lineage is version-controlled and testable.
The schema-change behaviour rule controls what happens when the source API adds, removes, or renames a field. "Ignore" silently drops the change and loses data. "Propagate fully" applies type changes automatically and breaks downstream dbt models that expected the old type. "Propagate columns" is the goldilocks setting: new columns appear in the warehouse, type changes pause the connection until a human reviews. Claude defaults to whatever the UI suggests, which has changed across Airbyte versions.
Install and API client setup
For Airbyte Cloud, configure the API client via OAuth2 client credentials. For self-hosted, use an API key.
# .env (Cloud)
AIRBYTE_WORKSPACE_ID=your-workspace-uuid
AIRBYTE_CLIENT_ID=your-client-id
AIRBYTE_CLIENT_SECRET=your-client-secret
# .env (Self-hosted)
AIRBYTE_API_URL=https://airbyte.your-domain.com/api/public/v1
AIRBYTE_API_KEY=your-api-key
For Terraform-managed configuration, install the Airbyte provider:
# terraform/airbyte/versions.tf
terraform {
required_providers {
airbyte = {
source = "airbytehq/airbyte"
version = "~> 0.7.0"
}
}
}
provider "airbyte" {
client_id = var.airbyte_client_id
client_secret = var.airbyte_client_secret
server_url = "https://api.airbyte.com/v1"
}
Add the Terraform pattern to CLAUDE.md:
## Configuration-as-code (ENFORCE)
- All sources, destinations, and connections are defined in terraform/airbyte/
- Changes go through: terraform plan -> code review -> terraform apply
- NEVER click through the UI to add or edit a production source or connection
- The Airbyte UI is for monitoring and debugging only in production
- terraform.tfvars holds secrets, .gitignored
- Use terraform workspaces to separate environments: staging, prod
Source configuration
A Stripe source for the production environment:
# terraform/airbyte/sources/stripe.tf
resource "airbyte_source_stripe" "stripe_prod" {
name = "stripe-prod"
workspace_id = var.airbyte_workspace_id
configuration = {
account_id = var.stripe_account_id
client_secret = var.stripe_secret_key
# Pin lookback window so re-syncs are bounded
lookback_window_days = 7
# Start date is the earliest data we want
start_date = "2024-01-01T00:00:00Z"
# Streams to enable (Stripe has many, enable selectively)
# Default to common ones, customise per project
num_workers = 3
}
}
A Snowflake destination:
# terraform/airbyte/destinations/snowflake.tf
resource "airbyte_destination_snowflake" "snowflake_raw_prod" {
name = "snowflake-raw-prod"
workspace_id = var.airbyte_workspace_id
configuration = {
host = var.snowflake_host
role = "AIRBYTE_LOADER"
warehouse = "AIRBYTE_WAREHOUSE"
database = "RAW_PROD"
schema = "RAW_STRIPE" # Overridden per connection if multi-source
username = var.snowflake_username
credentials = {
key_pair_authentication = {
private_key = var.snowflake_private_key
private_key_password = var.snowflake_private_key_password
}
}
}
}
Add a source / destination section to CLAUDE.md:
## Sources and destinations
- One Terraform file per resource at terraform/airbyte/{sources|destinations}/
- Resource name matches the source/destination name with underscores
- Credentials NEVER inline, always via var.{name} from terraform.tfvars
- Provider versions pinned (~> 0.7.0 not latest)
- Document any non-default configuration choice in a comment above the line
Connection configuration
The connection ties a source to a destination and declares the sync configuration per stream.
# terraform/airbyte/connections/stripe-to-snowflake.tf
resource "airbyte_connection" "stripe_to_snowflake" {
name = "stripe-prod-to-snowflake-raw-prod"
source_id = airbyte_source_stripe.stripe_prod.source_id
destination_id = airbyte_destination_snowflake.snowflake_raw_prod.destination_id
schedule = {
schedule_type = "cron"
cron_expression = "0 * * * *" # Hourly at minute 0
cron_time_zone = "UTC"
}
data_residency = "auto"
namespace_definition = "destination"
namespace_format = "raw_stripe"
non_breaking_schema_updates_behavior = "propagate_columns"
configurations = {
streams = [
{
name = "customers"
sync_mode = "incremental_append_dedup"
primary_key = [["id"]]
cursor_field = ["created"]
},
{
name = "charges"
sync_mode = "incremental_append_dedup"
primary_key = [["id"]]
cursor_field = ["created"]
},
{
name = "invoices"
sync_mode = "incremental_append_dedup"
primary_key = [["id"]]
cursor_field = ["created"]
},
{
name = "subscriptions"
sync_mode = "incremental_append_dedup"
primary_key = [["id"]]
cursor_field = ["created"]
},
{
name = "events"
sync_mode = "incremental_append" # Events are immutable, append-only
cursor_field = ["created"]
},
{
name = "balance_transactions"
sync_mode = "incremental_append"
cursor_field = ["created"]
},
]
}
}
Add a connection section to CLAUDE.md:
## Connections
- One Terraform file per connection at terraform/airbyte/connections/
- Schedule cron in UTC, document the cadence with a comment
- namespace_definition: "destination" with namespace_format declared explicitly
- non_breaking_schema_updates_behavior: "propagate_columns"
- Every stream explicitly listed (no auto-include of new streams)
- Each stream has: sync_mode, primary_key (if dedup), cursor_field (if incremental)
## Stream catalog discipline
- New streams are added explicitly, not by enabling a category
- Disabled streams are commented out in the Terraform, not removed
- When source API adds a stream we want, add it to the connection in a separate PR
Custom CDK connectors
When the Airbyte connector catalogue does not cover a source, build a custom connector with the CDK. The preferred path is the low-code YAML manifest, which describes the connector declaratively. The fallback is a Python source, which gives full programmability.
A manifest for a hypothetical events API:
# connectors/source-events/manifest.yaml
version: 4.5.0
type: DeclarativeSource
definitions:
base_requester:
type: HttpRequester
url_base: https://api.events.example.com/v1
http_method: GET
authenticator:
type: BearerAuthenticator
api_token: "{{ config['api_token'] }}"
retriever:
type: SimpleRetriever
requester:
$ref: "#/definitions/base_requester"
record_selector:
type: RecordSelector
extractor:
type: DpathExtractor
field_path: ["data"]
paginator:
type: DefaultPaginator
pagination_strategy:
type: CursorPagination
cursor_value: "{{ response.get('next_cursor', '') }}"
stop_condition: "{{ response.get('next_cursor') is none }}"
page_token_option:
type: RequestOption
field_name: "cursor"
inject_into: "request_parameter"
events_stream:
type: DeclarativeStream
name: events
primary_key: ["id"]
retriever:
$ref: "#/definitions/retriever"
requester:
$ref: "#/definitions/base_requester"
path: /events
incremental_sync:
type: DatetimeBasedCursor
cursor_field: occurred_at
datetime_format: "%Y-%m-%dT%H:%M:%S%z"
start_datetime:
datetime: "{{ config['start_date'] }}"
datetime_format: "%Y-%m-%dT%H:%M:%S%z"
step: "P1D"
cursor_granularity: "PT1S"
end_datetime:
type: MinMaxDatetime
datetime: "{{ now_utc().strftime('%Y-%m-%dT%H:%M:%S+00:00') }}"
datetime_format: "%Y-%m-%dT%H:%M:%S%z"
start_time_option:
type: RequestOption
field_name: "from"
inject_into: "request_parameter"
end_time_option:
type: RequestOption
field_name: "to"
inject_into: "request_parameter"
schema_loader:
type: InlineSchemaLoader
schema:
$schema: http://json-schema.org/draft-07/schema#
type: object
properties:
id:
type: string
occurred_at:
type: string
format: date-time
event_type:
type: string
payload:
type: object
streams:
- $ref: "#/definitions/events_stream"
check:
type: CheckStream
stream_names: ["events"]
spec:
type: Spec
documentation_url: https://docs.events.example.com
connection_specification:
$schema: http://json-schema.org/draft-07/schema#
type: object
required:
- api_token
- start_date
properties:
api_token:
type: string
airbyte_secret: true
title: API token
description: Bearer token from the Events API dashboard
order: 0
start_date:
type: string
title: Start date
description: Earliest date to backfill, ISO 8601
examples:
- "2024-01-01T00:00:00Z"
order: 1
The manifest captures incremental sync with a DatetimeBasedCursor, pagination with a cursor in the response, and a primary key on the stream. The schema is inlined for clarity. For larger schemas, externalise to a JSON file in the same directory.
Add a CDK section to CLAUDE.md:
## Custom connector conventions
- Prefer low-code YAML manifest (connectors/{name}/manifest.yaml)
- Fall back to Python source only when manifest is insufficient
- Every stream declares: primary_key, cursor_field (if incremental), schema
- Pagination strategy explicit (CursorPagination, OffsetIncrement, PageIncrement)
- Authenticator explicit (BearerAuthenticator, ApiKeyAuthenticator, OAuth2)
- Spec required + secret fields marked with airbyte_secret: true
- Build and test locally with: airbyte-ci connectors --name=source-events test
## Hard rules for custom connectors
- NEVER hardcode credentials in the manifest
- NEVER hardcode timestamps (use Jinja: {{ now_utc() }}, {{ config['start_date'] }})
- NEVER skip incremental_sync configuration for any append-only stream
- NEVER let pagination run unbounded (always set stop_condition)
- ALWAYS pin CDK version in metadata.yaml
For schema validation patterns inside the manifest, the Airbyte CDK uses JSON Schema draft-07 throughout. The connector also needs a metadata.yaml declaring the connector image, the CDK version, and the support level.
Sync monitoring and alerting
A production Airbyte deployment fires alerts when syncs fail, when they take longer than expected, or when schema changes are detected. The Airbyte API exposes the sync history per connection.
# scripts/check-sync-health.py
import os
import requests
from datetime import datetime, timedelta
WORKSPACE_ID = os.environ["AIRBYTE_WORKSPACE_ID"]
API_KEY = os.environ["AIRBYTE_API_KEY"]
API_URL = os.environ.get("AIRBYTE_API_URL", "https://api.airbyte.com/v1")
def list_connections():
r = requests.get(
f"{API_URL}/connections",
params={"workspaceId": WORKSPACE_ID},
headers={"Authorization": f"Bearer {API_KEY}"},
timeout=30,
)
r.raise_for_status()
return r.json()["data"]
def latest_job(connection_id):
r = requests.get(
f"{API_URL}/jobs",
params={"connectionId": connection_id, "limit": 1},
headers={"Authorization": f"Bearer {API_KEY}"},
timeout=30,
)
r.raise_for_status()
jobs = r.json()["data"]
return jobs[0] if jobs else None
def check():
failed = []
stale = []
threshold = datetime.utcnow() - timedelta(hours=2)
for conn in list_connections():
job = latest_job(conn["connectionId"])
if job is None:
continue
if job["status"] in ("failed", "incomplete"):
failed.append((conn["name"], job["status"]))
continue
last_updated = datetime.fromisoformat(job["lastUpdatedAt"].replace("Z", "+00:00")).replace(tzinfo=None)
if last_updated < threshold:
stale.append((conn["name"], last_updated.isoformat()))
return {"failed": failed, "stale": stale}
if __name__ == "__main__":
result = check()
if result["failed"] or result["stale"]:
print("ALERT")
print(result)
exit(1)
print("OK")
Add a monitoring section to CLAUDE.md:
## Monitoring
- scripts/check-sync-health.py runs every 15 minutes from a scheduler
- Alerts on: any job in failed | incomplete state, any connection with no successful sync in last 2 hours
- Alerts route to oncall on-call (pagerduty) for production connections
- Alerts route to data-eng slack for staging connections
- NEVER suppress an alert by extending the threshold, fix the underlying issue
For deeper sync metrics via tracing, Claude Code with Honeycomb covers the span shape for ELT operations.
Common Claude Code mistakes with Airbyte
Six patterns Claude generates incorrectly without CLAUDE.md constraints, with the correct replacement for each.
1. UI-configured connections
Claude generates: instructions to "click through the Airbyte UI to add the source".
Correct pattern: Terraform files in terraform/airbyte/ reviewed via PR and applied via CI.
2. Default sync mode (full refresh, append) on mutable streams
Claude generates: sync_mode = "full_refresh_append" on a customers stream.
Correct pattern: sync_mode = "incremental_append_dedup" with primary_key and cursor_field declared.
3. Airbyte normalisation enabled
Claude generates: "enable normalisation in the connection settings".
Correct pattern: normalisation disabled, all transformation lives in dbt in a separate codebase.
4. Schema change behaviour set to ignore
Claude generates: non_breaking_schema_updates_behavior = "ignore".
Correct pattern: non_breaking_schema_updates_behavior = "propagate_columns" so new columns flow through and type changes pause the connection.
5. Hardcoded timestamps in custom connectors
Claude generates: start_datetime: "2024-01-01T00:00:00Z" directly in the manifest.
Correct pattern: start_datetime: "{{ config['start_date'] }}" parameterised through the spec.
6. Unbounded pagination
Claude generates: a paginator with no stop_condition.
Correct pattern: stop_condition: "{{ response.get('next_cursor') is none }}" so the sync terminates on the last page.
Add a common mistakes section to CLAUDE.md with these six pairs. Claude benefits from explicit before/after comparisons because it can match the pattern it would otherwise generate to the corrected form.
Permission hooks for ELT scripts
An Airbyte deployment accumulates scripts: connector tests, sync triggers, configuration exporters, log fetchers. Some are read-only. Some trigger production syncs or modify the configuration. Permission hooks gate the destructive ones.
In .claude/settings.local.json:
{
"permissions": {
"allow": [
"Bash(airbyte-ci connectors*test*)",
"Bash(python scripts/check-sync-health.py*)",
"Bash(python scripts/export-connections.py*)",
"Bash(terraform plan*)"
],
"deny": [
"Bash(terraform apply*)",
"Bash(python scripts/reset-state.py*)",
"Bash(python scripts/trigger-full-refresh.py*)"
]
}
}
Connector tests, health checks, and terraform plans are safe operations. Applying terraform to production, resetting sync state, or triggering a full refresh on a large stream would have material cost or data implications. The deny list forces Claude to surface those operations as prompts.
Building ELT pipelines that survive year-two
The Airbyte CLAUDE.md in this guide produces ELT setups where sources, destinations, and connections live in Terraform reviewed via PR, every stream has the correct sync mode for its semantics, schema changes propagate columns and pause on type changes, normalisation lives in dbt where it can be tested, custom CDK connectors follow the low-code YAML manifest pattern with parameterised timestamps and bounded pagination, and sync health is monitored with alerts that page the right team.
The underlying principle is the same as any data-platform integration with Claude Code. Airbyte without a CLAUDE.md produces deployments that work for the first sync and accumulate technical debt with every schema change, every new stream added through the UI, and every connector upgrade that silently shifts a field type. The CLAUDE.md template removes each failure mode by making the configuration-as-code, sync-mode-explicit, schema-change-safe pattern the only pattern Claude can generate.
For the next layer of the data stack, Airbyte pairs with Claude Code with Drizzle for the operational database that often serves as both an Airbyte source and a downstream consumer, and Claudify includes an Airbyte-specific CLAUDE.md template with Terraform patterns, sync mode decisions, CDK manifest conventions, monitoring scripts, and all six common-mistake rules pre-configured.
Get Claudify. Ship Airbyte pipelines that survive schema drift and CDK upgrades.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify
