Leveraging Data Assets features in Airflow 3.0 to optimise resource utilization by more than 30%

At Halodoc, Airflow is the heartbeat of our data platform - not because it moves the data, but because it's the central point of orchestration, deciding when everything else runs. We run it as a managed service on Amazon MWAA, and from the start we kept orchestration and processing separate: Airflow decides what runs and when; the heavy lifting happens elsewhere.

The journey starts with raw CDC records from the systems behind our core products - pharmacy orders, teleconsultations, appointment bookings - which we upsert into Apache Hudi via Spark on EMR on EKS. From there we build dim/fact models, then ingest them into Redshift, our analytics layer. All of it batched, all of it on an Airflow schedule.

Raw CDC → Hudi (Spark on EMR on EKS) → dim/fact models → Redshift (analytics layer)

Because everything is batched, timing matters - a downstream model has no business running until every upstream table it depends on has landed. We enforce that with Airflow sensors, one per upstream dependency, so nothing runs on a half-loaded picture. But sensors aren't the only thing holding workers hostage: ingestion into Redshift runs through a custom operator built on synchronous psycopg, so each load also pins a worker for its full duration. Fine individually - but when dozens of schedules fire together, blocked workers pile up, MWAA autoscales to compensate, and once we hit the worker ceiling, everything else simply queues. Most of those "busy" workers were just holding a connection open, doing nothing.

Why We Upgraded

Because our workloads are batched, they don't trickle in evenly - they cluster into burst windows where dozens of schedules fire at roughly the same time. In those windows, two things pile up together: ingestion tasks blocked on psycopg waiting for Redshift, and sensor tasks blocked waiting on upstream data. Neither is a new problem on its own, but at burst scale they compound - enough blocked workers at once, and MWAA autoscales, hits its ceiling, and everything else queues behind them.

Airflow 3.0 gave us a direct way to fix both: Assets that don't need a sensor task polling over and over, and deferrable operators that can release the worker slot instead of holding it while they wait.

• Replace polling sensors with data-aware Assets (where applicable). Take that appointment-revenue model from earlier - two upstream tables meant two separate sensor tasks, each looping on its own "is it ready yet?" check, each holding a worker slot the whole time it waited. Assets flip that: the upstream job produces an Asset, and that event triggers the next step directly - no polling, no slot held just to wait.
• Swap worker-blocking ingestion for the deferrable Redshift Data Operator. Move Redshift loads off synchronous psycopg and onto the Redshift Data API through the RedshiftDataOperator, which can defer: it submits the SQL, frees the worker while Redshift runs it, and picks back up when the statement finishes - instead of pinning a slot for the whole query.

Both come down to the same fix: stop paying for compute to sit still during a burst. That was the plan. Getting there - from Airflow 2.10, through the wall of bugs we hit in 3.0.6, and finally onto 3.2.1 - was the part we'd underestimated.

Data Asset

As we described earlier, we leaned heavily on Airflow sensors to make sure a downstream job never began its ingestion until every upstream it depended on had finished processing.

As we mentioned earlier, we set out to try the Data Assets feature on the latest Airflow - starting with our non-critical pipelines - for two reasons:

• Downstream triggers only once every upstream is done. A downstream job fires automatically the moment all of its upstream Assets have been updated - no fixed-schedule guesswork, and no poll loop waiting for data to land.
• Fewer sensor listeners on every downstream DAG. It removes the stack of sensor tasks we'd been attaching to each downstream DAG just to watch upstream state.

With Data Assets, we don't have to give the downstream job a schedule of its own. Instead, we add an outlet to the upstream task and an inlet to the downstream:

• Outlet (upstream). When the upstream task finishes, its outlet emits the Asset - a signal that the upstream table is done and freshly updated.
• Inlet (downstream). The downstream picks up that Asset through the inlet we declare, and runs - no cron, no polling, just a reaction to the upstream being ready.

There was a wrinkle, though. Long before Assets, our pipelines already coordinated these dependencies through cross-job dependency with sensors, and every job's sensor configuration lives in a central control table. So adopting Data Assets was never a clean-slate exercise - we had to make it fit the dependency configuration we already had in that control table, rather than replace it.
Here's what that looked like:

So the downstream's inlets simply follow our existing configuration - the same dependencies, now read straight from the control table.

That covers the downstream. On the upstream side, each task needs an outlet - the piece that signals when its process has finished.

Our dependency model already carried an important distinction: each upstream is either a strong dependency the downstream must have, or a weak one it can proceed without - the is_skip_allowed flag from that same config. Sensors handled weak dependencies naturally by skipping them. Assets don't: a downstream only fires when its Assets are emitted, so a weak upstream with no new data would stay silent and the downstream would never trigger. To preserve that behaviour, we emit the outlet even when the upstream has no update or new data - the downstream still triggers, and we push the decision into the downstream task, where we validate whether ingestion is actually needed when one of the tables didn't change.

One deliberate exception: we don't emit asset events during backfills or manual triggers. Without that guard, a single backfilled or manually run upstream would cascade into a domino of downstream runs we never meant to start.

That downstream validation is where it all comes together. Before it ingests, each downstream job checks itself against its own configuration: if every dependency meets the rule, it proceeds with ingestion; if not, the ingestion task is skipped.

We layer one more check on top: how recently each upstream actually ran, bounded by a MAX_LOOKBACK_MINUTES window - set to 360 minutes, or six hours. This makes sure the downstream only runs when the dependency rule is satisfied and the data is genuinely fresh. It matters because Data Assets have no built-in freshness guardrail: Airflow only checks that each required Asset has been emitted at least once since the downstream last ran - not how recently. Without the lookback, an Asset emitted yesterday and another emitted today would both count as satisfied, and the downstream would fire on day-old, stale data.

Redshift Data Operator

For loading into Redshift, we had long relied on a custom ETL operator of our own, built on psycopg. Over time it had grown fairly capable: it handles SCD2 for our dimensions, chunks large loads into smaller batches, and manages its own temp tables for staging before the final write.

In practice, each load walks through the same sequence of steps:

1. Drop the staging table.
2. Create the staging table.
3. Insert-select the source data into the staging table.
4. Delete from the destination table.
5. Insert from the staging table into the destination table.

That capability came at the cost we met back in the introduction. Because psycopg is synchronous, the operator holds its Airflow executor slot for the entire run - and some of our loads run well past fifteen minutes. For that whole stretch, a worker stays pinned to a single job that is mostly just waiting on Redshift to finish.

It bites hardest during ETL bursts, when many of these loads fire at once and a whole cluster of workers ends up pinned together. That's what pushed us to the RedshiftDataOperator, which supports deferrable execution: the task submits its SQL, releases the worker, and only resumes once Redshift reports the statement is done.

It isn't a straight swap, though. The Redshift Data API it runs on comes with a couple of hard limits:

• The maximum query result size is 500 MB (after gzip compression).
• The maximum query statement size is 200 KB.
• The maximum number of active queries (STARTED and SUBMITTED queries) per Amazon Redshift cluster is 500.

None of these turned out to be a hard blocker - though two of them needed a bit of attention to stay safely inside the limits. Starting with the simplest: our loads don't pull data back into Airflow - they insert into a staging table, then run delete-and-insert into the target - so there is no large result set coming back, and the 500 MB ceiling simply never applies.

The active-query ceiling needed a small piece of engineering. The Data API counts every STARTED and SUBMITTED statement against that per-cluster limit of 500, so a large enough burst of deferred loads could, in principle. We cap that with an Airflow pool: every task that goes through the Data API has to claim a slot from a pool we sized at 65, so no matter how many DAGs fire at once, the number of in-flight statements stays comfortably below the cluster's ceiling.

The statement-size limit needed a little more handling. When a generated SQL script runs past 200 KB, we fall back to psycopg automatically - the operator picks the right path on its own, so nothing calling it has to care which one ran.

Statement size isn't the only signal we route on. Deferral only pays off when a query actually takes time to run - deferring a sub-second DROP TABLE or CREATE TABLE would just add overhead for nothing. So the first two steps - dropping and creating the staging table - stay on psycopg, and we reserve the Redshift Data API for the three that genuinely run long: the insert-select, the delete, and the stage-to-destination insert.

The hybrid approach gives us the best of both. The operations that genuinely need to wait - insert-select and the like - release their executor slot and defer, while the quick ones take the fastest path. And because we wanted to be careful, we built in one more safety net: if the RedshiftDataOperator ever runs into trouble, the task automatically falls back to psycopg on Airflow's third retry. Since the operator ultimately talks to an API, that guard means a transient hiccup - a timeout, say - doesn't derail the load; it just finishes the old way.

One caveat, to be straight about it: the final delete and insert on the destination run as two separate Data API statements, not one transaction - so if the insert fails after the delete commits, the table reads short until a retry rebuilds it. We could make it atomic by sending both as a single BatchExecuteStatement - that hardening is on the roadmap.

Keeping each statement independent lets the deferrable path reuse the synchronous operator's ETL logic unchanged - cheap DROP/CREATE stay on psycopg, only the heavy DML hits the Data API, and per-statement logging plus per-chunk checkpoints keep retries resumable instead of replaying an opaque block. It also suits Redshift specifically: wrapping a large delete-and-insert in one transaction holds a lock on the target for the whole load and serializes it against any other writer, whereas auto-committing each statement releases locks quickly, and the staging pattern already narrows the non-atomic window to the final swap.

Correctness isn't at stake: the sequence is idempotent, and after two failed attempts the router falls back to psycopg, so the table always converges - what's briefly exposed is availability until that retry, not wrong data.

We Hit the Bugs

With the plan set, we moved our MWAA environment from Airflow 2.10 up to 3.0.6 and got to work. This was the version where we'd build the two things we came for - the deferrable Redshift Data Operator and data-aware Assets - and on paper, 3.0.6 had everything we needed.

Then, almost out of nowhere, we ran into a bug. Airflow was issuing far more database round-trips than it should have - a classic N+1 query pattern, this time buried in the core itself as it serialized and scheduled our hundreds of DAGs. At our scale, those redundant queries piled up fast.

The symptom was impossible to miss once we went looking. In CloudWatch, the writer instance of Airflow's own metadata database was pinned near 100% CPU - and it stayed there even when almost nothing was running. This wasn't our data warehouse; it was the database Airflow uses to track its own state. On MWAA that database is fully managed for us, so we couldn't simply tune our way out of it. The pressure landed squarely on the scheduler and the DAG processor, and everything that leaned on that database started to feel sluggish.

The important part: this wasn't something in our DAGs or our operators. It lived in Airflow itself, which meant no config knob on our side could make it disappear. We were stuck on the exact version that had the features we wanted and a bug we couldn't live with - so the fix had to come from a newer Airflow. We set our sights on 3.2.1.

The jump paid off. On 3.2.1 version, the N+1 pattern was simply gone: the metadata database settled back down, its CPU returned to normal, and the scheduler got its breathing room back. The bug that had blocked us on 3.0.6 was fixed upstream, and because it was an upgrade within the same major version, the path itself was supported and low-drama.

3.2.1 did have one surprise of its own, though a far gentler one. As soon as it parsed our DAGs, it began flagging a warning we'd never seen before:

This Dag uses runtime-variable values in Dag construction.It causes the Dag version to increase as values change on every Dag parse.

This one traces back to DAG versioning, a new capability in Airflow 3: Airflow now keeps track of a version for each DAG's definition. The catch is that one of our DAGs passed pendulum.today('UTC') into its default_args - a value that resolves to a different timestamp on every parse. Airflow saw that shifting value and concluded the DAG had changed, bumping its version on every single parse. The fix was straightforward: keep runtime-varying values out of the DAG and Task constructors, and pin a static start date instead.

Conclusion

We shipped both changes successfully: Data Assets adoption and the deferrable Redshift Data Operator migration. These are critical parts of our production data flow, so rather than flipping everything over at once, we scoped this first phase deliberately - Data Assets across 30 DAGs, and the deferrable Data Operator across 130 DAGs, and watched them closely. After more than a month of monitoring, we've seen no discrepancies and no data issues: the event-driven scheduling and the deferrable loads have behaved exactly as we hoped.

The impact was clear in the numbers. During peak ETL windows - when ingestion loads and the Asset listeners run together - four utilization metrics came down: base worker CPU fell from 26.1% to 7.71% on average and base worker memory from 49.2% to 30.8% on the Redshift side, while scheduler memory dropped from 53.2% to 43.7% and scheduler CPU from 36.1% to 29.4%. And during ingestion, table-locking errors caused by heartbeat timeouts came down by roughly 38%.

There's still headroom to go further. The most immediate step is making the final delete-and-insert swap atomic by moving it to a single BatchExecuteStatement - the hardening we flagged earlier - which we'll take on in the next iteration. But even in this first phase, the direction is unmistakable: letting data readiness drive scheduling, and letting long-running loads defer, has handed us back a large share of the compute we used to burn just waiting.

Reference

Join us

Scalability, reliability, and maintainability are the three pillars that govern what we build at Halodoc Tech. We are actively looking for engineers at all levels and if solving hard problems with challenging requirements is your forte, please reach out to us with your resumé at careers.india@halodoc.com.

About Halodoc

Halodoc is the number one all-around healthcare application in Indonesia. Our mission is to simplify and deliver quality healthcare across Indonesia, from Sabang to Merauke. Since 2016, Halodoc has been improving health literacy in Indonesia by providing user-friendly healthcare communication, education, and information (KIE). In parallel, our ecosystem has expanded to offer a range of services that facilitate convenient access to healthcare, starting with Homecare by Halodoc as a preventive care feature that allows users to conduct health tests privately and securely from the comfort of their homes; My Insurance, which allows users to access the benefits of cashless outpatient services in a more seamless way; Chat with Doctor, which allows users to consult with over 20,000 licensed physicians via chat, video or voice call; and Health Store features that allow users to purchase medicines, supplements and various health products from our network of over 4,900 trusted partner pharmacies. To deliver holistic health solutions in a fully digital way, Halodoc offers Digital Clinic services including Haloskin, a trusted dermatology care platform guided by experienced dermatologists.We are proud to be trusted by global and regional investors, including the Bill & Melinda Gates Foundation, Singtel, UOB Ventures, Allianz, GoJek, Astra, Temasek, and many more. With over USD 100 million raised to date, including our recent Series D, our team is committed to building the best personalized healthcare solutions — and we remain steadfast in our journey to simplify healthcare for all Indonesians.