External Tables and the Lake/Warehouse Boundary

The boundary is a decision about access and ownership

A lake already holds Parquet files. Analysts need to query them, but making another managed copy is only one option. An external table gives an engine a table-shaped interface to data stored outside its ordinary managed-table lifecycle. A loaded table gives that engine control over a separate representation. Both consume compute; both need access controls, schema rules, and reliable publication. The choice depends on latency, concurrency, freshness, update requirements, and total cost, not simply whether the data is old or new.

Follow a query from catalog metadata to files, then consider when a managed copy is worthwhile and what a plain directory-backed table fails to guarantee. “External” and “transactional” are not opposites: a modern engine can expose an external table backed by an open table format. Here, a plain external table means registered partitions and ordinary Parquet files without a transactional table layer.

Managed storage can itself use object storage. “Local copy” here means an engine-managed representation, not a claim that warehouse data lives on a local disk.

What registration supplies—and what it does not

A catalog maps a name to a schema, storage location or table-metadata reference, format, and partition information. It can also hold properties, statistics, and access-policy references. Registration normally avoids copying the source rows, but the engine may cache metadata or data and write query results elsewhere. A shared catalog helps engines discover a table; compatible connectors, types, partition rules, and permissions are still required.

For an ordinary Hive-style table, ingest_date=2026-01-20/country=KR describes a partition location. The reader can derive these values without storing them in each file. The SQL below is an Athena/Hive-style illustration, not portable SQL for every warehouse, and is not executed here. Creating the table does not automatically register every existing partition in this mode. Athena CREATE TABLE

The execution identity needs access to the catalog, source objects, any required decryption keys, and query-result locations. A table grant alone need not grant object access. Conversely, users with direct storage access may bypass restrictions enforced only by one SQL engine. Shared files need a consistent governance path. Athena identity and access management

CREATE EXTERNAL TABLE raw.orders (
  order_id BIGINT, customer_id BIGINT, amount_cents BIGINT, note STRING
)
PARTITIONED BY (ingest_date STRING, country STRING)
STORED AS PARQUET
LOCATION 's3://example-lake/raw/orders/';

ALTER TABLE raw.orders ADD IF NOT EXISTS
PARTITION (ingest_date='2026-01-20', country='KR')
LOCATION 's3://example-lake/raw/orders/ingest_date=2026-01-20/country=KR/';

The example uses ISO date strings for the partition key; SQL callers must use the declared type. The database, bucket, and access configuration are assumed placeholders. One ADD PARTITION registers only that one location.

Separate pruning, bytes, and billing

A partition predicate can eliminate locations before opening data files. Projection limits the columns needed for output, filters, joins, and aggregation. Parquet statistics can also eliminate row groups even when the predicate uses a non-partition column. An ingestion-date partition may be poorly selective for an event-time query with late arrivals; do not add a date predicate that changes the intended answer merely to reduce cost. Spectrum performance guidance

Counting files surviving partition pruning and summing compressed chunks for all required columns, including filter columns, provides a candidate-byte estimate. Without row-group or page pruning, this is not bytes fetched, billed bytes, or an upper bound on total I/O. Metadata reads, caching, readahead, retries, and query-result writes are outside it. EXPLAIN describes the planned scan; runtime metrics are needed for actual execution.

Bytes-based pricing makes pruning economically relevant, but compute-based pricing is not equivalent: concurrency, idle capacity, CPU work, and elapsed time also matter. Athena offers scan- and compute-based options, with storage, requests, transfer, and catalog charges considered separately. Minimum charges and rounding must also be included; tiny queries are not automatically free. Use the selected service, region, billing mode, and actual metrics for a forecast. Athena pricing

What a managed copy can buy

A managed copy can provide engine-specific organization, statistics, caching, materialized aggregates, workload management, and supported update operations. Many warehouses still use columnar scans; indexes and millisecond responses are not universal. External scans can also be fast enough for dashboards. Benchmark the same result, filters, concurrency, and cache conditions before choosing a boundary.

Count the extra storage, ingestion, recurring refresh, maintenance, and managed-query compute. A simplified monthly comparison is external cost = q × e and managed cost = F + q × m, where q is query count, e and m are per-query costs, and F is the extra monthly fixed cost of the managed path. Common baseline costs cancel only if genuinely equal. If e > m, break-even is F / (e − m); if e ≤ m and F is positive, query frequency alone produces no cost break-even. For example, F=120, e=0.20, m=0.05 gives 800 queries. These invented values explain the calculation; real costs need workload measurements.

A second copy also has a freshness contract. Define a source snapshot or watermark, publish the loaded copy after validation, and reconcile counts and business aggregates. A faster stale table does not satisfy a current-data requirement.

Registration is not a commit protocol

Some external tables enumerate registered partitions; others discover paths dynamically or calculate them from configured rules. Athena partition projection can calculate locations without adding each partition to Glue, but another engine may still use Glue’s registered metadata. A shared table definition therefore need not produce identical discovery behavior. In Athena, enabling projection makes the engine ignore registered partition entries for that table. A configured range that excludes valid data can return zero rows without an error; test boundary dates and expected deliveries. Partition projection

For ordinary registered Hive partitions, explicit ADD PARTITION, a crawler, or a repair command can add new locations. Athena MSCK REPAIR TABLE adds discovered partitions; it does not remove stale ones and a timed-out repair can leave registration incomplete. These operations do not prove that each partition’s writer finished. Repair semantics

For a plain file table, write to isolated immutable locations, validate files and expected counts, and publish a reference through a protocol every reader honors. A _SUCCESS marker has no effect on an unaware reader. Directory rename is not a portable atomic operation over object stores, and atomic creation of one object does not commit many objects. A manifest must pin immutable files or object versions and retain them for readers.

Updates and history require a table layer

Overwriting affected Parquet files one at a time can expose mixed results to concurrent readers. Even a saved list of paths cannot reproduce the old answer after those paths are overwritten. A snapshot requires stable file identities, a consistent committed membership, and retention of both data and metadata. Query invisibility does not prove removal from historical versions, replicas, or backups.

Open table formats add transaction and metadata protocols, but they do not all use the same tree or one small delete file. Iceberg describes snapshots and atomic metadata replacement, with row-level delete representations depending on format version. Delta uses a transaction log and can use deletion vectors with compatible clients; rewrites are also possible. Hudi distinguishes copy-on-write and merge-on-read tables and their query behavior. Iceberg specification Delta deletion vectors Hudi table types

Atomicity depends on a supported catalog, storage, and concurrency protocol, not merely naming a metadata pointer. Time travel lasts only while required files and metadata remain available. Schema and partition evolution are format- and engine-specific, and existing files do not magically acquire a new physical layout. Adopting a format requires reader compatibility, migration validation, maintenance, and retention decisions. It is more than changing an external-table keyword.

Make the hot/cold boundary explicit

For the subscription-company example, replay history can remain in the lake, frequently refreshed dashboard aggregates can use a managed table or materialized result, and occasional historical analysis can use external scans. A thirteen-month managed window is a hypothesis to test against measured access, not an architectural rule. Small-file compaction can reduce metadata work but is not a prerequisite for registering a table; choose file sizes from scale and query behavior.

If one view combines cold external rows and hot managed rows, use mutually exclusive predicates on the same typed time field: cold < cutoff and hot ≥ cutoff. Publish a complete hot copy before advancing the boundary. Define the timezone, treatment of late updates and deletes, freshness watermark, and reconciliation procedure. UNION ALL otherwise duplicates overlaps or hides gaps; UNION can hide exact duplicates without repairing incorrect source ownership. Null boundary values satisfy neither comparison and disappear from both sides. Reject or quarantine them explicitly, or define a separate branch; do not silently treat them as cold.

Record p95 latency, concurrency, freshness, billed bytes or compute, refresh cost, file counts, registration lag, and errors by workload. Reconsider the boundary when those measurements or requirements change. A dashboard is not automatically a reason to load data, and a low query count is not automatically a reason to tolerate slow or inconsistent reads.

References

• Athena CREATE TABLE
• Athena identity and access management
• Spectrum performance guidance
• Athena pricing
• Partition projection
• Repair semantics
• Iceberg specification
• Delta deletion vectors
• Hudi table types