Scale analytics with Amazon Redshift multi-warehouse enhancements

Onboard analytics workloads at scale with Amazon Redshift’s improved distant desk information definition language (DDL), materialized view enhancements, and concurrency scaling enhancements for zero-ETL and auto-copy.

As organizations scale their analytics capabilities, they want the flexibility so as to add workloads with out disrupting manufacturing operation or being constrained by the sources of a single information warehouse. On this submit, we introduce new capabilities of Amazon Redshift that improve our multi-warehouse and scaling capabilities: distant materialized view (MV) operations, distant desk DDL assist, and concurrency scaling enhancements for zero-ETL and S3 occasion integration. These options enable you to construct extra scalable, performant decentralized analytics architectures on Amazon Redshift.

Allow us to evaluation how these new options allow you to run analytics at scale.

New distant materialized view operations

New distant desk DDL operations

• ALTER TABLE ALTER DISTSTYLE operations now work on distant warehouses by concurrency scaling and information sharing. You’ll be able to dynamically optimize information distribution throughout distributed environments, bettering question efficiency and useful resource utilization with out requiring information migration. That is particularly useful for information engineers fine-tuning efficiency throughout a number of warehouses and directors adapting to altering question patterns.
• ALTER TABLE APPEND operations now prolong to distant warehouses by concurrency scaling and information sharing. This consolidates information throughout distributed environments, so you may effectively mix tables with out complicated information motion or extract, rework, and cargo (ETL) processes. Organizations managing dynamic desk operations throughout a number of environments can keep information consistency whereas decreasing operational overhead.

Concurrency scaling enhancements

With these new concurrency scaling capabilities, you may keep constant information freshness with out compromising present warehouse efficiency. This eliminates the normal trade-off between analytics and information loading. Aside from turning on concurrency scaling, no extra adjustments are required to benefit from these options.

Buyer use circumstances

This part covers two trade use circumstances: the primary for a monetary companies buyer and the second for a gaming trade buyer.

Monetary companies use case

The next is a pattern structure for a big monetary companies buyer with international operations. This buyer makes use of a multi-warehouse structure constructed on Amazon Redshift.

The staging (STG) warehouse serves as a uncooked zone for information from numerous sources, just like the bronze layer of a medallion structure. This warehouse additionally cleanses and standardizes the uncooked information to the silver layer and makes it out there for additional processing. The STG warehouse makes use of MVs to course of tens of millions of nested JSON messages and extract attributes into scalar columnar Amazon Redshift tables.

CREATE MATERIALIZED VIEW rawdb.fsi.customer_orders_raw
distkey(c_custkey) sortkey(c_custkey) AS (
    SELECT c_custkey,
        o.o_orderstatus,
        o.o_totalprice,
        o_idx
    FROM customer_orders_lineitem c,
        c.c_orders o AT o_idx
);
REFRESH MATERIALIZED VIEW rawdb.fsi.customer_orders_raw;

The DWH warehouse serves as the first Amazon Redshift occasion and gold layer, offering information to consuming functions like Enterprise Objects and Tableau. The zero-ETL concurrency scaling enhancements present constant information freshness even when zero-ETL ingestion spikes happen alongside heavy DWH workloads. The DWH MVs present quick entry to aggregated information for Tableau extracts and Enterprise Objects dwell experiences. The DWH warehouse takes benefit of concurrency scaling when a number of MVs should be refreshed on the DWH occasion.

CREATE MATERIALIZED VIEW bodb.closing.customer_churn_tbl
AS (
    SELECT state,
        account_length,
        area_code,
        total_charge/account_length AS average_daily_spend,
        cust_serv_calls/account_length AS average_daily_cases,
        churn
    FROM custdb.closing.customer_activity_all
);
REFRESH MATERIALIZED VIEW bodb.closing.customer_churn_tbl;

The ETL01/02 warehouses function devoted compute environments for operating project-specific ETL jobs, whereas the USR01/02 warehouses deal with consumer workloads resembling ad-hoc evaluation or mannequin constructing from dbt. When new objects are required by consumer workloads, they’re created and maintained on the distant producer warehouse (DWH).

ALTER TABLE salesdb.closing.sales_report_all
ALTER DISTKEY sales_id;

ALTER TABLE APPEND salesdb.closing.sales_report_all
FROM stagingdb.gross sales.sales_2026_02;

Gaming trade use case

A number one gaming firm has constructed their complete analytics infrastructure on AWS, with their analytics crew managing information streaming from video games, information warehousing, and enterprise intelligence instruments. They standardized Amazon Redshift throughout the group, migrating off Vertica operating on Amazon Elastic Compute Cloud (Amazon EC2). After overcoming early challenges with cluster resize operations, the crew grew to become sturdy advocates for Amazon Redshift and now runs their major manufacturing cluster on 32 ra3.16xlarge nodes.

As their information ingestion pipeline grew, question workloads started competing with information ingestion processes, creating efficiency bottlenecks. Somewhat than scaling up their major cluster, they applied a workload isolation technique utilizing Amazon Redshift information sharing. The shopper launched a second 16-node ra3.4xlarge cluster as an information share shopper, with the first cluster serving because the producer. This structure allowed them emigrate consumption workloads to the patron cluster whereas the producer centered on information ingestion, successfully supporting progress with out growing the first cluster dimension.

Recognizing some great benefits of this distributed structure, the gaming firm expanded their method by migrating workloads to Amazon Redshift Serverless, additional utilizing the info sharing mannequin for workload isolation. Amazon Redshift’s distant materialized view functionality allowed the gaming firm to create materialized views straight on the info shared by the producer cluster. Every shopper cluster might now construct materialized views optimized for its particular workload patterns. This created pre-aggregated datasets, customized be part of methods, and workload-specific information distributions, with out impacting the producer cluster’s efficiency or requiring information duplication. The producer warehouse maintains information distribution and sorting methods designed for generic enterprise wants, offering constant information high quality throughout all customers. In the meantime, shopper warehouses used distant materialized views to fine-tune question efficiency for his or her distinct analytical necessities, whether or not supporting real-time participant analytics, enterprise intelligence dashboards, or ad-hoc information science workloads. This distributed method to information consumption optimization proved important for the gaming firm. It delivered quick question efficiency throughout numerous analytical workloads whereas sustaining a single supply of fact within the producer cluster and avoiding the operational overhead of managing redundant information copies.

Greatest practices

To get probably the most out of those new capabilities, take into account the next finest practices:

• Allow concurrency scaling in your Amazon Redshift clusters and Serverless workgroups to permit ETLs and consumer queries to run even quicker, offering constant report and dashboard efficiency.
• Arrange utilization limits for concurrency scaling on each Amazon Redshift provisioned clusters and Serverless workgroups by configuring an acceptable MaxRPU setting. This helps you keep away from surprising extra prices. For extra data, see the Amazon Redshift utilization limits documentation.
• Use distant MVs to dump resource-intensive MV creation and refresh operations out of your major warehouse to distant information share clusters.

Conclusion

On this submit, we walked by the brand new MV refresh options, distant desk DDL capabilities, and expanded concurrency scaling assist for zero-ETL and S3 auto-copy. These options enable you to transfer past the constraints of a single warehouse. They’re significantly useful for organizations managing distributed information architectures that require dynamic desk administration throughout a number of environments whereas sustaining information consistency and adapting shortly to altering workloads. To get began, ensure you are operating the most recent Amazon Redshift model. Then go to the Amazon Redshift documentation to study extra about concurrency scaling, information sharing, and materialized views.

Concerning the authors