A Serverless AWS Pattern for Managing ETL Logging at Scale - Part 2

Written by Raman Kadariya | Jul 9, 2026 1:30:00 PM

Introduction

This is the second installment of a three-part technical series detailing the end-to-end observability and log tracking architecture deployed at Red Oak Strategic to go from Step Function orchestration to Amazon Quick dashboard. By breaking this comprehensive case study into modular segments, we provide a targeted look at the specific orchestration, replication, and visualization layers that comprise our "dev vertical" monitoring framework.

This section focuses on the high-performance replication and transformation layer of the logging architecture. It explores the transition from traditional Lambda-based normalization to native DynamoDB Zero-ETL integration, which replicates data directly into Redshift Serverless as a SUPER data type. The document provides comprehensive configuration details for Redshift namespaces and workgroups, alongside a deep dive into incremental refresh strategies. It specifically highlights the use of SECURITY DEFINER stored procedures to extract fields from complex nested JSON, optimizing data for rapid downstream analytics.

1. DynamoDB Zero-ETL Integration with Redshift

The architecture uses DynamoDB Zero-ETL integration to continuously replicate DynamoDB table data into Redshift Serverless. This native integration eliminates the need for custom ETL pipelines between DynamoDB and Redshift, replacing the earlier pattern of DynamoDB Streams, Lambda normalization, and S3 Parquet export.

1.1 How Zero-ETL Works -

DynamoDB Zero-ETL integration requires Point-in-Time Recovery (PITR) to be enabled on the source table. Once the integration is created, DynamoDB continuously streams changes to Redshift. Data arrives in Redshift as a SUPER data type, with each DynamoDB item stored in a column named value. The SUPER column preserves the full DynamoDB attribute map structure, including type descriptors (S for string, N for number, etc.).

To extract individual fields from the SUPER column, queries use the pattern:

1.2 Prerequisites -

1.3 Namespace Registration -

The Redshift namespace must be registered with LakeFormation to participate in Zero-ETL replication. This registration includes the namespace name, workgroup name, and consumer account identifiers.

Sample: Namespace Registration YAML -
1.4 Replicated TablesCLI: Create a DynamoDB Zero-ETL Integration to Redshift -1.5 Advantages Over the Streams-to-S3 Pattern -

No custom Lambda normalization code to maintain. The Zero-ETL integration handles replication natively.
Near real-time replication without managing DynamoDB Streams, batch sizes, or S3 write logic.
Eliminates the intermediate S3 layer and Athena federated query connector, reducing both complexity and cost.
Automatic handling of schema changes. New DynamoDB attributes appear in the SUPER column without pipeline modifications.

2. Redshift Serverless Configuration

The analytics layer runs on Amazon Redshift Serverless, which removes the need to manage cluster sizing and provides automatic scaling based on query workload. However, it is important to note that Redshift Serverless involves specific infrastructure costs associated with VPC and networking components. Additionally, because it utilizes reserved capacity models to ensure performance, it generally incurs higher operational costs than Amazon Athena.

2.1 Namespace and Workgroup - Sample: Redshift Serverless Namespace and Workgroup (Terraform) -
2.2 IAM Role and Permissions -

The Redshift service role assumes trust for both redshift.amazonaws.com and redshift-serverless.amazonaws.com. It carries the AmazonRedshiftAllCommandsFullAccess managed policy plus scoped inline policies for Glue catalog read access (limited to specific databases), S3 data lake read access, and KMS decrypt permissions for encrypted objects.

2.3 Log Exports -

Three log types are exported from the Redshift namespace: connection logs (tracking who connects and when), user activity logs (capturing every SQL statement executed), and user logs (recording user creation, modification, and privilege changes). These logs ship to CloudWatch Logs for retention and alerting.

3. Zero-ETL Stored Procedures and Incremental Refresh

Four stored procedures transform Zero-ETL replicated DynamoDB data into flat analytics tables within Redshift. Each procedure extracts fields from the SUPER data type value column and uses a watermark pattern to process only new or changed records since the last refresh.

3.1 Watermark Strategies -

Timestamp watermark: The job tracking and access log procedures track _record_create_time, a metadata column automatically added by the Zero-ETL integration, and compared against a locally stored watermark timestamp.
Transaction sequence watermark: The failed jobs and workflows procedures use padb_internal_txn_seq_col, an internal Redshift column that provides a monotonically increasing sequence number.

3.2 Stored Procedure Details/Security Model for Scheduled Procedures -

The security context is critical for stored procedures. Redshift stored procedures default to SECURITY INVOKER (running with the caller's privileges), which is why the dedicated Redshift service role (used by the scheduler, as referenced in Section 7.2) must be explicitly granted GRANT EXECUTE on the procedure.

Because the procedures in this architecture are designed to run with elevated privileges (to update tables they do not own), they are created using SECURITY DEFINER (as shown in Section 8.3).

For the Scheduled Query to run successfully, the Redshift service role must also satisfy these IAM requirements:

Trust Policy: The trust policy principals must include scheduler.redshift.amazonaws.com.
Permissions: The role requires redshift-serverless:GetCredentials to obtain temporary database credentials for the scheduled query.

3.3 SUPER Column Extraction -

Each procedure uses SECURITY DEFINER context and enables case-sensitive identifiers. The core extraction pattern pulls fields from the SUPER column:

Sample: Incremental Refresh Stored Procedure (Redshift SQL) -3.4 Transformation Logic -

Each procedure applies consistent transformations:

DynamoDB attribute map parsing: Values extracted from nested JSON (value.field_name."S"::VARCHAR) and trimmed.
Timezone conversion: All timestamps converted to Eastern Time using CONVERT_TIMEZONE, with derived date, hour, day-of-week, and month fields.
Duration computation: Elapsed time computed in seconds and minutes, categorized as Fast, Normal, or Slow.
Operational flags: Boolean indicators derived for success, failure, retry eligibility, and error presence.
Throughput metrics: The job tracking procedure computes records_per_second and mb_per_record.

3.5 Scheduled Refresh -

The stored procedures are invoked on a schedule through EventBridge-triggered Lambda functions. These schedulers call the Redshift Data API to execute each procedure asynchronously. Dev defaults are set between 15 and 30 minutes depending on the vertical, with a maximum event age of 3,600 seconds and up to 3 retries.

Sample: Invoke Stored Procedure via Redshift Data API (Python) -

Conclusion

Replication Layer Secured. This second installment has demonstrated how to move from raw data capture to a performant, automated replication layer. By leveraging DynamoDB Zero-ETL and Redshift Serverless, we have successfully replaced complex, maintainable ETL pipelines with a streamlined architecture. The use of SECURITY DEFINER stored procedures ensures that data is consistently transformed and optimized for analytics. As we move to the final installment of this series, we will complete the architecture by building the visualization layer, where these insights are surfaced through interactive dashboards.