Metadata-Version: 2.4
Name: redshift-query-decomposer
Version: 0.2.0
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

# 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](https://github.com/tobymao/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

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

The import name is `redshift_decomposer`:

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

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

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

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

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

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

### Live fetch without a cache

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

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