Tables¶

Manage SQL tables on Microsoft Fabric Data Warehouses and SQL Analytics Endpoints. Commands and tools cover listing, counting, reading, creating (including CTAS, empty DDL from schema inference, and zero-copy clone), deleting, clearing, renaming, transferring to another schema, and loading data via COPY INTO from local files or remote URLs.

Targets: Data Warehouse / SQL Analytics Endpoint

CLI¶

tables clear¶

Targets: Data Warehouse only

Truncate a table (delete all rows, keep structure). You will be asked to confirm unless --yes is passed.

Synopsis

fdw [-w WORKSPACE] tables clear [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace --yes tables clear SalesWH dbo.staging_load

tables columns¶

Targets: Data Warehouse / SQL Analytics Endpoint

List the columns of a table, including name, formatted data type, nullability, ordinal position, collation, identity, and computed flags.

Synopsis

fdw [-w WORKSPACE] tables columns [WAREHOUSE] QUALIFIED_NAME

QUALIFIED_NAME must be a dot-separated schema.table_name string, e.g. dbo.Sales.

Example

fdw -w MyWorkspace tables columns SalesWH dbo.Sales

 ordinal  name    data_type     nullable  is_identity  is_computed  collation_name
 -------  ------  ------------  --------  -----------  -----------  ----------------------------
 1        id      INT           False     True         False
 2        amount  DECIMAL(18,2) True      False        False
 3        label   NVARCHAR(100) True      False        False        Latin1_General_CI_AS

tables clear¶

Targets: Data Warehouse only

Truncate a table (delete all rows, keep structure). You will be asked to confirm unless --yes is passed.

Synopsis

fdw [-w WORKSPACE] tables clear [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace --yes tables clear SalesWH dbo.staging_load

tables cluster-by¶

Targets: Data Warehouse only

Change (or remove) the data-clustering columns of an existing table via a transactional CTAS-swap.

Performance note: This operation copies the entire table. Runtime is proportional to table size.

The operation is atomic: all three steps run inside a single transaction. Any failure rolls back automatically - no orphan temp table is left behind.

Synopsis

fdw [-w WORKSPACE] tables cluster-by [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Examples

# Set new clustering columns
fdw -w MyWorkspace --yes tables cluster-by SalesWH dbo.orders \
  --cluster-by CustomerID --cluster-by SaleDate

# Remove clustering entirely
fdw -w MyWorkspace --yes tables cluster-by SalesWH dbo.orders

tables cluster-columns¶

Targets: Data Warehouse only

List the data-clustering columns of a table, ordered by clustering ordinal. Returns an empty table when no clustering is defined (exit 0).

Synopsis

fdw [-w WORKSPACE] tables cluster-columns [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace --json tables cluster-columns SalesWH dbo.orders

[
  {"column_name": "city", "clustering_ordinal": 1},
  {"column_name": "country", "clustering_ordinal": 2}
]

tables clone¶

Targets: Data Warehouse only

Create a zero-copy clone of a table using CREATE TABLE … AS CLONE OF. Pass --at to clone from a point in time within the warehouse data-retention window.

Synopsis

fdw [-w WORKSPACE] tables clone [OPTIONS] [ITEM]

Example

# Clone to the current state
fdw -w MyWorkspace tables clone SalesWH \
  --source dbo.orders \
  --name dbo.orders_backup

# Point-in-time clone
fdw -w MyWorkspace tables clone SalesWH \
  --source dbo.orders \
  --name dbo.orders_may_snapshot \
  --at 2024-05-20T14:00:00

tables count¶

Targets: Data Warehouse / SQL Analytics Endpoint

Return the total row count of a table using SELECT COUNT_BIG(*).

Use --as-of or --ago to count rows as they were at an earlier point in time (time travel). The same retention window applies as for tables read.

Synopsis

fdw [-w WORKSPACE] tables count [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace --json tables count SalesWH dbo.orders

{"schema": "dbo", "name": "orders", "row_count": 999999}

# Point-in-time row count
fdw -w MyWorkspace --json tables count SalesWH dbo.orders --as-of 2024-03-15T10:00:00Z
fdw -w MyWorkspace --json tables count SalesWH dbo.orders --ago 1h

tables export¶

Targets: Data Warehouse / SQL Analytics Endpoint

Export the full contents of a table to a local file in Parquet, CSV, or JSON format.

The output format is inferred from the --output extension (.parquet, .csv, .json, .pq). Use --format to override.

Memory caveat: the full result set is loaded into the Python process before writing. Ensure sufficient RAM, or use --limit to export a sample only. A streaming variant is a planned future follow-up.

Use --as-of or --ago to export the table as it was at an earlier point in time (time travel). The same retention window applies as for tables read.

By default, if the output file already exists it is overwritten. Pass --no-overwrite to fail instead.

Synopsis

fdw [-w WORKSPACE] tables export [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

# Full export to Parquet (format inferred from extension)
fdw -w MyWorkspace tables export SalesWH dbo.orders --output orders.parquet

# Sample 500 rows as CSV
fdw -w MyWorkspace tables export SalesWH dbo.orders --output sample.csv --limit 500

# Point-in-time export
fdw -w MyWorkspace tables export SalesWH dbo.orders --output snapshot.parquet --as-of 2024-03-15T10:00:00Z

# JSON output (machine-readable)
fdw -w MyWorkspace --json tables export SalesWH dbo.orders --output out.parquet

{"status": "exported", "rows": 42300, "output": "out.parquet"}

tables create¶

Targets: Data Warehouse only

Create a new table on a Fabric Data Warehouse. Two modes are available:

• CTAS (CREATE TABLE … AS SELECT): supply --select or --from-file. The body must start with SELECT (leading block/line comments are allowed).
• Empty DDL (CREATE TABLE … (col TYPE, …)): supply exactly one of --from-parquet, --from-csv, --from-json, or one-or-more --column. The schema is derived (Parquet/CSV/JSON inference) or listed explicitly; no data is ever read or inserted - this scaffolds the table structure only. To load data afterwards, use tables load.

Synopsis

fdw [-w WORKSPACE] tables create [OPTIONS] [WAREHOUSE]

CTAS options¶

Exactly one of --select or --from-file must be provided for the CTAS path. Cannot be combined with empty-DDL options.

Empty-DDL options¶

--from-parquet, --from-csv, --from-json, and --column are mutually exclusive with each other and with the CTAS path. For the explicit path at least one --column must be provided.

Arrow → T-SQL type mapping (Parquet / CSV / JSON inference)

Examples

# CTAS
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.orders_2026 \
  --select "SELECT * FROM dbo.orders WHERE YEAR(sale_date) = 2026"

# Empty table from Parquet schema
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.sales_empty \
  --from-parquet ./exports/sales.parquet

# Empty table from CSV header (type inference)
fdw -w MyWorkspace tables create SalesWH \
  --name staging.raw_products \
  --from-csv ./data/products.csv --varchar-length 500

# Empty table with explicit inline columns
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.events \
  --column "event_id:BIGINT:notnull" \
  --column "event_type:VARCHAR(100)" \
  --column "occurred_at:DATETIME2(7)"

# Empty table from JSON data - JSONL (schema inferred from the data)
fdw -w MyWorkspace tables create SalesWH \
  --name staging.events \
  --from-json ./data/events.jsonl

# Empty table from JSON data - a JSON array of objects
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.audit_log \
  --from-json ./data/audit_log.json --varchar-length 500

# CTAS with CLUSTER BY (column existence not validated on CTAS path)
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.orders_2026 \
  --select "SELECT CustomerID, SaleDate, Amount FROM dbo.orders WHERE YEAR(SaleDate) = 2026" \
  --cluster-by CustomerID --cluster-by SaleDate

# Empty table with explicit columns and CLUSTER BY
fdw -w MyWorkspace tables create SalesWH \
  --name dbo.events \
  --column "CustomerID:INT:notnull" \
  --column "SaleDate:DATE:notnull" \
  --column "Amount:DECIMAL(18,2)" \
  --cluster-by CustomerID --cluster-by SaleDate

tables delete¶

Targets: Data Warehouse only

Drop a table. You will be asked to confirm unless --yes is passed.

Synopsis

fdw [-w WORKSPACE] tables delete [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace --yes tables delete SalesWH dbo.orders_2026

tables health-check¶

Targets: SQL Analytics Endpoint

Run sp_get_table_health_metrics against a single table to surface Delta/Parquet layout issues (small files, fragmentation, excessive deletes/updates, delayed checkpoints) and decide whether maintenance is needed.

The proc is Generally Available (announced at Build 2026) but Microsoft Learn has no dedicated reference page yet. Output columns are passed through verbatim - the exact column names and types are determined by the proc and may change across Fabric releases.

--json on a zero-row result prints [], with no column names included. This is different from sql exec --json, which always emits {"columns": [...], "rows": [...], "rowcount": N} even when rows is empty. tables health-check --json follows the convention shared by every other list-style --json command in this CLI instead: the output is always a JSON array, regardless of row count. If you need the column names of a table with no health issues, they are also available unconditionally through the get_table_health_metrics MCP tool.

Synopsis

fdw [-w WORKSPACE] tables health-check [ENDPOINT] QUALIFIED_NAME

Example

fdw -w MyWorkspace tables health-check MySqlEndpoint dbo.FactSales
fdw -w MyWorkspace --json tables health-check MySqlEndpoint dbo.FactSales

tables sync-status¶

Targets: SQL Analytics Endpoint

Show when each table was last updated by the metadata sync, via sys.dm_db_external_tables_log_status. Only applies to SQL Analytics Endpoints created after New metadata sync (preview) was enabled under Workspace settings, Warehouse settings, for the workspace hosting the endpoint. On an endpoint using the legacy metadata sync, the command fails with an actionable message pointing back at that setting and at fdw sql-endpoints refresh as the fallback for refreshing the whole item.

The listing is driven from sys.tables, so a table with no matching DMV row still appears, with last_update_time_utc, latest_log_version, latest_checkpoint_version, and is_blocked all empty. This means no sync information is available for that table, not that it provably never synced - Microsoft documents the DMV as describing the most recent update, not as a complete sync history.

Coverage limit: by default, only tables that exist in the endpoint catalog (sys.tables) are listed. On a SQL Analytics Endpoint that catalog is itself maintained by the metadata sync, so a Lakehouse table whose discovery has not completed, or has failed, does not appear at all - it is not shown as a row with empty fields, it is simply absent. If a table you expect is missing, run fdw sql-endpoints refresh to force an item-level sync and check again, or pass --check-lakehouse (below) to see it listed directly.

Closing the gap with --check-lakehouse: this flag cross-references the backing Lakehouse's own table inventory and adds a row for every table it finds there but not in the endpoint catalog, with in_endpoint_catalog: false and every sync field empty (a table with no catalog row cannot have sync information yet). It works for both kinds of Lakehouse:

• A classic (non-schema-enabled) Lakehouse, via the GA Fabric REST "List Tables" API. Every table belongs to the implicit dbo schema.
• A schema-enabled Lakehouse, via the OneLake table APIs, a preview, Unity-Catalog-compatible API family that lists schemas and tables with schema attribution. Because it is a preview API with no documented GA date, the classic path above is kept for classic Lakehouses rather than routed through it too.

Within each schema, names are compared exactly (case-sensitively), matching Fabric's case-sensitive default collation - a Lakehouse FactSales and a catalog factsales are treated as distinct, and a Lakehouse table whose name differs from a catalog table only by case is reported with case_mismatched_catalog_name set to that catalog name, rather than as a flat "missing" table. This costs at least one extra REST call beyond the normal single TDS query (more for a schema-enabled Lakehouse, one per schema), so it is opt-in rather than always-on for a command you may run repeatedly.

If the endpoint's backing item cannot be resolved to a Lakehouse at all (a mirrored database, or similar), --check-lakehouse fails with a clear error - a hard failure (non-zero exit, no rows rendered), not a silent fallback to the catalog-only listing, since you asked for the cross-check and "no extra rows" must never be mistaken for "fully discovered". --check-lakehouse is mutually exclusive with --schema and --table: it always compares the whole endpoint.

Synopsis

fdw [-w WORKSPACE] tables sync-status [OPTIONS] [ENDPOINT]

Example

fdw -w MyWorkspace tables sync-status MyLakehouseEP
fdw -w MyWorkspace tables sync-status MyLakehouseEP --schema dbo
fdw -w MyWorkspace tables sync-status MyLakehouseEP --table dbo.FactSales

# Also surface tables the metadata sync has never discovered at all
fdw -w MyWorkspace tables sync-status MyLakehouseEP --check-lakehouse

References: Lakehouse - Tables - List Tables (classic Lakehouse path), OneLake table APIs overview (schema-enabled Lakehouse path, preview)

tables refresh¶

Targets: SQL Analytics Endpoint

Refresh a single table's data via sys.sp_dw_refresh_ext_table, without a full item-level metadata sync. Only applies to SQL Analytics Endpoints created after New metadata sync (preview) was enabled under Workspace settings, Warehouse settings, for the workspace hosting the endpoint. On an endpoint using the legacy metadata sync, the command fails with the same actionable message as tables sync-status.

This refreshes data only - it re-reads the underlying Delta log for that one table. It does not pick up schema changes (tables added or dropped); for that, use fdw sql-endpoints refresh instead. See the "which refresh do I want" comparison on that page.

On success, the command prints a one-line confirmation followed by the table's refreshed sync-status row (the same shape as tables sync-status), so the new last_update_time_utc is visible without a second command. A non-zero procedure return code (the procedure returns 0 for success, 1 for failure) fails with a clear error and a non-zero exit code.

QUALIFIED_NAME must already exist in the endpoint's catalog. The procedure does not create a table on demand. If it reports the table missing, the command fails with an actionable error - but the same driver error also fires when the table exists and the caller lacks permission to it, and the two are not distinguishable from the message alone. The error names both possibilities rather than asserting one: for a missing table, fdw sql-endpoints refresh forces an item-level sync and picks it up; for a permissions problem, that sync will not help - check permissions on the table instead.

The procedure can also decline a table by type, reported as "Refresh is not supported for this type of table" and translated into the same actionable error as the legacy-sync case above. Microsoft's reference for this procedure does not document which table types it accepts or rejects. If it fails for you with either message, fdw sql-endpoints refresh is the working alternative for that table.

--json emits a single JSON object, not an array - the command always acts on exactly one table, so fdw tables refresh MyLakehouseEP dbo.FactSales --json | jq .last_update_time_utc works without indexing.

Synopsis

fdw [-w WORKSPACE] tables refresh [ENDPOINT] QUALIFIED_NAME

Example

fdw -w MyWorkspace tables refresh MyLakehouseEP dbo.FactSales

Refreshed table metadata for dbo.FactSales.
 schema_name  name       last_update_time_utc  latest_log_version  latest_checkpoint_version  is_blocked
 ------------ ---------- --------------------- ------------------- -------------------------- ----------
 dbo          FactSales  2026-09-03T09:14:03Z  1285                1200                       False

fdw -w MyWorkspace --json tables refresh MyLakehouseEP dbo.FactSales | jq .last_update_time_utc

References: sys.sp_dw_refresh_ext_table, SQL analytics endpoint metadata sync (the New metadata sync preview setting)

tables list¶

Targets: Data Warehouse / SQL Analytics Endpoint

List all tables on a warehouse or SQL Analytics Endpoint. Pass --schema to filter to a single schema.

Synopsis

fdw [-w WORKSPACE] tables list [OPTIONS] [WAREHOUSE]

Example

fdw -w MyWorkspace tables list SalesWH --schema dbo

 schema_name  name      created               modified
 ------------ --------- --------------------- ---------------------
 dbo          customers 2026-01-10T08:00:00Z  2026-06-01T12:00:00Z
 dbo          orders    2026-02-01T09:00:00Z  2026-05-15T14:00:00Z

tables load¶

Targets: Data Warehouse only

Load data into a warehouse table via COPY INTO from either a local file or a remote URL.

Local file path (--file): the file is staged to a temporary Lakehouse in OneLake (chunked DFS upload), loaded into the target table via COPY INTO, and the staging Lakehouse is automatically deleted in a finally block regardless of success or failure. JSON files are converted client-side to Parquet (requires pyarrow) before staging.

Remote URL (--url): COPY INTO is issued directly from the given URL. For OneLake or same-tenant URLs no credential is needed. For secured external URLs (Azure Blob Storage, ADLS Gen2) supply --credential-type and --secret/--identity as appropriate.

Auto-create (create-and-load): Pass --create to auto-create the target table from the source schema before loading (local files only; requires pyarrow). The schema is inferred from the source:

• Parquet: exact types are read from the Parquet footer (no row data is read).
• CSV: the header row and up to --sample-rows rows are read for type inference. Use --all-varchar to skip inference and force every column to VARCHAR.
• JSON: the file is converted to Parquet internally (as required for staging); the schema is read from the resulting Parquet footer.

Use --if-exists to control behaviour when the table already exists:

truncate and replace are permanently destructive and require confirmation (or --yes / -y).

Use --cleanup-on-failure to drop the table if WE created it in this call and the subsequent COPY INTO fails. A pre-existing table is never dropped by this flag.

Synopsis

fdw [-w WORKSPACE] tables load [OPTIONS] [ITEM] QUALIFIED_NAME

QUALIFIED_NAME is the dot-separated schema.table_name of the destination table.

Examples

# Load a local CSV into an existing table (header row present)
fdw -w MyWorkspace tables load SalesWH dbo.sales --file data.csv

# Load a local Parquet file into an existing table
fdw -w MyWorkspace tables load SalesWH dbo.events --file events.parquet

# Load a local JSON file (converts to Parquet internally; requires pyarrow)
fdw -w MyWorkspace tables load SalesWH dbo.products --file products.json

# Auto-create the table from a Parquet schema, then load
fdw -w MyWorkspace tables load SalesWH dbo.sales --file data.parquet --create

# Auto-create from CSV, force all columns to VARCHAR
fdw -w MyWorkspace tables load SalesWH dbo.raw --file raw.csv --create --all-varchar

# Replace the existing table (drop + recreate schema + load), skip confirmation
fdw -w MyWorkspace tables load SalesWH dbo.sales --file data.parquet --create \
    --if-exists replace -y

# Auto-create; drop the table if the load fails (cleanup_on_failure)
fdw -w MyWorkspace tables load SalesWH dbo.sales --file data.parquet --create \
    --cleanup-on-failure

# Auto-create with CLUSTER BY (columns must exist in the inferred schema)
fdw -w MyWorkspace tables load SalesWH dbo.sales --file data.parquet --create \
    --cluster-by SaleDate --cluster-by CustomerID

# Load from a remote OneLake URL (no credential needed)
fdw -w MyWorkspace tables load SalesWH dbo.orders \
    --url "https://onelake.dfs.fabric.microsoft.com/ws/lh.Lakehouse/Files/orders.parquet" \
    --format parquet

# Load from Azure Blob with SAS token
fdw -w MyWorkspace tables load SalesWH dbo.events \
    --url "https://myaccount.blob.core.windows.net/data/events.csv" \
    --format csv --credential-type sas --secret "?sv=2021&..."

tables read¶

Targets: Data Warehouse / SQL Analytics Endpoint

Read up to --count rows from a table and emit them as JSON (default), CSV, or Parquet.

CSV and Parquet formats require --output. JSON is emitted to stdout by default.

Use --as-of or --ago to read the table as it was at an earlier point in time (time travel). Fabric supports OPTION (FOR TIMESTAMP AS OF ...) on SELECT statements; the retention window is 1-120 days (default 30). Timestamps outside the retention window error server-side - no client-side pre-validation is performed.

Synopsis

fdw [-w WORKSPACE] tables read [OPTIONS] [WAREHOUSE] QUALIFIED_NAME

Example

fdw -w MyWorkspace tables read SalesWH dbo.orders --count 5

[
  {"id": 1, "amount": 99.99, "customer_id": 42},
  ...
]

# Point-in-time read
fdw -w MyWorkspace tables read SalesWH dbo.orders --as-of 2024-03-15T10:00:00Z
fdw -w MyWorkspace tables read SalesWH dbo.orders --ago 2d

tables rename¶

Targets: Data Warehouse only

Rename a table via sp_rename. The new name must be an unqualified (bare) identifier - sp_rename cannot move a table to a different schema.

Synopsis

fdw [-w WORKSPACE] tables rename [OPTIONS] [ITEM] QUALIFIED_NAME

QUALIFIED_NAME is the current dot-separated schema.table_name.

Example

fdw -w MyWorkspace tables rename SalesWH dbo.orders_2025 --new-name orders_archive_2025

tables transfer¶

Targets: Data Warehouse only

Move a table to another schema via ALTER SCHEMA ... TRANSFER OBJECT::.... The command emits exactly ALTER SCHEMA [target_schema] TRANSFER OBJECT::[schema].[table], with every identifier validated and bracket-quoted before being embedded in the DDL.

CAUTION: Transferring a table on a SQL Analytics Endpoint is not supported and can break the OneLake sync, so this command is rejected there - use a Fabric Data Warehouse. Permissions granted directly on the table are dropped by the engine when the schema changes. Dependent views and stored procedures that reference the table by its old schema-qualified name are not automatically updated and may need refreshing after the transfer.

Reference: ALTER SCHEMA (Transact-SQL)

Synopsis

fdw [-w WORKSPACE] tables transfer [OPTIONS] [ITEM] QUALIFIED_NAME

QUALIFIED_NAME is the current dot-separated schema.table_name.

Example

fdw -w MyWorkspace tables transfer SalesWH dbo.orders_2025 --target-schema archive

MCP tools¶

clear_table¶

Targets: Data Warehouse only

Truncate a SQL table (remove all rows, preserve structure).

CAUTION: This is a destructive, irreversible operation. All rows will be permanently deleted. The table structure is preserved. Confirm with the user before calling.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.

Returns: { "truncated": true }: confirmation.

clone_table¶

Targets: Data Warehouse only

Create a zero-copy clone of a table using CREATE TABLE … AS CLONE OF …. Only supported on Fabric Data Warehouses (not SQL Analytics Endpoints).

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID.
• source (str): qualified source table name, e.g. dbo.sales.
• new_table (str): qualified name for the new cloned table, e.g. dbo.sales_clone.
• at (str | null, optional): ISO-8601 UTC timestamp for a point-in-time clone (e.g. 2024-05-20T14:00:00). Must be within the data-retention window. When omitted, the clone reflects the current state of the source table.

Returns: Table: the newly-created cloned table record.

get_table_columns¶

Targets: Data Warehouse / SQL Analytics Endpoint

Return column metadata for a SQL table via sys.columns. Works on both Fabric Data Warehouses and SQL Analytics Endpoints.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.Sales.

Returns: list[dict]: one dict per column, each containing:

• ordinal (int): 1-based column position (column_id).
• name (str): column name.
• data_type (str): formatted T-SQL type string, e.g. INT, VARCHAR(50), NVARCHAR(MAX), DECIMAL(18,2), DATETIME2(7).
• nullable (bool): whether the column allows NULL.
• collation_name (str | null): collation name, if applicable.
• is_identity (bool): whether the column is an identity column.
• is_computed (bool): whether the column is a computed column.

Results are ordered by ordinal position. Raises a ToolError if the table does not exist.

count_table_rows¶

Targets: Data Warehouse / SQL Analytics Endpoint

Return the total row count of a table via SELECT COUNT_BIG(*).

Supports time-travel counts via as_of: supply an ISO-8601 UTC timestamp to count rows as they were at that point in time. The same retention window applies as for read_table.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.
• as_of (str, optional): ISO-8601 UTC timestamp for a point-in-time count. Omit to count the latest data.

Returns: { "schema": str, "name": str, "row_count": int }: the schema name, table name, and total row count.

create_empty_table¶

Targets: Data Warehouse only

Create an empty SQL table from an explicit column specification (DDL only - no data is ever read or inserted). This scaffolds the table structure so that data can be loaded separately.

Server-side file access is unreliable in MCP deployments, so CSV/Parquet schema inference is not available via this tool. Use fdw tables create --from-parquet or --from-csv (CLI) for file-based schema inference.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.
• columns (list[object]): non-empty list of column definitions, each an object with:
• name (str): column identifier (must be a valid SQL identifier).
• sql_type (str): Fabric-DW-supported T-SQL type, e.g. "INT", "VARCHAR(255)", "DECIMAL(18,2)".
• nullable (bool, optional, default true): whether the column allows NULL.
• cluster_by (list[str], optional): column names for the CLUSTER BY clause (up to 4). Each name must appear in columns.

Returns: Table: the newly-created table record.

Example call:

{
  "workspace": "MyWorkspace",
  "item": "SalesWarehouse",
  "qualified_name": "dbo.events",
  "columns": [
    {"name": "event_id", "sql_type": "BIGINT", "nullable": false},
    {"name": "event_type", "sql_type": "VARCHAR(100)", "nullable": true},
    {"name": "occurred_at", "sql_type": "DATETIME2(7)", "nullable": false}
  ]
}

create_table¶

Targets: Data Warehouse only

Create a new SQL table via CTAS (CREATE TABLE … AS SELECT).

CAUTION: select_body is executed verbatim as DDL. Confirm intent before calling. The first non-comment keyword must be SELECT.

When cluster_by is supplied the DDL becomes CREATE TABLE … WITH (CLUSTER BY ([c1], [c2])) AS SELECT …. Column existence is not validated on the CTAS path because result columns come from the SELECT and are not known ahead of time.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.
• select_body (str): the SELECT statement for the CTAS source.
• cluster_by (list[str], optional): column names for the CLUSTER BY clause (up to 4). Column existence is not validated on the CTAS path.

Returns: Table: the newly-created table record.

delete_table¶

Targets: Data Warehouse only

Drop a SQL table.

CAUTION: This is a destructive, irreversible operation. All data will be permanently deleted. Confirm with the user before calling.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.

Returns: { "dropped": true }: confirmation.

get_cluster_columns¶

Targets: Data Warehouse only

Return the data-clustering columns of a table, ordered by clustering ordinal. Returns an empty list when no clustering is defined (not an error).

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.

Returns: list[{ "column_name": str, "clustering_ordinal": int }]: ordered by ascending clustering_ordinal.

get_table_health_metrics¶

Targets: SQL Analytics Endpoint

Return health metrics for a table via sp_get_table_health_metrics. Only supported on SQL Analytics Endpoints (not Data Warehouses).

The proc surfaces Delta/Parquet layout issues such as small files, fragmentation, excessive deletes/updates, and delayed checkpoints - useful for deciding whether table maintenance is needed.

The proc is Generally Available (announced at Build 2026) but its output column schema is not yet documented by Microsoft. Columns and rows are passed through verbatim.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): SQL Analytics Endpoint name or GUID. Data Warehouses are rejected with a ToolError.
• qualified_name (str): dot-separated table name, e.g. dbo.FactSales.

Returns: { "columns": list[str], "rows": list[list] }: column names and rows passed through verbatim from the proc.

list_table_sync_status¶

Targets: SQL Analytics Endpoint

Show per-table metadata sync freshness via sys.dm_db_external_tables_log_status. Only supported on SQL Analytics Endpoints (not Data Warehouses), and only on endpoints created after the workspace's New metadata sync (preview) setting was enabled.

The listing is driven from sys.tables, so a table with no matching DMV row still appears, with its sync fields null instead of the row being dropped. A null row means no sync information is available for that table - it is not proof the table has never synced.

Pass check_lakehouse=True to close part of this gap: it cross-references the backing Lakehouse's own table inventory and adds a row with in_endpoint_catalog: false (and every sync field null) for a table it finds there but not in the catalog. This works for both a classic (non-schema-enabled) Lakehouse, via the GA "List Tables" API, and a schema-enabled Lakehouse, via the preview, Unity-Catalog-compatible OneLake table APIs. Within each schema, names are compared exactly (case-sensitively), matching Fabric's case-sensitive default collation - a Lakehouse table whose name matches a catalog table case-insensitively but not exactly gets case_mismatched_catalog_name set to that catalog name instead of being reported as a flat miss. This costs one or more extra REST calls (one per schema for a schema-enabled Lakehouse), so avoid setting it on every call of a tool an agent may invoke repeatedly.

If the endpoint's backing item cannot be resolved to a Lakehouse at all (a mirrored database, or similar), check_lakehouse=True raises a ToolError explaining why, rather than silently returning the unchanged catalog-only result: a caller that explicitly asked for the cross-check must never read "no extra rows" as "fully discovered". check_lakehouse=True also raises a ToolError when combined with schema or table: it always compares the whole endpoint.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): SQL Analytics Endpoint name or GUID. Data Warehouses are rejected with a ToolError.
• schema (str | null, optional): when provided, only tables in this schema are returned. Mutually exclusive with check_lakehouse.
• table (str | null, optional): when provided, filter to this single (bare, unqualified) table name. Requires schema to also be given. Mutually exclusive with check_lakehouse.
• check_lakehouse (bool, default false): also surface tables present in the backing Lakehouse but missing from the endpoint catalog entirely. Raises a ToolError if it cannot run. See above for its cost and limits.

Returns: list[TableMetadataSyncStatus], one dict per table, each containing:

• schema_name (str): schema name.
• name (str): table name.
• qualified_name (str): schema.table.
• last_update_time_utc (str | null): ISO-8601 UTC timestamp of the last metadata sync update, or null when no sync information is available for this table.
• latest_log_version (int | null): the Delta transaction log version last processed.
• latest_checkpoint_version (int | null): the Delta checkpoint version last processed.
• is_blocked (bool | null): whether the last update attempt was blocked.
• in_endpoint_catalog (bool): true for every row sourced from sys.tables (i.e. every row when check_lakehouse is false). false only for a check_lakehouse row.
• case_mismatched_catalog_name (str | null): only set on a check_lakehouse row whose Lakehouse name matches an existing catalog table case-insensitively but not exactly - names the catalog table it likely corresponds to. null otherwise.

On an endpoint using the legacy metadata sync, the tool raises a ToolError with an actionable message naming the New metadata sync preview setting and pointing at fdw sql-endpoints refresh (or the refresh_sql_endpoint_metadata MCP tool) as the fallback for refreshing the whole item.

References: sys.dm_db_external_tables_log_status, Lakehouse - Tables - List Tables (classic Lakehouse path), OneLake table APIs overview (schema-enabled Lakehouse path, preview)

refresh_table_metadata¶

Targets: SQL Analytics Endpoint

Refresh one table's metadata via sys.sp_dw_refresh_ext_table. This is the cheap, per-table refresh for data-only staleness: it re-reads the table's underlying Delta log without a full item-level sync. Use refresh_sql_endpoint_metadata instead when the schema changed (tables added or dropped) - this tool does not pick up schema changes.

Only supported on SQL Analytics Endpoints (not Data Warehouses), and only on endpoints created after the workspace's New metadata sync (preview) setting was enabled.

Mutating (respects FABRIC_MCP_READONLY) but not destructive: it never drops or recreates anything, so it does not require the FABRIC_MCP_ALLOW_DESTRUCTIVE opt-in.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): SQL Analytics Endpoint name or GUID. Data Warehouses are rejected with a ToolError.
• qualified_name (str): dot-separated qualified table name, e.g. dbo.sales.

Returns: TableMetadataSyncStatus: the refreshed sync-status row for the table (same shape as list_table_sync_status).

On an endpoint using the legacy metadata sync, the tool raises a ToolError with the same actionable message as list_table_sync_status. A non-zero procedure return code (the procedure returns 0 for success, 1 for failure) also raises a ToolError. If the table is not present in the endpoint's catalog after a successful refresh, the tool raises a ToolError naming the table and pointing at refresh_sql_endpoint_metadata (or fdw sql-endpoints refresh) as the fallback.

qualified_name must already name a table present in the endpoint's catalog: the procedure does not create one on demand. If it reports the table missing, the tool raises a ToolError naming it - but the same driver error also fires when the table exists and the caller lacks permission to it, and the two are not distinguishable from the message alone, so the error names both possibilities rather than asserting one. For a missing table, refresh_sql_endpoint_metadata (or fdw sql-endpoints refresh) forces an item-level sync and picks it up; for a permissions problem, that sync will not help - check permissions on the table instead.

The procedure can also decline a table by type, reported as "Refresh is not supported for this type of table" and raised as a ToolError in the same family as the legacy-sync message above. Microsoft's reference for this procedure does not document which table types it accepts or rejects. If it fails for you with either message, refresh_sql_endpoint_metadata (or fdw sql-endpoints refresh) is the working alternative for that table.

References: sys.sp_dw_refresh_ext_table, SQL analytics endpoint metadata sync (the New metadata sync preview setting)

import_table_from_url¶

Targets: Data Warehouse only

Load data from a remote URL into an existing Data Warehouse table with control over what happens when the table already has data. This tool extends load_table_from_url with an if_exists policy.

if_exists policy:

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): dot-separated qualified table name, e.g. dbo.sales.
• url (str): source URL (OneLake DFS URL or external Azure Blob URL).
• file_type ("CSV" | "PARQUET"): file type to load. JSON is not supported for remote URLs.
• if_exists ("fail" | "append" | "truncate" | "replace", default "fail"): what to do when the target table already exists.
• credential_type ("none" | "sas" | "managed-identity" | "service-principal" | "account-key", default "none"): credential type for secured external URLs.
• secret (str | null, optional): credential secret (SAS token, client secret, or account key). Never logged.
• identity (str | null, optional): identity for managed-identity or service-principal.
• delimiter (str | null, optional): CSV column delimiter (e.g. ,, \t).
• has_header (bool, default true): when true, the first CSV row is a header and is skipped.
• encoding (str | null, optional): CSV encoding (e.g. UTF8, UTF8BOM).
• field_quote (str | null, optional): CSV field-quote character.
• row_terminator (str | null, optional): CSV row terminator (e.g. \n, \r\n).
• max_errors (int | null, optional): maximum errors before aborting.
• rejected_row_location (str | null, optional): URL to write rejected rows to.

Returns: CopyIntoResult: { "rows_loaded": int, "rows_rejected": int, "target": "schema.table" }.

list_tables¶

Targets: Data Warehouse / SQL Analytics Endpoint

List SQL tables on a warehouse or SQL Analytics Endpoint.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• schema (str | null, optional): when provided, only tables in this schema are returned.

Returns: list[Table]: each with schema_name, name, qualified_name, created, modified.

load_table_from_url¶

Targets: Data Warehouse only

Load data into a Data Warehouse table via COPY INTO from a remote URL. For OneLake or same-tenant URLs, no credential is needed. For secured external URLs (Azure Blob Storage, ADLS Gen2), supply credential_type and the appropriate secret/identity values.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): dot-separated qualified table name, e.g. dbo.sales.
• url (str): source URL (OneLake DFS URL or external Azure Blob URL).
• file_type ("CSV" | "PARQUET"): file type to load.
• credential_type ("none" | "sas" | "managed-identity" | "service-principal" | "account-key", default "none"): credential type for secured external URLs.
• secret (str | null, optional): credential secret (SAS token, client secret, or account key). Never logged.
• identity (str | null, optional): identity for managed-identity or service-principal.
• delimiter (str | null, optional): CSV column delimiter (e.g. ,, \t).
• has_header (bool, default true): when true, the first CSV row is a header and is skipped.
• encoding (str | null, optional): CSV encoding (e.g. UTF8, UTF8BOM).
• field_quote (str | null, optional): CSV field-quote character.
• row_terminator (str | null, optional): CSV row terminator (e.g. \n, \r\n).
• max_errors (int | null, optional): maximum errors before aborting.
• rejected_row_location (str | null, optional): URL to write rejected rows to.

Returns: CopyIntoResult: { "rows_loaded": int, "rows_rejected": int, "target": "schema.table" }.

read_table¶

Targets: Data Warehouse / SQL Analytics Endpoint

Return up to count rows from a table as JSON-serialisable columns and rows.

Supports time-travel reads via as_of: supply an ISO-8601 UTC timestamp to read the table as it was at that point in time. The Fabric retention window is 1-120 days (default 30); timestamps outside the window error server-side.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse or SQL analytics endpoint name or GUID.
• qualified_name (str): dot-separated table name, e.g. dbo.sales.
• count (int, default 10): maximum rows to return.
• as_of (str | null, optional): ISO-8601 UTC timestamp for a point-in-time read, e.g. 2024-03-15T10:00:00Z. Omit to read the latest data.

Returns: { "columns": list[str], "rows": list[list] }: column names and row arrays.

rename_table¶

Targets: Data Warehouse only

Rename a SQL table via sp_rename. Only supported on Fabric Data Warehouses (SQL Analytics Endpoints are rejected). The new name must be a bare (unqualified) identifier - sp_rename cannot move a table to a different schema.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID.
• qualified_name (str): current dot-separated qualified table name, e.g. dbo.sales.
• new_name (str): new bare table name (no schema prefix), e.g. sales_v2.

Returns: Table: the updated table record.

transfer_table¶

Targets: Data Warehouse only

Move a SQL table to another schema via ALTER SCHEMA ... TRANSFER OBJECT::.... Only supported on Fabric Data Warehouses: transferring a table between schemas via T-SQL is not supported on the Fabric SQL Analytics Endpoint and can break the OneLake sync, so SQL Analytics Endpoints are rejected with a ToolError.

CAUTION: Permissions granted directly on the table are dropped by the engine when the schema changes. Dependent views and stored procedures that reference the table by its old schema-qualified name are NOT automatically updated and may need refreshing after the transfer.

Reference: ALTER SCHEMA (Transact-SQL)

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): current dot-separated qualified table name, e.g. dbo.sales.
• target_schema (str): schema to move the table into, e.g. archive.

Returns: Table: the moved table record, fetched from the new schema.

set_cluster_columns¶

Targets: Data Warehouse only

Change (or remove) the data-clustering columns of an existing table via a transactional CTAS-swap. Requires FABRIC_MCP_ALLOW_DESTRUCTIVE=1.

Performance note: This operation copies the entire table. Runtime is proportional to table size.

CAUTION: Dependent views and stored procedures that reference this table by name are NOT automatically updated by sp_rename and may need refreshing after the swap.

The operation is atomic: CTAS + DROP + sp_rename all run in one transaction. Any failure rolls back automatically - no orphan temp table is left behind.

Parameters:

• workspace (str): workspace name or GUID.
• item (str): warehouse name or GUID. SQL Analytics Endpoints are rejected.
• qualified_name (str): dot-separated qualified table name, e.g. dbo.sales.
• cluster_by (list[str] | null, optional): new column names for the CLUSTER BY clause (up to 4). Pass null or an empty list to remove clustering (rebuilds table without CLUSTER BY).

Returns: Table: the re-clustered table record.