Redshift Query Decomposer

Compiles a monolithic Amazon Redshift query into a staged, DISTKEY/SORTKEY-tuned pipeline of temp tables — with the reasoning shown. Built on SQLGlot.

Given a SQL statement plus a catalog of table attributes and view definitions, Redshift Query Decomposer:

1. Parses Redshift SQL (SQLGlot dialect)
2. Explodes views into their underlying definitions
3. Pushes safe predicates and prunes columns into narrow staged temp tables
4. Chooses DISTKEY / SORTKEY for each stage, and explains every choice
5. Emits a multi-statement session script: stage temps → rewritten final query

No database connection is required. The caller supplies catalog metadata; the library only rewrites SQL. Every stage carries a rationale and a safety label, and every plan carries findings that say what to review.

Safety: Generated scripts are candidates. Always validate row counts, nulls, and EXPLAIN plans before production use.

Install

pip install redshift-query-decomposer

The import name is redshift_decomposer:

from redshift_decomposer import decompose, assess_decomposability

Quick triage: will decomposition even help?

New in 0.2.0 — a parse-only pre-flight check that scores any query 0.0–1.0 in milliseconds and names its reasons (SUPER/JSON manipulation, recursive CTEs, correlated subqueries, LATERAL, set operations, missing filters, deep nesting):

from redshift_decomposer import assess_decomposability

report = assess_decomposability(sql)
print(report.summary())
# [######----] 0.65  MODERATE - decompose, expect review findings
#   -0.25  Correlated subquery: 1 subquery scope(s) reference outer aliases...
#   -0.10  Window functions: 1 window expression(s)...

It also runs standalone (only needs sqlglot): python -m redshift_decomposer.triage.

Minimal decomposition example

from redshift_decomposer import Catalog, TableStats, ViewDef, decompose

catalog = Catalog(
    tables={
        "analytics.public.fact_orders": TableStats(
            columns={
                "order_id": "BIGINT",
                "cust_id": "BIGINT",
                "order_date": "DATE",
                "amount": "DECIMAL(18,2)",
            },
            diststyle="KEY",
            distkey="cust_id",
            sortkeys=("order_date",),
            rows=2_000_000_000,
            size_mb=420_000,
        ),
        "analytics.public.dim_customer": TableStats(
            columns={
                "cust_id": "BIGINT",
                "region": "VARCHAR",
            },
            diststyle="ALL",
            rows=5_000_000,
            size_mb=800,
        ),
    },
    views={
        "analytics.reporting.v_orders": ViewDef(
            sql="""
            SELECT order_id, cust_id, order_date, amount
            FROM analytics.public.fact_orders
            WHERE amount > 0
            """
        ),
    },
)

plan = decompose(
    """
    SELECT o.order_id, c.region, o.amount
    FROM analytics.reporting.v_orders o
    JOIN analytics.public.dim_customer c
      ON o.cust_id = c.cust_id
    WHERE o.order_date >= DATE '2024-01-01'
    """,
    catalog,
)

print(plan.script)
for stage in plan.stages:
    print(stage.name, stage.distkey, stage.sortkeys, stage.rationale)

Library contract

Table repository cache (recommended)

SVV_TABLE_INFO is per-database and can be monstrously slow. Build a cluster-wide cache once:

1. List local databases (svv_redshift_databases where database_type = 'local' — excludes datashares)
2. Open a new connection per database (Redshift cannot switch DB in-session)
3. Capture full SVV_TABLE_INFO + pg_table_def (compound sort key positions, distkey, types)
4. Store everything in one SQLite file

Lookup policy: cache hit → use it; miss → live SVV/pg_table_def (optional write-through).

import redshift_connector
from redshift_decomposer import build_table_repository, decompose, TableRepository

def connect(database: str):
    return redshift_connector.connect(
        host="...", database=database, user="...", password="..."
    )

# Slow path — schedule overnight / after major DDL
report = build_table_repository(
    connect,
    path="C:/cache/redshift_table_repo.sqlite",
    bootstrap_database="analytics",
)
print(report.databases_ok, report.table_count)

# Fast path — metrics from cache; live only on misses / views
plan = decompose(
    sql,
    repository="C:/cache/redshift_table_repo.sqlite",
    connect=connect,
    database="analytics",
)

repo = TableRepository("C:/cache/redshift_table_repo.sqlite")
print(repo.get_table("analytics", "public", "fact_orders").sortkeys)  # full compound key

Sort keys: SVV_TABLE_INFO.sortkey1 is only the leading column. The repository merges pg_table_def.sortkey positions so compound SORTKEY(a, b, c) is preserved.

Live fetch without a cache

plan = decompose(sql, connection=conn)  # current database only

Offline frames (e.g. DataBasix DuckDB):

from redshift_decomposer import catalog_from_databasix_frames, decompose

catalog = catalog_from_databasix_frames(table_info_df, view_definitions_df)
plan = decompose(sql, catalog)

Design principles

• SQLGlot-first: parse, transform, generate with dialect="redshift"
• Catalog-required for advanced work: no silent network catalog fetches
• Conservative defaults: refuse or warn on unsafe boundaries (outer joins, multi-alias shared stages, unresolved *)
• Honest output: every stage explains itself; every plan says what to review
• Publishable core: no GUI, DuckDB, or connector dependency

Known limitations (0.2.x)

Stated plainly, because you should know them before trusting a plan:

• Predicate pushdown reads top-level WHERE clauses only. A large table referenced solely inside a CTE body or a set-operation branch can be staged without its filter — an unfiltered copy that may cost more than it saves. Review stage SQL before running (every plan tells you to). Fix planned for 0.3.x.
• Column qualification degrades when catalog keys (db.schema.table) and query references (schema.table) disagree on depth; plans fall back to catalog-order projection instead of proven pruning.
• Decomposition targets read queries; DML/DDL and recursive CTEs are refused (the triage scorer says so up front).
• This is not query decorrelation, federation, or a dbt replacement — it rewrites one query into explicit staged steps you can read and tune.

Status

Alpha (0.2.0). Focus is a correct, testable decomposition pipeline that can grow more advanced staging strategies without breaking the public API.