agentleFS
santoshkanthetysantoshkanthety/powerbi-agent/skills/powerbi-duckdb-lakehouse/SKILL.md

powerbi-duckdb-lakehouse

Skill3 starsChanged 8 months agoMITView on GitHub

Query Fabric lakehouse Delta tables, warehouse data and OneLake Parquet with DuckDB — locally or inside a Fabric notebook — for data profiling, freshness and quality checks, and reconciling a semantic model against its source without paying Spark cold-start or capacity cost. Use when the user mentions: DuckDB, duckdb, query Delta locally, read Parquet without Spark, profile lakehouse data, data freshness check, data quality check, row count reconciliation, compare model to source, cheap lakehouse query, delta_scan, OneLake Parquet, local SQL over OneLake.

  • Installs packages

The file

We split this skill into the rules it gives an agent

3 rules. About 0 are common, 2 are distinctive, and 1 is very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈2

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 1

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

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

    line 48
  2. Distinctive:

    Never embed an account key, a SAS token or a bearer token in SQL — it lands in query logs and in whatever file you saved the script to.

    line 66
  3. Very specific:

    Run this before designing a star schema, not after.

    line 114

≈ “Common” is an estimate: at least 1 in 5 of the 372,429 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

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

LocationUse when
Local (your machine, this CLI)Profiling, reconciliation, quality gates, anything in a delivery loop. Needs OneLake access from the host.
Inside a Fabric notebookData 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.

Never embed an account key, a SAS token or a bearer token in SQL — it lands in query logs and in whatever file you saved the script to.

Reading lakehouse data

OneLake paths follow the ABFSS shape:

abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<item>.Lakehouse/Tables/<schema>/<table>
abfss://<workspace>@onelake.dfs.fabric.microsoft.com/<item>.Lakehouse/Files/<path>
-- Delta table, honouring the transaction log (correct row counts after updates/deletes)
SELECT count(*) FROM delta_scan(
  'abfss://analytics@onelake.dfs.fabric.microsoft.com/bronze.Lakehouse/Tables/dbo/orders'
);

-- Raw Parquet, including globs and Hive-style partitions
SELECT count(*) FROM read_parquet(
  'abfss://analytics@onelake.dfs.fabric.microsoft.com/bronze.Lakehouse/Files/raw/orders/**/*.parquet',
  hive_partitioning = true
);

Use delta_scan, not read_parquet, on a Delta table. Reading the underlying Parquet files directly ignores the transaction log, so deleted and superseded rows come back and your count is wrong — and wrong in the direction that looks like a genuine data discrepancy. This is the single most common error in this workflow.

Warehouse tables are reachable the same way through the warehouse item's OneLake path, or over the SQL endpoint with a SQL client when you need warehouse semantics rather than file reads.

The three jobs this does well

1. Profiling

SELECT
    count(*)                                        AS rows,
    count(DISTINCT customer_id)                     AS customers,
    count(*) FILTER (WHERE customer_id IS NULL)     AS null_customer,
    min(order_date)                                 AS first_order,
    max(order_date)                                 AS last_order,
    sum(amount)                                     AS total_amount
FROM delta_scan('abfss://.../Tables/dbo/orders');

Run this before designing a star schema, not after. Cardinality, null rates and date range decide the grain, the partitioning and whether a column belongs in a dimension — powerbi-star-schema-modeling.

2. Freshness and quality gates

-- Freshness: is the source newer than the model's last refresh?
SELECT max(order_date) AS source_max FROM delta_scan('abfss://.../Tables/dbo/orders');

-- Referential integrity before it becomes a blank row in a report
SELECT count(*) AS orphans
FROM delta_scan('abfss://.../Tables/dbo/orders')      o
LEFT JOIN delta_scan('abfss://.../Tables/dbo/customer') c USING (customer_id)
WHERE c.customer_id IS NULL;

-- Duplicate grain
SELECT order_id, count(*) AS n
FROM delta_scan('abfss://.../Tables/dbo/orders')
GROUP BY order_id HAVING count(*) > 1 LIMIT 20;

These are the checks that belong in a delivery gate (powerbi-testing-validation). Because they cost nothing, they can run on every change rather than once at UAT.

3. Reconciling a model against its source

The question that ends most "the number is wrong" escalations: does the model agree with the lakehouse?

# Source side — DuckDB over the lakehouse
#   SELECT sum(amount) FROM delta_scan('abfss://.../Tables/dbo/orders') WHERE year(order_date)=2026

# Model side — DAX over the semantic model
pbi-agent dax query "EVALUATE ROW(\"Total\", CALCULATE([Total Sales], 'Date'[Year] = 2026))"

Compare the two. A mismatch is one of: a Power Query filter or type coercion (powerbi-power-query), a relationship dropping rows, measure logic, or an incremental refresh partition that never reloaded (powerbi-refresh-semantic-model). Narrow it by reconciling at successively finer grain — year, then month, then one key — rather than guessing.

Limits — know them before promising

  • Single machine, single process. Memory-bound. A wide join across two multi-hundred-GB fact tables belongs in Spark. Set SET memory_limit and SET threads explicitly on a shared host rather than letting it take everything.
  • Egress. Local reads pull bytes out of OneLake across the network. Project the columns you need and push filters into the scan; SELECT * over a wide fact table is slow for a reason that is not DuckDB's fault.
  • Read-only, by policy here. DuckDB can write Parquet and Delta, but a lakehouse table's writer should be the pipeline that owns it, not an ad-hoc profiling script. Write to a local file or a scratch path, and never to a bronze/silver/gold table without explicit confirmation naming the table.
  • Delta feature coverage varies by extension version — column mapping, deletion vectors and newer protocol features may not be supported. If a delta_scan count disagrees with Spark's, suspect the extension version before suspecting the data.
  • Not a semantic layer. DuckDB answers what is in the source. It does not and cannot tell you what a measure means — that is the model's job.

Related skills

NeedSkill
Distributed compute, real transformspowerbi-spark-livy, powerbi-fabric-pipelines
Layer design the profile informspowerbi-medallion-architecture, powerbi-star-schema-modeling
Turning checks into a delivery gatepowerbi-testing-validation
Why source and model disagreepowerbi-power-query, powerbi-refresh-semantic-model
Cost of the Spark alternativepowerbi-fabric-capacity
Non-OneLake sourcespowerbi-source-integration

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.