Skip to content

Assessment Item Fact

Table Description

This fact table contains data regarding assessment items taken by users.

Grain: One row per assessment per item per user.

Table Type: Type 1

Key Business Uses: - Analyze item-level performance and response patterns - Calculate question difficulty and discrimination metrics - Track time spent on individual items - Support detailed diagnostic reporting

Primary Relationships: - Links to ASSESSMENT_FACT via ASSESSMENT_ID for assessment session context - Links to USER_DIM via USER_KEY for student information - Links to ORG_DIM via ORG_KEY for organization context - Links to LEARNOSITY_ITEMS_DIM via ITEM_KEY for item metadata

Table Overview

This table contains 31 columns.

Quick Navigation

Column Definitions

app_domain_key

Business Definition

Surrogate foreign key to APP_DOMAIN_DIM identifying the specific domain within the application (e.g., Reading, Math) for this item. Resolved by matching application_url, domain_name, environment_id, and application_name. A value of -1 indicates the domain could not be resolved.

Data Type

NUMBER(38,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

application_key

Business Definition

Surrogate foreign key to APPLICATION_DIM identifying the application or curriculum context (e.g., CAT, Benchmark) for this item attempt. Resolved by matching application_url and application_name from source events. A value of -1 indicates the application could not be matched.

Data Type

NUMBER(38,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_id

Business Definition

Learnosity session identifier linking this item response to the parent assessment attempt. This corresponds to the learnositysessionid from source events and enables joins to ASSESSMENT_FACT for session-level context. Null values indicate orphaned item records that could not be matched to a session.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_completed_on

Business Definition

Timestamp when the student submitted or completed this assessment item, captured from AssessmentItemEvent with action 'Completed'. This marks the end of active engagement and is paired with ASSESSMENT_ITEM_STARTED_ON to calculate attempt duration. Null indicates the item was not completed.

Data Type

TIMESTAMP_NTZ

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_completed_on_key

Business Definition

Surrogate date key in YYYYMMDD format representing when the item was completed, enabling efficient date dimension joins and partition pruning. Derived from ASSESSMENT_ITEM_COMPLETED_ON date portion. A value of 99999999 indicates completion date is unknown or null.

Data Type

NUMBER(8,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_grade

Business Definition

Target grade level for this assessment item extracted from item tags using the pattern 'Assessment Grade:X'. This represents the intended instructional grade for the item content and may differ from the student's enrolled grade. Used for grade-level performance analysis and item difficulty alignment.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_graded_on

Business Definition

Timestamp when the grading system finalized the score for this item, captured from GradeEvent with action 'Graded' and assessment_type 'AssessmentItem'. For most items this occurs immediately upon submission; for constructed-response items it may involve human scoring delay. Null indicates grading has not occurred.

Data Type

TIMESTAMP_NTZ

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_graded_on_key

Business Definition

Surrogate date key in YYYYMMDD format representing when the item was graded, enabling efficient date dimension joins and filtering. Derived from ASSESSMENT_ITEM_GRADED_ON date portion. A value of 99999999 indicates grading date is unknown or the item has not been graded.

Data Type

NUMBER(8,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_key

Business Definition

Composite surrogate key uniquely identifying each assessment item response record, constructed from assessment_id concatenated with item_id. This key serves as the unique constraint for incremental loads and enables efficient deduplication across event sources. Use this column as the primary key for joins and to ensure referential integrity with downstream aggregations.

Data Type

VARCHAR

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_started_on

Business Definition

Timestamp when the student first engaged with this assessment item, captured from AssessmentItemEvent with action 'Started'. This marks the beginning of the item attempt window and is used to calculate time-on-task metrics. Null indicates no start event was captured for this item.

Data Type

TIMESTAMP_NTZ

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

assessment_item_started_on_key

Business Definition

Surrogate date key in YYYYMMDD format representing when the item was started, enabling efficient date dimension joins and partition pruning. Derived from ASSESSMENT_ITEM_STARTED_ON date portion. A value of 99999999 indicates the start date is unknown or null.

Data Type

NUMBER(8,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

domain_id

Business Definition

Content domain identifier from the grading event's extensions_domain_id field, representing the skill or content strand assessed by this item (e.g., 'Reading Comprehension', 'Number Operations'). This supports domain-level performance aggregation and curriculum alignment. Null for items without domain classification.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

dw_created_by

Business Definition

User or process identifier that created this record in the data warehouse (e.g., 'DBT_SERVICE', 'ETL_BATCH'). Typically set to CURRENT_USER at insert time. Used for ETL troubleshooting and operational auditing.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

dw_created_on

Business Definition

Timestamp when this fact record was first created in the data warehouse during the ETL process. Used for operational auditing, data lineage, and identifying recently loaded records. All records should have this value populated.

Data Type

TIMESTAMP_NTZ

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

dw_updated_by

Business Definition

User or process identifier that last updated this record (e.g., 'DBT_INCREMENTAL'). Typically set to CURRENT_USER on updates. Used for ETL troubleshooting and change attribution.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

dw_updated_on

Business Definition

Timestamp of the most recent update to this fact record, reflecting when scores, timestamps, or other attributes were modified during incremental processing. Used for incremental load watermarking and identifying recently changed records.

Data Type

TIMESTAMP_NTZ

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

item_id

Business Definition

Natural identifier for the assessment item from Learnosity, normalized to lowercase for consistent matching. This column links to LEARNOSITY_ITEMS_DIM.ITEM_ID for item metadata such as IRT parameters, question type, and content classification. Use this for item-level analysis and content traceability.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

item_key

Business Definition

Surrogate foreign key to LEARNOSITY_ITEMS_DIM enabling efficient joins to item metadata including IRT parameters, question content, and psychometric properties. A value of -1 or null indicates the item could not be matched to the dimension, typically for legacy or unregistered items.

Data Type

NUMBER(38,0)

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

item_order

Business Definition

Sequential position of this item within the assessment session (1-based), indicating when the item was presented to the student. For adaptive assessments, this reflects the CAT engine's item selection sequence. Null if order information is not available from the source event.

Data Type

NUMBER

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

item_response

Business Definition

Student's response to the assessment item with JSON formatting and identifiers stripped for readability. Transformed using regexp_replace to remove quoted IDs and structural JSON characters. Used for response pattern analysis and constructed-response scoring review. May be truncated for very long responses.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

item_stage

Business Definition

Stage or section identifier within a multi-stage adaptive test (MST), indicating which test stage this item belongs to. Used for multi-stage routing analysis and stage-level performance aggregation. Null for single-stage assessments or when stage information is not provided.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

org_key

Business Definition

Surrogate foreign key to ORG_DIM identifying the school or organization context for this item attempt. Resolution logic favors ALA (Accelerated Learning Academy) org assignments when available, falling back to other org associations. A value of -1 indicates the organization could not be determined.

Data Type

NUMBER(38,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

product_key

Business Definition

Surrogate foreign key to PRODUCT_DIM identifying the curriculum product context for this item attempt. Currently defaults to -1 as product resolution is not implemented in the source transformation. Reserved for future product-level segmentation when product context becomes available in source events.

Data Type

NUMBER(38,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

question_type

Business Definition

Classification of the question interaction format (e.g., 'mcq' for multiple choice, 'shorttext', 'clozetext', 'inline choice'). Sourced from AssessmentItemEvent and used for analysis of performance by question format, scoring rule determination, and UI rendering decisions. Null for items without type classification.

Data Type

VARCHAR

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

score_given

Business Definition

Points awarded to the student for this item response, sourced from the GradeEvent. For dichotomous items this is typically 0 or 1; for partial credit items it may be any value up to SCORE_MAX. Null indicates the item has not been graded or score could not be determined.

Data Type

FLOAT

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

score_max

Business Definition

Maximum possible points for this item under the defined scoring model, sourced from the GradeEvent. Use with SCORE_GIVEN to compute percent correct (SCORE_GIVEN / SCORE_MAX). Null indicates the maximum score is not defined or the item uses non-numeric scoring.

Data Type

FLOAT

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

score_standard_error

Business Definition

Standard error of measurement associated with the theta estimate after this item, indicating the precision of the ability estimate. Lower values indicate more reliable estimates. Sourced from the GradeEvent's std_err field. Null for non-adaptive assessments or when standard error is not computed.

Data Type

FLOAT

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

score_theta

Business Definition

IRT ability estimate (theta) updated after this item response, representing the student's proficiency on the latent trait scale. Sourced from the GradeEvent's theta field. This value evolves throughout the CAT session as more items are administered. Null for non-adaptive assessments or when theta is not computed.

Data Type

FLOAT

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

source_system_id

Business Definition

Identifier for the originating system that produced the assessment event, derived from the user's source_system_id. Values exclude system ID 4 (Edge) as those events are filtered out. Use this to segment analysis by source platform when multiple systems feed into the assessment pipeline.

Data Type

NUMBER(38,0)

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

time_taken_complete_seconds

Business Definition

Elapsed time in seconds from item presentation to response submission, as reported by the Learnosity platform (learnosity_time_taken_seconds). This is the authoritative duration metric, preferred over calculated differences between start/complete timestamps. Null if timing data was not captured or the item was not completed.

Data Type

NUMBER

Nullable

Yes

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS

user_key

Business Definition

Surrogate foreign key to USER_DIM identifying the student who attempted this item. Derived from the actor_id (assignee_id for grade events) matched against user_uuid. A value of -1 indicates the user could not be resolved, typically for anonymous or unlinked sessions.

Data Type

NUMBER(38,0)

Nullable

No

Last Updated

2026-01-20T04:47:38 by ASSESSMENT_ITEM_FACT_ANALYSIS