# How to prevent views from constantly breaking due to underlying table changes

**URL:** <https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634>\
**Category:** Help\
**Tags:** snowflake\
**Created:** [February 6, 2023, 11:27pm UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634 "2023-02-06T23:27:02Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ptrn](https://avatars.discourse-cdn.com/v4/letter/p/f17d59/32.png) [@ptrn](https://discourse.getdbt.com/u/ptrn)\
**Post date:** [February 6, 2023, 11:27pm UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/1 "2023-02-06T23:27:02Z")

</div>

## The problem I’m having

We populate our raw database using Fivetran. Since dbt automatically hardcodes the columns in a view definition, our staging views break every time a new column is added to the underlying table, with, for example:  
`View definition for 'ANALYTICS_DB.STG_SCHEMA.STG_VIEW' declared 16 column(s), but view query produces 19 column(s).`

## The context of why I’m trying to do this

I’m trying to figure out a way to not have to re-run all staging views every time Fivetran finishes syncing to prevent this.

## What I’ve already tried

I’ve been looking at dbt-coves and dbt-osmosis. I think I can get things to work ok with a decent amount of modification and combination, but a) it seems like way more work than should be required for something like this, b) the performance is terrible, etc.

## Some example code or error messages

Model ANALYTICS\_DB.STG\_SCHEMA.STG\_VIEW is defined as

```auto
SELECT
	*

FROM
	FIVETRAN_DB.SCHEMA.TABLE

```

But gets compiled to

```auto
CREATE OR REPLACE VIEW ANALYTICS_DB.STG_SCHEMA.STG_VIEW AS
(
	SELECT
		COL1
	,	COL2
	,	COL3

	FROM
		FIVETRAN_DB.SCHEMA.TABLE
);

```

---

<div class="post-metadata">

**Author:** ![jaypeedevlin](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jaypeedevlin/32/638_2.png) [@jaypeedevlin](https://discourse.getdbt.com/u/jaypeedevlin)\
**Post date:** [February 6, 2023, 11:42pm UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/2 "2023-02-06T23:42:54Z")

</div>

What adapter are you using? I ask because when I look in `target/run` for both my test (duckdb) project I don’t see it enumerate the columns like your example shows.

 ![image](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/c/c9037a732c5373a43aff2888fc067904057ad86f.png)

---

<div class="post-metadata">

**Author:** ![ptrn](https://avatars.discourse-cdn.com/v4/letter/p/f17d59/32.png) [@ptrn](https://discourse.getdbt.com/u/ptrn)\
**Post date:** [February 7, 2023, 12:07am UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/3 "2023-02-07T00:07:17Z")

</div>

Thanks @jaypeedevlin for the quick response!

I’m using Snowflake. I just assumed the behavior would be consistent across adapters.

---

<div class="post-metadata">

**Author:** ![jaypeedevlin](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jaypeedevlin/32/638_2.png) [@jaypeedevlin](https://discourse.getdbt.com/u/jaypeedevlin)\
**Post date:** [February 7, 2023, 1:53am UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/4 "2023-02-07T01:53:59Z")

</div>

I had a play with Snowflake, and this is not actually dbt doing this, but instead how Snowflake stores the resultant created view. I did the same thing as above and the code in /target/run includes the select \* but I pointed it to a test table, which I then added a column to and got the error you’re seeing.

What dbt ran:

```sql
  create or replace view db.schema.test
  
   as (
    

select
  *
from db.scratch.jd_table
  );

```

What snowflake stores as the definition of the view:

```sql
create or replace view DB.SCHEMA.TEST(
	FOO,
	BAR
) as (
    

select
  *
from db.scratch.jd_table
  );

```

So when the underlying table adds a column the view fails.

You can automate adding the columns in using [dbt\_utils.star()](https://github.com/dbt-labs/dbt-utils#star-source) which will mean your views don’t fail when columns are added, and also will update without you having to touch the code on your next dbt run.

**All of that said** , my subjective opinion is that staging models are for renaming, casting types, etc. Essentially creating an endorsed view of your source data. With that framing, I prefer to manually enumerate the columns (using codegen to help initially). Otherwise, you may as well not have staging models at all and just select directly from the source.

---

<div class="post-metadata">

**Author:** ![ptrn](https://avatars.discourse-cdn.com/v4/letter/p/f17d59/32.png) [@ptrn](https://discourse.getdbt.com/u/ptrn)\
**Post date:** [February 7, 2023, 4:17pm UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/5 "2023-02-07T16:17:23Z")

</div>

That was awesome of you to dive in there. I should’ve looked at the compiled code myself, instead of just assuming that it was dbt doing this rather than Snowflake.

You make a great point at the end, and I agree.

Thanks again!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [February 14, 2023, 4:18pm UTC](https://discourse.getdbt.com/t/how-to-prevent-views-from-constantly-breaking-due-to-underlying-table-changes/6634/6 "2023-02-14T16:18:03Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.