AWS Data Analytics

View saved version →

---
name: amazon-opensearch-service
description: Guides migration, provisioning, search, log-analytics, trace-analytics, and Agentic AI Assistant workflows for Amazon OpenSearch Service and Serverless across six capabilities — migration (Solr/ES/self-managed into AOS/AOSS, schema/query translation, sizing, cutover); provisioning (domain + AOSS lifecycle, upgrades, FGAC, monitoring); search (vector / semantic / hybrid / RAG with Bedrock); log-analytics (PPL, OSI, anomaly detection, Dashboards); trace-analytics (OTel spans, service maps, Data Prepper); ai-assistant (natural language data exploration, incident investigation, root cause analysis). Triggers on OpenSearch, AOS, AOSS, Elasticsearch, Solr, vector/k-NN/semantic/hybrid search, RAG, log analytics, PPL, trace analytics, ISM, FAISS, HNSW, Migration Assistant, UltraWarm, OR1, query my data, analyze logs, investigate errors, root cause analysis.
metadata:
  version: "2"
---

# Amazon OpenSearch Service — the unified skill

This skill answers anything about Amazon OpenSearch Service or Serverless across six capabilities. **Step 0 below routes the question to ONE capability** and points at that capability's entry-point reference. Everything else — when to dispatch, sub-references, capability-specific facts, cross-capability links — lives in the entry-point reference for that capability.

> **AWS MCP server is recommended, not required.** Capability references show standard AWS CLI commands as the primary syntax (e.g., `aws opensearch describe-domain`, `aws opensearchserverless create-collection`). Where the AWS MCP server is available, its `call_aws` tool offers a streamlined alternative — but every operation in this skill MUST work via the AWS CLI alone. Data-plane HTTP calls against AOS / AOSS use `awscurl` for SigV4-signed requests; this works in both contexts.

## Step 0: detect the capability — first thing you do

Pick **one** of the six capabilities below. State the detected capability in your first sentence (e.g., *"Detected capability: SEARCH — semantic search setup with Bedrock embeddings."*). Then load the entry-point reference; that file describes when to dispatch, indexes the rest of the capability's files, and routes you to the next step.

| Capability | Entry-point reference |
|---|---|
| **migration** — Solr / Elasticsearch / self-managed OpenSearch into AOS or AOSS. Schema/query translation, sizing, cutover. | [`references/assessment-workflow.md`](references/assessment-workflow.md) |
| **provisioning** — Provisioning and managing AOS domains and AOSS collections. Lifecycle, upgrades, storage tiers, FGAC, monitoring. | [`references/provisioning-reference.md`](references/provisioning-reference.md) |
| **search** — Vector / semantic / hybrid / sparse / dense / RAG retrieval. Bedrock connectors, FAISS HNSW vs Lucene. | [`references/search-semantic-search-guide.md`](references/search-semantic-search-guide.md) |
| **log-analytics** — Log search, observability, PPL, OSI ingestion, anomaly detection, OpenSearch Dashboards. Splunk/Datadog/ELK alternatives. | [`references/log-analytics-guide.md`](references/log-analytics-guide.md) |
| **trace-analytics** — Distributed traces with OpenTelemetry. Span queries, service maps, Data Prepper. | [`references/trace-analytics-trace-queries.md`](references/trace-analytics-trace-queries.md) |
| **ai-assistant** — Agentic AI Assistant: auto-discovers indices, generates optimized PPL/DSL queries, summarizes results, and investigates incidents end-to-end. No manual query crafting needed. | [`references/ai-assistant.md`](references/ai-assistant.md) |

If a prompt spans capabilities (e.g., *"migrate from Solr AND set up RAG on the new domain"*), pick the dominant capability for the response and close with a one-line handoff to the other capability's entry-point ref.

## Universal rules (apply to ALL capabilities)

These rules apply to every response, regardless of capability. Capability-specific rules (sizing math, shape detection, Migra

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 55 — file references are metadata, not package contents.

View saved version →

---
name: connecting-to-data-source
description: >-
  Create and troubleshoot AWS Glue connections to JDBC databases (Oracle, SQL Server,
  PostgreSQL, MySQL, RDS), Redshift, Snowflake, and BigQuery. Gathers connection hints
  from user, discovers existing connections and RDS/Redshift candidates, registers
  credentials in Secrets Manager or IAM DB auth, configures VPC, and tests. Triggers
  on: connect to database, set up Glue connection, register data source, connect to
  Snowflake/BigQuery/RDS, connection timeout, test connection, troubleshoot connection.
  Do NOT use for moving data (use ingesting-into-data-lake), creating tables (use
  creating-data-lake-table), queries (use querying-data-lake), catalog exploration
  (use exploring-data-catalog), or SaaS (Salesforce, ServiceNow, SAP, MongoDB, Kafka).
metadata:
  version: "1"
  argument-hint: "'[source-type|connection-name|hostname]'"
---

# Connect to Data Source

Register an external data source with AWS Glue so downstream skills (ingesting-into-data-lake) can move data from it. A Glue connection stores the network config, driver, and credential reference for one source. Create once per source, reuse across jobs.

## Philosophy

**A connection is a named pipe, not a pipeline.** This skill produces a tested, reusable Glue connection. It does not move data.

## Common Tasks

You MUST execute commands using AWS MCP server tools when connected -- they provide validation, sandboxed execution, and audit logging. Fall back to AWS CLI only if MCP is unavailable. You MUST explain each step before executing.

## Workflow

### 1. Verify Dependencies and Context

- You MUST check whether AWS MCP tools or AWS CLI are available and inform the user if missing
- You MUST confirm target AWS region and verify credentials with `aws sts get-caller-identity`

### 2. Classify the Source

Ask the user which source type they want to connect to, or infer from hints:

| User says... | Source type | Connection type | Reference |
|---|---|---|---|
| "Oracle", "SQL Server", "Postgres", "MySQL", "RDS \<engine\>" | JDBC database | `JDBC` | [jdbc-setup.md](references/jdbc-setup.md) |
| "Redshift", "my cluster", "my data warehouse on AWS" | Redshift | `JDBC` | [jdbc-setup.md](references/jdbc-setup.md) (Redshift section) |
| "Snowflake" | Snowflake | `SNOWFLAKE` | [snowflake-setup.md](references/snowflake-setup.md) |
| "BigQuery", "Google analytics warehouse" | BigQuery | `BIGQUERY` | [bigquery-setup.md](references/bigquery-setup.md) |

If the user names DynamoDB or a local file, stop and tell them: DynamoDB is read directly by Glue without a connection, and local files belong in the ingesting-into-data-lake skill's local-upload workflow.

### 3. Gather Connection Hints from the User

You MUST ask for hints the user can provide -- do not guess.

**For all sources:**

- Desired connection name (lowercase, hyphens: `oracle-prod-sales`, `snowflake-analytics`)
- Existing Secrets Manager secret, or create one
- Is source reachable from a Glue VPC (same, peered, VPN, Direct Connect)

**JDBC:** hostname/endpoint, port, database, whether RDS/Aurora/self-managed, IAM DB auth enabled (Aurora/RDS MySQL/Postgres), SSL required.

**Snowflake:** account identifier, warehouse, role, default database, auth (password, key-pair, OAuth).

**BigQuery:** GCP project ID, location, whether service account JSON is provisioned.

### 4. Discover Existing Connections and Candidate Sources

Check what exists before creating.

**Existing Glue connections:**

```bash
aws glue get-connections --filter ConnectionType=<TYPE> --region <REGION>
```

If a suitable one exists, confirm and skip to Step 7.

**Candidate sources in account** (JDBC/Redshift only):

- RDS: `aws rds describe-db-instances`
- Aurora: `aws rds describe-db-clusters`
- Redshift: `aws redshift describe-clusters`

Present candidates to user; let them pick. See [discovery.md](references/discovery.md).

### 5. Register Credentials

You MUST encourage AWS Secrets Manager ov

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 7 — file references are metadata, not package contents.

View saved version →

---
name: creating-data-lake-table
description: >-
  Create managed Iceberg tables using Amazon S3 Tables (s3tables API namespace) with
  automatic compaction and snapshot management. Sets up table bucket, namespace, table,
  schema, Glue catalog registration, partitioning, IAM access control. Triggers on:
  create table, data lake table, analytics table, structured data storage, S3 Tables,
  Iceberg, Athena table, partitioning strategy, access permissions. Do NOT use  for:
  importing files (use ingesting-into-data-lake), vector storage (use storing-and-querying-vectors),
  querying existing tables (use querying-data-lake), or locating existing table (use
  finding-data-lake-assets).
metadata:
  version: "1"
  argument-hint: "'[table-description|schema-spec]'"
---

# Create Data Lake Tables with Amazon S3 Tables

## Overview

Amazon S3 Tables provides managed Iceberg tables with automatic compaction and snapshot management. Queryable via Athena and Iceberg-compatible engines.

## Common Tasks

You MUST use AWS MCP server tools when connected, they provide command validation, sandboxed execution, and audit logging. Fall back to AWS CLI if MCP unavailable.

## Decision Guide

**Before creating, You MUST check what exists:**

You MUST run `aws glue get-tables --database-name <NAME>` when user mentions a database.

| What you find | Action |
|---------------|--------|
| Fuzzy database name ("our analytics db") | You MUST STOP. Delegate to `finding-data-lake-assets` to resolve. |
| Non-S3-Tables table with matching name | You MUST STOP. Delegate to `finding-data-lake-assets`. You MUST NOT create until user confirms. |
| Existing S3 Tables table with matching name | You MUST check schema match. Reuse if compatible, recreate only if user confirms. |
| No matching tables | Proceed with creation (Steps 1-8). |
| User explicitly requests new S3 Tables table | Skip checks, proceed with creation. |

**Creation paths:**

- **Existing data in S3**: Create empty table (Steps 1-8), then use `ingesting-into-data-lake` skill.
- **Glue ETL pipeline**: Read `references/table-creation-glue-etl.md` first, then Steps 1-6.
- **Lake Formation access control**: Search AWS docs for `"S3 Tables integration with Lake Formation"`.

### 1. Verify Dependencies

**Constraints:**

- You MUST check whether AWS MCP server tools or AWS CLI are available and inform user if missing
- You MUST confirm target AWS region and verify credentials with `aws sts get-caller-identity`

### 2. Understand the Schema

- **Explicit schema**: Validate Iceberg types.
- **Loose description**: Ask columns, types, grain. Propose and confirm.
- **Existing S3 data**: Infer schema from file headers only. Create empty table first, then use `ingesting-into-data-lake` skill.

**Constraints:**

- You MUST read `references/best-practices.md` for Iceberg type mapping, partitions, and naming.
- You MUST ask for all required parameters upfront: table name, columns, types, partition strategy. For schema evolution, see `references/athena-ddl-path.md`.
- You MUST use all lowercase names -- Glue rejects mixed case with `GENERIC_INTERNAL_ERROR`. Namespace and table names MUST NOT contain hyphens.
- You SHOULD suggest partition columns based on access patterns.

### 3. Create Table Bucket

Names: 3-63 chars, lowercase, numbers, hyphens.

```bash
aws s3tables create-table-bucket --name <BUCKET_NAME> --region <REGION>
```

Capture `table-bucket-arn`. Encryption (SSE-S3 default, SSE-KMS) and storage class (STANDARD, INTELLIGENT_TIERING) set at creation. See `references/best-practices.md`.

**Constraints:**

- You MUST check existing buckets with `aws s3tables list-table-buckets` and ask user to select or create new.
- If using SSE-KMS, KMS key policy MUST allow S3 Tables maintenance service principal to read data. Search AWS docs for `"S3 Tables KMS key policy"` for required policy.
- If bucket creation fails, see `references/best-practices.md` for common errors.

### 4. Create Namespace

```bash
aws s

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 4 — file references are metadata, not package contents.

View saved version →

---
name: exploring-data-catalog
description: >-
  Full inventory and audit of AWS Glue Data Catalog assets across S3 Tables, Redshift-federated,
  and remote Iceberg catalogs. Triggers on: inventory the catalog, audit databases,
  list all tables, catalog overview, data landscape, enumerate catalogs, data inventory,
  search the catalog. Do NOT use for finding specific data (use finding-data-lake-assets),
  running queries (use querying-data-lake), or creating tables (use creating-data-lake-table).
metadata:
  version: "2"
  argument-hint: "'[search-term|catalog-name|database-name|s3://bucket-path|table-name]'"
---

Structured inventory and cataloging across your AWS data landscape: Glue Data Catalog with S3 Tables, Redshift-federated, and remote Iceberg catalogs.

## Overview

Maps data in an AWS account. Starts with catalog landscape (Glue, S3 Tables, federated), then drills into databases and tables. Read-only — no query execution.

**Constraints for parameter acquisition:**

- You MUST ask for the target AWS region upfront if not provided
- You MUST support a single optional argument: search term, catalog name, database name, S3 path, or table name
- You MUST accept the argument as direct input or a pointer to a file containing the spec
- You MUST confirm the scope (full landscape vs. targeted deep dive) before making API calls
- You MUST respect the user's decision to abort at any step

## Common Tasks

**Pagination:** All list and search calls in this workflow may return paginated results. You MUST pass `--next-token` from the previous response until no more tokens are returned. You MUST NOT assume a single page contains all results.

### 1. Verify Dependencies

Check for required tools and AWS access before discovery.

**Constraints:**

- You MUST verify AWS MCP server tools are available (`aws___call_aws`, `aws___search_documentation`) and fall back to AWS CLI if not
- You MUST confirm credentials are valid: `aws sts get-caller-identity`
- You MUST inform the user about any missing tools and ask whether to proceed

### 2. Consult Catalog Context (experimental — suggested first lookup)

Customers may publish context assets that describe the data landscape (canonical
names, domains, ownership) faster than a full enumeration.

These are the **Glue Discovery** operations (`SearchAssets` / `GetAsset` /
`ListIterableForms` / `BatchGetIterableForms`) — a distinct metadata-search surface,
NOT the legacy `glue search-tables`. They are **experimental** — not available in every
CLI build. Gate the
lookup on two checks first:

1. **Availability.** Confirm the `GetAsset` operation exists in the caller's Glue
   CLI model (redirect output so the CLI pager cannot block a non-interactive agent):

   ```
   aws glue get-asset help > /dev/null 2>&1
   # exit 0 = available. exit 2 (with "Invalid choice" in stderr) = not in this CLI (skip).
   # any other non-zero (network/credential error) = inconclusive; treat as unavailable.
   ```

   If it is not available, skip this step and go to full discovery (Steps 3-5).
2. **User opt-in.** If available, ask the user: "I can consult the Glue Data Catalog
   for customer-authored context using an experimental SearchAssets/GetAsset API.
   Use it? (yes/no)". Proceed only on an explicit yes; otherwise skip to Steps 3-5.

**How this model differs:** Discovery indexes **assets** (not databases/tables). Each
asset's `Id` is an **ARN**, and `get-asset` / `list-iterable-forms` key off it via the
identifier — there is no `--database-name`. CLI flags are kebab-case; top-level response fields are PascalCase. NOTE: a `*.Content` value is itself a JSON STRING with its own camelCase schema (e.g. `dataLocation`, `dataFormat`, `isPartitionKey`) — parse it as embedded JSON. The operations:

| Operation | Input → Output |
|---|---|
| `search-assets` | `--search-text` (+ optional `--filter-clause`) → `Items[]` of `{Id, AssetName, Type, Namespace, AssetTypeId, UpdatedAt}` (search items have NO description — call `

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: finding-data-lake-assets
description: >-
  Resolve data lake and lakehouse asset references across Glue Data Catalog, S3, S3
  Tables, and Redshift. Triggers on: find the table, where is our data, which table
  has, locate dataset, find data for, search catalog, what tables match, Redshift
  table, lakehouse table, data lake table, warehouse table, reverse lookup S3 path.
  Do NOT use for: full catalog audits (use exploring-data-catalog), running queries
  (use querying-data-lake), creating tables (use creating-data-lake-table).
metadata:
  version: "2"
  argument-hint: "'[table-name|keyword|column-name|s3://path]'"
---

# Find Data Lake Assets

## Overview

Resolves data lake asset references to concrete catalog entries. Acts as a
resolver for other skills and direct user requests. Covers Glue,
S3, S3 Tables, and Redshift. Optimized for low token usage — return the
answer fast and get out of the way.

**Constraints for parameter acquisition:**

- You MUST accept a single argument: table name, keyword, column name, or S3 path
- You MUST accept the argument as direct input or a pointer to a file containing the spec
- You MUST ask for the target AWS region if not already set
- You MUST confirm ambiguous input before searching (e.g., "Did you mean table X or bucket Y?")
- You MUST respect the user's decision to abort at any step

## Common Tasks

You MUST execute commands using AWS MCP server tools when connected — they
provide validation, sandboxed execution, and audit logging. Fall back to
AWS CLI only if MCP is unavailable. You MUST explain each step before
executing.

### 1. Verify Dependencies

Check for required tools and AWS access before searching.

**Constraints:**

- You MUST verify AWS MCP server tools (`aws___call_aws`) are available; fall back to AWS CLI if not
- You MUST confirm credentials with `aws sts get-caller-identity`
- You MUST inform the user about any missing tools and ask whether to proceed

### 2. Consult Catalog Context (experimental — suggested first lookup)

The customer may publish **context skill assets** in the Glue Data Catalog that map
their business language to the real tables — canonical names and aliases, join keys,
metrics, usage notes, descriptions — that the raw schema does not carry. When present,
this catalog is often enough to answer the request on its own.

These are the **Glue Discovery** operations (`SearchAssets` / `GetAsset` /
`ListIterableForms` / `BatchGetIterableForms`) — a distinct metadata-search surface,
NOT the legacy `glue search-tables` used in Step 5. They are **experimental** — not
available in every CLI build. Gate the lookup on two checks first:

1. **Availability.** Confirm the `GetAsset` operation exists in the caller's Glue
   CLI model (redirect output so the CLI pager cannot block a non-interactive agent):

   ```
   aws glue get-asset help > /dev/null 2>&1
   # exit 0 = available. exit 2 (with "Invalid choice" in stderr) = not in this CLI (skip).
   # any other non-zero (network/credential error) = inconclusive; treat as unavailable.
   ```

   If it is not available, skip this step and go to the normal search workflow (Steps 3-7).
2. **User opt-in.** If available, ask the user: "I can check the Glue Data Catalog
   for customer-authored context using an experimental SearchAssets/GetAsset API.
   Use it? (yes/no)". Proceed only on an explicit yes; otherwise skip to Steps 3-7.

**How this model differs:** Discovery indexes **assets** (not databases/tables). Every
asset has an `Id` that is an **ARN**, and every lookup after `SearchAssets` keys off that ARN
via the identifier — there is no `--database-name`/`--table-name`. CLI flags are kebab-case
(`--search-text`, `--max-results`, `--filter-clause`); top-level response fields are PascalCase
(`Id`, `AssetName`, `Forms`). NOTE: a `*.Content` value is itself a JSON STRING with its own
camelCase schema (e.g. `dataLocation`, `dataFormat`, `isPartitionKey`) — parse it as embedded JSON,
do not expect PascalCase i

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 1 — file references are metadata, not package contents.

View saved version →

---
name: ingesting-into-data-lake
description: >-
  Import data into the AWS data lake from S3 files, local uploads, JDBC databases
  (Oracle, SQL Server, PostgreSQL, MySQL, RDS, Aurora), Amazon Redshift, Snowflake,
  BigQuery, DynamoDB, or existing Glue catalog tables (migration). Default target
  is S3 Tables; standard Iceberg on a general purpose bucket is supported where S3
  Tables is not adopted. Handles one-time loads, recurring pipelines, migrations.
  Triggers on: import data, load data, ingest, sync database, migrate table, move
  data to AWS, set up pipeline, ETL, pull from Snowflake, query BigQuery into S3,
  export DynamoDB, CTAS, convert to Iceberg. Do NOT use for setting up or troubleshooting
  Glue connections (use connecting-to-data-source), creating empty tables (use creating-data-lake-table),
  running queries (use querying-data-lake), finding tables by fuzzy name (use finding-data-lake-assets),
  catalog audit (use exploring-data-catalog), or SaaS platforms like Salesforce, ServiceNow,
  SAP, MongoDB, Kafka.
metadata:
  version: "1"
  argument-hint: "'[source-path|connection-name|table-name] [--target s3-tables|iceberg|parquet]'"
---

# Ingest into Data Lake

Move data from a source into a queryable table in the data lake. This skill assumes the source connection (if one is needed) already exists. For Glue connection setup or troubleshooting, delegate to `connecting-to-data-source`.

## Philosophy

**Default to S3 Tables unless the environment says otherwise.** S3 Tables is the recommended target for new data lake work. If the user's catalog inventory shows they haven't adopted S3 Tables, recommend standard Iceberg on their existing general-purpose bucket instead of forcing them to change posture.

## Common Tasks

You MUST execute commands using AWS MCP server tools when connected -- they provide validation, sandboxed execution, and audit logging. Fall back to AWS CLI only if MCP is unavailable. You MUST explain each step before executing.

## Workflow

### 1. Verify Dependencies and Context

- You MUST check whether AWS MCP tools or AWS CLI are available and inform the user if missing
- You MUST confirm target AWS region and verify credentials with `aws sts get-caller-identity`
- For SageMaker Unified Studio project roles, note that target tables and connections may be scoped to the project. See the caller ARN detection pattern in `querying-data-lake`.

### 2. Classify the Source

| User says... | Source type | Reference |
|---|---|---|
| "upload my file", "local CSV", "move to S3" | Local file | [local-upload.md](references/local-upload.md) |
| "load from S3", "import CSV/JSON/Parquet from s3://" | S3 files | [s3-files.md](references/s3-files.md) |
| "import from Oracle/Postgres/MySQL/SQL Server/Redshift/RDS/Aurora" | JDBC | [jdbc-ingest.md](references/jdbc-ingest.md) |
| "pull from Snowflake", "Snowflake table to S3" | Snowflake | [snowflake-ingest.md](references/snowflake-ingest.md) |
| "import from BigQuery", "GCP analytics to S3" | BigQuery | [bigquery-ingest.md](references/bigquery-ingest.md) |
| "export DynamoDB", "DynamoDB to data lake" | DynamoDB | [dynamodb-ingest.md](references/dynamodb-ingest.md) |
| "migrate Glue table", "convert Hive to Iceberg" | Catalog migration | [catalog-migration.md](references/catalog-migration.md) |

If the user names Salesforce, ServiceNow, SAP, MongoDB, Kafka, or another SaaS/streaming source, decline -- these are not supported in this release.

If the source table is referenced by a fuzzy or business name ("migrate our orders table", "pull from the sales warehouse"), delegate to `finding-data-lake-assets` to resolve before proceeding.

### 3. Confirm Connection Exists (if applicable)

For JDBC, Snowflake, and BigQuery sources, a Glue connection is required. Check:

```bash
aws glue get-connection --name <CONNECTION_NAME> --region <REGION>
```

If the connection does not exist, stop and delegate to `connecting-to-data-source` to create and test it. Do not proceed 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 25 — file references are metadata, not package contents.

View saved version →

---
name: querying-data-lake
description: >-
  Execute and manage Athena SQL queries across default and federated catalogs (Glue,
  S3 Tables, Redshift). Triggers on phrases like: query data, run SQL, athena query,
  analyze table, SQL query, workgroup status, profile table, query Redshift catalog,
  query S3 Tables. Do NOT use for finding specific data assets (use finding-data-lake-assets),
  full catalog audits (use exploring-data-catalog), importing data (use ingesting-into-data-lake).
metadata:
  version: "1"
  argument-hint: "'[SQL-query|query-name|workgroup-name|catalog-name|''profile TABLE_NAME'']'"
---

# Query Data Lake

Execute SQL queries on Amazon Athena across default and federated catalogs (Glue, S3 Tables, Redshift) with workgroup selection, statement classification, and error recovery.

## Overview

Executes and manages Athena SQL queries across default and federated catalogs. Selects a workgroup, resolves target assets (delegating fuzzy references to `finding-data-lake-assets`), classifies statements for safety, and reports cost and data scanned. Use the AWS MCP server for sandboxed execution and audit logging; the same AWS CLI commands work directly when the MCP server is not available.

**Constraints for parameter acquisition:**

- You MUST accept a single optional argument: SQL text, a named-query name, a workgroup name, a catalog name, or `profile TABLE_NAME`
- You MUST accept the argument as direct text or a pointer to a file containing SQL
- You MUST ask the user for the target AWS region if not already set
- You MUST confirm the output S3 location before executing any non-trivial query
- You MUST respect the user's decision to abort at any step

## Common Tasks

### 1. Verify Dependencies

Check for required tools and AWS access before running queries.

**Constraints:**

- You MUST verify AWS MCP server tools are available (`aws___call_aws`) and run queries through them when present; fall back to AWS CLI only if the MCP server is unavailable
- You MUST NOT fall back to shell or Bash for query execution — results must be captured via the MCP tool or `aws athena` CLI so output location and cost are tracked
- You MUST confirm credentials with `aws sts get-caller-identity` and inform the user about any missing tools

### 2. Resolve Workgroup

Check caller identity, list workgroups, auto-select the best one (see [workgroup-selection.md](references/workgroup-selection.md)).

**Constraints:**

- You MUST select a workgroup before submitting any query (prevents output-location errors)
- You MUST present the selected workgroup and its output location to the user
- You MUST NOT auto-escalate to a different workgroup on failure without user confirmation

### 3. Resolve the Target Asset

If the user refers to a table by name, by business concept ("our quarterly report", "the sales data"), by S3 path, or by catalog without specifying the table, delegate to `finding-data-lake-assets` to return the concrete `database.table` (and catalog if non-default).

**Constraints:**

- You MUST NOT attempt to resolve fuzzy asset references with `athena list-data-catalogs` or by iterating `get-tables` — those miss federated catalogs and waste tokens
- You SHOULD skip this step only when the user provides a fully-qualified reference (exact `database.table`) or raw SQL they want executed as-is
- You MUST state the resolved asset explicitly before building the query: "Found [table] in [catalog]. Using this for the query."
- You SHOULD default to the default Glue catalog unless the user mentions "federated", "Redshift", "S3 Tables", or `finding-data-lake-assets` returns a different catalog

### 4. Discover Schema

For analytical queries, You SHOULD profile the target table before building the final query. You MUST show sample rows (`SELECT ... LIMIT 5`) as part of profiling.

### 5. Build Query

Table addressing depends on catalog type:

- Default Glue catalog: `database.table` (omit the catalog prefix for single-catalog queries). In 

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.

View saved version →

---
name: redshift-guide
description: "Amazon Redshift is NOT PostgreSQL — corrects PostgreSQL-derived LLM mistakes; covers Redshift-specific SQL, DDL, COPY/UNLOAD, system views, metadata discovery, and operational patterns. Applies ONLY when the task is about Redshift itself (cluster, Serverless workgroup, or Redshift SQL). Pushes back on: CREATE INDEX, string_agg, pg_catalog, text type, SERIAL, stl_query, LATERAL, RETURNING. Triggers on: Redshift SQL, Redshift CREATE TABLE, Redshift COPY/UNLOAD, slow Redshift query, Redshift permission denied, Redshift disk full, Redshift system views, QUALIFY, PIVOT, MERGE, Redshift Data API, Redshift WLM, concurrency scaling, Redshift resize, Redshift Spectrum external tables. Does NOT apply to (defer to that service's own skill): Amazon S3 storage/bucket policies, Athena or Glue queries/catalogs, data-lake or Iceberg work outside Redshift, Aurora, RDS, or DynamoDB — but S3/Glue ARE in scope for Redshift COPY, UNLOAD, or data-lake queries (external schemas/tables on S3)."
metadata:
  version: "1"
---

# Amazon Redshift Guide

## Redshift is NOT PostgreSQL (read first)

Redshift speaks PostgreSQL's wire protocol and shares much of its surface syntax, so
LLMs assume PostgreSQL behavior carries over — it frequently does not. Divergences span
system tables (`pg_catalog` is incomplete), DDL (no indexes, no sequences), functions
(`string_agg`, `SUBSTR` on tables, leader-node-only functions), types (a `text` column
becomes VARCHAR(256)), and comparison semantics (trailing blanks, unenforced constraints). **Assume
divergence and verify against the reference below — do not answer from PostgreSQL habit.**
Common PostgreSQL→Redshift divergences are in `references/redshift-sql-syntax.md`.

**Works best with** the [AWS MCP server](https://docs.aws.amazon.com/aws-mcp/) — it runs the
AWS CLI and Redshift Data API calls below in a sandboxed, audit-logged environment. All
guidance here is plain AWS CLI and SQL and works without it.

## STEP 0: Serverless or Provisioned?

Establish this before answering — APIs, system tables, and capabilities differ. Take it
from the question when it says which one; **ask** when it does not. `SELECT version()`
does not identify it.

- **Serverless** — identified by a *workgroup* (and namespace). Data API calls take
  `--workgroup-name`; the user says "workgroup"/"Serverless".
- **Provisioned** — identified by a *cluster*. Data API calls take
  `--cluster-identifier`; the user says "cluster".

| Target | System Views | Credentials API |
|---|---|---|
| **Provisioned** | `SYS_`, all `SVV_` + `STL_`, `STV_`, `SVL_`, `SVCS_` (single-AZ only — disabled on Multi-AZ) | `redshift:GetClusterCredentials` |
| **Serverless** | `SYS_` + a subset of `SVV_` ONLY (no `STL`/`STV`/`SVL`/`SVCS`) | `redshift-serverless:GetCredentials` |

## Critical Facts

- **SHOW commands are the primary metadata interface** — SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS, SHOW TABLE, SHOW VIEW. Do NOT default to pg_catalog or information_schema. → **Load `references/redshift-sql-metadata.md` for metadata/discovery questions and any "relation does not exist" report** — it has the diagnostic flow.
- **`SYS_` views are the preferred system views** — they work everywhere. `STL_`, `STV_`, `SVL_`, and `SVCS_` are provisioned single-AZ only, and some `SVV_` views are unsupported on Serverless. → **Load `references/redshift-sql-metadata.md` for any system-view or monitoring question.**
- **`sys_load_error_detail`** for COPY debugging (not `stl_load_errors`, which is provisioned single-AZ only).
- **DATEADD/DATEDIFF** — unit-first argument order: `DATEADD(day, -30, GETDATE())`, `DATEDIFF(day, start, end)`.
- **APPROXIMATE COUNT(DISTINCT col)** — Redshift-specific, ~2% error, much faster than exact COUNT(DISTINCT) on large datasets.
- **MERGE ... REMOVE DUPLICATES** — simplified dedup when source and target have identical schemas.
- **COPY should use IAM_ROLE** (the namespace role, not the caller role) + su

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 7 — file references are metadata, not package contents.

View saved version →

---
name: storing-and-querying-vectors
description: >-
  Store and query vector embeddings using Amazon S3 Vectors, a cost-effective long-term
  vector storage service with its own API namespace (s3vectors). Triggers on: create
  S3 vector bucket, vector index, store embeddings, semantic search, RAG vector storage,
  similarity search, vector database, migrate from other vector databases. Do NOT
  use for: querying tabular data (use querying-data-lake), S3 object storage, or hundreds/thousands
  of sustained QPS (use OpenSearch).
metadata:
  version: "1"
---

# Store and Query Vectors with Amazon S3 Vectors

## Overview

Amazon S3 Vectors is a cost-effective AWS service for storing and querying vector embeddings at scale. Optimized for long-term storage with subsecond latency for cold queries, as low as 100ms for warm queries.

## Decision Guide

- **Hundreds/thousands of sustained queries per second (QPS)**: Wrong tool. Recommend OpenSearch.
- **Hybrid search, aggregations, faceted search**: Recommend OpenSearch with S3 Vectors as storage engine. For OpenSearch integration, search AWS docs for `"Using S3 Vectors with OpenSearch Service"`.
- **Tiered (bulk + hot)**: S3 Vectors for storage + OpenSearch Serverless for real-time. See `references/limits-and-patterns.md`.
- **Cost-effective storage, infrequent queries, RAG**: S3 Vectors is the right fit. Proceed.

For latest guidance, search AWS docs for `"S3 Vectors best practices"`.

## Common Tasks

Classify the request before starting:

- **Simple query**: Existing index, skip to Step 6
- **Standard**: You MUST list existing indexes first and suggest reusing if relevant. Else, new index + store vectors, follow Steps 2-6
- **Migration or multi-tenant**: Read `references/limits-and-patterns.md` first, then Steps 2-6

You MUST execute commands using AWS MCP server tools when connected. Fall back to AWS CLI only if AWS MCP is unavailable. You MUST explain each step to the user before executing.

### 1. Verify Dependencies

**Constraints:**

- You MUST check whether AWS MCP tools or AWS CLI is available and inform user if missing
- You MUST confirm target AWS region

### 2. Create a Vector Bucket

You MUST confirm bucket name with user. Names: 3-63 chars, lowercase letters, numbers, hyphens only. Encryption (SSE-S3 default or SSE-KMS for compliance) is immutable after creation.

```bash
aws s3vectors create-vector-bucket \
  --vector-bucket-name <BUCKET_NAME>
```

**Constraints:**

- You MUST explain encryption cannot be changed after creation
- For SSE-KMS, KMS key policy MUST grant `kms:GenerateDataKey` and `kms:Decrypt` to the S3 Vectors service principal `indexing.s3vectors.amazonaws.com`. You MUST use full KMS key ARN (not alias). See `references/limits-and-patterns.md` for command example.

### 3. Create a Vector Index

Every parameter is **immutable after creation**.

**Pre-flight checklist (confirm ALL with user):**

1. **Dimension** (required, integer 1-4096) -- MUST match embedding model output
2. **Distance metric** (required) -- `cosine` or `euclidean`. Use embedding model's recommended metric;
3. **Non-filterable metadata keys** (optional, max 10, 1-63 chars) -- Declare at creation or lose forever. For Bedrock Knowledge Bases integration, search AWS docs for `"S3 Vectors Bedrock Knowledge Bases prerequisites"` to get the required key names.
4. **Encryption** (optional) -- Inherits from bucket. Override per-index if needed.

```bash
aws s3vectors create-index \
  --vector-bucket-name <BUCKET_NAME> \
  --index-name <INDEX_NAME> \
  --dimension <DIM> \
  --distance-metric <cosine|euclidean> \
  --data-type float32 \
  --metadata-configuration '{"nonFilterableMetadataKeys":["<KEY1>","<KEY2>"]}'
```

Omit `--metadata-configuration` if no non-filterable keys are needed.

Index names: 3-63 chars, lowercase, numbers, hyphens, dots. Unique within bucket. Filterable metadata: 2 KB limit. Total metadata (filterable + non-filterable combined): 40 KB. See `references/metadata-filter

Preview of the first 4,000 characters. Read full instructions →

Referenced files: 2 — file references are metadata, not package contents.