RESTORE

Microsoft Warehouse development – Pre Post Deployment scripts

Written by on in Microsoft Fabric

We continue our adventure with the Fabric Warehouse and database project in Visual Studio Code. In the first part, we discussed how to create a database project, add changes to it, and publish them to the target server. Today, we’ll expand on this concept a bit and explain how to use pre-deployment and post-deployment scripts—welcome to the read!

The scripts mentioned in the introduction are nothing more than code executed before the main database script and immediately after it. In a nutshell, it works like this:

  1. A differential script is generated between the project and the target database.
  2. The instructions contained in the pre-deployment script are executed.
  3. The script generated in step 1 is executed.
  4. The instructions contained in the post-deployment script are executed.

It’s worth noting that steps 1 and 3 are always executed in their entirety, while step 2 can vary depending on whether we’re performing a full deployment (drop – create) or an incremental one. This is very important to understand because the differential script is not “aware” of what happens in the pre- and post-deployment phases.

As for the mentioned scripts, within a single project, we can have a maximum of one pre-deployment script and one post-deployment script. However, there’s nothing stopping us from calling multiple instructions or other scripts from within them—we’ll discuss how to do this later in the article.

At this point, someone might reasonably ask: why do we need such scripts? There are many use cases, including:

  1. Preparing data and structures for the execution of the main database script,
  2. Copying data to a backup table and clearing the main table to avoid the “data loss may occur” error,
  3. Populating artificial dictionaries and dimensions (e.g., a time dimension),
  4. Logging deployment information,
  5. Other purposes.

There are indeed many use cases, and the ones listed above are just a few examples. Without wasting any more time, let’s move on to examples of using these features. To start, I’ve created a database project (I wrote about how to do this a week ago—you can find the article here). The project is very simple and contains only a table and a view within the dbo schema:

CREATE TABLE [dbo].[CustomerType]
(
    [CustomerTypeId] INT, 
    [CustomerType] VARCHAR(50) NULL, 
    [ModificationDate] DATETIME2 NULL , 
    [IsDeleted] BIT NULL
)

CREATE VIEW [dbo].[vGetCustomerType]
AS 
SELECT 
     [CustomerTypeId] I
    ,[CustomerType]	
FROM [dbo].[CustomerType]
WHERE [IsDeleted] = 0;
GO

Moving forward, there’s nothing left for us but to get to the heart of the matter; for the sake of order, I’ve added the following folders to place the appropriate scripts in them:

Let’s start with creating scripts; we’ll add them, of course, from the context menu by selecting Add Pre/Post-Deployment Script:

Therefore, I created one Pre and one Post Deployment script each:

The PreDeployment script, as the name suggests, will be executed before the actual deployment script. Upon opening it, we will see a short comment informing us what we can actually do at this point. In my test scenario, we will be cleaning the data if the table is not empty. The following T-SQL code will be used for this purpose:

IF OBJECT_ID('dbo.CustomerType') IS NOT NULL
BEGIN
    IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType)
    BEGIN
    
        TRUNCATE TABLE dbo.CustomerType;
    
    END
END

Why do I check if an object exists? It’s very important to realize that there are situations where this table doesn’t exist on the target server, so we won’t be able to clear it and will encounter an error. A question might arise: how can there be no target table? The answer is simple—for example, during the first deployment, the structures don’t exist at all! Another example could be a situation where a developer wants to set up the structures entirely on their development machine. We should always think in a way that ensures our scripts are universal and can be run every time, regardless of the state of the target environment.

Moving on to the Post Deployment script, it looks as follows:

INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
    ,[CustomerType]
)
VALUES
     (0,'Unknown')
    ,(1,'Company')
    ,(2,'Person');
GO

A simple INSERT that adds values to a previously cleared table. This way, we can insert various types of categories into tables that we cannot load from the source system. This is a common requirement, especially in data warehouse projects. Of course, the example presented is only illustrative, and instead of clearing and re-inserting data, a better idea would be to use the MERGE statement and avoid clearing the entire table each time. However, for now, we’ll leave it in this form.

Moving forward, there’s nothing left for us to do but generate the deployment file, after doing it we will receive our code, which will be executed on the target instance. Its main part looks as follows:

/*
Deployment script for dw01

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "dw01"
:setvar DefaultFilePrefix "dw01"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
    END


GO
IF OBJECT_ID('dbo.CustomerType') IS NOT NULL 
BEGIN IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType) 
    BEGIN 
        TRUNCATE TABLE dbo.CustomerType; 
    END 
END
GO

GO
/*
Table [shared].[Customer] is being dropped.  Deployment will halt if the table contains data.
*/

IF EXISTS (select top 1 1 from [shared].[Customer])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO
PRINT N'Dropping Table [shared].[Customer]...';


GO
DROP TABLE [shared].[Customer];


GO
PRINT N'Dropping Schema [shared]...';


GO
DROP SCHEMA [shared];


GO
PRINT N'Creating Table [dbo].[CustomerType]...';


GO
CREATE TABLE [dbo].[CustomerType] (
    [CustomerTypeId]   INT           NULL,
    [CustomerType]     VARCHAR (50)  NULL,
    [ModificationDate] DATETIME2 (7) NULL,
    [IsDeleted]        BIT           NULL
);


GO
PRINT N'Creating View [dbo].[vGetCustomerType]...';


GO
CREATE VIEW [dbo].[vGetCustomerType] 
AS 
SELECT 
[CustomerTypeId] I ,
[CustomerType] 
FROM [dbo].[CustomerType] 
WHERE [IsDeleted] = 0;
GO
INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
    ,[CustomerType]
)
VALUES
     (0,'Unknown')
    ,(1,'Company')
    ,(2,'Person');
GO

GO
PRINT N'Update complete.';


GO

In the above code we can also clearly see at which point the PreDeployment is executed – at that moment, the CustomerType table doesn’t exist yet, which is why I added a check to see if such a thing exists at all. Next, the DDL operations are called, which will create the objects, followed by the PostDeployment that populates the table. After running the script, I can try generating such a script again, and then I’ll see that the CustomerType table and its accompanying view won’t be created because they already exist on the target instance. However, the Pre/Post scripts are always executed regardless of the target database structure.

After executing above script, I decided to add a new table called Customer with a reference to the previously created CustomerType. Its definition looks as follows:

CREATE TABLE [dbo].[Customer]
(
    [Id] INT,
    [FirstName] VARCHAR(50),
    [LastName] VARCHAR(50),
    [FK_CustomerType_ID] INT
);
GO

After generating it, the differential script looks very interesting:

/*
Deployment script for dw01

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "dw01"
:setvar DefaultFilePrefix "dw01"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
    END


GO
IF OBJECT_ID('dbo.CustomerType') IS NOT NULL 
BEGIN IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType) 
    BEGIN 
        TRUNCATE TABLE dbo.CustomerType; 
    END 
END
GO

GO
/*
Table [shared].[Customer] is being dropped.  Deployment will halt if the table contains data.
*/

IF EXISTS (select top 1 1 from [shared].[Customer])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO
PRINT N'Dropping Table [shared].[Customer]...';


GO
DROP TABLE [shared].[Customer];


GO
PRINT N'Dropping Schema [shared]...';


GO
DROP SCHEMA [shared];


GO
PRINT N'Creating Table [dbo].[Customer]...';


GO
CREATE TABLE [dbo].[Customer] (
    [Id]                 INT          NULL,
    [FirstName]          VARCHAR (50) NULL,
    [LastName]           VARCHAR (50) NULL,
    [FK_CustomerType_ID] INT          NULL
);


GO
PRINT N'Creating Table [dbo].[CustomerType]...';


GO
CREATE TABLE [dbo].[CustomerType] (
    [CustomerTypeId]   INT           NULL,
    [CustomerType]     VARCHAR (50)  NULL,
    [ModificationDate] DATETIME2 (7) NULL,
    [IsDeleted]        BIT           NULL
);


GO
PRINT N'Creating View [dbo].[vGetCustomerType]...';


GO
CREATE VIEW [dbo].[vGetCustomerType] 
AS 
SELECT 
[CustomerTypeId] I ,
[CustomerType] 
FROM [dbo].[CustomerType] 
WHERE [IsDeleted] = 0;
GO
INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
    ,[CustomerType]
)
VALUES
     (0,'Unknown')
    ,(1,'Company')
    ,(2,'Person');
GO

GO
PRINT N'Update complete.';


GO

The Pre and Post scripts remained unchanged, and we can see that a new table has been added.

At this point, a red flag should go up for us. The issue is specifically about what will happen if someone adds data to the Customer table, and we then want to apply further changes. We’ll answer this question, once again, with an example. The above script can be run, and everything should complete without issues because, at this moment, the Customer table is empty.

Let’s add new data then directly to the Fabric Warehouse:

INSERT INTO dbo.Customer(Id, FirstName, LastName, FK_CustomerType_ID )
VALUES(1, N'Adrian', N'Chodkowski', 2 );

At this moment, the table on the target server contains data. Let’s try to make one more change to the project, namely, let’s modify the definition of the FirstName column in the dbo.Customer table from VARCHAR(50) to VARCHAR(40).

When we take a closer look at the differential script, we can notice the following entry:

IF EXISTS (select top 1 1 from [dbo].[Customer])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO
PRINT N'Altering [dbo].[Customer]...';
GO
ALTER TABLE [dbo].[Customer] ALTER COLUMN [FirstName] VARCHAR (40) NULL;
GO

So, if the table contains any data, an error will be returned, and the script will be interrupted. There are many solutions to this problem, one of which is to use, for example, a PreDeployment script and copy the data to a temporary table:

IF OBJECT_ID('dbo.CustomerCopy') IS NULL AND OBJECT_ID('dbo.Customer') IS NOT NULL
BEGIN

PRINT('Creating Customer copy table');

    SELECT 
        *
    INTO [dbo].[CustomerCopy]
    FROM
        [dbo].[Customer]

    IF EXISTS(SELECT TOP 1 1 FROM dbo.CustomerCopy)
    BEGIN
    
    PRINT('Truncate Customer table');
    
        TRUNCATE TABLE dbo.Customer;
    END
END

Then restoring data from a backup to the main table as part of PostDeployment:

IF OBJECT_ID('dbo.CustomerCopy') IS NOT NULL
BEGIN


    INSERT INTO dbo.Customer (Id,FirstName,LastName,FK_CustomerType_ID)
    SELECT Id,FirstName,LastName,FK_CustomerType_ID FROM dbo.CustomerCopy


    DROP TABLE dbo.CustomerCopy;
END

Of course, we can enhance scripts with transactions, TRY..CATCH, and similar structures to fully meet our requirements. Problems can be solved in a variety of ways, so everything depends on the approach and the specific situation. The only thing worth paying attention to is the fact that Pre and Post scripts always execute and should be designed with this characteristic in mind.

Within the scope of this article, I would also like to demonstrate that the scripts can reference code contained in other files. I encourage logically splitting operations into separate files, which will make the solution more transparent. To achieve this, I created folders that I named ReleaseA, where the Pre and Post scripts related to a specific release will be stored:

Next, I added scripts there , and I placed each logical operation in a separate file:

--RELEASE A:
:r .\ReleaseA\TurnOffCustomerCustomerTypeReferences.sql
:r .\ReleaseA\CreateCustomerCopyTable.sql
:r .\ReleaseA\TruncateCustomerTypeTable.sql

We obviously remember the order of individual calls. After generating the deployment script, we see that everything works as it should:

/*
Deployment script for dw01

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "dw01"
:setvar DefaultFilePrefix "dw01"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
    END


GO
IF OBJECT_ID('dbo.CustomerType') IS NOT NULL 
BEGIN IF EXISTS (SELECT TOP 1 * FROM dbo.CustomerType) 
    BEGIN 
        TRUNCATE TABLE dbo.CustomerType; 
    END 
END
GO

GO
/*
The type for column FirstName in table [dbo].[Customer] is currently  VARCHAR (50) NULL but is being changed to  VARCHAR (40) NULL. Data loss could occur and deployment may fail if the column contains data that is incompatible with type  VARCHAR (40) NULL.
*/

IF EXISTS (select top 1 1 from [dbo].[Customer])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

GO
PRINT N'Altering Table [dbo].[Customer]...';


GO
ALTER TABLE [dbo].[Customer] ALTER COLUMN [FirstName] VARCHAR (40) NULL;


GO
PRINT N'Creating Table [dbo].[CustomerType]...';


GO
CREATE TABLE [dbo].[CustomerType] (
    [CustomerTypeId]   INT           NULL,
    [CustomerType]     VARCHAR (50)  NULL,
    [ModificationDate] DATETIME2 (7) NULL,
    [IsDeleted]        BIT           NULL
);


GO
PRINT N'Creating View [dbo].[vGetCustomerType]...';


GO
CREATE VIEW [dbo].[vGetCustomerType] 
AS 
SELECT 
[CustomerTypeId] I ,
[CustomerType] 
FROM [dbo].[CustomerType] 
WHERE [IsDeleted] = 0;
GO
INSERT INTO dbo.CustomerType
(
     [CustomerTypeId]
    ,[CustomerType]
)
VALUES
     (0,'Unknown')
    ,(1,'Company')
    ,(2,'Person');
GO

GO
PRINT N'Update complete.';


GO

I also recommend adding comments in the form of Print statements, which makes it easy to track what’s happening during the execution of this script. The database lifecycle can be very complex and depends on many factors; however, in the approach described above, changes marked as ReleaseA, once they are on the production environment, can be removed/commented out in the main Pre/Post file, and new changes can be added. If these scripts are no longer needed, we can either delete them or archive them by creating a new folder with the current Release.

Everything integrated into a version control system can significantly speed up and simplify our development work. As I’ve emphasized many times, there are numerous approaches using a variety of techniques, and above I wanted to present the use of Pre and Post deployment scripts based on one of them. I hope I managed to achieve the goal I set for myself while writing this article.

Adrian Chodkowski
Follow me
Latest posts by Adrian Chodkowski (see all)

Leave a Reply