lakehouse-explorer

name	lakehouse-explorer
description	Explore and query your Fivetran Managed Data Lake Service (MDLS). As part of MDLS, Fivetran manages a Polaris catalog and the underlying Apache Iceberg tables where your connector data lands — your fully managed lakehouse. This skill gives you an agentic interface to explore that data (read-only in the current MDLS release). Use for ANY question about your MDLS tables: schemas, namespaces, row counts, sync status, data freshness, partitioning, snapshots, or running queries against your Iceberg data. Trigger on: "what tables do we have", "explore my lakehouse", "query the lakehouse", "check sync status", "how fresh is our data", "what's in MDLS", "describe this table", "how many rows", "show me our Iceberg tables".
metadata	{"short-description":"Query Fivetran-managed Iceberg tables via DuckDB and Polaris, cost-efficiently"}
user-invocable	true
argument-hint	<question about your MDLS tables, schemas, or data>
allowed-tools	bash(duckdb, curl, python3, pip)

Lakehouse Explorer Skill

Setup

Copy lakehouse_config.json.example to .lakehouse_config.json in this skill's directory and fill in your credentials. All fields must be non-empty strings.

Your MDLS credentials can be found in the Fivetran UI under Destinations → your MDLS destination → Catalog Integration.

Prerequisites

DuckDB CLI (required)

Requires the DuckDB CLI binary (>=1.2.0). Test with duckdb --version. If missing, install from https://duckdb.org/docs/installation/.

DuckDB 1.2 is the minimum because GCS (GCP-backed MDLS destinations) support was added in that release. AWS/S3-backed destinations work on 1.1.x, but 1.2+ is required for GCP and is the safe floor across all MDLS destinations.

pyiceberg (optional — Column Bounds Inspection only)

Required only for the Column Bounds Inspection section. Install with:

pip install pyiceberg

---

Start of Every Session

Step 1 — Verify DuckDB.

duckdb --version

If the command fails, inform the user DuckDB is not installed and link to https://duckdb.org/docs/installation/.

Step 2 — Read .lakehouse_config.json from the skill's base directory and validate that all fields are populated.

The skill base directory is injected at load time (shown at the top of this prompt). Read the config from <skill_base_dir>/.lakehouse_config.json. Every field must have a non-empty string value before this skill can proceed.

If any field is an empty string "", stop immediately and ask the user to populate it:

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

Do not proceed until all fields are confirmed populated. Once validated, extract and use these values throughout the session:

Config key	Used for
polaris.polaris_client_id	Polaris OAuth client ID
polaris.polaris_client_secret	Polaris OAuth client secret
polaris.polaris_endpoint	Polaris REST catalog base URL (e.g. https://pack-dictate.us-west-2.aws.polaris.fivetran.com/api/catalog)
polaris.polaris_warehouse	Warehouse name used in Polaris REST URL paths and DuckDB ATTACH

Three values are derived automatically — do not ask the user for them:

• polaris_oauth_uri = polaris_endpoint + /v1/oauth/tokens
  • e.g. https://pack-dictate.us-west-2.aws.polaris.fivetran.com/api/catalog/v1/oauth/tokens
• cloud_region = second label of the polaris_endpoint hostname
  • e.g. us-west-2 from pack-dictate.us-west-2.aws.polaris.fivetran.com
  • e.g. us-east4 from nibble-assessing.us-east4.gcp.polaris.fivetran.com
• cloud_provider = third label of the polaris_endpoint hostname
  • e.g. aws from pack-dictate.us-west-2.aws.polaris.fivetran.com
  • e.g. gcp from nibble-assessing.us-east4.gcp.polaris.fivetran.com
  • e.g. azure from something.eastus.azure.polaris.fivetran.com

Never hardcode these values. Always source them from .lakehouse_config.json.

Step 2b — Validate cloud provider support.

After deriving cloud_provider, check it against the list of backends supported by DuckDB's Iceberg extension before proceeding:

Provider	Supported?
aws	Yes — S3 and S3 Tables
gcp	Yes — GCS (added in DuckDB v1.2+)
azure	Unknown — check DuckDB docs

If cloud_provider is azure, fetch the current DuckDB Iceberg REST catalog docs to check whether ADLS support has been added:

https://duckdb.org/docs/current/core_extensions/iceberg/iceberg_rest_catalogs

• If the docs confirm Azure/ADLS is not yet supported, inform the user and do not attempt any DuckDB queries:

  "This lakehouse is backed by Azure Data Lake Storage (ADLS), which is not yet supported by DuckDB's Iceberg extension. DuckDB currently supports S3 (AWS) and GCS (GCP) only. See: https://duckdb.org/docs/current/core_extensions/iceberg/iceberg_rest_catalogs"

• If the docs confirm Azure/ADLS is now supported, proceed with the session and note to the user that Azure support has been added since this skill was last updated.

Step 3 — Verify Polaris REST connectivity.

Using the Standard Call Pattern below, acquire a token and list namespaces:

GET {polaris_endpoint}/v1/{polaris_warehouse}/namespaces

If this fails, check polaris_endpoint, polaris_client_id, and polaris_client_secret in .lakehouse_config.json.

Token lifetime: Polaris tokens expire (typically 1 hour). If a REST call returns 401 Unauthorized, re-acquire using the same pattern and retry.

---

Polaris REST API — Standard Call Pattern

Polaris tokens are valid for ~1 hour. Rather than re-acquiring on every call, cache to /tmp and reuse. The snippet below checks the cache age (Python os.path.getmtime is cross-platform) and only hits the token endpoint when the cached token is stale. Include this at the top of every bash script that makes REST calls, then use $POLARIS_TOKEN freely for all calls in that script.

POLARIS_TOKEN=$(python3 - << 'PYEOF'
import os, time, json, urllib.request, urllib.parse
cache = "/tmp/polaris_token_{polaris_warehouse}"
if os.path.exists(cache) and time.time() - os.path.getmtime(cache) < 3000:
    print(open(cache).read().strip())
else:
    data = urllib.parse.urlencode({
        "grant_type": "client_credentials",
        "client_id": "{polaris_client_id}",
        "client_secret": "{polaris_client_secret}",
        "scope": "PRINCIPAL_ROLE:ALL"
    }).encode()
    req = urllib.request.Request("{polaris_oauth_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 $POLARIS_TOKEN" \
  "<endpoint URL>" | python3 -m json.tool

Token cache file is named /tmp/polaris_token_{polaris_warehouse} so multiple warehouses on the same machine don't collide. TTL is 3000 seconds (50 min) — conservative against the 1-hour Polaris expiry.

---

DuckDB Session Setup

Every DuckDB invocation is a fresh in-memory database. The CREATE SECRET step triggers an OAuth token acquisition on first catalog access. To avoid repeating that cost across many queries, use a persistent secret (stored to ~/.duckdb/stored_secrets/): DuckDB auto-loads it in every subsequent session so you can drop CREATE SECRET from routine query scripts.

First-time setup — create the persistent secret once

Run this once per machine (or whenever credentials rotate). It stores the OAuth2 config to disk so all future sessions auto-load it.

cat > /tmp/lakehouse_setup.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
CREATE OR REPLACE PERSISTENT SECRET polaris_secret (
    TYPE iceberg,
    CLIENT_ID '<polaris_client_id>',
    CLIENT_SECRET '<polaris_client_secret>',
    OAUTH2_SCOPE 'PRINCIPAL_ROLE:ALL',
    OAUTH2_SERVER_URI '<polaris_oauth_uri>'
);
TEMPLATE
duckdb < /tmp/lakehouse_setup.sql

The persistent secret stores the OAuth2 configuration, not the bearer token itself. DuckDB still acquires a fresh bearer token per session automatically — you just don't need to re-declare the secret in every script.

Bash invocation pattern (after persistent secret is set up)

cat > /tmp/lakehouse_query.sql << 'TEMPLATE'
INSTALL httpfs FROM core; LOAD httpfs;
INSTALL iceberg FROM core; LOAD iceberg;
ATTACH '<polaris_warehouse>' AS fivetran_lakehouse (
    TYPE ICEBERG,
    ENDPOINT '<polaris_endpoint>',
    SECRET polaris_secret,
    DEFAULT_REGION '<cloud_region>'
);

<your SQL here>
TEMPLATE

duckdb < /tmp/lakehouse_query.sql

If the persistent secret hasn't been set up yet, add the CREATE OR REPLACE PERSISTENT SECRET block before the ATTACH — it will create it and persist it for all future sessions in the same invocation.

DuckDB catalog name

The catalog is always attached as fivetran_lakehouse (the AS fivetran_lakehouse alias in the ATTACH statement). Use this as the catalog prefix in all SQL queries:

DESCRIBE fivetran_lakehouse.<namespace>.<table>;
SELECT COUNT(*) FROM fivetran_lakehouse.<namespace>.<table>;

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

---

Core Principle

Polaris REST catalog first. Always. No exceptions.

Every DuckDB query reads Parquet files from cloud storage via vended credentials — that costs money and can crash the machine if the dataset is large. The Polaris REST API returns catalog metadata with zero data egress. Most questions can be answered entirely from the catalog. Only fall through to DuckDB when you genuinely need row-level data that the catalog cannot provide.

---

Catalog Name Reference — Critical

There are two different catalog identifiers in use. Using the wrong one causes 400/404 errors. Never confuse them.

Context	Value	Source
Polaris REST API calls (URL path)	{polaris_warehouse} from config	polaris_warehouse in .lakehouse_config.json
DuckDB queries	fivetran_lakehouse	Hardcoded alias from the ATTACH ... AS fivetran_lakehouse statement — never changes

---

Catalog Discovery — Always Do This First

Never assume the catalog structure. Namespaces, tables, and schemas vary by environment and change over time. Always discover live state from Polaris before answering any question about tables or writing any query. Acquire a single token and make all required calls in one script:

# Acquire or reuse cached token (see Standard Call Pattern)
POLARIS_TOKEN=$(python3 - << 'PYEOF'
import os, time, json, urllib.request, urllib.parse
cache = "/tmp/polaris_token_{polaris_warehouse}"
if os.path.exists(cache) and time.time() - os.path.getmtime(cache) < 3000:
    print(open(cache).read().strip())
else:
    data = urllib.parse.urlencode({"grant_type":"client_credentials","client_id":"{polaris_client_id}","client_secret":"{polaris_client_secret}","scope":"PRINCIPAL_ROLE:ALL"}).encode()
    req = urllib.request.Request("{polaris_oauth_uri}", data=data)
    token = json.loads(urllib.request.urlopen(req).read())["access_token"]
    open(cache,"w").write(token)
    print(token)
PYEOF
)

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

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

# 3. Get full table metadata (schema, partitions, snapshots) — do this before writing any query
curl -s -H "Authorization: Bearer $POLARIS_TOKEN" \
  "{polaris_endpoint}/v1/{polaris_warehouse}/namespaces/<namespace>/tables/<table>" | python3 -m json.tool

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

---

Three-Tier Decision Process

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

---

Tier 1 — Polaris REST API (zero cloud storage egress)

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

All calls follow the standard call pattern above.

Endpoint reference

List namespaces

GET {polaris_endpoint}/v1/{polaris_warehouse}/namespaces

Get namespace properties

GET {polaris_endpoint}/v1/{polaris_warehouse}/namespaces/<namespace>

List tables in a namespace

GET {polaris_endpoint}/v1/{polaris_warehouse}/namespaces/<namespace>/tables

Get full table metadata (schema, partitions, snapshots, stats, storage location)

GET {polaris_endpoint}/v1/{polaris_warehouse}/namespaces/<namespace>/tables/<table>

List catalog roles

GET {polaris_endpoint}/management/v1/catalogs/{polaris_warehouse}/catalogRoles

List principals

GET {polaris_endpoint}/management/v1/principals

What table metadata contains (no cloud storage read required)

When you call GET …/tables/<table>, Polaris returns the full Iceberg table metadata JSON, which includes:

• Schema: every column name, type, nullability, field ID
• Partition spec: which columns partition the table and how
• Sort order: clustering keys if any
• Snapshots: full history of every commit — timestamp, operation (append/overwrite/delete), added/deleted file counts, added/deleted record counts, total records, total files, total size in bytes
• Current snapshot summary: total-records, total-files-size, total-data-files, total-delete-files — all available without touching cloud storage
• Table properties: custom key/value metadata
• Storage location: the base path of the table in cloud storage

Row counts, file sizes, and record counts are available directly from the catalog — never run a COUNT(*) if the catalog already has it.

Per-file column bounds (in manifest files, not the table metadata JSON)

Fivetran writes per-file lower_bounds and upper_bounds for every column into the Iceberg manifest Avro files at sync time. These are not in the table metadata JSON returned by the Polaris REST API — they live inside the manifest files referenced by the snapshot's manifest-list.

• For tables with ≤200 columns, Fivetran writes bounds for all columns
• For larger tables: _fivetran_synced, all primary key columns, and history mode columns (_fivetran_active, _fivetran_start, _fivetran_end)

DuckDB reads these bounds automatically when planning a query — it skips entire Parquet files whose column bounds fall outside your filter predicate. This is called column bound pruning and happens even on unpartitioned tables. You will see it in the EXPLAIN ANALYZE output as optional: Dynamic Filter (<column>) in the TABLE_SCAN node.

Practical implication: filtering on _fivetran_synced, primary key columns, or any column with a wide value spread can skip many files even without partitioning. Always filter on these columns when possible to reduce #GET count.

To inspect the actual bound values for a table, use the pyiceberg approach in the Column Bounds Inspection section below.