Query Fivetran-Managed Apache Iceberg™ Tables from Python

This tutorial explains how to query Fivetran-managed Apache Iceberg™ tables from Python using PyIceberg.

Every Managed Data Lake Service destination includes a dedicated Fivetran Catalog by default. PyIceberg 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 PyIceberg to query the Iceberg tables, review the following considerations.

Install PyIceberg

Install PyIceberg using pip:
```
pip install pyiceberg
```

Verify the installation:

python -c "import pyiceberg; print(pyiceberg.__version__)"

Understand cloud storage access

When PyIceberg connects to the Fivetran Catalog through the Iceberg REST catalog protocol, it requests vended credentials by default. The Fivetran Catalog returns temporary, short-lived cloud storage credentials scoped to the tables you read. PyIceberg uses these credentials to access the underlying Parquet files in your data lake. You do not need to configure separate S3, Google Cloud Storage, or Azure Data Lake Storage credentials in your Python environment.

Reduce cloud egress costs

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

If your Python 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 Python 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

Log in to your Fivetran account.
Go to the Destinations page and select your Managed Data Lake Service destination.
Go to Catalog integration > Base configuration.
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.

Connect to the Fivetran Catalog

Load catalog

Use load_catalog to connect PyIceberg to the Fivetran Catalog. Replace the placeholder values with the credentials you copied from the Fivetran dashboard:

from pyiceberg.catalog import load_catalog

catalog = load_catalog(
    "fivetran_catalog",
    **{
        "type": "rest",
        "uri": "<server_endpoint>",
        "credential": "<oauth_client_id>:<oauth_client_secret>",
        "warehouse": "<catalog_name>",
        "scope": "PRINCIPAL_ROLE:ALL",
    }
)

Explore catalog

List all namespaces (connection schemas) available in the catalog:
```
catalog.list_namespaces()
```
List all tables in a namespace:
```
catalog.list_tables("<schema_name>")
```

Load table

Load a table by its namespace and name:

table = catalog.load_table("<schema_name>.<table_name>")

To inspect the table's schema:
```
print(table.schema())
```

Read data

Read into Pandas DataFrame

df = table.scan(limit=100).to_pandas()

Read into PyArrow table

arrow_table = table.scan(limit=100).to_arrow()

For large tables, stream the data in batches to avoid loading everything into memory at once:

for batch in table.scan().to_arrow_batch_reader():
    print(f"Batch contains {len(batch)} rows")

Filter and project data

PyIceberg pushes filters and column selections down to the scan, so only matching files and columns are read from cloud storage.

Filter rows

Pass a filter expression as a string using standard comparison operators:

df = table.scan(
    row_filter="_fivetran_synced > '2024-01-01T00:00:00+00:00'",
    limit=100
).to_pandas()

For boolean columns or more complex filters, use the expression DSL:

from pyiceberg.expressions import And, GreaterThanOrEqual, NotEqualTo

df = table.scan(
    row_filter=And(
        GreaterThanOrEqual("_fivetran_synced", "2024-01-01T00:00:00+00:00"),
        NotEqualTo("_fivetran_deleted", True)
    ),
    limit=100
).to_pandas()

PyIceberg string filter parser supports comparison operators (>, >=, <, <=, =, !=) but does not support IS TRUE or IS NOT TRUE. Use the expression DSL for boolean column filters.

Select specific columns

Use selected_fields to read only the columns you need:

df = table.scan(
    selected_fields=("id", "name", "created_at", "_fivetran_synced"),
    limit=100
).to_pandas()

Other output formats

PyIceberg can output scan results directly to several other frameworks:

# DuckDB relation
duck_relation = table.scan().to_duckdb(table_name="my_table")

# Polars DataFrame
polars_df = table.scan().to_polars()

# Ray Dataset
ray_dataset = table.scan().to_ray()

Additional considerations

Fivetran catalog is read-only

PyIceberg connects to 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 load_catalog call will fail with an authentication error. Update the credential parameter with the new secret to restore access.

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, they will appear with a # prefix in PyIceberg results.

Timestamp type behavior

Fivetran maps:

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

PyIceberg surfaces these as timezone-aware and timezone-naive Python datetime objects, respectively. Keep this in mind when filtering or comparing timestamp columns.

Summary

To query Fivetran-managed Iceberg tables from Python using PyIceberg:

Install PyIceberg with pip install pyiceberg.
Copy your catalog credentials from Catalog integration > Base configuration in the Fivetran dashboard.
Connect to the Fivetran Catalog using load_catalog with type: rest.
Explore namespaces and tables with list_namespaces() and list_tables().
Read data using table.scan().to_pandas() or table.scan().to_arrow().
Use row_filter and selected_fields to reduce data read from cloud storage.

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.