sqf-py 0.1.0

Semantic Query Fingerprinting for Snowflake — collapse syntactically different but logically identical SQL queries to a canonical fingerprint

sqf-py — Semantic Query Fingerprinting for Snowflake

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.

---

The Problem

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

-- 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 for methodology and caveats.

---

Installation

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

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.

---

Analyzing a Snowflake Workload

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/ and is also usable standalone.

---

Synthetic Workloads & Benchmarks

No Snowflake account needed to try the library:

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

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

Outputs benchmarks/results.json plus five charts:

---

Development

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