Handling Data Mapping from Two Sources with Different Schemas in dbt

Help
1

The problem statements

I have two data sources: one is a historical data table called src_table_his and the other is a daily data table called src_table_daily . I’m trying to create a dbt transformation process that maps fields from src_table_his to a final output table dst_table_all in one go, and daily maps fields from src_table_daily to the same output table dst_table_all . The two sources have different fields: src_table_his includes columns a, b, c, d, e, f, while src_table_daily includes b, d, g, h, i. The final output table needs to combine all these fields: a, b, c, d, e, f, g, h, i.

What I’ve already tried

  1. Method One
  • I created separate mapping SQL for src_table_his and src_table_daily, each configured with alias=dst_table_all. However, compilation fails because dbt does not allow two models to have the same name.
  • Added a dbt model with an if statement to switch based on run_mode variable (daily or historical).
  1. Method Two
  • I used an if-else statement to execute different mapping SQL based on the run_mode. However, I encountered an error where a column exists but cannot be referenced:
    {% if var(‘run_mode’, ‘daily’) == ‘historical’ %}
    {% else %}

Error messages

07:36:45    column "x_date" does not exist
07:36:45    LINE 17:         select "x_date", "...
07:36:45                            ^
07:36:45    HINT:  There is a column named "x_date" in table "src_order_daily", but it cannot be referenced from this part of the query.