Near Real-Time ETL on AWS Glue for AI-Ready Analytics

Your operations team closes a batch of orders at 3 PM. The analytics dashboard shows it tomorrow morning, after the overnight refresh. The sales director prices against last night’s numbers, and the data engineers spend their day explaining stale reports while patching a pipeline nobody documented.

This is what organically grown data infrastructure looks like: a database here, a cloud migration there, a BI tool on top, and no low-latency path between operational data and analytics. Erbis built a managed near real-time ETL service on AWS Glue to close that gap. This article explains the architecture, the design choices and where it has paid off.

The problem: overnight batch in a business that runs by the hour

Operational data lives in Oracle, PostgreSQL, MySQL, SQL Server, Amazon RDS, Aurora or on-premises systems. Analytics wants it in Amazon Redshift, a lakehouse on S3, Databricks or Snowflake on AWS. The traditional bridge is a nightly batch job: a full or partial extract, a transformation script, a load. It has three problems.

• Latency. Decisions made on data that is 8 to 24 hours old.
• Fragility. Custom scripts and third-party tools with licence costs, maintained by whoever wrote them.
• Load. Full extracts hit production databases at exactly the wrong time.

Near real-time ETL replaces the nightly job with a continuous flow of changes, so freshness is measured in minutes, the source database is barely touched, and the pipeline is declared rather than hand-coded.

Reference architecture

The pipeline has five stages and one cross-cutting layer. Everything runs inside the client’s AWS account.

1. Sources. Amazon RDS, Aurora, on-premises Oracle, PostgreSQL, MySQL and SQL Server, and any JDBC-compatible database. SaaS platforms join through their APIs or export feeds.

2. Change data capture (CDC). AWS Database Migration Service reads the database transaction log and emits every insert, update and delete as an event, after a one-time full load to start. Change events land in Amazon S3 as files or in an Amazon Kinesis stream, depending on the latency target. For Aurora and RDS sources feeding Redshift, AWS zero-ETL integrations can remove this stage entirely.

3. Transform with AWS Glue. Glue jobs, streaming or micro-batch, apply schema mapping, data-type conversion, deduplication and merge logic so that updates and deletes are applied correctly to the target. Job bookmarks track what has been processed; Glue Data Quality rules reject or quarantine bad records. There is no custom framework to maintain: the transformation logic is versioned in the job definition.

4. Store. Two targets, sometimes both. Amazon Redshift, including Redshift Serverless, for a warehouse with SQL performance for BI. Or an open lakehouse on S3 with Apache Iceberg tables, governed by the Glue Data Catalog and AWS Lake Formation, for a single copy of the data that warehouse, machine learning and agents all read.

5. Serve. Amazon QuickSight for dashboards and natural-language Q&A, Redshift or Athena for SQL, SageMaker for models, and Bedrock-based agents that need current data to act on.

Cross-cutting: catalog, monitoring and security. The Glue Data Catalog holds schemas, partitions and lineage; Lake Formation applies table and column permissions; CloudWatch alarms and a pipeline health dashboard track lag, failures and cost; KMS encryption and IAM roles scope every component.

In one line, the flow is: source database → DMS change data capture → S3 or Kinesis landing → AWS Glue transform → Redshift and/or Iceberg lakehouse → analytics, ML and agents.

What near real time means in practice

Near real time is a latency budget, not a product. The budget sets the design.

• Minutes (5 to 15). Micro-batch Glue jobs on CDC files in S3. Cheapest, simplest to operate, and enough for most operational reporting.
• Under a minute. Glue streaming jobs on Kinesis. Higher and steadier cost; justified for exception handling, fraud checks and live operations views.
• Seconds. Not an ETL problem. Push the event to the application directly and reconcile to the warehouse later.

We set the budget with the business per dataset. Orders and shipments may need minutes; the general ledger does not. Mixing the two in one pipeline is how costs escape.

Redshift warehouse or lakehouse: choosing the target

Amazon Redshift is the right target when the primary consumer is BI and SQL, the schema is stable, and the team knows warehousing. Redshift Serverless removes cluster management; QuickSight sits on top.

An Iceberg lakehouse on S3 is the right target when the same data feeds warehouse queries, machine learning features and agents, when raw and semi-structured data have to live next to curated tables, or when several engines need to read one copy. Redshift Spectrum, Athena, Glue and EMR all read Iceberg through the Glue Data Catalog.

Databricks on AWS is the lakehouse option when the organization has, or wants, one platform for data engineering, ML and analytics: Delta Lake tables, Unity Catalog for governance and Databricks SQL for BI, with the same DMS-based CDC feeding it through S3. Erbis is a Databricks Consulting Partner as well as an AWS Partner (Select Tier), so the choice is made on fit, not on which platform we know.

The pattern we recommend most often: CDC into an Iceberg or Delta lakehouse as the single source of truth, with Redshift or Databricks SQL as the serving layer for BI. It keeps one copy of the data and lets the analytics tools change without rebuilding the pipeline.

Governance with the Glue Data Catalog

A pipeline without a catalog is a set of files. The Glue Data Catalog gives the platform:

• Automatic schema discovery through crawlers and job-time registration, including schema evolution when the source adds a column.
• Lineage from source table to target table to dashboard, so an analyst can answer “where did this number come from” without a ticket.
• Centralized metadata that QuickSight, Athena, Redshift, SageMaker and Databricks all read.
• Access control through Lake Formation: table, column and row permissions, tag-based policies for PII, and audit logs of who queried what.

This is what makes self-service analytics safe. It is also what an auditor asks for.

Three use cases where this delivers value

1. Centralized data warehousing. Consolidate operational databases into one warehouse or lakehouse with freshness in minutes. Analysts and executives get a live view without engineering owning a maintenance queue.

2. Hybrid and multi-database integration. Oracle on-premises, PostgreSQL on RDS and a SaaS platform, joined in one model. Cross-system questions, such as margin by customer across ERP and CRM, stop being manual reconciliations.

3. Metadata management and data governance. Schema discovery, lineage and permissions in one place, so compliance reviews are faster and shadow datasets stop appearing.

A fourth is the reason most clients come to us now: feeding AI. For a multi-tenant SaaS platform, Erbis built an AI-powered BI platform on Amazon Redshift and QuickSight Q, with AWS Glue and AWS DMS moving the data, Keycloak for single sign-on and row-level security per tenant. The result was 60% faster insights and 80% less manual reporting effort. The same pipeline pattern applies whether the consumer is a dashboard or an agent.

Migrating from an existing batch pipeline

Most clients already have a nightly job. Replacing it is a sequence, not a cutover.

1. Inventory the consumers. Every dashboard, report and model that reads the warehouse, with the freshness each one actually needs. Most need minutes; a few need seconds; many are fine with hours and should stay on batch.
2. Stand up CDC alongside batch. DMS starts with a full load and then streams changes into a new schema or lakehouse table. The nightly job keeps running; nothing downstream changes yet.
3. Reconcile. Row counts, checksums and a handful of business totals compared between the batch target and the CDC target every day for at least two weeks. Differences are usually type conversions and soft deletes, and they are cheaper to find now.
4. Move consumers one at a time. Point the first dashboard at the new table, watch it, then the next. Models and agents move last, once their evaluation sets have been rebuilt on the new data.
5. Retire the batch job. Only after every consumer has moved and the reconciliation has been clean for the agreed period.

The whole sequence usually takes 6 to 10 weeks for a first source system, and less for each one after it, because stages 3 and 4 reuse the same tests.

Cost control and operations

A pipeline that runs every few minutes costs money every few minutes. Four practices keep it predictable.

• Right-size the latency. Micro-batch on S3 for most datasets, streaming only where the business case exists. This single choice sets most of the Glue bill.
• Partition and compact. Small CDC files accumulate; scheduled compaction into Iceberg or Parquet keeps query cost and Glue runtime down.
• Alert on lag and cost, not only on failure. A pipeline that is running but 40 minutes behind is a failure to the business. CloudWatch alarms on end-to-end lag and daily spend are part of the delivery.
• Own the runbook. Reprocessing a window, adding a column, rotating credentials, and pausing a source are documented procedures the client’s team can run. We stay involved, but the pipeline is not a dependency on us.

From dashboards to AI: why freshness matters for models and agents

A model is only as current as its features. An ETA model scored on yesterday’s shipment status is wrong by definition; the shipment ETA model Erbis built on SageMaker across 304,441 shipments depended on current lane and carrier data. Agents that act inside operations, releasing an order, drafting a customer update, flagging an exception, need the current state, not a snapshot.

Near real-time ETL is therefore the first component of an AI platform, not a BI convenience. The lakehouse it feeds is where features are computed, where evaluation sets are built and where agent actions are logged. Erbis’s data and AI platforms practice builds the pipeline, the lakehouse and the ML and agent layers together, so the latency budget is set by what the model needs.

Why Erbis

Erbis is an AWS Partner (Select Tier) and a Databricks Consulting Partner, holds ISO 27001 certification, and has run data and AI systems in production since 2017. We design the architecture, implement the pipelines, apply cost and scaling practices, and stay involved as volumes grow. Because the platform runs in your account, you keep control of security, cost and compliance, and the code is yours from day one.

FAQ

Is AWS Glue suitable for near real-time ETL?

Yes. Glue supports streaming jobs on Kinesis and Kafka for sub-minute latency and micro-batch jobs on S3 for latency in minutes, with job bookmarks for incremental processing and Data Quality rules for validation. The choice between them is set by the latency budget and the cost you accept.

Does this require changes to our production databases?

Minimal ones. CDC reads the transaction log, so it needs log access and, for some engines, supplemental logging or logical replication enabled. It does not add query load the way full extracts do.

Can we keep Snowflake or Databricks as the target?

Yes. The CDC and Glue stages are the same; only the target changes. Snowflake on AWS, Databricks on AWS and Redshift are all supported. For an open lakehouse, Iceberg on S3 with the Glue Data Catalog keeps the data readable by all of them.

How long does implementation take?

A first pipeline from one source to one target with cataloging and monitoring typically runs in weeks, not months, and can be scoped as a fixed-fee 4-week AI pilot. Additional sources are added incrementally.

If your organization is ready to stop waiting on data and start acting on it, get in touch with the Erbis team.