migrating-to-amazon-redshift

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 keys). Enable encryption on the target Redshift cluster.
• Blast radius. Discovery is read-only by design. Migration writes to the target — validate against a throwaway / non-production Redshift first, and never point a generated write-path at production without explicit operator confirmation.
• No secret leakage in artifacts. Generated output/… (manifests, reports, state.md) MUST NOT embed credentials or endpoints beyond what the operator supplies in migration-config.yaml.
• COPY IAM_ROLE hardening. Scope the role's policy to the specific staging prefix (not bucket-wide s3:*), and include condition keys in its trust policy (aws:SourceAccount / aws:SourceArn, or sts:ExternalId for cross-account) to prevent confused-deputy assumption — per Redshift IAM-role authorization best practices.
• Logging & monitoring. Enable CloudTrail (S3 data events on the staging bucket + Redshift management events), Redshift audit logging (connection/user-activity logs to S3 or CloudWatch), and CloudWatch alarms on COPY failures or unusual staging-bucket access during the migration.

The AWS MCP server (recommended runtime) additionally provides sandboxed execution and audit logging for the generated scripts.

References (specialized knowledge)