changing dbt dates names in snapshot

Question

How can I change the default columns name in a snapshot: dbt_valid_from dbt_valid_to?

I override the macro default__build_snapshot_table and it is working the first time, but when I re run the snapshot I am getting the following error:

Answer 1

There are 2 macros you need to override. The first (build_snapshot_table) handles the initial creation of the snapshot table, which you've done. The second (build_snapshot_staging_table) handles the delta on subsequent runs. You can see this in the dbt-core/snapshot.sql code (snapshot.sql):

  {% if not target_relation_exists %}


      {% set build_sql = build_snapshot_table(strategy, model['compiled_sql']) %}
      {% set final_sql = create_table_as(False, target_relation, build_sql) %}


  {% else %}


      {{ adapter.valid_snapshot_target(target_relation) }}


      {% set staging_table = build_snapshot_staging_table(strategy, sql, target_relation) %}
...

Take a look at the default implementation to see what logic you need to replace for renaming the columns. Should be pretty straightforward.

Answer 2

As mentioned by Adam above, there are more macros that you need to override. You should be looking at snapshot_merge_sql and snapshot_staging_table and also a assert

{{ adapter.valid_snapshot_target(target_relation) }}

In this link which you may have to override or remove at all.