Implementing a Dimensional Knowledge Warehouse with Databricks SQL: Half 2

As organizations consolidate analytics workloads to Databricks, they typically must adapt conventional information warehouse strategies. This collection explores tips on how to implement dimensional modeling—particularly, star schemas—on Databricks. The primary weblog centered on schema design. This weblog walks by ETL pipelines for dimension tables, together with Slowly Altering Dimensions (SCD) Kind-1 and Kind-2 patterns. The final weblog will present you tips on how to construct ETL pipelines for reality tables.

Slowly Altering Dimensions (SCD)

Within the final weblog, we outlined our star schema, together with a reality desk and its associated dimensions.  We highlighted one dimension desk specifically, DimCustomer, as proven right here (with some attributes eliminated to preserve area):

The final three fields on this desk, i.e., StartDate, EndDate and IsLateArriving, characterize metadata that assists us with versioning data.  As a given buyer’s revenue, marital standing, residence possession, variety of youngsters at residence, or different traits change, we are going to need to create new data for that buyer in order that details resembling our on-line gross sales transactions in FactInternetSales are related to the correct illustration of that buyer.  The pure (aka enterprise) key, CustomerAlternateKey, would be the similar throughout these data however the metadata will differ, permitting us to know the interval for which that model of the client was legitimate, as will the surrogate key, CustomerKey, permitting our details to hyperlink to the correct model.  

NOTE: As a result of the surrogate secret is generally used to hyperlink details and dimensions, dimension tables are sometimes clustered primarily based on this key. Not like conventional relational databases that make the most of b-tree indexes on sorted data, Databricks implements a singular clustering methodology often called liquid clustering. Whereas the specifics of liquid clustering are exterior the scope of this weblog, we constantly use the CLUSTER BY clause on the surrogate key of our dimension tables throughout their definition to leverage this characteristic successfully.

This sample of versioning dimension data as attributes change is called the Kind-2 Slowly Altering Dimension (or just Kind-2 SCD) sample. The Kind-2 SCD sample is most popular for recording dimension information within the basic dimensional methodology. Nonetheless, there are different methods to take care of modifications in dimension data.

One of the vital frequent methods to take care of altering dimension values is to replace current data in place.  Just one model of the document is ever created, in order that the enterprise key stays the distinctive identifier for the document.  For varied causes, not the least of that are efficiency and consistency, we nonetheless implement a surrogate key and hyperlink our reality data to those dimensions on these keys. Nonetheless, the StartDate and EndDate metadata fields that describe the time intervals over which a given dimension document is taken into account lively aren’t wanted. This is called the Kind-1 SCD sample.  The Promotion dimension in our star schema supplies an excellent instance of a Kind-1 dimension desk implementation:

However what in regards to the IsLateArriving metadata discipline seen within the Kind-2 Buyer dimension however lacking from the Kind-1 Promotion dimension? This discipline is used to flag data as late arriving.  A late arriving document is one for which the enterprise key exhibits up throughout a reality ETL cycle, however there isn’t a document for that key situated throughout prior dimension processing.  Within the case of the Kind-2 SCDs, this discipline is used to indicate that when the info for a late arriving document is first noticed in a dimension ETL cycle, the document needs to be up to date in place (identical to in a Kind-1 SCD sample) after which versioned from that time ahead.  Within the case of the Kind-1 SCDs, this discipline isn’t vital as a result of the document might be up to date in place regardless.

NOTE: The Kimball Group acknowledges further SCD patterns, most of that are variations and combos of the Kind-1 and Kind-2 patterns. As a result of the Kind-1 and Kind-2 SCDs are essentially the most continuously applied of those patterns and the strategies used with the others are intently associated to what’s employed with these, we’re limiting this weblog to simply these two dimension sorts. For extra details about the eight sorts of SCDs acknowledged by the Kimball Group, please see the Slowly Altering Dimension Methods part of this doc.

Implementing the Kind-1 SCD Sample

With information being up to date in place, the Kind-1 SCD workflow sample is essentially the most easy of the two-dimensional ETL patterns. To help a lot of these dimensions, we merely:

1. Extract the required information from our operational system(s)
2. Carry out any required information cleaning operations
3. Evaluate our incoming data to these already within the dimension desk
4. Replace any current data the place incoming attributes differ from what’s already recorded
5. Insert any incoming data that do not need a corresponding document within the dimension desk

As an instance a Kind-1 SCD implementation, we’ll outline the ETL for the continuing inhabitants of the DimPromotion desk.

Step 1: Extract information from an operational system

Our first step is to extract the info from our operational system.  As our information warehouse is patterned after the AdventureWorksDW pattern database supplied by Microsoft, we’re utilizing the intently related AdventureWorks (OLTP) pattern database as our supply. This database has been deployed to an Azure SQL Database occasion and made accessible inside our Databricks setting through a federated question.  Extraction is then facilitated with a easy question (with some fields redacted to preserve area), with the question outcomes persevered in a desk in our staging schema (that’s made accessible solely to the info engineers in our surroundings by permission settings not proven right here). That is however one in all some ways we are able to entry supply system information on this setting:

Step 2: Evaluate incoming data to these within the desk

Assuming we now have no further information cleaning steps to carry out (which we might implement with an UPDATE or one other CREATE TABLE AS assertion),  we are able to then sort out our dimension information replace/insert operations in a single step utilizing a MERGE assertion, matching our staged information and dimension information on the enterprise key:

One essential factor to notice in regards to the assertion, because it’s been written right here, is that we replace any current data when a match is discovered between the staged and printed dimension desk information. We might add further standards to the WHEN MATCHED clause to restrict updates to these situations when a document in staging has totally different info from what’s discovered within the dimension desk, however given the comparatively small variety of data on this explicit desk, we’ve elected to make use of the comparatively leaner logic proven right here.  (We are going to use the extra WHEN MATCHED logic with DimCustomer, which comprises much more information.)

The Kind-2 SCD sample

The Kind-2 SCD sample is a little more complicated. To help a lot of these dimensions, we should:

1. Extract the required information from our operational system(s)
2. Carry out any required information cleaning operations
3. Replace any late-arriving member data within the goal desk
4. Expire any current data within the goal desk for which new variations are present in staging
5. Insert any new (or new variations) of data into the goal desk

Step 1: Extract and cleanse information from a supply system

As within the Kind-1 SCD sample, our first steps are to extract and cleanse information from the supply system.  Utilizing the identical method as above, we concern a federated question and persist the extracted information to a desk in our staging schema:

Step 2: Evaluate to a dimension desk

With this information landed, we are able to now examine it to our dimension desk with a view to make any required information modifications.  The primary of those is to replace in place any data flagged as late arriving from prior reality desk ETL processes.  Please be aware that these updates are restricted to these data flagged as late arriving and the IsLateArriving flag is being reset with the replace in order that these data behave as regular Kind-2 SCDs shifting ahead:

Step 3: Expire versioned data

The subsequent set of information modifications is to run out any data that must be versioned.  It’s essential that the EndDate worth we set for these matches the StartDate of the brand new document variations we are going to implement within the subsequent step.  For that purpose, we are going to set a timestamp variable for use between these two steps:

NOTE: Relying on the info obtainable to you, it’s possible you’ll elect to make use of an EndDate worth originating from the supply system, at which level you wouldn’t essentially declare a variable as proven right here.

Please be aware the extra standards used within the WHEN MATCHED clause.  As a result of we’re solely performing one operation with this assertion, it will be doable to maneuver this logic to the ON clause, however we stored it separated from the core matching logic, the place we’re matching to the present model of the dimension document for readability and maintainability.

As a part of this logic, we’re making heavy use of the equal_null() operate.  This operate returns TRUE when the primary and second values are the identical or each NULL; in any other case, it returns FALSE.  This supplies an environment friendly option to search for modifications on a column-by-column foundation.  For extra particulars on how Databricks helps NULL semantics, please consult with this doc.

At this stage, any prior variations of data within the dimension desk which have expired have been end-dated.  

Step 4: Insert new data

We will now insert new data, each really new and newly versioned:

As earlier than, this might have been applied utilizing an INSERT assertion, however the outcome is similar.  With this assertion, we now have recognized any data within the staging desk that don’t have an unexpired corresponding document within the dimension tables. These data are merely inserted with a StartDate worth in line with any expired data which will exist on this desk.

Subsequent steps: implementing the very fact desk ETL

With the scale applied and populated with information, we are able to now give attention to the very fact tables. Within the subsequent weblog, we are going to exhibit how the ETL for these tables will be applied.

To study extra about Databricks SQL, go to our web site or learn the documentation. You can even try the product tour for Databricks SQL. Suppose you need to migrate your current warehouse to a high-performance, serverless information warehouse with an awesome person expertise and decrease whole value. In that case, Databricks SQL is the answer — attempt it free of charge.