System and method for traceable push-based ETL pipeline and monitoring of data quality in ETL environments

1 . A system for use with extract, transform, load (ETL) environments, comprising:

a computer comprising a microprocessor and a data integration service provided thereon, that operates to read or ingest data from object storage and push it to a data warehouse instance for further processing using an extract, transform, load (ETL) push-based pipeline that operates to extract data from the object storage, transform the data as needed, and load the transformed data into a data store, for subsequent usage;

wherein the pipeline is associated with one or more jobs that comprises one or more tasks, wherein a task is associated with a program or process operating within or as part of the pipeline and serving a particular data function; and

an orchestrator that operates over multiple layers, including:

a data layer that operates to trace one or more task's impact on a data level; and

a task layer that operates to define task sequence and dependency and to trigger downstream jobs;

wherein the orchestrator operates to build job orchestration rules and provide information to a dashboard;

wherein the system maintains a control table, which operates as a table of tables to trace task performance and data and table changes as the pipeline executes, including:

a data change table that traces data change state and time in data ingestion or data processing components that operate within or as part of the pipeline;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.

2 . The system of claim 1 , wherein:

each task is associated with a program or process operating within or as part of the pipeline and serving a particular data function that includes one of data loading, processing, or validation;

each job comprises one or more tasks wherein different jobs run in sequence or parallel, based on configuration;

wherein as jobs execute, the system operates to trace each table change history, over several dimensions including task, data, role, and time, in a data store such as a database or data warehouse.

3 . The system of claim 1 , wherein the data layer operates to trace each of one or more task's impact on a data level, including one or more source data change, data flow state, table change, or schema change.

4 . The system of claim 1 , wherein the system includes:

a schema change table that traces historical schema change for a source table;

a data change table that traces data change state and time in the various components that operate within or as part of the pipeline, including data ingestion or data processing components, to indicate a success, partial, or failure;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.

5 . The system of claim 1 , comprising, during processing of the pipeline, collecting information for task and data, through the control table or table-of-tables, wherein the task state table and dependency table are bound with the data change table in a same time window, to provide a real-time assessment of the pipeline.

6 . The system of claim 1 , wherein the system comprises the control table or table-of-tables by which the system can trace each task performance, and detailed data/table changes, and identify a root cause quickly when problems arise or in troubleshooting.

7 . The system of claim 1 , comprising a user interface or dashboard for monitoring and displaying pipeline health, and data quality monitoring, which automatically tracks each job performance in the pipeline statistically.

8 . The system of claim 1 , comprising an orchestrator that learns from pipeline health data and task/table change, and operates to identify repeated pipeline issues with root causes.

9 . The system of claim 1 , wherein the system is provided within a cloud computing environment that operates on a cloud computing infrastructure comprising one or more cloud interfaces or other application program interfaces that provide access to shared cloud resources.

10 . A method for use with extract, transform, load (ETL) environments, comprising:

providing, at a computer comprising a microprocessor, a data integration service that operates to read or ingest data from object storage and push it to a data warehouse instance for further processing using an extract, transform, load (ETL) push-based pipeline that operates to extract data from the object storage, transform the data as needed, and load the transformed data into a data store, for subsequent usage;

wherein the pipeline is associated with one or more jobs that comprises one or more tasks, wherein a task is associated with a program or process operating within or as part of the pipeline and serving a particular data function;

orchestrating the pipeline over multiple layers, including:

a data layer that operates to trace one or more task's impact on a data level; and

a task layer that operates to define task sequence and dependency and to trigger downstream jobs;

wherein the orchestrator operates to build job orchestration rules and provide information to a dashboard; and

maintaining a control table, which operates as a table of tables to trace task performance and data and table changes as the pipeline executes, including:

a data change table that traces data change state and time in data ingestion or data processing components that operate within or as part of the pipeline;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.

11 . The method of claim 10 , wherein:

each task is associated with a program or process operating within or as part of the pipeline and serving a particular data function that includes one of data loading, processing, or validation;

each job comprises one or more tasks wherein different jobs run in sequence or parallel, based on configuration;

wherein as jobs execute, the system operates to trace each table change history, over several dimensions including task, data, role, and time, in a data store such as a database or data warehouse.

12 . The method of claim 10 , wherein the data layer operates to trace each of one or more task's impact on a data level, including one or more source data change, data flow state, table change, or schema change.

13 . The method of claim 10 , wherein the system includes:

a schema change table that traces historical schema change for a source table;

a data change table that traces data change state and time in the various components that operate within or as part of the pipeline, including data ingestion or data processing components, to indicate a success, partial, or failure;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.

14 . The method of claim 10 , comprising, during processing of the pipeline, collecting information for task and data, through the control table or table-of-tables, wherein the task state table and dependency table are bound with the data change table in a same time window, to provide a real-time assessment of the pipeline.

15 . The method of claim 10 , wherein the system comprises the control table or table-of-tables by which the system can trace each task performance, and detailed data/table changes, and identify a root cause quickly when problems arise or in troubleshooting.

16 . The method of claim 10 , comprising a user interface or dashboard for monitoring and displaying pipeline health, and data quality monitoring, which automatically tracks each job performance in the pipeline statistically.

17 . The method of claim 10 , comprising an orchestrator that learns from pipeline health data and task/table change, and operates to identify repeated pipeline issues with root causes.

18 . The method of claim 10 , wherein the method is performed within a cloud computing environment that operates on a cloud computing infrastructure comprising one or more cloud interfaces or other application program interfaces that provide access to shared cloud resources.

19 . A non-transitory computer readable storage medium having instructions thereon, which when read and executed by a computer cause the computer to perform a method comprising:

providing, at a computer comprising a microprocessor, a data integration service that operates to read or ingest data from object storage and push it to a data warehouse instance for further processing using an extract, transform, load (ETL) push-based pipeline that operates to extract data from the object storage, transform the data as needed, and load the transformed data into a data store, for subsequent usage;

wherein the pipeline is associated with one or more jobs that comprises one or more tasks, wherein a task is associated with a program or process operating within or as part of the pipeline and serving a particular data function;

orchestrating the pipeline over multiple layers, including:

a data layer that operates to trace one or more task's impact on a data level; and

a task layer that operates to define task sequence and dependency and to trigger downstream jobs;

wherein the orchestrator operates to build job orchestration rules and provide information to a dashboard; and

maintaining a control table, which operates as a table of tables to trace task performance and data and table changes as the pipeline executes, including:

a data change table that traces data change state and time in data ingestion or data processing components that operate within or as part of the pipeline;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.

20 . The non-transitory computer readable storage medium of claim 19 , wherein the system includes:

a schema change table that traces historical schema change for a source table;

a data change table that traces data change state and time in the various components that operate within or as part of the pipeline, including data ingestion or data processing components, to indicate a success, partial, or failure;

a task state table that traces task state and time associated with a job; and

a dependency table that defines upstream and downstream dependency between tasks.