Metadata-Version: 2.4
Name: sqf-py
Version: 0.1.0
Summary: Semantic Query Fingerprinting for Snowflake — collapse syntactically different but logically identical SQL queries to a canonical fingerprint
License: MIT
Project-URL: Homepage, https://github.com/vermapragya/sqf-py
Project-URL: White Paper, https://github.com/vermapragya/sqf-py/blob/main/WHITEPAPER.md
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: sqlglot>=25.0.0
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"
Requires-Dist: pytest-cov; extra == "dev"
Provides-Extra: snowflake
Requires-Dist: snowflake-connector-python>=3.0; extra == "snowflake"
Provides-Extra: bench
Requires-Dist: matplotlib>=3.7; extra == "bench"
Dynamic: license-file

# sqf-py — Semantic Query Fingerprinting for Snowflake

[![Tests](https://img.shields.io/badge/tests-68%20passed-brightgreen)]()
[![Python](https://img.shields.io/badge/python-3.9%2B-blue)]()
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)]()

**sqf-py** assigns a stable, content-addressed fingerprint to any SQL query by normalizing away syntactic noise. Queries that are *logically identical* but *written differently* collapse to the same fingerprint — enabling deduplication analysis, cost attribution, and query-cache optimization on Snowflake.

Accompanies the white paper: [**Semantic Query Deduplication in Cloud Data Warehouses**](WHITEPAPER.md).

---

## The Problem

Modern data warehouses are bombarded with semantically identical queries that look different:

```sql
-- BI tool A (Looker)
SELECT o.user_id AS uid, SUM(o.amount) AS revenue
FROM orders AS o WHERE o.status = 'complete' AND o.created_at > '2024-01-01'
GROUP BY 1

-- BI tool B (Tableau)
SELECT SUM(amount) AS revenue, user_id AS uid
FROM orders WHERE created_at > '2023-06-01' AND status = 'active'
GROUP BY uid
```

These are logically the same query template. Snowflake's text-keyed result cache treats them as distinct — burning compute on every re-execution. sqf-py proves they're duplicates: both fingerprint to `3c1a8c600789df69…`.

On a synthetic 10,000-query BI-style workload, sqf-py identifies **99.7% of executions as semantic duplicates**, at **~440 queries/second** analyzed client-side. See [the white paper](WHITEPAPER.md) for methodology and caveats.

---

## Installation

```bash
pip install sqf-py                 # core library (sqlglot only)
pip install "sqf-py[snowflake]"    # + Snowflake connector
pip install "sqf-py[bench]"        # + matplotlib for benchmark charts
pip install "sqf-py[dev]"          # + pytest
```

---

## Quick Start

```python
from sqf import fingerprint, are_equivalent, canonical_form, SQFAnalyzer

# Single fingerprint
fp = fingerprint("SELECT a, b FROM t WHERE id = 1")
# → "3f4a1b9c..."  (64-char hex, stable)

# Equivalence check — these two queries are semantically identical
q1 = "SELECT a AS col1, b AS col2 FROM t WHERE id = 99"
q2 = "SELECT b, a FROM t WHERE id = 1"
are_equivalent(q1, q2)  # → True

# See the canonical form
canonical_form("SELECT a AS x, b AS y FROM t WHERE id = 42")
# → "SELECT A, B FROM T WHERE ID = ?"

# Bulk workload analysis
analyzer = SQFAnalyzer()
analyzer.ingest_sql(my_query_list, credits_per_query=0.05)
print(analyzer.report().summary())
```

---

## Normalization Pipeline

The SQF algorithm applies these passes in order:

| Pass | What it does | Example |
|------|-------------|---------|
| 1. GROUP BY reference resolution | `GROUP BY 1` / `GROUP BY alias` → actual expression | `GROUP BY user_id` |
| 2. Alias stripping | Remove all `AS` aliases and table qualifiers | `SELECT o.a AS x` → `SELECT a` |
| 3. Column sort | Sort SELECT list alphabetically | `SELECT b, a` → `SELECT a, b` |
| 4. GROUP BY sort | Sort GROUP BY keys | `GROUP BY b, a` → `GROUP BY a, b` |
| 5. Predicate canonicalization | Sort AND/OR operands recursively | `WHERE b=2 AND a=1` → `WHERE a=1 AND b=2` |
| 6. CTE inlining | Inline single-reference CTEs | `WITH x AS (...) SELECT ... FROM x` → subquery |
| 7. Literal abstraction | Replace all values with `?` | `WHERE id = 42` → `WHERE id = ?` |
| 8. Whitespace collapse + uppercase | Canonical string form | |
| **Hash** | SHA-256 of canonical string | 64-char hex fingerprint |

The precise equivalence class (and its deliberate trade-offs) is defined in [§2 of the white paper](WHITEPAPER.md).

---

## Analyzing a Snowflake Workload

```python
from sqf import SnowflakeIngestor, ClusterStore, SQFAnalyzer
import snowflake.connector

conn = snowflake.connector.connect(...)  # your credentials

# 1. Pull the last 30 days of QUERY_HISTORY
records = SnowflakeIngestor(conn, lookback_days=30, row_limit=50_000).fetch_records()

# 2. Fingerprint + cluster
report = SQFAnalyzer().ingest(records).report()
print(report.summary())
# ═══════════════════════════════════════════════════════════
#   Semantic Query Fingerprint (SQF) Analysis Report
# ═══════════════════════════════════════════════════════════
#   Total query executions   :    12,847
#   Unique SQF fingerprints  :     4,203
#   Dedup hit rate           :    67.3%
#   Credits wasted           :    86.4800
#   ...

# 3. Persist results back to Snowflake (idempotent MERGEs)
store = ClusterStore(conn, database="SQF", schema="ANALYTICS")
store.bootstrap()        # creates tables + 6 analytical views
store.persist(report)

# 4. Query the views
store.overall_metrics()          # headline KPIs
store.daily_hit_rate()           # time series for charts
store.top_waste(10)              # the 10 most expensive duplicate clusters
store.multi_variant_offenders(10)  # same logic, many SQL spellings
```

The bundled SQL (DDL, views, `QUERY_HISTORY` export) lives in [`sqf/sql/`](sqf/sql/) and is also usable standalone.

---

## Synthetic Workloads & Benchmarks

No Snowflake account needed to try the library:

```python
from sqf import SyntheticWorkloadGenerator, SQFAnalyzer

gen = SyntheticWorkloadGenerator(n_queries=1000, duplication_rate=0.7, seed=42)
report = SQFAnalyzer().ingest(gen.generate()).report()
print(report.summary())   # → 96.9% dedup hit rate
```

The generator models 12 logical query families (BI aggregates, joins, window functions, funnels, MRR rollups, …) with 8 syntactic variant dimensions each, plus realistic per-family credit cost distributions.

Reproduce the white paper's full benchmark grid (36 configurations, ~5 min):

```bash
python -m sqf.benchmark --out benchmarks --full
```

Outputs `benchmarks/results.json` plus five charts:

![Hit rate vs duplication rate](benchmarks/charts/01_hit_rate_vs_dup_rate.png)

---

## Development

```bash
git clone https://github.com/vermapragya/sqf-py
cd sqf-py
python3 -m venv .venv
.venv/bin/pip install -e ".[dev,bench]"
.venv/bin/python -m pytest        # 68 tests
```

---

## License

[MIT](LICENSE)
