E-MapReduce:Use materialized views for data modeling

Why use materialized views for data modeling

Raw data often suffers from low quality, overly complex metrics, or prohibitive query costs due to missing pre-aggregation. Modeling the raw data addresses these problems, but keeping the modeling process in sync with rapidly changing business requirements is difficult — and the return on investment is hard to justify in the early stages of a business.

StarRocks asynchronous materialized views address these problems through three capabilities:

• Simplified data warehouse architecture: StarRocks provides a one-stop data governance experience, eliminating the need to maintain separate data processing systems or components.

• Simplified modeling experience: Any data analyst with basic SQL knowledge can build data models — no professional data engineers required.

• Simplified system maintenance: Asynchronous materialized views automatically manage the dependencies and refresh order across data layers.

In practice, combine StarRocks logical views and asynchronous materialized views across three layers:

1. Intermediate layer: Use views to join real-time data with dimension tables; use materialized views to join historical data lake data with dimension tables. Apply data cleansing and business semantic mapping at this layer.

2. Application layer: Run Join, Agg, Union, and Window calculations for specific business scenarios. Use views for real-time links and materialized views for near real-time links.

3. Analytical data storage (ADS): Select the appropriate storage based on timeliness and performance requirements. This layer serves real-time dashboards, near real-time BI, ad hoc queries, and scheduled reports.

Views vs. materialized views

StarRocks data modeling combines logical views and asynchronous materialized views. Understanding when to use each is essential.

• Views do not store physical data. Every query against a view is parsed and executed from scratch. Views are well suited for expressing business semantics and simplifying SQL, but they do not reduce query execution overhead.

• Materialized views store pre-computed results, avoiding repeated computation. They optimize query performance and simplify ETL pipelines, at the cost of storage overhead and update latency.

Capabilities of asynchronous materialized views

StarRocks asynchronous materialized views provide four atomic capabilities for data modeling:

• Auto-refresh: Materialized views refresh automatically when data is imported into base tables — no external scheduling required.

• Partition refresh: For time-series data, partition-level refresh delivers near real-time computing without refreshing the entire materialized view.

• Collaborative use with views: Combine materialized views and logical views to implement multi-layer modeling, enabling intermediate layer reuse and simpler data models.

• Schema Change: Modify computation results with a simple SQL statement, without touching complex data pipelines.

CREATE MATERIALIZED VIEW <db_name>.<mv_name>
REFRESH
    [ ASYNC |
      ASYNC [START <time>] EVERY(<interval>) |
      MANUAL
    ]
AS <query>

CREATE MATERIALIZED VIEW <db_name>.<mv_name>
REFRESH ASYNC
PARTITION BY
    [
        <base_table_column> |
        date_trunc(<granularity>, <base_table_column>) |
        time_slice(<base_table_column>, <granularity>) |
        date_slice(<base_table_column>, <granularity>)
    ]
AS <query>

-- Modify a base table.
ALTER TABLE <db_name>.<table_name> ADD COLUMN <column_desc>;

-- Atomically replace a base table.
ALTER TABLE <db_name>.<table1> SWAP WITH <table2>;

-- Modify a view definition.
ALTER VIEW <db_name>.<view_name> AS <query>;

-- Atomically replace a materialized view (swaps names without modifying data).
ALTER MATERIALIZED VIEW <db_name>.<mv1> SWAP WITH <mv2>;

-- Re-enable a materialized view.
ALTER MATERIALIZED VIEW <db_name>.<mv_name> ACTIVE;

Hierarchical modeling

Real-world data sources include real-time detailed data, dimension data, and archived data in data lakes. Business requirements span real-time dashboards, near real-time BI, ad hoc queries, and scheduled reports. These scenarios have different requirements — some prioritize flexibility, others performance, and others cost.

No single approach meets all these needs. StarRocks uses a combination of views and materialized views to address each layer efficiently.

Partition modeling

In addition to hierarchical modeling, partition modeling controls how fact table data is associated and how long it is retained. Most partition-based models follow a star schema or snowflake schema pattern, with fact tables and dimension tables as the core building blocks. StarRocks materialized views support fact table partition association: when a fact table is partitioned, the Join results in the materialized view follow the same partition structure.

Key behaviors of partition association:

• Specify the partition key of the fact table in the materialized view using PARTITION BY fact_tbl.col. A materialized view can associate with only one base table.

• When data in a specific partition of the associated base table changes, only the corresponding partition in the materialized view is refreshed.

• When non-associated base tables (typically dimension tables) change, the entire materialized view is refreshed by default. To avoid this, configure excluded_trigger_tables to ignore changes from specific tables.