migrating-to-amazon-redshift

Files (14)

• SKILL.md
• architecture-mapping.md · references/teradata/
• bteq-to-rsql.md · references/teradata/
• common-errors.md · references/teradata/
• conversion-rules.md · references/teradata/
• data-migration-patterns.md · references/teradata/
• data-type-mapping.md · references/teradata/
• discovery-queries.md · references/teradata/
• orchestration.md · references/teradata/
• performance.md · references/teradata/
• reporting.md · references/teradata/
• sizing.md · references/teradata/
• stored-procedure-migration.md · references/teradata/
• validation-patterns.md · references/teradata/

SKILL.md (first lines)

# Migrating to Amazon Redshift

## What this skill is

This skill is **AI guidance, not an execution framework**. It is **entirely Markdown
knowledge** (rules, mappings, patterns, best practices) — **no executable code**. All
execution — conversion, the discovery/migration/validation runners, dependencies, and
infrastructure — **you (the AI) generate at runtime** from this knowledge, tailored to the
customer's environment.

Principle: **knowledge over shipped code → less drift, nothing for the customer to run or
depend on, reliable first-time results.** Do not look for a pyproject, a tools package, an
orchestrator engine, or shipped scripts — there are none by design; you generate execution.

> **Runtime:** this skill works **with or without the AWS MCP server** — step guidance uses AWS
> CLI syntax. Running it **with the AWS MCP server is recommended** for sandboxed execution and
> audit logging; without it, the AI runs the generated scripts on the host shell (assumes Bash,
> Python 3, and AWS CLI + credentials). Do not assume MCP-only tools are available.

## Source routing

This skill migrates a supported **source data warehouse to Amazon Redshift**. First identify the
**source system**, then load that source's knowledge under `references/<source>/`:

- **Teradata (Vantage)** → `references/teradata/` — supported (all references below).
- *Other sources (e.g. Snowflake, Oracle) — unsupported; each is added as its own `references/<source>/` set when ready.*

The **workflow is source-agnostic** (discovery → convert → migrate → validate → performance →
report); only the **conversion knowledge** is source-specific. Everything below is the Teradata set.

## When to use

- Migrating a Teradata system (Vantage) to Amazon Redshift.
- Converting Teradata DDL, SQL, stored procedures, macros, or BTEQ to Redshift/RSQL.
- Assessing Teradata→Redshift migration complexity/effort.

## Operating principles

- **Discovery is strictly read-only (SELECT-only) on the source.** Never change production
  state: no DDL/DML, and never enable logging (`BEGIN/REPLACE QUERY LOGGING`). If DBQL is
  empty, mark it `unavailable` and fall back to always-on `DBC.AMPUsageV` — see
  `references/teradata/discovery-queries.md`.
- **Skill provides knowledge; you generate execution.** Read the `references/` to reason and
  convert — apply the rules in `references/teradata/conversion-rules.md` directly for conversion, and
  generate the discovery/migration/validation runners (and the read-only discovery collector
  from `references/teradata/discovery-queries.md`) tailored to the environment.
- **Generate, don't assume a framework.** Assume the environment has Bash, Python 3, and AWS
  CLI + credentials. Any Python lib a generated script needs (`teradatasql`, `boto3`, …) is
  `pip install`-ed on demand by that script / its run-instructions — pin exact versions.
  Teradata **TTU** (BTEQ/TPT) is **Linux/Windows-only — not macOS**; prefer **WRITE_NOS** +
  **`teradatasql`** (cross-platform, no client) for discovery/extract unless a TTU/Linux host exists.
- **Credentials:** use a **read-only** Teradata user; prefer IAM roles over IAM users. For
  **production**, reference credentials from **AWS Secrets Manager or Systems Manager
  Parameter Store**. For **local development only**, a git-ignored `.env` file or profile may
  be used — never commit it. Never hard-code or echo secrets. In a portable
  bundle, reference a **co-located credentials file** and ship a `credentials.env.example` template — the
  real file is git-ignored.
- **Persist state in files.** All generated output goes under a git-ignored `output/` in the
  user's working dir; keep `output/state.md` current so work is resumable.

## Workflow (phases)

Run in order; each phase's `result/` feeds the next (see `references/teradata/orchestration.md`).

1. **Discovery** — inventory the source. → `references/teradata/discovery-queries.md` (read-only collection SQL + BTEQ driver template the AI generates) → `output/discovery/result/inventory.json`
2. **Conversion** — schema + code. Apply the conversion rules directly, flag the
   manual-rewrite long tail, and fix Redshift errors from the references. →
   `references/teradata/conversion-rules.md`, `references/teradata/data-type-mapping.md`,
   `references/teradata/architecture-mapping.md`, `references/teradata/stored-procedure-migration.md`,
   `references/teradata/bteq-to-rsql.md`, `references/teradata/common-errors.md`
3. **Data migration** — extract → S3 → COPY, restartable. → `references/teradata/data-migration-patterns.md`
4. **Validation** — counts/aggregates/sampling. → `references/teradata/validation-patterns.md`
5. **Performance** — baseline vs Redshift; size the target. → `references/teradata/performance.md`, `references/teradata/sizing.md`
6. **Reporting** — aggregate all phases. → `references/teradata/reporting.md`

## Conversion (how the AI applies it)

There is no converter to run — convert by **applying the rules in
`references/teradata/conversion-rules.md` directly** (with the type / architecture / stored-procedure /
BTEQ references): apply the deterministic rules to the well-understood bulk, **flag the
manual-rewrite constructs** with their suggested rewrites, assign a **confidence** per object,
and fix any Redshift errors using `references/teradata/common-errors.md`. The reference docs are the
single source of truth; `conversion-rules.md` includes golden input→output examples to match.

## Execution modes (connectivity)

- **Connected** — your host can reach Teradata/Redshift → run the generated scripts in place.
- **Disconnected** — it can't → generate a self-contained bundle under `output/<phase>/`
  (script + co-located credentials template + relative `result/` + `run-instructions.md`); the
  operator runs it on a reachable host and copies `result/` back. The copied-back `result/` is
  the durable state — read it (+ `state.md`) and continue.

## Project-workspace layout (per migration run)

```
<project-workspace>/
  migration-config.yaml          # operator-authored: endpoints, scope, strategy
  .gitignore                     # ignores output/
  output/                        # everything generated (git-ignored)
    state.md                     # progress cursor
    discovery/   …  result/inventory.json
    conversion/  …  result/{ddl,sql,procedures,rsql}/  manual_review.json
    data_migration/ … result/{extract,load,templates}/  migration_manifest.json
    validation/  …  result/validation_report.json
    performance/ …  result/{perf_baseline,perf_compare}.json
    reporting/      result/migration_report.md
```

## Security considerations

- **No shipped code or dependencies.** This skill is text-only — the customer runs nothing from
  it. Any runner the AI generates MUST pin exact dependency versions, validate/sanitize inputs
  (file paths, SQL, shell args), and never print or log credentials, secrets, or PII.
- **Least privilege + ephemeral credentials.** Use a **read-only** Teradata user for discovery. On AWS
  prefer **IAM roles over IAM users** and **IAM auth over username/password**. Keep secrets in
  **AWS Secrets Manager / Parameter Store** — never hard-code, echo, or commit them (credentials files
  are git-ignored; ship only `*.example` templates).
- **Data in transit / at rest.** Use TLS to both engines; stage extracts in an **encrypted S3
  bucket** (SSE) with a least-privilege bucket policy; load via `COPY … IAM_ROLE` (not access

From aws/agent-toolkit-for-aws, Apache-2.0.

More from aws/agent-toolkit-for-aws