Implementing a Dimensional Information Warehouse with Databricks SQL, Half 3

Dimensional modeling is a time-tested strategy to constructing analytics-ready knowledge warehouses. Whereas many organizations are shifting to fashionable 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 reality tables, emphasizing effectivity and integrity.

Reality tables and delta extracts

In the primary weblog, we outlined the very fact desk, FactInternetSales, as proven under.  In comparison with our dimension tables, the very fact desk is comparatively slender by way of document size, with solely international key references to our dimension tables, our reality measures, our degenerate dimension fields and a single metadata subject current:

NOTE: Within the instance under, we’ve altered the CREATE TABLE assertion from our first submit to incorporate the international key definitions as an alternative 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 function extra express on this reality desk.

The desk definition is pretty simple, but it surely’s value taking a second to debate the LastModifiedDateTime metadata subject. Whereas reality tables are comparatively slender by way of subject depend, they are usually very deep by way of row depend. Reality tables typically home tens of millions, if not billions, of information, typically derived from high-volume operational actions. As a substitute of trying to reload the desk with a full extract on every ETL cycle, we are going to sometimes restrict our efforts to new information and people which were modified.

Relying on the supply system and its underlying infrastructure, there are numerous methods to establish which operational information should be extracted with a given ETL cycle. Change knowledge seize (CDC) capabilities applied on the operational facet are essentially the most dependable mechanisms.  However when these are unavailable, we regularly fall again to timestamps recorded with every transaction document as it’s created and modified.  The strategy is just not bulletproof for change detection, however as any skilled ETL developer will attest, it’s typically the perfect we’ve received.

NOTE: The introduction of Lakeflow Join gives an attention-grabbing possibility for performing change knowledge seize on relational databases.  This functionality is in preview on the time of the writing of this weblog. Nonetheless, as the aptitude matures to increase increasingly more RDBMSs, we count on this to supply an efficient and environment friendly mechanism for incremental extracts.

In our reality desk, the LastModifiedDateTime subject captures such a timestamp worth recorded within the operational system.  Earlier than extracting knowledge from our operational system, we are going to assessment the very fact desk to establish the most recent worth for this subject we’ve recorded.  That worth would be the start line for our incremental (aka delta) extract.

The Reality ETL workflow

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

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

To make this workflow simpler to digest, we’ll describe its key phases within the following sections. In contrast to the submit on dimension ETL, we are going to implement our logic for this workflow utilizing a mixture of SQL and Python based mostly on which language makes every step most simple to implement.  Once more, one of many strengths of the Databricks Platform is its help for a number of languages. As a substitute of presenting it as an all-or-nothing alternative made on the prime of an implementation, we are going to present how knowledge engineers can rapidly pivot between the 2 inside a single implementation.

Steps 1-3: Delta extract part

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 most recent recorded worth for LastModifiedDateTime.  If the very fact desk is empty, accurately upon initialization, we outline a default worth that’s far sufficient again in time that we imagine it is going to seize all of the related knowledge within the supply system:

We will now extract the required knowledge from our operational system utilizing that worth.  Whereas this question consists of 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 objects which are new or modified (or related to gross sales orders which are new or modified):

As earlier than, the extracted knowledge is continued to a desk in our staging schema, solely accessible to our knowledge engineers, earlier than continuing to subsequent steps within the workflow. If we now have any further knowledge cleaning to carry out, we should always achieve this now.

Step 4: Late arriving members part

The everyday sequence in an information warehouse ETL cycle is working our dimension ETL workflows after which our reality workflows shortly after.  By organizing our processes this manner, we are able to higher guarantee all the knowledge required to attach our reality information to dimension knowledge shall be in place.  Nonetheless, there’s a slender window inside which new, dimension-oriented knowledge arrives and is picked up by a fact-relevant transactional document.  That window will increase ought to we now have a failure within the total ETL cycle that delays reality knowledge extraction.  And, after all, there can at all times be referential failures in supply techniques that permit questionable knowledge to look in a transactional document.

To insulate ourselves from this downside, we are going to insert right into a given dimension desk any enterprise key values present in our staged reality knowledge however not within the set of present (unexpired) information for that dimension.  This strategy will create a document with a enterprise (pure) key and a surrogate key that our reality desk can reference.  These information shall be flagged as late arriving if the focused dimension is a Kind-2 SCD in order that we are able to replace appropriately on the subsequent ETL cycle.

To get us began, we are going to compile an inventory of key enterprise fields in our staging knowledge.  Right here, we’re exploiting strict naming conventions that permit us to establish these fields dynamically:

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

Discover that we now have 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 subject and desk naming conventions to establish the dimension desk that ought to maintain that key after which carry out a left-semi be part of (just like a NOT IN() comparability however supporting multi-column matching if wanted) to establish any values for that column within the staging desk however not within the dimension desk.  Once we discover an unmatched worth, we merely insert it into the dimension desk with the suitable setting for the IsLateArriving subject: 

This logic would work high quality for our date dimension references if we needed to make sure our reality information linked to legitimate entries.  Nonetheless, many downstream BI techniques implement logic that requires the date dimension to accommodate a steady, uninterrupted collection 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 motive, we want barely totally different logic for any late arrival dates:

If in case 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() perform builds a sequence of values based mostly on a specified begin and cease.  The result’s an array that we are able to then explode (utilizing the explode() perform) so that every factor within the array kinds a row in a outcome set.  From there, we merely evaluate the required vary to what’s within the dimension desk to establish which parts should be inserted. With that insertion, we guarantee we now have a surrogate key worth applied on this dimension as a good key in order that our reality information may have one thing to reference. 

Steps 5 – 6: Information publication part

Now that we will be assured that every one enterprise keys in our staging desk will be matched to information of their corresponding dimensions, we are able to proceed with the publication to the very fact desk.

Step one on this course of is to search for the international key values for these enterprise keys.  This may be executed as a part of a single publication step, however the giant variety of joins within the question typically makes this strategy difficult to keep up. For that reason, we’d take the much less environment friendly however easier-to-comprehend and modify the strategy of wanting 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 simple 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 out there fields with these supported by the very 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 simple. We match our incoming information to these within the reality desk based mostly on the degenerate dimension fields, which function a singular identifier for our reality information, after which replace or insert values as wanted:

Subsequent steps

We hope this weblog collection has been informative to these looking for to construct dimensional fashions on the Databricks Platform.  We count on that many skilled with this knowledge modeling strategy 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 applied 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 knowledge engineers will discover this makes their work extra simple to implement and help over time.

To be taught extra about Databricks SQL, go to our web site or learn the documentation. You may as well take a look at the product tour for Databricks SQL. Suppose you need to migrate your present warehouse to a high-performance, serverless knowledge warehouse with a terrific person expertise and decrease complete price. In that case, Databricks SQL is the answer — strive it without spending a dime.