Steps in a Data Load
Data is loaded into a target table (the destination of the load process) by ETL+ in a two step process.
Temporary Staging Table: ETL+ loads the source data into a temporary data warehouse table also known as a staging table.
The staging table allow users to continue to query the target table during the load (especially important when loading large tables). The target table is a table in the destination data warehouse.
The staging table content is defined by the source table and the ETL SQL Statement. The staging table mirrors the source table when the ETL SQL Statement has no transformations.
If TableName_Temp already exists, it’s content is overwritten.
Target Table: The ETL process for step two is determined by the Load Type specified on the ETL+ Extract, Transform and Load (ETL) Page. Once the staging table load step is completed, ETL+ processes the temp table according to the Load Type assigned for the ETL source to destination mapping.
Replace: Delete the rows meeting the Replace criteria from the target table, insert the temp table’s rows onto the target table, and delete the temp staging table. The insert includes Design page transformations.
See also ETL+ Load Replace
Upsert: Delete the rows meeting the Upsert criteria from the target table, inserts the staging table’s rows onto the target table, and deletes the staging table. The insert includes Design page transformations.
If a load failure happens, the temp table might stay in the data warehouse until the next load.
For more on Load types see ETL+ Table Load Types
Table Load Conflicts
ETL+ does not allow two or more load processes to load the same target table at the same time.
At the start of a table load, ETL+ flags the metadata its load session for that particular table, and a date time stamp of the beginning of the load process. Every 15 seconds, ETL+ will update the metadata date-time stamp flag indicating that the load continues. ETL+ will clear those two settings at the end of the table load.
If an ETL+ session tries to load a target table while another ETL+ session is already loading data into, the former will show or/and log “Another ETL+ is loading this table. Waiting for table release…”. This ETL+ session will wait for 15 seconds and try to load the table again, repeating this cycle until the table is released by the latter ETL+ session.
If ETL+ is loading a table but then dies or stops working, the metadata date-time stamp will no longer be updated every 15 seconds. An ETL+ session waiting for a table release will take over that table load if the table’s metadata date-time stamp is 90 seconds or older.
Acumatica OData Load
Aside from the processes above, when ETL+ extracts 100 pages from an OData table (for instance, each page with 1,000 rows), ETL+ moves/inserts the TableName_Temp rows into TableName_Temp2, and then proceeds to extract and load the remaining source table’s rows onto TableName_Temp.
This process repeats until all of the source table rows have been extracted. And then ETL+ processes the TableName_Temp2 move/insert into the target table according to the load type described above.
What is a Target Table?
Otherwise known as a destination table. A target table can also be thought of as the destination table of a ETL load process.
The destination of the data loaded is always a table in the data warehouse.
Archiving Historical / Old Data
WIP documentation ⚒️
What Happens to Rows Deleted from the Data Source for Tables without Load All?
WIP documentation ⚒️