Orchestrating Data Pipelines with Airflow and Airbyte for AI Workflows

The AI workflows I built at Revfx — lead scoring, the research copilot, hiring-signal detection — all depended on a steady flow of fresh data from a dozen-plus sources into a warehouse (Redshift) that fed both analytics and the RAG pipeline underneath the research copilot. Airbyte handled ingestion, Airflow handled orchestration. Neither tool is exotic, but the way they’re combined and operated is where reliability actually gets won or lost.

Airbyte’s job: normalize the mess of source systems

Sales intelligence data comes from CRMs, enrichment APIs, calendar/email systems, job-posting feeds — each with its own API shape, rate limits, and pagination quirks. Airbyte’s value wasn’t cleverness, it was standardization: every source became a connector with a predictable sync behavior (full refresh or incremental), landing in a consistent raw layer before any transformation happened. The temptation with a new, unusual source is to write a bespoke one-off ingestion script “just this once” — every time I gave in to that temptation, it became the connector nobody remembered how to debug six months later. Routing everything through Airbyte, even sources that needed a custom connector, kept operational knowledge concentrated in one place.

Airflow’s job: make the dependency graph explicit

The real value of Airflow over a pile of cron jobs isn’t scheduling — it’s making pipeline dependencies explicit and visible. “The lead-scoring DAG can’t run until the CRM sync and the enrichment sync have both completed” is a dependency that’s trivial to express in a DAG and easy to silently get wrong with cron (a job that assumes upstream data is ready, based on timing rather than an actual completion signal). Every DAG I built keyed off actual upstream task completion, not wall-clock scheduling assumptions — a sync that runs long doesn’t just quietly get raced by a downstream job that assumes it finished on time.

sync_crm >> sync_enrichment >> transform_leads >> score_leads >> refresh_rag_index

The failure mode that actually mattered: partial data, not missing data

The scary failure mode for a pipeline isn’t a job crashing — that’s loud and gets noticed. The dangerous one is a job succeeding technically while producing partial or stale data, because a downstream consumer (the lead-scoring workflow, the RAG index) then confidently operates on bad input without any error to point to. The fix was building data-quality checks as explicit DAG tasks, not an afterthought — row-count sanity checks against historical baselines, freshness checks against expected sync cadence, and failing the DAG loudly rather than let a “successful” sync with 5% of expected rows propagate downstream silently.

Keeping the RAG index in sync with the warehouse

The research copilot’s RAG index needed to reflect the same underlying company data as the warehouse, without diverging. Rather than a separate, independently-scheduled reindexing job (which drifts out of sync with warehouse updates over time in ways that are hard to notice until someone asks the copilot a question with a stale answer), the reindex step was itself a DAG task, downstream of the same transform step that fed the warehouse. One source of truth for “when did this data last change,” one dependency graph, rather than two systems quietly drifting apart on their own schedules.

Backfills are where pipeline design gets tested for real

Every pipeline handles the steady-state case fine. What separates a pipeline that’s actually maintainable from one that becomes a liability is how it handles backfills — reprocessing three months of historical data after a transformation bug is found, without duplicating records or requiring someone to babysit it by hand. Designing DAG tasks to be idempotent (safe to rerun for the same date range without side effects) from the start, rather than retrofitting idempotency after the first painful manual backfill, is the single highest-leverage data-engineering decision in a pipeline like this — and the one most commonly skipped under initial time pressure.

What I’d prioritize building first

Data-quality checks as first-class DAG tasks, not monitoring bolted on after incidents. Idempotent tasks from day one, even though it’s slower to build initially. And a single dependency graph connecting ingestion through to every downstream consumer (warehouse, RAG index, scoring workflows) rather than parallel pipelines that happen to run on similar schedules — because “happen to run on similar schedules” is exactly the assumption that breaks the first time one job runs long.