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

Just a Pointer (Symlink)?

A simple analogy is a symlink in Linux, where you point from your current directory to another directory without moving data. You just add a pointer. If you read that file from that symlink, all it does is read it from the location the symlink points to.

An external table is the same, just a pointer to external data, bringing that data into the current data warehouse or cloud solution, hence the word external. You define the source format such as XML, CSV, etc., and define their structure, and then you can query that at any time. It's similar to a SQL View in that sense, but pointing to non-internal data.

Running DROP TABLE and deleting an external table is metadata-based only. No data is removed, only the table definition from the internal data catalog. The same is true with a symlink. Almost any relational database today has support for it, even if it's not called an external table. Everyone occasionally needs to read data outside of its warehouse or database.

The Origin Story: ISO in 2001

The history starts with ISO/IEC 9075-9, published in 2001. Part 9 of the SQL standard defined foreign-data wrappers and datalink types for managing external data from within SQL. The work was completed in late 2000 and published alongside SQL:1999, with full integration in SQL:2003 (it was later updated in 2023).

It was the initial definition and extensions to database language SQL to support management of external data through the use of foreign-data wrappers and datalink types.

My first encounter was with Oracle external tables, but according to Wikipedia there were earlier implementations, such as Microsoft Access linked tables (~1992). Microsoft Access linked tables (~1992) were the earliest consumer-facing implementation where users could link dBASE, Paradox, text files, and ODBC sources as if they were Access tables. ODBC 1.0 (1992) itself established the first standard for heterogeneous data access across databases, though it didn't create table abstractions.

Further, IBM's DB2 DataJoiner (~1995) was more ambitious with a middleware product enabling SQL queries across Oracle, Sybase, SQL Server, Informix, Teradata, and even VSAM files through a unified interface. With SQL Server 7.0's Linked Servers (1998) we got federated querying to Microsoft's ecosystem via OLE DB, supporting cross-database joins with four-part naming conventions.

Most of these implementations shared a common limitation that Oracle (9i Release 1 - 9.0.1 in 2001) solved: they focused on querying other databases or required middleware. Oracle's abstraction treated local flat files as first-class read-only table objects using the familiar CREATE TABLE ... ORGANIZATION EXTERNAL DDL syntax, providing a simple way to define external files as part of normal table creation and allowing ORACLE_LOADER access to query flat files (CSV, fixed-width, delimited) through DBAs.

It was an early way of separating declaration from compute (the Oracle loaders).

When Internal and When External Data? What's the Limit of External?

The tradeoffs come down to how often the data is queried, e.g. the hot versus cold question.

The tradeoffs and considerations you should make when wanting to use them come down to the decision of how often the data is queried. The table below shows it in more detail:

In the era of data lake and lakehouse architectures, this is an important consideration. VSCO says: "disk space was growing more quickly than our compute needs," which is what triggered the adoption of external tables.

If you look at your use case, if you need to do analytics across various sources with joins and augmentation of your data at an enterprise, you probably want to focus on loading data into your database or data warehouse, an architectural pattern that has survived more than 30 years. But if you have data that is external and small but you want to join it with existing data, or you always need fresh data and can live with a slower response time (maybe because it runs during the night), you might use external tables.

In any case, external tables are a good approach to keep in mind and a valuable toolkit to have.

They Work Well with Existing Tech and Common Patterns

Obviously, today's external tables are not the same as the earliest ones in Microsoft Access, but the principle of accessing data outside your system is still the same. Nowadays we have more support, new formats besides CSV and JSON. We can do Parquet or open table formats.

As mentioned, they work well with related long-lasting data warehouse patterns and applications such as materialized views and stored procedures. The recurring pattern is to access external data with your data management system, similar to the pattern of materialized views that refresh complex SQL statements and make them fast, and stored procedures that run glue code within your database.

Moreover, there are temporary tables that are similar but only available during a transaction or session. They all work in the same Lindy effect, e.g., Databricks just announced Temporary table support recently on December 9th, 2025, or Databricks SQL Stored Procedure a little earlier, August 14th, 2025, for reusing existing SQL statements.

Again and again, everything that is old will be new again. Exactly what the Lindy Effect is all about. We can clearly say that the Lindy effect over the last 33 years applies here. The longer something is in place, the more likely it is to be around for at least that long.

How a Classical External Table Works

To understand how traditional external tables work, let's first look at Oracle, which has built an extensive syntax around them and where they still work this way today.

First, we can create a place for external data called DIRECTORIES, which is simply a pointer or alias to a file system location where external files already exist:

Copy code
CREATE OR REPLACE DIRECTORY admin_dat_dir
    AS '/flatfiles/data';

This directory can point to local file systems, NFS mounts, or even cloud object storage today (with the ORACLE_BIGDATA driver for S3, OCI, Azure). The DIRECTORIES don't require moving data, though you could prepare those files via ETL pipelines or third-party tools, or they can be generated directly by applications.

We can now create an external table based on this directory, e.g., log files, bad data that we store externally, JSON files, and make data accessible inside the INFORMATION_SCHEMA and with plain SQL, as if it were internal.

Creating an external table:

Copy code
CREATE TABLE admin_ext_employees
                   (employee_id       NUMBER(4), 
                    first_name        VARCHAR2(20),
                    last_name         VARCHAR2(25), 
                    job_id            VARCHAR2(10),
                    manager_id        NUMBER(4),
                    hire_date         DATE,
                    salary            NUMBER(8,2),
                    commission_pct    NUMBER(2,2),
                    department_id     NUMBER(4),
                    email             VARCHAR2(25) 
                   ) 
     ORGANIZATION EXTERNAL 
     ( 
       TYPE ORACLE_LOADER 
       DEFAULT DIRECTORY admin_dat_dir  --notice this dir with above
       ACCESS PARAMETERS 
       ( 
         records delimited by newline 
         badfile admin_bad_dir:'empxt%a_%p.bad' 
         logfile admin_log_dir:'empxt%a_%p.log' 
         fields terminated by ',' 
         missing field values are null 
         ( employee_id, first_name, last_name, job_id, manager_id, 
           hire_date char date_format date mask "dd-mon-yyyy", 
           salary, commission_pct, department_id, email 
         ) 
       ) 
       LOCATION ('empxt1.dat', 'empxt2.dat') 
     ) 
     PARALLEL 
     REJECT LIMIT UNLIMITED; 

The first and most important choice is TYPE, which determines the access driver and what kind of files you can read: ORACLE_LOADER for plain text files like CSV or logs (read-only), ORACLE_DATAPUMP for Oracle binary dump files, ORACLE_BIGDATA for cloud object stores like S3 or OCI in formats like Parquet or Avro, and ORACLE_HIVE for Hadoop/Hive data. The DEFAULT DIRECTORY points to a server-side path alias, and LOCATION names the actual file(s), with wildcard support (*.dat) so you can load a whole batch at once.

The ACCESS PARAMETERS block is where you control parsing: row and field delimiters, null handling, custom date format masks, and where to write bad rows (badfile) and parse logs (logfile). On top of that, PARALLEL lets Oracle split file reading across multiple processes for large files, and REJECT LIMIT controls fault tolerance. Set it to UNLIMITED to skip bad rows silently, or 0 to fail immediately on the first error.

You see lots of built-in features that we can use compared to building a full-fledged data pipeline. Instead of exporting and importing CSVs from the source databases or developing a complex CDC pipeline that traditionally looked something like: source OLTP --> CSVs --> IDW (reports on yesterday) -> ingest into DWH for long-term analytics, we can just define a table based on external data and access it as part of our pipeline.

Integrated into Warehouses

Most modern warehouses - Snowflake, Redshift Spectrum, BigQuery, Athena, Synapse - come with a simplified version of CREATE EXTERNAL TABLE. Compared to the Oracle example, the schema is usually inferred from the file format (especially Parquet), S3 or another object store is the default backing location, and the parsing ceremony disappears. The pseudo-code looks roughly like this across engines:

Copy code
-- Pseudo-code: modern external table over Parquet on S3
CREATE EXTERNAL TABLE sales
WITH (
  LOCATION = 's3://my-bucket/sales/',
  FORMAT = 'PARQUET'
);

Object storage like S3, GCS, and Azure Blob has become the first-class citizen for external data. From here, the ecosystem layers on: dbt wraps this in YAML, DuckDB skips the DDL entirely in favor of schema-on-read, and open table formats add transactional guarantees on top.

External Tables with dbt?

On top of this base SQL form, dbt adds a YAML layer and can be used with its own package called dbt-external-tables. It's one of the most-used dbt packages, though it seems less actively maintained now.

The external table is defined via YAML, and there are lots of options to set, with the most important being external and its location, but also defining columns in different ways such as inference or the meta tag:

Copy code
version: 2

sources:
  - name: snowplow
    tables:
      - name: event
        description: >
            This source table is actually a set of files in external storage.
            The dbt-external-tables package provides handy macros for getting
            those files queryable, just in time for modeling.
                            
        external:
          location:         # required: S3 file path, GCS file path, Snowflake stage, Synapse data source
          ...               # database-specific properties of external table
          partitions:       # optional
            - name: collector_date
              data_type: date
              ...           # database-specific properties

        # Specify ALL column names + datatypes.
        # Column order must match for CSVs, column names must match for other formats.
        # Some databases support schema inference.

        columns:
          - name: app_id
            data_type: varchar(255)
            description: "Application ID"
          - name: platform
            data_type: varchar(255)
            description: "Platform"
          ...

        # Use `meta` to pass custom column properties (e.g. alias, expression)
        columns:
          - name: raw_timestamp
            data_type: timestamp
            config:
              meta:
                alias: event_timestamp       # rename the column in the external table
                expression: TO_TIMESTAMP(...) # custom SQL expression instead of default value extraction

This is a nice improvement over the ODBC GUI interface. It's not exactly an apples-to-apples comparison as dbt itself is not a database, but with its supported destinations such as Redshift (Spectrum), Snowflake, BigQuery, Spark, Synapse, and Azure SQL, you see that it will persist in these destinations, mostly data warehouses.

DuckDB with dbt

If you use dbt, you can also use DuckDB with dbt via dbt-duckdb, which is more up-to-date. But DuckDB is not an external table, right?

Yes, DuckDB doesn't have CREATE EXTERNAL TABLE syntax yet, mostly because it is an in-memory database, but you can achieve the same functionality through other means. DuckDB can not only be used as a database but also as a zero-copy SQL connector (see all categories at 5 Key Categories). We can just point it to an external source, as shown above with dbt. The difference is that DuckDB is both a database and a compute engine, making ad-hoc reads possible directly without a DDL definition, similar to an external table with Oracle loaders. With dbt, we can nicely declare this in dbt configs.

With DuckDB, you can query "external data" extremely fast over HTTPS or locally in formats such as Parquet, CSV, and many more, so the need for formal external tables is reduced since DuckDB does schema on read.

If you want to define the database schema ahead of time, we'd use external tables to do that and effectively have schema on write (though we don't write, just define the DDL table structure and data types), which is more of the classical ETL approach.

Here's an example with external_location to read external data with dbt:

Copy code
sources:
  - name: external_source
    config:
      external_location: "s3://my-bucket/my-sources/{name}.parquet"
    tables:
      - name: source1

Read more at Fully Local Data Transformation with dbt and DuckDB.

Other options are with database views that are supported in DuckDB with CREATE VIEW over read_parquet(). You can ship a .duckdb file to clients with pre-defined views over S3 data, so clients don't need to know about the underlying data, Hive partitioning, or even glob patterns — very similar to what a formal CREATE EXTERNAL TABLE would do.

Copy code
CREATE VIEW events AS
  SELECT * FROM read_parquet('s3://lake/events/*.parquet', hive_partitioning=true);

Or similarly use ATTACH to directly point to Postgres, MySQL, SQLite, S3, and others:

Copy code
-- Postgres (binary wire protocol, predicate + projection pushdown, read+write)
INSTALL postgres; LOAD postgres;
ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS pg (TYPE postgres);
ATTACH 'postgresql://user@host/db' AS pg (TYPE postgres, READ_ONLY);

-- MySQL (via MariaDB Connector/C; Postgres-style keyvalue string even for MySQL — easy trap)
INSTALL mysql; LOAD mysql;
ATTACH 'host=localhost user=root port=0 database=mysql' AS mdb (TYPE mysql);

-- SQLite (file opens directly; multi-reader single-writer by SQLite file locks)
INSTALL sqlite; LOAD sqlite;
ATTACH 'sakila.db' (TYPE sqlite);

-- Generic remote DuckDB file
ATTACH 's3://duckdb-blobs/databases/stations.duckdb' AS stations_db;

Open Table Formats and Lakehouse Architecture

That begs the question of whether Open Table Formats are the next evolution and modern way of external tables. These table formats allow almost any SQL compute engine to use them as external tables, and read, compute, and aggregate as a database would.

If we look at what table formats consist of, they're built on object storage, with a file format like Parquet, and then we have a manifest file that contains a list of files that unifies multiple single files into a "single" table, looking from the outside.

So again, the manifest file is our pointer or fancier symlink, but it lives next to the data, unlike external tables. There's much more going on in table formats, but if we have a data lake with open table format tables, we can see how we define tables in DDL and the pointers are to different files (Parquet, ORC, Avro), in most cases Parquet.

More broadly, we can say external tables decouple storage from compute. Open table formats decouple the table itself (schema, history, transactions, statistics) from any single engine.

Lakehouse and Connecting to DuckLake

One step further is obviously a lakehouse architecture, with the shift from format-agnostic file reading to governed, transactional, multi-engine open table formats.

If you extend the external table idea to a lakehouse architecture, these external tables with open table formats provide essentially what databases provide with ACID guarantees, time travel, schema evolution, partition evolution, and fine-grained access control, but for files.

But with the difference that data stays in open Parquet file format on customer-owned cloud storage. The external table, once a humble workaround for avoiding data loads, has become the architectural foundation of the data lakehouse if you like this analogy.

With DuckLake, we have the next evolution just around the corner, bringing back exactly that missing database, especially to handle all the metadata of such a lakehouse and all its files. This means having durable and consistent database storage for our manifest files.