The below table is an extract of all the jobs with narratives of their purposes and what time they run in BST (during summer) and UTC (during winter) in the United Kingdom. The table below has been last validated on 26/07/2021.
(IN PROGRES….) Timeline showing all schedules in BST and where they are triggered. Where the jobs are in UTC, their BST equivalent were added to the timeline. E.g. NDW Merge ETL runs at 2.30 during BST. All schedules are to be validated with devs/ndw/ops.
Purpose and Timing of ETL's
The below table is an extract of all the above jobs with narratives of their purposes and what time they run in BST (during summer) and UTC (during winter) in the United Kingdom. The table below has been last validated on 26/07/2021.
Job Name | Purpose of the job | Job type | Start time (BST) | End Time (BST) | Start time (UTC) | End time (UTC) | Start time (BST) - 26/07/21 | End Time (BST) - 26/07/21 | Start time (UTC) - 26/07/21 | End time (UTC) – 26/07/21 | Where notified on slack? | Notes |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Elasticsearch-snapshot-prod | ?? | Jenkins | 00:58 | 01:02 | 23:58 | 12:02 | #Jenkins_Azure | Check if this is still used John Simmons (Deactivated) / Liban Hirey (Unlicensed) | ||||
GMC Synch Prod | Runs the GMC ETL process on prod. Updates the Revalidation and Concerns Elastic Search indexes with data received from GMC. Updates Profile Traineeprofile table with doctors TISIds and GMC numbers received from GMC. | Jenkins | 01:05 | 01:10 | 00:05 | 00:10 |
|
|
|
| #monitoring | |
Intrepid-Reval-ETL-all-prod | Runs the Reval ETL process on prod. It used to pull data from Intrepid but not anymore since TIS switchover. It enriches the Revalidation and Connection Discrepancies Elastic Search indexes with data from TCS and Assessments service for the TISIds. | Jenkins | 02:00 | 02:17 | 02:00 | 02:17 |
|
|
|
| ? | No Notification |
TIS-NDW-ETL-cron-prod-to-prod | A spring-batch application which runs nightly to extract data from TIS, denormalise to the National Data Warehouse (NDW) schema, and write to TIS_NDW_INTEREIM_PROD interim database for the NDW PROD to import from.?? | AWS ECS
| 03:00 | 03:30 | 02:00 | 02:30 |
|
|
|
| #monitoring-prod Move to -> #monitoring-ndw | |
TIS-NDW-ETL-cron-stage-to-uat | A spring-batch application which runs nightly to extract data from TIS, denormalise to the National Data Warehouse (NDW) schema, and write to TIS_NDW_INTERIM_STAGE interim database for the NDW UAT to import from.?? |
AWS ECS | 02:00 | 03:11 | 01:00 | 02:11 | 03:40 | 04:35 | 02:40 | 03:35 | To do in #Monitoring-ndw | To do |
ndw-etl-nimdta | A spring-batch application which runs nightly to extract data from TIS, denormalise to the National Data Warehouse (NDW) schema, and write to TIS Northern Ireland. (Interim database for NDW) | AWS ECS | 03:30 | 03:35 | 02:30 | 02:35 | #monitoring-ndw | New | ||||
TIS-ESR-ETL-cron-notification-daily-load | Creates ESR notifications from TCS service, ready to be exported to ESR. | Jenkins | 06:00 | 06:02 | 06:00 | 06:02 | #esr_operations | Deprecated | ||||
TIS-ESR-ETL-cron-applicant-load | Runs the applicant-load part of the ETL. | Jenkins | 15:30 | 15:32 | 15:30 | 15:32 | #esr_operations | Deprecated | ||||
TIS-ESR-ETL-cron-applicant-export | Runs the applicant-export of the ESR ETL | Jenkins | 16:00 | 16:02 | 16:00 | 16:02 | #esr_operations | Deprecated | ||||
TIS-ESR-ETL-cron-notification-daily-export | Runs the notifications-daily-export. Creates CSV files on cloud storage from TCS (EsrNotifications) and ESR (PositionReconciliation and PositionInformation) logging those records in ESR (EsrNotificationDetailsRecord) | Jenkins | 17:30 | 17:32 | 17:30 | 17:32 | #esr_operations | Deprecated | ||||
TIS-ESR-ETL-cron-apc-import | Runs the APC confirmations import | Jenkins | 20:00 | 20:02 | 20:00 | 20:02 | #esr_operations | Deprecated | ||||
N3 ESR-FTP RMT files download to Azure | Downloads RMT Files from ESR FTP Out Folder to TIS Azure Inbound tisesrprod account | N3 FTP-Sync | 14:45 | 14:47 | 14:45 | 14:47 | #esr_operations | Deprecated | ||||
N3 ESR-FTP RMT files download to Azure | Downloads RMT Files from ESR FTP Out Folder to TIS Azure Inbound tisesrprod account | N3 FTP-Sync | 15:15 | 15:17 | 15:15 | 15:17 | #esr_operations | Deprecated | ||||
Files Upload from Azure to N3 ESR-FTP | APP, RTC, DNC, DNF files uploaded from Azure to ESR FTP In folder on N3 | N3 FTP-Sync | 18:00 | 18:02 | 18:00 | 18:02 | #esr_operations | Deprecated | ||||
N3 ESR-FTP APC files download to Azure | Download APC files from ESR-FTP Out folder to TIS Azure tisesrprod Inbound | N3 FTP-Sync | 19:00 | 19:02 | 19:00 | 19:02 | #esr_operations | Deprecated | ||||
NDW Merge ETL (UAT and PROD?) | Runs the MSSQL job on NDW to merge TIS data to NDW databases.UAT then PROD. ?? | NDW MSSQL | 02:30 | 03:30 | 01:30 | 02:30 | N/A | Ask Pavel | ||||
NDW-Hicom ETL ??? (SQL07) | Populating TIS data (merged from the NDW Merge ETL earlier) to Intrepid database to send to Hicom. ?? | NDW MSSQL | 05:00 | 07:00 | 04:00 | 06:00 | 04:00 | Before 04:30 | 04:00 | Before 04:30 | N/A | |
HEE Person Owner Rebuild | Rebuilds Person Ownership table in TCS. | TIS SYNC | 00:05 | 00:07 | 00:05 | 00:07 | 01:05 | 01:05 | 00:05 | 00:05 | #monitoring-prod | |
NIMDTA Person Owner Rebuild | Rebuilds Person Ownership table in TCS. | TIS SYNC | 01:05 | 01:05 | 00:05 | 00:05 | #monitoring-prod | New | ||||
HEE PersonPlacement EmployingBodyTrust | Populates the PersonTrust table with the Employing body trust of the placement in TCS | TIS SYNC | 00:10 | 00:45 | 00:10 | 00:45 | 01:09 | 01:39 | 00:09 | 00:39 | #monitoring-prod | |
NIMDTA PersonPlacement EmployingBodyTrust | Populates the PersonTrust table with the Employing body trust of the placement in TCS | TIS SYNC | 01:09 | 01:09 | 00:09 | 00:09 | #monitoring-prod | New | ||||
HEE PersonPlacement TrainingBodyTrust | Populates the PersonTrust table with the Training body trust of the placement in TCS | TIS SYNC | 00:45 | 01:07 | 00:45 | 01:07 | 01:29 | 02:00 | 00:29 | 01:00 | #monitoring-prod | |
NIMDTA PersonPlacement TrainingBodyTrust | Populates the PersonTrust table with the Training body trust of the placement in TCS | TIS SYNC | 01:29 | 01:29 | 00:29 | 00:29 | #monitoring-prod | New | ||||
HEE Post EmployingBodyTrust | Populates the PostTrust table with the employing body in TCS | TIS SYNC | 01:00 | 01:13 | 01:00 | 01:13 | 02:09 | 02:10 | 01:09 | 01:10 | #monitoring-prod | |
NI Post EmployingBodyTrust | Populates the PostTrust table with the employing body in TCS | TIS SYNC | 02:09 | 02:09 | 01:09 | 01:09 | #monitoring-prod | New | ||||
HEE Post TrainingBodyTrust | Populates the PostTrust table with the Training body in TCS | TIS SYNC | 01:20 | 01:22 | 01:20 | 01:22 | 02:19 | 02:20 | 01:19 | 01:20 | #monitoring-prod | |
NIMDTA Post TrainingBodyTrust | Populates the PostTrust table with the Training body in TCS | TIS SYNC | 02:19 | 02:19 | 01:19 | 01:19 | #monitoring-prod | |||||
HEE Person Elasticsearch Synch | Populates the Elasticsearch clusters for Person? | TIS SYNC | 01:30 | 01:36 | 01:30 | 01:36 | 02:29 | 02:40 | 01:29 | 01:40 | #monitoring-prod | Update the Slack notification Person Sync → Person Elastic Search |
NIMDTA Person Elasticsearch Synch | Populates the Elasticsearch clusters for Person? | TIS SYNC | 02:29 | 02:29 | 01:29 | 01:29 | #monitoring-prod | Update the Slack notification Person Sync → Person Elastic Search | ||||
ESR Inbound Data Reader Notifier | ESR sends us APC, DCC and RMC/F files. | real time | real time | real time | real time | #monitoring-esr | New | |||||
ESRExporter Stage > PendingAppExport | Creates CSV files for APP Files and uploads to ESR SFTP | 14:00 | 14:10 | 13:00 | 13:10 | #sentry-esr | Add slack notification to #monitoring-esr. Every 10 minutes after the files are picked up and uploaded to ESR. | |||||
ESRExporter Stage > PendingNotificationExport | Creates CSV files for DNC Files and uploads to to ESR SFTP | 15:00 | 15:10 | 14:00 | 14:10 | #sentry-esr | Add slack notification #monitoring-esr Every 10 minutes after the files are picked up and uploaded to ESR. | |||||
ESRExporter Prod> PendingAppExport | Creates CSV files for APP Files and uploads to ESR SFTP | 14:00 | 14:10 | 13:00 | 13:10 | #sentry-esr | Add slack notification #monitoring-esr. Every 10 minutes after the files are picked up and uploaded to ESR. | |||||
ESRExporter Prod> PendingNotificationExport | Creates CSV files for DNC Files and uploads to to ESR SFTP | 15:00 | 15:10 | 14:00 | 14:10 | #sentry-esr | Add slack notification to #monitoring-esr Every 10 minutes after the files are picked up and uploaded to ESR. | |||||
ESRDataWriter Stage | Matches ESR Assignments and their Positions to TIS PersonID's and posts/placements. This then updates Personal details. | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 15:00 (UTC/BST) from ESR. Then every 10 mins after is picked up. | |||||
ESRDataWriter Prod | Matches ESR Assignments and their Positions to TIS PersonID's and posts/placements. This then updates Personal details. | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 15:00 (UTC/BST) from ESR. Then every 10 mins after is picked up. Only structural issues triggered to Sentry in #sentry | |||||
ESRReconciliation Stage | Matches ESR Positions to TIS Posts. | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 15:00 (UTC/BST) from ESR. Then every 10 mins after is picked up. | |||||
ESRReconciliation Prod | Matches ESR Positions to TIS Posts. | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 15:00 (UTC/BST) from ESR. Then every 10 mins after is picked up. |
Add Comment