1. Home
  2. Big Data
  3. Amazon Redshift
  4. Amazon Redshift Architecture and SQL Training

Amazon Redshift Architecture and SQL Training

  4.6 out of 5 based on 56 Ratings

In this training class, students will learn the Amazon Redshift Architecture and SQL starting at the most basic level and going to the most advanced level with many examples.

Goals
  1. Learn to gain a deeper knowledge and understanding of the Amazon Redshift Architecture and SQL and how to write it.
Outline
  1. What is Columnar?
    1. What is Parallel Processing?
    2. The Basics of a Single Computer
    3. Data in Memory is Fast as Lightning
    4. Parallel Processing Of Data
    5. A Table has Columns and Rows
    6. Each Parallel Process Organizes the Rows inside a Data Block
    7. Moving Data Blocks is Like Checking In Luggage
    8. Facts That Are Disturbing
    9. Why Columnar?
    10. Row Based Blocks vs. Columnar Based Blocks
    11. As Row-Based Tables Get Bigger, the Blocks Split
    12. Data Blocks Are Processed One at a Time Per Unit
    13. Columnar Tables Store Each Column in Separate Blocks
    14. Visualize the Data: Rows vs. Columns
    15. The Architecture of Redshift
    16. Redshift has Linear Scalability
    17. Distribution Styles
    18. Distribution Key Where the Data is Unique
    19. Another Way to Create A Table
    20. Distribution Key Where the Data is Non-Unique
    21. Distribution Key is ALL
    22. Even Distribution Key
    23. Matching Distribution Keys for Co-Location of Joins
    24. Big Table / Small Table Joins
    25. Fact and Dimension Table Distribution Key Designs
    26. Improving Performance By Defining a Sort Key
    27. Sort Keys Help Group By, Order By and Window Functions
    28. Each Block Comes With Metadata
    29. How Data Might Look On A Slice
    30. Question: How Many Blocks Move Into Memory?
    31. The ANALYZE Command Collects Statistics
    32. Redshift Automatically ANALYZES Some Create Statements
    33. What is a Vacuum?
    34. When is a Good Time to Vacuum?
    35. The VACUUM Command Grooms a Table
    36. Database Limits
    37. Creating a Database
    38. Creating a User
    39. Dropping a User
    40. Inserting Into a Table
    41. Renaming a Table or a Column
    42. Adding and Dropping a Column to a Table
  2. Best Practices For Table Design
    1. Converting Table Structures to Redshift
    2. Converting Table Structures to Redshift Finale
    3. Best Practices for Designing Tables
    4. Choose the Best Sort Key
    5. Each Block Comes With Metadata
    6. Creating a Sort Key
    7. Sort Keys Help Group By, Order By and Window Functions
    8. Choose a Great Distribution Key
    9. Distribution Key Where the Data is Unique
    10. Matching Distribution Keys for Co-Location of Joins
    11. Big Table / Small Table Joins
    12. Define Primary Key and Foreign Key Constraints
    13. Primary Key and Foreign Key Examples
    14. Use The Smallest Column Size When Creating Tables
    15. Use Date/Time Data Types for Date Columns
    16. Specify Redundant Predicates on the Sort Column
    17. Setting the Statement_Timeout to Abort Long Queries
  3. System Tables
    1. Amazon Redshift System Tables
    2. Trouble Shooting Catalog Table pg_table_def
    3. Seeing the System Tables in your Nexus Tree
    4. Catalog Table pg_table_def
    5. Checking Tables for Skew (Poor Distribution)
    6. Checking All Statements That Used the Analyze Command
    7. Checking Tables for Skew (Poor Distribution)
    8. Checking For Details About the Last Copy Operation
    9. Checking When a Table Has Last Been Analyzed
    10. Checking For Column Information on a Table
    11. System tables for troubleshooting data loads
    12. Determining Whether a Query is Writing to Disk
  4. Compression
    1. Compression Types
    2. Byte Dictionary Compression
    3. Delta Encoding
    4. LZO Encoding
    5. Mostly Encoding
    6. Runlength encoding
    7. Text255 and Text32k Encodings
    8. ANALYZE COMPRESSION
    9. Copy
  5. Temporary Tables
    1. Create Table Syntax
    2. Basic Temporary Table Examples
    3. More Advanced Temporary Table Examples
    4. Advanced Temporary Table Examples
    5. Table Limits and CTAS
    6. Performing a Deep Copy
    7. Deep Copy Using the Original DDL
    8. Deep Copy Using A CTAS
    9. Deep Copy Using A Create Table LIKE
    10. Deep Copy By Creating a Temp Table and Truncating Original
    11. CREATING A Derived Table
    12. The Three Components of a Derived Table
    13. Naming the Derived Table
    14. Aliasing the Column Names in The Derived Table
    15. Visualize This Derived Table
    16. Most Derived Tables Are Used To Join To Other Tables
    17. Multiple Ways to Alias the Columns in a Derived Table
    18. Our Join Example With A Different Column Aliasing Style
    19. Column Aliasing Can Default For Normal Columns
    20. CREATING A Derived Table using the WITH Command
    21. Our Join Example With The WITH Syntax
    22. WITH Statement That Uses a SELECT *
    23. A WITH Clause That Produces Two Tables
    24. The Same Derived Query shown Three Different Ways
    25. Clever Tricks on Aliasing Columns in a Derived Table
    26. A Derived Table lives only for the lifetime of a single query
    27. An Example of Two Derived Tables in a Single Query
    28. Connecting To Redshift Via Nexus
  6. Explain
    1. Three Ways to Run an EXPLAIN
    2. EXPLAIN: Steps, Segments and Streams
    3. EXPLAIN Terms For Scans and Joins
    4. EXPLAIN Terms For Aggregation and Sorts
    5. EXPLAIN Terms For Set Operators and Miscellaneous Terms
    6. EXPLAIN Terms For Set Operators and Miscellaneous Terms
    7. EXPLAIN Example and the Cost
    8. EXPLAIN Example and the Rows
    9. EXPLAIN Example and the Width
    10. Simple EXPLAIN Example and the Costs
    11. EXPLAIN Join Example Using DS_BCAST_INNER
    12. EXPLAIN Join Example Using DS_DIST_NONE
    13. EXPLAIN Showing DS_DIST_NONE Visually
    14. EXPLAIN With a Warning
    15. EXPLAIN For Ordered Analytics Such as CSUM
    16. EXPLAIN For Scalar Aggregate Functions
    17. EXPLAIN For HashAggregate Functions
    18. EXPLAIN Using Limit, Merge and Sort
    19. EXPLAIN Using a WHERE Clause Filter
    20. EXPLAIN Using the Keyword Distinct
    21. EXPLAIN for Subqueries
  7. Basic SQL Functions
    1. Finding the Current Schema on the Leader Node
    2. Getting Things Setup in Your Search Path
    3. Five Details You Need To Know About The Search_Path
    4. Introduction
    5. SELECT * (All Columns) in a Table
    6. SELECT Specific Columns in a Table
    7. Commas in the Front or Back?
    8. Place your Commas in front for better Debugging Capabilities
    9. Sort the Data with the ORDER BY Keyword
    10. ORDER BY Defaults to Ascending
    11. Use the Name or the Number in your ORDER BY Statement
    12. Two Examples of ORDER BY using Different Techniques
    13. Changing the ORDER BY to Descending Order
    14. NULL Values sort First in Ascending Mode (Default)
    15. NULL Values sort Last in Descending Mode (DESC)
    16. Major Sort vs. Minor Sorts
    17. Multiple Sort Keys using Names vs. Numbers
    18. Sorts are Alphabetical, NOT Logical
    19. Using A CASE Statement to Sort Logically
    20. How to ALIAS a Column Name
    21. A Missing Comma can by Mistake become an Alias
    22. Comments using Double Dashes are Single Line Comments
    23. Comments for Multi-Lines
    24. Comments for Multi-Lines As Double Dashes Per Line
    25. A Great Technique for Comments to Look for SQL Errors
  8. The WHERE Clause
    1. Using Limit to bring back a Sample
    2. Using Limit With an Order By Statement
    3. The WHERE Clause limits Returning Rows
    4. Using a Column ALIAS throughout the SQL
    5. Double Quoted Aliases are for Reserved Words and Spaces
    6. Character Data needs Single Quotes in the WHERE Clause
    7. Character Data needs Single Quotes, but Numbers Don't
    8. NULL means UNKNOWN DATA so Equal (=) won't Work
    9. Use IS NULL or IS NOT NULL when dealing with NULLs
    10. NULL is UNKNOWN DATA so NOT Equal won't Work
    11. Use IS NULL or IS NOT NULL when dealing with NULLs
    12. Using Greater Than Or Equal To (>=)
    13. AND in the WHERE Clause
    14. Troubleshooting AND
    15. OR in the WHERE Clause
    16. Troubleshooting Or
    17. Troubleshooting Character Data
    18. Using Different Columns in an AND Statement
    19. Quiz: How many rows will return?
    20. What is the Order of Precedence?
    21. Using Parentheses to change the Order of Precedence
    22. Using an IN List in place of OR
    23. The IN List is an Excellent Technique
    24. IN List vs. OR brings the same Results
    25. Using a NOT IN List
    26. A Technique for Handling Nulls with a NOT IN List
    27. Another Technique for Handling Nulls with a NOT IN List
    28. BETWEEN is Inclusive
    29. NOT BETWEEN is Also Inclusive
    30. LIKE command Underscore is Wildcard for one Character
    31. LIKE Command Works Differently on Char Vs Varchar
    32. The Ilike Command Is NOT Case Sensitive
    33. Troubleshooting LIKE Command on Character Data
    34. Introducing the TRIM Command
    35. Numbers are Right Justified and Character Data is Left
    36. An Example of Data with Left and Right Justification
    37. A Visual of CHARACTER Data vs. VARCHAR Data
    38. Use the TRIM command to remove spaces on CHAR Data
    39. Like and Your Escape Character of Choice
    40. Like and the Default Escape Character
    41. Similar To Operators
    42. Similar To Operators
    43. Similar To Example With Lower Case Letters
    44. Similar To Example With Lower and Upper Case Letters
    45. Similar To Example With Multiple Occurrences
    46. Multiple Occurrences Must Be Consecutive
  9. Distinct Vs Group By AND TOP
    1. The Distinct Command
    2. Distinct vs. GROUP BY
    3. TOP Command
    4. TOP Command is brilliant when ORDER BY is Used!
    5. What is the Difference Between TOP and LIMIT?
  10. Aggregation
    1. The 3 Rules of Aggregation
    2. There are Five Aggregates
    3. Troubleshooting Aggregates
    4. GROUP BY when Aggregates and Normal Columns Mix
    5. GROUP BY Delivers one row per Group
    6. GROUP BY Dept_No or GROUP BY 1 the same thing
    7. Limiting Rows and Improving Performance with WHERE
    8. WHERE Clause in Aggregation limits unneeded Calculations
    9. Keyword HAVING tests Aggregates after they are Totaled
    10. Keyword HAVING is like an Extra WHERE Clause for Totals
  11. Join Functions
    1. A Two-Table Join Using Traditional Syntax
    2. A two-table join using Non-ANSI Syntax with Table Alias
    3. You Can Fully Qualify All Columns
    4. A two-table join using ANSI Syntax
    5. Both Queries have the same Results and Performance
    6. LEFT OUTER JOIN
    7. LEFT OUTER JOIN Results
    8. Left Outer Joins Compatible with Oracle
    9. RIGHT OUTER JOIN
    10. RIGHT OUTER JOIN Example and Results
    11. Right Outer Joins Compatible with Oracle
    12. FULL OUTER JOIN
    13. FULL OUTER JOIN Results
    14. Which Tables are the Left and Which are the Right?
    15. INNER JOIN with Additional AND Clause
    16. ANSI INNER JOIN with Additional AND Clause
    17. ANSI INNER JOIN with Additional WHERE Clause
    18. OUTER JOIN with Additional WHERE Clause
    19. OUTER JOIN with Additional AND Clause
    20. OUTER JOIN with Additional AND Clause Results
    21. The DREADED Product Join
    22. The DREADED Product Join Results
    23. The Horrifying Cartesian Product Join
    24. The ANSI Cartesian Join will ERROR
    25. The CROSS JOIN
    26. The CROSS JOIN Answer Set
    27. The Self Join
    28. The Self Join with ANSI Syntax
  12. Date Functions
    1. Current_Date
    2. TIMEOFDAY()
    3. SYSDATE Returns a Timestamp With Microseconds
    4. GETDATE Returns a Timestamp Without Microseconds
    5. Add or Subtract Days from a date
    6. The ADD_MONTHS Command Returns a Timestamp
    7. The ADD_MONTHS Command With Trunc Removes Time
    8. ADD_MONTHS Command to Add 1-Year or 5-Years
    9. Dateadd Function And Add_Months Function are Different
    10. The EXTRACT Command
    11. EXTRACT from DATES and TIME
    12. EXTRACT with DATE and TIME Literals
    13. EXTRACT of the Month on Aggregate Queries
    14. The Datediff command
    15. The Datediff Function on Column Data
    16. The Date_Part Function Using a Date
    17. The Date_Part Function Using a Time
    18. Date_Part Abbreviations
    19. The to_char command
    20. Conversion Functions
    21. Conversion Function Templates
    22. Conversion Function Templates Continued
    23. Formatting A Date
    24. A Summary of Math Operations on Dates
    25. Using a Math Operation to find your Age in Years
    26. Date Related Functions
    27. A Side Title example with Reserved Words as an Alias
    28. Implied Extract of Day, Month and Year
    29. DATE_PART Function
    30. DATE_PART Function using an ALIAS
    31. DATE_TRUNC Function
    32. DATE_TRUNC Function using TIME
    33. MONTHS_BETWEEN Function
    34. MONTHS_BETWEEN Function in Action
    35. ANSI TIME
    36. ANSI TIMESTAMP
    37. Redshift TIMESTAMP Function
    38. Redshift TO_TIMESTAMP Function
    39. Redshift NOW() Function
    40. Redshift TIMEOFDAY Function
    41. Redshift AGE Function
    42. Time Zones
    43. Setting Time Zones
    44. Using Time Zones
    45. Intervals for Date, Time and Timestamp
    46. Using Intervals
    47. Troubleshooting The Basics of a Simple Interval
    48. Interval Arithmetic Results
    49. A Date Interval Example
    50. A Time Interval Example
    51. A DATE Interval Example
    52. A Complex Time Interval Example using CAST
    53. A Complex Time Interval Example using CAST
    54. The OVERLAPS Command
    55. An OVERLAPS Example that Returns No Rows
    56. The OVERLAPS Command using TIME
    57. The OVERLAPS Command using a NULL Value
  13. OLAP Functions
    1. CSUM
    2. CSUM: The Sort Explained
    3. CSUM: Rows Unbounded Preceding Explained
    4. CSUM: Making Sense of the Data
    5. CSUM: Making Even More Sense of the Data
    6. CSUM: The Major and Minor Sort Key(s)
    7. Reset with a PARTITION BY Statement
    8. PARTITION BY only Resets a Single OLAP not ALL of them
    9. ANSI Moving Window is Current Row and Preceding n Rows
    10. How ANSI Moving SUM Handles the Sort
    11. Moving SUM every 3-rows Vs a Continuous Average
    12. Partition By Resets an ANSI OLAP
    13. Moving Average
    14. The Moving Window is Current Row and Preceding
    15. How Moving Average Handles the Sort
    16. Moving Average every 3-rows Vs a Continuous Average
    17. Partition By Resets an ANSI OLAP
    18. RANK Defaults to Ascending Order
    19. RANK Defaults to Ascending Order
    20. Getting RANK to Sort in DESC Order
    21. RANK() OVER and PARTITION BY
    22. RANK() OVER And LIMIT
    23. PERCENT_RANK() OVER
    24. PERCENT_RANK() OVER with 14 rows in Calculation
    25. PERCENT_RANK() OVER with 21 rows in Calculation
    26. COUNT OVER for a Sequential Number
    27. The MAX OVER Command
    28. MAX OVER with PARTITION BY Reset
    29. The MIN OVER Command
    30. The Row_Number Command
    31. Standard Deviation Functions Using STDDEV / OVER
    32. Standard Deviation Functions and STDDEV / OVER Syntax
    33. STDDEV / OVER Example
    34. VARIANCE / OVER Syntax
    35. Variance Functions Using VARIANCE / OVER
    36. Using VARIANCE with PARTITION BY Example
    37. Using FIRST_VALUE and LAST_VALUE
    38. Using FIRST_VALUE
    39. Using LAST_VALUE
    40. Using LAG and LEAD
    41. Using LEAD
    42. Using LEAD With and Offset of 2
    43. Using LAG
    44. Using LAG With an Offset of 2
  14. Temporary Tables
    1. CREATING A Derived Table
    2. The Three Components of a Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in The Derived Table
    5. Visualize This Derived Table
    6. Most Derived Tables Are Used To Join To Other Tables
    7. Multiple Ways to Alias the Columns in a Derived Table
    8. Our Join Example With A Different Column Aliasing Style
    9. Column Aliasing Can Default For Normal Columns
    10. CREATING A Derived Table using the WITH Command
    11. Our Join Example With The WITH Syntax
    12. WITH
    13. A WITH Clause That Produces Two Tables
    14. The Same Derived Query shown Three Different Ways
    15. Clever Tricks on Aliasing Columns in a Derived Table
    16. A Derived Table lives only for the lifetime of a single query
    17. An Example of Two Derived Tables in a Single Query
    18. Create Table Syntax
    19. Basic Temporary Table Examples
    20. More Advanced Temporary Table Examples
    21. Advanced Temporary Table Examples
    22. Performing a Deep Copy
    23. Deep Copy Using the Original DDL
    24. Deep Copy Using A CTAS
    25. Deep Copy Using A Create Table LIKE
    26. Deep Copy By Creating a Temp Table and Truncating Original
  15. Sub-query Functions
    1. An IN List is much like a Subquery
    2. An IN List Never has Duplicates: Just like a Subquery
    3. An IN List Ignores Duplicates
    4. The Subquery
    5. The Three Steps of How a Basic Subquery Works
    6. These are Equivalent Queries
    7. The Final Answer Set from the Subquery
    8. Should you use a Subquery of a Join?
    9. The Basics of a Correlated Subquery
    10. The Top Query always runs first in a Correlated Subquery
    11. Correlated Subquery Example vs. a Join with a Derived Table
    12. How the Double Parameter Subquery Works
    13. More on how the Double Parameter Subquery Works
    14. How to handle a NOT IN with Potential NULL Values
    15. Using a Correlated Exists
    16. How a Correlated Exists matches up
    17. The Correlated NOT Exists
    18. The Correlated NOT Exists Answer Set
    19. Answer: How many rows come back from this NOT Exists?
  16. Substrings and Positioning Functions
    1. The TRIM Command trims both Leading and Trailing Spaces
    2. A Visual of the TRIM Command Using Concatenation
    3. Trim and Trailing is Case Sensitive
    4. How to TRIM Trailing Letters
    5. The SUBSTRING Command
    6. How SUBSTRING Works with NO ENDING POSITION
    7. Using SUBSTRING to move Backwards
    8. How SUBSTRING Works with a Starting Position of -1
    9. How SUBSTRING Works with an Ending Position of 0
    10. The POSITION Command finds a Letters Position
    11. Using the SUBSTRING to Find the Second Word On
    12. Concatenation
    13. Concatenation and SUBSTRING
    14. Four Concatenations Together
    15. Troubleshooting Concatenation
    16. Declaring a Cursor
  17. Interrogating the Data
    1. The NULLIFZERO Command
    2. The ZEROIFNULL Command
    3. The COALESCE Command
    4. The COALESCE Answer Set
    5. The Coalesce Quiz
    6. The Basics of CAST (Convert And Store)
    7. Some Great CAST (Convert And Store) Examples
    8. The Basics of the CASE Statements
    9. The Basics of the CASE Statement
    10. Valued Case Vs. A Searched Case
    11. When an ELSE is present in CASE Statement
    12. Answer: When an ELSE is present in CASE Statement
    13. When an Alias is NOT used in a CASE Statement
    14. Combining Searched Case and Valued Case
    15. Nested Case
    16. Put a CASE in the ORDER BY
  18. View Functions
    1. Creating a Simple View to Restrict Sensitive Columns
    2. Creating a Simple View to Restrict Rows
    3. Creating a View to Join Tables Together
    4. You Select From a View
    5. Basic Rules for Views
    6. An ORDER BY Example Inside of a View
    7. An ORDER BY Inside of a View That is Queried Differently
    8. Creating a View With Ordered Analytics
    9. Creating a View With The TOP Command
    10. Creating a View With The LIMIT Command
    11. Altering A Table
    12. Altering A Table After a View has been Created
    13. A View that Errors After An ALTER
    14. Troubleshooting a View
    15. Updating Data in a Table through a View
  19. Set Operators Functions
    1. Rules of Set Operators
    2. INTERSECT Explained Logically
    3. INTERSECT Explained Logically
    4. UNION Explained Logically
    5. UNION Explained Logically
    6. UNION ALL Explained Logically
    7. UNION Explained Logically
    8. EXCEPT Explained Logically
    9. EXCEPT Explained Logically
    10. Minus Explained Logically
    11. Minus Explained Logically
    12. Testing Your Knowledge
    13. An Equal Amount of Columns in both SELECT List
    14. Columns in the SELECT list should be from the same Domain
    15. The Top Query handles all Aliases
    16. The Bottom Query does the ORDER BY (a Number)
    17. Great Trick: Place your Set Operator in a Derived Table
    18. UNION vs. UNION ALL
    19. A Great Example of how EXCEPT works
  20. Statistical Aggregate Functions
    1. The Stats Table
    2. STDDEV
    3. Casting STDDEV_SAMP and SQRT (VAR_SAMP)
    4. The STDDEV_POP Function
    5. A STDDEV_POP Example
    6. The STDDEV_SAMP Function
    7. A STDDEV_SAMP Example
    8. The VAR_POP Function
    9. A VAR_POP Example
    10. The VAR_SAMP Function
    11. A VAR_SAMP Example
    12. Will both queries bring back the same Answer Set?
    13. How would you Join these two tables?
    14. An Associative Table is a Bridge that Joins Two Tables
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

Training for your Team

Length: 3 Days
  • Private Class for your Team
  • Online or On-location
  • Customizable
  • Expert Instructors
Request Class

What people say about our training

The training was exceptional and the instructor was very good and very knowledgeable.
Eugene Kats
Wyndham Hotel Group
Great class. A fast pace and you'll learn and enjoy yourself.
James Taylor
IEEE
Most wonderful class I have taken so far!
Soma Deb
IEEE
I highly recommend Webucator. I had a fantastic experience with a wonderful instructor!
Pam Tetley
SunGard
More Testimonials

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

61,019

Students who have taken Instructor-led Training

11,714

Organizations who trust Webucator for their Instructor-led training needs

100%

Satisfaction guarantee and retake option

9.13

Students rated our Amazon Redshift Architecture and SQL Training trainers 9.13 out of 10 based on 56 reviews

Contact Us or call 1-877-932-8228