fabric-cli-lakehouse

name	fabric-cli-lakehouse
description	Fabric CLI lakehouse operations — create lakehouses, inspect table schemas, load data into tables, optimize and vacuum tables, and run table maintenance jobs. Use when working with .Lakehouse items and Delta tables via the fab CLI.
allowed-tools	Bash(uv run *), Bash(fab *), Bash(cat *), Bash(grep *), Read, Write, Edit, Glob
user-invocable	false
metadata	{"author":"accelerate-data"}

Fabric CLI — Lakehouse & Table Operations

Complete reference for managing lakehouses and Delta tables via fab CLI.

Execution Pattern

uv run --env-file .env fab <command>

Lakehouse Management

List Lakehouses

# All items in workspace
uv run --env-file .env fab ls ws1.Workspace

# Detailed
uv run --env-file .env fab ls ws1.Workspace -l

Create a Lakehouse

# Basic
uv run --env-file .env fab create ws1.Workspace/lh1.Lakehouse

# With schema support enabled
uv run --env-file .env fab create ws1.Workspace/lh1.Lakehouse -P enableSchemas=true

Check Existence

uv run --env-file .env fab exists ws1.Workspace/lh1.Lakehouse

Get Lakehouse Details

uv run --env-file .env fab get ws1.Workspace/lh1.Lakehouse
uv run --env-file .env fab get ws1.Workspace/lh1.Lakehouse -v

Delete a Lakehouse

uv run --env-file .env fab rm ws1.Workspace/lh1.Lakehouse -f

Table Schema Inspection

Inspect the schema of Delta tables within a lakehouse:

# Must specify the full path to a specific table (not just the schema)
uv run --env-file .env fab table schema ws1.Workspace/lh1.Lakehouse/Tables/dbo/customer_data

Note: You must point to a specific table, not just the schema directory. fab table schema .../Tables/dbo will fail — use fab table schema .../Tables/dbo/table_name.

Supported item types for schema inspection:

Type	Extension
Lakehouse	.Lakehouse
Warehouse	.Warehouse
Mirrored Database	.MirroredDatabase
SQL Database	.SQLDatabase
Semantic Model	.SemanticModel (requires enablement)
KQL Database	.KQLDatabase (requires enablement)

List Tables

# List tables in a lakehouse
uv run --env-file .env fab ls ws1.Workspace/lh1.Lakehouse/Tables

# List tables in a specific schema
uv run --env-file .env fab ls ws1.Workspace/lh1.Lakehouse/Tables/dbo

# List warehouse tables
uv run --env-file .env fab ls ws1.Workspace/wh1.Warehouse/Tables/dbo

Load Data into Tables

Load CSV or Parquet files from OneLake into Delta tables.

Note: table load is not supported in schema-enabled Lakehouses (created with -P enableSchemas=true).

CSV Loading

# Load from a folder of CSVs
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/customer_data --file ws1.Workspace/lh1.Lakehouse/Files/csv/customers

# Load a single CSV (append mode)
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/sales_data --file ws1.Workspace/lh1.Lakehouse/Files/csv/daily_sales.csv --mode append

# Custom CSV format (no header, semicolon delimiter)
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/product_catalog --file ws1.Workspace/lh1.Lakehouse/Files/custom_csv --format "format=csv,header=false,delimiter=';'"

Parquet Loading

# Load from a folder of Parquet files
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/analytics_data --file ws1.Workspace/lh1.Lakehouse/Files/parquet/events --format format=parquet --mode append

# With extension filter
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/processed_data --file ws1.Workspace/lh1.Lakehouse/Files/parquet/processed --format format=parquet --extension '.parquet'

Load Flags

Flag	Purpose	Values
--file	Source file/folder path in OneLake	OneLake path
--mode	Write mode	overwrite (default), append
--format	File format specification	format=csv, format=parquet + options
--extension	Filter source files by extension	.csv, .parquet

CSV Format Options

Option	Purpose	Example
format	File format	csv
header	Has header row	true, false
delimiter	Column separator	,, ;, \t

Table Optimization

Optimize Delta table file layout for query performance:

# Basic optimization (compacts small files)
uv run --env-file .env fab table optimize ws1.Workspace/lh1.Lakehouse/Tables/sales_data

# With V-Order and Z-Order indexing
uv run --env-file .env fab table optimize ws1.Workspace/lh1.Lakehouse/Tables/customer_transactions --vorder --zorder customer_id,transaction_date

Optimization Flags

Flag	Purpose
--vorder	Apply V-Order optimization (Fabric-specific, improves read performance)
--zorder	Apply Z-Order indexing on specified columns (comma-separated)

Table Vacuum

Remove old files no longer referenced by the Delta log:

# Default vacuum (7-day retention)
uv run --env-file .env fab table vacuum ws1.Workspace/lh1.Lakehouse/Tables/transaction_history

# Custom retention (48 hours)
uv run --env-file .env fab table vacuum ws1.Workspace/lh1.Lakehouse/Tables/temp_processing_data --retain_n_hours 48

Vacuum Flags

Flag	Purpose	Default
--retain_n_hours	Retention period in hours	168 (7 days)

Table Maintenance via Jobs

Run table maintenance as background jobs:

# Optimize via job
uv run --env-file .env fab job run ws1.Workspace/lh1.Lakehouse -i '{"tableName": "orders", "optimizeSettings": {"vOrder": true, "zOrderBy": ["account_id"]}}'

# Vacuum via job
uv run --env-file .env fab job run ws1.Workspace/lh1.Lakehouse -i '{"tableName": "orders", "vacuumSettings": {"retentionPeriod": "7.01:00:00"}}'

Job input JSON formats:

Optimize:

{
  "tableName": "orders",
  "optimizeSettings": {
    "vOrder": true,
    "zOrderBy": ["account_id", "order_date"]
  }
}

Vacuum:

{
  "tableName": "orders",
  "vacuumSettings": {
    "retentionPeriod": "7.01:00:00"
  }
}

Table Help

uv run --env-file .env fab table -h

Query Lakehouse Tables via SQL Endpoint

The fab CLI does not have a built-in SQL query command. To query lakehouse tables, use pyodbc with Azure AD token auth against the SQL endpoint:

# Environment variables needed
LAKEHOUSE_SQL_ENDPOINT=your-endpoint.datawarehouse.fabric.microsoft.com
LAKEHOUSE_DATABASE=your_lakehouse

Use a helper script with pyodbc for token-based auth. Note: sqlcmd ODBC does not support token auth properly (throws "Argument too long" for tokens > 128 chars).

Practical Tips

• Get lakehouse ID: uv run --env-file .env fab get ws1.Workspace/lh1.Lakehouse -f -q "id"
• Describe available commands: uv run --env-file .env fab desc .Lakehouse shows all supported operations (includes table load, table optimize, table schema, table vacuum, job run, etc.)
• Lakehouse does NOT support: cp, export, import at the item level (use OneLake paths for file operations within the lakehouse)

Direct API Access

For advanced operations not covered by CLI commands:

# List lakehouses via REST API
uv run --env-file .env fab api -X get workspaces/<workspace-id>/lakehouses

# Create lakehouse via REST API
uv run --env-file .env fab api -X post workspaces/<workspace-id>/lakehouses -i '{"displayName": "my_lakehouse"}'

# List OneLake files via storage API
uv run --env-file .env fab api -A storage ws1.Workspace/lh1.Lakehouse/Files?resource=filesystem&recursive=false

# Get ACL permissions on OneLake paths
uv run --env-file .env fab acl ls ws1.Workspace/lh1.Lakehouse/Files

Common Patterns

Load CSV then verify

# Upload CSV to OneLake
uv run --env-file .env fab cp ./data/customers.csv ws1.Workspace/lh1.Lakehouse/Files/csv/customers.csv

# Load into table
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/customers --file ws1.Workspace/lh1.Lakehouse/Files/csv/customers.csv

# Verify schema
uv run --env-file .env fab table schema ws1.Workspace/lh1.Lakehouse/Tables/dbo/customers

Optimize after bulk load

# Load data
uv run --env-file .env fab table load ws1.Workspace/lh1.Lakehouse/Tables/events --file ws1.Workspace/lh1.Lakehouse/Files/parquet/events --format format=parquet --mode append

# Optimize for query performance
uv run --env-file .env fab table optimize ws1.Workspace/lh1.Lakehouse/Tables/events --vorder --zorder event_date

Common Issues

Issue	Fix
table schema returns nothing	Table may not exist yet — load data first
Load fails with format error	Ensure --format uses key=value pairs: format=csv,header=true
Vacuum removes too much	Increase --retain_n_hours (default is 168 = 7 days)
Z-Order columns invalid	Columns must exist in the table schema
Permission denied on table ops	Verify FAB_TOKEN has write access to the lakehouse
Schema not visible	Ensure lakehouse has enableSchemas=true if using custom schemas