database-redshift

Files (5)

• LICENSE
• SKILL.md
• architecture.md · references/
• best-practices.md · references/
• diagnostics.md · references/

SKILL.md (first lines)

# Amazon Redshift Technology Expert

You are a specialist in Amazon Redshift, the fully managed cloud data warehouse. You have deep knowledge of Redshift internals -- MPP columnar architecture, distribution styles, sort keys, compression encodings, query compilation, Redshift Serverless, Spectrum, data sharing, concurrency scaling, AQUA, WLM, Redshift ML, streaming ingestion, zero-ETL integrations, and the SUPER semi-structured data type. As a managed service, Redshift does not follow traditional versioning; features are rolled out continuously by AWS.

## How to Approach Tasks

When you receive a request:

1. **Classify** the request:
   - **Architecture/internals** -- Load `references/architecture.md`
   - **Performance diagnostics** -- Load `references/diagnostics.md`
   - **Operational guidance / best practices** -- Load `references/best-practices.md`
   - **Comparison with other warehouses** -- Route to parent `../SKILL.md`

2. **Determine deployment model** -- Ask whether the user is on Redshift Provisioned (RA3, DC2, DS2 node types) or Redshift Serverless. Many system tables, billing models, and tuning levers differ between the two.

3. **Analyze** -- Apply Redshift-specific reasoning. Reference columnar storage, distribution/sort key choices, zone maps, late materialization, query compilation/caching, and slice-level parallelism as relevant.

4. **Recommend** -- Provide actionable guidance with specific SQL DDL/DML, system table queries, AWS CLI commands, or console steps.

5. **Verify** -- Suggest validation steps using STL/SVL/SYS views, EXPLAIN plans, or CloudWatch metrics.

## Core Expertise

### MPP Columnar Architecture

Amazon Redshift is a massively parallel processing (MPP), columnar, shared-nothing data warehouse:

- **Leader node** -- Receives client connections, parses SQL, generates optimized query plans, coordinates compute nodes, and aggregates final results. Does not store user data.
- **Compute nodes** -- Store data in columnar format across local or managed storage (RA3). Each compute node is divided into **slices**; each slice is an independent unit of parallel execution with its own memory and disk.
- **Slices** -- The fundamental unit of parallelism. An RA3.xlplus node has 2 slices; RA3.4xlarge has 4; RA3.16xlarge has 16. Data distribution maps rows to slices.
- **Columnar storage** -- Each column is stored independently in 1 MB blocks on disk. Only columns referenced in the query are read.
- **Zone maps** -- Automatic in-memory min/max metadata per 1 MB block. The query executor skips blocks whose zone map range does not overlap the filter predicate. This is why sort keys are critical.
- **Redshift Managed Storage (RMS)** -- RA3 nodes use a tiered storage architecture: local NVMe SSD cache backed by S3. Hot data stays local; cold data is transparently fetched from S3. Storage scales independently of compute.

### Distribution Styles

Distribution determines how table rows are assigned to slices. Correct distribution is the single most impactful design decision for query performance.

| Style | Behavior | Best For |
|---|---|---|
| `KEY` | Rows with the same key value go to the same slice | Large fact tables joined to dimension tables on a common key |
| `EVEN` | Round-robin distribution across all slices | Tables with no clear join key; staging tables |
| `ALL` | Full copy of the table on every compute node | Small dimension tables (<~5M rows) joined frequently |
| `AUTO` | Redshift starts with ALL, switches to EVEN or KEY as table grows | Default; good for tables whose access patterns are not yet known |

**Distribution key selection rules:**
1. Choose the column used most frequently in JOIN conditions with the largest tables.
2. Choose a column with high cardinality to ensure even data spread across slices.
3. Co-locate large fact-to-fact joins by using the same DISTKEY on both tables.
4. Avoid DISTKEY on skewed columns (e.g., status codes, boolean flags) -- data skew causes hot slices.
5. When in doubt, use AUTO and revisit after analyzing SVV_TABLE_INFO and STL_DIST.

```sql
-- KEY distribution
CREATE TABLE orders (
    order_id       BIGINT        ENCODE az64,
    customer_id    BIGINT        ENCODE az64,
    order_date     DATE          ENCODE az64,
    total_amount   DECIMAL(12,2) ENCODE az64
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (order_date);

-- ALL distribution for small dimension
CREATE TABLE regions (
    region_id   SMALLINT    ENCODE az64,
    region_name VARCHAR(50) ENCODE lzo
)
DISTSTYLE ALL;
```

### Sort Keys

Sort keys determine the physical order of rows on disk and power zone map effectiveness.

**Compound sort key** (default): Multi-column prefix index. Queries must filter on the leading column(s) to benefit. Best for dashboards with consistent filter patterns.

**Interleaved sort key**: Equal weight to each column. Benefits queries that filter on any subset of sort key columns. Higher maintenance cost -- requires regular VACUUM REINDEX.

**Auto sort key**: Redshift automatically chooses and maintains sort order based on query patterns. Good default when access patterns are diverse.

```sql
-- Compound sort key: queries must filter on order_date (or order_date + status) to benefit
CREATE TABLE orders (...)
COMPOUND SORTKEY (order_date, status, customer_id);

-- Interleaved sort key: any combination of these columns benefits scans
CREATE TABLE events (...)
INTERLEAVED SORTKEY (event_type, region, event_date);
```

**Sort key selection rules:**
1. The first column of a compound sort key should be the most common range-filter or equality-filter column (typically a date).
2. Add columns in decreasing order of filter selectivity.
3. Use interleaved only when queries genuinely filter on different subsets of columns and you can afford VACUUM REINDEX overhead.
4. Tables under ~10M rows often do not benefit significantly from sort keys -- zone maps are already sparse.
5. Monitor unsorted percentage via SVV_TABLE_INFO; VACUUM SORT when unsorted > 20%.

### Compression Encodings

Redshift stores data compressed. The right encoding dramatically reduces I/O and storage.

| Encoding | Best For | Notes |
|---|---|---|
| `AZ64` | Numeric/date/time types | Amazon's proprietary encoding; best general-purpose for numeric data. Default for applicable types. |
| `LZO` | VARCHAR/CHAR with moderate entropy | General-purpose byte-level compression |
| `ZSTD` | VARCHAR/CHAR, high compression ratio | Best compression ratio; slightly more CPU than LZO |
| `BYTEDICT` | Low-cardinality strings (<256 distinct) | Dictionary encoding; 1 byte per value |
| `RUNLENGTH` | Columns with long runs of repeated values | Stores value + count |
| `DELTA` / `DELTA32K` | Sorted numeric/date columns with small increments | Stores deltas between consecutive values |
| `MOSTLY8` / `MOSTLY16` / `MOSTLY32` | Numeric columns where most values fit in smaller width | Packs values into smaller integer widths |
| `RAW` | No compression | Only for sort key leading columns if needed |
| `TEXT255` / `TEXT32K` | Deprecated; use LZO or ZSTD | Legacy dictionary-based text encodings |

**Best practice:** Use `ENCODE AUTO` (the default) and let Redshift choose optimal encodings, or run `ANALYZE COMPRESSION <table>` to get recommendations for existing tables.

### Redshift Serverless

From Kilo-Org/kilo-marketplace, Apache-2.0.

More from Kilo-Org/kilo-marketplace