...

An Excel doc outlining the Slate queries and their fields can be found here: https://tufts.app.box.com/s/cbmhofh5q5jw7cjbrijyityi44kq47zq

Design

Components

Bold views are final views
Italic views are cached

Box

...

Cached view
Final view

• Box

  • Slate query listing, field descriptions, race release dates

• Denodo VDP

  • student_lifecycle

    • 01-base_layer

      • 01-connections

        • slate

          • ds_slate_tuf_application

          • ds_slate_tuf_person

          • ds_slate_tuh_application

          • ds_slate_tuh_person

          • ds_slate_tup_application

          • ds_slate_tup_person

          • ds_slate_tuv_application

          • ds_slate_tuv_person

          • ds_slate_tug_application

          • ds_slate_tug_person

          • ds_box_slate_race_release_dates

      • 02-base_views

        • slate

          • bv_slate_tuf_application

          • bv_slate_tuf_person

          • bv_slate_tuh_application

          • bv_slate_tuh_person

          • bv_slate_tup_application

          • bv_slate_tup_person

          • bv_slate_tuv_application

          • bv_slate_tuv_person

          • bv_slate_tug_application

          • bv_slate_tug_person

          • bv_box_slate_race_release_dates

    • 02-transformation

      • slate

        • agg_slate_person_max_race_release_date

        • p_slate_person

        • p_slate_application

    • 03-integration

      • slate

        • u_slate_person

        • u_slate_application

    • 04-interface_fulfillment

      • slate

        • slate_person

        • slate_application

• Denodo Scheduler

  • slate-union-views-cache (runs daily at 2AM)

...

Deans should not be able to see race data for a current or future application cycle in order to prevent intervention in applications based on raced. To prevent this, a race release date (term start date) is associated with each application based on the school and application term. These dates are currently defined in the race-release-dates sheet in the Box spreadsheet linked above. The max race release date is calculated for each person based on their applications and if this date is in the future, their race data values are set to null.

...

The Denodo Scheduler job slate-union-views-cache will cache the data for each Slate union base view on a daily basis at 2AM. This process will load fresh data for each view from all Slate instances and invalidate the old data. This means that any record additions, changes or removals will be reflected in Denodo after this job runs.

While invalidated cache data is not visible to Denodo users, it is retained in the database until the cache clean up job runs on Sunday at 4AM. This job removes all invalidated cache data for all views in Denodo.

To see the refresh dates for all Slate views run the following query:

select 
    viewname, 
    expirationdate as refresh_date 
from cache_content('student_lifecycle') 
where ViewName regexp_ilike 'bv_slate_tu.*_(application|person)';