Does staging make Teradata event ingestion faster? In this synthetic test, staging through individual insert calls took about as long as individual MERGE calls. Sending the staging rows through one array-bound call and then running one MERGE reduced elapsed time sharply. The useful distinction is how rows reach the database, as well as how they are merged.
This article compares four loading methods on Teradata Database 20.00.28.81. Every method must produce the same 250 target rows from 275 incoming events. Complete source, input data, expected results, execution plans and all measured runs are available below.
The event-loading problem
A Kafka or JMS pipeline feeding Apache NiFi eventually reaches a database writer. One practical design uses individual parameterized MERGE statements; another stages a batch and merges it as a set. Before comparing them, define the business rule. In this experiment, an existing event ID is retained unchanged. For a new event ID appearing more than once in the batch, the first arrival wins. There are no matched updates or deletes.
The benchmark isolates these database loading methods using the Teradata Python driver. It does not run Kafka, NiFi, JDBC or a custom NiFi processor. Use the findings to choose what to measure in your own flow, rather than treating them as NiFi throughput figures.
A fixture with duplicates and conflicting values
| Item | Value |
|---|---|
| Initial target | Keys 1–100; metric = -event_id |
| First 250 events | Keys 1–250; metric = event_id × 10 |
| Later 25 events | Keys 151–175; conflicting metric = 999999 |
| Expected result | 250 target rows; 150 new rows; original 100 unchanged |
| Repeated new IDs | 151–175 retain their first value, not 999999 |
Row counts alone would miss an incorrect winning value. Each run reads all target columns in event-ID order and compares the complete result against the independently constructed expected list. An intentional change to one stored metric was detected before accepting the timed runs. The CSV files make the input order and expected values inspectable.
Target and staging tables
CREATE MULTISET TABLE target_events (
event_id INTEGER NOT NULL,
metric INTEGER NOT NULL
) UNIQUE PRIMARY INDEX (event_id);
CREATE VOLATILE MULTISET TABLE stage_events (
event_id INTEGER NOT NULL,
metric INTEGER NOT NULL,
arrival_seq INTEGER NOT NULL
) PRIMARY INDEX (event_id)
ON COMMIT PRESERVE ROWS;The target uses a unique primary index on the event ID. Staging permits repeated keys. The synthetic arrival sequence is unique, so it gives ROW_NUMBER a deterministic ordering. In a real pipeline, choose an ordering rule that remains meaningful during replay, partitioning and late arrival; an arbitrary wall-clock timestamp is not automatically sufficient.
Four methods, with the same insert-only result
| Method | Data submission | Commit policy |
|---|---|---|
| Individual MERGE, autocommit | 275 individual MERGE calls | Autocommit |
| Individual MERGE, one transaction | 275 individual MERGE calls | One transaction |
| Individual staging INSERTs, one MERGE | 275 individual INSERT calls, then one MERGE | One transaction |
| Array-bound staging INSERT, one MERGE | One array-bound INSERT call, then one MERGE | One transaction |
The individual statement binds one event at a time. Question marks below are driver parameters, not literal SQL values:
MERGE INTO target_events AS t
USING (SELECT CAST(? AS INTEGER) AS event_id, CAST(? AS INTEGER) AS metric) AS s
ON t.event_id=s.event_id
WHEN NOT MATCHED THEN INSERT (event_id,metric) VALUES (s.event_id,s.metric);Both staging methods use the same insert statement and the same set-based MERGE. The difference is whether Python calls the insert once per row or passes the list of parameter rows in one call:
INSERT INTO stage_events (event_id,metric,arrival_seq) VALUES (?,?,?);
MERGE INTO target_events AS t
USING (SELECT event_id,metric FROM stage_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY arrival_seq)=1) AS s
ON t.event_id=s.event_id
WHEN NOT MATCHED THEN INSERT (event_id,metric) VALUES (s.event_id,s.metric);The QUALIFY expression keeps the first arrival for each source key before merging. Without an explicit source-duplicate policy, a set-based rewrite may change results or fail. This policy is appropriate only when retaining the first event is the intended behavior. It is not a general change-data-capture algorithm.
# Individual staging calls:
for row in staged:
cur.execute(STAGE_INSERT, row)
# Array-bound staging call:
cur.execute(STAGE_INSERT, staged)
# Both staging methods then run:
cur.execute(MERGE_SET)
con.commit()Measured elapsed time
Each method ran once as a warmup and five times for measurement. Method order was rotated and reversed between rounds. Every measured load started from the same 100 target rows and an empty staging table. The timer includes data submission, staging-list construction where used, the MERGE and commit. Login, DDL, resets, EXPLAIN, query-band setup and validation reads are outside the timer. No extra wait to accumulate a streaming batch is modeled.
| Method | Median seconds | Min–max seconds |
|---|---|---|
| Individual MERGE, autocommit | 6.2402 | 5.9093–6.4598 |
| Individual MERGE, one transaction | 5.7880 | 5.7702–5.7973 |
| Individual staging INSERTs, one MERGE | 5.8324 | 5.8175–5.8544 |
| Array-bound staging INSERT, one MERGE | 0.1047 | 0.1041–0.1060 |
Individual staging inserts still require a call for every event. Their elapsed time is therefore an important control: replacing many MERGE statements with many INSERT statements and one MERGE did not remove the repeated calls. Array-bound staging changes that submission pattern. The experiment supports batching as a practical optimization to test; it does not establish how much time was spent in the network, driver, parsing, locking or commit individually.
Database CPU, I/O and logged requests
DBQL was enabled only for the disposable benchmark user. Each load carried a method-and-round query band. The following medians sum logged INSERT, MERGE, BT and ET requests for one complete load; setup, query-band statements and result-validation queries are excluded. The request counts were checked on every round. TotalIOCount is a database I/O counter, not bytes transferred or network calls.
| Method | Logged requests | AMP CPU s | Parser CPU s | TotalIOCount |
|---|---|---|---|---|
| Individual MERGE, autocommit | 275 | 1.2040 | 0.1160 | 35708 |
| Individual MERGE, one transaction | 277 | 1.0720 | 0.1320 | 35108 |
| Individual staging INSERTs, one MERGE | 278 | 0.1320 | 0.9000 | 3697 |
| Array-bound staging INSERT, one MERGE | 4 | 0.0120 | 0.0200 | 933 |
The individual-staging control also changed the resource profile: median AMP CPU and TotalIOCount fell relative to individual transactional MERGE, while parser CPU increased. Its elapsed time stayed close to the individual-MERGE method. Lower database I/O alone therefore did not predict a faster client load in this fixture.
For this driver and fixture, the two staging methods logged 278 and 4 included requests respectively. The array-bound insert contains all 275 input rows in one logged insert request. These counts are observed results, not a guarantee for larger batches or another driver. The download includes per-run metrics so the resource comparison can be inspected alongside the elapsed times.
Execution plans: estimates, not timings
The individual plan was captured for the new key 101. The set-based plan was captured with 100 target rows and 275 staging rows before the timed runs. Both complete plans are included in the download. Their estimates describe the optimizer plan at that point; the measured wall-clock times come from executing the loads. No statistics were explicitly collected in this experiment.
Individual MERGE plan excerpt:
1) First, we lock DWP_MERGE_E242F5DA52.target_events in TD_MAP1 for
write on a reserved RowHash to prevent global deadlock.
2) Next, we lock DWP_MERGE_E242F5DA52.target_events in TD_MAP1 for
write.
3) We do an INSERT step into Spool 2.
4) We do a single-AMP RETRIEVE step from Spool 2 (Last Use) by way of
an all-rows scan into Spool 1 (used to materialize view, derived
table, table function or table operator s) (all_amps), which is
redistributed by the hash code of (:? (INTEGER)) to all AMPs in
TD_MAP1. Then we do a SORT to order Spool 1 by row hash. The
size of Spool 1 is estimated with high confidence to be 1 row (29
bytes). The estimated time for this step is 0.00 seconds.
5) We do an all-AMPs merge with unmatched inserts into
DWP_MERGE_E242F5DA52.target_events from Spool 1 (Last Use) with a
condition of ("DWP_MERGE_E242F5DA52.target_events.event_id =
EVENT_ID"). The number of rows merged is estimated with high
confidence to be 1 row. The estimated time for this step is 1.61
seconds.
6) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.The download contains the full plan, including any lines omitted from this excerpt.
Set-based MERGE plan excerpt:
1) First, we lock DWP_MERGE_E242F5DA52.target_events in TD_MAP1 for
write on a reserved RowHash to prevent global deadlock.
2) Next, we lock DWP_MERGE_E242F5DA52.target_events in TD_MAP1 for
write.
3) We do an all-AMPs STAT FUNCTION step in TD_MAP1 from
DWP_MERGE_E242F5DA52.stage_events by way of an all-rows scan with
no residual conditions into Spool 5 (Last Use), which is built
locally on the AMPs in TD_MAP1. The result rows are put into
Spool 3 (all_amps), which is built locally on the AMPs. The size
is estimated with high confidence to be 275 rows (11,275 bytes).
The estimated time for this step is 0.00 seconds.
4) We do an all-AMPs RETRIEVE step in TD_MAP1 from Spool 3 (Last Use)
by way of an all-rows scan with a condition of ("Field_4 = 1")
into Spool 1 (used to materialize view, derived table, table
function or table operator s) (all_amps), which is redistributed
by the hash code of (DWP_MERGE_E242F5DA52.stage_events.event_id)
to all AMPs in TD_MAP1. Then we do a SORT to order Spool 1 by row
hash. The size of Spool 1 is estimated with low confidence to be
207 rows (6,003 bytes). The estimated time for this step is 0.00
seconds.
5) We do an all-AMPs merge with unmatched inserts into
DWP_MERGE_E242F5DA52.target_events from Spool 1 (Last Use) with a
condition of ("DWP_MERGE_E242F5DA52.target_events.event_id =
EVENT_ID"). The number of rows merged is estimated with low
confidence to be 207 rows. The estimated time for this step is
1.62 seconds.
6) We spoil the parser's dictionary cache for the table.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> No rows are returned to the user as the result of statement 1.The download contains the full plan, including any lines omitted from this excerpt.
Replay and rollback checks
Replaying the same ordered input preserved the expected target for all four methods, using each method’s own commit policy. This demonstrates idempotent target contents for this fixture. It does not establish exactly-once delivery across a message broker, NiFi and Teradata, or prove that a replay reordered across batches would choose the same winner.
For each of the three explicit-transaction methods, the test loaded the batch, attempted an invalid numeric conversion before commit, and rolled back. The target returned to its original 100 rows. This checks one conversion-failure path. It does not cover lost connections, process crashes, ambiguous commit outcomes, deadlocks or concurrent writers. Individual autocommit writes do not share that batch rollback boundary.
Test environment and limitations
The test used Teradata Database 20.00.28.81, 4 configured AMPs, target map TD_MAP1, Python 3.12.3 and teradatasql 20.0.0.72 in TERA/BTET mode. The database VM had four vCPUs and 8 GiB assigned memory. The Python client ran on the Linux lab host. This was a shared virtualized lab with existing swap use; other workloads were not isolated and caches were not reset.
The workload is deliberately small: 275 narrow incoming events, one writer and one fixed batch size. It does not measure sustained throughput, realistic event-width variation, skewed keys, multi-node NiFi behavior, batch-fill delay or tail latency. Larger batches can reduce calls while increasing waiting time, memory use, lock duration and retry cost. Measure those trade-offs before choosing a production setting.
What to change in a NiFi loading design
Inspect how the writer actually submits rows. A processor’s Batch Size can group work without necessarily producing one set-based MERGE or one array-bound staging request. Check the implementation and the database request log. Then measure equivalent target results, end-to-end latency, rejected rows, retries and database resources under representative load.
Keep duplicate handling and commit policy explicit when replacing a writer. This experiment’s insert-only first-arrival rule would be wrong for a pipeline that must apply the latest update or propagate deletes. For the SQL background, see Teradata MERGE INTO versus UPDATE. For another continuous-loading approach, see Teradata TPump.
Download and reproduce the experiment
Download the complete Teradata MERGE evidence bundle: executable benchmark, pinned driver requirement, complete synthetic CSV files, expected target, all 24 run records, DBQL metrics and both execution plans. The README describes permissions, timing boundaries and cleanup. Run the harness only in a disposable lab; it creates and removes its own user and flushes the global DBQL cache.
ZIP SHA-256: 4d3b3db441f7a1fb30a9b1882c14a096aa083be1e1902429af049c77b3291394. The harness uses Python assertions for validation, so run it without the -O option. A changed expected request count is a reason to investigate driver behavior, not to bypass the check.
References: Teradata Python driver: autocommit and transactions; Teradata Python driver: parameterized execution; Teradata MERGE reference. Measurements and correctness checks above are from the downloadable DWHPro experiment.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.