Purpose

The terradotta.StudyAbroad view provides comprehensive student information specifically formatted for Terradotta's study abroad integration. This view combines data from multiple sources to create a detailed student profile including academic information, contact details, and custom fields required for study abroad program management.

View Structure

Source System Mapping

Data Transformation Rules

1. Date Formatting

   DOB = CASE
     WHEN B.DOB IS NULL THEN ''
     ELSE FORMAT(CAST(B.DOB AS DATE), 'MM/dd/yyyy')
   END
   

2. Student Type Determination

   CUSTOM2 = CASE
     WHEN B.INTERNATIONAL_STUDENT = 'Y' THEN 'International'
     ELSE 'Domestic'
   END
   

3. Onsite Status

   CUSTOM3 = CASE
     WHEN B.ONSITE_CREDITS > 0 THEN 'Y'
     ELSE 'N'
   END
   

Data Quality Rules

1. Required Fields

   • UUUID
   • LAST_NAME
   • FIRST_NAME
   • EMAIL
   • STUDENT_ID
   • CLASS_LEVEL
   • ACADEMIC_LEVEL

2. Validation Rules

   • Email must be valid
   • Student ID must be unique
   • Academic information must be current
   • Contact information must be complete

3. Business Rules

   • Only active students included
   • Valid academic program required
   • Current advisor information needed
   • Complete address information required

Usage Notes

1. Integration Considerations

   • Used for study abroad program management
   • Supports student eligibility checks
   • Includes academic and contact information
   • Custom fields for program-specific data

2. Performance Considerations

   • Multiple CTEs for data organization
   • Complex joins for academic information
   • Current status filtering
   • Efficient null handling

3. Maintenance

   • Monitor academic program changes
   • Track advisor assignments
   • Validate contact information
   • Update custom field mappings