SQL Querying and Views in Redshift and Athena

SQL Querying and Views in Redshift and Athena – Complete Guide for AWS Data Engineer Associate

Why SQL Querying and Views in Redshift and Athena Matter

SQL querying is the primary mechanism by which data engineers and analysts extract insights from data stored in AWS data warehouses and data lakes. Amazon Redshift and Amazon Athena are two of the most widely used AWS services for running SQL queries at scale. Understanding how to write efficient queries, create views, and optimize performance across both services is critical for the AWS Data Engineer Associate exam and for real-world data engineering work.

Views, in particular, allow you to encapsulate complex query logic, simplify data access for downstream consumers, enforce security boundaries, and improve maintainability of your SQL codebase. The exam frequently tests your ability to choose the right service, understand query execution behavior, and apply best practices for views in both Redshift and Athena.

What Are SQL Querying and Views?

SQL Querying refers to using Structured Query Language to retrieve, filter, aggregate, join, and transform data stored in tables. Both Redshift and Athena support standard SQL (with some dialect differences), enabling you to run complex analytical workloads.

Views are virtual tables defined by a SQL SELECT statement. They do not store data themselves (with some exceptions); instead, they store the query definition and execute it when referenced. Views provide abstraction, security, and reusability.

Amazon Redshift – SQL Querying and Views

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse. It uses a columnar storage format and massively parallel processing (MPP) architecture to deliver fast query performance on large datasets.

Key SQL Features in Redshift:

• Standard SQL Support: Redshift supports ANSI SQL with extensions. You can use SELECT, JOIN, GROUP BY, HAVING, WINDOW functions, CTEs (Common Table Expressions), subqueries, and more.

• Distribution Styles: Redshift distributes data across nodes using KEY, ALL, EVEN, or AUTO distribution. Choosing the right distribution style affects join performance significantly.

• Sort Keys: Compound and interleaved sort keys determine how data is physically ordered on disk, which influences query performance for range-restricted scans and filters.

• Workload Management (WLM): Redshift uses WLM queues to manage concurrency and allocate resources to different query workloads. Understanding WLM is important for performance tuning.

• Result Caching: Redshift caches the results of queries. If the same query is run again and the underlying data has not changed, results are returned from cache, drastically improving performance.

• Federated Queries: Redshift can query data in Amazon RDS, Aurora, and Amazon S3 (via Redshift Spectrum) without moving data.

• Redshift Spectrum: Allows you to run queries against exabytes of data in Amazon S3 without loading it into Redshift tables. Uses external tables and schemas defined in the AWS Glue Data Catalog or a Redshift-managed Hive metastore.

Views in Redshift:

• Regular Views: Standard views that store a SQL definition and execute the underlying query each time they are referenced. Created with CREATE VIEW view_name AS SELECT ...

• Late-Binding Views: Created with CREATE VIEW view_name AS SELECT ... WITH NO SCHEMA BINDING. These views do not check the underlying table schema at creation time. They are especially useful for Redshift Spectrum external tables because the schema may change. The view only validates the schema at query execution time.

• Materialized Views: Created with CREATE MATERIALIZED VIEW. These views physically store the precomputed results of the query. They can be refreshed manually or automatically. Materialized views significantly improve performance for repeated complex queries, aggregations, and dashboard workloads. Redshift can automatically rewrite queries to use materialized views when applicable (auto query rewriting).

• Key Points on Materialized Views: They support incremental refresh (only processing changed data) for certain query patterns. They consume storage. They can reference base tables but have some restrictions on the SQL constructs they support (e.g., certain window functions or external tables may not be supported).

Amazon Athena – SQL Querying and Views

Amazon Athena is a serverless, interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL. It is based on the Presto/Trino engine and uses the AWS Glue Data Catalog as its metastore.

Key SQL Features in Athena:

• Serverless Architecture: No infrastructure to manage. You pay per query based on the amount of data scanned.

• Standard SQL (Presto/Trino): Athena supports ANSI SQL including SELECT, JOINs, aggregations, window functions, CTEs, UNNEST for complex/nested types, and more.

• Data Formats: Athena works with various data formats stored in S3: CSV, JSON, Parquet, ORC, Avro, and others. Columnar formats (Parquet, ORC) are recommended because they reduce the amount of data scanned, lowering cost and improving performance.

• Partitioning: Tables can be partitioned by one or more columns (e.g., year, month, day). Partitioning reduces data scanned by limiting which S3 prefixes are read. This is one of the most important cost and performance optimization techniques in Athena.

• CTAS and INSERT INTO: CREATE TABLE AS SELECT (CTAS) allows you to create new tables from query results, useful for ETL and data transformation. INSERT INTO appends data to existing tables.

• AWS Glue Data Catalog Integration: Athena uses the Glue Data Catalog to store table definitions, schemas, and partition information. Tables created in Athena are visible in Glue and vice versa.

• Athena Engine Versions: Athena v3 is based on Trino, while earlier versions used Presto. The engine version affects supported SQL syntax and functions.

Views in Athena:

• Standard Views: Created with CREATE VIEW view_name AS SELECT .... These are logical views stored in the Glue Data Catalog. They execute the underlying query each time they are accessed. They do not store data.

• Named Queries (Saved Queries): While not views in the traditional sense, Athena allows you to save query strings for reuse. These are different from views because they are not referenced in other SQL statements.

• No Native Materialized Views: Unlike Redshift, Athena does not support materialized views natively. To achieve similar functionality, you would use CTAS to create a new table from query results or use a scheduled pipeline (e.g., AWS Glue, Step Functions) to periodically refresh a results table.

How SQL Querying and Views Work – Comparison

Feature	Redshift	Athena
Engine	PostgreSQL-based MPP	Presto/Trino-based serverless
Data Location	Redshift managed storage (+ S3 via Spectrum)	Amazon S3
Pricing	Per-node (provisioned) or per-query (Serverless)	Per TB of data scanned
Standard Views	Yes	Yes
Late-Binding Views	Yes (WITH NO SCHEMA BINDING)	Not applicable
Materialized Views	Yes (with auto-refresh and incremental refresh)	No (use CTAS as workaround)
Result Caching	Yes (automatic)	Yes (query results stored in S3)
Partitioning	Not traditional partitioning; uses distribution and sort keys	S3-based partitioning (Hive-style)
External Tables	Yes (Redshift Spectrum)	Yes (all tables are external to S3)

Performance Optimization Strategies

For Redshift:
• Choose appropriate distribution keys to minimize data movement during joins
• Use sort keys to speed up range-restricted queries
• Use materialized views to precompute expensive aggregations
• Leverage result caching for repeated queries
• Use ANALYZE and VACUUM to maintain table statistics and reclaim space
• Use Redshift Spectrum to offload cold or infrequently queried data to S3
• Use concurrency scaling for burst workloads

For Athena:
• Use columnar formats (Parquet, ORC) to reduce data scanned
• Partition data by commonly filtered columns (date, region, etc.)
• Use compression (Snappy, GZIP, ZSTD) to reduce data size
• Avoid SELECT *; specify only needed columns
• Use CTAS to convert data into optimized formats
• Use bucketing for frequently joined columns
• Limit the use of complex regex-based SerDes when possible

Security Considerations for Views

• In Redshift, views can be used to restrict access to specific columns or rows. You grant SELECT permission on the view without granting access to the underlying tables. This is a common security pattern.
• In Athena, views stored in the Glue Data Catalog can be secured using IAM policies and AWS Lake Formation. Lake Formation provides fine-grained access control (column-level and row-level security) for Athena queries.
• Both services support encryption of data at rest and in transit.

Common Use Cases

• Data Abstraction: Create views to simplify complex joins and transformations so downstream users see clean, business-friendly tables.
• Dashboard Acceleration: Use Redshift materialized views to precompute aggregations for BI dashboards (e.g., Amazon QuickSight).
• Data Lake Querying: Use Athena views to provide a SQL interface over raw data in S3, hiding complexity of partitions and file formats.
• Cross-Service Querying: Use Redshift Spectrum with late-binding views to query both Redshift tables and S3 data in a single query.
• ETL Pipelines: Use Athena CTAS to transform raw data into optimized formats as part of an ETL pipeline.

Exam Tips: Answering Questions on SQL Querying and Views in Redshift and Athena

1. Know When to Use Redshift vs. Athena: If the question involves a persistent data warehouse with complex joins, high concurrency, and sub-second query requirements, Redshift is likely the answer. If the question involves ad-hoc querying of data in S3, serverless requirements, or cost optimization based on data scanned, Athena is the answer.

2. Materialized Views = Redshift: If a question asks about precomputing query results, automatic refresh, or improving repeated query performance with stored results, the answer involves Redshift materialized views. Remember, Athena does NOT support materialized views.

3. Late-Binding Views for Spectrum: When a question involves querying external tables in S3 through Redshift Spectrum and mentions schema flexibility or external schema changes, the answer is late-binding views (WITH NO SCHEMA BINDING).

4. Cost Optimization in Athena: Questions about reducing Athena query costs almost always involve: using columnar formats (Parquet/ORC), partitioning, compression, or avoiding SELECT *. These are heavily tested topics.

5. Views for Security: If a question describes restricting user access to certain columns or rows, views are a valid answer. In Redshift, grant access to the view rather than the underlying table. In Athena, consider AWS Lake Formation for fine-grained access control combined with views.

6. CTAS in Athena: If the question asks about transforming data format (e.g., converting CSV to Parquet) or creating summary/aggregate tables in Athena, CTAS is the key feature. This is Athena's alternative to materialized views for some use cases.

7. Understand Result Caching: Redshift automatically caches results; Athena stores query results in a specified S3 bucket. Both can avoid re-scanning data under certain conditions, but they work differently.

8. Glue Data Catalog is Central: Both Athena and Redshift Spectrum use the AWS Glue Data Catalog. Questions about shared metadata, schema management, or cross-service table definitions will reference the Glue Data Catalog.

9. Watch for Federated Query Keywords: If a question mentions querying RDS or Aurora data from Redshift, this is about Redshift federated queries. If it mentions querying S3 data from Redshift, this is Redshift Spectrum.

10. Incremental Refresh: If a question asks about efficiently refreshing precomputed results when only a portion of source data has changed, the answer is Redshift materialized views with incremental refresh.

11. Concurrency and Workload Management: For questions about managing multiple concurrent queries in Redshift, understand WLM queues, concurrency scaling, and how Redshift Serverless handles automatic scaling.

12. Eliminate Wrong Answers: If a question mentions materialized views and one option says Athena, that option is likely wrong. If a question mentions serverless SQL with no infrastructure and one option says Redshift provisioned cluster, look for the Athena or Redshift Serverless option instead.

13. Partitioning vs. Distribution: Do not confuse Athena partitioning (S3 prefix-based) with Redshift distribution styles (KEY, ALL, EVEN, AUTO). They serve different purposes and are tested in different contexts.

14. Read Questions Carefully: Pay attention to keywords like serverless, petabyte-scale warehouse, data lake, ad-hoc queries, precomputed results, and external tables. These keywords strongly hint at which service and feature the question is targeting.

By mastering these concepts and exam strategies, you will be well-prepared to answer any question on SQL querying and views in Redshift and Athena on the AWS Data Engineer Associate exam.