This article is the fourth of a serie on how data flows across data projects based on how environments are configured. The way environments are connected heavily influences the way teams work and their effectiveness.

After looking at my preferred modern setup and the heavenly cases when we can make things even simpler, it is time in this article to tackle the common scenario in many industries where you cannot work with production data.

When production data is no go?

In this serie we have seen, and many practitioners near you can confirm it, that being able to work with production data in all environments is much better than having to deal with sub-par TEST and -God forbid- DEV data.

If it is so much better -on many fronts- why in too many projects is still a thing working with TEST data?

There are generally two cases named to require developers to work with non production data:

1. the most compelling one is where we started our journey: upstream projects release at such low speed that waiting for changes to go to production is incompatible with the time to market required to our project, so we need to look ahead.
2. the most frequently named is connected to security or privacy: in short the idea is that some sensible data like bank accounts or medical records should not be exposed unless in business work. Therefore some surrogate test data is to be used during development to avoid developers (and consultants) to be able to know the real situation of customers or patients.

The first case is compelling to me because it has good motives: while you cannot change all processes in a big company, it still makes sense to try to use at best the agility of new processes and tools. If the value is enough, then we can accept the extra complexity.

The solution proposed in this article works well if you decide to always develop with TEST data and accept all the bad consequences this brings.

In the next article we will discuss a possible solution to this specific case when sometimes you want to look ahead to how things are in an upstream environment (to start early work on a solution), but most of the time you are fine to work with production data. Trying to get the best of both worlds is not just a walk in the park.

The second case is something that I understand being necessary in some cases, but in many cases where I have seen it applied, it was more of a habit or a security blanket that someone decided to follow or impose, than a real need.

This is often shown when you dig enough to understand who does and how it does the unavoidable analysis on weird or wrong production outputs and incidents.

Often the same pool of developers is granted access to production data to support the identification and resolution of issues, but the same people are forbidden to use that same data for development, making their work slower, less reliable and in turn increasing the chances they need to access production data to fix issues.

What baffles me is that even after agreeing that such a state of things is not optimal and makes only partial sense… it is not uncommon that for resignation or a preference of peaceful and quiet life nothing has been changed.

The main downside of working with test data

To conclude this introduction I do urge you to validate thoroughly any request that you receive to develop using non production data, as it has plenty of drawbacks that you can read again in the first article of this serie.

Here I want just to recall the most critical problem of such a scenario: the layout and naming of TEST and PROD data DO become misaligned.

This is actually the normal process that development happens: something is changed and is released in TEST and only after a while it goes to PRODUCTION.

In the meantime the two environments are incompatible and the same code cannot run on both.

To be able to release your development without breaking PROD you need to know when and where the TEST data you are working on is not aligned with the PROD data.

This unfortunately seldom happens and is a common source of issues.

When you find -by information or error- that TEST and PROD are not aligned anymore, then you need to keep the TEST based developments on hold in their own feature branches until the upstream misalignment has been removed or do some extensive effort to explicitly handle the misalignment.

Between the times you will not know what is misaligned and the ones when you will release some apparently ready feature forgetting why it was on hold… you see a great potential for troubles.

Developing with test data

We have done our due diligence and it’s true that we need to develop with test data. So how does it look like?

The following image illustrates the common case where you have production data only flowing through production environments and some kind of surrogate test data being available to develop in other environments.

You might have noticed that I have now labeled the intermediate environment as STAGING.

This is deliberate, and it is meant to point to the fact that dbt Cloud provides a clear path to setup your environments for such -sadly- common requirement.

The reference framework is the one from the image and provides for two sets of inputs: one for the PROD environment and one for the other environments.

The development cycle is the usual one: you develop in DEV, then you release to STAGING -that might be actually named QA or CI or TEST- and finally you release to PROD.

What is going to be different with respect to our default, production only, setup is that in dbt Cloud you will label your intermediate environment of reference as THE STAGING environment and dbt Cloud will use it to resolve any internal or project cross-reference from DEV and STAGING environments, de facto securing your PROD data in the PROD environment.

Environment specific inputs

The above change alone only ensures that any non PROD environment will defer to the designated STAGING environment when looking for data.

What really makes the above setup work as expected is the simple, but fundamental, act of providing different inputs for your PROD environment and the other environments.

The points of contact of a dbt project with the external world are its sources, so it is there that we have to make our magic.

If you are loading the external data in some schema in the same database of the project then now you can load TEST data in the database of the TEST environment and PROD data in the PROD database. That might be enough.

In the more common case when you are loading external data to a separate database from the ones used by the dbt project, then you will load TEST and PROD data to two different databases and use some conditional in the dbt source configuration to point to the PROD data when in PROD and to the TEST data otherwise.

Let’s make an example assuming that for the FINANCE schema of the source system SAP we have data in SAP_PROD and SAP_TEST databases.

One explicit route is to define directly the alternatives to choose from in the source definition, like below:

version: 2
sources:
  - name: SAP_FINANCE
    database: "{{ 
      'SAP_PROD' if target.name == 'prod'
      else 'SAP_TEST' }}"
    schema: FINANCE
    tables:
      - name: sap_table_abc

In the above code you can use any conditional that will work and makes you feel safe. In my experience the use of target.name or target.database are the two most reliable ways to know in what environment you are based on the dbt configuration, with target.name existing specifically for this purpose.

Another simple way to achieve the same goal, but a bit less explicit, is to define a SAP_DB dbt Cloud environment variable with different values for PROD and the other environments (default), and then use it in the source definition.

The result would be something like the following:

version: 2
sources:
  - name: SAP_FINANCE
    database: "{{ env_var('SAP_DB') }}"
    schema: FINANCE
    tables:
      - name: sap_table_abc

You might probably think of some other way to swap the inputs, but the point is that by combining the differentiated inputs with the right configuration of the STAGING environment you can use the advanced dbt CLoud functionalities like deferral without risk exposing production data.

In the end we have seen that in dbt Cloud it is not so difficult to adapt our projects to work with different data for the PROD environment and the other ones.

What are not going away are the consequences of working with non production data, but when it’s needed it is just one layer of complexity that we have to deal with.

A small recap

In this series we have gone through the most common environment setups, discussed their pros and cons and argued why it makes sense to try to reduce the overall complexity of the project, when it’s possible.

In the next article we will see how it is possible -in the same dbt Cloud project- to work with both TEST and PROD data to be able to start developing on something that exists only in the upstream TEST without mixing PROD and TEST data and without blowing up the production environment.

Originally published on 24/03/2025 at https://pragmatic-data.org/

Articles in the Environments serie

1. Environments pt.1 – The hell from legacy
2. Environments pt.2 – Out of hell, into modernity
3. Environments pt.3 – Paradise, sometimes
4. Environments pt.4 – Advanced sensitive data setup (this article)
5. Environments pt.5 – Using both TEST and PROD data