Automatic partitioning

Overview

Etlworks can divide a relational database source into multiple transformations and process them concurrently, so a large table is extracted in parallel chunks instead of one sequential pass. There are two supported approaches:

• Automatic primary-key range partitioning — the recommended starting point. Etlworks discovers the table's primary key and calculates balanced ranges for you. No Partition SQL and no manually tokenized Source query are required.
• Manual/custom partitioning with Partition SQL — the advanced option, fully supported. Use it when you need physical database partitions, date ranges, custom boundaries, keyless tables, or other application-specific partitioning.

The two methods are mutually exclusive for a given transformation: a transformation cannot enable automatic primary-key partitioning and configure Partition SQL at the same time. Enabling both produces an explicit configuration error.

Availability

Automatic primary-key range partitioning is available in Etlworks 9.7.11 or newer.

When a flow runs on an Integration Agent, the Agent must also be upgraded to a version that supports this feature. Upgrading only the Host application is not sufficient for flows executed by an older Agent — an older Agent does not recognize the new configuration and will run the flow as a single transformation.

Choose a partitioning method

Neither method is deprecated. Automatic PK partitioning is the recommended default; Partition SQL remains fully supported and is preferable whenever you need custom partition criteria.

Automatic primary-key range partitioning

How it works

1. Etlworks discovers the table's primary-key columns using JDBC metadata.
2. It counts the rows returned by the effective Source query.
3. It scans the primary keys in sorted order and selects approximately balanced boundary values.
4. It generates non-overlapping primary-key ranges.
5. Each range becomes an independent transformation executed through the flow's bounded parallel thread pool.
6. All ranges write to the configured destination.

Ranges are based on ordered row positions, not equal numeric widths, so gaps and uneven values in a numeric key do not prevent balanced ranges. Duplicate sampled boundaries are removed, so low-cardinality or repeated key values cannot create empty ranges.

For a single-column key, the ranges are conceptually:

SELECT * FROM Orders WHERE OrderId <= ?
SELECT * FROM Orders WHERE OrderId > ? AND OrderId <= ?
SELECT * FROM Orders WHERE OrderId > ?

The first range has no lower bound, intermediate ranges are lower-exclusive and upper-inclusive, and the final range has no upper bound. Actual boundary values are passed as typed bind variables.

For a composite primary key, Etlworks generates lexicographic lower and upper predicates using the ordered primary-key columns, with typed bind variables. You do not need to construct those predicates manually.

The final range is intentionally open-ended, so a row inserted with a key above the maximum observed during planning still belongs to a range. This does not provide a transactionally consistent point-in-time snapshot — see Source consistency warning.

Configuration

On the transformation's Parameters tab:

• Automatically partition source tables by primary key — enables automatic PK-range partitioning.
• Automatic partition multiplier — controls how many primary-key ranges are planned per parallel thread. Default 1.

The flow-level Maximum Number of Parallel Threads setting controls how many transformations run at the same time. The number of ranges Etlworks requests is:

Requested partitions = Maximum Number of Parallel Threads × Automatic partition multiplier

The multiplier controls the number of work units, not the number of concurrently running threads. For example, with Maximum Number of Parallel Threads = 5 and Automatic partition multiplier = 2, Etlworks requests up to 10 primary-key ranges while no more than 5 run concurrently.

The actual number of ranges can be lower when:

• The table has fewer rows than the requested range count.
• Duplicate sampled boundary values reduce the number of safe ranges.
• The table is not eligible for automatic partitioning.

Start with a multiplier of 1. Increase it when ranges finish at substantially different times and additional queued work would keep all threads busy. Excessive ranges add planning, connection, query, and destination-load overhead.

Note: Do not enable Use Parallel Threads when processing sources by a wildcard for automatic PK ranges. Automatic PK ranges are already submitted for parallel execution. That setting applies to the manual/wildcard workflow described later.

Source query behavior

• A Source query is not required. Without one, Etlworks reads the concrete source table directly.
• If a Source query is configured, Etlworks preserves it and applies the primary-key range predicates around the query result.
• The Source query must return every primary-key column under its original column name.
• Filters in the Source query are respected when counting rows and planning ranges.
• Normal flow variables are resolved before planning.

For wildcard source transformations:

• Each concrete table matching the wildcard is evaluated independently, and each matched table with a primary key is divided into ranges on its own.
• Use {table} in a custom Source query where the concrete matched table name must be inserted.
• A query containing the literal configured wildcard is also resolved to the concrete table.
• If Etlworks cannot associate the query with the concrete wildcard table, the flow reports a clear configuration error rather than silently querying the wildcard name.

SELECT *
FROM {table}
WHERE archived = 0

When to use automatic PK partitioning

• One or more large relational tables have primary keys.
• A single-table extraction does not use the available source, network, Agent, or destination capacity.
• The destination can accept concurrent loads.
• You want parallel extraction without maintaining Partition SQL.
• A wildcard flow contains multiple large keyed tables.
• The primary key is single-column or composite and uses sortable, JDBC-compatible values.

When not to use it

Use manual Partition SQL or a regular single transformation when:

• The table has no primary key.
• A view does not expose primary-key metadata.
• A different column, date, tenant, physical partition, or business rule should define the ranges.
• The Source query does not return the primary-key columns under their original names.
• Exact point-in-time consistency is required while the source table is actively changing. Automatic ranges use separate database connections and do not by themselves guarantee that every range sees the same database snapshot.
• The source database cannot handle the additional concurrent queries or connections.
• The destination cannot safely accept concurrent writes.
• The overhead of planning and multiple queries would exceed the benefit for a small table.

Numeric or consecutive primary keys are not required; any sortable, JDBC-compatible primary key works.

When automatic partitioning is not used even when enabled

Enabling the checkbox is a request to partition eligible tables, not a guarantee that every table will be split. Etlworks logs the reason and processes the source as a single transformation when:

• The source is not a relational database.
• The table has no primary key.
• The source is a view without discoverable primary-key metadata.
• The requested partition count is one.
• The effective query returns zero or one row.
• Max Records is configured (the limit applies to the whole query, not to each range).
• Before Prepare Source Query is configured (the hook can change the SQL after ranges are planned).
• The Source query contains a token resolved only later during extraction, such as a high-watermark or SQL Server Change Tracking token.
• Required connection, driver, table, or metadata information is unavailable.
• The primary-key columns cannot be selected or ordered from the custom Source query.
• Boundary planning fails for the database or primary-key data type.

These cases are never silently ignored — the flow log always explains why Etlworks fell back to one transformation. An invalid wildcard Source query that cannot be mapped to a concrete table is a configuration error, not a silent fallback.

Source consistency warning

Partition boundaries remain non-overlapping, but multiple partition connections do not automatically create a shared transaction snapshot. If rows are inserted or deleted, or their primary-key values change, while extraction is running, the resulting dataset can reflect different points in time. Use a maintenance window, a stable source table, or an appropriate database-specific consistency strategy when an exact snapshot is required.

Destination considerations

Database destinations:

• Etlworks coordinates destination setup and finalization once per concrete table, not once per range.
• Automatic table and index creation is coordinated before concurrent partition loads begin.
• Still verify that the selected load action, indexes, constraints, and database capacity support concurrent writes.

File destinations:

• Every partition must resolve to a unique file name.
• Configure Add Suffix When Creating Files in Transformation on the destination file connection, for example uuid.
• Flow Inspection reports a critical issue when automatic PK partitioning writes to a file connection without a configured suffix.
• Do not rely on {PartitionId} as a replacement for the required destination connection suffix.

Manual/custom partitioning with Partition SQL

Partition SQL is the advanced option for when automatic primary-key ranges are not applicable or you need explicit control over the boundaries. You define Partition SQL — a query that returns one row per partition with the parameters Etlworks should use for that partition — and Etlworks generates a separate transformation per row, then runs them in parallel. Partition SQL does not have to use physical database partitions: it can be any SQL that produces start/end conditions (for example, date or numeric ranges).

The requirements in this section — in particular that a Source query is required — apply to Partition SQL only, not to automatic PK-range partitioning.

Use Partition SQL when you need any of the following: physical database partitions, date ranges, tenant ranges, custom SQL boundaries, keyless tables, or partition-specific destination names.

Set up Partition SQL

1. Create a flow where the source is a relational database and add a source-to-destination transformation.

   

2. Click Configure, select the Parameters tab, and enter Partition SQL.

   

3. For a wildcard source, enable Use Parallel Threads when processing sources by a wildcard for faster processing.

   

   Configure other parameters such as Action and Lookup fields.

   

4. Select the Mapping tab and enter the Source query.

   

Why is the Source query required?

With Partition SQL, the Source query is a template that adapts to the parameters returned by the Partition SQL for each partition.

1. Partition-specific extraction. Each partition represents a distinct segment of the dataset (date range, numeric range, partition number, …). The Source query is parameterized with values from the Partition SQL so each transformation extracts only its slice of the data.
2. Handles arbitrary partitioning strategies. Because Partition SQL doesn't have to use physical partitions, the Source query must adapt — for example by substituting dynamic start/end dates.
3. Drives parallel execution. One transformation per partition; each targets its own subset. Without a partition-specific Source query, partitions could overlap or miss data.
4. Ensures coverage and consistency. Partition tokens like {PartitionNumber}, {StartDate}, {EndDate} are substituted into the Source query so each row is processed exactly once.

Partition-specific Source query examples

Based on partition number

For databases that use partition numbers, use the $PARTITION function to target a single partition:

SELECT * FROM Orders
WHERE $PARTITION.OrderDateRangePF(OrderDate) = {PartitionNumber};

Based on date ranges

SELECT * FROM Orders
WHERE OrderDate >= '{StartDate}' AND OrderDate < '{EndDate}';

Partitioning examples by database

The Partition SQL returns partition-specific parameters that are passed to the Source query for data extraction. The examples below cover both partitioning styles:

• Physical partitions are database-managed segments defined at the schema level (range, list, hash). Queries can target a specific partition for better performance.
• Logical partitioning divides the data based on user-defined conditions (date ranges, numeric ranges, …) and is implemented at the ETL level. It does not require any database-level partitioning and is the most flexible option.

Oracle — physical partitions

Partition SQL — retrieves partition information for a table partitioned by date:

SELECT partition_name AS PartitionName, high_value AS HighValue
FROM user_tab_partitions
WHERE table_name = 'ORDERS';

Source query — extracts data from the named partition:

SELECT * FROM Orders PARTITION ({PartitionName});

SQL Server — physical partitions

Partition SQL:

SELECT
  p.partition_number AS PartitionNumber,
  prv.value          AS HighBoundaryValue
FROM sys.partitions p
JOIN sys.partition_range_values prv
  ON p.partition_number = prv.boundary_id
JOIN sys.tables t
  ON p.object_id = t.object_id
WHERE t.name = 'Orders'
  AND p.index_id IN (0, 1);

Source query — builds a dynamic query that filters by partition number:

DECLARE @PartitionNumber INT = {PartitionNumber};
DECLARE @TableName NVARCHAR(128) = 'Orders';
DECLARE @PartitionFunction NVARCHAR(128) = 'OrderDateRangePF';
DECLARE @PartitionColumn NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);

SELECT TOP 1 @PartitionColumn = c.name
FROM sys.index_columns ic
JOIN sys.columns c
  ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.partition_ordinal > 0
  AND OBJECT_NAME(ic.object_id) = @TableName;

IF @PartitionColumn IS NULL
BEGIN
  RAISERROR ('No partition column found for the specified table.', 16, 1);
  RETURN;
END

SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) +
           N' WHERE $PARTITION.' + QUOTENAME(@PartitionFunction) +
           N'(' + QUOTENAME(@PartitionColumn) + N') = ' +
           CAST(@PartitionNumber AS NVARCHAR(10));

EXEC sp_executesql @SQL;

MySQL — physical partitions

Partition SQL:

SELECT PARTITION_NAME AS PartitionName
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'Orders';

Source query:

SELECT * FROM Orders PARTITION ({PartitionName});

PostgreSQL — physical partitions

Partition SQL — lists child partitions of a range-partitioned table:

SELECT inhrelid::regclass AS PartitionTableName
FROM pg_inherits
WHERE inhparent = 'Orders'::regclass;

Source query — queries the child partition directly:

SELECT * FROM {PartitionTableName};

DB2 — physical partitions

SELECT PARTITION_NUMBER AS PartitionNumber, LIMITKEY AS HighValue
FROM SYSCAT.DATAPARTITIONS
WHERE TABNAME = 'ORDERS';

SELECT * FROM Orders WHERE MOD(OrderID, {PartitionNumber}) = 0;

AS400 — physical partitions

SELECT PARTITION_NUMBER AS PartitionNumber, PARTITION_NAME AS PartitionName
FROM QSYS2.SYSTABLEPARTITIONS
WHERE TABLE_NAME = 'ORDERS';

SELECT * FROM Orders WHERE PartitionColumn = {PartitionNumber};

Informix — physical partitions

SELECT partnum AS PartitionNumber
FROM sysfragments
WHERE tabname = 'Orders';

SELECT * FROM Orders WHERE PARTITION BY (OrderID) = {PartitionNumber};

Logical partitioning examples

Oracle — logical partitioning

SELECT TO_DATE('2022-01-01', 'YYYY-MM-DD') AS start_date,
       TO_DATE('2022-02-01', 'YYYY-MM-DD') AS end_date
FROM DUAL
UNION ALL
SELECT TO_DATE('2022-02-01', 'YYYY-MM-DD'),
       TO_DATE('2022-03-01', 'YYYY-MM-DD')
FROM DUAL
UNION ALL
SELECT TO_DATE('2022-03-01', 'YYYY-MM-DD'),
       TO_DATE('2022-04-01', 'YYYY-MM-DD')
FROM DUAL;

SELECT * FROM Orders
WHERE OrderDate >= {start_date} AND OrderDate < {end_date};

SQL Server — logical partitioning

WITH NumberRanges AS (
  SELECT 1    AS start_id, 1000 AS end_id
  UNION ALL
  SELECT 1001, 2000
  UNION ALL
  SELECT 2001, 3000
)
SELECT start_id, end_id FROM NumberRanges;

SELECT * FROM Orders WHERE OrderID BETWEEN {start_id} AND {end_id};

MySQL — logical partitioning

SELECT '2022-01-01' AS start_date, '2022-01-31' AS end_date
UNION ALL
SELECT '2022-02-01', '2022-02-28'
UNION ALL
SELECT '2022-03-01', '2022-03-31';

SELECT * FROM Orders
WHERE OrderDate >= '{start_date}' AND OrderDate < '{end_date}';

PostgreSQL — logical partitioning

SELECT generate_series('2022-01-01'::date, '2022-03-01'::date, '1 month') AS start_date,
       (generate_series + '1 month'::interval - '1 day'::interval)::date AS end_date;

SELECT * FROM Orders
WHERE OrderDate >= '{start_date}' AND OrderDate < '{end_date}';

DB2 — logical partitioning

SELECT '2022-01-01' AS start_date, '2022-01-31' AS end_date FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT '2022-02-01', '2022-02-28' FROM SYSIBM.SYSDUMMY1;

SELECT * FROM Orders WHERE OrderDate BETWEEN '{start_date}' AND '{end_date}';

AS400 — logical partitioning

SELECT DATE('2022-01-01') AS start_date, DATE('2022-01-31') AS end_date FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT DATE('2022-02-01'), DATE('2022-02-28') FROM SYSIBM.SYSDUMMY1;

SELECT * FROM Orders WHERE OrderDate BETWEEN '{start_date}' AND '{end_date}';

Informix — logical partitioning

SELECT 1 AS start_id, 1000 AS end_id FROM systables WHERE tabid = 1
UNION ALL
SELECT 1001, 2000 FROM systables WHERE tabid = 1;

SELECT * FROM Orders WHERE OrderID BETWEEN {start_id} AND {end_id};

Using partition parameters in Source queries and transformations

Columns returned by the Partition SQL can be referenced anywhere in the Source query or in the TO part of the source-to-destination transformation using {ColumnName}. For example, if Partition SQL returns StartDate and EndDate:

SELECT * FROM Orders
WHERE OrderDate >= '{StartDate}' AND OrderDate < '{EndDate}';

You can also use these tokens to name output files dynamically:

orders_{EndDate}.csv

The partition index is available as {PartitionId} — a 1-based number representing the row returned by the Partition SQL. Useful for uniquely identifying or naming partitions.

Troubleshooting and performance guidance

• Review the flow log to confirm how many primary-key ranges were planned.
• If the log says the source is processed as one transformation, use the logged reason to identify the unsupported setting or missing primary key.
• More partitions do not always mean better performance.
• Increase Maximum Number of Parallel Threads only while the source database, Agent, network, and destination still have unused capacity.
• Increase the Automatic partition multiplier when work is uneven, not merely to increase concurrency.
• Benchmark representative tables and monitor source load, connection count, Agent memory, destination concurrency, and total elapsed time.
• For file destinations, resolve the critical missing-suffix Flow Inspection finding before running the flow.