Why I Built My Own Teradata Real-Time MERGE Processor for Apache NiFi

How time-bounded microbatching, automatically generated volatile tables, and set-based MERGE operations combine low latency with high throughput.

Apache NiFi provides powerful tools for ingesting data into relational databases. Its standard processors support JDBC batching, record-based INSERT and UPDATE operations, and configurable SQL execution.

But when I started designing real-time ingestion pipelines for Teradata, I had two requirements that conventional database loading approaches did not adequately address together.

I wanted very low latency and very high throughput.

I did not want to wait until thousands of records had accumulated before updating Teradata. Equally, I did not want to execute thousands of individual UPDATE statements when large numbers of records arrived.

My target was simple: Accumulate incoming records for no more than approximately 200 milliseconds before initiating a set-based MERGE into Teradata.

If 100,000 rows arrive during those 200 milliseconds, excellent. I can process those 100,000 rows using one optimized MERGE operation. If only five rows arrive, that is fine too. I do not wait for more data. The MERGE is triggered by the latency deadline.

This design became the foundation of my custom Teradata loading solution built around Apache NiFi and PutSQL.

1. The Traditional Tradeoff: Latency Versus Throughput

Data ingestion architectures frequently optimize for one of two objectives.

Low latency

Process every incoming record immediately. This works reasonably well when data volumes are small, but it can become expensive at scale.

Imagine receiving 100,000 database changes every second. Executing a separate UPDATE or INSERT for every record introduces substantial statement-processing overhead, even when the JDBC driver can batch requests efficiently.

High throughput

Collect large batches and process them together. This is more efficient for large data volumes, but it introduces another problem.

What if the system waits for 10,000 records before processing the batch? During peak activity, that threshold may be reached almost immediately. But during quiet periods, accumulating 10,000 records might take several minutes. The result is unpredictable latency.

For real-time data pipelines, that is not an acceptable design.

My solution does not depend on reaching a minimum number of rows. It uses time as the primary batching boundary.

2. The Solution: Time-Bounded Microbatching

The concept is straightforward. Incoming rows are accumulated into a Teradata volatile staging structure. Rather than waiting for a fixed batch size, the system initiates the MERGE according to a short time window.

For example, with a 200-millisecond accumulation target:

Incoming records within 200 ms	MERGE behavior
5 rows	MERGE 5 rows
100 rows	MERGE 100 rows
1,000 rows	MERGE 1,000 rows
10,000 rows	MERGE 10,000 rows
100,000 rows	MERGE 100,000 rows

The batch size automatically reflects the incoming data rate. The numbers are illustrative. Actual throughput depends on the hardware, JDBC loading capacity, table design and execution workload.

The important point is that the system does not artificially delay a small batch merely to obtain a larger one.

How the pipeline works

1. Receive records: Apache NiFi ingests changes from the source system, for example Kafka.
2. Accumulate records: Records are collected into a temporary staging structure associated with the Teradata MERGE operation.
3. Reach the time boundary: After approximately 200 milliseconds of accumulation, the batch is ready for MERGE execution, regardless of row count.
4. Execute the MERGE: A single MERGE INTO processes the accumulated rows against the target table.
5. Continue processing: New incoming records are accumulated for subsequent MERGE operations.

An important latency distinction

The 200-millisecond objective concerns the accumulation delay before initiating the MERGE. It does not mean every database change is committed within 200 milliseconds.

Overall latency also includes ingestion overhead, staging, execution time, transaction commits and any time spent waiting for database resources. These must be measured separately. Limiting the batching delay nevertheless removes one major source of unnecessary latency.

3. Why a Single MERGE Is Different from JDBC Batching

Apache NiFi’s PutDatabaseRecord supports JDBC batch execution. This reduces communication and execution overhead compared with sending every SQL statement separately.

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Email address I agree to receive the free book, Teradata updates and occasional promotions from DWHPro. I can unsubscribe at any time.

Email me the free book

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

However, JDBC batching and set-based MERGE processing are fundamentally different concepts.

Consider 50,000 incoming customer updates. With conventional JDBC batching, the driver may execute thousands of parameterized UPDATE statements in batches. With my approach, the incoming rows are staged, followed by a single MERGE operation.

Conceptually:

MERGE INTO Customer_Target AS tgt
USING VT_Customer AS src
ON tgt.Customer_ID = src.Customer_ID
WHEN MATCHED THEN
  UPDATE SET
    Customer_Name = src.Customer_Name,
    Customer_Status = src.Customer_Status
WHEN NOT MATCHED THEN
  INSERT (
    Customer_ID,
    Customer_Name,
    Customer_Status
  )
  VALUES (
    src.Customer_ID,
    src.Customer_Name,
    src.Customer_Status
  );

The significant difference is that the Teradata optimizer sees the complete source set. It can optimize the source-target join and distribute the work across the database’s parallel processing architecture.

For suitable workloads, this can reduce repetitive statement overhead and improve the efficiency of applying changes.

The improvement is workload-dependent. A single large MERGE is not automatically faster than every alternative. Primary index selection, row distribution, target-table characteristics and concurrency all matter.

Nevertheless, this is fundamentally more than a JDBC batching optimization. It changes how Teradata processes the incoming modifications.

I also recently published a Teradata MERGE benchmark comparing individual calls with array-bound staging. That synthetic experiment found that how rows reach the staging table matters greatly: staging through individual calls can erase much of the elapsed-time benefit. The benchmark uses the Teradata Python driver, not NiFi, and should not be mistaken for measured performance of this custom NiFi solution.

4. Automatic Volatile Table Generation: No Separate DDL Maintenance

The second major feature of my solution is automatic staging-table generation.

Traditional staging-and-MERGE architectures require developers to maintain a staging-table definition alongside the target table and MERGE SQL. That becomes tedious when the data model evolves.

Suppose a target table initially contains:

Column	Datatype
Customer_ID	INTEGER
Customer_Name	VARCHAR(100)
Customer_Status	CHAR(1)

Later, Customer_Name becomes VARCHAR(200), and a new column, Customer_Category, is added.

In a conventional implementation, developers may need to update the staging-table DDL as well as the MERGE statement. If they forget, the pipeline can fail because the staging structure is no longer compatible.

I wanted to eliminate this duplicated maintenance.

The MERGE statement defines the staging structure

My solution analyzes the MERGE statement and automatically generates the matching Teradata VOLATILE TABLE.

The staging definition is derived from the processing requirements rather than being maintained independently. When the MERGE changes, the required volatile-table structure is generated accordingly.

This means that compatible schema changes can be accommodated without manually synchronizing a second DDL definition. There is no separately maintained CREATE VOLATILE TABLE statement to forget.

And since the staging table is temporary, there is no need to deploy permanent staging objects into the database.

The MERGE defines what the database should do. The volatile table is generated to provide the data needed for that operation.

There are still limits. An invalid MERGE, an incompatible datatype conversion or a removed target column referenced by the SQL cannot be repaired automatically just by changing the staging table. But the solution eliminates an important class of staging-schema maintenance problems.

5. Why Volatile Tables Fit This Architecture

Teradata volatile tables are well suited to temporary data processing. Their definitions are scoped to a database session. They do not require permanent table objects, and they can be used as input sources for relational operations.

In my design, they provide temporary staging for the records being accumulated before MERGE execution.

• No permanent staging tables are required for every pipeline.
• The staging structure can be generated according to the MERGE requirements.
• Data can be processed within Teradata instead of being moved back into NiFi for relational operations.
• Individual database sessions can maintain independent volatile-table instances.

But proper session handling is essential.

Volatile tables are visible only within their Teradata session. JDBC connection pooling must therefore be managed carefully, ensuring that the staging data and MERGE execution use the appropriate session.

Transaction boundaries, concurrent execution, cleanup and the treatment of failed MERGE operations are equally important. These concerns are part of the database loading design, not simply NiFi configuration details.

6. Why Native NiFi Processors Are Not Sufficiently Sophisticated for This Use Case

Apache NiFi provides several processors that can implement parts of this solution. However, none provides the complete combination of time-bounded Teradata microbatching, MERGE-driven staging generation and set-based execution as one ready-made capability.

PutDatabaseRecord

PutDatabaseRecord is designed primarily to translate incoming records into SQL statements and execute them through JDBC. It supports batching, database metadata lookup and multiple statement types.

Newer versions also provide Pre-Processing SQL and Post-Processing SQL, making it possible to execute additional statements before and after loading records.

That is useful, but it does not automatically analyze a MERGE and generate the volatile-table structure required by that MERGE. It also does not independently implement my 200-millisecond staging-and-MERGE strategy.

MergeRecord

MergeRecord is particularly relevant because it supports record accumulation. It can combine multiple FlowFiles and release them based on record counts, size thresholds or maximum bin age.

This means NiFi already understands the general concept of time-bounded batching. However, the documented Max Bin Age configuration is expressed in seconds, minutes or hours rather than a native 200-millisecond setting.

More importantly, MergeRecord only creates a larger FlowFile. It does not manage a Teradata volatile-table session or execute a MERGE. Additional processors and coordination logic are required.

PutSQL

PutSQL can execute arbitrary SQL statements, including MERGE operations. I use it as part of my solution.

But the standard processor does not interpret a MERGE statement and generate an appropriate volatile table. Nor does it independently provide the complete real-time accumulation strategy and schema-aware staging logic.

UpdateDatabaseTable

UpdateDatabaseTable helps adapt database tables to incoming record schemas. For example, it can create missing tables and add columns.

But it operates from record schemas and database metadata, not from the semantics of a Teradata MERGE statement. It is not designed to generate and manage volatile staging structures for real-time MERGE workloads.

7. Comparing the Capabilities

Capability	Native NiFi	My solution
JDBC batch loading	Yes	Uses JDBC/SQL execution
Record aggregation	Yes, MergeRecord	Yes
Time-based aggregation	Yes, with limitations	Approximately 200 ms target
Batch size adapts to traffic	Possible	Yes
Single MERGE for accumulated rows	Requires custom design	Core design
Automatic volatile-table generation	No	Yes
Staging DDL derived from MERGE	No	Yes
Adaptation to compatible MERGE changes	Requires custom logic	Automatic
Teradata-specific execution strategy	Limited in generic processors	Central to design

The individual NiFi capabilities are valuable. The limitation is the absence of a single processor that combines them into an integrated Teradata-specific ingestion mechanism.

Recreating my approach using standard processors would require additional SQL generation, staging management, time-window orchestration and error handling. This is exactly the complexity I wanted to encapsulate.

8. Why Low Latency and High Throughput Can Work Together

One of the most interesting aspects of this architecture is how its behavior changes with the incoming data rate.

Scenario A: High-volume ingestion

A system produces a very large number of changes. During a 200-millisecond collection window, tens of thousands of rows arrive.

Instead of executing a massive number of individual target operations, the system stages these rows and applies them using one MERGE. A large incoming volume naturally produces a large batch. The efficiency of set-based processing becomes particularly valuable.

Scenario B: Low-volume ingestion

Now suppose only three records arrive during the same period. A batch-size-driven architecture might wait until additional records arrive. My solution does not.

The latency objective takes precedence over attempts to optimize the batch size. The three records are processed in the next MERGE. The execution overhead is higher per row, but with such low volumes, that overhead is usually less important than keeping the data current.

The principle

High traffic produces large, efficient MERGE batches. Low traffic produces small, timely MERGE batches.

The ingestion workload determines the number of rows being processed. The latency target determines how long the system is willing to accumulate them.

I consider this a more appropriate strategy for real-time analytical ingestion than simply maximizing the JDBC batch size.

9. What Still Needs to Be Measured

The architecture has clear functional advantages, but performance should always be measured. I would evaluate:

• Time from record arrival to MERGE initiation.
• Time spent loading the volatile table.
• MERGE execution duration.
• Time from record arrival to successful transaction commit.
• Sustained rows processed per second.
• Database CPU usage and spool consumption.
• Lock contention and concurrent workload behavior.
• Backpressure, retries and recovery after failures.

A particularly important question is what happens when MERGE execution takes longer than the desired collection window. For example, a MERGE might need 500 milliseconds even though the batching interval is only 200 milliseconds.

The implementation must then handle overlapping incoming batches without violating session isolation or causing uncontrolled database concurrency.

A 200-millisecond accumulation target is not a guarantee that Teradata can process an unlimited number of rows every 200 milliseconds. Sustainable throughput ultimately depends on the database’s ability to complete the work at least as quickly as new work accumulates over time.

These constraints do not invalidate the design. They are important considerations for production tuning and capacity planning.

10. Why I Did Not Choose a Commercial JDBC Driver

Commercial JDBC vendors often emphasize bulk operations and high-performance connectivity. CData, for example, publishes instructions for batch INSERT, UPDATE and DELETE processing against Teradata using Apache NiFi.

There is nothing inherently wrong with this approach. But my challenge was not simply how to send multiple SQL statements over JDBC.

I wanted to control the relationship between incoming data volume, processing latency, volatile staging and set-based Teradata execution.

Replacing the JDBC driver does not automatically provide this architecture. A commercial driver might offer useful connectivity features or performance improvements, but it does not remove the need for the Teradata-specific processing logic I developed.

I therefore preferred to extend NiFi’s existing SQL execution capabilities rather than introduce another dependency without a demonstrated benefit.

11. The Bigger Lesson: Data Integration Should Understand the Target Database

Generic data integration tools are designed to work across many different systems. That flexibility is valuable.

But Teradata is a massively parallel analytical database designed for executing relational workloads efficiently. Treating it merely as a destination for thousands of individual JDBC statements means potentially missing some of its strongest capabilities.

I believe ingestion pipelines should be designed around what the target platform does best.

For Teradata, that often means set-based SQL operations rather than record-by-record processing.

My architecture follows a simple division of responsibilities:

• NiFi handles ingestion and orchestration.
• Custom processing logic handles latency-aware batching and staging generation.
• Teradata handles parallel relational processing using MERGE.

This allows the ingestion architecture to benefit from NiFi’s flexibility without being constrained by the generic behavior of its database processors.

Conclusion

I built my Teradata MERGE solution because I wanted to solve three problems simultaneously.

First, low latency. Incoming records should not wait for an arbitrary batch-size threshold. My target is a maximum accumulation delay of approximately 200 milliseconds before initiating MERGE processing.

Second, high throughput. When large numbers of records arrive, they should be processed together using an optimized, set-based MERGE rather than thousands of individual target-table modifications.

Third, maintainability. The volatile staging structure should be generated automatically from the MERGE statement, eliminating the need to maintain separate staging DDL for compatible changes.

The result is an architecture that adapts naturally to changing data volumes.

If 100,000 rows arrive in the collection window, process those 100,000 rows together. If only five arrive, process those five without unnecessary waiting.

The architecture does not demand a large batch to justify processing, and it does not sacrifice the opportunity to process large batches efficiently when the workload provides them.

That is the real objective: low latency when traffic is low, high throughput when traffic is high, and automatically maintained staging structures in both cases.

For me, this represents a more sophisticated way of integrating real-time data streams with Teradata than conventional JDBC batching alone.

References and further reading

• DWHPro: Teradata MERGE Benchmark, Individual Calls vs Array-Bound Staging
• DWHPro: Teradata MERGE INTO vs. UPDATE
• Apache NiFi PutDatabaseRecord
• Apache NiFi MergeRecord
• Apache NiFi PutSQL
• Apache NiFi UpdateDatabaseTable
• CData: Teradata JDBC Batch Operations in Apache NiFi

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Email address I agree to receive the free book, Teradata updates and occasional promotions from DWHPro. I can unsubscribe at any time.

Email me the free book

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book