lakehouse-explorer

# 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](#column-bounds-inspection) section. Install with: ```bash pip install pyiceberg ``` --- ## Start of Every Session **Step 1 — Verify DuckDB.** ```bash 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](#polaris-rest-api--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. ```bash 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. ```bash 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) ```bash 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: ```sql 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: ```bash # 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](#polaris-rest-api--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](#column-bounds-inspection) section below.