What can I access in the Insights Data Lake?

Provides information about the data assets stored within the data lake and is a valuable resource into the structure, meaning, and usage of the data. Created so users can navigate and make informed decisions when accessing the data lake environment.

Data Lake Tables

Data Lake Views

Data Lake Tables

academic_levels

Academic level is an academic unit representation of the institutional hierarchy. It typically stops at the department, and object relations to items in this table include learning outcomes, programs and courses.

Field Name	Type	Description
ddl_id	char	Internal UUID (v4).
name	varchar	Hierarchy level name (ex: College of Medicine)
parent_id	varchar	Internal UUID (v4) of parent hierarchy level (optional) (ex: Academic Health Center).
root_id	varchar	Internal UUID (v4) of optional top level of the academic hierarchy (optional).

academic_subjects

Academic subjects are either areas of study or departments (the BIO in BIO101). We understand that many institutions will compose those differently, we hope to accommodate all of these differences.

Field Name	Type	Description
ddl_id	char	Internal UUID (v4).
name	varchar	Academic subject code (ex: BIO).
title	varchar	Longform name representation of academic subject code (optional) (ex: Biology).

assessment_results

Assessment result is a representation of a student’s mastery of a competency or learning outcome.

Field Name	Type	Description
canvas_id	int	Canvas ID of assessment result for a single user and outcome.
ddl_id	varchar	Internal UUID (v4).
mastery	bool	A student achieved benchmark value for mastery of a competency or learning outcome.
score	real	The most recent raw score achieved by a student on outcome/competency rating scale.
possible	real	The maximum achievable score on assessment rating scale.
percentage	real	A students’ score divided by the max possible score.
mastery_level_id	varchar	Internal UUID (v4).
mastery_level	varchar	Institutional mastery level achieved by student on assessment (optional) (ex: Partial Mastery).
mastery_level_seq	int	Ordering key for increasing levels of mastery achieved, relative to institution-wide scale.
canvas_user_id	int	Canvas ID of student being assessed.
canvas_assignment_id	varchar	Canvas ID of results assignment source.
canvas_learning_outcome_id	varchar	Canvas ID of learning outcome assessed.
purl_assignment_id	varchar	Non-Canvas assignment (PURL) ID.
context_type	varchar	Context of result collection (ex: canvas_course_embedded, juried_assessment).
outcome_id	varchar	Internal UUID (v4).
outcome_canvas_id	int	Canvas ID of assessed learning outcome.
outcome_name	varchar	Name of outcome being assessed.
outcome_type	varchar	Type of outcome being assessed (ex: ISLO, PSLO, CSLO).
canvas_enrollment_term_id	int	Canvas’ ID of enrollment term during which assessment was completed.
term_name	varchar	Friendly name of result collection term (ex: Spring 2023).
canvas_course_id	varchar	Canvas’ ID of course offering in which assessment was completed..
course_id	varchar	Internal course UUID (v4).
course_level_id	varchar	Internal course level UUID (v4).
course_level_title	varchar	Friendly course level title (ex: Level 1).
course_name	varchar	Internal course name (name of parent course instance in Insights for canvas offering).
subject_code	varchar	Academic subject/department code to which course belongs (ex: BIOL).
slo_outcome_category_id	varchar	Learning outcome category internal UUID (v4) (optional).
slo_outcome_category_name	varchar	Name of learning outcome category to which assessed outcome belongs (optional).
slo_outcome_category_description	varchar	Description of learning outcome category to which assessed outcome belongs (optional).
evidence_id	varchar	Assignment submission UUID (v4) (optional).
calc_method	varchar	‘direct’ or ‘indirect’. ‘direct’ where outcome assessed is the outcome scored. ‘indirect’ where outcome assessed is curriculum mapped to outcome score owner, ie: a Canvas result for a CLO curriculum-mapped to an ILO creates 2 result entries: one ‘direct’ score for the CLO as well as one ‘indirect’ for the implicitly assessed ILO.
outcome_classification	varchar	‘new’ - First version of an outcome ‘version’ - Later outcome version ‘variant’ - Variant of an outcome version ‘private’ - Outcome is unrelated to outcomes approved in curriculum. Outcome belongs to those faculty members who teach offerings in which it appears.
outcome_display_name	varchar	Name of the most recent outcome version.
parent_outcome_version_id	varchar	Internal outcome UUID (v4) of the first version of an outcome on record.
assignment_type	varchar	‘’quiz’ if Canvas quiz produced outcome result; ‘assignment’ if Canvas assignment rubric produced outcome result.
assessment_id	varchar	Internal UUID (v4).
plan_id	varchar	Internal UUID (v4).
cycle_id	varchar	Internal UUID (v4).
cycle_name	varchar	Title of the cycle in Insights which produced the result

course_groups

A table that represents the Insights’ course groups.

Field Name	Type	Description
ddl_id	char	Internal UUID (v4).
name	varchar	Friendly name of course group (ex:CSCI Prerequisites).
group_type	varchar	User defined string describing the properties of the course group (ex: Elective, Required)
last_updated	timestamp	The last occurrence of a write to the field

course_group_offerings

A join table to connect offerings to course groups.

Field Name	Type	Description
course_group_ddl_id	char	Internal course group UUID (v4).
offering_ddl_id	char	Internal offering UUID (v4).

hierarchy_level_subjects

A join table to connect academic subjects (departments) to hierarchy levels.

Field Name	Type	Description
level_ddl_id	char	Internal UUID (v4) for the level.
subject_ddl_id	char	Internal UUID (v4) for the subject.

offerings

Term by term instances of a course (aka sections).

Field Name	Type	Description
ddl_course_id	varchar	Internal UUID (v4) for the course.
ddl_offering_id	varchar	Internal UUID (v4) for the offering.
canvas_id	int	The Canvas id of the course offering.
name	varchar	The name of the Canvas course offering.
account_id	int	The Canvas account id (may not be root id).
canvas_uuid	varchar	The Canvas UUID for the offering.
sis_course_id	varchar	Correlated id for the record for this course in the SIS system, if configured in Canvas (optional).
enrollment_term_id	int	The Canvas id of the offering enrollment term.
created_at	varchar	The time at which this offering was created.
start_at	varchar	Offering start date in ISO8601 format.
canvas_course_code	varchar	Raw Canvas course code (ex: CSCI200-4362).
ddl_course_code	varchar	Insights representation of Canvas course code, post-intake processing (ex: CSCI200).
root_account_id	int	Canvas id of the root account owner of this offering/section.
end_at	varchar	Section end date in ISO8601 format.
apply_assignment_group_weights	bool	Whether or not to apply weights to the assignment group
total_students	int	The number of students enrolled in the offering
academic_subject	varchar	The offering’s subject/department owner (ex: BIOL).
course_level_id	varchar	Internal course level UUID (v4).
course_level_title	varchar	The friendly description for the course level (ex: Level 1)

programs

This table represents all the setup in programs in Insights.

Field Name	Type	Description
ddl_id	char	Internal UUID (v4).
name	varchar	Friendly name of program (ex: Accounting A.S.)
program_type	varchar	Description of the program type (ex: Associate of Science)
accreditor	varchar	Program accreditor (ex: CCNE, ACIN, AACSB, CAEP, ABET)
last_updated	timestamp	Date of last up to the program (ex: 2023-06-30 20:13:08.000 -0500)

program_course_groups

A join table to connect course groups to programs.

Field Name	Type	Description
program_ddl_id	char	Internal program UUID (v4).
course_group_ddl_id	char	Internal course group UUID (v4).

student_demographics

Extensible attributes about a Canvas user, in association with a provided Canvas user id.

Field Name	Type	Description
student_id	int	Canvas user id
attribute	varchar	Extensible demographics field attribute (ex: Gender, Ethnicity, Age)
value	varchar	Extensible demographics values (ex: Female)
valid_from	timestamp	When value last changed (ex: 2022-11-03 15:09:01.000)

student_learning_outcomes

A table representing all the learning outcomes setup in Insights.

Field Name	Type	Description
id	Int, not nullable	Auto-incrementing id.
slo_source_outcome_id	int	Outcome id in source context (source example: Canvas)
slo_context_id	varchar	Context’s internal UUID (v4) (context example: Canvas course offering).
ddl_outcome_id	varchar	Outcome’s internal UUID (v4).
slo_outcome_statement	varchar	Learning outcome statement.
slo_outcome_title	varchar	Learning outcome title.
slo_outcome_type	varchar	Learning outcome type (ex: ISLO, PSLO, CLSO).
slo_outcome_category_id	varchar	Outcome category UUID (v4) (optional).
slo_outcome_category_name	varchar	Outcome category name (ex: Nursing (BSN) - PLO) (optional).
slo_outcome_category_description	varchar	Outcome category description (ex: Nursing BSN Program Learning Outcomes) (optional).
outcome_classification	varchar	‘new’ - First version of an outcome ‘version’ - Later outcome version ‘variant’ - Variant of an outcome version ‘private’ - Outcome is unrelated to outcomes approved in curriculum. Outcome belongs to those faculty members who teach offerings in which it appears.
outcome_display_name	varchar	Name of the most recent outcome version.
parent_outcome_version_id	varchar	Internal outcome UUID (v4) of the first version of an outcome on record.

terms

A Table representing the terms setup in Insights.

Field Name	Type	Description
ddl_id	varchar	Internal UUID (v4).
term_id	int	Canvas term ID.
title	varchar	Name of the term (ex: Spring 2023).
description	varchar	Description of the term (ex: Spring 2023).
start_at	varchar	Start date of the term (ex: 2023-01-16T06:00:00Z).
end_at	varchar	End date of the term (ex: 2023-01-16T06:00:00Z).
workflow_state	varchar	Term state (ex: active).

Data Lake Views

v_academic_hierarchy

This view flattens out the academic hierarchy. (Note: this view was created to be a helper to v_assessment_results.)

Field Name	Type	Description
parent_name	varchar	Name of the parent organization (ex: College of Business)
school_name	varchar	Name of the school or department (ex: Accounting Education)
name	varchar	Academic subject/department code (ex: ACCT)
title	varchar	Longform name representation of academic subject code (optional) (ex: ACCT)

v_academic_results

This view flattened out assessment results by academic hierarchy, academic subject, program, and demographics.

Field Name	Type	Description
program_name	varchar	Friendly name of program (ex: Accounting A.S.)
accreditor	varchar	Program accreditor (ex: CCNE, ACIN, AACSB, CAEP, ABET)
course_group_name	varchar	Friendly name of course group.
course_name	varchar	Course name
term_name	varchar	Term name
mastery	bool	A student achieved benchmark value for mastery of a competency or learning outcome.
score	real	The most recent raw score achieved by a student on outcome/competency rating scale.
possible	real	The maximum achievable score on assessment rating scale.
percentage	real	A students’ score divided by the max possible score.
mastery_level	varchar	Institutional mastery level achieved by student on assessment (optional) (ex: Partial Mastery)
demographic_attribute	varchar	Demographics attribute (ex: Gender)
demographic_value	varchar	Demographics values (ex: Female)
demographic_valid_from	timestamp	When value last changed (ex: 2022-11-03 15:09:01.000)
parent_name	varchar	Name of the parent organization (ex: College of Business)
school_name	varchar	Name of the school or department (ex: Accounting Education)
academic_subject_name	varchar	Academic subject code (ex: ACCT)
academic_subject_title	varchar	Longform name representation of academic subject code, if available (optional) (ex: Accounting)
outcome_name	varchar	Name of outcome being assessed.
outcome_type	varchar	Type of outcome being assessed
slo_outcome_title	varchar	Learning outcome title
slo_outcome_statement	varchar	Learning outcome statement
slo_outcome_type	varchar	Learning outcome type (ex: ISLO, PSLO, CLSO)
slo_outcome_category_name	varchar	Outcome category name (ex: Nursing (BSN) - PLO)
slo_outocme_category_description	varchar	Outcome category description (ex: Nursing BSN Program Learning Outcomes)