Creating & managing tables and views¶

This guide walks through building out a schema model on a Microsoft Fabric Data Warehouse with fabric-dw — from an empty schema, through populated tables, to reporting views and the statistics that keep the optimizer honest. Every step shows a runnable CLI example (fdw …) and names the equivalent MCP tool, so the same workflow applies whether you drive it from a terminal or from an AI assistant wired to the MCP server.

The flat per-command references stay the source of truth for every flag and parameter: Schemas, Tables, Views, and Statistics. This guide ties them together as a single narrative.

What you'll build¶

A small star-schema-style model in one warehouse:

• a custom sales schema,
• a dimension-style table (sales.customer) created from explicit columns,
• a fact-style table (sales.orders) loaded from a file,
• a reporting view (sales.vw_orders_by_month) kept under version control as a .sql file,
• single-column statistics so the optimizer can plan joins and filters.

You'll then maintain and iterate on the model — clone, rename, re-cluster, clear, and finally tear it down.

CLI vs MCP — when to use which¶

• CLI (fdw …) is the full surface. File-based schema inference (--from-parquet / --from-csv / --from-json), tables load, and if-exists replace are CLI-only because they need reliable local file access.
• MCP tools mirror the CLI for everything that doesn't depend on server-side file access, so an AI assistant can author and inspect the same objects. Where an MCP equivalent exists it's named in each step; where it doesn't (notably tables load), the gap is called out.

Prerequisites¶

• fabric-dw installed and authenticated (the Azure credential chain — Azure CLI, managed identity, service principal, and more).
• A target warehouse you can write to. The examples use a workspace MyWorkspace and a warehouse SalesWH; substitute your own (both are resolvable by name or GUID, and both default from your configuration, so -w/the item argument can be omitted once defaults are set).
• For MCP usage, the MCP server registered with your assistant. Mutating and destructive tools have their own opt-in guards (see Destructive operations below).

Set your defaults¶

Store the workspace and warehouse once so you do not repeat them on every command:

fdw config set workspace MyWorkspace
fdw config set warehouse SalesWH

The rest of this guide assumes these defaults are set, so the examples omit -w MyWorkspace and drop the warehouse positional where it is optional. Any command still accepts an explicit -w/--workspace or a positional [WAREHOUSE]/[ITEM] to override them. Commands that take a trailing required argument (such as tables clear … QUALIFIED_NAME or schemas create … NAME) keep the warehouse positional so the remaining arguments stay unambiguous. See Configuration & defaults.

Step 1 — Create a schema¶

Schemas are the namespace for your tables and views. dbo always exists; create custom schemas to group related objects.

Targets: Data Warehouse · SQL Analytics Endpoint

# Create the schema (warehouse positional kept — schema NAME follows)
fdw schemas create SalesWH sales

# Confirm it exists (system schemas like sys / INFORMATION_SCHEMA are excluded; dbo is shown)
fdw schemas list

MCP: create_schema, list_schemas.

Step 2 — Create tables¶

tables create is a single command with mutually-exclusive source modes (validated client-side before any DDL runs). All of them are Data Warehouse only — tables can't be created on a SQL Analytics Endpoint.

Targets: Data Warehouse only · MCP: create_table (CTAS), create_empty_table (explicit columns)

Empty table from explicit columns¶

The most direct way to scaffold a dimension table. --column NAME:TYPE[:null|notnull] is repeatable; columns are nullable unless you append :notnull.

fdw tables create \
  --name sales.customer \
  --column "customer_id:BIGINT:notnull" \
  --column "name:VARCHAR(200):notnull" \
  --column "region:VARCHAR(100)" \
  --column "signed_up:DATE"

MCP (create_empty_table) takes an explicit columns list — each entry is {name, sql_type, nullable?}:

{
  "workspace": "MyWorkspace",
  "item": "SalesWH",
  "qualified_name": "sales.customer",
  "columns": [
    {"name": "customer_id", "sql_type": "BIGINT", "nullable": false},
    {"name": "name", "sql_type": "VARCHAR(200)", "nullable": false},
    {"name": "region", "sql_type": "VARCHAR(100)"},
    {"name": "signed_up", "sql_type": "DATE"}
  ]
}

Infer a table from a data file (Parquet, CSV, or JSON)¶

All three file-based flags — --from-parquet, --from-csv, and --from-json — do the same thing conceptually: read only the schema from a data file and scaffold an empty table (no rows are read or inserted). How much of the file each flag reads differs by format:

• Parquet — reads only the Parquet footer, which encodes exact column names and types; no row-group data is touched.
• CSV — reads the header row plus a bounded sample of rows (controlled by --sample-rows) to infer types; accepts --delimiter and --encoding for non-default files.
• JSON — reads a bounded sample of records from a JSONL file (one JSON object per line) or a JSON file containing an array of objects.

Shared options (available on all three paths unless noted):

Mutual exclusivity: --from-parquet, --from-csv, --from-json, --column, and the CTAS flags --select/--from-file are all mutually exclusive — pick exactly one column source per tables create invocation.

# From a Parquet footer (exact types, no sampling)
fdw tables create \
  --name sales.orders \
  --from-parquet ./exports/orders.parquet

# From a CSV header with type inference
fdw tables create \
  --name staging.raw_products \
  --from-csv ./data/products.csv --varchar-length 500

# From a JSONL file (one JSON object per line)
# data/audit_log.jsonl: {"id": 1, "action": "login", "occurred_at": "2026-01-01T00:00:00"}
fdw tables create \
  --name sales.audit_log \
  --from-json ./data/audit_log.jsonl --varchar-length 500

Once the table is created, load data into it with tables load --format <parquet|csv|json>.

CTAS — create a table from a query¶

CREATE TABLE … AS SELECT materialises a query result into a new table. Supply the SELECT inline with --select, or keep the body under version control and pass --from-file body.sql. The body is rejected client-side if its first non-comment keyword isn't SELECT.

# Inline SELECT
fdw tables create \
  --name sales.orders_2026 \
  --select "SELECT * FROM sales.orders WHERE YEAR(sale_date) = 2026"

# Body from a versioned .sql file, with CLUSTER BY
fdw tables create \
  --name sales.orders_2026 \
  --from-file ./sql/orders_2026.sql \
  --cluster-by customer_id --cluster-by sale_date

MCP: create_table (pass the SELECT as select_body, and optionally cluster_by).

--cluster-by COL is repeatable (up to 4) on any create mode and emits WITH (CLUSTER BY (…)). On the empty-DDL paths each clustering column must exist in the schema; on the CTAS path existence isn't validated, because the result columns come from the SELECT.

Inspect what you built¶

Targets: Data Warehouse · SQL Analytics Endpoint · MCP: get_table_columns, count_table_rows, list_tables

# Column metadata (name, type, nullability, ordinal, collation, identity/computed)
fdw tables columns SalesWH sales.customer

# Row count via COUNT_BIG(*)
fdw --json tables count SalesWH sales.orders

# List tables in a schema
fdw tables list --schema sales

Step 3 — Populate the tables¶

Loading data is its own topic. The bridge from "empty table" to "populated table" is tables load, which issues COPY INTO from a local file or a remote URL (and can auto-create the table from the source schema with --create):

fdw tables load SalesWH sales.orders --file ./data/orders.parquet

Targets: Data Warehouse only. There is no MCP load tool for local files; the MCP server exposes load_table_from_url / import_table_from_url for remote URLs only (local staging needs reliable file access). For the full loading surface — --create, --if-exists, credentials for secured external URLs, CSV options — see the tables load reference rather than this guide.

Step 4 — Create views¶

Views give consumers a stable, named query. Unlike tables, views are creatable on both a Data Warehouse and a SQL Analytics Endpoint — there's no Warehouse-only guard.

Targets: Data Warehouse · SQL Analytics Endpoint · MCP: create_view, update_view, get_view, get_view_columns, rename_view, list_views

Create from a versioned .sql file¶

Keeping the view body in a .sql file (rather than inlining SQL) makes it reviewable and diff-able. The body is rejected client-side if it isn't a SELECT.

# sql/vw_orders_by_month.sql:
# SELECT region,
#        DATETRUNC(month, sale_date) AS month,
#        COUNT_BIG(*)               AS order_count,
#        SUM(amount)                AS total_amount
# FROM sales.orders
# GROUP BY region, DATETRUNC(month, sale_date)

fdw views create \
  --name sales.vw_orders_by_month \
  --from-file ./sql/vw_orders_by_month.sql

You can also create inline with --select "<SELECT>". MCP: create_view (pass the SELECT as select_body).

Inspect and update a view¶

# Full definition (from sys.sql_modules) — warehouse positional kept, view name follows
fdw views get SalesWH sales.vw_orders_by_month

# Column metadata
fdw views columns SalesWH sales.vw_orders_by_month

# Redefine in place via CREATE OR ALTER VIEW (prompts for confirmation)
fdw views update SalesWH sales.vw_orders_by_month \
  --from-file ./sql/vw_orders_by_month.sql

MCP: get_view, get_view_columns, update_view (which runs CREATE OR ALTER VIEW).

Step 5 — Help the optimizer¶

After you load data, give the query optimizer the statistics it needs. fabric-dw manages single-column statistics (a Fabric limitation — multi-column statistics aren't supported), and you must pass an explicit --name (Fabric requires an explicit statistic name — there is no auto-generated default).

Targets: Data Warehouse only (create/update/delete) · Data Warehouse · SQL Analytics Endpoint (list/show) · MCP: create_statistics, update_statistics, list_statistics, show_statistics

# Create a single-column statistic
fdw statistics create \
  --table sales.orders --column region --name stat_orders_region

# Refresh it after a load (warehouse positional kept — table/stat names follow)
fdw statistics update SalesWH sales.orders stat_orders_region

# List / inspect
fdw statistics list --table orders
fdw statistics show SalesWH sales.orders stat_orders_region

MCP: create_statistics, update_statistics, list_statistics, show_statistics. The mutating statistics tools are DW-only; list_statistics / show_statistics also work on a SQL Analytics Endpoint.

Layout tuning with clustering¶

Beyond statistics, CLUSTER BY controls physical data layout. You set clustering at create time (Step 2) or change it later on an existing table:

# Re-cluster an existing table (transactional CTAS-swap; copies the whole table)
fdw --yes tables cluster-by SalesWH sales.orders \
  --cluster-by customer_id --cluster-by sale_date

# Remove clustering (omit --cluster-by entirely)
fdw --yes tables cluster-by SalesWH sales.orders

# Inspect current clustering columns
fdw tables cluster-columns SalesWH sales.orders

Targets: Data Warehouse only · MCP: set_cluster_columns (destructive — copies the full table), get_cluster_columns.

Step 6 — Maintain & iterate¶

Clone a table (zero-copy, near-instant, independent of the source). Add --at for a point-in-time clone within the warehouse's data-retention window:

# Current-state clone
fdw tables clone --source sales.orders --name sales.orders_backup

# Point-in-time clone (UTC, within retention)
fdw tables clone \
  --source sales.orders --name sales.orders_may_snapshot \
  --at 2026-05-20T14:00:00

Rename a table or view (sp_rename; the new name must be unqualified — you can't move objects across schemas):

fdw tables rename SalesWH sales.orders_2025 --new-name orders_archive_2025
fdw views rename SalesWH sales.vw_recent --new-name vw_revenue

Clear a table (TRUNCATE TABLE — removes all rows, keeps the structure):

fdw --yes tables clear SalesWH staging.raw_products

Health-check a table on a SQL Analytics Endpoint — this is the inverse of the usual guard: tables health-check runs sp_get_table_health_metrics (Delta/Parquet layout diagnostics) and is rejected on a Data Warehouse:

fdw tables health-check MySqlEndpoint dbo.FactSales

Tear down when you're done:

fdw --yes views drop SalesWH sales.vw_orders_by_month
fdw --yes tables delete SalesWH sales.orders
fdw --yes schemas delete SalesWH sales --cascade

schemas delete --cascade first drops all tables, views, functions, and stored procedures in the schema. Without --cascade, the engine rejects DROP SCHEMA on a non-empty schema. On a SQL Analytics Endpoint, cascade can't drop tables (no DROP TABLE there), so a schema still holding tables won't drop — remove them from the warehouse first.

MCP equivalents: clone_table, rename_table / rename_view, clear_table, get_table_health_metrics, drop_view, delete_table, delete_schema(..., cascade=True).

Destructive operations and the --yes flag¶

These commands prompt for confirmation before they run; pass --yes / -y to skip the prompt in scripts (at your own risk):

tables clear, tables delete, tables cluster-by, views drop, views update, schemas delete, statistics delete, and tables load --if-exists truncate|replace.

The matching MCP tools are marked destructive=True. An assistant must satisfy the server's destructive-operations guard before they execute — see the MCP server install page.

The SQL Analytics Endpoint read-only guard¶

A SQL Analytics Endpoint can't mutate tables. The following raise an error when the target item is a SQL Analytics Endpoint:

• tables create (CTAS and empty DDL), tables clone, tables rename, tables clear, tables delete, tables cluster-by,
• all mutating statistics operations (create / update / delete).

Everything else works on both item kinds: every list / read / columns / count / get operation, schemas create / delete, and all views mutations (create / update / rename / drop). The one inverse is tables health-check, which is SQL-Analytics-Endpoint-only and rejected on a Data Warehouse.

--json output and name-or-GUID resolution apply uniformly across every command group.

Limitations & gotchas¶

Fabric's T-SQL surface differs from SQL Server. The points below most often trip people up when authoring a schema model; each links the canonical Microsoft Learn guidance.

Unsupported data types¶

tables create --column accepts any type string you give it, but the engine rejects persisting columns whose type isn't supported for warehouse tables/views. Notably unsupported: money / smallmoney, datetime / smalldatetime, datetimeoffset, nchar / nvarchar, text / ntext, image, tinyint, geography / geometry, json, xml, CLR UDTs, and Vector. Use the documented alternatives instead — decimal, datetime2, char / varchar, varbinary, smallint, and so on. See Data types in Fabric Data Warehouse.

Constraints require NOT ENFORCED, and can't be inline¶

fabric-dw has no constraint command. When you do need keys, reach for the fdw sql / fdw queries escape hatch (MCP: execute_sql), and respect Fabric's rules:

• PRIMARY KEY / UNIQUE are only allowed when both NONCLUSTERED and NOT ENFORCED; FOREIGN KEY only when NOT ENFORCED.
• No default constraints.
• Constraints can't be declared inline in CREATE TABLE — add them afterwards with ALTER TABLE.

Because the keys are unenforced, the engine trusts them but doesn't validate them — don't treat an unenforced key as a guaranteed-unique JOIN candidate. See Table constraints and the performance guidelines.

T-SQL surface area¶

The Warehouse supports tables, views, procedures, and functions; TRUNCATE TABLE, sp_rename, CTAS, and a limited ALTER TABLE (add a nullable column, drop a column, add/drop NOT ENFORCED constraints) are supported. Creating, altering, or dropping tables and running DML are not supported on a Lakehouse SQL Analytics Endpoint — only views, table-valued functions, and stored procedures. This grounds the read-only-endpoint guard above. See T-SQL surface area.

Fixed, case-sensitive collation¶

A warehouse is created with the default Latin1_General_100_BIN2_UTF8 collation (case-sensitive), and collation can't be changed after creation. This makes schema and object names case-sensitive — sales.Orders and sales.orders are different. Settle your naming convention up front. See Collation in Fabric Data Warehouse.

Naming rules¶

Table and schema names can't contain / or \, and can't end with a .. See Tables in the warehouse.

Clone limits¶

tables clone is zero-copy and near-instant, and the clone is fully independent of its source. But: point-in-time clones (--at) are limited to the configured data-retention window (default 30 days, configurable 1–120); clones are not supported across warehouses or workspaces, nor on a SQL Analytics Endpoint; and there's no schema-level or warehouse-level clone. See Clone table, clone scenarios, and data retention.

Table design¶

For the schema model itself, follow the dimensional-modeling and table design guidance: fact / dimension / integration table categories, surrogate keys, types matched to semantics (date / datetime2, integer types for whole numbers, the smallest viable decimal precision — see data-type optimization), and statistics refreshed after loads. The canonical DDL references are CREATE TABLE and CREATE TABLE AS SELECT.

The same workflow via MCP¶

Every authoring and inspection step above maps to an MCP tool. The exceptions are file-dependent operations (local-file load and Parquet/CSV/JSON inference), which stay CLI-only.

For the MCP server setup, destructive-operation guards, and per-tool parameters, see the MCP server install page and the per-domain command references.