Skip to content

rbutinar/ade-ops / .agents/skills/fabric-sql-deploy

fabric-sql-deploy

Verified

Deploy SQL Scripts to a Fabric Warehouse

/fabric-sql-deploy — Deploy SQL Scripts to a Fabric Warehouse

You are deploying SQL DDL (views, stored procedures, schemas) to a Microsoft Fabric Warehouse over a T-SQL endpoint (pyodbc + Azure AD).

The required infra is now in place inside ade-ops:

  1. core.connectors.fabric_warehouse.FabricWarehouseConnector (pyodbc + AAD).
  2. core.platforms.fabric.auth.get_sql_token (MSAL cache, scope https://database.windows.net/.default).

For today this skill is not relevant in practice — uses Power BI on Databricks, not a Fabric Warehouse. Kept here for parity with the ADE lab harness, ready when a project starts deploying warehouse DDL.

Prerequisites

  • ODBC Driver 17 (or 18) for SQL Server installed
  • pyodbc, msal Python packages
  • Azure AD account with Contributor on the target warehouse
  • Scripts in the project's sql/ directory, alphabetically ordered (prefix with 01_, 02_, etc.)

Usage

/fabric-sql-deploy {target} --env {env}

Where target is one of:

  • all — schema + views + procedures (full deploy in order)
  • schema — create schemas if missing
  • views — deploy all *view*.sql (numeric order)
  • procedures — deploy all *procedure*.sql (numeric order)
  • cleanup — drop test/temporary objects
  • file:{path} — deploy a single SQL file

Behavior

Step 1: Resolve Project & Warehouse

Read:

overlay.fabric_warehouse.server          # e.g. abcd.datawarehouse.fabric.microsoft.com
overlay.fabric_warehouse.name            # e.g. wrh_SALES_PD
environments.{env}.platforms.fabric.workspace_id   # (for context)

(The overlay key fabric_warehouse is new — projects that need this skill add it.)

Step 2: Authenticate (SQL scope)

Use the framework connector — it handles MSAL + pyodbc internally:

from core.connectors.fabric_warehouse import FabricWarehouseConnector
connector = FabricWarehouseConnector.from_credentials(load_credentials(project_root))
conn = connector.client.connect()

Underneath: core.platforms.fabric.auth.get_sql_token_struct returns the AAD token packed for the pyodbc ActiveDirectoryAccessToken attribute (1256).

Step 3: Resolve Scripts

Locate under {project_root}/sql/. Expected naming:

sql/
  01_create_schema.sql
  02_view_*.sql
  03_procedure_*.sql
  99_cleanup.sql

For target=views, pick files matching *view*.sql in alphabetical order. Same logic for procedures.

For target=file:{path}, only that file.

Step 4: Confirmation Gate

=== FABRIC SQL DEPLOY ===
Env:       {env}
Warehouse: {server}/{warehouse_name}
Target:    {target}
Scripts:   {n} files
  01_create_schema.sql
  02_view_material_plant.sql
  ...

⚠️ This executes DDL against the warehouse.
Proceed? (yes/no — prod double-confirm)

Step 5: Execute

For each file (alphabetical order):

  1. Read UTF-8
  2. Split on GO (case-insensitive, line-anchored) — GO is not a T-SQL statement, it's a batch separator handled by SSMS / sqlcmd. pyodbc does not recognise it.
  3. Execute each batch via cursor.execute(batch), conn.commit()
  4. On error: log batch + error, stop processing this file (no automatic rollback — DDL is auto-commit in Fabric Warehouse)

Step 6: Verify

After deploy, query INFORMATION_SCHEMA:

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = '{target_schema}'

For each created view, run SELECT TOP 10 to confirm it parses against the current data.

Step 7: Log

{ISO_timestamp} | {role} | FABRIC-SQL-DEPLOY | {env} | {target} on {warehouse_name}: {ok_count} ok, {fail_count} fail | {ok|fail}

Fabric Warehouse Compatibility

Fabric Warehouse is a DW engine, not OLTP. Not supported:

PRIMARY KEY                           -- declarative PKs (informational only)
IDENTITY(...)                         -- auto-increment
FOREIGN KEY                           -- referential integrity
CREATE INDEX                          -- explicit indexes
ALTER TABLE ... ADD CONSTRAINT        -- runtime constraints

Supported:

CREATE SCHEMA / DROP SCHEMA
CREATE VIEW / DROP VIEW
CREATE PROCEDURE / DROP PROCEDURE
CREATE TABLE (simple, no PK/IDENTITY/FK)
SELECT ... INTO new_table FROM source   -- materialization

Script Conventions

-- 02_view_example.sql

DROP VIEW IF EXISTS automation.v_example;
GO

CREATE VIEW automation.v_example AS
SELECT col1, col2
FROM dbo.source_table;
GO

Use DROP ... IF EXISTS before CREATE so re-deploys are idempotent.

Logging Details

Deployment log (one per run): _data/analysis/deployment_log_{timestamp}.txt.

Common Errors

ErrorCauseFix
Incorrect syntax near 'PRIMARY'DDL has PRIMARY KEYRemove — Fabric Warehouse doesn't support it
403 ForbiddenUser lacks ContributorAsk warehouse admin
Invalid object name 'dbo.x'Referenced object missingDeploy dependencies first
pyodbc connection refusedNetwork / firewallCheck VPN, Fabric endpoint reachable
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 318kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 196kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 117kUpdated Claude CodeCodexVerifiedInstall