Enterprise API Integration & Workflow Automation Platform
From Spreadsheet to State Machine: Automating a Batch Job Chain
A card payment processor ran its end-of-day batch pipeline, card issuing, acquiring, clearing, embossing, as a chain of roughly eighty jobs triggered by hand through an operations console, using a dependency order tracked only in a spreadsheet. I built a prototype that reads that spreadsheet, reconstructs the real parent/child job tree, and generates an AWS Step Functions state machine from it automatically, with true parallel branches wherever the data supports them, so the trigger order comes from the data instead of a person typing commands.
The problem
The client's card issuing and acquiring platform runs its batch pipeline as roughly eighty scheduled jobs, each with a class name, a parameters payload, and a trigger relationship to the job before it. That relationship lived only in a spreadsheet (one row per job, a parent-guid column), and running the jobs meant an operator issuing a scheduler command by hand for each one, in order, using a login and password that sat in the same spreadsheet. A script that just replayed the spreadsheet's rows in sequence would be wrong: several jobs have more than one dependent that could safely run at the same time, and several chains are fully independent of each other. What the client needed was something that read the real tree, including its parallel branches, and turned it into an orchestration a scheduler could run without a person in the loop for every job.
Constraints
- ·The job graph already existed, informally, as parent-guid rows in a spreadsheet, not as a diagram or a config file, so it had to be reconstructed rather than redesigned.
- ·Real parallelism in the data: several nodes have more than one dependent job, and several trees are independent, so a flat sequential replay would understate what could run concurrently.
- ·AWS Step Functions was the target orchestration engine, constraining the generator's output to that state language (Pass, Task, Parallel).
- ·Built and proven in a personal sandbox AWS account first, before any production wiring was in scope.
Architecture
A tree builder downloads the client's job-tracking spreadsheet from S3, parses each row's guid, parent guid, and parameters, and reconstructs the dependency tree by linking every job to its parent (a job with no parent becomes a root). A state machine generator walks that tree and emits one Pass/Task state pair per job, wrapping any node with more than one dependent in a Parallel state with one branch per child, and wrapping all independent trees in a single outer Parallel root. The generated definition is registered with AWS Step Functions directly from the Lambda. Each Task state's Resource is the ARN of an existing job-runner Lambda that would execute the actual batch step against the client's Quartz scheduler on OpenShift, that Lambda's code sits outside this project.
- ›Job catalog: the client's batch job spreadsheet (guid, parent guid, parameters), read from S3
- ›Tree builder: reconstructs the parent/child dependency tree recursively from the parent-guid column
- ›State machine generator: emits nested Pass/Task/Parallel states, one Parallel branch per sibling job
- ›AWS Step Functions: the generated state machine is registered here (created, not started)
- ›Validation tooling: a notebook that renders the parsed tree as an interactive graph (networkx + pyvis) to check it against the spreadsheet by eye
- ›External, not built here: a job-runner Lambda (referenced by ARN) that would run the client's Quartz scheduler command on OpenShift
How one request flows
- 01Pull the job catalog: download the client's tracking spreadsheet from S3 and load its rows, guid, parent guid, and a parameters string, into a table.
- 02Parse per-job parameters: extract fields like a program code from each row's parameters string, used later by whichever job actually runs the batch step.
- 03Rebuild the dependency tree: link every job to its parent by guid, treat a missing parent as a root, and produce one tree per independent job chain.
- 04Walk the tree into states: each job becomes a Pass state feeding a Task state, and a job with more than one dependent becomes a Parallel state with one branch per child.
- 05Wrap the trees in one root: all independent job chains are wrapped in a single outer Parallel state, so unrelated chains run at the same time.
- 06Register the state machine: the finished definition is registered with AWS Step Functions under a fixed name, returning its ARN. The prototype creates the state machine, it does not start or schedule it.
Engineering decisions
The tree comes from a foreign key, not sheet order
What. Rebuilt the job tree by mapping every row to its parent guid and treating a missing parent as a root.
Why. The spreadsheet has no explicit hierarchy column, and row order does not reliably reflect trigger order in the source data.
Tradeoff. None significant, this is the only correct way to read a parent-guid table.
Parallel branches, not a flat chain
What. Replaced an early flat sequential chain with a Parallel state per multi-child node, one branch per dependent job.
Why. Several jobs in the real data have more than one dependent that can safely run at the same time, and a flat chain would force them through one line for no reason.
Tradeoff. A flat chain is simpler to generate and to read in the AWS console. Correctness was chosen over simplicity, because understating real parallelism defeats the point of moving off a manual trigger process.
Visual validation before trusting the parser
What. Rendered the parsed tree as an interactive graph with networkx and pyvis and checked it against the spreadsheet before writing the state machine generator.
Why. With roughly eighty rows and a tree rebuilt from a foreign key, a silent parsing bug would be easy to miss and expensive to find after it was baked into a generated state machine.
Tradeoff. None significant, one extra offline step against a real risk.
The generator stops at 'created,' on purpose
What. The prototype registers the state machine in AWS and returns its ARN. It does not start it, schedule it, or wire in the job-runner Lambda that would execute a batch job against the client's scheduler.
Why. That Lambda's code was never part of this build, and the goal was to prove the orchestration approach against the real job data, not to claim an end-to-end run against the client's production scheduler.
Tradeoff. A fuller demo could have stubbed that Lambda out to show a complete run. The honest boundary was chosen over a more impressive but misleading demo.
What changed along the way
- →Started against a small hardcoded sample tree to get the Step Functions state shapes right, then swapped in the real S3-backed spreadsheet once the generator was correct.
- →The first working version chained every job sequentially. Once the sample data showed nodes with more than one child, Parallel states were added so siblings run concurrently.
- →The first tree-reading pass only returned one level of children per root. It was rewritten to recurse fully once it was clear several chains ran several levels deep.
Security and reliability
- ·Credentials kept out of code: the job-trigger login used by the client's own scheduler script lives in their spreadsheet, never hardcoded into this project's Lambda functions.
- ·Scoped AWS access: the generator runs under its own IAM role, limited to reading the spreadsheet from one S3 bucket and creating state machines in Step Functions.
- ·Fail-soft parameter parsing: a job whose parameters string cannot be parsed falls back to a default rather than crashing the whole tree build, a known gap since it can mask a bad row instead of surfacing it.
- ·No production wiring, by design: built and run only against a personal sandbox AWS account, never against the client's production account.
Metrics
What shipped
- ✓Built a working generator that turns a real, undocumented job dependency spreadsheet into a valid, registered AWS Step Functions state machine.
- ✓The generated state machine correctly represents parallel job branches, not just the sequential order rows happen to appear in.
- ✓Delivered a visual validation step, an interactive dependency graph, that let the parsed tree be checked against the spreadsheet before trusting it in code.
- ✓Proved the automation approach end to end, from raw spreadsheet to a created state machine, on a personal sandbox account.
- ✓Left a clear, documented boundary on what remains for production: the job-runner Lambda, per-state error handling, and a schedule trigger.
Lessons learned
- ·A dependency tree hiding in spreadsheet rows should be reconstructed from its actual foreign key, never assumed to match row order.
- ·Rendering a parsed tree visually before writing generator logic on top of it catches structural bugs far earlier than debugging a state machine after the fact.
- ·Building against a small hardcoded sample first, before the real data source, made it easy to isolate bugs in the Step Functions shape from bugs in the spreadsheet parsing.
- ·A prototype that clearly states its own boundary, what it does and does not do, is more useful than one that fakes an end-to-end demo.