Somewhere right now, a 2-billion-row table is being scanned in full to answer a question about last Tuesday. This library exists for that query: it parses Redshift SQL, explodes the views, pushes the safe predicates down, prunes the columns, picks distribution and sort keys for each stage, and hands back a script of narrow temp tables plus a rewritten final query — and explains every decision it made.

pip install redshift-query-decomposer

from redshift_decomposer import decompose, assess_decomposability

No database connection required. The library never touches your cluster — it has no idea what your password is, and would like to keep it that way.

For engineers: This tool is not perfect. Use it one query at a time. Always show the estimated conversion-success likelihood (assess_decomposability / plan.conversion_likelihood) and treat the generated script as a skeleton to move forward — not a drop-in production rewrite. Validate row counts, nulls, and EXPLAIN vs the original before use. The script header and findings say the same thing on purpose.

Before → After

Before — the query your BI tool wrote. It looks innocent, which is how they always look:

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'
  AND c.segment = 'enterprise'

What's actually behind it: v_orders is a view hiding fact_orders — 2,000,000,000 rows × 14 columns (~420 GB) — and the join key, the filters, and the three columns anyone asked for are buried inside it. Redshift will happily scan all of it.

After — actual, unedited library output, shown piece by piece with what each piece used to be.

Stage 1 — the dimension, cut down to the enterprise slice

This was: analytics.public.dim_customer, 5M rows joined in full, its segment filter applied only at the end. Now: only enterprise rows, co-located on the join key before the join happens.

DROP TABLE IF EXISTS tmp_rsd_01_dim_customer;
CREATE TEMP TABLE tmp_rsd_01_dim_customer
DISTKEY("cust_id")
SORTKEY("segment")
AS
SELECT
    "cust_id", "region", "segment"
FROM "analytics"."public"."dim_customer" AS src
WHERE (src.segment = 'enterprise');
ANALYZE tmp_rsd_01_dim_customer;

Stage 2 — the deeply embedded fact table, extracted to need-to-know

This was: the 2-billion-row, 14-column fact_orders hiding inside the view — channel, promo_code, warehouse_id, carrier, ship_date, return_flag, tax, discount, etl_batch_id all along for a ride nobody asked them to take. Now: 5 of 14 columns — the three the query returns, the join key, and status (kept because the view's own WHERE clause needs it — the planner reads view bodies, not just your query). The date filter was pushed through the view alias into the stage, DISTKEY(cust_id) is preserved from the source so the join stays local, and SORTKEY(order_date) keeps the pushed range filter fast.

DROP TABLE IF EXISTS tmp_rsd_02_fact_orders;
CREATE TEMP TABLE tmp_rsd_02_fact_orders
DISTKEY("cust_id")
SORTKEY("order_date")
AS
SELECT
    "order_id", "cust_id", "order_date", "amount", "status"
FROM "analytics"."public"."fact_orders" AS src
WHERE (src.order_date >= CAST('2024-01-01' AS DATE));
ANALYZE tmp_rsd_02_fact_orders;

Final query — the view, unmasked

This was: FROM analytics.reporting.v_orders o — a black box. Now: the view is inlined as a visible subquery with its status <> 'CANCELLED' guard preserved, reading from the slim staged temp instead of the 420 GB original. Semantics identical; I/O is not.

SELECT
  o.order_id,
  c.region,
  o.amount
FROM (
  SELECT
    order_id,
    cust_id,
    order_date,
    amount,
    status
  FROM tmp_rsd_02_fact_orders AS fact_orders
  WHERE
    status <> 'CANCELLED'
) AS o
JOIN tmp_rsd_01_dim_customer AS c
  ON o.cust_id = c.cust_id
WHERE
  o.order_date >= CAST('2024-01-01' AS DATE) AND c.segment = 'enterprise';

The receipts

Every stage explains itself — this column is generated, not hand-written:

from redshift_decomposer import Catalog, TableStats, ViewDef, decompose

catalog = Catalog(
    tables={
        "analytics.public.fact_orders": TableStats(
            columns={c: "VARCHAR" for c in (
                "order_id", "cust_id", "order_date", "amount", "status",
                "channel", "promo_code", "warehouse_id", "carrier",
                "ship_date", "return_flag", "tax", "discount", "etl_batch_id",
            )},
            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", "segment": "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, status
            FROM analytics.public.fact_orders
            WHERE status <> 'CANCELLED'
            """
        ),
    },
)

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'
      AND c.segment = 'enterprise'
    """,
    catalog,
)

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

Triage first: estimated conversion-success likelihood

Hand an engineer one slow query — not a bulk queue. The scorer answers in milliseconds (parse-only; no catalog, no connection). The 0.0–1.0 score is the estimated likelihood of a successful conversion into a usable staged skeleton — not a runtime-savings estimate, and not a guarantee. Show them this number. Every deduction is a named signal tied to real planner behavior.

from redshift_decomposer import assess_decomposability, decompose

report = assess_decomposability(sql)
print(report.summary())          # likelihood + "not perfect / skeleton" framing
# after decompose(...):
# print(plan.conversion_likelihood, plan.engineer_brief())

Redshift Query Decomposer is not perfect — treat output as a skeleton,
not a guaranteed production rewrite. One query at a time; engineer review required.
[######----] 0.65  estimated conversion-success likelihood
  MODERATE estimated conversion success — expect review findings; not drop-in
  likelihood reductions:
  -0.25  Correlated subquery: ...

It knows about SUPER/JSON manipulation, recursive CTEs, true correlated subqueries (uncorrelated IN (SELECT …) is not penalized), multi-alias self-joins, the CTE filter-pushdown gap, Spectrum/external schemas, set operations, missing filters, deep nesting, and SELECT * — which remains perfectly legal; proving it was a good idea is another matter. Also runs standalone with nothing but sqlglot installed:

python -m redshift_decomposer.triage        # paste a query interactively

How it works

 monolithic SQL ──▶ parse ──▶ explode views ──▶ analyze lineage & predicates
 (sqlglot, redshift dialect)                            │
                                                        ▼
   final query  ◀── rewrite ◀── plan stages (CTAS + DISTKEY/SORTKEY
  (over temps)                  + column pruning + rationale + safety label)

Feeding it a catalog

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

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

from redshift_decomposer import catalog_from_databasix_frames, decompose

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

schema
table          -- or table_name
tbl_rows       -- or rows
size           -- or size_mb  (megabytes)
diststyle
distkey
sortkey1
stats_off
unsorted       -- percent unsorted; percent sorted ≈ 100 - unsorted

database
schema
table_name     -- package also accepts column name "table"
table_id
encoded
diststyle
sortkey1
sortkey_num
size
pct_used
empty
unsorted
stats_off
tbl_rows
skew_sortkey1
skew_rows
estimated_visible_rows
create_time
distkey        -- if your extract stores it as its own column

from redshift_decomposer import catalog_from_rows, decompose

# You run: SELECT ... FROM my_meta.svv_table_info_mirror
# with the field names above in the result set.
catalog = catalog_from_rows(
    table_rows,                 # list[dict] or frame rows
    column_rows=column_rows,    # optional — see below
    view_rows=view_rows,        # optional — for view explosion
    default_database="analytics",
)
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 *); a wrong rewrite is worse than no rewrite
• 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 — and because we would rather you hear it from us:

• 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 will tell you so before you find out the hard way.
• 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.x) — the API may still change; the honesty is permanent. Focus is a correct, testable decomposition pipeline that can grow more advanced staging strategies without breaking the public API.

License

MIT © 2026 Ryan Capece