How data actually gets into your warehouse: batch, CDC, webhooks — and the DAGs that keep it all honest

Almost nobody asks the question out loud, but it's quietly behind every wrong decision made from a dashboard: how fresh is this data? A report built on numbers that's seven hours old is a different decision than one built on numbers seven minutes old. The answer to “how fresh” isn't decided by the dashboard. It's decided by the pipeline pattern underneath it, and most operators have never been told which pattern they're on.

There are three patterns that do almost all the work of moving data from source systems into a warehouse, and one piece of orchestration that makes the whole thing safe to run. What follows is a walkthrough of each in plain English, when to reach for which, and how the DAGs that tie them together let us move that same pipeline cleanly between dev, staging, and prod.

Pattern 1 — Scheduled batch

The oldest pattern, and still the right answer more often than people assume. On a schedule — every hour, every night — a job extracts a chunk of data from the source, transforms it, and loads it into the warehouse. The unit of work is a time window: “everything that changed since the last run.” Classic ETL and ELT both live here; they differ on where the transform happens, not on the batching.

Strengths. Simple to reason about. Cheap to run. Easy to make idempotent — a failed run is just re-run, and you get the same result. Matches well with the natural cadence of most business reporting, which is daily or hourly, not sub-minute.

Weaknesses. Freshness is capped at the interval. A nightly job means the dashboard on top of it is up to 24 hours stale. Every schedule is a promise about freshness, and the promise is weaker than people tend to assume.

When to reach for it. Data that changes slowly (products, price lists, chart-of-accounts). Analysis that doesn't need to-the-minute accuracy. Source systems that don't support streaming.

Pattern 2 — Change Data Capture (CDC)

Instead of re-querying the source on a schedule, read the source database's transaction log and stream every insert, update, and delete into the warehouse as it happens. The unit of work drops from “a time window” to “a single row change.”

Strengths. Near-real-time freshness — seconds, not hours. Cheap on the source database, because you're reading the log rather than issuing repeated SELECTs. Captures deletes cleanly, which naive batch pulls almost always miss. A row that disappears from the source on a Tuesday will be missing from a weekly batch pull forever unless you go out of your way to detect it; CDC sees it the moment it happens.

Weaknesses. More infrastructure to run. More moving parts to monitor. Ordering, exactly-once semantics, and schema changes are real problems that need real handling. When CDC works it feels effortless; when it breaks it tends to break quietly.

When to reach for it. Databases you control, where freshness actually matters — operational dashboards, fraud and risk, inventory management, anything where the delta between “now” and “an hour ago” is the difference between catching a problem and missing it.

Pattern 3 — Webhooks and event streams

The third pattern inverts the polling relationship entirely. The source system decides what changed and pushes an event to you as it happens — “order placed,” “ticket closed,” “invoice paid.” You write an endpoint that receives these events and lands them in the warehouse.

Strengths. The source does the work of knowing what changed. Near-real-time. Works across services you don't own — most modern SaaS tools publish webhooks or event streams, and that's often the only way to get their data without hammering their API.

Weaknesses. You're on the receiving end of somebody else's retry logic. Duplicate events are common (providers retry on timeout). Out-of-order events are common. Missed events happen. Every webhook pipeline needs deduplication, ordering handling, and a reconciliation mechanism to catch what slipped through — typically a periodic batch pull against the same source to cross-check.

When to reach for it. SaaS sources that publish webhooks (the CRM, the support tool, the ad platform). Product analytics and user-behavior events. Anything event-driven where the source system is the authority on what happened.

How they actually get mixed

Nobody uses just one. A working stack typically looks like this: batch for slow-changing reference data (products, price lists, employee rosters); CDC for the operational databases you own and care about most (orders, customers, payments); webhooks for external SaaS sources you don't own (CRM, ad platforms, support tool, ticketing). The warehouse is the place they all land and get reconciled into tables that are boring to query. The pattern you pick per source is driven by two questions: how fast does this data need to be, and what does the source system actually support?

“The warehouse only gets to be the single source of truth if the pipelines feeding it are boring, predictable, and re-runnable.”

— RevenuePoint design principle

DAGs — the piece that makes it all work

Pipelines in isolation are scripts. A production data stack is many of them, with dependencies between them, and some of them have to wait for others to finish. The moment you have more than three jobs that depend on each other, cron stops being the right tool. What you want is a DAG.

A DAG is a directed acyclic graph — a set of tasks with dependencies that can't loop back on themselves. Each node is a unit of work (“extract orders from the CRM,” “rebuild the daily-revenue table”). Each edge is a dependency (“daily-revenue depends on orders finishing first”). A scheduler walks the graph and runs each task once its dependencies complete.

Why DAGs, not cron

Cron runs a script at 3 AM whether or not the thing it depends on finished. DAGs don't. If the upstream extract fails, every downstream task that depends on it is held, not run on stale data. That alone is the difference between reports that are “usually right” and reports that either work or fail loudly. A partial failure in cron is invisible until a human notices the numbers look wrong. A partial failure in a DAG is a red node with a timestamp and a stack trace.

Idempotency and backfills

Every task has to be safe to re-run. If a task failed at 3 AM and you re-run it at 8 AM, you should get the same result either way. In practice, that means every task takes an explicit “which window am I processing?” input and writes its output in a way that can overwrite the previous version without corrupting anything downstream. This is what makes backfills — re-running a pipeline over the last ninety days because a metric definition changed — a routine operation rather than an emergency.

Observability

The DAG itself is the operations dashboard. Which task failed, which tasks are blocked by it, how long each one took, which downstream tables are now stale — you get all of that by looking at the graph. You don't need a second tool to tell you the state of the first tool.

Promoting the same DAG across environments

The same DAG definition runs in three environments — dev, staging, prod — each pointed at a different warehouse. The DAG file is the artifact that moves. The environment-specific bits (connection strings, schema names, credentials, feature flags) get injected at run time from config, not hardcoded into the graph.

The promotion path works like code. A change is authored in dev and exercised against a dev warehouse, typically loaded with sampled or synthetic data so iteration is fast and nothing production-critical is touched. Once the pipeline passes in dev, the same DAG is promoted to staging, which runs against full-scale data but has no production consumers downstream — dashboards, agents, and alerts in staging are staging copies. Once it passes there, the exact same DAG is promoted to prod, where it runs against the real warehouse and feeds the consumers your operators actually look at.

This is how “it worked in dev” gets to mean something. The pipeline isn't being rewritten between environments — the same code runs, just against a different warehouse. Bugs that are going to show up in prod tend to show up in staging first, on the way through.

A minimal DAG definition that supports this pattern looks roughly like this:

python

from datetime import timedelta

ENV   = get_env()                    # "dev" | "staging" | "prod"
CFG   = load_config(ENV)             # connection strings, schema names, credentials
DEFAULTS = {
    "retries":       3,
    "retry_delay":   timedelta(minutes=5),
    "on_failure":    alert("#data-ops"),
}

dag = DAG(
    dag_id   = "orders_daily",
    schedule = "0 2 * * *",          # nightly at 02:00
    defaults = DEFAULTS,
)

extract   = Task("extract_orders",   source=CFG.crm,        window="{{ ds }}")
transform = Task("transform_orders", warehouse=CFG.warehouse)
load      = Task("load_daily_revenue", warehouse=CFG.warehouse, table=f"{CFG.schema}.daily_revenue")
verify    = Task("verify_row_counts", warehouse=CFG.warehouse, expected_range=(800, 1500))

extract >> transform >> load >> verify

Three things to notice. The DAG takes its environment from a runtime variable; the same file runs in dev, staging, or prod. Every task is window-scoped ({{ ds }}), so re-running yesterday's failed extract yields yesterday's data, not today's. A verify_row_counts task runs last — a light-weight data-quality check, so the pipeline fails loudly if the output is outside the expected range rather than quietly loading a garbage-sized table.

Diagram: source systems feed three pattern lanes (batch, CDC, webhooks) into a central DAG orchestrator, which writes into three stacked warehouses (dev, staging, prod) with a promotion arrow between them — Sources pick a pattern. The DAG orchestrates. The same DAG runs against dev, staging, and prod warehouses, with code promoted across environments the way application code is.

Ad-hoc scripts vs. a real orchestrator

The contrast is easiest to see on a single ordinary question: what happens when the 3 AM extract fails?

Cron jobs and shell scripts

The extract fails at 3:04 AM. The transform runs at 3:30 AM anyway, against stale data, and writes a partial result. The load runs at 4:00 AM and overwrites the previous day's (correct) table with the partial result.

Dashboards are now subtly wrong. Nobody knows. Someone notices three days later because a number in a weekly review looks off. An analyst spends a day figuring out what happened.

A DAG orchestrator

The extract fails at 3:04 AM. The DAG retries twice per the task config, then marks the task red. Transform and load don't run — they depend on the extract. The yesterday's (correct) table remains in place.

A failure alert fires in the data-ops channel at 3:30 AM with a link to the failed task. Once the cause is fixed, the DAG is re-run from the failed task forward. The dashboards are never wrong; they're just pointed at yesterday's data until today's finishes.

How we think about it at RevenuePoint

Data pipelines aren't the interesting part of a data stack, which is exactly why they have to be boring and reliable. Match the pattern to the shape of the data — scheduled batch for slow-moving reference, CDC for your own operational databases, webhooks for everything else — and orchestrate the whole thing as a DAG so dependencies, retries, and backfills are first-class. Promote the same DAG across dev, staging, and prod the way application code is promoted, so “it worked in dev” actually means something. Get that right and the warehouse on top earns the trust every layer above it is already assuming it has.