fabric-cli-lakehouse

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