Snowflake Federated Queries

Federated queries in Snowflake enable querying data from external databases and data sources without copying or moving the data.

Federated Query Definition

Federated Query Capabilities

Snowflake provides several mechanisms to query data from external sources without ingesting it first.

External Tables

External tables allow direct querying of files in cloud storage (S3, GCS, Azure Blob) without copying data into Snowflake. The metadata is stored in Snowflake but the data remains in the external location.

CREATE OR REPLACE EXTERNAL TABLE my_external_table
    WITH LOCATION = @my_s3_stage/data/
    FILE_FORMAT = (TYPE = PARQUET)
    AUTO_REFRESH = TRUE;

Query an external table:

SELECT *
FROM my_external_table
WHERE date_column >= '2025-01-01';

Iceberg Tables

Apache Iceberg tables in Snowflake provide access to open-format data lakes with full ACID transaction support. You can read Iceberg tables from external catalogs.

CREATE OR REPLACE EXTERNAL CATALOG my_iceberg_catalog
    CATALOG_TYPE = ICEBERG
    TABLE_FORMAT = ICEBERG
    LOCATION = 's3://my-bucket/iceberg/'
    CATALOG_NAMESPACE = 'my_namespace';

CREATE OR REPLACE EXTERNAL TABLE my_iceberg_table
    CATALOG = my_iceberg_catalog
    DATABASE = my_database
    SCHEMA = public
    TABLE = my_iceberg_table;

SELECT * FROM my_iceberg_table WHERE year = 2025;

External Functions

External functions call REST APIs or serverless functions from within Snowflake SQL, enabling integration with any external service.

CREATE OR REPLACE EXTERNAL FUNCTION call_external_api(input_data VARCHAR)
    RETURNS VARIANT
    HTTP_METHOD = 'POST'
    API_INTEGRATION = my_api_integration
    URL = 'https://api.example.com/process';

Query using the external function:

SELECT customer_id,
       call_external_api(customer_data) AS api_response
FROM customers;

Performance Considerations

Federated queries have different performance characteristics than native Snowflake queries. Consider these factors when designing federated queries.

• Data locality: External tables require reading data from remote storage, which adds network latency compared to local tables
• Filter pushdown: Snowflake pushes filter predicates to external sources where possible, reducing data transfer
• Caching: External table results are cached in Snowflake's result cache, so repeated identical queries are fast
• Concurrency: Federated queries consume warehouse credits the same way as local queries
• Data volume: Large external table scans can be expensive; use partition pruning to limit scanned data

Comparison: Federated Query vs COPY vs Snowpipe

See Also

• Cross-Platform Integration - Connecting Snowflake with other platforms and services
• Semi-Structured Data - Working with JSON, Avro, and Parquet data in Snowflake
• Iceberg Tables - Deep dive into Apache Iceberg support in Snowflake
• Data Sharing - Sharing data securely across organizations with Snowflake