Serverless Querying: Athena, BigQuery and Lakes

• Describe how serverless engines scan partitioned Parquet on object storage
• Tune data layout (format, partitioning, compaction) to make scans cheap
• Decide when serverless SQL beats a dedicated warehouse

• Zero infrastructure: no cluster sizing, no scaling, no idle compute.
• Lakes become immediately queryable — you stop copying into a warehouse for the 20 queries you actually run.
• Ellastic cost: an exploratory query costs cents; a month of quiet costs nothing.
• Formats are open, so the same Parquet serves Athena today and Trino/Spark tomorrow.

You don't staff 40 cabs all year for one festival weekend — you rent surge capacity. Serverless queries stand up compute per question and stand it down after.

• Data layout first: Parquet/ORC with hive-style partitioning (dt=YYYY/MM/DD, partition columns as top-level dirs).
• Predicate pushdown: the engine inspects partition paths and column block stats to skip whole files and row-groups.
• Scan-and-pay pricing tiers: Athena per-TB-scanned, BigQuery per-slot with flat-rate options.
• Metadata consolidation: Glue catalog / lakeformation tables define schema and partition layout ahead of the query.
• Concurrency handled by the engine; no warehouse contention because there is no warehouse.
• Caching: recent scans cached by region/engine to avoid re-reads of the same bytes.

• A SQL-curious analyst queries S3 clickstreams in Athena daily without raising a ticket — zero infra, cents per query.
• A data team runs month-end compliance SQL over a 10 TB lake with column pruning, billing less than a warehouse hour.
• A startup prototypes analytics on DuckDB pointed at S3 Parquet before committing to a warehouse — same files, both paths.

• Parquet + partition + compact files (128 MB target) — small files triple scan bytes to overhead.
• Put partition columns in the path AND as Parquet columns stats so pruning actually catches.
• Use columnar projections in every query: SELECT only what you need.
• Load a compact metadata/statistics layer (Iceberg stats, Glue/Athena metastore) to keep prunes sharp.

• Querying CSV/JSON — serverless engines scan every byte; the cost difference vs Parquet is enormous.
• Millions of tiny files — same bytes, orders of magnitude more overhead scan.
• Partitions that don't match query patterns — partitioning by dt when queries filter by user.
• Skipping the catalog, so every query restarts with zero pruning and full table scans.

• High cost → check bytes scanned: that's your pruning health (should track partition-bounded sizes).
• Slow query → list file count/avg size in the scanned partition; compact if it's thousands of <1 MB files.
• Wrong results → metastore schema drift: catalog schema and file Parquet schema disagree.
• 'Partition not found' → partitions exist but the catalog wasn't refreshed after new files landed.