# 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:

```sql
-- 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.

***