Implementing a Dimensional Information Warehouse with Databricks SQL, Half 3

Dimensional modeling is a time-tested method to constructing analytics-ready information warehouses. Whereas many organizations are shifting to trendy platforms like Databricks, these foundational methods nonetheless apply.

In Half 1, we designed our dimensional schema. In Half 2, we constructed ETL pipelines for dimension tables. Now in Half 3, we implement the ETL logic for truth tables, emphasizing effectivity and integrity.

Reality tables and delta extracts

In the primary weblog, we outlined the actual fact desk, FactInternetSales, as proven beneath.  In comparison with our dimension tables, the actual fact desk is comparatively slender when it comes to report size, with solely international key references to our dimension tables, our truth measures, our degenerate dimension fields and a single metadata discipline current:

NOTE: Within the instance beneath, we’ve altered the CREATE TABLE assertion from our first put up to incorporate the international key definitions as a substitute of defining these in separate ALTER TABLE statements. We’ve additionally included a main key constraint on the degenerate dimension fields to be extra express about their position extra express on this truth desk.

The desk definition is pretty easy, nevertheless it’s value taking a second to debate the LastModifiedDateTime metadata discipline. Whereas truth tables are comparatively slender when it comes to discipline depend, they are usually very deep when it comes to row depend. Reality tables usually home thousands and thousands, if not billions, of data, usually derived from high-volume operational actions. As a substitute of trying to reload the desk with a full extract on every ETL cycle, we’ll sometimes restrict our efforts to new data and people which have been modified.

Relying on the supply system and its underlying infrastructure, there are a lot of methods to determine which operational data have to be extracted with a given ETL cycle. Change information seize (CDC) capabilities carried out on the operational facet are probably the most dependable mechanisms.  However when these are unavailable, we frequently fall again to timestamps recorded with every transaction report as it’s created and modified.  The method shouldn’t be bulletproof for change detection, however as any skilled ETL developer will attest, it’s usually one of the best we’ve obtained.

NOTE: The introduction of Lakeflow Join supplies an fascinating choice for performing change information seize on relational databases.  This functionality is in preview on the time of the writing of this weblog. Nonetheless, as the aptitude matures to develop an increasing number of RDBMSs, we anticipate this to supply an efficient and environment friendly mechanism for incremental extracts.

In our truth desk, the LastModifiedDateTime discipline captures such a timestamp worth recorded within the operational system.  Earlier than extracting information from our operational system, we’ll overview the actual fact desk to determine the newest worth for this discipline we’ve recorded.  That worth would be the place to begin for our incremental (aka delta) extract.

The Reality ETL workflow

The high-level workflow for our truth ETL will proceed as follows:

1. Retrieve the newest LastModifiedDateTime worth from our truth desk.
2. Extract related transactional information from the supply system with timestamps on or after the newest LastModifiedDateTime worth.
3. Carry out any further information cleaning steps required on the extracted information.
4. Publish any late-arriving member values to the related dimensions.
5. Lookup international key values from related dimensions.
6. Publish information to the actual fact desk.

To make this workflow simpler to digest, we’ll describe its key phases within the following sections. Not like the put up on dimension ETL, we’ll implement our logic for this workflow utilizing a mixture of SQL and Python primarily based on which language makes every step most easy to implement.  Once more, one of many strengths of the Databricks Platform is its assist for a number of languages. As a substitute of presenting it as an all-or-nothing selection made on the high of an implementation, we’ll present how information engineers can rapidly pivot between the 2 inside a single implementation.

Steps 1-3: Delta extract section

Our workflow’s first two steps deal with extracting new and newly up to date data from our operational system.  In step one, we do a easy lookup of the newest recorded worth for LastModifiedDateTime.  If the actual fact desk is empty, appropriately upon initialization, we outline a default worth that’s far sufficient again in time that we consider it is going to seize all of the related information within the supply system:

We will now extract the required information from our operational system utilizing that worth.  Whereas this question contains fairly a little bit of element, focus your consideration on the WHERE clause, the place we make use of the final noticed timestamp worth from the earlier step to retrieve the person line gadgets which are new or modified (or related to gross sales orders which are new or modified):

As earlier than, the extracted information is endured to a desk in our staging schema, solely accessible to our information engineers, earlier than continuing to subsequent steps within the workflow. If we’ve got any further information cleaning to carry out, we should always accomplish that now.

Step 4: Late arriving members section

The standard sequence in an information warehouse ETL cycle is working our dimension ETL workflows after which our truth workflows shortly after.  By organizing our processes this manner, we are able to higher guarantee all the knowledge required to attach our truth data to dimension information will probably be in place.  Nonetheless, there’s a slender window inside which new, dimension-oriented information arrives and is picked up by a fact-relevant transactional report.  That window will increase ought to we’ve got a failure within the general ETL cycle that delays truth information extraction.  And, after all, there can at all times be referential failures in supply programs that enable questionable information to look in a transactional report.

To insulate ourselves from this drawback, we’ll insert right into a given dimension desk any enterprise key values present in our staged truth information however not within the set of present (unexpired) data for that dimension.  This method will create a report with a enterprise (pure) key and a surrogate key that our truth desk can reference.  These data will probably be flagged as late arriving if the focused dimension is a Sort-2 SCD in order that we are able to replace appropriately on the following ETL cycle.

To get us began, we’ll compile a listing of key enterprise fields in our staging information.  Right here, we’re exploiting strict naming conventions that enable us to determine these fields dynamically:

NOTE: We’re switching to Python for the next code examples.  Databricks helps the usage of a number of languages, even throughout the similar workflow.  On this instance, Python offers us a bit extra flexibility whereas nonetheless aligning with SQL ideas, making this method accessible to extra conventional SQL builders. 

Discover that we’ve got separated our date keys from the opposite enterprise keys.  We’ll return to these in a bit, however for now, let’s deal with the non-date (different) keys on this desk.

For every non-date enterprise key, we are able to use our discipline and desk naming conventions to determine the dimension desk that ought to maintain that key after which carry out a left-semi be a part of (much like a NOT IN() comparability however supporting multi-column matching if wanted) to determine any values for that column within the staging desk however not within the dimension desk.  After we discover an unmatched worth, we merely insert it into the dimension desk with the suitable setting for the IsLateArriving discipline: 

This logic would work advantageous for our date dimension references if we needed to make sure our truth data linked to legitimate entries.  Nonetheless, many downstream BI programs implement logic that requires the date dimension to deal with a steady, uninterrupted sequence of dates between the earliest and newest values recorded.  Ought to we encounter a date earlier than or after the vary of values within the desk, we want not simply to enter the lacking member however create the extra values required to protect an unbroken vary.  For that cause, we want barely totally different logic for any late arrival dates:

When you have not labored a lot with Databricks or Spark SQL, the question on the coronary heart of this final step is probably going international.  The sequence() operate builds a sequence of values primarily based on a specified begin and cease.  The result’s an array that we are able to then explode (utilizing the explode() operate) so that every ingredient within the array varieties a row in a outcome set.  From there, we merely examine the required vary to what’s within the dimension desk to determine which components have to be inserted. With that insertion, we guarantee we’ve got a surrogate key worth carried out on this dimension as a sensible key in order that our truth data could have one thing to reference. 

Steps 5 – 6: Information publication section

Now that we might be assured that each one enterprise keys in our staging desk might be matched to data of their corresponding dimensions, we are able to proceed with the publication to the actual fact desk.

Step one on this course of is to search for the international key values for these enterprise keys.  This may be accomplished as a part of a single publication step, however the massive variety of joins within the question usually makes this method difficult to keep up. For that reason, we would take the much less environment friendly however easier-to-comprehend and modify the method of trying up international key values one enterprise key at a time and appending these values to our staging desk:

Once more, we’re exploiting naming conventions to make this logic extra easy to implement.  As a result of our date dimension is a role-playing dimension and subsequently follows a extra variable naming conference, we implement barely totally different logic for these enterprise keys.

At this level, our staging desk homes enterprise keys and surrogate key values together with our measures, degenerate dimension fields, and the LastModifiedDate worth extracted from our supply system. To make publication extra manageable, we should always align the obtainable fields with these supported by the actual fact desk.  To try this, we have to drop the enterprise keys:

NOTE: The supply dataframe is outlined within the earlier code block.

With the fields aligned, the publication step is easy. We match our incoming data to these within the truth desk primarily based on the degenerate dimension fields, which function a singular identifier for our truth data, after which replace or insert values as wanted:

Subsequent steps

We hope this weblog sequence has been informative to these searching for to construct dimensional fashions on the Databricks Platform.  We anticipate that many skilled with this information modeling method and the ETL workflows related to it is going to discover Databricks acquainted, accessible and able to supporting long-established patterns with minimal adjustments in comparison with what might have been carried out on RDBMS platforms. The place adjustments emerge, akin to the power to implement workflow logic utilizing a mixture of Python and SQL, we hope that information engineers will discover this makes their work extra easy to implement and assist over time.

To study extra about Databricks SQL, go to our web site or learn the documentation. You may as well try the product tour for Databricks SQL. Suppose you need to migrate your present warehouse to a high-performance, serverless information warehouse with a fantastic person expertise and decrease whole price. In that case, Databricks SQL is the answer — strive it free of charge.