Configure Table Slicing

Slicing divides the source table into multiple independent chunks of data that can be transferred to the destination in parallel. Instead of copying the entire table in a single thread, XL Ingest splits the work across multiple threads, with each thread responsible for a portion of the data. When configuring slicing, select a column to slice on, typically the primary key or an indexed column with evenly-distributed values. XL Ingest divides the data into slices based on value ranges in the slice column. Each range becomes a slice that transfers independently.

Note: Date and time columns (for example DATE, DATETIME, TIMESTAMP) are not supported as slice columns in XL Ingest.

XL Ingest provides three slicing methods: Auto, Rule-based, and Manual. Select the method that best matches your table and how much control is needed over the slice plan.

Method

Description

When to use this method

Auto

XL Ingest analyzes the table and creates a slice plan automatically.

The table has a supported single-column primary key and you want XL Ingest to choose the slicing plan. Auto is the default method.

Rule-based

Select the slice column, inspect source statistics, and specify the target number of slices and the round-off or prefix value.

Auto cannot create a plan, or you want control over the slice column or target slice count.

Manual

Select the slice column and enter the slice boundaries manually.

The appropriate split points are known, or exact boundaries are needed.

To configure slicing in Table Setup tab in XL Ingest:

  1. On the Table Setup tab, select the table you want to slice.

  2. In the Define slices section, select Auto, Rule-based, or Manual.

  3. Optionally enter a Where condition. Enter only the condition; do not include the WHERE keyword. Only rows that match the condition are included in the slice plan.

  4. Configure the fields for the selected method, then start slicing.

  5. Open the table status to monitor the slicing task and sync the table after the slice plan is ready.

Auto Slicing

Auto slicing is selected by default. It analyzes the table and creates a slice plan without requiring you to choose the slice column, prefix, round-off, or number of slices.

To use Auto Slicing:

  1. In Table Setup, select the table you want to slice.

  2. In Define slices, select Auto.

  3. Optionally enter a Where condition.

  4. Select Auto Slice.

When slicing completes successfully, Flow displays Auto slicing completed and shows the generated slices in the Slices area.

XL Ingest determines how to divide the table into slices. It counts the rows, estimates the row size and transfer time, and uses the table's primary key as the slice column. It automatically determines the number of slices and their boundaries based on three limits, which you can adjust on the Configuration tab in the XL Ingest UI:

  • Slice - Max recs: 500,000 rows

  • Slice - Max size (bytes): 250 MB

  • Slice - Max extract duration (seconds): 6,000 seconds

If the table fits within a single slice, XL Ingest creates one Full Table slice instead of dividing the table.

After the task completes, click View Slicing Details to see the slice column and data type, row count, minimum and maximum values, estimated size, records per slice, size per slice, time per slice, number of slices, and the prefix or round-off used.

Limitations

Auto slicing supports MySQL, PostgreSQL, and SingleStore sources. It is available only when XL Ingest can identify a supported single-column primary key with a supported integer or string/text type. 

If Auto slicing cannot create a plan, Flow displays:

Auto slicing is not possible on this table. Please use Rule-based slicing. 

Rule-based Slicing

Rule-based slicing lets you choose the slice column and provide the target slicing parameters.

To use Rule-based slicing:

  1. In Table Setup, select the table you want to slice.

  2. In Define slices, select Rule-based.

  3. Select a slice column. Prefer an indexed, NOT NULL column with many distinct values, ideally the primary key.

  4. Optionally enter a Where condition.

  5. Select Count to retrieve the row count, minimum, maximum, estimated size, and estimated time from the source.

  6. Enter No. of Slices as the desired number of slices.

  7. For an integer column, enter Round-off (digits). For a text column, enter Prefix (chars).

  8. Review the calculated values and select Rule-based Slice.

The slicing parameters control how XL Ingest calculates slice boundaries:

  • Round-off (digits) controls the granularity of boundaries for integer columns. For example, a round-off of 4 groups IDs into buckets of 10,000. 

  • Prefix (chars) controls how many leading characters are compared for text columns.  A larger prefix value creates finer boundaries but results in a slower Count operation. 

  • No. of Slices specifies the target number of slices. The actual result can be one or two slices more or fewer depending on the data distribution.

Note: Rows where the slice column is NULL do not fall into any slice and will not be extracted. Choose a NOT NULL column to ensure all rows are captured.

Count and Rule-based Slice are separate operations and can run at the same time. However, Rule-based Slice is unavailable while a sync or slicing task is already queued or running for the table.

Manual Slicing

Manual slicing gives you control over the slice boundaries. Select a slice column and enter boundary values in ascending order, separated by spaces. Each adjacent pair defines a range that includes the lower value and excludes the upper value. The final range can be open-ended so that it includes all remaining values.

Understanding Slice Boundaries

In manual slicing, N boundary values create N-1 slices. For example, boundaries 100 500 1000 create two slices. The first slice includes rows with slice column values  >= 100 and < 500 and the second slice includes rows with values >= 500 and < 1000. To capture all records, set the first boundary less than or equal to the minimum value of the slice column, and set the last boundary either greater than any value that may occur during extraction, or leave it open-ended.

Required inputs for manual slicing:

  • Slice Column: The column to slice on.  

  • Slice Boundaries: Space-separated values that define how to split the data into slices.

To use manual slicing:

  1. In Table Setup, select a table you want to slice.

  2. In Define slices, select Manual.

  3. Select a slice column. Prefer an indexed, NOT NULL column with many distinct values, ideally the primary key.

  4. In the Slices field, enter space-separated boundary values in ascending order.

  5. Optionally, enter a Where condition to filter rows during extraction. In the Where field, enter only the condition, without the WHERE keyword.

    For example, enter:

    id > 1000
  6. Click Manual Slice to apply the configuration.

Once saved, XL Ingest displays Slices saved successfully and shows the boundaries in the Slices area.

Manual Slicing Examples

  1. Consider a table named customers with 10,000 records, where customer_id ranges from 1 to 10,000. Select customer_id as the slice column. In the Slices field, enter:

    1 2500 5000 7500 ~~OPEN~~

    This creates 4 slices: >= 1 and < 2500, >= 2500 and < 5000, >= 5000 and < 7500, and >= 7500 to Open (captures all remaining rows).

  2. To extract only records where customer_id is less than 10000, enter:

    1 2500 5000 7500 10000

    This creates 4 slices: >= 1 and < 2500, >= 2500 and < 5000, >= 5000 and < 7500, and >= 7500 and < 10000. Rows at or above 10000 are not extracted.

  3. For a table with a last_name column, enter:

    A G M S ~~OPEN~~

    This creates 4 slices: A to G, G to M, M to S, and S to Open.

Note: Start at or below the smallest value in your table. Use ~~OPEN~~ as the last boundary to capture all remaining rows, or use a specific value to set a hard upper limit.

Slicing Behaviour

  • When slicing starts, the table shows the task status as Scheduled, Running, Completed, or Failed.

  • Slicing a table for the first time marks it as selected for XL Ingest.

  • Reopening a table resumes an Auto, Rule-based, or Count task that is still queued or running.

  • If a table already has slices, Auto and Rule-based slicing ask for confirmation before replacing the existing slice plan. Manual slicing replaces the existing plan without a confirmation prompt.

  • Tables set to Skip Initial Extract in Ingest are flagged in the table list on the left.

  • If a slicing task fails, Flow shows the failure and retains the previous slice plan.

  • When more than 20 slices are generated, the UI shows the first 20 slice ranges and collapses the rest behind a +N more button. Click it to expand all slices. A Copy button is also available to copy all boundaries to the clipboard.

  • After slicing, each slice appears in Table Status as a separate row showing its range (e.g., ORD-000 to ORD-100). You can sync and compare each slice independently.

Using SQL on the Source to Compute Slice Boundaries

When using manual slicing, you can calculate boundaries with assistance from the source SQL database. You can create boundaries for slices of roughly equal size (an approximation of an equi-depth histogram, a type of histogram where the bars are of roughly equal length, and the boundaries of each bar vary in width) using SQL with the NTILE window function as follows:

Set number_of_buckets to the (size of your data / 10GB ) + 1  to create slices of size just under 10GB for large tables.

WITH RankedData AS (
SELECT
your_column,
NTILE(number_of_buckets) OVER (ORDER BY your_column)
AS bucket_number
FROM your_table
),
BucketBoundaries AS (
SELECT
bucket_number,
MIN(your_column) AS lower_bound,
MAX(your_column) AS upper_bound,
COUNT(*) AS row_count
FROM
RankedData
GROUP BY
bucket_number
)
SELECT
bb.bucket_number,
bb.lower_bound,
bb.upper_bound,
bb.row_count
FROM
BucketBoundaries bb
ORDER BY
bb.bucket_number;

Note: If your slice column uses UUID or other data types that do not support MIN/MAX functions directly, cast the column to a string type.

Consider the following table:

CREATE TABLE orderdetails (
orderNumber int(11) NOT NULL DEFAULT '0',
productCode varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
quantityOrdered int(11) DEFAULT NULL,
priceEach decimal(10,2) DEFAULT NULL,
orderLineNumber smallint(6) DEFAULT NULL,
PRIMARY KEY (orderNumber, productCode)
);

To calculate 10 slices on orderNumber column, you can run:

WITH RankedData AS (
SELECT
orderNumber,
NTILE(10) OVER (ORDER BY orderNumber) AS bucket_number
FROM
orderdetails
),
BucketBoundaries AS (
SELECT
bucket_number,
MIN(orderNumber) AS lower_bound,
MAX(orderNumber) AS upper_bound
FROM
RankedData
GROUP BY
bucket_number
)
SELECT
CONCAT(
GROUP_CONCAT(lower_bound ORDER BY bucket_number SEPARATOR ' '),
' ',
MAX(upper_bound) + 1
) AS boundaries
FROM
BucketBoundaries;

Note: This example uses MySQL syntax. If using PostgreSQL, replace GROUP_CONCAT with STRING_AGG. For other databases, refer to your database documentation for the equivalent aggregation function.

This query returns a space-separated list of boundaries:

10100 10135 10167 10194 10226 10263 10293 10322 10355 10389 10426

The orderNumber column in this example has a minimum value of 10100 and a maximum value of 10425. If new records may be inserted during extraction, you can either add ~~OPEN~~ as the final boundary to capture all records, or set the last  boundary to a higher specific value (for example, 99999) to include a buffer while maintaining an upper limit.

Sampling to Speed Up Boundary Calculation

For extremely large tables, you can use sampling on the source database table in SQL to speed up the scan. For example, sampling at one-hundredth of a percent on a 50 million row table can produce results with slice boundaries nearly identical to a full scan while reducing scan time to a small fraction of the full scan time.

Do not use sampling to calculate the minimum and maximum values of the slice column, as this can miss the actual minimum and maximum. Instead, calculate these values directly with a separate query or use domain knowledge to determine appropriate bounds for your data range.

Consult your source database documentation for details on sampling. For example, PostgreSQL supports the TABLESAMPLE clause for this purpose.

Last modified:

Was this article helpful?