Facts and Dimensions for Software Engineers
I’ve been using Google BigQuery at work and run into a few concepts I hadn’t come across as a software engineer. I figured I’d write this post for future reference and for anyone facing similar issues.
If you are a software engineer, you’re probably familiar with primary keys, foreign keys and joins. In a typical application data model, we have tables like user, role and user_role_association. We do this to isolate data into specialized tables and prevent data duplication.
In this example, the schema might look something like this:
user
----
user_id
username
email
role
----
role_id
role_name
user_role_association
----
user_id
role_id
The association table tells us the relationship between the two entities, user and role.
In warehousing we use terms like fact, dimension and bridge tables.
In an application, we are mostly concerned with the most up-to-date state of data. In a warehouse, however, we are concerned about a different type of problem. Often we want to know how things have changed over time instead. That does not mean that we can’t use a warehouse for querying the current state of data. For example, if we wanted to represent the above model in a data warehouse, we would use a schema like this:
dim_user
----
user_id
username
email
dim_role
----
role_id
role_name
bridge_user_role
----
user_id
role_id
This should look familiar. A dimension is any entity and bridge tables are association tables which tell you how two entities are linked.
Now say we want to know which roles a user had last month. The bridge table only shows their current roles, so we need to keep a history of assignments. We can store those assignments in a fact table like this:
fact_user_role_assignment
----
user_id
role_id
assigned_at
revoked_at
assigned_by_user_id
assignment_source
Each row in this table captures a user-role assignment event, i.e., an event that led to the current state of user-role assignments. We record each assignment as a fact. Since this table does not record any numerical measures, it is called a factless fact table.
These approaches serve different needs. In a warehouse system, we are interested in how the state of the system changes over time whereas in a traditional application database, we are interested in the latest value of the data and typically don’t need historical data.