Skip to content

ILC Data Dictionary - Landing Zone Open Schema

Overview

This directory contains data dictionary documentation for ILC (Imagine Learning Classroom) tables in the WELD_NORTH_PROD.LANDING_ZONE_OPEN schema.

JIRA Ticket: DP-3160 - Integrate new ILC data dictionary info into DataDictionary Repo

Schema Information

  • Database: WELD_NORTH_PROD
  • Schema: LANDING_ZONE_OPEN
  • Source System: ILC (Imagine Learning Classroom)
  • Tables: 15 tables
  • Columns: 197 columns documented

Tables

Core Entity Tables

ILC_CUSTOMERS (32 columns)

ILC customer organizations and sites - Row Count: 1,875 - Key Fields: ID (PK), NAME, SUBDOMAIN, SITE_TYPE - Purpose: Stores customer organization information including site configuration and settings

ILC_USERS (25 columns)

ILC users including students, teachers, and administrators - Row Count: 7,781,390 - Key Fields: ID (PK), CUSTOMER_ID (FK), EMAIL, USERNAME, ROLE - Purpose: Central user registry for all ILC users across different roles - Roles: 1=Global Admin, 2=Teacher, 4=Site Admin, 5=Student, 6=Author, 7=School Admin, 8=Customer Admin, 10=Customer Viewer

ILC_SCHOOLS (6 columns)

ILC schools within customer organizations - Row Count: 12,956 - Key Fields: ID (PK), CUSTOMER_ID (FK), NAME - Purpose: School entities within customer organizations

Class & Assignment Tables

ILC_KLASSES (13 columns)

ILC classes (klasses) - Row Count: 6,139,908 - Key Fields: ID (PK), NAME, CUSTOMER_ID (FK), ACTIVE, GRADE_LEVEL_CODES - Purpose: Class/course sections where students are enrolled - Note: ACTIVE=false means class is archived

ILC_TEACHER_KLASSES (6 columns)

Association between teachers and classes - Row Count: 8,404,196 - Key Fields: ID (PK), TEACHER_ID (FK), KLASS_ID (FK) - Purpose: Many-to-many relationship between teachers and classes

ILC_USER_SCHOOLS (5 columns)

Association between users and schools - Row Count: 6,701,599 - Key Fields: ID (PK), USER_ID (FK), SCHOOL_ID (FK) - Purpose: Links users to their schools

Content & Assignment Tables

ILC_LESSON_PLANS (23 columns)

ILC lesson plans and instructional content - Row Count: 919,389 - Key Fields: ID (PK), TITLE, LESSON_TYPE - Purpose: Instructional content and lesson materials - Lesson Types: - 0 = Lesson Plan (full lesson) - 1 = Instructional Video - 2 = Assessment - 3 = Activity - Note: Types 0, 2, and 3 typically have digital items

ILC_LZ_CODES (14 columns)

ILC assignments (lz_codes) - Row Count: 3,440,442 - Key Fields: ID (PK), CODE, USER_ID (FK), CUSTOMER_ID (FK), LZ_CODEABLE_ID (FK), LEARNOSITY_ACTIVITY_ID - Purpose: Assignment definitions created by teachers - Note: ACTIVE=false means assignment is archived

ILC_LZ_CODE_VIEWS (21 columns)

Student interactions with assignments - Row Count: 105,798,216 - Key Fields: ID (PK), STUDENT_ID (FK), LZ_CODE_KLASS_ID (FK), LEARNOSITY_SESSION_ID - Purpose: Tracks student engagement with assignments including timing and completion - Important: Use LZ_CODE_KLASS_ID to link to assignments (ACTIVITY_ID is deprecated) - Timing Fields: - CREATED_AT: When student first loaded assignment - VIEWED_AT: When assignment was viewed - LAST_ANSWERED_AT: When student last interacted - OPEN_AT: When teacher opened for student - CLOSE_AT: When teacher closed for student - RETURN_AT: When teacher returned (optional)

ILC_LZ_CODE_KLASSES (6 columns)

Association between assignments and classes - Row Count: 5,473,734 - Key Fields: ID (PK), LZ_CODE_ID (FK), KLASS_ID (FK) - Purpose: Many-to-many relationship between assignments and classes

Assessment & Scoring Tables

ILC_DATA_DASHBOARD_ITEM_SCORES (10 columns)

Item-level scoring insights used to build data dashboard materialized views - Row Count: 157,040,736 - Key Fields: ID (PK), LEARNOSITY_SESSION_ID, LEARNOSITY_ITEM_REFERENCE, SCORE, MAX_SCORE - Purpose: Item-level scoring data from Learnosity assessments - Note: IS_GRADED = true when assignment is closed and all items have scores or are unattempted - Warning: STANDARDS field is legacy and will be removed - do not use

Standards & Alignment Tables

ILC_STANDARDS (14 columns)

Educational standards (Common Core, state standards, etc.) - Row Count: 68,372 - Key Fields: ID (PK), STANDARD_INITIATIVE_ID (FK), CODE, NAME, SUBJECT_CODE - Purpose: Educational standards aligned to content - Subjects: math, ela

ILC_STANDARD_INITIATIVES (10 columns)

Standard initiatives (e.g., Common Core, state-specific standards) - Row Count: 55 - Key Fields: ID (PK), NAME, ACRONYM - Purpose: Standard frameworks and initiatives

ILC_LESSON_PLAN_STANDARDS (7 columns)

Association between lesson plans and educational standards - Row Count: 5,171,746 - Key Fields: ID (PK), STANDARD_ID (FK), LESSON_PLAN_ID (FK) - Purpose: Links lesson plans to aligned standards

ILC_LEARNOSITY_ITEM_STANDARDS (5 columns)

Association between Learnosity items and educational standards - Row Count: 3,004,458 - Key Fields: ID (PK), LEARNOSITY_ITEM_ID (FK), STANDARD_ID (FK) - Purpose: Links assessment items to standards

Data Relationships

Key Entity Relationships

ILC_CUSTOMERS
    ├── ILC_USERS (via CUSTOMER_ID)
    ├── ILC_KLASSES (via CUSTOMER_ID)
    ├── ILC_SCHOOLS (via CUSTOMER_ID)
    └── ILC_LZ_CODES (via CUSTOMER_ID)

ILC_USERS
    ├── ILC_LZ_CODES (via USER_ID - creator)
    ├── ILC_LZ_CODE_VIEWS (via STUDENT_ID)
    ├── ILC_TEACHER_KLASSES (via TEACHER_ID)
    └── ILC_USER_SCHOOLS (via USER_ID)

ILC_KLASSES
    ├── ILC_TEACHER_KLASSES (via KLASS_ID)
    └── ILC_LZ_CODE_KLASSES (via KLASS_ID)

ILC_LESSON_PLANS
    ├── ILC_LZ_CODES (via LZ_CODEABLE_ID)
    └── ILC_LESSON_PLAN_STANDARDS (via LESSON_PLAN_ID)

ILC_LZ_CODES (Assignments)
    └── ILC_LZ_CODE_KLASSES (via LZ_CODE_ID)
        └── ILC_LZ_CODE_VIEWS (via LZ_CODE_KLASS_ID)
            └── ILC_DATA_DASHBOARD_ITEM_SCORES (via LEARNOSITY_SESSION_ID)

ILC_STANDARDS
    ├── ILC_STANDARD_INITIATIVES (via STANDARD_INITIATIVE_ID)
    ├── ILC_LESSON_PLAN_STANDARDS (via STANDARD_ID)
    └── ILC_LEARNOSITY_ITEM_STANDARDS (via STANDARD_ID)

Important Notes

Data Provenance

  • All data originates from the ILC (Imagine Learning Classroom) PostgreSQL database
  • Data is loaded into Snowflake LANDING_ZONE_OPEN schema
  • See ILC GitHub Schema for source definitions

Deprecated Fields

  • ILC_LZ_CODE_VIEWS.ACTIVITY_ID - Not used anymore, use LZ_CODE_KLASS_ID instead
  • ILC_DATA_DASHBOARD_ITEM_SCORES.STANDARDS - Legacy field, will be removed

Fields Not in Snowflake

The following tables from ILC are NOT available in Snowflake: - data_dashboard_sessions - Assignment-level insights - data_dashboard_items - Item-level insights (different from ILC_DATA_DASHBOARD_ITEM_SCORES)

User Roles

ILC user roles (from ROLE field): - 1 = Global Admin (internal role) - 2 = Teacher - 4 = Site Admin - 5 = Student - 6 = Author - 7 = School Admin - 8 = Customer Admin (internal role) - 10 = Customer Viewer (internal role)

Site Types

ILC customer site types (from SITE_TYPE field): - 0 = Content Internal - 1 = Content Partner - 2 = Customer - 3 = Demo - 4 = Internal - 5 = Technology Partner - 6 = Pilot

Lesson Types

ILC lesson plan types (from LESSON_TYPE field): - 0 = Lesson Plan (full lesson) - typically has digital items - 1 = Instructional Video - 2 = Assessment - typically has digital items - 3 = Activity - typically has digital items

Usage Patterns

Finding Student Assignment Activity

-- Get student assignments and their completion status
SELECT 
    u.USERNAME,
    k.NAME as CLASS_NAME,
    lp.TITLE as LESSON_TITLE,
    lcv.CREATED_AT as FIRST_ACCESSED,
    lcv.LAST_ANSWERED_AT,
    lcv.OPEN_AT,
    lcv.CLOSE_AT
FROM ILC_LZ_CODE_VIEWS lcv
JOIN ILC_USERS u ON lcv.STUDENT_ID = u.ID
JOIN ILC_LZ_CODE_KLASSES lck ON lcv.LZ_CODE_KLASS_ID = lck.ID
JOIN ILC_LZ_CODES lc ON lck.LZ_CODE_ID = lc.ID
JOIN ILC_LESSON_PLANS lp ON lc.LZ_CODEABLE_ID = lp.ID
JOIN ILC_KLASSES k ON lck.KLASS_ID = k.ID
WHERE u.ID = <student_id>
ORDER BY lcv.CREATED_AT DESC;

Finding Teacher Classes and Students

-- Get all classes for a teacher
SELECT 
    t.EMAIL as TEACHER_EMAIL,
    k.NAME as CLASS_NAME,
    k.GRADE_LEVEL_CODES,
    COUNT(DISTINCT lcv.STUDENT_ID) as STUDENT_COUNT
FROM ILC_TEACHER_KLASSES tk
JOIN ILC_USERS t ON tk.TEACHER_ID = t.ID
JOIN ILC_KLASSES k ON tk.KLASS_ID = k.ID
LEFT JOIN ILC_LZ_CODE_KLASSES lck ON k.ID = lck.KLASS_ID
LEFT JOIN ILC_LZ_CODE_VIEWS lcv ON lck.ID = lcv.LZ_CODE_KLASS_ID
WHERE t.ID = <teacher_id>
GROUP BY t.EMAIL, k.NAME, k.GRADE_LEVEL_CODES;

Finding Standards Alignment

-- Get standards aligned to lesson plans
SELECT 
    lp.TITLE as LESSON_TITLE,
    si.ACRONYM as STANDARD_FRAMEWORK,
    s.CODE as STANDARD_CODE,
    s.NAME as STANDARD_TEXT,
    s.SUBJECT_CODE
FROM ILC_LESSON_PLANS lp
JOIN ILC_LESSON_PLAN_STANDARDS lps ON lp.ID = lps.LESSON_PLAN_ID
JOIN ILC_STANDARDS s ON lps.STANDARD_ID = s.ID
JOIN ILC_STANDARD_INITIATIVES si ON s.STANDARD_INITIATIVE_ID = si.ID
WHERE lp.ID = <lesson_plan_id>;

Sync to METADATA_CATALOG

To sync these markdown files to the Snowflake METADATA_CATALOG:

cd DataDictionary
python sync-scripts/markdown_to_metadata_catalog.py --force

This will create 197 records in DATA_GOVERNANCE.DATA_DICTIONARY.METADATA_CATALOG with: - SCHEMA_NAME = 'landing_zone_open' - TABLE_NAME = lowercase table names (e.g., 'ilc_customers') - COLUMN_NAME = UPPERCASE column names (e.g., 'ID')

Documentation Source

  • ILC Team Guidance Document (August 2024)
  • ILC GitHub Repository: https://github.com/LearnZillion/learnzillion
  • ILC Postgres ER Diagram
  • Internal ILC data model documentation

Maintainer

Data Platform Team - Imagine Learning

Last Updated: December 15, 2025 JIRA: DP-3160