Skip to main content

Automatic Azure Fabric Data Warehouse Schema Migrations with Atlas

The Fabric Data Warehouse is a cloud-native analytical warehouse in Microsoft Fabric that unifies SQL-based warehousing with a data lake foundation for high-scale, performant analytics.

It's a popular tool for analyzing data within the Microsoft ecosystem, but managing its schemas can be challenging when dealing with complex data pipelines, multiple teams, and coordinated schema changes across your analytics infrastructure.

Enter: Atlas​

Atlas helps developers manage their database schema as code - abstracting away the intricacies of database schema management. With Atlas, users provide the desired state of the database schema and Atlas automatically plans the required migrations.

In this guide, we will dive into setting up Atlas for Azure Fabric Data Warehouse schema migration, and introduce the different available workflows.

Prerequisites​

  1. An Azure account with access to Microsoft Fabric
  2. A Fabric workspace with a Data Warehouse created
  3. Atlas installed on your machine:

To download and install the latest release of the Atlas CLI, simply run the following in your terminal:

curl -sSf https://atlasgo.sh | sh

Logging in to Atlas​

To use Azure Fabric Data Warehouse with Atlas, you'll need to log in to Atlas. If it's your first time, you will be prompted to create both an account and a workspace (organization):

atlas login

Database Connection URL​

To use Atlas with Azure Fabric Data Warehouse, we need to provide a connection URL to the database.

Atlas uses the azuresql scheme (from microsoft/go-mssqldb) to connect to Azure Fabric Data Warehouse.

Finding your connection details​

To find your Fabric Data Warehouse connection details:

  1. Navigate to your Microsoft Fabric workspace
  2. Select your Data Warehouse
  3. Click on Settings → SQL connection string
  4. Copy the server name (e.g., xyz123abc.datawarehouse.fabric.microsoft.com)
  5. Note your database name

Now that we have the server and database names, we can construct the connection URL based on authentication method.

Authentication methods​

Atlas supports the following authentication methods:

This method uses the default Azure credential chain, which automatically tries multiple authentication methods:

azuresql://<server>.datawarehouse.fabric.microsoft.com?database=<database>&fedauth=ActiveDirectoryDefault

This is the default approach, as it works with:

  • Azure CLI authentication (az login)
  • Environment variables
  • Managed Identity (when running in Azure)
  • Visual Studio Code Azure Account extension

For automated scenarios like CI/CD pipelines, a Service Principal with client credentials is more secure than password authentication and allows for better access control.

First, create a Service Principal in Azure:

  1. Go to Azure Portal → Microsoft Entra ID → App registrations
  2. Click New registration and create a new application
  3. After creation, store the Application (client) ID and Directory (tenant) ID
  4. Go to Certificates & secrets → New client secret and create a secret
  5. Copy the secret value (you won't be able to see it again)

Next, grant the Service Principal access to your Fabric Data Warehouse:

  1. In your Fabric workspace, go to Manage access
  2. Add the Service Principal with appropriate permissions (e.g., Contributor)

Then use the following connection URL format:

azuresql://<server>.datawarehouse.fabric.microsoft.com?database=<database>&fedauth=ActiveDirectoryServicePrincipal&client id=<client-id>&client secret=<client-secret>&tenant id=<tenant-id>

Replace:

  • <client-id> with your Application (client) ID
  • <client-secret> with your client secret value
  • <tenant-id> with your Directory (tenant) ID
tip

For security, store credentials in environment variables or a secret manager rather than hardcoding them in connection strings.

For this guide, we'll use the following example connection URL:

azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse&fedauth=ActiveDirectoryDefault

Replace xyz123abc and MyWarehouse with your actual server and database names.

Inspecting our Database​

Let's start by creating a simple table in your Fabric Data Warehouse. You can run this SQL in the Fabric SQL editor:

CREATE TABLE users (
  id bigint NOT NULL,
  name varchar(255) NOT NULL
);
ALTER TABLE users ADD CONSTRAINT PK_users PRIMARY KEY NONCLUSTERED (id) NOT ENFORCED;

The atlas schema inspect command supports reading the database description provided by a URL and outputting it in different formats, including Atlas DDL (default), SQL, and JSON. In this guide, we will demonstrate the flow using both the Atlas DDL and SQL formats.

info

For in-depth details on the atlas schema inspect command, covering aspects like inspecting specific schemas, handling multiple schemas concurrently, excluding tables, and more, refer to our documentation here.

Inspect your Azure Fabric Data Warehouse using the -u flag for the connection URL, and write the output to a file named schema.hcl:

atlas schema inspect -u "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse&fedauth=ActiveDirectoryDefault" > schema.hcl

Open the schema.hcl file to view the HCL schema that describes our database.

schema.hcl
table "users" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}
schema "dbo" {
}

This first block represents a table resource with id, and name columns.

The schema field references the dbo schema that is defined in the block below.

In addition, the primary_key sub-block defines the id column as the primary key for the table. Atlas will automatically add the NOT ENFORCED clause when generating SQL for Fabric Data Warehouse, as this constraint is required by the platform.

Visualizing our schema​

To generate an Entity Relationship Diagram (ERD), or a visual representation of our schema, we can add the -w flag to the inspect command:

atlas schema inspect -u "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse&fedauth=ActiveDirectoryDefault" -w

Declarative Migrations​

The declarative approach lets users manage schemas by defining the desired state of the database as code. Atlas then inspects the target database and calculates an execution plan to reconcile the difference between the desired and actual states.

Let's see this in action.

We will start by making a change to our schema file, such as adding a repos table:

table "users" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}

table "repos" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  column "owner_id" {
    type = bigint
    null = false
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}
schema "dbo" {
}

Now that our desired state has changed, Atlas will plan a migration for us to apply these changes to our database by running the atlas schema apply command.

Since Azure Fabric Data Warehouse is a cloud-only service without Docker support, we need to use a remote dev database. In this example, we will use a separate Fabric Data Warehouse, but you can also dedicate a different schema within your existing warehouse to use for development purposes.

atlas schema apply \
  -u "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse&fedauth=ActiveDirectoryDefault" \
  --to file://schema.hcl \
  --dev-url "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse_Dev&fedauth=ActiveDirectoryDefault"

Approve and apply the changes, and that's it! You have successfully run a declarative migration.

info

For a more detailed description of the atlas schema apply command refer to our documentation here.

Versioned Migrations​

Alternatively, the versioned migration workflow, sometimes called "change-based migrations", allows each change to the database schema to be checked-in to source control and reviewed during code-review. Users can still benefit from Atlas intelligently planning migrations for them, however they are not automatically applied.

Creating the first migration​

In the versioned migration workflow, our database state is managed by a migration directory. The migration directory holds all of the migration files created by Atlas, and the sum of all files in lexicographical order represents the current state of the database.

To create our first migration file, we will run the atlas migrate diff command with the necessary parameters:

  • --dir: URL to the migration directory (file://migrations by default).
  • --to: URL of the desired state. A state can be specified using a database URL, HCL or SQL schema, or another migration directory.
  • --dev-url: URL to a Dev Database that will be used to compute the diff.

Since Azure Fabric Data Warehouse is a cloud-only service without Docker support, we need to use a remote dev database. In this example, we will use a separate Fabric Data Warehouse, but you can also dedicate a different schema within your existing warehouse to use for development purposes.

atlas migrate diff initial \
  --to file://schema.hcl \
  --dev-url "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse_Dev&fedauth=ActiveDirectoryDefault"

Run ls migrations, and you'll notice that Atlas has automatically created a migration directory for us, as well as two files looking similar to the following:

-- atlas:txmode none

-- Create "users" table
CREATE TABLE [dbo].[users] (
  [id] bigint NOT NULL,
  [name] varchar(255) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL
);
ALTER TABLE [dbo].[users] ADD CONSTRAINT [PK_users] PRIMARY KEY NONCLUSTERED ([id]) NOT ENFORCED;

-- Create "repos" table
CREATE TABLE [dbo].[repos] (
  [id] bigint NOT NULL,
  [name] varchar(255) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,
  [owner_id] bigint NOT NULL
);
ALTER TABLE [dbo].[repos] ADD CONSTRAINT [PK_repos] PRIMARY KEY NONCLUSTERED ([id]) NOT ENFORCED;

The migration file represents the current state of our database, and the sum file is used by Atlas to maintain the integrity of the migration directory. To learn more about the sum file, read the documentation.

Pushing migration directories to Atlas​

Now that we have our first migration, we can apply it to a database. There are multiple ways to accomplish this, with most methods covered in the guides section. In this example, we'll demonstrate how to push migrations to Atlas Cloud, much like how Docker images are pushed to Docker Hub.

Let's name our new migration project app and run atlas migrate push:

atlas migrate push app \
  --dev-url "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse_Dev&fedauth=ActiveDirectoryDefault"

Once the migration directory is pushed, Atlas prints a URL to the Atlas Cloud project page where you can follow the migration history.

Applying migrations​

Now that we have a new location for our migration directory, let's create a simple Atlas configuration file (atlas.hcl) to store the settings for our environment:

atlas.hcl
# The "prod" environment represents our production warehouse.
env "prod" {
  url = "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse&fedauth=ActiveDirectoryDefault"
  migration {
    dir = "atlas://app"
  }
}

Run atlas migrate apply using the --env flag to instruct Atlas to select the prodenvironment configuration from the atlas.hcl` file:

atlas migrate apply --env prod

And that's it! After applying the migration, you should receive a link to the deployment and the database where the migration was applied.

Generating another migration​

After applying the first migration, it's time to update our schema defined in the schema file and tell Atlas to generate another migration. This will bring the migration directory (and the database) in line with the new desired state defined in the schema file.

Let's make two changes to our schema:

  • Add a new description column to our repos table
  • Add a new commits table
schema.hcl
table "users" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}

table "repos" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "name" {
    type = varchar(255)
    null = false
  }
  column "owner_id" {
    type = bigint
    null = false
  }
  column "description" {
    type = varchar(max)
    null = true
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}
table "commits" {
  schema = schema.dbo
  column "id" {
    type = bigint
    null = false
  }
  column "message" {
    type = varchar(255)
    null = false
  }
  column "repo_id" {
    type = bigint
    null = false
  }
  column "author_id" {
    type = bigint
    null = false
  }
  primary_key {
    nonclustered = true
    columns = [column.id]
  }
}
schema "dbo" {
}

Next, let's run the atlas migrate diff command once more:

atlas migrate diff add_commits \
  --to file://schema.hcl \
  --dev-url "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse_Dev&fedauth=ActiveDirectoryDefault"

Run ls migrations, and you'll notice that a new migration file has been generated.

20260114193410_add_commits.sql
-- atlas:txmode none

-- Modify "repos" table
ALTER TABLE [dbo].[repos] ADD [description] varchar(MAX) NULL;
-- Create "commits" table
CREATE TABLE [dbo].[commits] (
  [id] bigint NOT NULL,
  [message] varchar(255) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL,
  [repo_id] bigint NOT NULL,
  [author_id] bigint NOT NULL
);
ALTER TABLE [dbo].[commits] ADD CONSTRAINT [PK_commits] PRIMARY KEY NONCLUSTERED ([id]) NOT ENFORCED;

Let's run atlas migrate push again and observe the new file on the migration directory page.

atlas migrate push app \
  --dev-url "azuresql://xyz123abc.datawarehouse.fabric.microsoft.com?database=MyWarehouse_Dev&fedauth=ActiveDirectoryDefault"

Azure Fabric-Specific Considerations​

When working with Azure Fabric Data Warehouse, keep these considerations in mind:

Transaction mode​

Azure Fabric Data Warehouse has limited transaction support. Atlas automatically adds the -- atlas:txmode none directive to migration files to ensure statements are executed outside of transactions.

Primary key and foreign key constraints​

Fabric Data Warehouse requires primary keys and foreign keys to use NOT ENFORCED constraints. These constraints are used for query optimization but are not enforced at the database level.

Atlas automatically adds the NOT ENFORCED clause when generating SQL statements for Fabric Data Warehouse. You don't need to specify any special attributes in your HCL schema - just define your primary keys and foreign keys as you normally would:

primary_key {
  nonclustered = true
  columns = [column.id]
}

foreign_key "fk_repo_owner" {
  columns     = [column.owner_id]
  ref_columns = [table.users.column.id]
}

Atlas will generate the appropriate SQL with NOT ENFORCED:

ALTER TABLE [dbo].[users] ADD CONSTRAINT [PK_users] PRIMARY KEY NONCLUSTERED ([id]) NOT ENFORCED;
ALTER TABLE [dbo].[repos] ADD CONSTRAINT [fk_repo_owner] FOREIGN KEY ([owner_id]) REFERENCES [dbo].[users]([id]) NOT ENFORCED;

No Docker support​

Azure Fabric Data Warehouse cannot be run locally in Docker, so you'll need to provision a separate empty Fabric Data Warehouse or use a dedicated empty schema within your workspace for development purposes.

Authentication​

Azure Fabric requires Azure Active Directory (Entra ID) authentication. Make sure you're authenticated via one of these methods:

  • Azure CLI (az login) - for local development
  • Service Principal - recommended for CI/CD pipelines (see Authentication Methods above)
  • Managed Identity - when running in Azure services
  • Environment variables (AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_TENANT_ID) - used by DefaultAzureCredential

Next Steps​

In this guide we learned about the declarative and versioned workflows, and how to use Atlas to generate migrations, push them to an Atlas workspace and apply them to databases.

Next steps:

For more in-depth guides, check out the other pages in this section or visit our Docs section.

Have questions? Feedback? Find our team on our Discord server.