Puzzleshot #009 - Remote data with DuckDB

raw data→duckdb→result

So far we’ve been working with local files — compressed, malformed, split across hundreds of files. This week let’s try files from a different location.

What if the data you need is already published somewhere on the internet?

DuckDB’s httpfs extension allows you to query remote files directly over HTTP or S3 — no download, no import, no intermediate steps. The file doesn’t touch your local disk.

A simple example — querying a remote Parquet file:

INSTALL httpfs;
LOAD httpfs;

SELECT *
FROM read_parquet('https://example.com/dealers.parquet')
LIMIT 5;

That’s the only change - DuckDB streams the remote file and queries it as though it were local.

This week’s challenge:

The dealer reference data from our previous Puzzleshots is now published remotely. Using DuckDB’s httpfs extension, answer the following against the remote file:

How many dealers exist per region?
Which region has the highest average order total?

Things to consider:

Does httpfs need to be installed and loaded every session?
What’s the difference between querying HTTP vs S3 sources?
What are the performance implications of querying remote vs local files?

Reveal solution

This challenge introduced DuckDB’s httpfs extension — querying remote files without downloading them first. Let’s work through it.

Setup:

INSTALL httpfs;
LOAD httpfs;

Worth noting — INSTALL only needs to run once. LOAD is required each session unless you configure DuckDB to load it automatically via a .duckdbrc file.

How many dealers exist per region:

SELECT
  region_id,
  count(*) as dealer_count
FROM read_parquet('https://example.com/dealers.parquet')
GROUP BY region_id
ORDER BY dealer_count DESC

Which region has the highest average order total:

SELECT
  d.region_id,
  avg(o.order_total) as avg_order_total
FROM read_parquet('https://example.com/dealers.parquet') d
JOIN 'sales_clean.parquet' o
  ON d.dealer_id = o.dealer_id
GROUP BY d.region_id
ORDER BY avg_order_total DESC
LIMIT 1

Note that the second query joins the remote dealers file against our local Parquet file from #008 — DuckDB handles both sources in a single query without any intermediate steps.

On the HTTP vs S3 question:

HTTP sources work exactly as shown. For S3 sources you need to provide credentials:

SET s3_region='us-east-1';
SET s3_access_key_id='your_key';
SET s3_secret_access_key='your_secret';

SELECT * FROM read_parquet('s3://your-bucket/dealers.parquet');

Public S3 buckets don’t require credentials — DuckDB can query them directly the same way as HTTP.

On performance:

DuckDB is smart about remote Parquet files — it reads only the row groups and columns it needs rather than downloading the entire file. For selective queries against large remote Parquet files this makes a meaningful difference. For full table scans the network is your bottleneck regardless.