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 timeline below was validated on 26/07/2021.
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. All schedules on this page are to be 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 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) | 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 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 | #monitoring-prod 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 | 03:40 | 04:35 | 02:40 | 03:35 | To do in #Monitoring-ndw | 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 |
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 | 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 | 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 | 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 SYNC | 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 | 02:09 | 02:10 | 01:09 | 01:10 | #monitoring-prod | |
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 | 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 | 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 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. |
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-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. |
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-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 (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 14:55 (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 14:55 (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 14:55 (BST) from ESR. Then every 10 mins after is picked up. |
Add Comment