How to version table schemas in Azure Synapse Analytics: step by step

João Barros 03 de November de 2026 5 min read

This tutorial explains how to implement a simple method to version table schemas in Azure Synapse Analytics, in order to maintain a history and facilitate rollbacks. The approach is useful for teams that change tables in Dedicated SQL Pool or in SQL databases within the workspace and need change control without relying on complex external tools.

Prerequisites

Azure account with permissions to create and alter objects in Azure Synapse Analytics.
Synapse workspace with Dedicated SQL Pool (or SQL database within the workspace).
Basic knowledge of T-SQL and naming conventions.

Step 1: Understand the why and choose a strategy

There are several ways to version schemas: migrations with CI/CD, external tools, or a simple pattern based on object naming and metadata tables. Here we use a practical method: create a schema history table and apply versions with names that include a version number. This allows quick rollback and auditing without requiring external pipelines.

Step 2: Create the schema metadata table

Create a table where you will record each schema change: object, version, author, date and change script. Storing the script allows you to reconstruct the previous state.

CREATE TABLE dbo.SchemaVersioning (
  ObjectName sysname NOT NULL,
  VersionNumber INT NOT NULL,
  AppliedBy sysname NOT NULL,
  AppliedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
  ChangeScript NVARCHAR(MAX) NOT NULL,
  CONSTRAINT PK_SchemaVersioning PRIMARY KEY (ObjectName, VersionNumber)
);

Step 3: Standardize names for versioned tables

Adopt a simple convention for active and historical tables. For example, base table mytable_v1; when you change the schema create mytable_v2 and then update the view or synonym to point to the active version.

-- naming example
-- Active table: Sales_v3
-- Next table: Sales_v4

Step 4: Export the current DDL and create the new version

Before changing, export the current DDL (for example with SCRIPT TABLE in Synapse Studio or using OBJECT_DEFINITION). Then create the new table with the new DDL and copy data if needed.

-- get simplified DDL (example of capturing column schema)
SELECT c.name, t.name AS type_name, c.max_length, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE object_id = OBJECT_ID('dbo.Sales_v3');

-- create new version (minimal example)
CREATE TABLE dbo.Sales_v4 (
  SalesId INT NOT NULL,
  ProductId INT NOT NULL,
  Quantity INT NOT NULL,
  SaleDate DATETIME2 NOT NULL,
  Region NVARCHAR(50) NULL
);

-- copy data from previous version (when compatible)
INSERT INTO dbo.Sales_v4 (SalesId, ProductId, Quantity, SaleDate)
SELECT SalesId, ProductId, Quantity, SaleDate FROM dbo.Sales_v3;

Step 5: Register the version in the metadata table

Save the script used to create or alter the new version inside SchemaVersioning. This enables auditability and rollback.

DECLARE @script NVARCHAR(MAX) = N'
CREATE TABLE dbo.Sales_v4 (
  SalesId INT NOT NULL,
  ProductId INT NOT NULL,
  Quantity INT NOT NULL,
  SaleDate DATETIME2 NOT NULL,
  Region NVARCHAR(50) NULL
);
';

INSERT INTO dbo.SchemaVersioning (ObjectName, VersionNumber, AppliedBy, ChangeScript)
VALUES ('dbo.Sales', 4, SUSER_SNAME(), @script);

Step 6: Update the pointer to the active version

Instead of changing applications, use a view or a synonym that will always point to the active version. Changing the pointer is a quick and safe operation.

-- use view for pointer
IF OBJECT_ID('dbo.vw_Sales', 'V') IS NOT NULL
  DROP VIEW dbo.vw_Sales;

CREATE VIEW dbo.vw_Sales AS
SELECT * FROM dbo.Sales_v4;

Step 7: Roll back if necessary

If the new version has issues, recreate the view to point to the previous version and record a new rollback entry in SchemaVersioning. For a full rollback, you can also recreate the previous table from the stored script.

-- simple rollback: point view to previous version
ALTER VIEW dbo.vw_Sales AS
SELECT * FROM dbo.Sales_v3;

-- register rollback
INSERT INTO dbo.SchemaVersioning (ObjectName, VersionNumber, AppliedBy, ChangeScript)
VALUES ('dbo.Sales', 5, SUSER_SNAME(), 'Rollback to v3 via ALTER VIEW');

Verify the result

Check that the view points to the correct table and that the metadata table contains records for each change. Test read queries and, if applicable, validate data integrity after the copy.

-- confirm view pointer
SELECT OBJECT_NAME(OBJECT_ID) AS ViewName, definition
FROM sys.sql_modules m
JOIN sys.views v ON m.object_id = v.object_id
WHERE v.name = 'vw_Sales';

-- list versions
SELECT * FROM dbo.SchemaVersioning WHERE ObjectName = 'dbo.Sales' ORDER BY VersionNumber DESC;

Conclusion

Versioning schemas directly in Azure Synapse Analytics with a metadata table and naming conventions is a lightweight and effective solution for change control and fast rollback. Next steps: integrate this flow with CI/CD pipelines or automate DDL creation from templates. Tip: always test the rollback process in a staging environment before production — which part of the process do you want to automate first?