When you work on relatively big Enterprise data warehouse (EDW), you will have a large number of tables with different structures. The table structure includes, column name, type of data type, distribution style or sort key use. Redshift does not provide show (list) or describe SQL command. Though, you can use psql command line options. In this article, we will check what are Redshift show and describe table command alternative with an examples. Redshift Show and Describe Table Command Alternative As mentioned earlier, the Redshift SQL reference does not provide…

Redshift do not have a primary or unique key. You can define primary, Foreign or unique key constraints, but Redshift will not enforce them. You can insert the duplicate records in the Redshift table. There are no constraints to ensure uniqueness or primary key, but if you have a table and have loaded data twice, then you can de-duplicate in several ways. Below methods explain you how to identify and Remove duplicate records from Redshift table. Remove Duplicate Records from Redshift Table There are many methods that you can use…

The cumulative sum or running total is one of the interesting problems where you have to calculate the sum or average using current result and previous row value. Most of the modern analytical database line Netezza, Teradata, Oracle, Vertica provides supports to analytical functions. You can make use of those analytical functions along with window specification to calculate the cumulative sum and average. In this article, we will check how to calculate Redshift Cumulative Sum (running total) or cumulative average with some examples. Redshift Cumulative Sum As explained earlier, cumulative…

Redshift lateral column alias sometimes called derived columns are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports lateral column alias. Recently Redshift also started supporting lateral column alias reference. In this post, lets us explore that new feature. Page Content Introduction Why Use Lateral Column Alias in Redshift? How…

The performance of the Redshift database is directly proportional to the optimal table design in your database. Optimizing Amazon Redshift table structure is very important aspect to speed up your data loading and unloading process. In this article, we will check out some tricks to optimize Redshift table design to improve performance. Optimize Redshift Table Design There is no specific set of rules to optimize Redshift table structure. Creating optimal table design is based on the type of data that you are about to load. However, here are some of…

When you work on large enterprise data warehouse, you will be working with multiple tables and those tables will be shared across multiple data marts within your application. Sometimes, users will explicitly lock the access to the table if they are refreshing or updating data. In this article, we will check how to identify and kill Redshift Table locks. Redshift Identify and Kill Table Locks You can use Redshift system tables to identify the table locks. One such table is STV_LOCKS, this table holds details about locks on tables in…

When you work in an enterprise data warehouse (EDW), you will work with multiple data sources. Sometimes you may have to derive the table to get only required columns instead of joining big table. This process not only improve the performance, but also avoid unnecessary memory usage. In this article, we will check how to improve the performance of Redshift query by creating derived tables with some examples. Amazon Redshift Derived Tables In the real world scenario, you may derive the some column from base table and instead of creating…