Building a data model base table

1. Building a data model base table

Welcome back. In this video we'll demonstrate building a base table using a join.

2. Scenario setup

Business analysts at Oakmark have been continually performing the same manual join between two tables: loans and loan applications. The resulting table can be used in analyses that happen every day, like reviewing the relationship between credit score and loan terms.

3. Scenario setup

To ensure consistent, reliable, and easy analysis, let's join these tables together into a base table in a data model that we'll provide to users.

4. Demo

Let's start by creating a new data model from the Sigma home screen.

5. Demo

We'll select create new, and then choose Data model. The blank data model opens. You might notice that unlike a workbook, data models start in the published state. That means anyone with the right permissions can see this blank data model. However, they won't see any future changes until we hit publish.

6. Demo

Now, let's consider the base table we want to create, and identify our data sources. Recall that our end goal is a table with data about both loans and loan applications. From the add element bar, we can navigate to Data, add a Table, and add in both the loans and loan applications tables from our data platform.

7. Demo

Before we combine these tables, let's consider the desired granularity. We want a table where each row contains one loan and the corresponding information from its application. We know that each loan came from a single application, so it's intuitive to start from the loans table, and add on data from the applications table.

8. Demo

Now, we're ready to identify a shared column to use as our join key.

9. Demo

Every row in the loans table has an application ID that identifies the application that loan originated from. This same column exists in the applications table too, uniquely identifying each application, so it's a great candidate for our join key.

10. Demo

With our target granularity and join key identified, let's delete the element for loan applications. We'll add it back in by creating a join from the existing loans element.

11. Demo

From the element options for the loans table, select Element source. Under transform, select Join. This opens the Select source modal, where we can search for the applications table, and click Select.

12. Demo

On the create join screen, set the join type to Inner join. For the Join keys, select the Application ID column from both tables. Notice that all the Loans have exactly one matching application. But, not every application has a matching loan. This is expected, as not every application is accepted. So, we can select preview output, and then Done.

13. Demo

The result is a joined table with data from both the loans and applications tables.

14. Demo

In the editor panel, we can select what columns we want to use, and how we want to arrange them. For example, we can place application date next to loan start date, and credit score next to loan term years and interest rate.

15. Demo

Click publish, and now we have a base table for users to work from in their Sigma workbooks.

16. Let's practice!

Now it's your turn! In the next exercises, practice building a base table.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.