Joins and unions in a data model

Data Modeling in Sigma

Ben Harris

Technical Instructional Designer at Sigma

Purpose of a base table

 

 

• First step in creating a data model: add a table
• Not a strict requirement in Sigma
• In this course, data models start with a base table

Purpose of a base table

 

 

• Base table = core unit of analysis
• One row per object or event you're modeling
• Example: orders model → one row = one order
• Defines granularity of the data

Purpose of a base table

 

 

• After granularity: does base table need more warehouse tables?
• Example: orders + customer data
• Combine orders and customers into one base table

Purpose of a base table

 

 

• Two ways to combine data: joins and unions
• Same concept as in SQL and other tools
• This course: use them in Sigma

Purpose of a base table

 

More on joins and unions: DataCamp resources and Sigma documentation:

DataCamp resources

• Joining Data in SQL course (recommended)
• Introduction to SQL Joins tutorial

Sigma resources:

• Joins Overview

Joins

Joins

• Bring columns from one table onto another
• Use a shared column as the key
• Result: table gets wider (more columns)

Joins

 

• Example: accounts + customer data
• Join key: customer ID
• Each row gets account holder info (e.g. email)

Unions

Unions

 

• Add rows from one table onto the end of another
• Match columns to one another
• Result: longer table (more rows)

Unions

 

 

• Use when similar data is split across tables
• Tables have matching columns and same granularity
• Union = one continuous dataset

Unions

 

• Example: two locations → two transaction tables
• Union them → single table, all transactions

Purpose of combining data

 

• If users often join/union manually → add to the model
• Consistent base table for everyone
• Fewer errors from repeated manual joins

 

Let's practice!

Data Modeling in Sigma