Introduction

The Student Journey data model, introduced in the CCC Data Warehouse with the CCC Data 3.0.0 release, consolidates student information from multiple datasets into a unified view for CCC researchers. By integrating data from various sources, it streamlines access, eliminating the need to search through multiple databases and tables. Designed for analytics, this dataset enhances how researchers view, query, and utilize the CCC Data Warehouse.

Contents

Benefits for Researchers

The Student Journey dataset integrates data from CCCApply and CO-MIS, providing researchers with a more comprehensive and accessible view of student information.

With the CCC Data 3.0.0 release, this new database consolidates datasets and simplifies access permissions, eliminating the need to navigate multiple sources. Designed specifically for analytics, it offers optimized tables that improve query performance and usability.

The Student Journey model is built for adaptability, evolving based on researcher needs. Future enhancements will be guided by user feedback, ensuring the dataset continues to support relevant and impactful research.

Note: Existing databases and tables outside the Student Journey dataset remain unchanged and will continue to be supported.

Scope of this User Guide

This guide provides detailed information on the Student Journey dataset, including an overview of the data model, data sources, and instructions on how to use it, complete with example queries.

For direct access to the Data Warehouse and information on available datasets, refer to the Data Warehouse Direct Connect Access User Guide.

Student Journey Goals

The Student Journey dataset is designed to:

• Improve the usability and accessibility of student data for researchers.

• Establish clear correlations between CO-MIS and CCCApply data, enabling more comprehensive analysis.

Data Model Explanation

The Student Journey data model uses a canonical modeling approach to unify data from multiple sources into a single, structured framework. Instead of maintaining separate, sparsely populated tables for different data sources, this model consolidates attributes into core tables with linked satellite tables, improving data consistency and usability.

Core Tables

These tables serve as the foundation of the model:

• Application

• College

• District

• Needs_and_Interests

Satellite Tables

Each core table is associated with satellite tables that store additional details:

• Education

• Contact_Information

• Personal_Information

• Personal_Sociodemographic

Consult the Student Journey Database Data Dictionary to identify the source, table, description, and example values for each data field.

Description of the Schema

Researchers can query the Student Journey dataset in the CCC Data Warehouse via direct access. The following tables are available:

Core Tables

• ccc__district_vw

• ccc__college_vw

• ccc__needs_and_interests_vw

Application and Student Data Tables

• apply__mis_associative_entity_vw

• apply__application_vw

• apply__college_attended_vw

• apply__contact_information_vw

• apply__education_vw

• apply__needs_and_interests_vw

• apply__personal_information_vw

• apply__personal_sociodemographic_vw

These tables consolidate data from multiple sources and follow a canonicalized structure to ensure consistency and ease of use.

Using the Student Journey Data Model

This section provides guidance on interpreting and querying the tables within the Student Journey canonical model.

Example Query 1: Educational Attainment

To analyze educational attainment, researchers can use the apply__application_vw and apply__education_vwtables, as this attribute is linked to each student’s college application. The following examples demonstrate how to retrieve this information.

_{Figure 1: Application Table}

The application table includes fields common to all application data sources. The source field specifies the origin of each record, as shown in the table below.

_{Figure 2: Application Source}

The application_education table, linked to the source field, provides additional details on educational background and goals, as shown below.

_{Figure 3: Application Education Table}

By querying the apply__education_vw view, researchers can access attributes related to a student's educational background and future goals. This table includes both valid values and unknown values (represented as 0), but does not contain null or blank values.

To determine how many submitted applications include an edu_goal, researchers can perform a left join between the application and application_education tables using the query below.

_{Figure 4: Example Query}

SELECT count(*) as num_applications
  FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
    ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application' 
   AND cae.field = 'hs_edu_level' ;

_{Figure 5: Query Result}

To generate a report on educational attainment by student, use the following query:

_{Figure 6: Example Query}

(
SELECT source, app_id, hs_name, hs_city, hs_state
FROM apply__education_vw 
PIVOT
 ( 
   max(value) FOR field IN ('hs_name','hs_city', 'hs_state')
 )) group by source, app_id;

Example Query 2: Student Sociodemographic Information

To generate a report on student sociodemographic information, use the following query.

_{Figure 7: Example Query}

SELECT ccc_id,max(value)  as   hs_edu_level
  FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
    ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application' 
   AND cae.field = 'hs_edu_level' 
   and ca.tstmp_create > '2025-01-01' group by ccc_id;

This requires data from the apply__application_vw and apply__personal_information_vw tables.

_{Figure 8: apply_personal_Information_vw view}

_{Figure 9: apply_personal_sociodemographic_vw View}

By querying these tables, researchers can access attributes related to a student's sociodemographic status. For example, to determine how many students indicated they were married when completing their application, use the following query.

_{Figure 10: Marriage Information Query}

SELECT pi.app_id,sd.field,sd.value ,pi.value last_name FROM apply__personal_sociodemographic_vw sd inner join
apply__personal_information_vw pi on pi.app_id=sd.app_id where sd.field='married_or_rdp'
and pi.field='lastname';

This query returns the following results:

_{Figure 11: Query Results}

More Connection Info: DW Direct Access User Guide

For details on connecting to the Data Warehouse and accessing current datasets, refer to the Data Warehouse Direct Connect Access User Guide. 

Student Journey Application Database Name and Schema Format

When connecting to the Data Warehouse using the instructions in the Data Warehouse Direct Connect Access User Guide, use the following details for the Student Journey dataset:

• Database Name: Student_Journey

• Schema: public

All tables are stored in the public schema, eliminating the need to navigate to a specific schema based on your MIS code. Data access is automatically managed and protected.

Data Access Validation & Testing for Student Journey

To validate your connection to the Student Journey dataset, follow the instructions in the Data Warehouse Direct Connect Access User Guide and run the following SQL query:

select * from student_journey.public.ccc_district_vw
limit 10;

This query should return 10 rows, confirming successful connectivity.

Colleges are encouraged to perform additional data access tests, such as running:

select * from student_journey.public.apply__mis_associative_entity limit 10;

General Data Warehouse Help

If you need assistance with the CCC Data Warehouse, visit the http://CCCTechnology.info  support channel. There, you can receive help from the Enabling Services Support (ESS) team and collaborate with other colleges and districts on using the CCC Data Warehouse.