# US 12,182,156 B2 · Managing extract, transform and load systems > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 12,182,156 B2 - Document ID: US12182156B2 - Status: Granted - Kind: B2 - Application number: 18/473,356 - Filed: 2023-09-25 - Published: 2024-01-11 - Granted: 2024-12-31 - Earliest claimed: 2021-06-29 - Assignees: International Business Machines Corporation - Applicants: International Business Machines Corporation - Inventors: Chengxuan Xing; Doina Liliana Klinger; Alexander Robert Wood; Tom Soal - Web page: https://patentgrep.com/patent/US12182156B2 ## CPC - G06F 16/254: {Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses} - G06F 11/1402: {Saving, restoring, recovering or retrying} - G06F 11/3414: {Workload generation, e.g. scripts, playback} - G06F 16/215: Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors - G06F 11/004: {Error avoidance (G06F11/07 and subgroups take precedence)} - G06F 11/3433: {for load management (allocation of a server based on load conditions G06F9/505; load rebalancing G06F9/5083; redistributing the load in a network by a load balancer H04L67/1029)} - G06F 2201/80: Database-specific techniques - G06F 2201/81: Threshold - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract An approach to implement an extract, transform and load system, a queue is provided for holding units of data between extraction and transformation. When units of data suffer a rate limit error in the load phase, they are requeued so they can be resubmitted for transformation. The contents of the queue are monitored and, if too many requeued units of data are detected in the queue, then this is taken as an indicator of an unacceptable number of rate limit errors and active pacing management is triggered. A retry schedule is defined for the requeued units of data. Extraction is temporarily halted to allow the requeued units of data to be retransformed without more units of data queuing up. Then, after the suspension is lifted, a pacing delay is inserted between subsequent extract events to avoid the same load phase bottleneck recurring. ## Claims ### Claim 1 An extract, transform and load (ETL) engine operable to perform an ETL method with extract, transform and load phases, the ETL engine comprising: an extract engine operable to perform an extract phase by extracting units of data from a source system; a queue configured to receive units of data from the extract engine; a processing engine operable to perform the transform and load phases by receiving units of data from the queue, transforming and loading the units of data to a target system; a pace manager operable to monitor the queue, such that, if the pace manager detects that there are more than a threshold number of requeued units of data in the queue, then the pace manager is operable to impose at least one restriction on the extract phase; and the pace manager being further operable to monitor a history of units of data held in the queue and to delete one or more of the units of data based on their history, respectively, when the units of data fail a threshold number of times. ### Claim 2 (depends on 1) The ETL engine of claim 1, wherein the retry schedule is set so as to avoid rate limit errors occurring again in the load phase after the units of data have been reprocessed by the processing engine. ### Claim 3 (depends on 2) The ETL engine of claim 2, wherein the at least one extract restriction includes imposing a pause on extraction for an amount of time set according to an expected amount of time needed to reprocess the requeued units of data taking account of the retry schedule. ### Claim 4 (depends on 1) The ETL engine of claim 1, wherein the at least one extract restriction includes introducing a pacing limitation to limit how many units of data are extracted per unit time. ### Claim 5 (depends on 4) The ETL engine of claim 4, wherein the at least one extract restriction further includes reducing size of the units of data. ### Claim 6 (depends on 1) The ETL engine of claim 1, wherein the threshold number takes account of how many requeued units of data there are compared with a total number of units of data held in the queue. ### Claim 7 (depends on 1) The ETL engine of claim 1, wherein are received from the target system. ### Claim 8 (depends on 1) The ETL engine of claim 1, wherein a load phase rate limit error occurs when more than a threshold number of transformed units of data are loaded. ### Claim 9 (depends on 1) The ETL engine of claim 1, wherein the pace manager is further operable to vary the at least one extract restriction responsive to monitoring the queue in respect of changes in the number of requeued units of data in the queue. ### Claim 10 (depends on 1) The ETL engine of claim 1, wherein the pace manager is further operable to monitor at least one history attribute of the units of data held in the queue and to delete one or more of the units of data based on their history attributes based on a retry schedule for reprocessing the requeued units of data by the processing engine. ### Claim 11 (depends on 10) The ETL engine of claim 10, wherein at least one history attribute is age of a data unit in the ETL engine, wherein a unit of data is deleted if it has an age greater than a maximum age limit. ### Claim 12 (depends on 10) The ETL engine of claim 10, wherein at least one history attribute is a value indicating how many times a unit of data has been processed by the processing engine, wherein a unit of data is deleted if it has been processed more than a maximum permitted number of times. ### Claim 13 A system for hosting an extract, transform and load, ETL, method with extract, transform and load phases, the system comprising: a source system operable to supply units of data in an extract phase; a target system operable to receive units of data in a load phase; and an extract, transform and load engine comprising: an extract engine operable to perform the extract phase by extracting units of data from the source system; a queue configured to receive units of data from the extract engine; a processing engine operable to perform the transform and load phases by receiving units of data from the queue, transforming the units of data and loading the units of data to the target system; a pace manager operable to monitor the queue, wherein, if the pace manager detects that there are more than a threshold number of requeued units of data in the queue, then the pace manager is operable to impose at least one restriction on the extract phase; and the pace manager being further operable to monitor a history of units of data held in the queue and to delete one or more of the units of data based on their history, respectively, when the units of data fail a threshold number of times. ### Claim 14 An extract, transform and load (ETL) method, the method comprising: an extract phase of extracting units of data from a source system and placing them in a queue; a transform phase of receiving units of data from the queue and processing them; and a load phase of loading units of data that have been processed to a target system, wherein the method: requeues units of data that are subject to a rate limit error in the load phase; monitors the queue to detect requeued units of data therein and, if there are more than a threshold number of requeued units of data in the queue, imposes at least one restriction on the extract phase; and monitors a history of units of data held in the queue and deletes one or more of the units of data based on their history, respectively, when the units of data fail a threshold number of times. ### Claim 15 (depends on 14) The ETL method of claim 14, wherein the retry schedule is set so as to avoid rate limit errors occurring again in the load phase after the units of data have been reprocessed in the transform phase. ### Claim 16 (depends on 15) The ETL method of claim 15, wherein the at least one extract restriction includes imposing a pause on extraction for an amount of time set according to an expected amount of time needed to reprocess the requeued units of data taking account of a retry schedule. ### Claim 17 (depends on 14) The ETL method of claim 14, wherein the at least one extract restriction includes introducing a pacing limitation to limit how many units of data are extracted per unit time. ### Claim 18 (depends on 17) The ETL method of claim 17, wherein the at least one extract restriction further includes reducing size of the units of data. ### Claim 19 (depends on 14) The ETL method of claim 14, wherein the threshold number takes account of how many requeued units of data there are compared with a total number of units of data held in the queue. ### Claim 20 (depends on 14) The ETL method of claim 14, wherein units of data subject to a rate limit error are received from the target system.