powerbi-duckdb-lakehouse

DuckDB over lakehouse and OneLake data

DuckDB is an in-process SQL engine that reads Parquet and Delta directly. Pointed at OneLake, it answers most of the questions people reach for Spark to answer — row counts, distincts, null rates, min/max dates, a reconciliation against what the semantic model reports — with no cluster, no cold start, and no capacity consumption.

That last point is the reason this skill exists. A freshness check that costs nothing can run on every delivery; one that opens a Spark session and smooths CU forward for 24 hours cannot.

Reach for DuckDB first. Reach for powerbi-spark-livy only when the volume or the transformation genuinely needs distributed compute.

Where it runs

Location	Use when
Local (your machine, this CLI)	Profiling, reconciliation, quality gates, anything in a delivery loop. Needs OneLake access from the host.
Inside a Fabric notebook	Data is large enough that pulling it to the host is the bottleneck, but small enough that Spark is overkill. Still cheaper than a Spark session on the same notebook.

Setup

pip install duckdb        # or: uv pip install duckdb

Extensions, loaded per connection:

import duckdb

con = duckdb.connect()
con.execute("INSTALL azure; LOAD azure;")   # ABFSS / OneLake access
con.execute("INSTALL delta; LOAD delta;")   # Delta log awareness — not just the Parquet files

INSTALL needs network access once; on an air-gapped build host, pre-seed the extension directory and skip INSTALL.

Authentication to OneLake

Use the Azure credential chain rather than a key or a SAS token:

con.execute("""
    CREATE SECRET onelake (
        TYPE azure,
        PROVIDER credential_chain,
        CHAIN 'cli;env;managed_identity',
        ACCOUNT_NAME 'onelake'
    );
""")

That picks up az login locally and a managed identity in a hosted runtime. Confirm the exact clause names against the DuckDB azure extension docs for your installed version — the secret syntax has changed across DuckDB releases and a wrong clause fails with a parse error rather than an auth error.