publish_sqlproj¶

Publish a Fabric SQL database or a warehouse from its item folder: the schema, the shared queries, and the pre- and post-deployment scripts the project references. fab deploy creates both types empty; this puts everything in them.

Step fragment for a hooks file. Shared step keys are under Steps.

- action: publish_sqlproj
  item: <Name>.SQLDatabase           # or <Name>.Warehouse
  timeout_seconds: 900
  on_error: fail                     # default: stop the deploy here; `continue` logs it and goes on

Keys¶

Behaviour¶

The hook sends the item folder, unchanged, as the item's definition in sqlproj format: the project file, every .sql file, and .sharedqueries/*.sql. Fabric then does what it does on an update from git (SQL database source control): it builds the project, publishes the schema, deploys the shared queries into the editor and runs the scripts the project references.

The call is fab api …/updateDefinition, on sqlDatabases/<id> for a SQL database and items/<id> for a warehouse. It is a long-running operation the hook polls until it succeeds or fails. A build error fails the step with Fabric's message. The step prints what it sent: the object count, the shared query count, and the two script names it found in the project.

fabric-cicd calls the same endpoint for most item types, but it lists Warehouse and SQLDatabase in SHELL_ONLY_PUBLISH: it creates the item with its name and description only, and on later deploys patches those two fields, so the folder never reaches Fabric through fab deploy. This hook is that missing call.

Fabric runs a publish in this order:

1. The pre-deployment script.
2. The schema change: Fabric compares the project with the item and applies the difference.
3. The post-deployment script.

A change that would lose data fails at step 2, on both types. Dropping a column from a table that holds rows fails the step with DataLoss: The column [dbo].[<table>].[<column>] is being dropped, data loss might occur. The table is left as it was, the post-deployment script does not run, and the deploy stops at the hook. The pre-deployment script has already run by then, and its work is not rolled back. Write it so that a publish that then fails leaves nothing wrong behind. Treat schema changes that lose data as forward-only.

On a warehouse, an object the project does not hold is left alone, not dropped; a SQL database has not been tried. Removing a table's file from the folder, or never committing a table a script created, keeps it in the item. Drop it by hand, or in a pre-deployment script.

bin/, obj/ and any file that is not .platform, .sqlproj or .sql are left out, because the API names the extensions it accepts. Nothing is written to the repo.

Name it under post_deploy_hooks on the item's node, before any step that reads the tables.

Pre- and post-deployment scripts¶

A script that runs before or after every publish, such as a MERGE that seeds a lookup table or a GRANT the project cannot express, is a shared query marked as one in the Fabric editor. Each item takes one of each.

For a SQL database:

1. Open the database in the Fabric portal and create a query holding the script.
2. Rename it, for example seed-pipeline-config.sql, and move it to Shared Queries.
3. On its ... menu choose Set as Post-deployment Script or Set as Pre-deployment Script.
4. Commit from the workspace's Source control panel.

For a warehouse, mark a shared query under the warehouse's Settings, CI/CD, or from Set deployment scripts on the query in Object Explorer, then commit (deployment scripts, preview).

From then on the script runs on every publish, by this hook or by Fabric's own update from git, on a new item and on one that already holds the schema. Keep it safe to run again: a MERGE, or TRUNCATE then INSERT, never a bare INSERT. It runs as the deploying identity, which needs the permissions its statements do.

The commit writes the marks into the project file, each in its own ItemGroup:

<ItemGroup>
  <PreDeploy Include=".sharedqueries/pre-deployment.sql" />
</ItemGroup>
<ItemGroup>
  <PostDeploy Include=".sharedqueries/post-deployment.sql" />
</ItemGroup>

A reference written by hand works too, with either slash, and the next commit from a workspace keeps it, rewritten in this form. Marking the query in the editor is still the simplest way to get it right.

Needs¶

Nothing beyond fab. The deploying identity must be able to write the item, which Contributor on the workspace gives. A service principal works: the sample's pipeline principal, a Contributor on dev, publishes both of the sample's items this way.

SQL database definition · Warehouse definition · Update Item Definition