Query Fivetran-Managed Apache Iceberg™ Tables from DuckDB

This tutorial explains how to query Fivetran-managed Apache Iceberg™ tables directly from DuckDB.

Every Managed Data Lake Service destination includes a dedicated Fivetran Catalog by default. DuckDB connects to the Fivetran Catalog through the Iceberg REST catalog protocol, discovers available namespaces and tables, and reads the underlying table data from cloud storage.

Before you begin

Before you configure DuckDB to query the Iceberg tables, review the following considerations.

Verify your DuckDB version

Use DuckDB v1.2.0 or later. DuckDB v1.2.0 introduced support for Google Cloud Storage (GCS). Earlier versions support Amazon S3-backed data lakes but cannot query GCS-backed data lakes.

To check the installed DuckDB version, run:

duckdb --version

If DuckDB is not installed, download it from the DuckDB website.

Understand cloud provider support

The DuckDB Iceberg extension supports the following cloud storage providers:

If your data lake uses Azure Data Lake Storage (ADLS), verify that the DuckDB version you are using supports ADLS before you continue.

Reduce cloud egress costs

DuckDB reads table data directly from the cloud storage location behind your Managed Data Lake Service destination.

If your DuckDB environment runs in a different cloud provider or region from your data lake, your storage provider may charge data egress fees.

To reduce or avoid egress costs, run your DuckDB environment in the same cloud provider and region as your data lake.

Save your OAuth client secret

The OAuth client secret for your catalog is displayed only once in the Fivetran dashboard. Make sure you save it before leaving the page. If you lose it, you must regenerate it. However, regenerating the client secret immediately invalidates the previous value.

Find your catalog credentials

1. Log in to your Fivetran account.

2. Go to the Destinations page and select your Managed Data Lake Service destination.

3. Go to Catalog integration > Base configuration.

4. Make a note of the following values:

   • Server endpoint: The Fivetran Catalog endpoint (for example, https://pack-dictate.us-west-2.aws.polaris.fivetran.com/api/catalog)
   • Catalog: The name of the Fivetran Catalog associated with your data lake
   • Client ID: The OAuth client ID
   • Client secret: The OAuth client secret
   Fivetran displays the client secret only once. Save it before you leave the page. If you lose the secret, click Regenerate client secret to invalidate the previous secret and generates a new one.

Set up DuckDB

Install required extensions

DuckDB requires the following extensions to query Iceberg tables through a REST catalog:

• httpfs: Enables DuckDB to read files from cloud storage over HTTP
• iceberg: Enables DuckDB to query Iceberg tables and connect to Iceberg REST catalogs.

In a DuckDB session, run the following statements:

INSTALL httpfs FROM core;
INSTALL iceberg FROM core;
LOAD httpfs;
LOAD iceberg;

Create persistent secret for Fivetran Catalog

Create a persistent OAuth secret so DuckDB can authenticate with the Fivetran Catalog. A persistent secret is stored locally and is automatically available in future DuckDB sessions, so you only need to create it once on each machine.

Replace the placeholder values with the credentials that you copied from the Fivetran dashboard, then run the following statement:

CREATE OR REPLACE PERSISTENT SECRET polaris_secret (
    TYPE iceberg,
    CLIENT_ID '<oauth_client_id>',
    CLIENT_SECRET '<oauth_client_secret>',
    OAUTH2_SCOPE 'PRINCIPAL_ROLE:ALL',
    OAUTH2_SERVER_URI '<server_endpoint>/v1/oauth/tokens'
);

Append /v1/oauth/tokens to the Server endpoint value from the Fivetran dashboard and use the result as the value for OAUTH2_SERVER_URI.

Attach Fivetran Catalog

Attach the Fivetran Catalog to DuckDB. Replace the placeholder values with the Catalog name, Server endpoint, and cloud region from the Fivetran dashboard, then run the following statement:

ATTACH '<catalog_name>' AS fivetran_lakehouse (
    TYPE ICEBERG,
    ENDPOINT '<server_endpoint>',
    SECRET polaris_secret,
    DEFAULT_REGION '<cloud_region>'
);

Replace the placeholders as follows:

• <catalog_name>: The Catalog value from the Fivetran dashboard.
• <server_endpoint>: The Server endpoint value from the Fivetran dashboard.
• <cloud_region>: The region of your data lake. Use the second part of the server endpoint hostname. For example, if the server endpoint is pack-dictate.us-west-2.aws.polaris.fivetran.com, enter us-west-2.

After DuckDB attaches the catalog, it automatically discovers all schemas and Fivetran-managed Iceberg tables in the catalog.

Query Iceberg tables

Explore catalog

1. List all tables available in the attached catalog:

   SELECT table_schema, table_name
   FROM information_schema.tables
   WHERE table_catalog = 'fivetran_lakehouse';
   

2. Inspect the table's schema:

   DESCRIBE fivetran_lakehouse.<schema_name>.<table_name>;
   

Query tables

Query tables using fully qualified names in the following format: fivetran_lakehouse.<schema_name>.<table_name>

SELECT *
FROM fivetran_lakehouse.<schema_name>.<table_name>
LIMIT 100;

Replace <schema_name> with your Fivetran connection schema name and <table_name> with the name of the table you want to query.

Additional considerations

Fivetran Catalog is read-only

DuckDB attaches the Fivetran Catalog in read-only mode. You cannot create, modify, or delete schemas or Fivetran-managed tables through the catalog. All updates must go through Fivetran.

OAuth client secret rotation

If you regenerate the OAuth client secret in Fivetran, your existing DuckDB secret stops working immediately. Queries for your Fivetran-managed Apache Iceberg™ tables will fail until you drop and recreate the persistent secret in DuckDB with the new value.

Reserved Iceberg column names

Fivetran prefixes reserved Iceberg column names with a hash symbol (#) to avoid conflicts.

The affected column names are:

• _deleted
• _file
• _partition
• _pos
• _spec_id
• file_path
• pos
• row

If your source data includes any of these names, DuckDB displays them with a # prefix.

Timestamp type behavior

Fivetran maps:

• UTC timestamps (INSTANT) to Iceberg TIMESTAMPTZ
• Timezone-naive timestamps (LOCALDATETIME) to Iceberg TIMESTAMP

DuckDB renders these types as TIMESTAMPTZ and TIMESTAMP, respectively. Keep this in mind when filtering, comparing, or transforming timestamp columns.

Summary

To query Fivetran-managed Iceberg tables from DuckDB:

1. Verify DuckDB v1.2.0 or later is installed.
2. Copy your catalog credentials from Catalog integration > Base configuration in the Fivetran dashboard.
3. Install and load the httpfs and iceberg extensions.
4. Create a persistent OAuth secret with CREATE PERSISTENT SECRET.
5. Attach the Fivetran Catalog using ATTACH ... TYPE ICEBERG.
6. Query tables using fivetran_lakehouse.<schema_name>.<table_name>.

For more information, see the Fivetran Catalog Integration Guide or contact Fivetran Support.

Legal notices

Apache Iceberg is a trademark of the Apache Software Foundation.