Dimension tables
Dimensions provide context surrounding the fact tables. Simply put, they give the who, what, where of a fact.
fact_learn_events
, for example, can be joined to dim_content
to get more information on the content items (title, technology, topic, etc.)
The dimension tables available are:
dim_content
This table stores detailed information on the different types of content available on the platform.
group_id
The unique group identifier
content_type
The type of content (course, chapter, project, assessment, etc.)
content_id
The unique content identifier
exercise_id
The unique exercise identifier
chapter_id
The unique course chapter identifier
course_id
The unique course identifier
content_title
The title of the content item
technology
The technology of the content item (R, Python, SQL, Spark, etc.)
topic
The topic of the content item (Programming, Data Manipulation, Artificial Intelligence, etc.)
slug
The slug of the content item, if applicable
xp_available
The maximum XP points the user can earn by completing the content item
course_state
The current status of the course (Live, Soft Launch, Archived)
course_is_custom
Whether the course is custom or not
assessment_id
The unique identifier of the assessment
assessment_active
The current status of the assessment
project_id
The unique identifier of the project
project_is_guided
Whether the project is guided or unguided
project_state
The current status of the project (Live, Archived)
project_is_custom
Whether the project is custom or not
practice_pool_id
The unique identifier of the practice pool
practice_pool_status
The current status of the practice pool (Live, Archived)
exercise_is_deleted
Whether the exercise has been deleted
dim_user
This table contains detailed information on the user. This table is commonly joined with other tables to enrich user-related data, as most fact event tables contain a user ID.
group_id
The unique group identifier
user_id
The unique user identifier
The user’s email address
first_name
The user’s first name
last_name
The user’s last name
slug
The slug of the user, used in the user's portfolio/profile URL
avatar_file_name
The avatar file name of the user
dim_team
This table stores detailed information about teams.
group_id
The unique group identifier
team_id
The unique team identifier
team_name
The name of the team
created_at
The timestamp when the team was created
updated_at
The timestamp when the team was last updated
deleted_at
The timestamp when the team was deleted, if applicable
group_created_at
The timestamp when the group was created
slug
The slug of the team
team_color_hexcode
The hexacode of the team’s color
dim_track
This dimension table is designed to store detailed information about skill, career, and custom tracks. It’s commonly used to enrich track metrics with track information. For example, it helps in the Progress report where it provides the track title.
Entries in this table contain both a track_id
and track_version_id
because a track can contain multiple versions.
Track fact events are always tied to a track_version_id
.
group_id
The unique group identifier
track_version_id
The unique track version identifier. Every time a particular track is updated, it gets a new track_version_id
while keeping the same track_id
track_id
The unique track identifier
track_type
The type of track (public or custom)
track_title
The title of the track. The full name of the track is a concatenation of track_title
and track_subtitle
track_subtitle
The subtitle of the track
track_category
The category of the track (career or skills)
is_current_version
Whether this track version is the current (latest) version of the track
track_version_number
The ordinal version number of the track. Each time the track is updated, the track_version_number
increases by one
track_published_at
The timestamp when the track was published
track_archived_at
The timestamp when the track was archived, if applicable
track_technology
The technology of the track (R, Python, Shell, etc.)
track_state
The current status of the track (live, archived)
track_short_description
A short description of the track
track_slug
The track slug
dim_certification
This table stores details on certifications.
certification_id
The unique certification identifier
certification_name
The name of the certificate
certification_type
The certification is restricted if applicable (Public, Restricted)
dim_date
This table contains a row per date from the year 2010 onwards. It serves as a support table for joining other tables with date_id
.
This table includes pre-calculated values for common date-related operations, such as the day of the year, the day of the month, and the day of the week.
date_id
The unique date identifier (YYYYMMDD)
date
The date
day_of_week
The day of the week for that date (1-7)
day_of_month
The day of the month for that date (1-31)
day_of_quarter
The day of the quarter for that date (1-91)
day_of_year
The day of the year for that date (1-366)
Last updated