03 March 2025

Retain Snapshots of Confirmed SDRs

A SQL database snapshot of all the records submitted in each confirmed SDR is created by ebs. Snapshots provide a method of backing up previously confirmed SDR submissions, so that users can identify changes in data when the SDR is resubmitted. A database management tool (for example: SQL Tools, SQL Server Management Studio and so on) can be used to report on snapshots, and delete historical snapshots.

The following snapshots are created:

  • SDR_SNAPSHOT
  • SDR_COURSE_COMPLETION_SNAPSHOT
  • SDR_COURSE_ENROLMENTS_SNAPSHOT
  • SDR_COURSE_REGISTER_SNAPSHOT
  • SDR_QUAL_COMPLETION_SNAPSHOT
  • SDR_STUDENTS_SNAPSHOT

The following information is stored by ebs against a snapshot:

  • The return type (return_id)
  • The date and time it was created (created_date)
  • The username of the person who submitted the SDR files (created_by)
  • The status of the return (sdr_status) - can be E (extracted), or S (submitted)

    The return status remains at E until submitted, and is then overwritten with a status of S.

SDR snapshot

The ID of the return can be used to search for specific snapshot file content. For example:

  • select * from sdr_course_completion_snapshot where sdr_snapshot_id = ‘141’;

SDR snapshot

When a SDR is run and the snapshots created, the PARENT_ID field (shown in the above graphic) captures the unique identifier of an enrolment row as selected by the relevant SDR view.

The PARENT_ID field stores the following data in these snapshots:

  • SDR_COURSE_COMPLETION_SNAPSHOT - the PEOPLE_UNITS.ID
  • SDR_COURSE_ENROLMENTS_SNAPSHOT - the PEOPLE_UNITS.ID
  • SDR_QUAL_COMPLETION_SNAPSHOT - the ATTAINMENTS.ATTAINMENT_CODE

The creation of SDR snapshots allows you to:

  • Update one or more of the records used to create the last confirmed set of SDR files - the contents of the snapshot do not change if the relevant submission has been confirmed

    Snapshots are overwritten if a previously run submission for the same return type has not been confirmed or submitted.

  • Generate another SDR for a return type that has already been confirmed - the system retains the old snapshot and generates a second one for this new run
  • Confirm the second SDR - the system preserves the corresponding snapshot

Snapshots are retained indefinitely unless deleted manually using SQL commands.