Open any SCADA-to-cloud reference architecture and the boxes read the same: historian, then an OT middleware layer (HighByte, AVEVA CONNECT, Twin Talk), then a warehouse. Airflow never appears in the diagram. But go look at what is actually moving tag data at a mid-size operator that never bought that middleware, and it is a cron job, a Python script, or an Airflow DAG somebody wrote in an afternoon. Usually the DAG.
So there is a strange gap. The published stack assumes a product most operators don’t own, and the tool most operators do use to run these jobs has essentially no practitioner writing behind it for this case. This post is that writing. Where Airflow fits in a SCADA stack when there is no dedicated OT middleware, how to model the ingestion DAG so it survives contact with real telemetry, and why orchestrating time series is a different job than orchestrating the business pipelines you already have.
This is part 3 of Beyond the Historian. Part 1 covered why the IT/OT gap exists, part 2 covered the unified namespace as an addressing layer. This one is the plumbing.
Where Airflow fits without OT middleware
OT middleware does two things that matter here. It normalizes tag data from mixed protocols into a consistent shape, and it manages the connection to the operational systems so your analytical side never touches them directly. That is real value. If you have eight SCADA vendors and a UNS to maintain, a product like HighByte earns its license.
Most mid-size upstream operators do not have eight vendors. They have one or two historians, a vendor-hosted SCADA platform, and a couple of scheduled exports. For that shape, the middleware layer is a line item without a job. What you actually need is something to run the extract on a schedule, retry it when it fails, track what it pulled, and hand the raw data to the warehouse for shaping. That is Airflow’s entire reason to exist.
The mental model that keeps this clean: Airflow is the scheduler and the coordinator, not the transport and not the transform. It decides when a pull runs, in what order, with how much concurrency, and what happens when one fails. The connection logic (OPC UA client, ODBC query, SFTP fetch) lives in a task or, better, in a container the task invokes. We make the case for keeping that logic out of the DAG file in the case for decoupled Airflow, and it applies double here, because the code that talks to a historian is exactly the code you do not want welded to your orchestrator.
Airflow does not replace middleware’s normalization. If your tags are a mess across vendors, you still have a mapping problem, and Airflow will happily run a broken mapping on a schedule. What it replaces is the part of middleware that operators are least willing to pay for: job management they can already do.
The three connection types you will actually hit
Almost every SCADA source an operator hands us falls into one of three buckets, and each wants a different Airflow treatment.
REST API to a vendor-hosted platform. ZEDI, ZDSCADA, and most cloud SCADA products expose a REST endpoint with a date-range query and token auth. This is the friendliest case. Use the HTTP hook or a plain requests call inside a task, page through results, and respect the vendor’s rate limits with an Airflow pool sized to what their API tolerates, not what your warehouse can absorb. The failure mode here is pulling too wide a window and getting throttled or timed out, which we covered in how not to take down your SCADA source.
ODBC or a direct client to a historian database. OSI PI, AVEVA Historian, Canary, and Ignition’s tag history sit behind a database or a purpose-built API. If you have ODBC access, this looks like a SQL extract, and you treat it like one: chunked queries, read-only credentials, and a connection defined once in Airflow’s connection store rather than hardcoded. The trap is that these historians are production systems for the control room. A wide SELECT across thousands of tags competes with the live display. Chunk by day and by tag group, and cap concurrency hard.
SFTP scheduled exports. The lowest-tech and, honestly, one of the most reliable. A vendor drops a daily CSV or Parquet file to an SFTP landing spot, and Airflow’s SFTP sensor waits for it before triggering the load. No live query against an operational system at all. When a historian is locked down or the vendor won’t grant API access, this is often the path of least resistance, and it has the pleasant property of putting the extract load entirely on the vendor’s side.
Whichever bucket you are in, land the raw payload first and shape it in the warehouse. Aggregating inside the source query makes every pull more expensive and drags the operational system into work it should not be doing.
Why time-series orchestration is a different job
If your Airflow experience is business pipelines (pull yesterday’s invoices, run dbt, refresh a dashboard) SCADA will break your instincts in four specific ways.
The time series is immutable, but your copy of it is not. An invoice, once booked, does not change its date. A SCADA reading’s event time is fixed, but the value at that timestamp can be rewritten after the fact: calibration corrections, manual overrides, allocation adjustments, a quality flag that flips from good to bad on Friday. A pipeline that assumes “once loaded, done” will quietly hold stale values. You need a separate pass that catches edits, not just new rows.
Late-arriving data is the norm, not the exception. An RTU comes back online and dumps a buffer. A pumper enters a manual reading three days after the fact. The reading’s timestamp is days in the past, but it landed today. A watermark keyed on event time misses all of it. This is the single most common reason a SCADA DAG “works” and the numbers are still wrong.
Clock drift is a data-correctness problem, not a nuisance. When the historian’s clock and your warehouse’s clock disagree by a few minutes, late readings land in the wrong partition, freshness checks report green while data is actually stale, and reconciliation against allocated production produces numbers nobody can explain. Normalize every timestamp to UTC at ingest and record the source’s own timestamp separately. Do not trust datetime.now() anywhere in the path.
Backfill is constrained by the source, not by your compute. In a business pipeline, backfilling a year is a matter of throwing compute at it. Against a historian, a year-wide backfill can degrade the control-room display for the people running the field. Airflow’s catchup=True will cheerfully schedule 365 backfill runs the moment you deploy a DAG with a start date last year, and hammer a production operational system doing it. Turn catchup off, and make deep backfills an explicit, coordinated, off-hours event.
Modeling the ingestion DAG
Put those constraints together and the DAG has a distinct shape. Three passes on different cadences, not one query trying to be correct about everything.
The frequent pass pulls a short overlap window keyed on event time and merges by primary key. The overlap absorbs most late arrivals. A slower pass catches edits using the source’s updated_at column if it exposes one. And a reconciliation pass, weekly, compares row counts and hashes over a trailing window and only repairs what actually drifted. That division of labor is the pattern from how not to take down your SCADA source; here is what the frequent pass looks like as real Airflow.
from datetime import datetime, timedelta
import pendulum
from airflow.decorators import dag, task
from airflow.models import Variable
from airflow.providers.odbc.hooks.odbc import OdbcHook
# Overlap window: re-pull the trailing N days every run so late-arriving
# and buffered readings are captured. Merge by primary key downstream.
OVERLAP_DAYS = int(Variable.get("scada_overlap_days", default_var=7))
TAG_GROUPS = Variable.get(
"scada_tag_groups", deserialize_json=True,
default_var=[["casing_psi", "tubing_psi"], ["flow_mcf", "tank_level"]],
)
@dag(
dag_id="scada_historian_ingest",
schedule="0 */6 * * *", # every six hours, tuned to the report cadence
start_date=pendulum.datetime(2026, 7, 1, tz="UTC"),
catchup=False, # never let a late deploy trigger a year of backfill
max_active_runs=1, # no overlapping runs against the historian
default_args={"retries": 2, "retry_delay": timedelta(minutes=5)},
tags=["scada", "ingest"],
)
def scada_historian_ingest():
@task
def extract_tag_group(tag_group: list[str], data_interval_end=None) -> str:
"""Pull one tag group for the overlap window. Runs inside an Airflow
pool so total concurrency against the historian stays bounded."""
window_end = data_interval_end # UTC, from Airflow
window_start = window_end - timedelta(days=OVERLAP_DAYS)
hook = OdbcHook(odbc_conn_id="historian_readonly") # read-only credentials
placeholders = ", ".join("?" for _ in tag_group)
sql = f"""
SELECT tag_name, event_time_utc, value, quality_flag, updated_at
FROM tag_history
WHERE tag_name IN ({placeholders})
AND event_time_utc >= ?
AND event_time_utc < ?
"""
rows = hook.get_pandas_df(
sql, parameters=[*tag_group, window_start, window_end]
)
# Land raw to object storage; shape it in the warehouse, not here.
key = f"raw/scada/{window_end:%Y/%m/%d/%H}/{tag_group[0]}.parquet"
rows.to_parquet(f"s3://ingest-landing/{key}", index=False)
return key
@task
def merge_to_warehouse(landed_keys: list[str], data_interval_end=None):
"""MERGE landed parquet into the target keyed on (tag_name, event_time_utc).
The overlap window means most rows are no-ops; a changed value or flipped
quality flag updates in place."""
# merge logic omitted for brevity; the key point is upsert-by-key,
# not insert, so the overlap does not create duplicates.
...
landed = extract_tag_group.expand(tag_group=TAG_GROUPS)
merge_to_warehouse(landed)
scada_historian_ingest()
A few things in there are load-bearing. catchup=False and max_active_runs=1 are not stylistic; they are what stops Airflow from turning a deploy into a denial-of-service against the control room. The extract lands raw Parquet and the merge is a separate task that upserts by key, so the overlap window costs almost nothing on rows that didn’t change. And data_interval_end comes from Airflow’s own UTC-aware scheduling, not datetime.now(), which is how you keep clock drift out of the window math.
The pool is the other half. Define it once, size it to the source, and put every extract task in it.
from airflow.providers.docker.operators.docker import DockerOperator
# The pool "historian" is created in the Airflow UI with, say, 2 slots.
# The historian tolerates two concurrent extractions; everything else queues.
extract = DockerOperator(
task_id="extract_tag_group",
image="infocus/scada-extractor:v1.4.0", # connection logic lives in the image
pool="historian",
environment={
"HISTORIAN_DSN": "{{ conn.historian_readonly.get_uri() }}",
"WINDOW_END": "{{ data_interval_end }}",
"OVERLAP_DAYS": "{{ var.value.scada_overlap_days }}",
},
)
That second form is the decoupled version: the code that talks to the historian is a versioned container, the DAG just says when to run it and with how much concurrency. When you migrate off this historian, or off Airflow, the extractor image doesn’t change.
The edits pass and the reconciliation pass
The frequent DAG above is deliberately incomplete. It catches new and late-arriving readings inside the overlap window. It does not catch an edit to a value from four months ago.
For that, add a nightly DAG that queries WHERE updated_at > {last_successful_run} and merges the results. That updated_at column is often present in a historian and not advertised; it is worth a specific ask to the vendor. If the source genuinely has no modified-timestamp, the reconciliation pass becomes your safety net instead of a backstop.
The reconciliation DAG runs weekly, samples a set of tags, and compares row counts and a hash total between source and warehouse over the trailing 90 days. If they match, it does nothing. If a window drifted, it repairs that window specifically. This is the job that replaces “full refresh every run just to be safe,” and it does real work only when there is real work to do. It is also where a watermark table earns its place: per source, per pass, the last successful watermark and the row count returned, so that when something looks wrong the first question (“what did the pipeline actually pull?”) has an answer that isn’t a guess.
None of this makes the pipeline trustworthy on its own. A DAG that runs is not a pipeline you can trust; that gap is tests, ownership, and a runbook, and it sits on top of everything here.
Where this stops being enough
Being honest about the limits: Airflow-managed ingestion is the right call for one or two historians, batch-oriented latency (data that needs to be fresh within hours, not seconds), and a team that already runs Airflow. That covers most of the mid-size operators we work with.
It is the wrong call when you need sub-second streaming from the field, when you are running a genuine multi-vendor UNS that needs live tag normalization, or when the number of distinct sources has grown past what a handful of DAGs can stay readable at. At that point the OT middleware layer the reference diagrams assume starts earning its cost, and the streaming patterns (MQTT with Sparkplug B, Snowpipe Streaming) become the better fit. If your ingestion is fanning out across customer networks and edge sites, the distributed Airflow worker pattern is the bridge between “a few DAGs” and “a real fleet.”
Most operators are nowhere near that line. They have a historian, a report that needs to run at 6 AM, and a pile of tag data that never made it out of the control room. Airflow, modeled for immutable-but-editable time series instead of tidy business batches, gets that data into the warehouse without a middleware invoice. Start with the frequent pass and one use case worth of tags. Add the edits pass and reconciliation once it’s load-bearing. The diagram in the vendor deck is not the only architecture that works.