Monitoring and alerting platform for extract, transform, and load jobs

Patent metadata

Field	Value
Publication number	US-2022222266-A1
Application number	US-202117248187-A
Country	US
Kind code	A1
Filing date	Jan 13, 2021
Priority date	Jan 13, 2021
Publication date	Jul 14, 2022
Grant date	—

Abstract

In some implementations, a monitoring device may obtain information related to one or more extract, transform, and load (ETL) jobs scheduled in an ETL system. The monitoring device may generate ETL job metrics that include status information, timing information, and data volume information associated with one or more constituent tasks associated with the one or more ETL jobs, wherein the ETL job metrics include metrics related to extracting data records from a data source, transforming the data records into a target format, and/or loading the data records in the target format into a data sink. The monitoring device may enable capabilities to create or interact with one or more dashboards to visualize the ETL job metrics via a workspace accessible to one or more client devices. The monitoring device may invoke a messaging service to publish one or more notifications associated with the ETL job metrics via the workspace.

First claim

What is claimed is:

1 . A system for monitoring extract, transform, and load (ETL) jobs, the system comprising:
one or more memories; and one or more processors, communicatively coupled to the one or more memories, configured to:
obtain information related to one or more ETL jobs scheduled in an ETL system,
wherein the one or more ETL jobs are each associated with constituent tasks to extract data records from a data source, transform the data records into a target format, and load the data records in the target format into a data sink;

generate, for the one or more ETL jobs scheduled in the ETL system, ETL job metrics that include status information, timing information, and data volume information associated with extracting, transforming, or loading data records;
identify an anomaly associated with at least one ETL job, among the one or more ETL jobs scheduled in the ETL system, based on the ETL job metrics,
wherein the anomaly is identified based on the status information, the timing information, or the data volume information associated with the at least one ETL job satisfying one or more conditions; and

invoke a messaging service to generate, based on the anomaly, an alert that includes information related to the anomaly associated with the at least one ETL job.

2 . The system of claim 1 , wherein the one or more conditions are satisfied based on the status information indicating that the at least one ETL job has failed.

3 . The system of claim 1 , wherein the one or more conditions are satisfied based on the status information indicating that the at least one ETL job is queued and the timing information associated with the at least one ETL job indicating a queue time that satisfies a threshold.

4 . The system of claim 1 , wherein the one or more conditions are satisfied based on the data volume information indicating that the at least one ETL job is associated with a quantity of data records that satisfies a threshold.

5 . The system of claim 1 , wherein the ETL job metrics further include historical trends related to changes over time in the timing information or the data volume information associated with the at least one ETL job or the constituent tasks associated with the at least one ETL job.

6 . The system of claim 5 , wherein the one or more conditions are satisfied based on the timing information or the data volume information associated with the at least one ETL job or the constituent tasks associated with the at least one ETL job deviating from the historical trends.

7 . The system of claim 1 , wherein the one or more processors are configured to:
invoke the messaging service to publish information associated with the ETL job metrics to a workspace accessible to one or more client devices; and enable, via the workspace, a capability to create or interact with one or more dashboards to visualize data analytics associated with the ETL job metrics.

8 . The system of claim 1 , wherein the ETL job metrics include granular metrics for the one or more ETL jobs, an overall job set that includes the one or more ETL jobs, the constituent tasks associated with the one or more ETL jobs, and one or more ETL jobs nested within an ETL job.

9 . The system of claim 1 , wherein:
the status information indicates whether an ETL job or a constituent task associated with an ETL job has a queued status, a running status, a completed status, or a failed status, the timing information indicates one or more of a queue time, an execution time, a start time, or completion time for an ETL job or a constituent task, and the data volume information indicates a quantity of data records to be extracted, transformed, or loaded or a quantity of data records that have been extracted, transformed, or loaded for an ETL job or a constituent task associated with an ETL job.

10 . The system of claim 1 , wherein the one or more processors are configured to:
obtain authenticated access to one or more of the ETL system, the data sink, or a data analytics service using one or more credentials obtained from a secure credential device.

11 . A method for monitoring extract, transform, and load (ETL) jobs, comprising:
obtaining, by a monitoring device, information related to one or more ETL jobs scheduled in an ETL system; generating, by the monitoring device, ETL job metrics that include status information, timing information, and data volume information associated with one or more constituent tasks associated with the one or more ETL jobs scheduled in the ETL system,
wherein the ETL job metrics include metrics related to one or more of extracting data records from a data source, transforming the data records into a target format, or loading the data records in the target format into a data sink;
enabling, by the monitoring device, a capability to create or interact with one or more dashboards to visualize data analytics associated with the ETL job metrics via a workspace accessible to one or more client devices; and invoking, by the monitoring device, a messaging service to publish one or more notifications associated with the ETL job metrics via the workspace.

12 . The method of claim 11 , wherein:
the status information indicates whether an ETL job or a constituent task associated with an ETL job has a queued status, a running status, a completed status, or a failed status, the timing information indicates one or more of a queue time, an execution time, a start time, or completion time for an ETL job or a constituent task, and the data volume information indicates a quantity of data records to be extracted, transformed, or loaded or a quantity of data records that have been extracted, transformed, or loaded for an ETL job or a constituent task associated with an ETL job.

13 . The method of claim 12 , wherein the one or more notifications indicate that the status information for an ETL job or a constituent task associated with an ETL job has changed to or from the queued status, the running status, the completed status, or the failed status.

14 . The method of claim 12 , wherein the one or more notifications indicate that the queue time or the execution time for an ETL job or a constituent task associated with an ETL job having the queued status or the running status satisfies a threshold.

15 . The method of claim 12 , wherein the one or more notifications include at least one notification that is published based on the quantity of data records associated with an ETL job or a constituent task associated with an ETL job satisfying a threshold.

16 . The method of claim 11 , wherein the ETL job metrics further include historical trends related to changes over time in timing information or data volume information associated with an ETL job or the constituent tasks associated with an ETL job.

17 . A non-transitory computer-readable medium storing a set of instructions, the set of instructions comprising:
one or more instructions that, when executed by one or more processors of a device, cause the device to:
obtain information related to one or more ETL jobs scheduled in an ETL system;
generate ETL job metrics that include status information, timing information, and data volume information associated with one or more constituent tasks associated with the one or more ETL job scheduled in the ETL system,
wherein the status information indicates whether an ETL job or a constituent task associated with an ETL job has a queued status, a running status, a completed status, or a failed status,
wherein the time information indicates one or more of a queue ti

Assignees

• Capital One Services Llc

Inventors

• Makumbi Alex
• Stevens Andrew

Classifications

• G06F21/54 Primary

  by adding security routines or objects to programs · CPC title

• G06F16/258

  Data format conversion from or to a database · CPC title

• G06F16/254 Primary

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F21/31

  User authentication · CPC title