Back to Devoured - April 27, 2026
DEVOURED
Internal vs. External Storage: What's the Limit of External Tables?

Internal vs. External Storage: What's the Limit of External Tables?

Data databaseinfrastructure motherduck.com
External tables, which store metadata in databases but reference data in object storage, have persisted for 25 years because they solve the economic tradeoff between storage cost and query speed.
What: A comprehensive analysis of external tables in databases, comparing them to internal storage and exploring their evolution from Oracle's 2001 implementation through modern lakehouse architectures. The article examines when to store data inside your warehouse versus externally in object storage, with benchmarks showing external tables are 1.3–1.7× slower but up to 20× cheaper for archival data.
Why it matters: This architectural pattern keeps getting re-implemented in modern systems (Snowflake 2021, Databricks, BigQuery) because the core economics still work. As data volumes grow faster than compute needs, understanding the hot versus cold storage decision directly impacts both infrastructure costs and query performance. The article traces how external tables evolved from simple CSV readers to ACID-compliant lakehouse tables, showing they're not a legacy pattern but a fundamental database primitive.
Takeaway: Evaluate your data temperature before choosing storage: use internal storage for dashboards and frequently-queried hot data, but use external tables for archival cold data where 1.5× slower queries are acceptable in exchange for 10–20× lower storage costs on S3 tiers.
Deep dive
  • External tables store only metadata in the database while data lives in external storage (like S3), functioning similarly to symlinks that create pointers without moving data
  • The pattern emerged in Oracle 9i (2001) implementing the SQL:2003 standard for foreign-data wrappers, and predecessors existed in Microsoft Access linked tables (1992) and IBM DB2 DataJoiner (1995)
  • Modern implementations have added support for columnar formats (Parquet, ORC, Avro) and semi-structured data (JSON), replacing the CSV-only parsers of 2008
  • The hot versus cold decision drives the tradeoff: internal storage delivers 23.8ms median query times for dashboards, while external tables cost 41–56ms but reduce storage costs from $23/TB/month (Snowflake) to $1/TB on S3 Glacier Deep Archive
  • Benchmark on TPC-H 6M rows showed DuckDB internal storage was 1.0× baseline, DuckLake external was 1.3× slower, raw Parquet 1.4× slower, and Iceberg 1.7× slower, but all answered cold queries under 150ms
  • Common production pattern combines external tables with materialized views to refresh nightly snapshots during off-peak hours, avoiding impact on upstream production systems while optimizing query performance
  • Modern warehouse integrations (Snowflake, Redshift Spectrum, BigQuery, Athena) simplified the DDL from Oracle's verbose ACCESS PARAMETERS syntax to schema-inferred formats over S3 with minimal configuration
  • The dbt-external-tables package wraps external table definitions in YAML, enabling declarative configuration of location, partitions, and column inference across multiple warehouse backends
  • DuckDB achieves external table functionality without CREATE EXTERNAL TABLE syntax through schema-on-read with direct Parquet/CSV reading and ATTACH commands for Postgres, MySQL, SQLite, and S3
  • Open table formats (Iceberg, Delta, Hudi) represent the next evolution where the manifest file acts as the pointer, adding ACID guarantees, time travel, and schema evolution to external data
  • DuckLake addresses the small-file problem that plagues streaming workloads: benchmark showed Iceberg creating 352 files (201 data + 151 metadata) for 50 single-row inserts versus DuckLake's zero files with rows inlined in the catalog
  • The metadata benchmark demonstrated DuckLake's 926× faster queries on streaming workloads by reading indexed rows directly rather than walking manifest trees
  • Modern lakehouse architectures blur the internal/external distinction by adding database semantics (transactions, indexes, statistics) to external storage, completing a 25-year cycle back to RDBMS principles while maintaining storage/compute separation
  • ADBC (Apache Arrow Database Connectivity) enables 30× faster data transfer than ODBC by streaming columnar data end-to-end instead of row-by-row serialization, making external Parquet tables viable for interactive BI tools
  • The pattern's persistence across database generations (Oracle 2001, Snowflake 2021, Databricks Unity Catalog, BigLake 2022) suggests it will remain relevant for another 25 years per the Lindy Effect, because reading data in place always beats moving it
Decoder
  • External table: A database table definition that stores only metadata (schema, column types) in the database while the actual data lives in external storage like S3, accessed via SQL as if it were an internal table
  • Internal storage: Data fully managed and stored within the database system itself, offering faster queries but higher storage costs compared to external alternatives
  • Materialized view (MV): A pre-computed query result stored as a physical table that refreshes periodically, combining external table freshness with internal table speed
  • Hot vs. cold data: Hot data is frequently queried (dashboards, recent analytics) requiring fast access, while cold data is archival or rarely accessed, prioritizing low storage cost over query speed
  • DDL (Data Definition Language): SQL commands like CREATE TABLE that define database structure and schema without manipulating actual data
  • ACID guarantees: Atomicity, Consistency, Isolation, Durability—database properties ensuring reliable transactions even when systems fail
  • Open table format: Vendor-neutral table specifications (Iceberg, Delta, Hudi) that store metadata alongside Parquet files, enabling multiple engines to read the same data with transactional consistency
  • Lakehouse architecture: Hybrid approach combining data lake storage (cheap object storage, open formats) with data warehouse features (SQL, ACID, governance)
  • Schema-on-read vs. schema-on-write: Schema-on-read infers structure when querying data (flexible, used by DuckDB), while schema-on-write defines structure upfront (strict, traditional databases)
  • Small-file problem: Performance degradation when table formats create thousands of tiny metadata files instead of consolidating writes, requiring expensive compaction operations
  • Lindy Effect: The principle that the longer a technology has existed, the longer it's likely to continue existing—explaining external tables' 25-year persistence
Original article

Internal tables store and manage both data and metadata within the database system, while external tables only store metadata and reference data that lives outside the system, leaving the underlying data untouched. Internal tables enable tighter lifecycle management, whereas external tables decouple storage and compute, making it easier to scale, share, and access large datasets without moving or duplicating data.

Explore all 66 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi