1.3 — Database Projects and Deployment Pipelines
DP-700 Domain 1 · Skill 1.3
What You Need to Know
Two lifecycle tools appear in this skill. Database projects treat a warehouse schema as code that you can build and validate. Deployment pipelines move items from Development to Test to Production inside Fabric.
Database projects for warehouses
| Fact | Detail |
|---|---|
| Format | An SDK-style SQL database project (.sqlproj). Fabric Data Warehouse uses the target platform SqlDwUnifiedDatabaseSchemaProvider |
| How it's created | Committing a warehouse to Git saves it as a database project. You can also create one in VS Code with the SQL Database Projects extension |
| Build | Checks every object's syntax and references and produces a .dacpac. Broken references fail the build (for example error SQL71501, unresolved reference) |
| Publish | SqlPackage compares the .dacpac with the target warehouse and applies only the differences |
| Cross-warehouse dependencies | Add a database reference to the other warehouse in the project so the build can resolve three-part names |
| Pre- and post-deployment scripts | Run SQL before or after the schema is applied, for example to seed reference data (preview) |
Use a database project when you need to catch invalid references before deployment, review schema changes in pull requests, or deploy a warehouse from a build pipeline.
Deployment pipelines
- A pipeline has 2 to 10 stages, and each stage is assigned one workspace. You deploy to an adjacent stage, usually forward, though backward deployment also works.
- Deploying copies item definitions (metadata), not data. After a first deployment, refresh semantic models or reload tables in the target stage.
- Pairing: items are paired when a workspace is assigned to a stage, or when a new item is first deployed. A paired item is overwritten on the next deployment; its ID, URL and permissions stay the same. An item with the same name that isn't paired produces a duplicate instead.
- Folders: an item's folder path counts as part of its name. If you move an item into a folder and deploy it, the paired item moves too, and the folder is created in the target if needed. Empty folders aren't deployed.
- Autobinding: a deployed item reconnects to the item it depends on in the target stage. If that item doesn't exist there, the deployment fails, so use Select related.
What is and isn't copied
| Copied | Not copied |
|---|---|
| Item definitions, data sources and parameters (adjustable with rules), report pages, model metadata, relationships between items | Data, item ID and URL, permissions, workspace settings, app content |
| For semantic models: role assignments, refresh schedule, data source credentials, endorsement |
Making values differ per stage
- Deployment rules change a data source or parameter in the target stage, for example pointing Test at a test database.
- Variable libraries hold one set of values per stage, which items such as pipelines and notebooks read at run time.
- The gateway connection of a target item isn't mapped automatically. Configure it once after the first deployment; later deployments keep it.
Permissions and automation
- To deploy, you need the pipeline Admin role and at least Contributor on both the source and target workspaces.
- Deployment pipelines REST APIs let Azure DevOps or GitHub Actions run deployments. Authenticate with a service principal (or managed identity) that has access to the pipeline and workspaces. Don't use a user's password.
- You can attach a deployment plan (preview) to control the order of items and run actions before or after each deployment.
Key Concept: Deployment pipelines move definitions between stages, and pairing decides whether an item is overwritten or duplicated. A database project validates a warehouse schema before anything is deployed.
Exam Traps
- Expecting deployment to copy table data or semantic model data
- Deleting target items "to make overwrite work" when they're already paired (just deploy)
- Expecting a semantic model's refresh schedule to follow it to the next stage
- Renaming items so that they pair (pairing doesn't depend on names)
- Signing in to the REST API with a user name and password instead of a service principal
Check your understanding
Practice Scenario
You use a deployment pipeline to deploy a semantic model and its report from the Development stage to an empty Test stage for the first time. In Test, the report shows no data, and the daily refresh schedule you set up in Development is missing. What explains both symptoms?
Log in to save your progress.
Sources
- Develop warehouse projects in Visual Studio Code, Microsoft Learn
- Develop and deploy cross-warehouse dependencies, Microsoft Learn
- Introduction to deployment pipelines, Microsoft Learn
- The deployment pipelines process, Microsoft Learn
- Automate deployment pipelines using Fabric APIs, Microsoft Learn