Skip to content

Instantly share code, notes, and snippets.

@dataders

dataders/SKILL.md Secret

Last active June 14, 2026 13:06
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dataders/43789cf55713f86987ab5e17a32bdd1a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dataders/43789cf55713f86987ab5e17a32bdd1a to your computer and use it in GitHub Desktop.
Download ZIP
lakehouse explorer dev agent skill. credit: Jack Lowery (jack.lowery@fivetran.com)
Raw
SKILL.md
name iceberg-explorer
description Explore and query any Apache Iceberg table exposed through an Iceberg REST catalog — Polaris/Snowflake Open Catalog, Lakekeeper, Databricks Unity Catalog, Nessie, AWS Glue, S3 Tables, Gravitino, or any spec-compliant implementation. This skill gives you an agentic, cost-aware interface to explore that data (read-only). Use for ANY question about Iceberg tables: schemas, namespaces, row counts, freshness, partitioning, snapshots, or running queries. Trigger on: "what tables do we have", "explore my lakehouse/catalog", "query the iceberg catalog", "check data freshness", "describe this iceberg table", "how many rows", "show me our iceberg tables".
metadata
team owner short-description
sales-engineering
Jack Lowery <jack.lowery@fivetran.com>
Query any Iceberg REST catalog via DuckDB, cost-efficiently
user-invocable true
argument-hint <question about your Iceberg tables, schemas, or data>
allowed-tools bash(duckdb, curl, python3, pip, aws)

Iceberg Explorer Skill

Explore and query Apache Iceberg tables through any Iceberg REST catalog, cost-efficiently. The skill is catalog-agnostic: it speaks the standard Iceberg REST protocol and supports three authentication modes, so it works with managed services (Snowflake Polaris/Open Catalog, Databricks Unity, Fivetran MDLS), open-source catalogs (Lakekeeper, Nessie, Gravitino), and AWS-native catalogs (Glue, S3 Tables).

Setup

Fill in .iceberg_config.json in this skill's directory before using the skill. The required fields depend on your auth_type (see the table below). Every field you need for your chosen auth_type must be a non-empty string.

Config schema (.iceberg_config.json)

{
  "catalog_uri": "",
  "warehouse": "",
  "catalog_alias": "iceberg_catalog",
  "auth_type": "oauth2",

  "oauth2_client_id": "",
  "oauth2_client_secret": "",
  "oauth2_scope": "",
  "oauth2_server_uri": "",

  "token": "",

  "aws_region": "",
  "sigv4_signing_name": "",
  "endpoint_type": "",

  "storage_region": ""
}
Config key Required for Meaning
catalog_uri all (except pure sigv4 Glue/S3 Tables, which use endpoint_type) REST catalog base URL, e.g. https://my-org.us-west-2.aws.polaris.fivetran.com/api/catalog, http://localhost:8181/catalog (Lakekeeper), https://<workspace>.cloud.databricks.com/api/2.1/unity-catalog/iceberg (Unity)
warehouse most Warehouse / catalog identifier used as the REST prefix and in DuckDB ATTACH. For Glue this is the AWS account id; for S3 Tables it's the table-bucket ARN
catalog_alias all DuckDB ATTACH ... AS <alias> name. Defaults to iceberg_catalog. This is the catalog prefix in all SQL
auth_type all One of oauth2, bearer, sigv4
oauth2_client_id / oauth2_client_secret oauth2 Client-credentials pair
oauth2_scope oauth2 (optional) Scope string. Polaris/Open Catalog use PRINCIPAL_ROLE:ALL; many catalogs use catalog or leave it blank
oauth2_server_uri oauth2 Token endpoint. For Polaris-style catalogs this is {catalog_uri}/v1/oauth/tokens. For catalogs that delegate to an external IdP (e.g. Unity, some Lakekeeper setups), use that IdP's token URL
token bearer Pre-issued bearer token / PAT
aws_region sigv4 AWS region of the Glue/S3 Tables endpoint
sigv4_signing_name sigv4 (optional) glue or s3tables. Usually inferred from endpoint_type
endpoint_type sigv4 DuckDB ENDPOINT_TYPE: glue or s3_tables
storage_region optional Region for vended object-storage credentials (DEFAULT_REGION in DuckDB). For sigv4 defaults to aws_region

Auth-type cheat sheet

  • oauth2 — Polaris / Snowflake Open Catalog, Fivetran MDLS, Lakekeeper (OAuth mode), Unity (when fronted by client-credentials), Nessie with OAuth.
  • bearer — any catalog where you hold a static bearer token / PAT (some Lakekeeper, Nessie, Unity PAT setups).
  • sigv4 — AWS Glue Iceberg REST and S3 Tables, signed with AWS SigV4 via the default credential chain.

Never overwrite .iceberg_config.json — it holds the user's credentials.

Prerequisites

DuckDB CLI (required)

Requires the DuckDB CLI (>=1.3.0 recommended). Test with duckdb --version. Install from https://duckdb.org/docs/installation/.

Version floor by backend: GCS-backed storage needs >=1.2; AWS Glue / S3 Tables ENDPOINT_TYPE support stabilized around 1.3. Use 1.3+ to be safe across every backend.

AWS CLI (only for auth_type: sigv4)

The sigv4 REST tier and credential checks use aws. Test with aws sts get-caller-identity.

pyiceberg (optional — Column Bounds Inspection and sigv4 REST tier)

pip install "pyiceberg[pyarrow]"

Start of Every Session

Step 1 — Verify DuckDB.

duckdb --version

If it fails, tell the user DuckDB is not installed and link https://duckdb.org/docs/installation/.

Step 2 — Read .iceberg_config.json from the skill's base directory and validate.

The skill base directory is injected at load time (top of this prompt). Read <skill_base_dir>/.iceberg_config.json. Validate that every field required for the configured auth_type is a non-empty string. If any required field is empty, stop and ask the user to fill it in:

"Your .iceberg_config.json is missing values for: <list of blank required fields> (required for auth_type: <type>). Please fill them in and let me know when it's ready."

Do not proceed until validated. Source every value from the config — never hardcode.

Step 3 — Verify catalog connectivity.

The check differs by auth_type:

  • oauth2 / bearer — use the Standard REST Call Pattern to hit the spec-standard config endpoint, which validates auth and returns the canonical prefix/defaults:

    GET {catalog_uri}/v1/config?warehouse={warehouse}

    Then list namespaces:

    GET {catalog_uri}/v1/{warehouse}/namespaces

  • sigv4 — REST calls require SigV4 signing, which curl can't do cleanly. Verify with the AWS CLI instead:

    aws sts get-caller-identity
# Glue:
aws glue get-catalog-import-status 2>/dev/null || aws glue get-databases --max-results 1
# S3 Tables:
aws s3tables list-table-buckets --region {aws_region}

If connectivity fails, re-check catalog_uri, warehouse, and the auth fields for the configured auth_type.

Token lifetime (oauth2): tokens typically expire in ~1 hour. On 401, re-acquire (the cached-token pattern below handles this) and retry.

Standard REST Call Pattern

The metadata-first principle (below) relies on cheap REST calls. How you make them depends on auth_type.

oauth2 — cache the token, reuse it

Tokens are valid ~1 hour. Cache to /tmp and reuse. Include this at the top of any bash script that makes REST calls, then use $ICEBERG_TOKEN freely.

ICEBERG_TOKEN=$(python3 - << 'PYEOF'
import os, time, json, urllib.request, urllib.parse, hashlib
key = hashlib.md5("{warehouse}|{catalog_uri}".encode()).hexdigest()[:12]
cache = f"/tmp/iceberg_token_{key}"
if os.path.exists(cache) and time.time() - os.path.getmtime(cache) < 3000:
    print(open(cache).read().strip())
else:
    params = {"grant_type": "client_credentials",
              "client_id": "{oauth2_client_id}",
              "client_secret": "{oauth2_client_secret}"}
    scope = "{oauth2_scope}"
    if scope:
        params["scope"] = scope
    data = urllib.parse.urlencode(params).encode()
    req = urllib.request.Request("{oauth2_server_uri}", data=data)
    token = json.loads(urllib.request.urlopen(req).read())["access_token"]
    open(cache, "w").write(token)
    print(token)
PYEOF
)

curl -s -H "Authorization: Bearer $ICEBERG_TOKEN" "<endpoint URL>" | python3 -m json.tool

Cache file is keyed by a hash of warehouse|catalog_uri so multiple catalogs on one machine don't collide. TTL 3000s (50 min), conservative vs the 1-hour expiry.

bearer — use the static token directly

ICEBERG_TOKEN="{token}"
curl -s -H "Authorization: Bearer $ICEBERG_TOKEN" "<endpoint URL>" | python3 -m json.tool

sigv4 — no curl; use pyiceberg or the AWS CLI

SigV4 request signing in shell is error-prone. For metadata, prefer:

  • pyiceberg loaded with rest-sigv4 auth (see Catalog Discovery), or
  • the native AWS CLI (aws glue get-table …, aws s3tables get-table …), or
  • DuckDB's iceberg_metadata() on the attached catalog (Tier 2, but metadata-only operations read little).

DuckDB Session Setup

Every DuckDB invocation is a fresh in-memory database. Use a persistent secret (stored to ~/.duckdb/stored_secrets/) so DuckDB auto-loads credentials in every later session and you can drop the CREATE SECRET from routine scripts.

The secret + ATTACH form depends on auth_type.

First-time setup — create the persistent secret once

oauth2:

cat > /tmp/iceberg_setup.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
CREATE OR REPLACE PERSISTENT SECRET iceberg_secret (
    TYPE ICEBERG,
    CLIENT_ID '{oauth2_client_id}',
    CLIENT_SECRET '{oauth2_client_secret}',
    OAUTH2_SCOPE '{oauth2_scope}',
    OAUTH2_SERVER_URI '{oauth2_server_uri}'
);
TEMPLATE
duckdb < /tmp/iceberg_setup.sql

If oauth2_scope is blank, omit the OAUTH2_SCOPE line entirely.

bearer:

cat > /tmp/iceberg_setup.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
CREATE OR REPLACE PERSISTENT SECRET iceberg_secret (
    TYPE ICEBERG,
    TOKEN '{token}'
);
TEMPLATE
duckdb < /tmp/iceberg_setup.sql

sigv4: the storage/credential secret is an AWS secret using the default credential chain (no token to persist):

cat > /tmp/iceberg_setup.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
CREATE OR REPLACE PERSISTENT SECRET aws_secret (
    TYPE s3,
    PROVIDER credential_chain,
    REGION '{aws_region}'
);
TEMPLATE
duckdb < /tmp/iceberg_setup.sql

Query invocation pattern (after the secret exists)

oauth2 / bearer:

cat > /tmp/iceberg_query.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
ATTACH '{warehouse}' AS {catalog_alias} (
    TYPE ICEBERG,
    ENDPOINT '{catalog_uri}',
    SECRET iceberg_secret,
    DEFAULT_REGION '{storage_region}'
);

<your SQL here>
TEMPLATE

duckdb < /tmp/iceberg_query.sql

sigv4 — Glue:

ATTACH '{warehouse}' AS {catalog_alias} (TYPE ICEBERG, ENDPOINT_TYPE 'glue');

sigv4 — S3 Tables ({warehouse} is the table-bucket ARN):

ATTACH '{warehouse}' AS {catalog_alias} (TYPE ICEBERG, ENDPOINT_TYPE 's3_tables');

If the persistent secret isn't set up yet, prepend the matching CREATE OR REPLACE PERSISTENT SECRET block before the ATTACH — it creates and persists in the same invocation.

DuckDB catalog name

The catalog is always attached as {catalog_alias}. Use it as the prefix in all SQL:

DESCRIBE {catalog_alias}.<namespace>.<table>;
SELECT COUNT(*) FROM {catalog_alias}.<namespace>.<table>;

Do not use {warehouse} as the SQL catalog prefix — that value is only used in ATTACH and in REST URL paths.

Core Principle

Catalog metadata first. Always. No exceptions.

Every DuckDB data query reads Parquet from object storage via vended/assumed credentials — that costs money and can crash the machine on large datasets. The Iceberg REST API (or iceberg_metadata() for sigv4) returns catalog metadata with zero data egress. Most questions are answerable from metadata alone. Only fall through to a data scan when you genuinely need row-level data the catalog can't provide.

Catalog Name Reference — Critical

Two different identifiers. Using the wrong one causes 400/404 errors.

Context Value Source
REST API calls (URL path prefix) {warehouse} warehouse in config (or the prefix returned by /v1/config)
DuckDB SQL queries {catalog_alias} The ATTACH ... AS {catalog_alias} alias — never changes

Nested namespaces: the Iceberg REST spec joins multi-level namespaces with the unit-separator char (%1F, URL-encoded) in paths, e.g. /v1/{warehouse}/namespaces/a%1Fb/tables. In DuckDB SQL they appear as {catalog_alias}.a.b.<table> — wrap dotted parts in double quotes if needed.

Catalog Discovery — Always Do This First

Never assume catalog structure. Discover live state before answering anything about tables or writing any query.

oauth2 / bearer (curl)

# Acquire/reuse token per the Standard REST Call Pattern, then:

# 1. List namespaces
curl -s -H "Authorization: Bearer $ICEBERG_TOKEN" \
  "{catalog_uri}/v1/{warehouse}/namespaces" | python3 -m json.tool

# 2. List tables in a namespace
curl -s -H "Authorization: Bearer $ICEBERG_TOKEN" \
  "{catalog_uri}/v1/{warehouse}/namespaces/<namespace>/tables" | python3 -m json.tool

# 3. Full table metadata (schema, partitions, snapshots) — before writing any query
curl -s -H "Authorization: Bearer $ICEBERG_TOKEN" \
  "{catalog_uri}/v1/{warehouse}/namespaces/<namespace>/tables/<table>" | python3 -m json.tool

sigv4 (pyiceberg)

from pyiceberg.catalog import load_catalog
cat = load_catalog("rest", **{
    "type": "rest",
    "uri": "{catalog_uri}",            # Glue/S3 Tables REST URI
    "warehouse": "{warehouse}",
    "rest.sigv4-enabled": "true",
    "rest.signing-name": "{sigv4_signing_name}",   # "glue" or "s3tables"
    "rest.signing-region": "{aws_region}",
})
print(cat.list_namespaces())
for ns in cat.list_namespaces():
    print(ns, cat.list_tables(ns))
# Full metadata:
t = cat.load_table("<namespace>.<table>")
print(t.schema(), t.spec(), t.metadata.snapshots[-1].summary)

Never guess column names, partition columns, or row counts — fetch them first.

What table metadata contains (no storage read)

loadTable returns the full Iceberg metadata JSON:

  • Schema — every column name, type, nullability, field id
  • Partition spec — partition columns and transforms
  • Sort order — clustering keys if any
  • Snapshots — full commit history: timestamp, operation, added/deleted file & record counts, totals
  • Current snapshot summary — total-records, total-files-size, total-data-files, total-delete-files
  • Table properties and storage location

Row counts, file sizes, and record counts come straight from the catalog — never run COUNT(*) if the snapshot summary already has it.

Per-file column bounds (in manifest Avro, not the loadTable JSON)

Iceberg writers record per-file lower_bounds/upper_bounds in the manifest Avro files (referenced by the snapshot's manifest-list), not in the loadTable JSON. DuckDB reads these automatically when planning, skipping Parquet files whose bounds fall outside your filter — column bound pruning, visible in EXPLAIN ANALYZE as Dynamic Filter (<col>) in the TABLE_SCAN node. This works even on unpartitioned tables.

Practical implication: filtering on columns with a wide value spread (timestamps, primary keys, audit columns like _fivetran_synced / updated_at if your writer adds them) skips many files. See Column Bounds Inspection to inspect actual bound values.

Three-Tier Decision Process

Work through these in order. Stop at the first tier that can answer.

Tier 1 — Catalog metadata (zero storage egress)

Use for: namespaces, table lists, schemas, types, partition specs, sort orders, snapshot history, table properties, file counts, table-level stats, catalog structure.

oauth2/bearer: curl the REST endpoints below. sigv4: pyiceberg or aws CLI.

Standard Iceberg REST endpoints (oauth2/bearer):

GET {catalog_uri}/v1/config?warehouse={warehouse}                                  # catalog defaults + prefix
GET {catalog_uri}/v1/{warehouse}/namespaces                                        # list namespaces
GET {catalog_uri}/v1/{warehouse}/namespaces/<namespace>                            # namespace properties
GET {catalog_uri}/v1/{warehouse}/namespaces/<namespace>/tables                     # list tables
GET {catalog_uri}/v1/{warehouse}/namespaces/<namespace>/tables/<table>            # full table metadata

These five are part of the Iceberg REST spec and portable across catalogs. Vendor-specific endpoints (roles, principals, grants) are not — see the Vendor Appendix.

Tier 2 — DuckDB data scan with EXPLAIN ANALYZE gate (storage egress incurred)

Only when the question needs actual row data the catalog can't provide.

Mandatory three-step process — no exceptions

Step 1: Run plain EXPLAIN first — free, zero egress. Inspect the logical plan: are your filters in the TABLE_SCAN node? Only needed columns projected? Right structure? If not, rewrite and re-EXPLAIN until clean.

Step 2: Run EXPLAIN ANALYZE to get real execution stats.

EXPLAIN ANALYZE actually executes the query. The stats are real, not estimates — the storage cost has already been incurred. Running the real query afterward costs the same again.

Step 3: Present the plan report and wait for explicit confirmation.

📊 Query Plan Report
─────────────────────────────────────
⚠️  EXPLAIN ANALYZE executes the query — these are real numbers. The storage
    cost below has already been incurred. Running the real query costs the same again.

Query:           <the SQL you intend to run>

HTTPFS Stats:
  Data read:     <in: X KiB/MiB/GiB>
  HTTP GETs:     <#GET count>  ← number of object-storage file reads

Execution:
  Total time:    <Xs>
  Files scanned: <Total Files Read: N>
  Rows scanned:  <rows at TABLE_SCAN node>
  Rows returned: <rows at top node>

Plan quality:
  Filters pushed down:    <filters in TABLE_SCAN, or "none">
  Projections only:       <columns in TABLE_SCAN Projections>
  Partition pruning:      <yes/no — files scanned vs total files>
  Column bound pruning:   <yes/no — "Dynamic Filter (<col>)" in TABLE_SCAN>
  Small files warning:    <flag if data-files / records ratio > 1 file per 100 rows>

Shall I run this query? Running it incurs the cost above a second time. (yes / no / suggest alternative)

Execute the real query only after explicit confirmation. On "no", revise → re-EXPLAIN ANALYZE → present a new report.

Reading EXPLAIN ANALYZE output

  • HTTPFS HTTP Stats — in: X bytes = actual storage transferred; #GET = file reads. Zero bytes = metadata only.
  • TABLE_SCAN node — Total Files Read, applied Filters, Projections. Filters present = pushdown worked.
  • Dynamic Filter (<col>) — column bound pruning active (distinct from partition pruning, works unpartitioned).
  • Row counts per node and Total Time (wall clock incl. storage latency).

Good vs bad plans

Signal Good Bad
Files Read 1–3 >10
Filters in TABLE_SCAN Present Absent (full scan)
Dynamic Filter Present Absent
Projections Only needed columns All (SELECT *)
Data in (HTTPFS) KiB MiB/GiB
#GET 1–10 >50 (even if data is small — small-files problem)
files / rows ratio <1 per 100 rows >1 per 100 rows

High #GET with small data = file fragmentation. Recommend compaction/OPTIMIZE, not a query rewrite.

Mandatory query rules

  1. Fully qualified names: {catalog_alias}.<namespace>.<table>
  2. Confirm schema from Tier 1 first — know columns and partitions before writing SQL
  3. Always LIMIT any row-returning SELECT — hard cap 100
  4. Prefer aggregations (COUNT, GROUP BY, MIN, MAX, AVG) over fetching rows
  5. Filter on partition or bound columns — check the partition spec from Tier 1; if unpartitioned, filter on a high-cardinality column (timestamp, primary key, or an audit column like _fivetran_synced/updated_at if present) to trigger column bound pruning
  6. Filter soft-deletes when present — e.g. WHERE _fivetran_deleted IS NOT TRUE (Fivetran) or your writer's equivalent. Only apply if the column exists in the schema you fetched
  7. Never scan large tables without a partition or time filter — check row counts and partitions from Tier 1 first
  8. Select only needed columns — never SELECT * unless explicitly asked

Safe query patterns

-- Aggregation (safe)
SELECT <group_col>, COUNT(*) AS n
FROM {catalog_alias}.<namespace>.<table>
GROUP BY <group_col> ORDER BY n DESC;

-- Filtered sample (safe — only needed columns)
SELECT <col1>, <col2>
FROM {catalog_alias}.<namespace>.<table>
WHERE <partition_col> = '<value>'
LIMIT 20;

DuckDB quirks for Iceberg catalogs

  • Use DESCRIBE {catalog_alias}.<ns>.<table> — not information_schema.columns (returns UNKNOWN types for Iceberg)
  • Use information_schema.tables — not SHOW TABLES/SHOW SCHEMAS
  • duckdb_tables() doesn't return estimated_size for Iceberg tables
  • Always prefix {catalog_alias}. — unqualified names fail
  • iceberg_metadata({catalog_alias}.<ns>.<table>) returns per-file info (manifest/file paths, record counts) on the attached catalog. It does not expose per-column bounds — use pyiceberg for those
  • parquet_metadata('<storage://path>') does not work — it bypasses the catalog secret and hits storage with no/anonymous creds (403 / access denied). Use iceberg_metadata() or pyiceberg

Tier 3 — Rewrite or refuse (query is fundamentally unsafe)

If EXPLAIN ANALYZE shows an unsafe plan even after rewriting (no filters available, millions of rows, GiB-range read), do not present it. Instead:

  1. Explain why it's unsafe (files scanned, data volume)
  2. Offer a safe alternative (aggregation, filtered subset, catalog metadata)
  3. Ask whether to proceed with the alternative

Unsafe patterns: full scan of a large table with no partition filter; log tables with no time filter; multi-table joins with no selective filter on either side.

Decision Flowchart

Question received
       │
       ▼
Read .iceberg_config.json → validate fields for auth_type → extract values
       │
       ▼
Can catalog metadata answer this?
(schema, columns, partitions, snapshots, row counts, file sizes, properties)
       │
      YES ──► Tier 1 (REST curl | pyiceberg | aws CLI) → Answer   (zero egress)
       NO
       │
       ▼
Write best DuckDB query (partition filters, projections, aggregations, LIMIT)
       │
       ▼
Run EXPLAIN  ← free, zero egress
       │
Plan clean? ──NO──► rewrite → EXPLAIN again
       │YES
       ▼
Run EXPLAIN ANALYZE  ← executes, real cost incurred
       │
       ▼
Present Query Plan Report → user confirms?
       │
      YES ──► run real query → present results
      NO  ──► revise → EXPLAIN → EXPLAIN ANALYZE → repeat
       │
   Fundamentally unsafe even after rewrite
       └──► explain why → offer safe alternative → ask to proceed

Response Format

  • Lead with the answer, not the tool call or SQL.
  • State which tier: "From catalog metadata — no storage read." / "Ran aggregation on [table] ([N] rows scanned)."
  • Show SQL / curl after the answer, not before.
  • Tabular results → markdown table (cap 20 rows). Bytes → human-readable. Schema → clean column/type table.

Column Bounds Inspection

Diagnostic tool only. Reads manifest Avro from storage (real latency on large tables). Use to understand which filter values enable file skipping or to verify pruning effectiveness — not routinely.

Why this is non-trivial

Per-file bounds are binary-encoded in manifest Avro, not in the loadTable JSON. Three approaches fail before one works:

  1. DuckDB parquet_metadata('<storage://path>') — bypasses the catalog secret, hits storage unauthenticated. 403 / access denied.
  2. DuckDB iceberg_metadata(...) — returns file paths and record counts, but not lower_bounds/upper_bounds.
  3. pyiceberg straight — table.scan().plan_files() exposes DataFile.lower_bounds/.upper_bounds directly and works for standard catalogs. It only breaks if your writer emits a custom puffin blob type pyiceberg doesn't recognize (e.g. Fivetran's fivetran-synced-distribution), which fails Pydantic validation on table load. The HTTP-intercept below works around that.

Decode helper + (optional) custom-blob intercept

The intercept is only needed for catalogs whose writer emits non-standard puffin blobs (Fivetran MDLS is the known case). For Polaris, Lakekeeper, Unity, Nessie, Glue, S3 Tables, etc., skip the patch.object(...) wrapper and call plan_files() directly.

import json, struct, requests
from datetime import datetime, timezone
from unittest.mock import patch

# Standard puffin blob types pyiceberg understands. If your writer emits others,
# they get stripped here so table load succeeds. (Only needed for Fivetran MDLS.)
KNOWN_BLOBS = ("apache-datasketches-theta-v1", "deletion-vector-v1")

def _strip_unknown_blobs(resp):
    if "tables" in resp.url and resp.status_code == 200:
        try:
            data = resp.json()
            for s in data.get("metadata", {}).get("statistics", []):
                s["blob-metadata"] = [b for b in s.get("blob-metadata", []) if b.get("type") in KNOWN_BLOBS]
            resp._content = json.dumps(data).encode()
        except Exception:
            pass
    return resp

original_get = requests.Session.get
def patched_get(self, url, **kwargs):
    return _strip_unknown_blobs(original_get(self, url, **kwargs))

def decode_bound(typ, b):
    if not b: return "(null)"
    if typ in ("timestamptz", "timestamp"):
        micros = struct.unpack_from("<q", b)[0]
        return datetime.fromtimestamp(micros/1_000_000, tz=timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")
    if typ == "boolean": return "true" if b == b'\x01' else "false"
    if typ == "int":     return str(struct.unpack_from("<i", b)[0])
    if typ == "long":    return str(struct.unpack_from("<q", b)[0])
    if typ == "double":  return str(struct.unpack_from("<d", b)[0])
    try: return b.decode("utf-8").rstrip("\x00")
    except: return repr(b)

def load_catalog_for_bounds():
    from pyiceberg.catalog import load_catalog
    # oauth2 / bearer:
    return load_catalog("explorer", **{
        "type": "rest",
        "uri": "{catalog_uri}",
        "warehouse": "{warehouse}",
        "credential": "{oauth2_client_id}:{oauth2_client_secret}",   # oauth2
        # "token": "{token}",                                        # bearer (use instead of credential)
        "scope": "{oauth2_scope}",                                   # omit if blank
    })
    # sigv4: swap the dict for the rest.sigv4-enabled / signing-name / signing-region form shown in Catalog Discovery.

Pattern A — single-file tables (per-file detail)

# Wrap in `with patch.object(requests.Session, "get", patched_get):` ONLY for Fivetran MDLS; otherwise run directly.
catalog = load_catalog_for_bounds()
table = catalog.load_table("<namespace>.<table>")
schema = table.schema()
field_map = {f.field_id: f.name for f in schema.fields}
type_map  = {f.field_id: str(f.field_type) for f in schema.fields}

print(f"{'column':<25} {'type':<15} {'min':<40} {'max':<40}")
print("-"*120)
for task in table.scan().plan_files():
    df = task.file
    lo, hi = df.lower_bounds or {}, df.upper_bounds or {}
    for fid in sorted(set(list(lo)+list(hi))):
        typ = type_map.get(fid, "?")
        print(f"{field_map.get(fid, fid):<25} {typ:<15} {decode_bound(typ, lo.get(fid, b'')):<40} {decode_bound(typ, hi.get(fid, b'')):<40}")

Pattern B — multi-file tables (aggregated, skippability-focused)

FILTER_COLUMN = "<column you plan to filter on>"
FILTER_VALUE_STR = "<decoded value matching decode_bound output>"

catalog = load_catalog_for_bounds()
table = catalog.load_table("<namespace>.<table>")
schema = table.schema()
field_map = {f.field_id: f.name for f in schema.fields}
type_map  = {f.field_id: str(f.field_type) for f in schema.fields}

from collections import defaultdict
col_mins, col_maxs, total = defaultdict(list), defaultdict(list), 0
for task in table.scan().plan_files():
    df = task.file; total += 1
    lo, hi = df.lower_bounds or {}, df.upper_bounds or {}
    for fid in set(list(lo)+list(hi)):
        typ = type_map.get(fid, "?")
        col_mins[fid].append(decode_bound(typ, lo.get(fid, b"")))
        col_maxs[fid].append(decode_bound(typ, hi.get(fid, b"")))

print(f"Total files: {total}\n")
print(f"{'column':<25} {'type':<15} {'global_min':<35} {'global_max':<35} {'skippable':>14}")
print("-"*128)
filter_fid = next((fid for fid, n in field_map.items() if n == FILTER_COLUMN), None)
for fid in sorted(col_mins):
    name, typ = field_map.get(fid, f"field_{fid}"), type_map.get(fid, "?")
    g_min, g_max = min(col_mins[fid]), max(col_maxs[fid])
    skip = "-"
    if fid == filter_fid:
        skip = f"{sum(1 for mx in col_maxs[fid] if mx < FILTER_VALUE_STR)}/{total}"
    print(f"{name:<25} {typ:<15} {g_min:<35} {g_max:<35} {skip:>14}")
print(f"\nFor WHERE {FILTER_COLUMN} >= '{FILTER_VALUE_STR}', DuckDB can skip files whose file_max < the filter value.")

What the output tells you

  • Wide min↔max spread — good distribution; filtering skips many files.
  • Narrow spread (min ≈ max) — clustered; filtering won't skip much.
  • Boolean flag constant across files — filtering on it skips everything instantly for the opposite value.
  • Skippable count — the core metric: e.g. 350/410 skippable means the real query reads 60 files, slashing #GET and bytes.

Troubleshooting

Problem Solution
duckdb: command not found Install from https://duckdb.org/docs/installation/
401 Unauthorized (oauth2) Token expired/stale — delete /tmp/iceberg_token_* and retry; the cache pattern re-acquires
401 Unauthorized (bearer) token is wrong or expired — refresh it in .iceberg_config.json
403/AccessDenied (sigv4) Check aws sts get-caller-identity, IAM perms for Glue/S3 Tables, and aws_region
400 Bad Request on REST warehouse doesn't match the catalog prefix — confirm via GET /v1/config?warehouse=…
404 on table metadata List namespaces/tables first — never guess names. Check nested-namespace %1F encoding
DuckDB iceberg extension not found INSTALL iceberg FROM core; manually
Secret "iceberg_secret"/"aws_secret" already exists Persistent secret already loaded. DROP PERSISTENT SECRET <name> if creds changed, then re-run first-time setup
Storage credential/region errors Set storage_region (or aws_region for sigv4) correctly
GCS errors despite correct creds Need DuckDB >=1.2 (GCS added then)
Glue/S3 Tables ATTACH fails Need DuckDB >=1.3; confirm endpoint_type is glue/s3_tables and {warehouse} is the account id / table-bucket ARN
Azure / ADLS errors DuckDB Iceberg ADLS support is evolving — check https://duckdb.org/docs/stable/core_extensions/iceberg/iceberg_rest_catalogs
parquet_metadata() returns 403 Expected — it bypasses the catalog secret. Use iceberg_metadata() or pyiceberg
pyiceberg ValidationError on a puffin blob (e.g. fivetran-synced-distribution) Your writer emits a non-standard blob type. Use the HTTP-intercept in Column Bounds Inspection to strip it before validation
High #GET, small data Small-files problem — recommend compaction/OPTIMIZE, not a query rewrite

Vendor Appendix

These endpoints are not part of the Iceberg REST spec — they only work on specific catalogs. Use only when you know the backend.

Polaris / Snowflake Open Catalog (management API)

GET {catalog_uri}/management/v1/catalogs/{warehouse}/catalogRoles     # list catalog roles
GET {catalog_uri}/management/v1/principals                            # list principals

OAuth scope for Polaris-family catalogs is PRINCIPAL_ROLE:ALL; the token endpoint is {catalog_uri}/v1/oauth/tokens.

Fivetran MDLS (Managed Data Lake Service)

A Polaris-backed oauth2 catalog. Conventions worth knowing:

  • Audit columns: _fivetran_synced, _fivetran_deleted, and (history mode) _fivetran_active, _fivetran_start, _fivetran_end. Filter WHERE _fivetran_deleted IS NOT TRUE; filter/sort on _fivetran_synced for cheap column-bound pruning.
  • Writes per-file bounds for all columns on tables ≤200 columns; for larger tables only _fivetran_synced, primary keys, and history-mode columns.
  • Emits a custom fivetran-synced-distribution puffin blob — needs the HTTP-intercept in Column Bounds Inspection.
  • Hostname encodes region/provider, e.g. pack-dictate.us-west-2.aws.polaris.fivetran.com → storage_region=us-west-2, provider aws. Set storage_region accordingly.

AWS Glue / S3 Tables

sigv4 catalogs. The REST tier is awkward over curl — prefer pyiceberg (rest.sigv4-enabled) or the aws glue / aws s3tables CLIs for metadata, and DuckDB ENDPOINT_TYPE 'glue'/'s3_tables' for queries.

Reference Files

  • .iceberg_config.json — user-populated catalog credentials. Read from the skill base directory. Validate the fields required for auth_type are non-empty before proceeding. Never overwrite it.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment