Note that Redshift doesn't use indexes or partitions as I am used to seeing them in relational databases so, in many ways, table definition is a lot simpler. For each query I am summarizing 5 facts from the main fact table, joining to each of the master tables and using a variety of filters to restrict the records I want to aggregate over. Test setupįor this test, I am using the same database as before (simulated Point of Sale data at item-store-week level with item, store and calendar master tables) on 4 'dw1.xlarge' AWS nodes. In this follow-up post then, let's look at how Redshift performs when we want to aggregate across particular records.

In this initial testing I was aggregating the entire fact table to get comparable tests to the previous benchmark, but that's typically not how a reporting (or analytic) system would access the data.

RedShift performed very well indeed, especially so as the number of facts returned by the queries increased. Additionally, late binding views can be used with external tables via Redshift Spectrum.In my recent post on 'The right tools (structured) BIG DATA handling', I looked at using AWS Redshift to generate summaries from a large fact table and compared it to previous benchmark results using a columnar database on a fast, SSD drive. Using late-binding views in a production deployment of dbt can vastly improve the availability of data in the warehouse, especially for models that are materialized as late-binding views and are queried by end-users, since they won’t be dropped when upstream models are updated. In practice, this means that if upstream views or tables are dropped with a cascade qualifier, the late-binding view does not get dropped as well. This DDL option 'unbinds' a view from the data it selects from. Redshift supports views unbound from their dependencies, or late binding views. AWS Documentation » Amazon Redshift » Database Developer Guide » Designing Tables » Choosing Sort Keys.AWS Documentation » Amazon Redshift » Database Developer Guide » Designing Tables » Choosing a Data Distribution Style.sql files, eg:įor more information on distkeys and sortkeys, view Amazon's docs: Sort and dist keys should be added to the block in model. if no setting is specified, sort_type defaults to compound. sort_type can have a setting of interleaved or compound.dbt will build the sort key in the same order the fields are supplied. sort accepts a list of sort keys, for example.dist can have a setting of all, even, auto, or the name of a key.Note that these settings will have no effect for models set to view or ephemeral models. Supplying these values as model-level configurations apply the corresponding settings in the generated CREATE TABLE DDL. Tables in Amazon Redshift have two powerful optimizations to improve query performance: distkeys and sortkeys. Performance optimizations Using sortkey and distkey In dbt-redshift, the following incremental materialization strategies are supported:Īll of these strategies are inheirited via from dbt-postgres. Redshift configurations Incremental materialization strategies

Redshift sortkey and distkey