Configuring QDS Materialization for Query-Based Datasets
QDS materialization is a Public Preview feature.
You can configure query-based datasets (QDSs) to be materialized as AtScale-managed tables. Opting to materialize QDSs can help reduce processing time and costs associated with building aggregates based on the QDSs and querying them from BI and AI tools.
About QDS materialization
Query-based datasets (QDSs) allow you to select data from one or more tables in your data warehouse and save the query in AtScale as a new dataset. This enables you to manipulate your existing tables and select only the rows you are interested in for your model.
While convenient, QDSs can have some drawbacks:
- Building aggregates based on a QDS can take a long time and require a lot of resources, as each query involved must execute the QDS. If the QDS needs to scan and select from large tables, or if it is highly complex, this adds to aggregate build times and resource consumption.
- Inbound queries that do not hit aggregates may be routed to QDSs, resulting in slower processing and longer wait times for BI and AI tool users.
- Large or complex QDSs can run indefinitely during stats collection, resulting in timeouts.
If your models include QDSs and you have encountered these issues, you may want to enable QDS materialization for specific query-based datasets. QDS materialization converts your QDSs into physical, AtScale-managed tables, which can be used to reduce the total amount of data warehouse work done over the course of a day.
When not to enable QDS materialization
Although QDS materialization can reduce processing time and costs when building aggregates, they do have some associated costs:
- Because the resulting QDS tables are materialized, they take up some non-ephemeral space in the AtScale database.
- Materializing the tables requires upfront compute.
If you'd rather avoid these costs, you may not want to enable QDS materialization. Additionally, if you have data models that do not use aggregates, you might choose not to materialize QDSs.
About QDS table builds
AtScale performs initial QDS materialization when you deploy the model. The QDS table appears in the AtScale database with a name formatted as as_qds_<UUID>_<dataset_name_suffix>.
QDS materialization will fail if the underlying dataset has any security restrictions, such as row-access or masking policies, applied in the data warehouse.
AtScale automatically rebuilds QDS tables at the following times:
- When the model is redeployed.
- During aggregate maintenance.
- During aggregate batch rebuilds.
- During scheduled aggregate builds.
Enable and configure QDS materialization in the system
Before you can enable QDS materialization for your datasets, it must be enabled in AtScale. This is controlled by the qdsMaterialization.enabled global and model settings (default: true). Ensure this setting is enabled at either level.
You can further customize QDS materialization behavior with the following global settings.
| Setting Name | Description | Default Value |
|---|---|---|
qdsMaterialization.maxConcurrent | The maximum number of concurrent Create Table As Select (CTAS) executions allowed per model. | 4 |
qdsMaterialization.maxRetries | The maximum number of retry attempts per QDS on failure. | 3 |
qdsMaterialization.retryBackoff.initial | Initial backoff delay for retry attempts. | 1 minute |
qdsMaterialization.retryBackoff.multiplier | Backoff multiplier per retry. | 2.0 |
qdsMaterialization.retryBackoff.max | Maximum backoff delay. | 15 minutes |
qdsMaterialization.buildTimeout | Timeout per CTAS execution. | 90 minutes |
For more information on these settings, see QDS Materialization Settings and Other Model Settings.
Additionally, you can use the following global settings to configure QDS table names.
| Setting Name | Description | Default Value |
|---|---|---|
db.max.table.name.length | Controls the maximum length of the physical QDS table names. | 30 |
aggregate.snowflake.table.names.uppercase | For Snowflake data warehouses only. Determines whether uppercase letters are used in physical QDS table names. | false |
For more information on these settings, see Database Settings and Aggregate Settings.
Enable QDS materialization for your query-based datasets
Once QDS materialization has been enabled and configured in the system, you can enable it for your query-based datasets:
-
In Design Center, locate the query-based dataset you want to enable QDS materialization for and open it for editing. The Dataset properties panel opens.
-
In the Source section, enable Allow QDS Materialization:
-
Click Apply.
AtScale performs initial QDS materialization once you deploy the model. The QDS table appears in the AtScale database with a name formatted as as_qds_<UUID>_<dataset_name_suffix>.