Data Modeling & Architecture

To achieve the exceptional performance and cost advantages provided by Amazon Redshift, it all starts with a solid Redshift data model designed around current AWS Redshift best practices. Redshift has automated much of what used to be manual tuning work; a modern architecture knows when to trust that automation and when to override it. Our data modeling and architectural solutions include:

• Design and development of scalable petabyte DW solutions on provisioned RA3 clusters and Redshift Serverless workgroups
• Advanced architectural data models based on MPP design principles
• Distribution design: AUTO distribution with Automatic Table Optimization monitoring, and explicit KEY / ALL / EVEN distribution where a known join pattern wins
• Sorting strategy: SORTKEY AUTO by default, compound sort keys where manual control is justified by the query profile, and review of SVV_ALTER_TABLE_RECOMMENDATIONS before pinning anything
• Investigation of data skew and remediation based on the actual row distribution across slices
• Selection of column compression encodings (AZ64 and ZSTD first; legacy LZO and delta encodings replaced during migration)
• Concurrency design: Concurrency Scaling for provisioned clusters and RPU sizing for Serverless workgroups, instead of extra clusters
• Data sharing architecture: producer / consumer namespaces across accounts and regions so workloads are isolated without copying data
• Materialized views with auto-refresh and automatic query rewrite for the reporting layer
• Lakehouse modeling: Redshift Spectrum, Apache Iceberg and S3 Tables for cold or shared data, with Lake Formation permissions
• Constraint definition and configuration (primary and foreign keys as planner hints)
• Logical and physical data modeling

Read more about how we approach sort and distribution keys in the AUTO era.

...See More Redshift Services