upstream-prod is a dbt package for easily using production data in a development environment. It's a hands-off alternative to the defer flag - only without the need to find and download a production manifest - and was inspired by similar work by Monzo.

In a typical project, prod and dev models are materialised in separate environments. Although this ensures end users are unaffected by ongoing development, there's a significant downside: the isolation means that each environment needs a complete, up-to-date copy of every model. This can be challenging for complex projects or long-running models, and out-of-date data can cause frustrating errors.

upstream-prod solves this by intelligently redirecting refs to prod outputs. It is highly adaptable and can be used whether your environments are in separate schemas, databases, or a combination of both. On most warehouses it can even compare dev and prod outputs and use the most recently-updated relation.

If upgrading from an earlier version:

• Your ref macro must be updated by copying this example.
• Projects using the deprecated upstream_prod_database_replace variable should follow the setup guide to use upstream_prod_env_dbs instead.

ℹ️ If you need help setting up the package, please create an issue or tag / DM @LewisDavies on the dbt Slack.

The package relies on a few variables that indicate where prod data is avaiable. The exact requirements depend on your setup; use the questions below to find the correct variables for your project.

If you aren't sure, check your macros directory for a macro called generate_schema_name. The exact filename may differ - dbt's docs call it get_custom_schema.sql - so you may need to check the file contents.

Your platform may use a different term, such as catalog on Databricks or project on BigQuery.

-- 1. Add an is_upstream_prod parameter that defaults to False
{% macro generate_schema_name(custom_schema_name, node, is_upstream_prod=False) -%}

    {%- set default_schema = target.schema -%}
    -- 2. In the clause that generates your prod schema names, add a check that the value is True
    --    **Make sure to enclose the or condition in brackets**
    {%- if (target.name == "prod" or is_upstream_prod is true) and custom_schema_name is not none -%}

        {{ custom_schema_name | trim }}

    {%- else -%}

        {{ default_schema }}

    {%- endif -%}

{%- endmacro %}

# dbt_project.yml
vars:
  # Required
  upstream_prod_env_schemas: true
  # Optional, but recommended
  upstream_prod_fallback: true
  upstream_prod_prefer_recent: true
  upstream_prod_disabled_targets:
    - prod

# dbt_project.yml
vars:
  # Required
  upstream_prod_schema: <prod_schema_name/prefix>
  # Optional, but recommended
  upstream_prod_fallback: true
  upstream_prod_prefer_recent: true
  upstream_prod_disabled_targets:
    - prod

-- 1. Add an is_upstream_prod parameter that defaults to False
{% macro generate_schema_name(custom_schema_name, node, is_upstream_prod=False) -%}

    {%- set default_schema = target.schema -%}
    -- 2. In the clause that generates your prod schema names, add a check that the value is True
    --    **Make sure to enclose the or condition in brackets**
    {%- if (target.name == "prod" or is_upstream_prod is true) and custom_schema_name is not none -%}

        {{ custom_schema_name | trim }}

    {%- else -%}

        {{ default_schema }}

    {%- endif -%}

{%- endmacro %}

# dbt_project.yml
vars:
  # Required
  upstream_prod_database: <prod_database_name>
  upstream_prod_env_schemas: true
  # Optional, but recommended
  upstream_prod_fallback: true
  upstream_prod_prefer_recent: true
  upstream_prod_disabled_targets:
    - prod

-- 1. Add an is_upstream_prod parameter that defaults to False
{% macro generate_database_name(custom_database_name=none, node=none, is_upstream_prod=False) -%}

    {%- set default_database = target.database -%}
    -- 2. In the clause that generates your prod database names, add a check that the value is True
    --    **Make sure to enclose the or condition in brackets**
    {%- if (target.name == "prod" or is_upstream_prod is true) and custom_database_name is not none -%}

        {{ custom_database_name | trim }}

    {%- else -%}

        {{ default_database }}

    {%- endif -%}

{%- endmacro %}

# dbt_project.yml
vars:
  # Required
  upstream_prod_database: <prod_database_name>
  upstream_prod_schema: <prod_schema_name/prefix>
  # Optional, but recommended
  upstream_prod_fallback: true
  upstream_prod_prefer_recent: true
  upstream_prod_disabled_targets:
    - prod

-- 1. Add an is_upstream_prod parameter that defaults to False
{% macro generate_database_name(custom_database_name=none, node=none, is_upstream_prod=False) -%}

    {%- set default_database = target.database -%}
    -- 2. In the clause that generates your prod database names, add a check that the value is True
    --    **Make sure to enclose the or condition in brackets**
    {%- if (target.name == "prod" or is_upstream_prod is true) and custom_database_name is not none -%}

        {{ custom_database_name | trim }}

    {%- else -%}

        {{ default_database }}

    {%- endif -%}

{%- endmacro %}

In your macros directory, create a file called ref.sql with the following contents:

{% macro ref(
    parent_arg_1,
    parent_arg_2=None, 
    prod_database=var("upstream_prod_database", None),
    prod_schema=var("upstream_prod_schema", None),
    enabled=var("upstream_prod_enabled", True),
    fallback=var("upstream_prod_fallback", False),
    env_schemas=var("upstream_prod_env_schemas", False),
    version=None,
    prefer_recent=var("upstream_prod_prefer_recent", False),
    env_dbs=var("upstream_prod_env_dbs", False)
) %}

    {% do return(upstream_prod.ref(
        parent_arg_1,
        parent_arg_2,
        prod_database,
        prod_schema,
        enabled,
        fallback,
        env_schemas,
        version,
        prefer_recent,
        env_dbs
    )) %}

{% endmacro %}

Alternatively, you can find any instances of {{ ref() }} in your project and replace them with {{ upstream_prod.ref() }}. This is suitable for testing the package but is not recommended for general use.

Assume your project has an events model that depends on intermediate and staging layers. The simplified DAGs looks like this:

  graph LR
      source[(Source)]
      source -.-> prod_stg[stg_events]
      source ==> dev_stg[stg_events]
      
      subgraph prod
      prod_stg -.-> prod_int[int_events] -.-> prod_mart[events]
      end
      
      subgraph dev
      dev_stg ==> dev_int[int_events] ==> dev_mart[events]
      end

You want to change int_events, so you need a copy of stg_events in dev. This could be expensive and time-consuming to create from scratch, and it could slow down your development process considerably. Perhaps this model already exists from previous work, but is it up-to-date? If the model definition or underlying data has changed, your dev model may break in prod.

upstream-prod sovles this problem by intelligently redirecting refs based on the selected models for the current run. Running dbt build -s int_events+ would:

1. Create dev.int_events using data from prod.stg_events
2. Create dev.events on top of dev.int_events, since the package recognises that int_events has been selected
3. Run tests against dev.int_events and dev.events

Now that your dev models are using prod data, you DAG would look like this:

  graph LR
      source[(Source)]
      source ==> prod_stg[stg_events]
      source -.-> dev_stg[stg_events]
      
      subgraph prod
      prod_stg -.-> prod_int[int_events] -.-> prod_mart[events]
      end
      
      subgraph dev
      dev_stg ~~~ dev_int
      prod_stg ==> dev_int[int_events] ==> dev_mart[events]
      end

upstream-prod is built and tested on Snowflake. Based on my experience and user reports, it is known to work on:

• Snowflake
• Databricks
• BigQuery
• Redshift (you may need RA3 nodes for cross-database queries)
• Azure Synapse

It should also work with community-supported adapters that specify a target database or schema in profiles.yml.