Request to validate an approach

Hello.

Your 3-node cluster of ra3.4xlarge instances will run out of storage in about 2 months (assuming linear ingestion and no optimizations/compressions). Redshift does support automatic compression which can reduce the storage requirements, but this varies based on the type of data. So, even with compression, you might not have enough storage for 3 months of data. You'd need to consider a larger cluster size or regularly offload older data to more cost-effective storage options like Amazon S3 using Redshift Spectrum.

Redshift with the use of Kinesis Firehose is quite powerful and can handle large-scale ingestion. However, the rate at which you're ingesting (6TB/day) is significant. While Kinesis Firehose is designed to handle large-scale streaming data, there will be a need for proper optimization. You'll need to batch the records appropriately, tune COPY commands, and ensure that the distribution style of the table is optimized for your ingestion patterns.

Having Materialized Views (MV) that auto-refresh, along with the high ingestion rate, can put a strain on the cluster. The performance of the cluster will depend on the complexity of the MVs, frequency of refresh, and other concurrent operations (like other queries and exports). Ensuring the queries are optimized, using distribution and sort keys effectively, and monitoring the query performance will be crucial.

If you plan to retain 2-3 months of data in the cluster, you'll need to implement a strategy for regular deletion of old data, given your high ingestion rate. Using time-based partitioning can help in more efficient data deletion.

Best regards, Andrii