PyAirbyte: Running Airbyte Connectors as a Python Library

pyairbyte is the open-source Python library that takes Airbyte's catalog of 600+ source connectors — the same connectors that power the Airbyte platform's REST APIs, databases, and SaaS integrations — and lets you run any one of them inside your own Python process. There is no server to deploy, no web UI to click through, no connection to configure in a control plane. You pip install airbyte, call ab.get_source(...), pick the streams you want, and source.read(...) lands the records in a local cache you can immediately turn into a pandas DataFrame.

That is a genuinely different shape from the two ways data engineers reached Airbyte connectors before it: the full Airbyte platform (a server plus scheduler plus UI plus destinations that you stand up and operate), or a hand-rolled requests script that re-implements pagination and auth for one API and rots the moment that API changes. This guide walks through the four ideas an interviewer will actually probe — the source / stream / read object model, the default DuckDB cache and how you read data back out, incremental sync with state persisted in the cache, and where the library fits versus the managed platform — and pairs each with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse the load-shape decisions on the data-transformation practice set →, and design end-to-end flows on the pipelines practice set →.

---

On this page

• Why PyAirbyte changes how you use Airbyte in 2026
• Sources, streams & the read pipeline
• Caches — the DuckDB default and reading data out
• Incremental sync with state in the cache
• PyAirbyte vs the Airbyte platform — reading into a warehouse
• Cheat sheet — PyAirbyte recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why PyAirbyte changes how you use Airbyte in 2026

PyAirbyte is a library that runs connectors in your process, not a platform you operate — that one fact decides where it fits

The one-sentence invariant: PyAirbyte runs Airbyte source connectors inside your own Python process, so ingestion becomes a dependency you import rather than a service you deploy. Everything that makes PyAirbyte attractive to a data engineering team follows from that. There is no Airbyte server to run, no scheduler, no web UI, no managed destination cluster; a PyAirbyte read is just code that runs anywhere Python runs — a notebook, an Airflow task, a GitHub Action, an AWS Lambda, your laptop.

The EL split — what PyAirbyte does and deliberately does not do.

• Extract. PyAirbyte reuses the Airbyte connector catalog — REST APIs, SQL databases, files, and hundreds of SaaS sources — so you get the same battle-tested extraction logic (pagination, auth, rate limiting) without writing it. ab.get_source("source-faker") installs and runs the connector for you.
• Load. PyAirbyte writes the extracted records into a cache — a local DuckDB file by default, or a SQL cache like Snowflake, BigQuery, or Postgres — and manages the table schemas and sync state for you.
• Transform is out of scope on purpose. PyAirbyte is the EL in ELT. Business logic — joins, dimensional models, metrics — belongs downstream in dbt or SQL, against the clean tables the cache holds. Keeping transform out is why the library stays small.

Where PyAirbyte sits against the alternatives.

• vs a hand-rolled script. A requests loop that re-implements one API's pagination and auth has no shared connector maintenance, no schema handling, and no sync state. When the API changes, you fix it. PyAirbyte inherits the community-maintained connector and its state machine for free.
• vs the Airbyte platform. The platform is the right tool when you need scheduled syncs, a UI for non-engineers, managed destinations, alerting, and centralized monitoring. PyAirbyte wins when you want ingestion embedded in a Python program, when you are prototyping in a notebook, or when you cannot stand up and operate a server.
• vs raw connector Docker images. You can run an Airbyte connector Docker image yourself and parse its AirbyteMessage protocol by hand. PyAirbyte is that, wrapped: it manages the connector install, feeds config, reads the record stream, and lands it in a cache — so you never touch the protocol.

What interviewers listen for.

• Do you say "PyAirbyte is a library, not the platform" in the first sentence? — senior signal.
• Do you place it as "the same connectors, run in-process, landing in a cache" unprompted? — required framing.
• Do you reach for PyAirbyte when the answer is "I want Airbyte's connector without running Airbyte" rather than as a full replacement for the platform's scheduling and monitoring? — senior signal.
• Do you mention the DuckDB default cache and incremental state as built-ins, not things you write yourself? — the whole point.

Worked example — five lines that pull a real connector into pandas

Detailed explanation. The canonical PyAirbyte "hello world" reads the built-in source-faker connector (which generates deterministic fake users, products, and purchases) into the default DuckDB cache, then hands you a pandas DataFrame. It looks trivial, and that is the point: the same five lines that read the faker source scale unchanged to a Stripe, Postgres, or Salesforce connector, because PyAirbyte only ever gives you a source object, a cache, and a ReadResult.

Question. Read the users stream from source-faker and show the DataFrame you get, without configuring any server.

Input. The source-faker connector configured to generate a small count of fake records.

Code.

import airbyte as ab

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

result = source.read()                 # lands in the default DuckDB cache
users_df = result["users"].to_pandas()
print(users_df.head())

Step-by-step explanation. ab.get_source("source-faker", ...) installs the connector into an isolated virtualenv (because of install_if_missing=True) and applies the config; it does not read anything yet. select_all_streams() marks every stream the connector exposes for syncing. source.read() runs the connector, streams its records, and writes them into the default DuckDB cache (a local file under .cache/). result["users"] is the cached users table, and .to_pandas() materializes it as a DataFrame — no CREATE TABLE, no connection boilerplate, no Airbyte server.

Output.

PyAirbyte produced	value
connector	source-faker installed in an isolated venv
cache	default DuckDB file under .cache/
tables	users, products, purchases
users_df	a pandas DataFrame of the 5 fake users

Rule of thumb. If Airbyte has a connector for your source, PyAirbyte can read it in five lines — the toy faker example and a production Stripe read differ only in the connector name and config, never in the shape of the code.

---

2. Sources, streams & the read pipeline

get_source, select_streams, and read are the entire mental model — learn these three and the rest is config

PyAirbyte has exactly three moves you compose, and an interviewer who asks "walk me through PyAirbyte" wants them in order. Get the vocabulary crisp and the whole library snaps into focus.

The three moves.

• Source — a configured connector. ab.get_source(name, config=..., install_if_missing=True) installs an Airbyte connector and applies its config. The returned Source object is your handle; source.check() validates the config against the live system before you read.
• Streams — the tables the connector exposes. source.get_available_streams() lists them; source.select_streams([...]) (or select_all_streams()) chooses which ones to sync. A stream maps one-to-one to a table in the cache.
• Read — run the connector into a cache. source.read(cache=...) executes the connector, streams the records, and lands each selected stream as a table. It returns a ReadResult you index by stream name.

The stages every read() executes.

• Spawn. PyAirbyte launches the connector process (a Python entry point in its own venv, or a Docker image) and hands it the config.
• Stream. The connector emits Airbyte RECORD and STATE messages; PyAirbyte consumes them, batching records to the cache and tracking state as it goes.
• Land. PyAirbyte writes each stream's records into its cache table, creating or updating the schema, and records the sync state so the next read can be incremental.

Why selecting streams matters.

• A large SaaS connector can expose dozens of streams; reading all of them when you need two is slow and wasteful. select_streams(["users", "purchases"]) reads only those.
• Each stream is independent — one can be full-refresh and another incremental — and only the selected streams land in the cache.
• You can inspect a stream's records lazily without a cache using source.get_records("users"), which returns an iterator straight from the connector.

Worked example — configure, check, select, read

Detailed explanation. Real reads start by configuring a connector, validating it, and picking streams. Here a source-faker source is configured, checked, narrowed to two streams, and read into the default cache — the exact sequence you repeat for any connector.

Question. Configure source-faker, verify the config with check(), select only users and purchases, and read them.

Input. A connector config and the list of streams the connector reports.

Code.

import airbyte as ab

source = ab.get_source("source-faker", config={"count": 1000})

source.check()                          # validates config, raises on failure
print(source.get_available_streams())   # ['users', 'products', 'purchases']

source.select_streams(["users", "purchases"])   # skip 'products'
result = source.read()

print(result.processed_records)         # total records landed in the cache

Step-by-step explanation. get_source(...) installs and configures the connector. source.check() runs the connector's check operation against the config and raises if it is invalid — the fail-fast step before any read. get_available_streams() returns the three streams the faker exposes; select_streams(["users", "purchases"]) marks two of them, so products is never fetched. source.read() runs the connector and lands only the selected streams; result.processed_records reports how many records were written.

Output.

step	call	effect
1	get_source(...)	connector installed + configured
2	check()	config validated (no exception)
3	select_streams([...])	users, purchases selected; products skipped
4	read()	two tables landed in the cache

Rule of thumb. One get_source = one connector; one selected stream = one cache table. Configure and check() first, then select only the streams you actually need.

PyAirbyte interview question on the source/stream model

Question. An interviewer gives you a large SaaS connector that exposes 40 streams, but your job only needs customers and invoices. You must keep the read cheap and confirm the streams exist before syncing. Show the code.

Solution Using select_streams after inspecting available streams

Code.

import airbyte as ab

source = ab.get_source(
    "source-bigcommerce",               # any multi-stream connector
    config={"...": "..."},
    install_if_missing=True,
)
source.check()

available = source.get_available_streams()
wanted = ["customers", "invoices"]
missing = [s for s in wanted if s not in available]
if missing:
    raise ValueError(f"connector is missing streams: {missing}")

source.select_streams(wanted)           # only these two are synced
result = source.read()

Step-by-step trace.

step	streams considered	action
1	all 40 reported by get_available_streams()	listed
2	customers, invoices checked against the list	both present, no error
3	select_streams(["customers", "invoices"])	2 of 40 marked
4	read()	only 2 streams fetched and landed

1. check() validates the config first, so an auth or connectivity problem fails before any stream is read.
2. get_available_streams() returns the connector's full catalog; comparing your wanted list against it turns a typo into a loud error instead of a silently empty table.
3. select_streams(wanted) limits the sync to two streams, so the other 38 are never requested from the API.
4. read() runs the connector once and lands exactly the two selected tables in the cache.

Output:

cache table	rows	streams skipped
customers	synced	38 other streams untouched
invoices	synced	—

Why this works — concept by concept:

• Catalog inspection — get_available_streams() exposes the connector's declared streams, so you validate names before syncing instead of discovering a typo as an empty table.
• Stream selection — select_streams scopes the read; the connector only requests the two streams, cutting API calls, time, and cache size.
• check before read — running check() first turns a bad config into an immediate, clear failure rather than a half-finished sync.
• One source, many streams — a single connector process yields many independent streams, and you pay only for the ones you select.
• Cost — read time is O(records in selected streams), independent of how many streams the connector could expose.

ETL
Topic — etl
ETL extract-and-load pipeline problems

Practice →

Pipelines Topic — pipelines Pipeline-design and connector-selection problems

Practice →

---

3. Caches — the DuckDB default and reading data out

PyAirbyte lands data in a cache — DuckDB by default — that you read as pandas, Arrow, or SQL

The thing that makes PyAirbyte feel like a library rather than a pipeline runner is the cache: source.read() does not stream records at you, it lands them in a queryable store and hands you a ReadResult over it. By default that store is a local DuckDB file, so you get a real analytical database with zero setup, and the cache persists between runs so a read is resumable and re-queryable.

The default cache.

• DuckDB, local, free. ab.get_default_cache() returns a DuckDBCache backed by a file under .cache/. If you call source.read() with no cache argument, PyAirbyte uses this default — no credentials, no server.
• One table per stream. Each selected stream becomes a table in the cache, named after the stream, with the connector's declared schema plus Airbyte system columns (_airbyte_raw_id, _airbyte_extracted_at, _airbyte_meta).
• Persistent. The cache is a file on disk, so a second run reuses it — appending incrementally rather than re-reading everything (covered in section 4).

Reading data back out.

• result["users"].to_pandas() materializes a stream as a pandas DataFrame — the most common move for analysis and notebooks.
• result["users"].to_arrow() returns an Arrow table for zero-copy hand-off to Polars, DuckDB, or a columnar sink; iterating for record in result["users"]: streams dict records without loading everything into memory.
• cache.get_sql_engine() hands you a SQLAlchemy engine over the cache, so you can run arbitrary SQL — joins across streams, aggregations — directly against the landed tables.

Working with the cache directly.

• The cache is reusable across sources: pass the same cache to several source.read(cache=cache) calls and all their streams live in one DuckDB file you can join.
• result.cache gives you back the cache object, and list(cache.streams) (or result.streams) enumerates the landed tables.
• Because the store is DuckDB, len(result["users"]) and SQL COUNT(*) agree — the DataFrame is just one view over a real table.

Worked example — read once, then pandas and SQL over the same cache

Detailed explanation. The everyday pattern is to read once into a cache and then pull results out two ways: a DataFrame for quick inspection and a SQL query for a cross-stream aggregation. Both hit the same DuckDB tables, so there is no second sync.

Question. Read source-faker, get the purchases stream as a DataFrame, then run a SQL query that counts purchases per user over the cache.

Input. The faker users and purchases streams already selected.

Code.

import airbyte as ab
from sqlalchemy import text

source = ab.get_source("source-faker", config={"count": 1000})
source.select_all_streams()

cache = ab.get_default_cache()          # local DuckDB
result = source.read(cache=cache)

purchases_df = result["purchases"].to_pandas()   # pandas view

engine = cache.get_sql_engine()         # SQL over the same cache
with engine.connect() as conn:
    rows = conn.execute(text(
        "SELECT user_id, COUNT(*) AS n "
        "FROM purchases GROUP BY user_id ORDER BY n DESC LIMIT 3"
    )).fetchall()
print(rows)

Step-by-step explanation. source.read(cache=cache) lands users, products, and purchases as DuckDB tables. result["purchases"].to_pandas() materializes one table as a DataFrame without re-reading the connector. cache.get_sql_engine() returns a SQLAlchemy engine bound to the same DuckDB file, so the GROUP BY runs against the landed purchases table — no export, no second sync. The DataFrame and the SQL both read the identical cached rows.

Output.

read-out path	result
to_pandas()	pandas DataFrame of all purchases rows
SQL GROUP BY user_id	top-3 (user_id, n) tuples by purchase count
syncs performed	1 (both views share the cache)

Rule of thumb. Read once into a cache, then read out many ways — to_pandas() for analysis, get_sql_engine() for SQL — because the cache is a real database, not a throwaway buffer.

PyAirbyte interview question on querying the cache

Question. You have landed users and purchases in the default cache. The interviewer wants total spend per user without leaving Python and without re-reading the source. How do you do it, and why is a second read() unnecessary?

Solution Using the cache SQL engine to join landed streams

Code.

import airbyte as ab
from sqlalchemy import text

source = ab.get_source("source-faker", config={"count": 1000})
source.select_streams(["users", "purchases"])
cache = ab.get_default_cache()
source.read(cache=cache)                 # both streams landed once

engine = cache.get_sql_engine()
with engine.connect() as conn:
    result = conn.execute(text("""
        SELECT u.id AS user_id, SUM(p.price) AS total_spend
        FROM users u
        JOIN purchases p ON p.user_id = u.id
        GROUP BY u.id
        ORDER BY total_spend DESC
    """)).fetchall()

Step-by-step trace.

step	operation	source of data
1	read(cache=cache)	connector run once, 2 tables landed
2	get_sql_engine()	SQLAlchemy engine over the DuckDB cache
3	JOIN + GROUP BY	runs inside DuckDB, no network
4	fetchall()	per-user total spend returned to Python

1. The single read() lands both streams as persistent DuckDB tables, so the data is already local.
2. get_sql_engine() connects to that same cache file, meaning the query sees exactly the rows that were synced.
3. The JOIN and aggregation execute inside DuckDB — a real analytical engine — so a second connector read is never needed.
4. The result is computed entirely in-process; no data leaves Python and the source is untouched.

Output:

query	result	source reads
total spend per user	one row per user_id with total_spend	0 extra

Why this works — concept by concept:

• Cache as a database — the cache is DuckDB, not a buffer, so full SQL (joins, aggregates, windows) runs directly over the landed streams.
• Read once, query many — one read() populates persistent tables; every subsequent query reuses them with no additional API calls.
• SQLAlchemy engine — get_sql_engine() exposes a standard engine, so existing SQL tooling and pandas read_sql work unchanged.
• Streams as tables — because each stream is a table, cross-stream joins are ordinary SQL, not custom Python glue.
• Cost — the join is O(rows) inside DuckDB against local storage, far cheaper than re-fetching from the source API.

ETL
Topic — etl
Load-into-a-store and read-back problems

Practice →

Transform Topic — data-transformation Join-and-aggregate over landed tables problems

Practice →

---

4. Incremental sync with state in the cache

PyAirbyte remembers where each stream stopped — sync mode plus cached state is what makes the second read cheap

A full re-read every night is fine for a tiny lookup table and ruinous for a stream with hundreds of millions of records. Incremental sync means each read pulls only records newer than the last read, and the thing that makes it reliable in PyAirbyte is that the "last read" marker lives in the cache alongside the data, keyed by source and stream, so it survives process restarts.

The mechanism.

• Sync mode is the connector's. Each stream declares whether it supports incremental and which cursor field it uses (often updated_at or an increasing id). PyAirbyte uses the connector's incremental mode when the stream and cache support it.
• State lives in the cache. After a successful read, PyAirbyte persists the connector's STATE messages into the cache, keyed by the (source, stream) pair. The next read restores that state and hands it back to the connector.
• The connector filters. On the next read, the connector uses the restored state to request only records past its cursor, so only fresh rows flow — the cheap path.
• Records are appended. Incremental streams append new records to the existing cache table rather than rebuilding it, so history accumulates across reads.

The knobs that matter.

• force_full_refresh=True. source.read(cache=cache, force_full_refresh=True) ignores stored state and re-reads everything — the escape hatch when you suspect the cache or state drifted.
• A persistent cache is required. Incremental only helps if the cache (and therefore its state) persists between runs; an ephemeral cache means every read is a full read.
• Cursor is chosen by the connector. You do not pick the cursor field — the connector does. Your job is to give it a durable cache so its state is remembered.

Failure modes interviewers probe.

• Deletes are invisible. A cursor-based incremental stream sees inserts and updates, never hard deletes — a deleted source row simply stops appearing. If you need deletes, you need a CDC connector (log-based), not a cursor.
• Wiping the cache resets state. Delete the .cache/ file and the next read is a full refresh, because the state lived there. Treat the cache as durable if you rely on incremental.

Worked example — two reads, the second one cheap

Detailed explanation. The everyday incremental pattern is to read into a persistent cache, then read again later against the same cache. If the stream supports incremental sync, the second read only fetches records that changed since the first, because the cursor state was saved.

Question. Read an incremental stream twice into the same persistent cache and show that the second read fetches only new records.

Input. A source stream that supports incremental sync on updated_at, read at two points in time.

Code.

import airbyte as ab

source = ab.get_source("source-my-db", config={"...": "..."})
source.select_streams(["orders"])       # a stream that supports incremental

cache = ab.get_default_cache()          # persistent DuckDB file

r1 = source.read(cache=cache)           # run 1 — full read, state saved into the cache
print("run 1:", r1.processed_records)

## ...time passes, new orders arrive at the source...

r2 = source.read(cache=cache)           # run 2 — incremental, only new records past the cursor
print("run 2:", r2.processed_records)

Step-by-step explanation. Run 1 has no stored state, so the connector does a full read of orders and PyAirbyte saves the resulting cursor state into the cache. Between runs, new orders arrive. Run 2 reuses the same cache, so PyAirbyte restores the saved state and the connector requests only records with updated_at past the cursor. Those new records are appended to the existing orders table, and the cursor advances.

Output.

run	stored state at start	records fetched	table after run
1	none	all orders (e.g. 10,000)	10,000 rows
2	cursor from run 1	only new (e.g. 120)	10,120 rows

Rule of thumb. Incremental is only as durable as your cache — point both reads at the same persistent cache, and let the connector own the cursor.

PyAirbyte interview question on incremental correctness

Question. A teammate reports that every nightly PyAirbyte job re-reads the entire source, even for streams that support incremental. Nothing is wrong with the connector. What is almost certainly misconfigured, and how do you make the second read cheap?

Solution Using a persistent cache so state survives between runs

Code.

import airbyte as ab

## WRONG: a fresh temporary cache each run -> state is lost -> full read every time
## cache = ab.new_local_cache()         # ephemeral / per-run

## RIGHT: a named, persistent cache reused across runs
cache = ab.caches.DuckDBCache(db_path="./.cache/prod.duckdb")

source = ab.get_source("source-my-db", config={"...": "..."})
source.select_streams(["orders"])

result = source.read(cache=cache)        # run 2+ restores state from prod.duckdb
print(result.processed_records)
## to intentionally rebuild:
## source.read(cache=cache, force_full_refresh=True)

Step-by-step trace.

scenario	cache	state at run 2	records read on run 2
ephemeral cache	new each run	none (lost)	ALL (full re-read)
persistent cache	same file reused	restored cursor	only new
persistent + force_full_refresh	same file	ignored on purpose	ALL (intentional)

1. Incremental sync depends on state, and PyAirbyte stores state in the cache — so a new cache every run throws the cursor away and forces a full read.
2. Pointing every run at the same persistent cache (a fixed db_path) lets PyAirbyte restore the cursor and request only new records.
3. force_full_refresh=True is the deliberate override for a rebuild; leaving it off is what keeps nightly runs cheap.
4. The fix is configuration, not connector code: reuse one durable cache and the second read becomes incremental automatically.

Output:

setup	run-2 cost	correctness
persistent cache	O(new records)	incremental, idempotent
ephemeral cache	O(all records)	correct but wasteful

Why this works — concept by concept:

• State in the cache — PyAirbyte persists the connector's cursor next to the data, so incrementality is a property of a durable cache, not of the process.
• Persistent vs ephemeral — a fixed db_path is reused across runs; an ephemeral cache silently disables incremental by discarding state.
• Connector-owned cursor — the connector decides the cursor field and filters on it; you only guarantee its state is remembered.
• Idempotent re-reads — with saved state, running again with no new source data reads nothing new, which is what makes scheduled jobs safe to retry.
• Cost — the cheap second read is O(new records); the ephemeral mistake makes it O(all records) every single night.

ETL
Topic — etl
Incremental extract-load problems

Practice →

Pipelines Topic — pipelines Resumable, state-aware pipeline problems

Practice →

---

5. PyAirbyte vs the Airbyte platform — reading into a warehouse

The library embeds; the platform operates — and a SQL cache reads a connector straight into a warehouse

The most common interview trap is treating PyAirbyte as a drop-in for the Airbyte platform. It is not — they overlap on connectors and diverge on everything operational. Say it in one breath: the platform is a service you run for scheduled, monitored, UI-driven syncs; PyAirbyte is a library you embed for in-process reads that land in a cache or warehouse you choose.

What the platform gives you that the library does not.

• Scheduling & orchestration. The platform runs syncs on a cron with retries and backfills; PyAirbyte runs when your Python code runs — you bring your own scheduler (Airflow, cron, a Lambda trigger).
• A UI and connection management. The platform lets non-engineers configure sources, destinations, and mappings in a web console; PyAirbyte is code-only.
• Managed destinations & monitoring. The platform ships full destination connectors, normalization, and dashboards; PyAirbyte lands in a cache and leaves alerting to you.

What the library gives you that the platform does not.

• Embeddability. Ingestion lives inside your Python program — a notebook cell, a data app, a test — with no server to stand up.
• Zero-ops for small jobs. For a prototype or a one-off, pip install airbyte beats deploying and operating a platform.
• A choice of cache/warehouse in one line. Swap the default DuckDB cache for a SnowflakeCache or BigQueryCache and the same connector lands straight in your warehouse — no Airbyte server, no destination connector to run.

Reading into a warehouse.

• SQL caches. airbyte.caches ships SnowflakeCache, BigQueryCache, PostgresCache, and MotherDuckCache. Passing one as the cache argument makes it the landing store.
• Same read, different landing. source.read(cache=snowflake_cache) runs the identical connector but writes tables into Snowflake instead of a local DuckDB file — incremental state included.
• When to still choose the platform. If you need centralized scheduling, a UI for analysts, and managed monitoring across many connections, the platform is the right tool — PyAirbyte is for embedding EL in code.

Worked example — land a connector straight into Snowflake

Detailed explanation. The clearest way to see the library-vs-platform split is to keep the read identical and only swap the cache. Here the same source-faker read lands in Snowflake instead of DuckDB, using a SnowflakeCache — no Airbyte server, no destination connector.

Question. Read source-faker into Snowflake from a plain Python job, without deploying the Airbyte platform.

Input. Snowflake account credentials and the faker connector config.

Code.

import airbyte as ab
from airbyte.caches import SnowflakeCache

cache = SnowflakeCache(
    account="myorg-myacct",
    username="loader",
    password="...",
    database="ANALYTICS",
    warehouse="LOAD_WH",
    role="LOADER",
    schema_name="RAW",
)

source = ab.get_source("source-faker", config={"count": 1000})
source.select_all_streams()

result = source.read(cache=cache)        # lands tables in Snowflake RAW schema
print(result.processed_records)

Step-by-step explanation. SnowflakeCache(...) describes the warehouse landing target — account, database, warehouse, role, and schema. get_source(...) and select_all_streams() are unchanged from the DuckDB example; the connector does not know or care where its output lands. source.read(cache=cache) runs the same connector but writes users, products, and purchases as tables in the Snowflake RAW schema, with sync state stored there too — all from a plain Python process with no Airbyte server involved.

Output.

aspect	DuckDB default	SnowflakeCache
landing store	local .cache/ file	Snowflake RAW schema
code changed	—	only the cache object
server required	none	none

Rule of thumb. The connector and read code are constant; the cache is the one knob that decides where data lands — swap DuckDB for Snowflake and the same job becomes a warehouse loader.

PyAirbyte interview question on choosing library vs platform

Question. Your team needs nightly syncs of five SaaS sources into Snowflake, with a web UI for analysts to add new connections, retries, and centralized alerting. A colleague proposes replacing your Airbyte platform with a PyAirbyte script. Do you agree, and where would PyAirbyte fit instead?

Solution Using the platform for operations and PyAirbyte for embedded reads

Code.

## PyAirbyte is the right tool for an EMBEDDED, code-driven read — e.g. a
## notebook exploration or a single Python job that lands one source in Snowflake:
import airbyte as ab
from airbyte.caches import SnowflakeCache

cache = SnowflakeCache(account="...", username="...", password="...",
                       database="ANALYTICS", warehouse="LOAD_WH",
                       role="LOADER", schema_name="RAW")
source = ab.get_source("source-stripe", config={"...": "..."})
source.select_streams(["charges", "customers"])
source.read(cache=cache)

## It is NOT a replacement for the platform's scheduler, UI, and monitoring:
## those operational needs (5 sources, analyst self-serve, alerting) stay on
## the Airbyte platform. Use each where it is strong.

Step-by-step trace.

requirement	best fit	why
web UI for analysts	Airbyte platform	PyAirbyte is code-only
centralized scheduling + retries	Airbyte platform	library has no scheduler
alerting / monitoring dashboards	Airbyte platform	library leaves ops to you
embed one read in a Python app	PyAirbyte	in-process, no server
notebook exploration of a source	PyAirbyte	five lines, DuckDB cache

1. The stated needs — a UI, scheduling, retries, alerting across five connections — are exactly the operational features the platform provides and the library does not.
2. Replacing the platform with a script would force you to re-build scheduling and monitoring by hand, trading a managed service for undifferentiated glue.
3. PyAirbyte fits the other jobs: an embedded read inside a Python service, a notebook, or a single warehouse-load job where standing up the platform is overkill.
4. The senior answer is "keep the platform for operations, add PyAirbyte for embedded reads" — not "replace one with the other."

Output:

decision	outcome
keep platform for the 5 scheduled syncs	UI, retries, alerting retained
use PyAirbyte for embedded / ad-hoc reads	zero-ops, in-process, warehouse-ready

Why this works — concept by concept:

• Same connectors, different runtime — both use the Airbyte catalog, so the choice is operational, not about connector coverage.
• Operate vs embed — the platform is a service to operate (scheduler, UI, monitoring); the library is code to embed — matching the tool to the requirement is the whole skill.
• SQL cache as landing — a SnowflakeCache lets PyAirbyte load a warehouse directly, covering the embedded case without any server.
• Right tool per job — scheduled multi-source syncs with analyst self-serve stay on the platform; ad-hoc and embedded reads move to the library.
• Cost — replacing the platform would add O(rebuild scheduling + monitoring) engineering cost for no gain; splitting by strength costs nothing extra.

ETL
Topic — etl
Load-into-warehouse EL problems

Practice →

Transform Topic — data-transformation Warehouse-landing and modelling problems

Practice →

---

Cheat sheet — PyAirbyte recipes

Minimal read to DuckDB.

import airbyte as ab
source = ab.get_source("source-faker", config={"count": 100})
source.select_all_streams()
result = source.read()
df = result["users"].to_pandas()

Configure + check a source.

source = ab.get_source("source-faker", config={"count": 1000}, install_if_missing=True)
source.check()                          # raises on bad config
print(source.get_available_streams())

Select a subset of streams.

source.select_streams(["users", "purchases"])   # skip everything else
result = source.read()

Read to pandas / Arrow / SQL.

df = result["users"].to_pandas()
tbl = result["users"].to_arrow()
engine = result.cache.get_sql_engine()  # run SQL over the cache

Incremental re-read (persistent cache).

cache = ab.caches.DuckDBCache(db_path="./.cache/prod.duckdb")
source.read(cache=cache)                # 2nd+ run fetches only new records
## source.read(cache=cache, force_full_refresh=True)  # rebuild

Read straight into Snowflake.

from airbyte.caches import SnowflakeCache
cache = SnowflakeCache(account="...", username="...", password="...",
                       database="ANALYTICS", warehouse="LOAD_WH",
                       role="LOADER", schema_name="RAW")
source.read(cache=cache)

Cache picker.

Situation	Cache
Local dev, notebooks, zero setup	default DuckDBCache
Land into a cloud warehouse	SnowflakeCache / BigQueryCache
Load a relational DB	PostgresCache
Serverless DuckDB in the cloud	MotherDuckCache

---

Frequently asked questions

What is PyAirbyte?

PyAirbyte is an open-source Python library that runs Airbyte's source connectors inside your own process. You pip install airbyte, call ab.get_source(...), select streams, and source.read(...) lands the records in a cache — a local DuckDB file by default. It gives you Airbyte's connector catalog as a library import, with no server, scheduler, or UI to operate.

How is PyAirbyte different from the Airbyte platform?

The Airbyte platform is a service you deploy and operate: it schedules syncs, offers a web UI, manages destinations, and provides monitoring. PyAirbyte is a library you embed in Python code; it runs the same connectors but lands data in a cache you choose and leaves scheduling and alerting to you. Use the platform for operated, multi-connection, UI-driven syncs; use PyAirbyte to embed a read in a notebook, script, or app.

Where does PyAirbyte store the data it reads?

In a cache. By default that is a local DuckDB file under .cache/, created by ab.get_default_cache(). Each selected stream becomes a table, and you read it back with result["stream"].to_pandas(), .to_arrow(), or SQL via cache.get_sql_engine(). You can also point PyAirbyte at a SQL cache such as Snowflake, BigQuery, or Postgres.

How does incremental sync work in PyAirbyte?

If a stream supports incremental sync, the connector tracks a cursor (such as updated_at), and PyAirbyte persists that state in the cache, keyed by source and stream. On the next read against the same persistent cache, the connector requests only records past the cursor, appending the new ones. Use a fixed db_path so state survives between runs, and force_full_refresh=True to rebuild deliberately.

Can PyAirbyte load into Snowflake or BigQuery?

Yes. airbyte.caches provides SnowflakeCache, BigQueryCache, PostgresCache, and MotherDuckCache. Construct one with your warehouse credentials and pass it as the cache argument to source.read(cache=...); the same connector then lands its streams as tables in that warehouse, sync state included, with no Airbyte server involved.

Does PyAirbyte need Docker or the Airbyte server?

No Airbyte server is required. PyAirbyte installs Python-based connectors into isolated virtualenvs and runs them in-process, so most connectors need only pip. Connectors published only as Docker images can be run via Docker, but the common case — and the whole point of the library — is running connectors as ordinary Python without deploying anything.

Practice on PipeCode

Pipecode.ai is Leetcode for Data Engineering — every PyAirbyte idea above, from the get_source-select-read model to the DuckDB cache, incremental state, and the Snowflake-cache warehouse load, maps to a hands-on practice room where you build the load against real graded inputs. PipeCode pairs each reading with 450+ DE-focused problems and a real-time scoring engine, so your answer to "how would you make this ingestion cheap and idempotent?" holds up under a senior interviewer's depth probes.

Practice ETL problems now →
Pipeline-design drills →