[CT-2468] [Feature] For versioned models, automatically create view/clone of latest version in unsuffixed database location

[jtcohen6]

Thanks @dbeatty10 @joellabes for working through this with me!

As the producer of a versioned model, I want the unsuffixed database relation (<db>.<schema>.<model_name>) to always point to the latest version of that model. This provides downstream consumers with the same optionality that's available to ref: "pin" to a specific version (suffix), or "unpin" (no suffix) and always get the latest.

dbt should handle this for me, by creating an additional "pointer" view (select * from <latest>) or table clone (depending on the relation type and data platform support) whenever I run the latest version of my model.

Implementation options

I'm not in love with any of these options:

1. Create a new node in the manifest with this logic, materialized as a view (or clone?). Not sure if/how we'd be able to set configurations on that additional node/object (e.g. persisting descriptions).
2. Update the view, table, and incremental materializations to produce an additional relation, if the model is versioned (and this is the latest version)
3. Provide users with a macro that they can "set and forget" as a model post-hook

Of those, (3) is the least magical, and it's what we'll propose to users in the short term (v1.5). We still don't like:

• Hooks in general, and especially hooks that run DDL to create database objects
• Lacking the robustness of an actual materialization
• Requiring users to copy-paste a macro and update their project, when this is the Right Behavior that should just happen out of the box
• Making the proposed logic for schema management more complicated

[jtcohen6]

We have three implementation options. Each has trade-offs around user limitations and complexity of implementation.

Next step: Let's spike each of the options, and make a call.

[nicohein]

Hi All,

I would like to propose a fourth option, that is a little bit more verbose but adds flexibility:

Option 4

The main model itself remains unchanged. It is expected to be implemented in dim_customers.sql. The materialization with the version number of the main model becomes optional. If you want to have this materialization, then you create an item in the versions.

- models:
  - name: dim_customers
     # it essentially becomes obsolete to specify the main/latest version
    config:
      materialized: table
      meta:
        key: value
    columns:
      - name: customer_id
        description: This is the primary key
        data_type: int
      - name: ...
    versions:
      - version: 1
        implemented_in: dim_customers_OLD
        deprecation_date: '2022-01-01'
        config:
          meta: {} #remove the meta from v2
        columns:
          include: ...
          exclude: ...
      - version: 2
        implemented_in: dim_customers
      - version: 3
        description: A dramatic reimagining of our customers model with no column overlap? What are they thinking?!
        implemented_in: dim_customers_NEW_BUT_NOT_READY
        # deprecation_date is optional and not included for this version
        columns:
          - name: totally_new_column_1
          - name: totally_new_column_2
          - name: totally_new_column_3
          ...

This would result in a second table being created called dim_customers_v2. If the developer instead choses to materialize _v2 as a view thats possible by instead writing:

      - version: 2
        # implemented_in: dim_customers_v2   # expected default 

And adding the corresponding .sql file:

{{ config(materialized="view") }}

select * from {{ ref('dim_customers') }}

If a team decides to have _v2 materialized as a table and define dim_customers as a view, that is now equally possible.

One remaining challenge I can think of is that a developer could introduce a drift between the main model and v2 because there is simply no guarantee of those models being equal. I regard this risk as low because whoever will make use of models, will put some thought behind the versioning model.