I am currently working on a user management applications (UMA) that manages a data warehouse security management (users, rights etc.).

The data warehouse has production databases (and production users) + pre-production (PP) databases (and PP users).

UMA like any other applications has its own PP and Prod environments.

Currently UMA PP deals with datawarehouse PP databases only and UMA Prod deals with datawarehouse Prod databases only.

Pros and cons (I see so far)


  • intuitive - key testers (that includes data warehouse clients) are familiar with testing using PP users on PP databases


  • risky - by using a PP version to change actually used PP databases users might lead to security issues (a PP user might see some data that is not supposed to see) since PP version is delivered for some key users to actually check that UMA behaves correctly.

  • licensing - UMA must provide aggregate information for all databases (PP and Prod)

Not sure if it is relevant, but producing a new PP database in the data warehouse is not an easy task, so PP databases are "recycled" (partially data wipe, transfer data from a Prod.

Also, UMA also handles reporting systems users.

I am wondering what is the best practice to model in this scenario?

Question: How to model a user management system that deals with both pre-production and production databases?

  • I'm not really sure what your problem is. This sounds like a pretty standard setup. – Greg Burghardt May 28 '19 at 12:42
  • @GregBurghardt - my main concern is that data warehouse PP users are managed using a PP application which might have some bugs. This means that persons operating on PP databases might get messed up users. In this case, I would be tempted to use a productive (stable and approved) user management application to also handle PP databases users. – Alexei May 28 '19 at 12:46
  • Are one of the pre-production environments used to test out new roles and permissions? It sounds like you need several pre-production environments. One to handle potential defects in the application, and another as a proving grounds for data. Basically you need an environment to test new application logic, and another environment to test new data against stable application logic. – Greg Burghardt May 28 '19 at 12:56
  • @GregBurghardt - yes, this sounds reasonable (a clear separation between user management PP and data warehouse PP). – Alexei May 28 '19 at 12:58
  • Typically preprod is the last step before deployment to shake out the delivery problems. Many development groups also have Integration and QA as well. Integration is for all the automated tests to ensure that the application is still working, and QA is for testers to actually hit and ensure requirements were implemented correctly. – Berin Loritsch May 28 '19 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.