How do you find "code smells" in TimeXtender?

Spring is in full swing, which makes it a good time for some spring cleaning of your TimeXtender implementations. In this blog series, Senior TimeXtender Consultant Ruairidh Smith zooms in on typical things you can improve, especially in long-running implementations or implementations built by multiple developers. These are things that are usually seen as "details" that are less important than delivering new data. In this second edition, we go into a little less technical depth and look at functionality within TimeXtender that helps you find "code smells". Code smell is a software engineering term that refers to pieces of code that could benefit from improvement through refactoring. In data warehousing, the equivalent is looking for clues that point towards improvements in efficiency or understandability.

Use the tools you already have: Data Profile, Reports, Logging

While you build your implementation, TimeXtender handles data quality for you automatically. Most people will experience this through Primary Key checks: if your data source contains duplicate records for the key you have defined, TimeXtender will (under default settings) not allow those duplicates through to your valid table. Key collisions are set aside in error tables, where you can see which records are not coming through and for what reasons. Normally, you set up your keys and do not think about them again, but it is always good to check periodically whether your assumptions still hold. For example, if you add new data from another source to an existing dimension, duplicates may arise: you will not see them in your valid table, but they are written to the error tables.

Within TimeXtender, you can view all Errors and Warnings directly via the Reports menu. So after working on keys or Field Validations, it is a good idea to run a quick check to see whether any errors occur. If you have never looked at it before, it is well worth taking a look anyway: a lot of data may be getting caught!

As an example, the image below shows records that do not meet a Field Validation that only lets Product records through if they are finalized products. If I were to add a new source for product data and forgot to initialize that field correctly, none of those records would come through. The error and warning reports show the contents of the _L and _M tables and let you filter the different types of errors.

"Known" errors that are not actively resolved

In practice, I sometimes come across "known" errors that are not actively resolved. Although this can work fine, it is an implicit way of dealing with a problem that is easily overlooked or forgotten during a handover. Because errors and warnings generate records in the _L and _M tables, this can also create a performance bottleneck if there are many faulty records. For example, when adding a new mapping to a table produces more duplicates than expected.

One aspect to keep an eye on when you use the PK quality checks deliberately is that the first record with a given PK will go through to the valid table, but subsequent records will not. Without a custom script, you cannot influence the load order, so you may load the wrong records into your valid table if you are not aware of this.

An example of such a situation is an incremental load scenario where your deltas can contain multiple rows for the same primary or business key. In such situations, you may want to keep the last row in the set rather than the first, because the last record is the most recent. Since there is no direct control over the row order in the cleansing step between the raw and valid table, you will have to find another way to determine the correct records. You could consider influencing the row order going into the raw table and hope that your DWH engine does not decide to deliver records in a different order. This will work until it doesn't; changes in the SQL engine or perhaps migrating to a different type of engine such as Synapse or Snowflake can have side effects in this area. One way that guarantees consistency is to use an Aggregation table to pick the winner, or to use a custom SQL script.

Data Profile

The new release of TimeXtender gives you access to a handy new feature: the Data Profile. This gives you basic statistics on the contents of every column in your tables. Every time you execute your data, the statistics are updated. One of the most obvious applications is checking for NULLs without having to use the Query Tool in a Preview. For example, if you change your surrogate key setup, it is wise to take a quick look at the fields you are going to use for it.

Another handy option is being able to quickly see the minimum and maximum values for a date field: perhaps you do not expect to find dates in the future there, but they are there anyway because something changed in a business process, causing problems in your logic as a result.

As a final example, there is the option to query the distribution of values in a categorical field, as shown in the image below.

Logging

When you carry out a performance analysis, the Execution Log Overview is very useful: you gain insight into historical runtime, record counts and error counts. This lets you quickly understand why your table now takes longer to reload, or shows you that you forgot to turn Truncate Valid table before cleanse back on after turning off Incremental Load on a table.

In the image below, we see that the number of records in the valid table of the DIM_Product table in MDW has doubled in the last run compared to the previous runs. This happens because I turned off Simple Mode in the table settings, as I wanted to implement a Field Validation. If you turn off Simple Mode, you need to turn Truncate valid table before data cleansing back on, because it is off for Simple Mode. Without checking the logging, this issue can lie dormant until it causes a problem in a report.

One of the simplest things to check is how much time each part of a table reload takes. From this, you may conclude that a small table loads faster when it is fully loaded rather than incrementally.

In conclusion: TimeXtender contains plenty of options to check that your implementation behaves as expected, without dormant problems lurking. Having potential issues under control makes it much easier to use generic tools such as SQL Server Query Store to focus on your database performance.

Technical footnote: SQL Server usually delivers records in load order when no explicit ORDER BY is specified in a query. However, this order is not guaranteed if no ORDER BY is applied. For example, if you load data into SQL Server from a CSV file, SQL Server will so far read the records in file order (with a single-threaded load process). This is an effect of how SQL Server is implemented, but it is not part of a set-in-stone agreement. It works that way until it no longer does. As we move into an era where you are no longer always in detailed control of the underlying infrastructure, it becomes increasingly important to focus on what needs to happen and thereby avoid implicit assumptions about the how as much as possible. For example, if you migrate from SQL Server on a virtual machine to Snowflake, you may notice differences in behavior at the detail level.