Date |
|
Authors | |
Status | In ProgressDocumenting |
Summary | ETL failed after an initial attempt at moving it into our new AWS ECS environment |
Impact | Downstream NDW and Hicom processes failed - data in NDW was 1 day out of date (this has now been fixed) |
Non-technical Description
The TIS team moved our ETL from Jenkins a single to AWS environment. Although it ran, it started later than needed and took longer to complete. As a result the downstream NDW processes that begin at 03.45 had no fresh data to work with, and the Hicom pull into Leave Manager could not pull live data.
...
Moving the ETL from Jenkins to ECS, not starting it at the right time and not optimising the speed with which it completed to ensure had it started at the right time, it would have completed in time for the downstream NDW and Hicom processes to be able to use current datameant the job didn’t run within required constraints (completed by the required time).
...
Detection
No alerts notifications in the #monitoring-ndw channel (alerts had been moved to . Job notifications appeared in the #monitoring-prod channel).
Alerts Notification in the #monitoring-prod channel confirmed the ETL ran at the wrong time and took too long to complete.
...
Re-work the new ECS ETLs to start after the TIS Sync jobs on which they depend have finished (i.e. after 02.40), and complete before the NDW and Hicom processes that depend on the ETLs begin (i.e. before 03.45).
Put a process in place to ensure the successful running of TIS NDW ETLs, after 0245 and before 03:45 each morning, over the weekend/Monday morning.
...
Timeline
: 03:15 - No alert notification in the #monitoring-ndw channel at the expected time (c.03.15 - 03:35)
: 04:00 - An alert Notification in the #monitoring-prod channel of ETL success but in the wrong time slot (c.04.00 - 05:30)
...
: 10:20-11:30 - Discussions in TIS team to identify exactly what had happened/been missed and what to do to resolve it. This resulted in an agreement to reschedule the AWS ECS job to start shortly after 02:40 (02:45 perhaps), and optimise the performance of the job to ensure it completed before 03:45
: 11:55 04 - Pepe restarted started the job from Jenkins instead of AWS ECS but this failed and was restarted at 11:55.
: 12:00 - Sarah Krause confirms that the London loads keep trying until they can grab new data, so were able to process data when the new ETL completed (c.05:30).
...
Focused on getting the ETLs to work in ECS.
Verification in preprod did not cover all aspects: Notifications, constraints on execution time.
Misalignment of time zones for different Lack of focus on the scheduling of the ETLs and the speed with which they needed to be processed in order to not impact dependencies.
...
Be carefully when modifying ETLs.:
Refer to the image to the right when modifying in future (and keep that image up to date). Keep the overall ETL Timings (re-draft - for discussion) TIS Scheduled Jobs & Timings page up to date |
|
.
Action Items
Action Items | Owner | Status |
|---|---|---|
Adjusted timing and resource allocation for TIS NDW ETLs, after 0245 and before 03:45 each morning. | Done. Starts at 3AM BST (rather than 3AM GMT) and configuration has been adjusted to bring duration down. | |
Update the ETL timings page. Use wider list of ETLs, i.e. image with swim-lanes above. | Page updated. Diagram with swimlanes/showing full dependency graph still needs to be included. | |