Replicating Lazy (Late-Binding) Views in PostgreSQL Using dbt Custom Materialization — Safe for Production?

In-Depth Discussions
, ,
1

Hi all,

I’m trying to replicate lazy (late-binding) view behavior in PostgreSQL using a custom dbt materialization — mainly to avoid tight coupling between views and underlying tables, like what’s possible in Snowflake or Redshift with WITH NO SCHEMA BINDING.

:bullseye: What I’m Doing

The materialization does the following:

  1. Executes the model SQL with LIMIT 0 to introspect the columns and types.
  2. Creates a PostgreSQL function like this:
create function my_model__func()
returns table (<col defs>) as $
begin
  return query
  <model_sql>;
end;
$ language plpgsql;
  1. Then creates a view with the model name that wraps the function:
create view my_model as
select * from my_model__func();

This lets me treat it like a normal view in downstream models or BI tools, but keeps the logic deferred until query time. The view and function remain in place even if source tables are dropped — errors only happen at runtime when the view is queried.

:red_question_mark: What I’d Like Feedback On

So far this is working well in practice — but before adopting it more broadly, I’d love your thoughts:

  • Is this a solid pattern to use in production, especially under moderate-to-heavy query load?
  • Are there any known downsides (e.g., performance, caching, planner behavior) to wrapping views around RETURNS TABLE functions?
  • Would you recommend any improvements to this approach — or are there better ways to achieve “late binding” behavior in PostgreSQL?
  • Any experience from others who have tried similar techniques?

Thanks in advance — I’d really appreciate any thoughts or cautions.