OnTimeStack | Terpli Redshift ETL Executor

Project overview

Terpli's Redshift ETL Executor orchestrated the critical data flow connecting storefront events, GA4, REST APIs, postMessage, Lambda/serverless, Redshift, and S3. The Node.js application ran as an ECS Fargate task, executing versioned SQL scripts in deterministic order to build trusted layers, analytics views, financial dashboards, and enriched e-commerce transaction tables tied to recommendations, reviews, loyalty, and product names.

Terpli Redshift ETL data flow and process overview diagram — High-level flow for Terpli's Redshift ETL: event ingestion, data river, Lambda/serverless, Redshift, S3 backups, ECS orchestration, hotfixes, and deduplication.

Challenge

Transform noisy and heterogeneous e-commerce data into reliable analytics models while preserving S3 backups, preventing concurrent ECS runs, closing orphan Redshift connections, and allowing retailer-specific hotfixes without compromising the core pipeline.

Solution

Implemented a containerized batch executor with an ECS API concurrency guard, Redshift Data API SQL execution, SCHEDULED_SERVICES-driven stages, statement-level status in dfm_execution_status, Slack alerts, UNLOAD backups to S3, product-name extraction, and a multi-pass order and event deduplication pipeline.

Tech Stack

Node.js
Amazon Redshift
Amazon S3
AWS ECS/Fargate
ETL

Technical scope

Node.js orchestration of Redshift SQL scripts
mv_* trusted layer and vw_fact_* dashboards
UNLOAD backups to Amazon S3 with incremental control
Hotfixes, product-name extraction, and deduplication v1-v7

Let's build something amazing?

We are ready to understand your technical challenge and propose the best architecture. Contact us for an initial consultation without commitment.