Data Cost Optimizer

A developer tool that analyzes SQL queries for cost anti-patterns, estimates execution cost across cloud warehouses, recommends partitioning and clustering strategies, and benchmarks engines head-to-head — all from the command line.

Architecture

flowchart TD
    subgraph Input
        SQL[SQL Query / File]
        WL[Query Workload]
    end

    subgraph Analyzer
        PA[Query Analyzer<br/>sqlglot AST]
        CE[Cost Estimator<br/>BigQuery · Snowflake · Spark]
    end

    subgraph Recommender
        PR[Partition Recommender<br/>workload analysis]
        ZO[Z-Order Advisor<br/>co-location scoring]
    end

    subgraph Benchmark
        BR[Benchmark Runner<br/>p50 · p99 · mean]
        DK[DuckDB Engine<br/>baseline]
        SP[Spark Engine<br/>optional]
        RP[Report Generator<br/>Markdown]
    end

    subgraph Outputs
        CLI[CLI Output<br/>rich terminal]
        SD[Streamlit Dashboard]
        MD[Benchmark Report<br/>Markdown]
    end

    SQL --> PA
    SQL --> CE
    WL --> PR
    WL --> ZO
    PA --> CLI
    CE --> CLI
    PR --> CLI
    ZO --> CLI
    BR --> DK
    BR --> SP
    DK --> RP
    SP --> RP
    RP --> MD
    CLI --> SD
    MD --> SD

Loading

Features

Feature	Description
Query Analysis	Detects SELECT *, missing partition filters, cartesian joins, unbounded windows, large IN lists
Cost Estimation	Estimates cost on BigQuery ($6.25/TB), Snowflake (credits), Spark (cluster-hours)
Partition Recommender	Analyses a workload of queries to recommend PARTITION BY and ZORDER BY columns with DDL
Benchmark Runner	Runs the NYC Taxi query suite on DuckDB (and optionally Spark), reporting p50/p99 latency
Streamlit Dashboard	Interactive 4-page UI for exploratory analysis

Quick Start

pip install -e .

# Analyze a single query
cost-optimizer analyze "SELECT pickup_date, COUNT(*) FROM nyc_taxi WHERE year=2024 GROUP BY 1"

# Analyze queries from a file
cost-optimizer analyze --file queries/my_workload.sql

# Get partition recommendations for a workload directory
cost-optimizer recommend --workload-dir queries/

# Run the benchmark suite
cost-optimizer benchmark --engine duckdb --report benchmark_results/report.md

# Launch the Streamlit dashboard
streamlit run dashboard/streamlit_app.py

CLI Reference

cost-optimizer analyze

Options:
  --dialect TEXT   SQL dialect: bigquery, snowflake, spark, duckdb  [default: spark]
  --file PATH      Read SQL from a file instead of inline
  --json           Output raw JSON instead of formatted table

Exit codes: 0 = no issues, 1 = critical issues found.

cost-optimizer recommend

Options:
  --workload-dir PATH   Directory of .sql files to analyse
  --file PATH           Single SQL file
  --table TEXT          Target table name for generated DDL  [default: my_table]

cost-optimizer benchmark

Options:
  --engine [duckdb|spark|all]   Engine(s) to benchmark  [default: duckdb]
  --data-path PATH              Path to NYC Taxi parquet files
  --report PATH                 Write Markdown report to this path
  --runs INTEGER                Timed runs per query  [default: 5]

Anti-Pattern Detection

Issue	Severity	Description
SELECT *	HIGH	Reads all columns; prevents projection pushdown
Missing partition filter	HIGH	Full table scan on partitioned tables
Cartesian join	CRITICAL	Cross join produces row explosion
Unbounded window	MEDIUM	No PARTITION BY on window function
Large IN list	MEDIUM	>50 values; rewrite as JOIN or temp table
No LIMIT on SELECT	LOW	Returns unbounded rows to client

Cost Model

Engine	Unit	Rate
BigQuery	TB scanned	$6.25 / TB
Snowflake	Credits	$3.00 / credit
Spark (EMR)	Node-hours	Cluster config × hours
DuckDB	—	Free (local)

Estimates assume typical column cardinality from the NYC Taxi dataset (~15M rows/month). Use --json to get raw estimates for your own calibration.

Benchmark Suite — NYC Taxi Queries

Query	Description
aggregation	Count trips and avg fare by vendor per month
hourly_revenue	Sum revenue by hour of day
rolling_7d_avg	7-day rolling average revenue per zone
tip_distribution	Tip percentage histogram
top_zones	Top 10 pickup zones by trip count

Project Structure

data-cost-optimizer/
├── analyzer/
│   ├── models.py            # QueryIssue, CostEstimate, QueryReport dataclasses
│   ├── query_analyzer.py    # sqlglot AST anti-pattern detection
│   └── cost_estimator.py    # Per-engine cost formulas
├── recommender/
│   └── partition_recommender.py  # Workload-driven PARTITION / ZORDER advice
├── benchmark/
│   ├── models.py            # BenchmarkResult, BenchmarkSuite
│   ├── runner.py            # BenchmarkRunner + NYC_TAXI_SUITE
│   ├── report.py            # Markdown report generator
│   └── engines/
│       ├── duckdb_engine.py
│       └── spark_engine.py
├── cli/
│   ├── main.py              # Click CLI entry point
│   └── commands/
│       ├── analyze_cmd.py
│       ├── recommend_cmd.py
│       └── benchmark_cmd.py
├── dashboard/
│   └── streamlit_app.py     # 4-page Streamlit UI
├── tests/
│   ├── test_query_analyzer.py
│   ├── test_cost_estimator.py
│   └── test_recommender.py
├── docs/decisions/
│   ├── 001-sqlglot-for-parsing.md
│   └── 002-duckdb-as-benchmark-baseline.md
└── .github/workflows/ci.yml

Architecture Decision Records

• ADR-001: Use sqlglot for SQL Parsing
• ADR-002: Use DuckDB as Benchmark Baseline

Development

python -m venv .venv && source .venv/bin/activate
pip install -r requirements-dev.txt
pip install -e .

# Lint
ruff check analyzer/ recommender/ benchmark/ cli/ tests/

# Tests with coverage
pytest tests/ -v --cov=analyzer --cov=recommender --cov=benchmark --tb=short

Related Projects

• data-lakehouse-pipeline — Delta Lake Bronze/Silver/Gold pipeline (generates the workload this tool optimizes)
• self-service-data-platform — Catalog and scaffold platform
• data-platform-iac — Terraform + Docker Compose infrastructure