DEV Community: Aliasgar K C

Unlocking Data at Scale: A Complete Guide to Amazon Redshift Spectrum with AWS Glue and Lake Formation

Aliasgar K C — Mon, 04 Aug 2025 10:08:58 +0000

Amazon Redshift Spectrum brings powerful analytics directly to the data stored in your Amazon S3 data lake. This feature allows you to run complex SQL queries on vast amounts of structured and semi-structured data in S3, without moving or loading the data into your Redshift cluster.

However, setting up Redshift Spectrum with modern AWS services like Lake Formation and AWS Glue can be challenging due to complex permission requirements. This comprehensive guide walks you through the complete process, from basic concepts to production-ready implementation.

What Is Redshift Spectrum?

Redshift Spectrum extends Amazon Redshift's analytics capabilities seamlessly into your S3 data lake. Instead of loading all your data into Redshift tables, you can define external tables that reference S3 files directly and query them using standard SQL. Key highlights include:

Query Across S3 and Redshift Data: Run ad-hoc analytics pulling together data in both Redshift and S3.
Cost Efficiency: Only pay for the amount of data scanned in S3, with no persistent compute cost if not in use.
Scalability: Redshift Spectrum harnesses thousands of AWS-managed nodes for massively parallel query processing. It can scale to exabyte-level datasets.
Performance: Compute-intensive work such as filtering and aggregation is pushed to the Spectrum layer, freeing Redshift cluster resources.

Modern Architecture: How the Components Work Together

Understanding the data flow is crucial for successful implementation:

AWS Glue Crawler → Discovers your S3 data and creates table metadata in the Glue Data Catalog
Lake Formation → Manages fine-grained permissions for catalog data and S3 locations
Redshift Spectrum → Queries external tables using Glue Catalog metadata with Lake Formation permissions

This integration provides enterprise-grade security and governance while maintaining performance and scalability.

Redshift Spectrum: Typical Use Case

Scenario: Seamless Analysis of Historical Website Clickstream Data

Imagine an e-commerce company tracking website visitor behavior. Recent data (current year) resides in Amazon Redshift tables for quick analysis, but years of historical clickstream logs are archived in compressed Parquet format in S3. Analysts want to compare recent trends with multi-year history without having to reload terabytes of old data.

With Redshift Spectrum:

Analysts can create external tables referencing raw S3 data.
Run SQL queries joining fresh Redshift data with archived S3 logs.
Gain immediate, full-history insights using a single query engine.

This hybrid analytics workflow is impossible or cost-prohibitive with traditional ETL pipelines. Spectrum saves significant time and storage costs.

Complete Step-by-Step Implementation Guide

Prerequisites

Before starting, ensure you have:

Data files (CSV, Parquet, JSON, etc.) in an S3 bucket, in the same AWS region as your Redshift cluster
Administrative access to AWS IAM, Glue, Lake Formation, and Redshift
AWS CLI configured (optional but recommended for troubleshooting)

Step 1: Create IAM Role for AWS Glue Crawler

Part 1: Setting Up IAM Roles and Permissions

# Navigate to IAM Console → Roles → Create role

Role Configuration:

Trusted entity: AWS service
Use case: Glue
Role name: AWSGlueServiceRole-akc

Required Policies:

AWS Managed Policy: AWSGlueServiceRole
Custom S3 Access Policy (S3-access-policy):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::your-bucket-name",
                "arn:aws:s3:::your-bucket-name/*"
            ]
        }
    ]
}

Glue Catalog Permissions (Table-policy):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Action": [
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:CreateTable",
                "glue:UpdateTable",
                "glue:DeleteTable",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:CreatePartition",
                "glue:UpdatePartition",
                "glue:DeletePartition"
            ],
            "Resource": [
                "arn:aws:glue:us-east-1:ACCOUNT-ID:catalog",
                "arn:aws:glue:us-east-1:ACCOUNT-ID:database/your-database",
                "arn:aws:glue:us-east-1:ACCOUNT-ID:table/your-database/*"
            ]
        }
    ]
}

Lake Formation Access (Lakeformation-policy):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Statement1",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess"
            ],
            "Resource": "*"
        }
    ]
}

Step 2: Create IAM Role for Redshift Spectrum

# Navigate to IAM Console → Roles → Create role

Role Configuration:

Trusted entity: AWS service
Use case: Redshift - Customizable
Role name: SpectrumRole

Required Policies:

AWS Managed Policy: AmazonS3ReadOnlyAccess
Glue Access Policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetTable",
                "glue:GetTables",
                "glue:GetPartition",
                "glue:GetPartitions",
                "glue:CreateDatabase"
            ],
            "Resource": [
                "arn:aws:glue:us-east-1:ACCOUNT-ID:catalog",
                "arn:aws:glue:us-east-1:ACCOUNT-ID:database/*",
                "arn:aws:glue:us-east-1:ACCOUNT-ID:table/*/*"
            ]
        }
    ]
}

Lake Formation Access Policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess"
            ],
            "Resource": "*"
        }
    ]
}

Part 2: Setting up the Redshift Cluster

Step 3: Create Redshift Namespace and Workgroup

Namespace Configuration:

Namespace name: spectrum-analytics-namespace
Database name: analytics_db
Admin user name: admin_user
Admin user password: Create a secure password
Associated IAM roles: We'll add the Spectrum role later

Permissions and encryption:

Manage IAM roles: Skip for now (we'll configure later)
Encryption: Use AWS managed key (default)
Log exports: Enable audit logging and user activity logging (recommended)

Click Create namespace

Workspace Configuration:

# Navigate to Amazon Redshift Console → Serverless dashboard → Create workgroup

Step 4: Associate Redshift Role with Cluster

# Navigate to Redshift Console → Clusters → [Your Cluster] → Properties → Security
# Manage IAM roles → Associate IAM roles → Select Spectrum-role

Part 3: Setting Up AWS Glue Data Catalog

Step 5: Create Glue Database

# Navigate to AWS Glue Console → Databases → Add database

Database Configuration:

Name: your-database (e.g., akc-db)
Description: Database for analytics data
Location: Leave empty

Step 6: Configure Lake Formation Permissions

Grant Database Permissions to Crawler:

# Navigate to Lake Formation Console → Permissions → Grant

Principal: AWSGlueServiceRole-akc
LF-Tags or catalog resources: Catalog resources
Databases: Select your database
Database permissions: ✓ Create table, Alter, Drop, Describe

Grant Data Location Access to Crawler:

# Navigate to Lake Formation Console → Permissions → Grant

Principal: AWSGlueServiceRole-akc
LF-Tags or catalog resources: Data locations
Amazon S3 path: s3://your-bucket-name/
Data location permissions: ✓ Data location access

Grant Permissions to Redshift Spectrum Role:

# Navigate to Lake Formation Console → Permissions → Grant

Principal: Spectrum-role
Database: Your database
Database permissions: ✓ Describe
Tables: All tables
Table permissions: ✓ Select, Describe

Grant Data Location Access to Spectrum Role:

# Navigate to Lake Formation Console → Permissions → Grant

Principal: Spectrum-role
LF-Tags or catalog resources: Data locations
Amazon S3 path: s3://your-bucket-name/
Data location permissions: ✓ Data location access

Step 7: Create and Run Glue Crawler

# Navigate to AWS Glue Console → Crawlers → Create crawler

Crawler Configuration:

Name: akc-crawler
Description: Crawler for analytics data

Data Sources:

Data source: S3
Location of S3 data: s3://your-bucket-name/your-folder/
Subsequent crawler runs: Crawl all folders

Security Settings:

IAM role: AWSGlueServiceRole-akc

Output and Scheduling:

Target database: Your database
Table name prefix: Leave empty
Crawler schedule: On demand

Run the Crawler:

# Navigate to AWS Glue Console → Crawlers → your-data-crawler → Run crawler

Step 8: Verify Table Creation

# Navigate to AWS Glue Console → Databases → your-database → Tables

You should see a table created based on your data file. Click on it to verify:

Column names and data types
S3 location
File format

Part 3: Setting Up Redshift Spectrum

Step 9: Create External Schema in Redshift

Connect to your Redshift cluster and execute:

CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'your-database'
IAM_ROLE 'arn:aws:iam::ACCOUNT-ID:role/Spectrum-role'
REGION 'us-east-1';

Step 10: Query Your Data

Basic Query:

SELECT * FROM spectrum_schema.your_table_name LIMIT 10;

Check Table Structure:

SELECT * FROM SVV_EXTERNAL_COLUMNS
WHERE schemaname = 'spectrum_schema'
AND tablename = 'your_table_name';

Analytics Queries:

SELECT
c.customer_id,
COUNT(*) AS page_views
FROM
spectrum_schema.akc_bucket_rs c
WHERE
event_time BETWEEN '2025-01-01' AND '2025-06-30'
GROUP BY
c.customer_id;

Conclusion

Amazon Redshift Spectrum, when properly configured with AWS Glue and Lake Formation, provides a powerful platform for modern data analytics. The integration enables organizations to:

Scale analytics across petabytes of S3 data
Reduce costs by avoiding data movement
Implement governance with fine-grained access controls
Accelerate insights with familiar SQL interfaces

While the initial setup involves multiple services and complex permissions, following this comprehensive guide ensures a robust, production-ready implementation. The key to success lies in understanding the relationship between IAM, Lake Formation, Glue, and Redshift, and systematically configuring each component.

By implementing the best practices outlined in this guide, you'll have a scalable, secure, and cost-effective data analytics platform that can grow with your organization's needs. Start with a simple use case, validate the setup, and gradually expand to more complex scenarios as your team becomes comfortable with the architecture.

Remember: proper planning and permission management are crucial for success. Take time to understand each component's role, test thoroughly in development environments, and monitor performance in production.

Ready to unlock the full potential of your data lake? Start implementing Redshift Spectrum today and transform your organization's approach to large-scale data analytics.

Building a Classic Tetris Game with Amazon Q – A Fun Coding Adventure

Aliasgar K C — Sat, 24 May 2025 10:23:00 +0000

Recently, I took on a fun challenge — building a simple 2D Tetris game using Amazon Q, Amazon's generative AI coding assistant. As part of their developer challenge, I wanted to explore how well it could help with game development, and the result was surprisingly delightful.

The Prompt

All I started with was a simple request:

Can you build a basic 2D game like Tetris in Python?

That’s it. No complex specifications, no detailed requirements — just a clear idea and curiosity.

The Experience

From the moment I submitted the prompt, Amazon Q was impressively intuitive. It quickly scaffolded the game logic, using pygame to create the Tetris grid, handle block movements, and implement game-over conditions. I learned a lot by reading through the code it generated and tweaking the logic to suit my style.

I was especially impressed with how easy it was to iterate, making small changes to the mechanics or visuals felt seamless. It was like pair programming with a supercharged assistant.

Final Thoughts

Building this Tetris clone wasn’t just about nostalgia—it was a fun way to explore Amazon Q's capabilities creatively and hands-on. Whether you're prototyping games or building serious applications, tools like Amazon Q can make the process more enjoyable and efficient.

If you're curious, I highly recommend giving it a try — sometimes, all it takes is a single prompt to start building something cool.

Github link: Tetris game

The Week in Review

Aliasgar K C — Tue, 31 Aug 2021 05:49:15 +0000

It was the 4th week of the 100DaysofCode challenge and it turned out to be the most productive week in the entire challenge. I learned about REST API and defining RESTful routes. To practice defining RESTful routes, I made a project involving CRUD operations where the user could post a comment, edit the comment, view and delete comments. Though there were not any authentication and user account features, it was sufficient to get an idea about defining RESTful routes and passing data. In addition to this, I also solved 40 problems on arrays on geeksforgeeks and leetcode. Also focused on personal development and made it a habit to read at least 20 pages a day.

Day 4/100

Aliasgar K C — Mon, 26 Jul 2021 10:44:37 +0000

4 days and now I have started gathering momentum. Started the day with solving some problems on Hackerrank. Then watched some some tutorials on arrays and practiced the most frequently array problems on Leetcode. Initially, I solved the problems with a bruteforce approach but then I tried to solve it more efficiently. I solved more problems today than usual, but what makes me more happy is that I was able to solve them efficiently after coming up with bruteforce approaches. Coming on to the web course, I learnt about classes in Javascript and also about the extends and super keyword based on the concept of inheritance. Also came across some of the basics terminal commands that will be used further when I move on to Node and Express. In the end, I revised the concepts which I learnt previously.

check out my portfolio:
https://aliasgarkc.netlify.app/

Day 3/100

Aliasgar K C — Sun, 25 Jul 2021 10:20:33 +0000

The third day of the challenge and the schedule did go haywire😓... Slept late at night and consequently woke up late and then went on to revise what I learnt in the past 2 days, practiced some basic array problems and made one more project ( A jokes website). The same concept of APIs was used. Had a tough time recollecting all the previously learnt topics, but in the end... I came out on top😉.Also made a list of tasks to be performed in the upcoming days. My consistency was tested today ... But I am pretty contented that the process continued.

Check out my portfolio: https://aliasgarkc.netlify.app/

Day 2/100

Aliasgar K C — Sat, 24 Jul 2021 11:00:03 +0000

Day 2 of the challenge started as usual, by solving problems on leetcode and hackerrank. Also revised arrays and practiced some problems on it. Then, I went on to continue my web development course and learnt about object oriented programming in JavaScript along with prototypes. I then started working on a TV Show search project which made use of APIs and also styled it with bootstrap and some custom CSS and published it on Github and hosted the site on Netlify. Finally, I went through what I learnt yesterday. So far so good, hope that I remain consistent and cover most of the concepts in the upcoming days.

TV Show search Website Link : https://showsearch.netlify.app/
Check out my portfolio : https://aliasgarkc.netlify.app

Day 1 / 100

Aliasgar K C — Fri, 23 Jul 2021 11:39:55 +0000

So, Finally after so many days of procrastination, here I am taking up the 100DaysOfCode challenge. Today was the day 1 of the challenge. I started with solving some problems on Hackerrank and then went on to watch some tutorials on data structures and algorithms, specifically on time and space complexity. After that I continued learning JavaScript on Udemy, had a quick glance on the basics(which I already learnt before) and then went on to learn about AJAX and APIs. I also learned to use Postman, which I believe is pretty useful when working with APIs. Pretty satisfied with what I learnt today and hoping to do the same for rest of the challenge and also continue after the challenge ends.