Foreign Tables

---

Foreign Tables (SQL/MED)

A Foreign Table is a database object that acts as a virtual pointer to a table in an external data source. It allows you to query data from other systems—such as different databases, flat files, or cloud storage—using your current database's SQL without physically moving the data.

How it Works

1. Foreign Data Wrapper (FDW): A library or "driver" that translates SQL commands from your local engine into a language the external system understands.

2. Foreign Server: An object that defines the connection details (host, port, credentials) for the external source.

3. Foreign Table Definition: A schema created in your local database that mirrors the structure of the remote data, linking it to the foreign server.

Key Benefits

• Zero ETL: Avoids complex data migration, storage duplication, and the need for a "Load" step.

• Real-time Access: Queries always fetch the most current data directly from the source.

• Unified Querying: You can perform JOINs between a local table and an external S3 file using standard SQL.

• Cost-Effective: Reduces storage costs because data isn't duplicated across systems.

---

Implementation and Syntax

The syntax follows the SQL/MED (Management of External Data) standard. While the concept is universal, the keywords vary by provider.

Example: PostgreSQL (The Standard Syntax)

To query a local CSV file as if it were a SQL table:

-- 1. Setup the "driver"
CREATE EXTENSION file_fdw;
CREATE SERVER local_files FOREIGN DATA WRAPPER file_fdw;

-- 2. Create the virtual pointer
CREATE FOREIGN TABLE ext_taxi_trips (
    vendor_id INT,
    pickup_datetime TIMESTAMP,
    fare_amount NUMERIC
) 
SERVER local_files
OPTIONS ( filename '/data/trips.csv', format 'csv', header 'true' );

Provider Variations

Database	Feature Name	Common Use Case
PostgreSQL	FOREIGN TABLE	Connecting to Postgres, MySQL, or CSVs.
Snowflake	EXTERNAL TABLE	Querying Parquet/JSON files in S3 or Azure.
BigQuery	EXTERNAL TABLE	Querying data in Google Cloud Storage or Drive.
SQL Server	PolyBase	Querying Hadoop, Azure Blob, or MongoDB.

---

Use Cases and Trade-offs

Common Scenarios

• Data Lakehouse: Querying massive files in cloud storage (S3/GCS) without importing them.

• Data Federation: Integrating a central data warehouse with various operational databases.

• Legacy Migration: Accessing data incrementally from old systems during a transition.

The "Catch" (Important for DEs)

• Performance: Foreign tables are almost always slower than local tables because data must travel over a network or be parsed from a file on the fly.

• Dependency: If the external file is moved or the remote server goes down, your local query will fail immediately.

• Optimization: Look for Pushdown support. A "smart" foreign table will send your WHERE filters to the external source so it only transfers the rows you actually need.

---