bicortex

Speeding up SQL Server Data Warehouse Architecture With Automation Procedures – 10 Problem-Solution Scenarios To Jump-Start Your Development

Note: all code used in this post can be viewed and downloaded from my publicly accessible OneDrive folder HERE.

Most skilled BI/DW professionals, when planning and scoping to deliver a new data warehouse will choose to work with some sort of framework to automate and simplify certain common tasks e.g. data acquisition, index creation, schema rebuilt or synchronization etc. Over the years, I have design and built a large number of data marts and enterprise data warehouses using both industry standard as well as bespoke design patterns. It is fair to say that it often pays not to reinvent the wheel and reuse (where possible) some of the staple elements and code bases which may be applicable to those development activities. After all, time is money, and the quicker I can focus on delivering value through providing well-structured and rich data, the happier my clients will be. With that in mind, I thought it would be a good idea to share some of the code which handles many of the tedious but necessary tasks so that one can operationalize those with relative speed to start addressing a business or problem-specific architecture. In my experience, even most technology-savvy clients are not interested in the intricate details of how data is piped across the various domains and constructs. In the end, for most stakeholders, even the most elaborate and elegant solutions are just a part of a ‘shoe-box’ referred to as the data warehouse and until data can be realized and utilized through a report or a piece of analytics, code is just a means to an end – the quicker we can build it and make it work, the better.

The following tidbits of SQL provide a small collection of ‘utilities’ one can implement with zero to little modifications to automate activities such as index creation/dropping, foreign key creation/dropping, automated data acquisition from source databases, reconciling schema changes etc. All this code was developed using SQL Server platform as large chunk of my engagements still heavily rely on a virtual environment, on-premise or hybrid Microsoft SQL Server deployments. Also, rather than simply delivering the source code or a Github link, I tried to provide a common scenario or a problem statement, along with a short explanation of what it does and how it solves the after mentioned issue. In this structure, each problem statement roughly follows a sequence of tasks a typical architect or developer may need to address in the process of building a data acquisition framework i.e. check if source data/environment is accessible, build-up metadata for source and target environments, manage schema changes based on metadata, acquire data etc. This is not an exhaustive list of all activities and many of those may be redundant or not applicable to your specific scenario, but if you’re grappling with implementing a proof of concept or maybe a small scale project and look for a more defined hands-on springboard to take your architecture from idea to a set of tangible artifacts to jump-start development process, this may be of great help.

Also, it is worth pointing out that these scripts work well with POCs and prototypes as well as small projects, where speed and agility is paramount. Most enterprise data warehouse developments are not like that and require a fair amount of upfront planning and preparation (even when done in an agile manner). If your project is complex in nature and, for example, requires large systems integration, complex ETL/ELT functionality, changing APIs, non-relation/non-file-based data stores or comes with bespoke requirements, you may want to look at either buying an off-the-shelf framework or customize one which has already been developed. Building a new framework from scratch is probably a bad idea and since most DW core concepts have been left unchanged for many years, chances are it’s better to ‘stand on the shoulders of giants’ rather than trying to reinvent the wheel.

Problem 1

Check if Linked Server connection is active before data acquisition job can be initiated. If not, retry predefined number of times, waiting for a preset amount of time and if still not resolving notify the operator/administrator.

 

On a SQL Server platform, most database connections across number of different vendors can be configured using the good, old-fashion Linked Server functionality – in my experience it worked well on MSSQL, MySQL/MariaDB, PostgreSQL, Vertica, Oracle, Teradata and even Sqlite. Before data warehouse can be loaded with data from a source system (usually an externally hosted database), it’s worth checking if the connection we’ve created is active and resolving without issues. If not, we may want to wait for a few minutes (instead of failing the load completely) and if the problem persists, notify administrator to start the troubleshooting process. These issues are quite unlikely to appear and, in my personal experience, are mostly related to network failures and not source system availability problems but when they do surface, DW administrators are often left in the dark. The code for this small stored procedure can be downloaded from HERE.

Problem 2

Provide a selective list of objects, attributes and data and store this as metadata to be referenced at later stage e.g. schema rebuilt, data acquisition, indexes creation etc.

 

Often times, whether it’s because of security reasons (data viewed as a liability) or simply as a storage space-saving mechanism (largely uncommon these days), clients want to be able to selectively nominate a list of objects from a database, along with some of their attributes and data to be acquired. They may not want all tables and tables’ columns to be replicated in the staging area of the data warehouse. Furthermore, they may want to go down to the level of individual values from a given attribute. For example:

• Out of 200 tables in the source system, they are only interested in 150
• Since some of the columns are used for logging and metadata storage only, the want those excluded as they don’t provide any valuable information
• Finally, some columns may contain a mixture of data with various levels of sensitivity e.g. staff and clients’ e-mail addressed. As a result, they want staff e-mail address values but client e-mail addresses should be masked or obfuscated for security and/or privacy reasons

There are a few different ways to address this issue, but the simplest solution would be to create three views containing some of the metadata mentioned above i.e. table names, column names etc. and load those into a small, three-table database which can be used as a reference point for individual objects, attributes and data exclusion/inclusion. Let’s see how that would work in practice.

After creating the target environment as well as sample linked server to an Azure SQL DB to further demonstrate this in practice (again, all T-SQL downloadable from HERE) I typically create three views which hold source system metadata. The content of those is loaded into a small three-table database which can be referenced in a number of ways. The reason I create intermediary views as opposed to only storing this data in tables is because it’s a lot easier to manage metadata in this format e.g. version control it, make changes and alternations etc. Once the views are created we can use a simple stored procedure to (re)build table schema, provide the initial data load and update data based on business requirements and source systems changes.

These three tables will become very helpful in providing the reference data for any other subsequent activities. For example, if we’d like to selectively specify object names for data acquisition tasks or run post-acquisition data reconciliation job, it’s a lot easier to have this information available on the local instance.

Problem 3

Dynamically manage schema changes in target database.

Changes to the source schema are usually communicated downstream, before going into production, so that data engineers can unit-test and validate them for impact on the data warehouse and ETL logic. However, in my experience, that’s often a pie-in-the-sky scenario and for teams/divisions with weak change control culture a highly likely event. Cloud technologies have offered tools to easily reconcile both: schema and data across various data sources, but in case I need to roll my own, I often rely on a script which can do it for me. The code (downloadable from HERE) stages source and target metadata and compares it for attributes such as data types, object and column names, numeric precision, numeric scale etc. If any changes are detected, it tries to replicate them on the target database, after which the comparison is run again to verify the fix.

I have used this code extensively for schema reconciliation between number of different vendor database e.g. MSSQL, MariaDB, PostgreSQL with only small modifications to account for idiosyncrasies between how data can be represented across those (SQL Server being the target/sink database). In ideal world schema changes should always be treated as a potential risk to downstream processing, with a proper chain of custody in place to account for potential issues. Howerer, in many cases, it’s either not possible or the approach taken is ‘too agile’ to ensure precautions are taken before those are communicated and deployed so if ensuring schemas are in sync is required, this small step (executed before data acquisition kick-off) can be of great benefit.

Problem 4

Acquire data from source system based on metadata input and changes.

 

Sometimes, the most difficult issue when dealing with new data mart development is source data acquisition. Once data has been imported into the target zone, it usually quite easy to mold, transform and apply business rules to. However, due to factors such as source-to- target schema changes, history and CDC tracking, delta values reconciliation etc., acquiring source data can sometimes turn into a challenging problem to solve. Most organizations turn to purchasing a COTS (Commercial, off-the-shelf) framework or developing one themselves if the business requirements are too bespoke to warrant going the commercial route. However, for quick projects with small amount of data, or even as a test/POC solution, we can just as easily create a Linked Server connection to the source database (if available) and build a simple acquisition pipeline to speed up and automate the whole process.

This code, run on schedule or in on-demand fashion, relies on the control (meta)data created as part of Problem 2 outline, acquiring source data in parallel, spooling multiple SQL Server Agent jobs. It is comprised of two separate stored procedures: (1) Parent module responsible for the metadata management and spawning SQL Server Agent Jobs and (2) Worker module which does the heavy-lifting i.e. inserts data into the target object based on metadata information passed down from its parent.

The number of jobs can be determined by the count of vCPUs/CPU cores available on the host machine and can even be further partitioned to ‘break-up’ individual objects using additional logic e.g. in case of very large tables, those can be broken up into smaller ‘chunks’ of data (using numeric value primary key filed) and SELECTED/INSERTED in parallel. Below is a sample logic to split a table’s data into 10 evenly-distributed blocks in T-SQL and Python.

--Create dynamic SQL
DECLARE @SQL NVARCHAR(MAX)
DECLARE @PK_Col_Name VARCHAR (100) = 'id'
DECLARE @Target_DB_Object_Name VARCHAR(1000) = 'Target_Object'
DECLARE @Proc_Exec_No INT = 10
DECLARE @Remote_Server_Name VARCHAR (100) = 'Source_Server_Name'

SET @SQL =				'DECLARE @R1 INT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '	+CHAR(13)
SET @SQL = @SQL +		'''SELECT MIN('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''')) '	+CHAR(13) 
SET @SQL = @SQL +		'DECLARE @R2 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '	+CHAR(13)
SET @SQL = @SQL +		'''SELECT (MAX('+@PK_Col_Name+')-MIN('+@PK_Col_Name+')+1)'					+CHAR(13)
SET @SQL = @SQL +		'/'+CAST(@Proc_Exec_No AS VARCHAR(10))+' as id FROM'						+CHAR(13)
SET @SQL = @SQL +		''+@Target_DB_Object_Name+'''))'											+CHAR(13)
SET @SQL = @SQL +		'DECLARE @R3 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '	+CHAR(13)
SET @SQL = @SQL +		'''SELECT MAX('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''')) '	+CHAR(13) 
SET @SQL = @SQL +		'INSERT INTO #Ids_Range'													+CHAR(13)
SET @SQL = @SQL +		'(range_FROM, range_to)'													+CHAR(13)
SET @SQL = @SQL +		'SELECT @R1, @R1+@R2'														+CHAR(13)
SET @SQL = @SQL +		'DECLARE @z INT = 1'														+CHAR(13)
SET @SQL = @SQL +		'WHILE @z <= '+CAST(@Proc_Exec_No AS VARCHAR(10))+'-1'					   +CHAR(13)
SET @SQL = @SQL +		'BEGIN'																		+CHAR(13)
SET @SQL = @SQL +		'INSERT INTO #Ids_Range (range_FROM, range_TO) '							+CHAR(13)
SET @SQL = @SQL +		'SELECT LAG(range_TO,0) OVER (ORDER BY id DESC)+1, '						+CHAR(13)
SET @SQL = @SQL +		'CASE WHEN LAG(range_TO,0) OVER (ORDER BY id DESC)+@R2+1 >= @R3'			   +CHAR(13)
SET @SQL = @SQL +		'THEN @R3 ELSE LAG(range_TO,0) OVER (ORDER BY id DESC)+@R2+1 END'			+CHAR(13)
SET @SQL = @SQL +		'FROM tempdb..#Ids_Range WHERE @z = id'										+CHAR(13)
SET @SQL = @SQL +		'SET @z = @z+1'																+CHAR(13)
SET @SQL = @SQL +		'END'	
PRINT (@SQL)

--Take printed dynamic SQL and generate ranges (stored in a temporary table)
IF OBJECT_ID('tempdb..#Ids_Range') IS NOT NULL
BEGIN
    DROP TABLE #Ids_Range;
END;
CREATE TABLE #Ids_Range
(
    id SMALLINT IDENTITY(1, 1),
    range_FROM BIGINT,
    range_TO BIGINT
);

DECLARE @R1 INT =
        (
            SELECT id
            FROM OPENQUERY
                 (Source_Server_Name, 'SELECT MIN(id) as id from Target_Object')
        );
DECLARE @R2 BIGINT =
        (
            SELECT id
            FROM OPENQUERY
                 (Source_Server_Name, 'SELECT (MAX(id)-MIN(id)+1)
/10 as id FROM
Target_Object')
        );
DECLARE @R3 BIGINT =
        (
            SELECT id
            FROM OPENQUERY
                 (Source_Server_Name, 'SELECT MAX(id) as id from Target_Object')
        );
INSERT INTO #Ids_Range
(
    range_FROM,
    range_TO
)
SELECT @R1,
       @R1 + @R2;
DECLARE @z INT = 1;
WHILE @z <= 10 - 1
BEGIN
    INSERT INTO #Ids_Range
    (
        range_FROM,
        range_TO
    )
    SELECT LAG(range_TO, 0) OVER (ORDER BY id DESC) + 1,
           CASE
               WHEN LAG(range_TO, 0) OVER (ORDER BY id DESC) + @R2 + 1 >= @R3 THEN
                   @R3
               ELSE
                   LAG(range_TO, 0) OVER (ORDER BY id DESC) + @R2 + 1
           END
    FROM tempdb..#Ids_Range
    WHERE @z = id;
    SET @z = @z + 1;
END;

SELECT * FROM #Ids_Range

def split_into_ranges(start, end, parts):
    ranges = []
    x = round((end - start) / parts)
    for _ in range(parts):
        ranges.append([start, start + x])
        start = start + x + 1
        if end - start <= x:
            remainder = end - ranges[-1][-1]
            ranges.append([ranges[-1][-1] + 1, ranges[-1][-1] + remainder])
            break
    return ranges

Problem 5

Capture stored procedure/SSIS package execution errors and exceptions information for analysis.

 

SQL Server comes with a built-in mechanism to capture various levels of information and metadata on SSIS package execution. However, since my preference has mostly been to use SSIS as an orchestration engine, executing finely tuned and optimized SQL code, and not relying on ‘black box’ SSIS transformations, I needed to devise a way to capture and persist execution errors and related data in a central place which can be reported out of. Since I have already written about this at length in my other two blog posts, mainly HERE and HERE, for brevity, I won’t be repeating this information again. All the code (two stored procedures – one for creating and one for updating database objects) can be found HERE. The schema created by the script is as per below and the way to reference and integrate other code against it can be found in my two previous posts: PART 1 and PART 2.

Once populated with target instance metadata, captured errors can be visualized and analyzed. The following image depicts a simple dashboard outlining various error metrics and their associated attributes.

Problem 6

Persist and historize data changes across all objects and attributes.

 

Old-school approach to ensuring that source data changes e.g. deletion, updates etc. are captured and stored in the data warehouse rely heavily on the concept on Slowly Changing Dimension (SCD). Depending on which SCD type is used (more on this topic can be read in the Wikipedia article HERE), data changes do not overwrite the initial value(s) but create a separate record or column to persist any modifications on the nominated set of attributes. This operation is typically reserved for dimensions as facts are transactional in nature thus not subjected to changes. The problem with this approach is that data is required to be shaped and molded in a very specific way, sometimes loosing some of its ‘raw’ value along the way in favor of adhering to a specific modelling methodology. Sometimes it would be great to re-delegate history persistence functionality to the ‘area’ responsible for staging source system data, across all objects and fields. I have already written a post on building relational data lake (link HERE) so reiterating this content is out of scope but it’s fair to say that with ever-changing landscape for how data is captured and stored, allowing for the maximum flexibility in how it can be modeled should be paramount.

There are many patterns which can facilitate implementing relational data lake but this simple design allows for a clever way of persisting all application data changes (one the core requirements of any data warehouse, irrespective of modelling approach taken) along with providing robust foundations for provisioning virtual data marts to satisfy reporting and analytical data needs. The virtual data marts can be easily implemented as views, with the required business rules embedded inside the SQL statements and, if required for performance reasons, materialized or physically persisted on disk or in memory.

This framework relies on creating a custom, performance-optimized data flow logic along with four databases which together handle transient application data (as acquired from the source system), staged data and finally data containing all transitional changes (inserts, deletes and updates) across all history. Again, repeating my previous post outlining all the details is out of scope here so for a comprehensive overview of how this method works for history persistence and performantly handling data changes across large systems please refer to the previous post HERE.

Problem 7

Use metadata-driven approach for indexes creation and dropping.

 

If a large number of indexes is required to be created on the nominated objects (or dropped prior to data load execution to speed up the process), it is nice to have a way to store it as a metadata, which in turn can be referenced in an automated fashion. Again, this type of requirement can be addressed in a number of ways but probably the simplest solution would be to have a piece of code which can do it for us on demand, relying on metadata stored in dedicated objects (as per Problem 2), where number of values can be passed as parameters to target specific use case e.g. drop/create indexes using same codebase, do it across the whole schema or just individual objects etc.

The code (downloadable from HERE) can be used as part of the ETL logic to either DROP or CREATE indexes on one or more tables in the nominated database and schema. It does not cater for indexes rebuilt or reorganization (I may add this functionality to a later version) but it simplifies indexes management and can be used for automation.

Problem 8

Use metadata-driven approach for foreign keys constraints creation and dropping.

 

As it is the case with Problem 6, providing metadata-driven approach to any repeatably-executed operation may be a good investment in time taken to initially develop the code. This SQL can be taken and plugged into a new or existing ETL process if referential integrity across facts and dimensions is required. It relies on the ‘control’ database table entries which makes it easy to reference without having to hard-code individual SQL statements and can be used for both: dropping and creating foreign key constraints. The code is published HERE.

Problem 9

Check data acquisition status and validate record count.

 

Finally, if we need to ensure that no errors were raised during data acquisition (logged in AdminDBA table as per Problem 5 description) and check if all records were copied successfully (this only relies on record count and does not take changes/alterations into consideration so use with caution) we can use the solution I outlined previously HERE and a small piece of SQL for record count comparison. The SQL part is very straightforward (this implementation relies on MSSQL to MSSQL comparison) and relies on a static threshold value of 0.001% permissible variance between source and target across all tables. This is so that when sourcing data from a ‘live’ system, where data is being subjected to standard CRUD operations, small differences i.e. smaller than 0.001% of all record count do not raise exceptions or errors. There’s also an option to get source record counts from two different places: the ‘Control’ database created earlier as per Problem 2 outline or the actual source system. The C# code on the other hand is an SSIS package implementation and relies on the output from both: msdb database and error logging database (AdminDBA) for acquisition job execution status. The stored procedure can be downloaded from HERE whereas a full description of the SSIS code can be viewed HERE.

Problem 10

Notify administrator(s) on execution failure

 

As with a lot of solutions in this space, there is nearly an infinite amount of ways one could create a process of notifications and alerts. For larger projects this is usually the ETL framework’s core functionality and most well-respected and popular tools, cloud or on-premise, do it to some extent. For small projects e.g. where a small data mart is populated using SSIS or SQL, I usually plug-in a small stored procedure which wraps some extra functionality around the SQL Server’s native msdb.dbo.sp_send_dbmail process. Providing the Database Mail has been enabled on the target instance (beyond the content of this post), a small piece of code can send out an e-mail message containing key pieces of data on what exactly failed, along with some peripheral information helping administrator(s) identify and troubleshoot a problem. Most developers these days also like Slack or other IM applications integration but for a small project with infrequent level of alerting, email notification is still the best way to raise potential issues.

Below is a sample email sent out by the ETL pipeline containing a small snippet of HTML code (click on image to enlarge). It provides a simple template for outlining all necessary pieces of data required to identify the problem e.g. affected object name, process name, executed package name, date and tie of the event as well as a link to the report which one can access to gain the ‘big picture’ view from the AdminDBA database as per what’s outlined in Problem 5.

The stored procedure execution can be triggered by the output parameter of another task (a simple IF…THEN logic) and relies on a number of parameters typically passed as values from a SSIS package. It’s execution is also preceded by running a function responsible for acquiring individual e-mail addresses of individuals required to be notified (these are stored in the AdminDBA database as per Problem 5 description). In this way multiple administrators can be notified of an issue. All other parameters’ values are derived or come from the SSIS package itself. And again, all this code (including the aforementioned function) can be downloaded from my OneDrive folder HERE.

There you go – a simple list of ten potential problems and corresponding solutions one may face and need to resolve when provisioning a data mart or data warehouse on a SQL Server environment.

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: SQL, SQL Server, SSIS

Tags: Data Warehouse, Microsoft, SQL, SQL Server

This entry was posted on Thursday, February 7th, 2019 at 4:35 pm and is filed under SQL, SQL Server, SSIS. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.