Launch Deal · 50% off 1st month ·

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?

Report issue

Log in to save your progress.