Using Duckdb
Query Fabric lakehouse and warehouse data using DuckDB, either locally or inside a...
Install
npx skills add data-goblin/power-bi-agentic-development --skill using-duckdb -g -a claude-code -y- Repository
- data-goblin/power-bi-agentic-development1.1k
- Category
- Engineering
- Updated
- 5d ago
- License
- GPL-3.0
- Context cost
- Lightabout 1,076 tokens
- Runs scripts
- No, instructions only
What it does
Query Fabric lakehouse and warehouse data using DuckDB, either locally or inside a Fabric notebook. Automatically invoke when the user mentions "DuckDB", "query Delta tables locally", or asks to "attach DuckDB to a lakehouse", "query OneLake data", "explore lakehouse data", "data freshness check", "validate data quality", "use DuckDB in Fabric".
Using DuckDB with Fabric
Query Delta Lake tables and raw files in OneLake using DuckDB. Works both locally (CLI/Python) and inside Fabric notebooks. Read-only; for writes, use the executing-spark skill.
Two Modes
| Mode | Where it runs | Auth | Best for |
|---|---|---|---|
| Local | Developer machine | Azure CLI (az login) | Exploration, validation, ad-hoc analysis |
| In-notebook | Fabric Spark container | notebookutils.credentials.getToken('storage') | Combining DuckDB speed with Spark write-back |
Local: Prerequisites
- DuckDB installed (
brew install duckdbon macOS) - Azure CLI authenticated (
az login) - Extensions installed:
INSTALL delta; INSTALL azure;(one-time)
Local: Querying Delta Tables
WS_ID=$(fab get "Workspace.Workspace" -q "id" | tr -d '"')
LH_ID=$(fab get "Workspace.Workspace/LH.Lakehouse" -q "id" | tr -d '"')
duckdb -c "
LOAD delta; LOAD azure;
CREATE SECRET (TYPE azure, PROVIDER credential_chain, CHAIN 'cli');
SELECT * FROM delta_scan(
'abfss://${WS_ID}@onelake.dfs.fabric.microsoft.com/${LH_ID}/Tables/schema/table'
) LIMIT 10;
"
The CHAIN 'cli' parameter uses Azure CLI credentials. Without it, DuckDB tries managed identity first (fails on local machines).
Local: Querying Raw Files
BASE="abfss://${WS_ID}@onelake.dfs.fabric.microsoft.com/${LH_ID}/Files"
duckdb -c "
LOAD azure;
CREATE SECRET (TYPE azure, PROVIDER credential_chain, CHAIN 'cli');
SELECT * FROM read_csv('${BASE}/data.csv') LIMIT 10;
SELECT * FROM read_parquet('${BASE}/facts.parquet') LIMIT 10;
SELECT * FROM read_json('${BASE}/events/*.json');
"
Glob patterns (*, **) work for reading multiple files.
In-Notebook: Attaching DuckDB to a Lakehouse
Inside a Fabric notebook, DuckDB can query lakehouse Delta tables directly using a storage token. This approach is faster than Spark SQL for analytical queries on single-node data.
import duckdb
import time
