Optimize your Queries

As your data grows, analytical queries can become increasingly slower. This is where materialized views help optimize performance. They allow you to define transformations on your data that are either applied at the time of insertion (incremental) or periodically (refreshable). API endpoints can then query these materialized pipes directly, resulting in significantly faster execution.

​

Incremental Materialization

Airfold’s materialized pipes support incremental data transformation by automatically ingesting new data as it arrives. This model ensures your pipeline only processes fresh data ideal for time-series logs, user activity streams, or continuously growing datasets, Enabling near-real-time aggregations and rollups

​

UI

Let’s say we want to get the signup, purchases and page_views metrics along with total_events from the web events data. we could run the query below to get the metrics per day.

SELECT
    toDate(timestamp) AS date,
    countState(event_id) AS total_events,
    countIfState(event_type = 'signup') AS signups,
    countIfState(event_type = 'purchase') AS purchases,
    countIfState(event_type = 'page_view') AS page_views
FROM web_events
GROUP BY date

This runs fine for smaller dataset, but as no. of rows increases, the above query will take large amount of time to query all the rows and calculate the metrics. We can use an incremental materialized pipe here, and store the aggregated data on a new source as the data is being inserted and have the API query the new source. This significantly decreases the number of rows queries and hence the time taken for the query to run.

​

CLI

To materialize a draft pipe, set the to field to the name of the target source.

Although materialized pipes themselves cannot be accessed (via FROM) by other pipes, the source they write to is accessible like any other source.

name: agg_logs_mv
description: 'Aggregate logs by level'
nodes:
    - load:
        description: Loading logs
        sql: select timestamp, level, file, message from logs
    - error:
        description: Aggregating logs by level
        sql: |
            select
                level,
                countState() as count
            from load
            group by level
to: logs_by_level

​

Refreshable Materialization

​

UI

Let’s consider an example, if we want to get the top_pages visited by unique visitors per page_url, we can do it by the following command.

SELECT
    page_url,
    count(event_id) AS total_visits,
    uniqExact(user_id) AS unique_visitors
FROM web_events
WHERE event_type = 'page_view'
GROUP BY page_url

This will eventually take a long time to load when the data increases, this computation should be performed on the whole dataset and hence we will use refreshable materialized pipe.

​

CLI

To create a refreshable materialized pipe using the CLI in Airfold, simply add the refresh block to the YAML definition of the incremental materialized pipe and fill in the refresh strategy and interval.

description: 'Aggregate logs by level'
nodes:
    - load:
        description: Loading logs
        sql: select timestamp, level, file, message from logs
    - error:
        description: Aggregating logs by level
        sql: |
            select
                level,
                countState() as count
            from load
            group by level
to: logs_by_level
refresh:
    strategy: replace
    interval: EVERY 1 MINUTE