@valiro21 ,

You are correct, the behaviour will be as described in the Option 4.Because an MV is a declarative object, it must reflect the results of the query as if it were run from scratch. If your WHERE clause limits data to the last 12 months, any data older than 12 months will be deleted from the MV during the refresh to maintain consistency with the source.

The recommended pattern

Typically with SDP, you don't need to define "last 12 months", as it will be automatically handled by the incremental refresh. With the below approach:

CREATE MATERIALIZED VIEW mv_monthly_median AS
SELECT
customer_id,
date_trunc('month', timestamp) AS month,
percentile_approx(value, 0.5) AS median_value
FROM time_series_table
GROUP BY
customer_id,
date_trunc('month', timestamp);

The MV will naturally accumulate all history (14, 20, 24 months, etc.). MVs use incremental refreshing. It will only process new data as it arrives and never touch those old months (unless there is late arrival data). Simply apply the WHERE month >= ... filter in your downstream BI tool or Gold-layer view. This keeps your storage logic simple and your compute efficient.

2. Other questions:

• Databricks Asset Bundles (DABs) is the gold standard for managing MVs across Dev/QAS/Prod environments.
• SDP (MVs and STs) are available in both SQL and Python
• Changing the MV formatting or table aliases within the query does not trigger a full refresh. Any change to the query logic itself (i.e., adding new column, changing group or aggregations, renaming columns) will trigger a full refresh.
• To avoid a massive recomputation when logic changes, you can use the APPEND ONCE FLOW pattern. You create a new version of the MV, migrate existing data into it, and then allow the incremental refresh to take over for new data.
  
  • Create a new MV with new logic
  • Copy and modify the data from old MV to the new MV with APPEND ONCE 
  • Run refresh to get new data with the new logic

Best regards,