What is a staging table?

Question

Are staging tables used only in Data warehouse project or in any SSIS Project? I would like to know what is a staging table? Can anyone give me some examples on how to use it and in what circumstances it is implemented? Also, may I please know the best practices while using it?

Unattended

ramareddydcp.wordpress.com/2017/02/12/… – Offshore

Answer 1

staging tables are just database tables containing your business data in some form or other. Staging is the process of preparing your business data, usually taken from some business application. For your average BI system you have to prepare the data before loading it. A staging table is essentially just a temporary table containing the business data, modified and/or cleaned. Depending on your actual case you may have several staging tables from different source systems that then are combined into the resulting fact table for the BI system.

Tisman

Thank you Dirk. Can you give me a simple real time example, please? – Unattended

simple example: your ERP system has several tables for invoices, invoice header, invoice lines, involved partners. You need some information from all of these tables in one single fact table. Additionally you need to convert dates in your invoice data into a date dimension. So you first load your invoice data into staging tables, add the date dimension data and create a fact table from there. Sometimes staging tables may look identical to the tables in the origin system, sometimes they may be a result of a SQL query joining several tables. All depends on your scenario. – Tisman

Hello Dirk, thank you again. So as you have specified (result of a SQL query joining several tables), it can also be used in a non data-ware house project. Even though I am not familiar with the term staging table, now I could understand that it is just a temp table or intermediate table used for data modification/cleaning purpose. – Unattended

is there a rule on how staging tables should be named? – Hyphen

Answer 2

A staging table allows incremental maintenance support for deferred materialized query table(s). It usually collects changes that must be applied to the materialized table (e.g., a fact table) to synchronize it with the contents of underlying tables. See IBM documentation here.

More info:

Staging Area is a temporary location where data from source systems is copied. Due to varying business cycles, data processing cycles, hardware and network resource limitations and geographical factors, it is not feasible to extract all the data from all Operational databases simultaneously. Data in the Data Warehouse can be either persistent (i.e. remains around for a long period) or transient (i.e. only remains around temporarily). Not all businesses require a Data Warehouse Staging Area. For many businesses, it is feasible to use ETL to copy data directly from operational databases into the Data Warehouse.

Here's an example of where usually is developed a staging table:

Jermaine

Answer 3

A staging table in BASEL Regulatory authority OFSAA is a set of tables to contain data in Product Processors and other Stage tables to store data from the Operational Systems of the Bank. Product Processors are created based on the various financial products that the bank caters to. Stage tables for Product Processors have been categorized as Exposures data of Product Processors.

Fireworm