Cloudflare R2 SQL Deep Dive: Query Petabytes Without Managing Servers

A deep dive into Cloudflare's serverless analytics query engine — how it works, how much it costs, and how to use it today

🎯 The Big Idea

Imagine you have 10 terabytes of log data sitting in an object store. You want to ask questions like:

"How many 500 errors happened in the last hour?"
"Which users are triggering fraud alerts?"
"What's our revenue breakdown by region?"

Traditionally, you'd need to spin up a cluster — Trino, Spark, Presto, or Athena. That means managing servers, worrying about costs when they're idle, and dealing with egress fees when moving data around.

Cloudflare R2 SQL says: What if the query engine lives ON the storage, runs serverlessly, and costs you nothing?

That's exactly what it is — a serverless analytics query engine that runs SQL on Apache Iceberg tables stored in Cloudflare R2, with zero infrastructure to manage and no egress fees ever.

Let's dig into how it actually works. 🧠

🏗️ Architecture: The Two-Phase Genius

R2 SQL doesn't just "read all the files." That would be impossibly slow at petabyte scale. Instead, it uses a two-phase approach that's clever as heck.

Phase 1: The Query Planner (Think Before You Leap)

Before reading ANY data, the planner navigates Iceberg's metadata hierarchy. Iceberg tables store metadata about their own data — think of it as a card catalog at a library. You don't pull every book off the shelf; you check the catalog first.

Layer 1: Table Metadata (JSON)
  └─ Points to the current snapshot

Layer 2: Manifest List (partition stats)
  └─ Min/max partition values per manifest file
  └─ 🟢 PRUNE: Skip entire manifests that don't match WHERE

Layer 3: Manifest Files (column stats per data file)
  └─ Min/max values for each column in each Parquet file
  └─ Null counts per column
  └─ 🟢 PRUNE: Skip files where col min/max don't satisfy query

Layer 4: Parquet Row Groups (footer stats)
  └─ Statistics inside each Parquet file
  └─ 🟢 PRUNE: Skip row groups within files

Real example: If you query WHERE status_code = 500 and a file's stats show status_code min=200, max=302, the planner says: "This file can't possibly contain status_code=500," and skips it entirely without reading a single byte. This is called metadata pruning, and it's the secret sauce.

Phase 2: Distributed Workers (Read Only What's Needed)

Once the planner identifies which files to read, it distributes work across Cloudflare's global network — not centralized servers. Each worker reads only the row groups it needs and streams results back.

The "Stop Early" Optimization

For ORDER BY timestamp DESC LIMIT 5 queries, R2 SQL maintains a bounded heap of the top 5 results and a high-water mark. The moment it can prove better results can't exist in unread files — it stops. The official Cloudflare blog notes this can reduce data read to under 10% of matching files.

💰 Pricing: Where It Gets Interesting

During open beta, R2 SQL query engine usage is free. You only pay for:

Service	Cost
R2 Storage	$0.015/GB/month
Class A Ops (writes)	$4.50/million
Class B Ops (reads)	$0.36/million
Data Catalog Ops	$9.00/million
Data Egress	$0.00 (always free)

Comparison vs competitors:

Service	Cost per TB scanned	Egress fees	Server management
Cloudflare R2 SQL	$0 (beta)	$0	None
AWS Athena	~$5.00/TB	$0.09/GB out	None
BigQuery	~$5.00/TB	$0.12/GB out	None
Snowflake	~$23.00/TB	Varies	Managed

Cloudflare will give 30 days notice before any query pricing begins.

📥 How to Store Data

R2 SQL works with Apache Iceberg tables managed through the R2 Data Catalog. You don't INSERT directly — you ingest via Cloudflare Pipelines.

Setup: One-Time

# 1. Create an R2 bucket
npx wrangler r2 bucket create my-analytics

# 2. Enable Data Catalog on it
npx wrangler r2 bucket catalog enable my-analytics
# → Note the Warehouse ID returned

# 3. Define a schema
cat > schema.json << 'EOF'
{
  "fields": [
    {"name": "user_id",    "type": "string",  "required": true},
    {"name": "event_type", "type": "string",  "required": true},
    {"name": "amount",     "type": "float64", "required": false}
  ]
}
EOF

# 4. Create stream + sink + pipeline
npx wrangler pipelines streams create events_stream --schema-file schema.json --http-enabled true
npx wrangler pipelines sinks create events_sink --type "r2-data-catalog" --bucket "my-analytics" --table "ecommerce"
npx wrangler pipelines create events_pipeline --sql "INSERT INTO events_sink SELECT * FROM events_stream"

Send Data

curl -X POST https://{stream-id}.ingest.cloudflare.com \
  -H "Content-Type: application/json" \
  -d '[
    {"user_id":"user_123","event_type":"purchase","amount":29.99},
    {"user_id":"user_456","event_type":"view_product"}
  ]'

Alternative: Direct Iceberg REST Catalog

Since R2 Data Catalog exposes a standard Iceberg REST API, you can connect DuckDB, Spark, or PyIceberg directly — and still pay zero egress:

-- DuckDB example
INSTALL iceberg; LOAD iceberg;
CREATE SECRET r2_secret (TYPE ICEBERG, TOKEN 'your-api-token');
ATTACH 'https://catalog.cloudflare.com/...' AS r2_catalog (TYPE ICEBERG);

INSERT INTO r2_catalog.default.ecommerce
SELECT * FROM read_csv_auto('local_data.csv');

📤 How to Query Data

Via Wrangler CLI

export WRANGLER_R2_SQL_AUTH_TOKEN="your-api-token"

# Get your warehouse name
npx wrangler r2 bucket catalog get my-analytics

# Basic query
npx wrangler r2 sql query "YOUR_WAREHOUSE" \
  "SELECT * FROM default.ecommerce LIMIT 10"

# Filtered + aggregated
npx wrangler r2 sql query "YOUR_WAREHOUSE" \
  "SELECT event_type,
          COUNT(*) AS cnt,
          ROUND(AVG(amount), 2) AS avg_amount
   FROM default.ecommerce
   WHERE amount IS NOT NULL
   GROUP BY event_type
   ORDER BY cnt DESC
   LIMIT 10"

Via REST API

curl -X POST \
  "https://api.sql.cloudflarestorage.com/api/v1/accounts/{ACCOUNT_ID}/r2-sql/query/my-analytics" \
  -H "Authorization: Bearer ${TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT event_type, COUNT(*) AS cnt FROM default.ecommerce GROUP BY event_type ORDER BY cnt DESC LIMIT 10"}'

🧪 Supported SQL

Feature	Status	Example
SELECT with WHERE	✅	`SELECT * FROM t WHERE x > 5`
GROUP BY + aggregates	✅	`GROUP BY cat, COUNT(*), AVG(x)`
ORDER BY / LIMIT	✅	`ORDER BY x DESC LIMIT 100`
JOINs (INNER/LEFT/RIGHT)	✅	`JOIN t2 ON t1.id = t2.id`
CTE (WITH)	✅	`WITH cte AS (...) SELECT * FROM cte`
Subqueries	✅	`WHERE id IN (SELECT id FROM ...)`
SHOW DATABASES	✅	Schema discovery
DESCRIBE table	✅	Schema inspection
INSERT/UPDATE/DELETE	❌	Append-only via Pipelines
CREATE TABLE	❌	Auto-created by Pipelines

🎯 Who Is It For?

You should use R2 SQL if...	You should stick with alternatives if...
Your data is already in R2	You need transactional (row-level) updates
You want zero egress costs	You need real-time streaming queries (<1s latency)
You hate managing clusters	You need a specific engine feature not yet supported
You're building on Cloudflare Workers	Your analytics tool requires a JDBC/ODBC driver

⚡ Real-World Performance

Based on Cloudflare's benchmarks and community reports:

Sub-second queries on tables up to hundreds of GB (thanks to metadata pruning)
Linear scaling — more data means more files but pruning keeps time constant for selective queries
Cold start ~2-5 seconds for first query (spinning up workers on edge nodes)
Subsequent queries on same table benefit from catalog caching

Cloudflare's engineering blog notes that for partitioned + clustered tables, they've seen 90%+ file skipping rates for typical analytical queries.

🔗 Resources & Source Links

Written by John — Software Engineer, Docker wrangler, and accidental Chromium zombie wrangler. First published June 12, 2026.