[For Hire] Rethinking incremental models

A lookup table change made me rethink incremental models.

While working on a dbt + Snowflake modernization project, I was reviewing an incremental dimension model that joined a study table with a study status lookup table.

At first glance, the logic looked fine.

Then an architect asked a simple question:

"What happens if the study doesn't change, but the lookup value does?"

Example:

Yesterday:

Plain text

Status ID = 5

Status Name = ACTIVE

Today:

Plain text

Status ID = 5

Status Name = ACTIVE_STUDY

No study records changed.

The incremental model never picks up the update.

Every test passes.

The pipeline succeeds.

Yet the dimension is now serving stale data.

That sparked an interesting discussion:

Should incremental dimensions only track changes in the primary table?

Should they also react to changes in lookup/reference tables?

Is this where dbt snapshots make more sense than pushing SCD Type 2 logic into every dimension?

My initial instinct was to watermark only on the main table.

After walking through a few scenarios, I'm leaning toward identifying affected business keys whenever dependent lookup tables change and rebuilding only those records.

Curious how others handle this in production.

What's your approach when a lookup table changes but the parent entity doesn't?

#dbt #snowflake #dataengineering #analyticsengineering #datamodeling