Contents

1. What are the Facts?

You can find the video summary for this here.

The next steps are now creating the Fact and Dimension tables for reporting. These types of tables are created from the Kimball Methodology in our analytics engineering process.

The following are some of the things to consider in this implementation:

  • Dimension and Fact Table Materialization

  • Embedding business logic (SQL Transformations)

  • Aggregations

2. Dimension and Fact Tables

Now, we surface the Stage-layer tables into dimensional and fact table reporting, for our Data Mart.

7 eleven

Per Kimball Methodology:

  • “A fact table contains the numeric measures produced by an operational measurement event in the real world. At the lowest grain, a fact table row corresponds to a measurement event and vice versa” - source

  • “Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts. With the grain of a fact table firmly in mind, all the possible dimensions can be identified. Whenever possible, a dimension should be single valued when associated with a given fact row.” - source

The following are some example dbt commands to create our particular tables (based off our dbt project’s folder structure):

dbt run --models mart.dim.*

dbt run --models mart.fct.*

Example #1

3. Embedding Business Logic

Your business stakeholder wants a currency conversion of Net Amount into British Pounds. Moreover, they want the estimated Net Amount per order quantity.

That is, your business stakeholder wants the following:

  • Net Amount (Pounds)

  • \(\tfrac{\text{Net Amount (Pounds)}}{\text{Unit Price}}\)

as two additional columns in the catalog sales reporting.

How would we go about it?

We transform the existing columns in our fact table catalog_sales_fct, as seen below:

         catalog_sales.*,
        -- X $Dollars * (British Pounds / $Dollars)
        net_amount * (.77/1) AS net_amount_british_pounds,
        net_amount_british_pounds / quantity AS net_amount_per_order_unit_british_pounds

Example #2

3.1. Aggregations

The following is an implementation of aggregating existing data for, well, aggregated tables. This is useful for reports that require:

  • Overall Summaries

  • Snappy/Quick reporting updates

Decrease Latency

The following are some example commands and visualizations on finally materializing the aforementioned tables.

dbt run --models mart.*

Example #3

In the following, you’ll see dbt commands for respective materialization types:

  • dimensions

  • facts

  • dimensions + facts (everything in the “marts” folder)

Example #4

Congrats! Now you understand materializing tables in dbt, and a process for going about said materialization.

However, there is so much more to dbt we have not covered! I wish I could cover more, but sadly we cannot cover all of the, ever growing, dbt functionality.

That being said, we will at least cover a couple of additional, useful items. These useful dbt features are:

  • Documentation

  • Testing

  • Advanced Templating

Get up to the Stage 1. Documention