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 timeline below has been last was validated on 26/07/2021.
(IN PROGRES….) Timeline NOTE: Timeline below 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 on this page are to be validated with devs/ndw/ops.
...
kept up-to-date by TIS devs & Ops and NDW team whenever there is a change.
...
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 was last validated and updated on 26/07/2021.
Job Name | HEE or NIMDTA | 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 | HEE | ?? | 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 | HEE | 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 | HEE | 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 NotificationNo Notification Add slack notification to #monitoring? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TIS-NDW-ETL-cron-prod-to-prod | HEE | 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 |
|
|
|
Move to -> #monitoring-ndw | #monitoring-prod Move to -> #monitoring-ndw | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
TIS-NDW-ETL-cron-stage-to-uat | HEE | 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-ndwTo do | Add slack notification to | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ndw-etl-nimdta | 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 | HEE NDW Merge ETL (UAT and PROD?) | HEE | 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 - James Harris | |||||||||||||||||||||||||||||||||||||||||||||||||
NIMDTA NDW Merge ETL | NIMDTA | NDW MSSQL | Is this relevant- James Harris | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
NDW-Hicom ETL ??? (SQL07) | HEE | 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 | HEE | 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 | NIMDTA | Rebuilds Person Ownership table in TCS. | TIS SYNC | 01:05 | 01:05 | 00:05 | 00:05 | #monitoring-prod | New | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
HEE PersonPlacement EmployingBodyTrust | HEE | 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 | NIMDTA | 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 | HEE | Populates the PersonTrust table with the Training body trust of the placement in TCS | TIS SYNC00:45 | 01:07 | 00:45 | 01:07 | 01:29 | 02:00 | 00:29 | 01:00 | #monitoring-prod | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
NIMDTA PersonPlacement TrainingBodyTrust | NIMDTA | 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 | HEE | 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 NIMDTA Post EmployingBodyTrust | NIMDTA | 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 | HEE | 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 | NIMDTA | 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 | HEE | 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 | NIMDTA | 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 | HEE | ESR sends us APC, DCC and RMC/F files. | RabbitMQ | real time | real time | real time | real time | #monitoring-esr | New Usually at 14:55 BST from ESR | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESR Inbound Data Reader Notifier | HEE | ESR sends us APC files. | RabbitMQ | real time | real time | real time | real time | #monitoring-esr | New Usually at 16:25 BST from ESR | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESR Inbound Data Reader Notifier | HEE | ESR sends us DCC files. | RabbitMQ | real time | real time | real time | real time | #monitoring-esr | New Usually at 16:25 BST from ESR | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESRExporter Stage > PendingAppExport | HEE | Creates CSV files for APP Files and uploads to ESR SFTP | RabbitMQ/SFTP Sync | 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 | HEE | Creates CSV files for DNC Files and uploads to to ESR SFTP | RabbitMQ/SFTP Sync | 15:00 | 15:10 | 14:00 | 14:10 | #sentry-esr | Add slack notification #monitoring-esrEvery 10 minutes after the files are picked up and uploaded to ESR. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESRExporter Prod> PendingAppExport | HEE | Creates CSV files for APP Files and uploads to ESR SFTP | RabbitMQ/SFTP Sync | 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 | HEE | Creates CSV files for DNC Files and uploads to to ESR SFTP | RabbitMQ/SFTP Sync | 15:00 | 15:10 | 14:00 | 14:10 | #sentry-esr | Add slack notification to #monitoring-esrEvery 10 minutes after the files are picked up and uploaded to ESR. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESRDataWriter Stage | HEE | Matches ESR Assignments and their Positions to TIS PersonID's and posts/placements. This then updates Personal details. | RabbitMQ | 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 | HEE | Matches ESR Assignments and their Positions to TIS PersonID's and posts/placements. This then updates Personal details. | RabbitMQ | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 1514:00 55 (UTC/BST) from ESR. Then every 10 mins after is picked up. Only structural issues triggered to Sentry in #sentry | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESRReconciliation Stage | HEE | Matches ESR Positions to TIS Posts. | RabbitMQ | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 1514:00 55 (UTC/BST) from ESR. Then every 10 mins after is picked up. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
ESRReconciliation Prod | HEE | Matches ESR Positions to TIS Posts. | RabbitMQ | real time | real time | real time | real time | #sentry-esr | Add slack notification #monitoring-esr (combined/single) Usually start around 1514:00 55 (UTC/BST) from ESR. Then every 10 mins after is picked up. |
...