Study guide

7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena

AWS Certified Data Engineer - Associate DEA-C01

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Streaming Data Ingestion

1.1 Kinesis Data Streams vs. Firehose Architecture & Partitioning 1.2 Amazon MSK (Managed Streaming for Apache Kafka) & Serverless Patterns 1.3 Event-Driven Streaming Ingestion with DynamoDB Streams & Lambda 1.4 Streaming Distribution, Replay & Processing State

2 Chapter 2: Batch Data Ingestion & Migration

2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC) 2.2 S3 Batch Operations & High-Throughput Bulk Data Ingestion 2.3 Hybrid Data Transfer with Snow Family & AWS DataSync 2.4 Application, API & Managed File Ingestion

3 Chapter 3: ETL Transformation with AWS Glue

3.1 AWS Glue Jobs, DynamicFrames & PySpark Transformations 3.2 Glue Crawlers, Data Catalog & Schema Evolution 3.3 Data Preparation & Quality Rules with AWS Glue DataBrew & Data Quality 3.4 Incremental Processing with AWS Glue Job Bookmarks & Partition Processing 3.5 Technical and Business Catalogs, Connections & Lineage

4 Chapter 4: Big Data Processing & Workflow Orchestration

4.1 Amazon EMR Cluster Architecture, Spark & Hadoop Ecosystem 4.2 EMR Serverless & Instance Selection (On-Demand vs. Spot Optimization) 4.3 Serverless Orchestration with AWS Step Functions 4.4 Airflow Pipeline Orchestration with Amazon MWAA 4.5 Containers, Lambda & Distributed Processing Performance 4.6 Data Engineering Code, IaC & CI/CD

5 Chapter 5: Object Storage & Data Lake Architecture

5.1 Amazon S3 Storage Classes, Lifecycle Policies & Cost Optimization 5.2 S3 Data Lake Partitioning, Bucketing & Layout Strategies 5.3 Efficient Columnar Formats: Parquet, ORC, and Avro Schema Design 5.4 S3 Performance Optimization, Multipart Uploads & Transfer Acceleration 5.5 Apache Iceberg, S3 Tables & Open Table Operations

6 Chapter 6: Data Warehousing with Amazon Redshift

6.1 Amazon Redshift Architecture, RA3 Nodes & Managed Storage 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys 6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum

7 Chapter 7: NoSQL, Relational & Specialty Data Stores

7.1 Amazon DynamoDB Partition Keys, Global Secondary Indexes & Data Modeling 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena 7.4 Vector Stores, Indexes & LLM Data Processing

8 Chapter 8: Pipeline Operations, Monitoring & Observability

8.1 Monitoring Pipeline Health with Amazon CloudWatch Metrics & Logs 8.2 Event-Driven Alerting & Automation with EventBridge & CloudWatch Alarms 8.3 Centralized Audit Logging with AWS CloudTrail

9 Chapter 9: Troubleshooting, Data Quality & Performance Tuning

9.1 Diagnosing & Resolving AWS Glue, EMR & Lambda Execution Failures 9.2 Redshift & Athena Query Performance Tuning & Memory Management 9.3 Implementing Data Quality Frameworks & Automated Validation Checks 9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew

10 Chapter 10: High Availability, Backup & Cost Optimization

10.1 Disaster Recovery, Cross-Region Replication & Backup Strategies 10.2 Designing High Availability Architectures Across AWS Data Services 10.3 Cost Optimization & Resource Utilization across Analytics Workloads

11 Chapter 11: Data Security, Encryption & Identity Management

11.1 Fine-Grained Access Control with AWS IAM Policies, Roles & Conditions 11.2 Data Encryption at Rest & in Transit with AWS KMS & S3 Bucket Policies 11.3 Secure Credentials & Secrets Management with AWS Secrets Manager & Parameter Store 11.4 Private Network Connectivity & Isolation with AWS PrivateLink & VPC Endpoints

12 Chapter 12: Data Governance, Privacy & Compliance

12.1 Centralized Data Lake Governance & Access Control with AWS Lake Formation 12.2 Automated Sensitive Data Discovery & PII Protection with Amazon Macie 12.3 Configuration Auditing & Compliance Enforcement with AWS Config 12.4 Data Privacy, Sovereignty, Sharing & Retention Guardrails 12.5 SageMaker Unified Studio Domains, Projects & Catalog Governance

7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena

Key Takeaways Explain This Topic AI

Amazon Neptune supports property-graph queries with Gremlin or openCypher and RDF queries with SPARQL, using graph-native indexes and traversal operators for highly connected data.

Amazon OpenSearch Service provides distributed full-text search, log analytics, and k-NN vector search capabilities for high-dimensional embeddings in RAG and GenAI applications.

Amazon Athena runs serverless SQL over Amazon S3; SQL workgroups can use bytes-scanned billing or provisioned capacity, so columnar layout, partition pruning, and workgroup controls matter.

Athena Federated Query uses AWS Lambda connectors to run SQL queries across S3 data lakes and external data sources (DynamoDB, RDS, Redshift) without upfront data ingestion.

Partition Projection calculates S3 partition locations dynamically using DDL table properties, bypassing AWS Glue Data Catalog metadata API lookups and eliminating MSCK REPAIR TABLE calls.

Last updated: August 2026

  • 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena
  • The Specialty Data Store Paradigm

In modern data engineering architectures, no single database engine satisfies all operational and analytical access patterns. The concept of Polyglot Persistence advocates leveraging purpose-built data stores engineered specifically for distinct data models:

Relational / OLTP (RDS, Aurora): Acid compliance, complex table transactions.

Key-Value / NoSQL (DynamoDB): Single-digit millisecond key lookups at scale.

Data Warehousing / OLAP (Redshift): Columnar MPP aggregations over petabytes.

Graph Analytics (Neptune): Traverse multi-hop entity relationships with graph-native operators; cost still grows with the paths explored.

Search & Vector Search (OpenSearch): Unstructured text matching and high-dimensional vector embeddings.

Serverless Data Lake Analytics (Athena): Ad-hoc ANSI SQL queries directly over Amazon S3 objects.

Amazon Neptune: Purpose-Built Graph Database Engine

Amazon Neptune is a high-performance, fully managed graph database engine optimized for storing and querying highly connected datasets.

Why Relational Databases Fail for Graph Workloads

In a relational database, representing graph structures (such as social networks, financial fraud trees, or supply chains) requires join tables (many-to-many junction tables). Executing a multi-hop query (e.g. "Find all friends of friends who purchased the same product as User X") requires cascading JOIN operations: -- Relational Multi-Hop JOIN (Exponential Performance Degradation)

  • SELECT p2.product_name
  • FROM users u
  • JOIN friendships f1 ON u.id = f1.user_id
  • JOIN friendships f2 ON f1.friend_id = f2.user_id
  • JOIN purchases p1 ON f2.friend_id = p1.user_id
  • JOIN products p2 ON p1.product_id = p2.id
  • WHERE u.id = 'USR-8841';

Relational multi-hop queries can require repeated joins and large intermediate results. Neptune provides graph-native storage, indexes, and traversal operators, but runtime still depends on graph degree, filters, path length, and the portion of the graph explored.

Neptune Graph Models & Query Languages

Neptune supports two distinct graph frameworks within the same engine:

Graph Framework Core Data Structure Query Languages Primary Use Case

Property Graph (PG) Nodes (Vertices), Edges (Relationships), Properties Apache TinkerPop Gremlin , openCypher Fraud detection, social networks, recommendations

Resource Description Framework (RDF) Triples (Subject, Predicate, Object) SPARQL Semantic web, knowledge graphs, biomedical ontologies

Neptune Storage Architecture

Like Amazon Aurora, Neptune features a cloud-native decoupled storage engine. A Neptune storage volume automatically scales up to 128 TiB and replicates data 6 ways across 3 Availability Zones, supporting up to 15 low-latency read replicas.

Amazon OpenSearch Service: Search, Log Analytics & Vector Database

Amazon OpenSearch Service (successor to Amazon Elasticsearch Service) is a managed distributed search and analytics suite used for real-time log analytics, application search, monitoring, and generative AI vector retrieval.

Cluster Architecture & Node Roles

An OpenSearch cluster comprises specialized node types:

Dedicated Manager Nodes : Manage cluster state, shard routing, and index creation.

Data Nodes : Store indexed documents and execute search/aggregation queries.

UltraWarm & Cold Storage : Decouples historical log storage by offloading cold indices to Amazon S3 while maintaining query access via OpenSearch Dashboards.

Full-Text Inverted Indexing vs. Vector Search

Full-Text Search : Uses inverted indexes (built via Apache Lucene) to analyze text tokens, compute TF-IDF / BM25 relevancy scores, and deliver instant search results.

Vector Database & k-NN : OpenSearch includes a native k-Nearest Neighbor (k-NN) plugin. High-dimensional vector embeddings generated by machine learning models (e.g. Amazon Bedrock, Titan) are stored in vector fields. OpenSearch executes similarity searches using HNSW (Hierarchical Navigable Small World) algorithms for Retrieval-Augmented Generation (RAG) pipelines.

Amazon Athena: Serverless Interactive Analytics on S3

Amazon Athena is an interactive, serverless query service that allows data engineers to analyze data directly in Amazon S3 using standard ANSI SQL. Athena requires zero ETL, zero infrastructure provisioning, and no cluster management.

Architectural Underpinnings

Distributed Engine : Built on open-source Presto and Trino query engines.

Data Catalog Integration : Connects seamlessly with the AWS Glue Data Catalog to resolve table definitions, data types, and S3 partition structures.

Pricing Model : Billed strictly per query based on total terabytes of data scanned (with Regional bytes-scanned rates or provisioned capacity). Minimizing data scanned via columnar file formats and partitioning directly translates to cost optimization. Athena SQL Query ---> [ Presto/Trino Query Planner ] ---> Consults AWS Glue Data Catalog

  • --> Scans Partitioned Parquet on S3
  • Athena Performance & Cost Optimization Techniques

Columnar File Formats (Apache Parquet / ORC) :

Convert raw JSON/CSV files to Parquet. Columnar storage permits Athena to scan only the specific columns requested in a SELECT statement, reducing data scanned by up to 90%.

Data Compression :

Compress S3 files using Snappy or GZIP. Athena scans fewer bytes from disk, improving query speeds.

Partitioning & Partition Projection :

Standard Partitioning : S3 path prefixes like s3://my-bucket/logs/year=2026/month=08/day=13/ limit query scans.

The Glue Catalog Bottleneck : As partition counts grow into millions, running MSCK REPAIR TABLE or querying Glue Data Catalog APIs introduces severe latency and throttling.

Partition Projection : Configured directly in table DDL TBLPROPERTIES . Athena calculates partition locations dynamically using rules (e.g. date ranges, integer ranges) without calling the Glue Data Catalog API, drastically accelerating query performance.

Athena Federated Query :

Extends Athena beyond S3. Using custom AWS Lambda connectors, data engineers can run unified ANSI SQL queries joining S3 Parquet tables directly with live data stored in DynamoDB, Amazon RDS, Redshift, or ElastiCache.

CTAS & Apache Iceberg Support :

CTAS ( CREATE TABLE AS SELECT ) : Creates a new Athena table and writes transformed query results back to S3 as partitioned Parquet files in a single operation.

Apache Iceberg Format : Athena natively supports Apache Iceberg table format, bringing ACID transaction guarantees, time-travel point-in-time queries, and automated schema evolution to S3 data lakes.

Code Example: Neptune Gremlin Query, OpenSearch Vector Search, and Athena DDL with Partition Projection -- Athena DDL Table Creation utilizing Partition Projection for fast date routing

  • CREATE EXTERNAL TABLE IF NOT EXISTS clickstream_logs (
  • event_id STRING,
  • user_id STRING,
  • event_type STRING,
  • page_url STRING,
  • response_time_ms INT
  • )
  • PARTITIONED BY (
  • event_date STRING
  • )
  • ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  • STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
  • OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
  • LOCATION 's3://datacenter-analytics-lake/clickstream/'
  • TBLPROPERTIES (
  • 'projection.enabled' = 'true',
  • 'projection.event_date.type' = 'date',
  • 'projection.event_date.range' = '2025-01-01,NOW',
  • 'projection.event_date.format' = 'yyyy-MM-dd',
  • 'projection.event_date.interval' = '1',
  • 'projection.event_date.interval.unit' = 'DAYS',
  • 'storage.location.template' = 's3://datacenter-analytics-lake/clickstream/event_date=${event_date}/'
  • ); # Python Gremlin Query example for Amazon Neptune
  • from gremlin_python.driver.driver_remote_connection import DriverRemoteConnection
  • from gremlin_python.process.anonymous_traversal import traversal
  • # Connect to Neptune Cluster Endpoint inside Private Subnet
  • neptune_endpoint = 'wss://your-neptune-cluster.neptune.amazonaws.com:8182/gremlin'
  • g = traversal().withRemote(DriverRemoteConnection(neptune_endpoint, 'g'))

def find_suspicious_fraud_network(user_id: str):

"""

Traverses Neptune Property Graph to find multi-hop shared credit card accounts.

"""

try:

  • # Gremlin query: User -> HasCard -> Card <- HasCard <- OtherUser
  • shared_accounts = g.V().has('User', 'userId', user_id) \
  • .out('hasCreditCard') \
  • .in_('hasCreditCard') \
  • .hasLabel('User') \
  • .values('userId') \
  • .toList()
  • print(f"Detected {len(shared_accounts)} accounts linked by shared credit card.")
  • return shared_accounts

except Exception as e:

  • print(f"Neptune traversal error: {str(e)}")
  • raise e

Loading diagram... Amazon Athena Data Lake & Federated Query Architecture