How to migrate an Ab Initio reconciliation graph

To migrate an Ab Initio workflow, choose one graph and an output that a reporting owner can approve. Capture the graph and the plan that runs it, then document the input formats, reconciliation rules, and restart behavior. Rebuild the selected batch in SQL or Python when the measured workload fits, and compare both matched records and exceptions before you switch the report.

When should you deduplicate an Ab Initio reconciliation input?

A repeated ledger row changes the reconciliation. Compare its immutable file version and row number, then check the ledger line ID against the deployed graph's duplicate rules.

Recovery. Ignore a verified transport replay. Keep a distinct, valid ledger line and sum it with the other lines at the business date, currency and transaction key before joining the two sides.

Cutover check. Compare the original exception file and shadow output by full key, presence on each side, outcome and signed amount. Block release on any unexplained difference, including a zero-value transaction classified as matched when the ledger side is absent.

Convert a deployed reconciliation graph into executable component contracts

This synthetic example assumes two daily extracts and a downstream exception report. Use the deployed GDE component labels and EME revision from your environment. The names below are working labels you can replace. Ask the operator to confirm the resolved DML and parameter values for both input legs before writing the parser. Ab Initio product and metadata tooling

Implement the reconciliation join

Run this in a SQL engine after parsing and accepting both legs. Each stage has one row per validated source line. trade_stage must already enforce the approved trade-key duplicate policy. A full outer join is equivalent only after both sides have been rolled up.

WITH t AS (
  SELECT business_date, currency, txn_id, SUM(amount_minor) AS trade_minor
  FROM trade_stage GROUP BY business_date, currency, txn_id
), l AS (
  SELECT business_date, currency, txn_id, SUM(signed_minor) AS ledger_minor
  FROM ledger_stage GROUP BY business_date, currency, txn_id
), keys AS (
  SELECT business_date, currency, txn_id FROM t
  UNION SELECT business_date, currency, txn_id FROM l
)
SELECT k.*, t.trade_minor, l.ledger_minor,
       COALESCE(t.trade_minor, 0) - COALESCE(l.ledger_minor, 0) AS delta_minor,
       CASE WHEN t.txn_id IS NULL THEN 'LEDGER_ONLY'
            WHEN l.txn_id IS NULL THEN 'TRANSACTION_ONLY'
            WHEN t.trade_minor = l.ledger_minor THEN 'MATCHED'
            ELSE 'AMOUNT_MISMATCH' END AS outcome
FROM keys k
LEFT JOIN t USING (business_date, currency, txn_id)
LEFT JOIN l USING (business_date, currency, txn_id)
ORDER BY business_date, currency, txn_id;

For the synthetic fixture above, append a JPY T1 zero-value transaction and no JPY ledger row. Expect five keys across two currencies. The JPY row is TRANSACTION_ONLY with a zero delta. Now replay one ledger line with the same file ID and row number, then replay a distinct line ID with the same amount. The transport duplicate contributes no value. The legitimate second line contributes according to the approved ledger rules. Test both conditions separately before signing off.

Move the run, including its restarts

Package the parser, normalization rules, SQL and report writer together. Inject the business_date, input file versions and rule version from the schedule rather than asking each task to discover them. Initially use a shadow output location unavailable to report subscribers. Have the legacy operator retain the resolved file identities and original exception outputs from an approved run. Add a peak-volume input slice to verify the chosen engine meets the deadline without changing the reconciliation key or report order. After the last accepted legacy report, record the input and output hashes, the next owned date and the original plan's paused state. A rollback stops the replacement publisher first and reassigns only dates without an accepted receipt.

Implementation runbook

What the source proves

Ab Initio documents the GDE as the design tool, the EME as the versioned store for graphs, schemas and rules, and Conduct>It as orchestration with recovery. Control>Center can also schedule jobs. The public product pages do not establish a portable graph export command. Ask a licensed operator for the deployed EME tag or source control revision and the associated project/sandbox using their installed version's supported workflow. Compare the revision against the run log and deployed wrapper. A screenshot or reconstructed GDE graph cannot prove deployment parity. Ab Initio product list

What to collect before converting components

1. Inventory each graph, subgraph, record layout (for example DML if used), transform, parameter include, lookup snapshot and shell dependency. Capture where environment overrides and command arguments resolve. Record the sanitized effective values for a success and a recovery.
2. For each link capture field order, character encoding, fixed width vs delimited layout, null sentinel, precision, decimal sign/scale, business date and header/trailer. Require a raw-byte fixture for fixed-width records, especially multibyte data. Preserve audit and reject streams independently. For parallel files request the layout/control path, partition count and key, sort key and tie-breaker, per-partition ordering versus global ordering, and how the runtime collects partitions.
3. Draw the execution graph from scheduler predecessor to delivered file. Capture waiting-for-input, retrying after failure, restart point, return-code handling, max concurrency and downstream ready marker. A recovery checkpoint in the old runtime is evidence of completed work and cannot be loaded into a SQL job. Create a new ledger from the last audited output and the input identities that produced it.
4. For each actual component define a contract. Normalize should specify trim and padding, null/blank handling, decimal parsing and reject reason. Sort/dedupe should specify survivor ordering. Rollup should specify complete grouping key and sign conventions. Join should specify key and null behavior, outer/inner path and multiplicity. Delivery should specify bytes, per-currency count/amount and success signal. These are names for examples, so use the labels and semantics from the deployed graph.

Implement a nightly transaction-ledger match

• Load the frozen business-date extracts into separate staging tables with source system, immutable file/checksum, row number, currency and integer minor units. Reject records failing format checks into a separate table. Enforce currency-specific scale and deterministic ordering with null and duplicate fixtures. Do not compare dollar sums across currencies.
• Establish whether same-ID repeats represent ledger lines to aggregate, exact transport duplicates, or conflicting transaction IDs to quarantine. In the worked reconciliation, repeated ledger lines are legitimate and rolled up before joining. Use GROUP BY business_date, currency, txn_id independently for the transaction and ledger sides. For portability, build the full keyset with UNION then join each aggregate to the keyset. Keep source counts so a zero-dollar row can be distinguished from a missing row.
• Emit MATCHED, AMOUNT_MISMATCH, TRANSACTION_ONLY and LEDGER_ONLY with each side's total. Compare old/new rows by full business key, exception class and signed delta. Compare CSV bytes only when formatting/ordering forms part of the downstream contract. Run a row difference as well as grouped control totals.
• Record date, input hashes, lookup version, transform version, counts by class/currency, staged URI, published version. A unique date-and-output claim prevents two workers from publishing. Publish a versioned export with its ready marker only after controls pass. Verify the marker points to that exact staged checksum. Resume from that ledger on error rather than resubmitting a successful transfer. Run a volume/peak partition test before simplifying a graph which requires parallel compute.

Acceptance fixture with duplicate join pressure

Synthetic USD cents for one day: transactions T1=425, T2=900, T3=125. Ledger T1 has two lines 200 and 225, T2=875 and ledger-only T4=50. Expect 4 output keys, T1 matched 425/425, T2 mismatch 900/875 (+25), T3 transaction-only 125/absent (+125), T4 ledger-only absent/50 (-50). Transaction total 1450, ledger total 1350, signed delta 100. Test a second currency with the same T1 to prove keys include currency. After staging, deliberately stop before the ready marker, then rerun the identical date/input identity. Expect one final marker and no duplicate delivery. On cutover, reconcile the last old accepted date and the first new date with the reporting owner. Keep a retained original snapshot plus the old graph available to replay only approved missing outputs.

Search and implementation questions

• What should I export from Ab Initio before rewriting a graph? A deployed revision, its input/output record layouts, component rules, run-effective parameters, Conduct>It or equivalent execution plan and one successful plus one failed run.
• Can a SQL replacement reproduce a multi-file graph? Yes for a workload that fits the target engine and where partitioning, sort order, reject paths and output controls can be expressed and tested. Reproduce the consumer's logical and byte contracts before changing the engine.
• AI task: pass a sanitized component contract matrix and the synthetic fixtures. Request one SQL query per transform, explicitly keyed by component and source field, followed by expected output rows, gaps needing an operator, and a diff against old/new exception files. Withhold production connections, credentials and real data.

Try a reconciliation fixture

Run this with Python 3. It represents one amount per transaction in integer cents. Equal amounts match. An unequal amount or a transaction missing from the ledger becomes an exception.

transactions = {"T1": 425, "T2": 900, "T3": 125}
ledger = {"T1": 425, "T2": 875}
matched = {key for key, amount in transactions.items() if ledger.get(key) == amount}
exceptions = {key: amount - ledger.get(key, 0)
              for key, amount in transactions.items() if key not in matched}
assert matched == {"T1"}
assert exceptions == {"T2": 25, "T3": 125}
assert sum(transactions.values()) - sum(ledger.values()) == 150

Add fixtures for a ledger-only record and a repeated transaction key. Decide how to classify both before writing the production join. Match your known-good report's control totals and compare exception IDs, since equal totals can hide offsetting mistakes.

Copy these prompts into your AI assistant

Read this sanitized Ab Initio graph inventory, record layouts, and Conduct>It steps. List the inputs, transform and sort rules, reconciliation keys, exception outputs, and restart dependencies. Cite graph component names and mark each hidden rule that still needs an operator.

Build a SQL reconciliation draft using these two snapshot schemas and integer cents. Include the expected fixture output, tests for duplicate and unmatched keys, a staging-only writer, and a comparison report keyed by business date. Keep unresolved matching choices in a review list.

Schedule and switch the report

Lock each report date to one publisher. Retry temporary source failures with the same snapshots and bounded backoff. Quarantine rule or schema failures and preserve the previously approved report. A scheduled AI task can summarize missing reports and exception changes from the run manifest for the owner.

Shadow the existing graph over regular runs and a close-day sample. Agree the last old batch and first new batch, then change only that report's publishing schedule. Retain the prior graph and input versions for rollback, and reconcile published dates before restarting the old report.

References

• Ab Initio GDE, EME, and Conduct>It
• Ab Initio platform independence and processing

Need help with the migration?

Explore Ab Initio migration services or browse all DIY migration guides.