/migrating-to-amazon-redshift

Orchestration — phase workflow & state model

AI-facing knowledge (the AI reads this to drive the migration); it is not executed code. The patterns below were proven in the pre-restructure tools/orchestrator.py engine; here they are guidance for how you (the AI) sequence phases and persist state in files.

Purpose

Describe how the AI sequences a Teradata→Redshift migration end to end, how state is persisted so work is resumable, and how execution adapts to the customer's connectivity.

Phases

Run in order; each phase's result/ is the durable hand-off that feeds the next.

Conversion folds in a code-review gate: low conversion confidence (scored per the rubric in conversion-rules.md) or constructs with no deterministic rule (functions, triggers, join indexes, RESET WHEN) are written to manual_review.json and pause the run — see HITL gate below.

Phase status lifecycle

Every phase has exactly one explicit status. Record it in state.md so the run is observable.

PENDING ──▶ RUNNING ──▶ SUCCESS        (continue to next phase)
                   │
                   ├──▶ SKIPPED         (phase not applicable this run; continue)
                   ├──▶ NEEDS_REVIEW    (HITL pause — a human must decide, then resume)
                   └──▶ FAILED          (stop — diagnose, fix, resume)

• SUCCESS / SKIPPED let the pipeline proceed.
• NEEDS_REVIEW / FAILED stop the run. Resume re-enters at the first non-SUCCESS phase, which is what makes resume idempotent (already-SUCCESS phases are not redone).

HITL gate (when to pause for human review)

Pause the phase as NEEDS_REVIEW — do not silently proceed — when:

• Conversion: an object's conversion confidence < threshold (default 0.70, matching the rubric's High-weight auto-flag ceiling in conversion-rules.md; scored per the rubric in conversion-rules.md), any manual-review items are flagged, or the object kind has no deterministic rule (function / trigger / join index). Summarize as {objects, needs_review, min_confidence}.
• Data migration: any table failed to extract/stage/load. Validating a partial load as if it were complete hides real problems — pause instead.
• Validation: any table's status is fail and the operator has not accepted the diff.

After a human decides, record the decision and call resume.

State model (resumability)

• All generated artifacts live under output/ (git-ignored) in the user's working dir.
• output/state.md is the single source of truth for progress — the durable cursor that survives process restarts, machine changes, and disconnected copy-backs.
• Durable vs ephemeral: the per-phase status + the pointer to each phase's result/ are durable (written to state.md / files). Live in-memory objects (the discovery inventory, the migration manifest) are ephemeral — intentionally not persisted. On a resumed run the AI re-derives them by reading the prior phase's result/ (e.g. re-read inventory.json) rather than expecting them in memory.
• After every phase transition, update state.md before starting the next phase.

state.md schema (example)

# Migration run state
run_id: acme-edw-2026-06-29
source_db: teradata
strategy: full-load
updated: 2026-06-29T17:40:00Z

| phase          | status        | result pointer                                   | note |
|----------------|---------------|--------------------------------------------------|------|
| discovery      | success       | output/discovery/result/inventory.json           | 3 schemas, 214 tables |
| conversion     | needs_review  | output/conversion/result/manual_review.json      | 7 objects < 0.8 confidence |
| data_migration | pending       | —                                                | |
| validation     | pending       | —                                                | |
| performance    | pending       | —                                                | |
| reporting      | pending       | —                                                | |

Keep one row per phase. result pointer is the relative path the next phase reads.

Hand-off contract between phases

• A phase may start only when the previous phase's status is SUCCESS (or SKIPPED when not applicable) and its result/ artifact exists and is readable.
• Each phase reads only its declared inputs (see the Phases table) — never another phase's in-memory state. This keeps every phase independently runnable and testable.
• A phase writes its outputs to result/ before marking itself SUCCESS, so a crash between "wrote files" and "marked success" simply re-runs the phase (idempotent).

Decision points (guidance for the AI)

• Full vs incremental load — default to full-load (truncate-and-reload, idempotent). Use incremental/CDC only when the table is too large to reload in the cutover window and a reliable high-water-mark column exists. See data-migration-patterns.md.
• Connected vs disconnected — see Execution modes. Decide once at run start; it changes whether you run scripts in place or emit a bundle.
• Stop vs continue on a failed object — a single failed table or low-confidence conversion pauses the run for review; it does not abort the whole migration. Other tables already SUCCESS are preserved.

Execution modes

• Connected — agent host can reach TD/RS → it runs generated scripts in place and writes result/ directly.
• Disconnected — agent emits a self-contained output/<phase>/ bundle (script + co-located creds template + relative result/ + run-instructions.md); the operator runs it on a reachable host and copies result/ back. The copied-back result/ (+ state.md) is the durable state — read it and continue from the first non-SUCCESS phase.

See also

• SKILL.md — phase list and project-workspace layout (keep this table in sync with it).
• data-migration-patterns.md — migration_manifest.json contract consumed by validation.
• validation-patterns.md — validation_report.json consumed by reporting.