ALTER MATERIALIZED VIEW SET REFRESH

Changes a materialized view's refresh strategy and parameters without recreating the view.

Syntax​

ALTER MATERIALIZED VIEW viewName SET REFRESH
[ IMMEDIATE | MANUAL | EVERY interval [ START timestamp ] [ TIME ZONE timezone ] ]
[ PERIOD ( LENGTH length [ TIME ZONE timezone ] [ DELAY delay ] ) ]
[ PERIOD ( SAMPLE BY INTERVAL ) ]

Parameters​

ParameterDescription
viewNameName of the materialized view to modify
IMMEDIATERefresh after each base table transaction
MANUALRefresh only when explicitly triggered
EVERY intervalRefresh on a timer (e.g., 10m, 1h, 1d)
START timestampWhen to begin the timer schedule
TIME ZONETimezone for schedule alignment
PERIOD LENGTHDefine fixed-length refresh periods
PERIOD SAMPLE BY INTERVALMatch period to the view's SAMPLE BY interval
DELAYGrace period before period closes

When to use​

Change refresh strategy when:

  • Switching from real-time (IMMEDIATE) to batched (EVERY) for performance
  • Adding period-based refresh for data that arrives at fixed intervals
  • Switching to MANUAL for full control during maintenance windows

Examples​

Switch to timer-based refresh​

Refresh every 12 hours
ALTER MATERIALIZED VIEW trades_hourly
SET REFRESH EVERY 12h START '2025-12-31T00:00:00Z' TIME ZONE 'Europe/London';

Add period-based refresh​

Daily periods with 1-hour delay for late data
ALTER MATERIALIZED VIEW trades_daily
SET REFRESH PERIOD (LENGTH 1d DELAY 1h);

Match period to SAMPLE BY​

Period matches view's aggregation interval
ALTER MATERIALIZED VIEW trades_hourly
SET REFRESH PERIOD (SAMPLE BY INTERVAL);

Switch to immediate refresh​

Real-time refresh
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH IMMEDIATE;

Switch to manual refresh​

Manual control
ALTER MATERIALIZED VIEW trades_hourly SET REFRESH MANUAL;

Behavior​

AspectDescription
Existing dataPreserved; only future refresh behavior changes
Pending refreshCompletes before new strategy takes effect
Timer resetEVERY schedule resets based on START time

Permissions (Enterprise)​

Changing refresh settings requires the ALTER MATERIALIZED VIEW permission:

Grant alter permission
GRANT ALTER MATERIALIZED VIEW ON trades_hourly TO user1;

Errors​

ErrorCause
materialized view does not existView with specified name doesn't exist
invalid intervalTimer interval is invalid or below minimum (1m)
permission deniedMissing ALTER MATERIALIZED VIEW permission (Enterprise)

See also​