Do all columns in target have to be in the model

Help
, , ,
1

My model does not contain all the rows that are in my target table, and when i try running it it fails with an unrecognised name error

I am trying to make it so that my model runs and populates only the columns in the model, and all other columns in the target table populate with just null so that i can have my target table be extended if need be without affecting our current models

I have tried using chatgpt which suggests to use a macro to get all of the extra columns, and then adding this into my model where it gets the columns from the source i am using and the ones from the target table and if it is not in the source tables it should populate with null, but every time i get an error unrecognised name: column name
I have also considered using full refresh, but we want to add data to the target table without losing the data that is currently in there

Any advice or code suggestions would be! Thank you

2

Hi @LiterallyJustAGirl,

If you have a source table with columns A, B & C for example, but you want A-E in your target, you should be able to do something like the following in your model:

target_table_model.sql:

SELECT
  A,
  B,
  C,
  NULL AS D,
  NULL AS E
FROM {{ source('source_name', 'table_name') }}
1 Like
3

Hi, the issue is my table is shared by other users, so we want our model to run with the columns we have selected but other users might want to add another column or more, and we dont want to have to update the model every time a new column is added so I am trying to find a dynamic way columns added can be automatically made null when our dbt runs!

4

@LiterallyJustAGirl could you put an example here with some really small dummy tables etc? I’m not sure I’m following what you mean sorry.

5

Hi,
So in bigquery I have my source table set with say
Source1column1, source1column2, source1column3

And I run my model to have
Select sourcecolumn1 targetcolumn1,
Sourcecolumn2 targetcolumn2

And i create a table in bigquery with the specific columns
Targetcolumn1, targetcolumn2

If someone were to come along and create their own model and add a new column targetcolumn3 for whatever reason, when i ran my model again it was failing because of the extra column and i would have to update my model every time

However i have found now that using on schema change: append new column in my model means that when i update the bigquery table with a new column not in my model and run the model, it is automatically filling the extra column in with null which is fixing my issue!