Global Airline Converts 3,200 DataStage Jobs to DBT on Snowflake — 2,140 SQL Models, 1,060 Snowpark Jobs, Sequences as the DAG

Executive Summary

A global airline’s ops and revenue warehouse still ran on IBM DataStage: 3,200 parallel and server jobs, 410 sequences, 2.7 million lines of stage logic and BASIC, PX sort-merge and a leftover of C transformers. Snowflake was already the commercial analytics store. The data-platform team had standardized on DBT for everything that is a SELECT. MigryX parsed .dsx exports, turned SQL-shaped stage graphs into DBT models with ref() matching sequence predecessors, and left 1,060 jobs as Snowpark where a transformer was a state machine or a C UDF. Fourteen months. Two IATA settlement months dual-ran. DataStage engines are powered off. Three-year gap: $6.1 million.

Client Overview

Passenger service, crew, inventory, and proration grew a DataStage estate because that is what the 2011 EDW program bought. Commercial finance already queried Snowflake. Ops still waited on an on-prem PX grid. A prior attempt to “rewrite in Snowpark” produced 200 jobs and a revolt — analysts could not review Python. DBT was the peace treaty: SQL in Git, sequences as the project DAG, PX leftovers named as Python models.

This is not the federal DataStage-to-Snowflake SQL-only story and not the energy DataStage-to-Databricks story. The target contract is dbt. Snowflake is the warehouse DBT runs on.

Business Challenge

• Sequences are the DAG. 410 sequence jobs. Emitting 3,200 disconnected models would have thrown away the only schedule the airline trusts (departure-day plus settlement calendar).
• PX is not dbt. Sort-merge, partition insert, and IPC pairs are physical. If the stage graph was logically SQL, we still emitted SQL and let Snowflake plan. If it was a custom C transformer, we did not wrap it in Jinja.
• Shared containers. 220 shared containers referenced everywhere. Those became DBT macros or a single staging model — once — not 220 copies.
• IATA / proration output lock. Settlement files have a published layout. Dual-run is the control, not a screenshot of a job log.
• 2.7M LOC is stage expressions + BASIC + C, containers counted once. .dsx XML boilerplate is not counted.

The MigryX Approach

Classifier on each job: if every stage maps to SQL (sequential file → source, transformer expressions → SELECT list, join/agg/filter/sort → SQL), emit a DBT model. Link metadata became column tests where DataStage had not-null or unique constraints. Sequence job links became ref() plus a dbt_project.yml selector per operational calendar (D-1 ops vs monthly settlement).

1,060 jobs failed that test: C transformers, some hashed lookups with reject links that were actually SCD, and IPC pairs we did not collapse because the downstream job is a 14-hour settlement and needs a checkpoint. Those are Snowpark, invoked from DBT Python models or Snowflake Tasks that an exposure documents. Shared containers that were SQL became macros; the 18 that were C became one Python package.

Target Architecture

DataStage PX + sequences → MigryX → DBT on Snowflake (Snowpark for C / IPC / SCD leftovers)

DataStage

PX / Server3,200 jobs

Sequences410 DAGs

Shared containers220, once

→

MigryX

dbt emit2,140 SQL models

Snowpark emit1,060 PX / C

→

Snowflake + DBT

dbt CoreSelectors = calendars

Virtual WHOps vs settlement

SnowparkC / IPC / SCD

TasksWhere sequences branched

GitHub ActionsCI + slim CI

TestsFrom DS constraints

A sequence that used COND-style branching is a DBT selector plus a Task, not a 400-line Jinja if-block. We kept that boring on purpose.

Estate Inventory and Cutover Waves

2,140 + 1,060 = 3,200. Settlement (IATA) stayed last because the file layout is a contract with clearing houses, not an internal dashboard.

Results

"We had already decided DBT was how SQL gets to production. DataStage was 3,200 reasons that decision was stuck. The useful work was the classifier — what is a model, what is still a job — not another Snowpark monorepo."

— Director of Data Platform, global airline