Best practices for transferring data across long, wide tables

Question

As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns.

To reduce the overhead, I'm considering data tracking on these tables so we only look at changed values rather than scanning the complete table. Unfortunately our source system runs on SQL Server 2014 Standard Edition and CDC is an option we can not take advantage of.

Is there an alternative method that would be better than change tracking?

Answer 1

Have you considered adding a ''rowversion'' column to your source tables?

The simplest way to think about rowversion (formerly timestamp in SQL Server) is as a database-wide autonumber. Every INSERT and UPDATE increments the database-wide rowversion. Any table that has a rowversion column defined has that column's value set to the database rowversion when the associated row is INSERTed or UPDATEd.

Here's a brief algorithm for how you could use this:

• Save the database-wide rowversion value (available via @@DBTS; i.e., DataBase TimeStamp)
• Process your data into your Data Warehouse
• Next time through, only process rows where the rowversion value is greater than the previously saved @@DBTS

Note that while you should not miss any rows that should be processed, you may re-process rows with no actual change in data. This is because the rowversion is incremented with every UPDATE statement, even if the data is "UPDATEd" to its current value and not actually changed.

Also, it won't help identify which columns might have been changed. As the name implies, it works strictly at the row level.

Answer 2

To me, there are two potential ways for the standard SQL Server edition

1. Create triggers to track changes (you can test your workload and see whether triggers impact your performance)

or

2. Assuming you do not need to transfer OLTP tables to your DW tables in near real time, you can create a snapshot every hour, and then have a CLR stored proc to calculate the each row hash value of tables of interest in both snapshot database and the current database, and use primary key to quickly find out the changed rows and then insert/update/delete the corresponding OLAP tables.

Answer 3

You could add a "High Water Mark" column on your source table in order to do an incremental change. This column should store the datetime of your last insert/update.

i.e: You can create a column called DtLastUpdate default GETDATE() and a trigger after update to guarantee that this value receives the GETDATE() value after each update.

When doing your incremental load, you can use this column as a filter:

where DtLastUpdate > @dtIncremental

In this example, @dtIncremental represents the date where your last successful load started - delta (could be from 1 min to 1 day, depending on the volume and precision of your datetime fields)

Of course that this trigger could cause an overhead, but it is usually small.

Advantages:

• Change tracking usually retains a small amount of time. If by any error, you need to reload a bigger period, you have to do a full load with Change Tracking

• Once that the same logic applies for Full or Incremental loads, your ETL is simpler (you have to change only the @dtIncremental parameter)

Disadvantages:

• Have to change the source schema (not always viable)

• Does not track deletes - But a similar approach could do it

Well, but is it better? -> I let the conclusion with you. Particularly I use Change Tracking if the source system doesn't have a "High Water Mark" column and if I can't change its schema.

Answer 4

Generally speaking, I find that it is infinitely preferable to go with the solution proposed by your database system vendor. The situation with respect to implementing RI (Referential Integrity) on the client side springs to mind - and is a recipe for unmitigated disaster - read my answer to a post here about that particular can of worms!

You might might want to look at "Debezium" (a CDC project supported by RedHat, see here and here). Unfortunately, the SQL Server connector is only in alpha, but the last commit to the overall project was only 7 days ago, so it's active - you could always ask.

Having said that, Debezium is supported by RedHat which is always a good sign, however it involves setting up Apache Kafka - a complex system in itself.

If I were you, I would give careful thought to a cost/benefit analysis of going to the trouble (man-hours, potential for bugs even if the SQL Server connector gets out of alpha) of setting up a roll-your-own solution (OK, it's RedHat, but still...) or using an out-of-the-box solution from Microsoft themselves.

An interesting read (about LinkedIn's MySQL solution) here and here The last commit was slightly over a year ago, so I don't know how active the project is and I'm pretty sure that they won't be doing SQL Server anytime soon!