dbt: How to manage concurrent access to materialized views

⚙️ Why `CONCURRENTLY` for Materialized Views?

When refreshing a materialized view in a data warehouse like PostgreSQL, the default behavior is:

❌ Exclusive lock on the view during REFRESH MATERIALIZED VIEW, which blocks readers until the refresh is complete.

That’s a problem if:

Your view is large and takes time to refresh.
Users/apps need uninterrupted access to that view.

✅ Solution: `REFRESH MATERIALIZED VIEW CONCURRENTLY`

This tells the database to:

Build a new, temporary copy of the view in the background.
Only atomically swap it in when the refresh is complete.
✅ Allow reads to continue without being blocked.

📦 Example: Using `CONCURRENTLY` in a dbt Custom Materialization

Let’s say you have a model customer_summary that’s materialized as a materialized view.

✅ Step 1: Create a custom materialization

In macros/materializations/materialized_view_concurrent.sql:

{% materialization materialized_view_concurrent, default %}

  {% set relation = api.Relation.create(
      identifier=model['alias'],
      schema=model['schema'],
      database=model['database'],
      type='materializedview'
  ) %}

  {% set is_incremental = false %}  {# Materialized views don’t support incremental builds #}

  {% if not adapter.get_relation(database=relation.database, schema=relation.schema, identifier=relation.identifier) %}
    -- First time: create the materialized view
    {% do run_query("CREATE MATERIALIZED VIEW " ~ relation ~ " AS (" ~ sql ~ ")") %}
  {% else %}
    -- Refresh using CONCURRENTLY
    {% do run_query("REFRESH MATERIALIZED VIEW CONCURRENTLY " ~ relation) %}
  {% endif %}

  {{ return({'relations': [relation]}) }}

{% endmaterialization %}

✅ Step 2: Use it in your model

-- models/customer_summary.sql

{{ config(
    materialized='materialized_view_concurrent'
) }}

select
  customer_id,
  count(*) as order_count,
  sum(order_amount) as total_spent
from {{ ref('orders') }}
group by customer_id

⚠️ Important Notes

PostgreSQL requires a unique index on the materialized view to use CONCURRENTLY.
You can add it using a post-hook:

models:
  - name: customer_summary
    config:
      post-hook:
        - "CREATE UNIQUE INDEX IF NOT EXISTS idx_customer_summary_id ON {{ this }}(customer_id)"

✅ Benefits of `CONCURRENTLY`

Feature	Description
🔓 Non-blocking	Readers are never blocked during refresh
♻️ Atomic swap	Ensures consistent state at all times
📈 Scales to large views	Avoids downtime during long-running refreshes
🧰 Integrates with dbt hooks	Works cleanly inside dbt materializations

🧼 Summary

Use REFRESH MATERIALIZED VIEW CONCURRENTLY when data freshness and availability are both critical.
In dbt, implement this via a custom materialization.
Add required indexes to enable the concurrent refresh.

This is especially useful in production dashboards or reporting tools that depend on high availability of your dbt-generated views.

⚙️ Why CONCURRENTLY for Materialized Views?

✅ Solution: REFRESH MATERIALIZED VIEW CONCURRENTLY

📦 Example: Using CONCURRENTLY in a dbt Custom Materialization