⏰ 👀 COMING SOON - This data set is currently in development and is subject to change
FCT_RECORD_STORE_SERVICE_STATES provides granular detail on whether a user has visited and completed individual parts of a piece of content - not just the content as a whole. For instance, an eLearning package might be broken into several components, and this table lets you see a user's visit and completion status for each one separately.
It captures where a learner currently stands within a piece of learning - for example, their current score, whether they've finished, or where they left off so they can resume later. The table can be joined to DIM_USER_SERVICE_USER on User ID (many-to-one) to identify which users the activity belongs to.
Data retention is based on Stored At, covering the previous two calendar years plus the current one. Because this table reflects a current, "as-is" state, it only retains the most recent snapshot for each record - not a full history.
The table's structure, format, and which fields are populated, depend on how the learning package was built and which authoring tool was used. For example, a record won't have a score or user answer if the tracked activity is simply watching a video with no quiz or assessment attached.
Because of this variability, the table can be less straightforward to read and query than a standard fact table - field values and meaning shift depending on the underlying content, so familiarity with how the source package was structured is recommended when interpreting the data.
COLUMN NAME | DATA TYPE (FIELD SIZE) | DESCRIPTION | CURRENT AVAILABLE VALUES | EXAMPLE |
|---|---|---|---|---|
Activity ID | VARCHAR(16777216) | The unique digital address for the content (like the specific URL of a quiz or video) | content/123a12b3cd4e5f67g890h1ij | |
Attempt Number | NUMBER(38,0) | A counter showing how many times the learner has tried this activity (e.g., their 1st try, 2nd try) | 1 | |
Body ID | VARCHAR(16777216) | The unique ID for this specific save record | 123a12b3cd4e5f67g890h1ij | |
Doc Created At | TIMESTAMP_NTZ(9) | Timestamp inherited from parent snowflake object(s) | 2025-01-01T00:00:00.000+0000 | |
Doc Updated At | TIMESTAMP_NTZ(9) | Timestamp inherited from parent snowflake object(s) | 2025-01-01T00:00:00.000+0000 | |
Ended At | TIMESTAMP_NTZ(9) | The date and time the learner finished or closed the session | 2025-01-01T00:00:00.000+0000 | |
Is Complete | BOOLEAN | A status indicating if the learner has finished the entire lesson or activity | TRUE, FALSE, NULL | TRUE |
Is Interaction Complete | BOOLEAN | Used to track if a specific part of a lesson (like one specific interaction) is finished | TRUE, FALSE, NULL | TRUE |
Is Visited | BOOLEAN | A record of whether the learner has actually opened or viewed a specific section | TRUE, FALSE, NULL | TRUE |
Passed | BOOLEAN | A simple "True" or "False" showing if the learner met the passing requirements | TRUE, FALSE, NULL | TRUE |
Question Details | VARIANT | A breakdown of exactly how the learner answered specific questions in a quiz | [ | |
Record ID | VARCHAR(16777216) | An identifier for the RECORD_STORE_SERVICE_STATES document | 123a12b3cd4e5f67g890h1ij | |
Registration ID | VARCHAR(16777216) | A unique code that identifies one specific "sitting" or attempt by a learner | 1a234bcd-567e-890f-g12h-i3j4kl5m6n7 |