Ngtjyty

I have a general question on a Data Warehouse Architecture. I have heard a lot of a Staging and a Production, so that data should be loaded via SSIS first into a staging dwh and then via SSIS to the Production environment.

But is there any difference? I mean is it only a "copy" of the production data warehouse (to do some quality checks on data before pushing to production?) or of the source data in a different model or (different schema)?

From software development perspective i know that both are identical and the only purpose is to check the software version from all stakeholders and get the release to deploy on prod. Normally in the software case the staging is same (maybe only hardware sizing is different) to production.

I think this is a case of the same word being used to describe two different things.

The first being a staging environment. As you mention, this is a near copy of the production environment architecture and can be used to test releases that are due to be moved into production or to allow users to view upcoming features before they are released.

The second is a data staging area. Although it is called staging, it could actually be located within the production environment (as has been the case in most companies that I have worked for). This area is used to cleanse and validate data extracted from OLTP systems before being loaded into a data warehouse.

Generally, at least in my work history, I have found that production data may not be copied into the staging environment due to legal or logistical constraints. Mostly I have found that it is because developers/testers should not access sensitive data such as personal details, credit cards etc... but it can be copied into a data staging area in preparation of loading it into the data warehouse.

Over time, as you gain more experience it gets easier to tell which one an individual is referring to - but until then, just remember that live data should not leave the production environment and it is perfectly fine to have a dedicated area within production in which you can process your data.

In general 'stage' or 'staging' is a sort of intermediate place or "Holding area" for data before being put into its final format in the presentation layer. (Within your environments (e.g. servers) it could be on the same server, or a different one, than the final format -- depending on your individual setup. Typically it would be at least in a different database, on the same server.)

The important point is that 'staging' is generally a place where data -- which could be from various different sources, depending on what you are ingesting into your DWH -- gets landed initially, and then it gets processed into the final form that is consumed by your business users.

In a minimal case it could be just a "copy" of what's in production, before you push it, but typically there would be additional processing carried out on it before putting into production such as:

• translating raw data into dimensions


• grouping and aggregating


• cleaning (e.g. how to deal with missing values)

Staging has a different meaning in data warehousing than it does in general software development (I know what you are getting at -- the existence of a 'stage' environment between UAT and production which is the same as production but a simulated copy).

In my (DWH) experience Staging is generally in a pretty similar format to the raw data, e.g. if you have 'Account' on your source server then you could have 'StageAccount' in the staging db, and then you would carry out some more processing on the StageAccount to transform it into (e.g.) DimAccount records.