Metadata-Version: 2.4
Name: redshift-query-decomposer
Version: 0.2.1
Summary: Redshift Query Decomposer: compiles a monolithic Amazon Redshift query into a staged, DISTKEY/SORTKEY-tuned pipeline of temp tables - with the reasoning shown.
Author-email: Ryan Capece <lcapece@optonline.net>
License: MIT
Project-URL: Homepage, https://github.com/lcapece/redshift-query-decomposer
Project-URL: Repository, https://github.com/lcapece/redshift-query-decomposer
Project-URL: Documentation, https://github.com/lcapece/redshift-query-decomposer#readme
Project-URL: Changelog, https://github.com/lcapece/redshift-query-decomposer/blob/main/CHANGELOG.md
Keywords: amazon-redshift,redshift,sql,sqlglot,query-optimization,query-decomposition,slow-query,distkey,sortkey,temp-tables
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlglot<28,>=26.0.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Dynamic: license-file

<div align="center">

# Redshift Query Decomposer

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

[![PyPI](https://img.shields.io/pypi/v/redshift-query-decomposer)](https://pypi.org/project/redshift-query-decomposer/)
[![Python](https://img.shields.io/pypi/pyversions/redshift-query-decomposer)](https://pypi.org/project/redshift-query-decomposer/)
[![License: MIT](https://img.shields.io/badge/license-MIT-blue.svg)](https://github.com/lcapece/redshift-query-decomposer/blob/main/LICENSE)
[![Built on SQLGlot](https://img.shields.io/badge/built%20on-SQLGlot-blueviolet)](https://github.com/tobymao/sqlglot)

</div>

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**.

```bash
pip install redshift-query-decomposer
```

```python
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.

> **Safety:** Generated scripts are *candidates*. Always validate row counts,
> nulls, and EXPLAIN plans before production use. The tool says this too, on
> every single plan, because it means it.

---

## Before → After

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

```sql
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.

```sql
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.

```sql
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.

```sql
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:

| Stage | Was | Became | Rationale (verbatim) |
|---|---|---|---|
| `tmp_rsd_02_fact_orders` | 2B rows × 14 cols behind a view | 5 need-to-know cols, date-filtered | large source (~2,000,000,000 rows, 420,000 MB); pushed 1 predicate(s); DISTKEY preserves source key cust_id; SORTKEY preserves source leading key order_date |
| `tmp_rsd_01_dim_customer` | 5M-row dim, filtered after the join | enterprise slice, filtered before it | large source (~5,000,000 rows, 800 MB); pushed 1 predicate(s); DISTKEY from join column cust_id; SORTKEY supports pushed filter on segment |

<details>
<summary><b>The full example, runnable</b> (click to expand)</summary>

```python
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)
```

</details>

---

## Triage first: is this query even worth decomposing?

For when someone hands you 400 lines of SQL and says "it's slow" — the scorer
answers in milliseconds, before you commit your afternoon. Parse-only, no
catalog, no connection; it grades 0.0–1.0 and names every deduction:

```python
from redshift_decomposer import assess_decomposability

print(assess_decomposability(sql).summary())
```

```text
[######----] 0.65  MODERATE - decompose, expect review findings
  -0.25  Correlated subquery: 1 subquery scope(s) reference outer aliases;
         these cannot be staged independently.
  -0.10  Window functions: 1 window expression(s); filters cannot be pushed
         through them.
```

It knows about SUPER/JSON manipulation, recursive CTEs, correlated subqueries,
`LATERAL`, 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:

```bash
python -m redshift_decomposer.triage        # paste a query interactively
```

---

## How it works

```text
 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)
```

| You provide | Redshift Query Decomposer produces |
|-------------|------------------------------------|
| Query text | Multi-statement Redshift script |
| Column schemas for referenced tables | Qualified / pruned stage SELECTs |
| Optional physical stats (rows, size, dist/sort keys) | DISTKEY / SORTKEY on temps |
| View SQL definitions | Inlined (exploded) physical plan |

---

## Feeding it a catalog

<details>
<summary><b>Cluster-wide table repository cache</b> — recommended; build once, reuse forever</summary>

`SVV_TABLE_INFO` is **per-database** and can be monstrously slow — run the
build overnight so it queries every database once and you never have to again:

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).

```python
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.

</details>

<details>
<summary><b>Live fetch / offline frames</b> — the quick alternatives</summary>

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

Offline frames (e.g. DataBasix DuckDB):

```python
from redshift_decomposer import catalog_from_databasix_frames, decompose

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

</details>

---

## 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
