Fast Snapshot Loading
Fast Snapshot Loading accelerates Database Reader snapshot loads by increasing the amount of source data that can be read in parallel. The feature includes three source-read capabilities: reading multiple tables concurrently, running multiple labeled queries, and reading table partitions in parallel. Depending on the source database and loading scenario, you may use one, two, or all three capabilities to increase source-read throughput.
Use Fast Snapshot Loading for initial loads, backfills, and historical bootstraps. Moving historical data faster helps downstream data warehouses, data lakes, databases, and AI context or feature stores begin operating with a complete baseline sooner.
Additionally, for all sources, in the event of initial load being interrupted, Striim does not re-read tables it already read (Fast Snapshot Recovery).
Note
While Fast Snapshot Loading can speed up the source-read phase, the end-to-end load time depends on additional factors including source database system resources, table size, network throughput, data pipeline architecture, target writer configuration, and the target data system resources.
Fast Snapshot Loading capabilities
Fast Snapshot Loading is an umbrella feature that includes three source-read capabilities: Database Reader read parallelism, multiple labeled queries, and partition-aware parallelism.
Capability | How it works | Supported sources |
Read parallelism | Read multiple tables concurrently. Each table is read by one thread. | All sources supported by Database Reader |
Multiple labeled queries | Runs up to 20 SELECT queries sequentially to read from the source database, or concurrently, if read parallelism is enabled. | MySQL, Oracle, PostgreSQL, and SQL Server |
Partition-aware parallelism | Detects source table partitions and reads the partitions sequentially or concurrently, if read parallelism is enabled. | MySQL, Oracle, PostgreSQL, and SQL Server |
Read parallelism
When Parallel Threads is set to its default value of 1, Database Reader reads one table at a time. To enable read parallelism, set Parallel Threads to 2 or more, and Database Reader will read tables concurrently. Each thread reads one table at a time. When a thread finishes reading a table, Database Reader assigns the next available table to that thread until it has read all selected tables.
When the Tables property specifies a list of tables, Database Reader selects tables in the order in which they are listed. If the Tables property uses a wildcard, the sequence is determined by the order in which the JDBC driver provides the list of tables.
Configure read parallelism in the Flow Designer
In the Database Reader properties, under Fast Snapshot Loading, set the value of Parallel Threads.
Operational considerations
For Fast Snapshot Loading to Azure Synapse, BiqQuery, Databricks, Fabric Data Warehouse, Microsoft Dataverse, or Snowflake, we recommend that you set the number of threads in the Database Reader to be the same as the number of threads in the writer. This value should not be higher than the number of tables to be read. For other targets, set to the default of 1.
For optimum performance, in all cases, we recommend that the number of Parallel Threads does not exceed the same number of cores in your Striim server.
Increasing Parallel Threads in the Database Reader increases concurrent activity against the source. Verify that the source database can sustain the additional load.
Read with multiple labeled queries
You can write up to 20 labeled SQL SELECT queries to read source data. Each query can read from one table or join multiple tables. You can preview each query to verify that Database Reader reads exactly the source data you need. You can also divide a large table into query-defined ranges and run those ranges concurrently.
Use cases
Divide a large source table into smaller ranges so that multiple threads can read the ranges concurrently.
Run multiple join queries from one Database Reader source.
Send query result sets to different downstream tables, files, Kafka topics etc.
Prerequisites
The database user must have permission to execute every query and read every referenced table or view.
Configure queries in the Flow Designer
In the Database Reader properties, under Data Selection, click on Add Queries.
Enter a Query Identifier that uniquely identifies the result set.
Enter a SQL SELECT statement.
Select Preview Query to verify that the query returns the expected columns and data.
Optionally, enter an Execution Priority.
Click Add Query to enter a new query, and repeat the above steps for each query, up to a maximum of 20 queries.
Select Save Queries.
To run queries concurrently, set Parallel Threads to 2 or more.
Query fields
Field | Required | Accepted value | Behavior |
Query Identifier | Yes | Letters, numbers, and underscores | Uniquely identifies the query. Striim treats the value as case-insensitive and converts it to uppercase. |
Query | Yes | One SQL SELECT statement | Striim submits the statement to the source database table. Use database-specific quoting and delimiters. |
Execution Priority | No | Unique integer from 1 through 20 | Lower numbers enter execution first. If omitted, queries retain their configuration order. |
How query priority works
Priority determines when a query becomes eligible for execution. Lower numbers have higher priority. For example, priority 1 is submitted before priority 3.
With one thread, Database Reader starts queries in priority order.
With multiple threads, Database Reader assigns the highest-priority pending queries to available threads first.
Priority does not reserve a thread, control event throughput, or guarantee that a query finishes before lower-priority queries.
When no priorities are specified, Database Reader uses the order in the Multi Query configuration.
TQL example: Read one table in parallel ranges
The following example divides an employee table into two nonoverlapping ranges. Each Query Identifier becomes the TableName value in the emitted WAEvent metadata.
CREATE SOURCE EmployeeSnapshot USING Global.DatabaseReader (
ConnectionURL: 'jdbc:postgresql://<host>:5432/<database>',
Username: '<username>',
Password: '<password>',
MultiQuery: [
{
"QueryIdentifier": "EMPLOYEE_LOW",
"Query": "SELECT * FROM public.employee WHERE employee_id <= 500000",
"Priority": 1
},
{
"QueryIdentifier": "EMPLOYEE_HIGH",
"Query": "SELECT * FROM public.employee WHERE employee_id > 500000",
"Priority": 2
}
],
ParallelThreads: 2,
QuiesceOnILCompletion: true
)
OUTPUT TO EmployeeSnapshotStream;Map query results to targets
For events produced by Multi Query, the event’s TableName metadata field contains the Query Identifier instead of a physical source-table name. Use the Query Identifier in downstream routing and target-table mappings.
CREATE TARGET EmployeeTarget USING Global.DatabaseWriter (
ConnectionURL: 'jdbc:postgresql://<target-host>:5432/<database>',
Username: '<username>',
Password: '<password>',
Tables: 'EMPLOYEE_LOW,public.employee_target;
EMPLOYEE_HIGH,public.employee_target'
)
INPUT FROM EmployeeSnapshotStream;WAEvent contents for the output of multiple labelled queries
For this query:
MultiQuery : [
{
"QueryIdentifier" : "EMP_DEPT_DATA",
"Query" : "
SELECT
e.EMP_ID,
e.EMP_NAME,
e.SALARY,
e.JOB_TITLE,
d.DEPT_ID,
d.DEPT_NAME,
d.LOCATION
FROM EMP e
JOIN DEPARTMENT d
ON e.DEPT_ID = d.DEPT_ID;
}
]A WAEvent output from the reader would look as follows:
WAEvent{
data: [1,"Deborah","480K","Field Engg","D1S ","9273 Thorne AV","Orchard Park"]
metadata: {"TableName":"EMP_DEPT_DATA","ColumnCount":7,"OperationName":"SELECT",
"OPERATION_TS":1681412863364}
userdata: null
before: null
dataPresenceBitMap: "fwM="
beforePresenceBitMap: "AAA="
typeUUID: {"uuidstring":"01edda2e-77f7-9b21-83c2-8e859085da65"}
};The QueryIdentifier populates the TableName field in WAEvent. To map the output of this query to the emp table in the target, you would specify EMP_DEPT_DATA,emp in the writer's Tables property
Recovery and completion
When application recovery is enabled, Striim tracks initial-load completion by Query Identifier. Keep Query Identifiers permanently associated with their queries so that recovery positions remain consistent. Do not add, rename, or modify query entries while recovery is enabled.
For Restart Behavior on IL Interruption
Keep target table data and Truncate target table are supported for Restart Behavior on IL Interruption.
Replace target table data is not supported because automatic schema creation is unavailable for query result sets.
When Quiesce on IL Completion is enabled, the application quiesces after every configured query has completed and downstream targets have processed the results.
Observability: Mon metrics
Database Reader adds the following initial-load mon metrics:
Query Info: Reports the query text, status, and rows read so far for each Query Identifier
MultiQuery Summary: Reports the total, completed, in-progress, and pending query counts.
Note: Database Reader does not calculate the total number of rows a custom query will return. Monitoring shows rows read and query status, but not a completion percentage based on a total-row count. |
Operational considerations
Automatic target schema creation is not supported with MultiQuery because a query result does not provide source constraints, keys and schema definition.
MultiQuery cannot be combined with the Database Reader Tables, Query, or Exclude Tables property.
Upgrade behavior for existing query-based applications
This upgrade behavior applies only to applications where Database Reader uses a SQL query to read from MySQL, Oracle, PostgreSQL, or SQL Server.
In Striim versions earlier than 5.4.2, Database Reader supported only one custom query through the Query property. After you upgrade to Striim 5.4.2:
An existing Query configuration appears in Flow Designer, and it continues to use the legacy Query behavior.
If you add another query in the Flow Designer, Striim automatically converts the existing query configuration to multiple labeled queries (using MultiQuery).
For TQL applications, you must manually convert Query to MultiQuery when you want to use the multiple labeled query feature.
Partition-aware parallelism
Partition-aware parallelism allows Database Reader to detect a partitioned table in the source and read the detected partitions individually or concurrently, if Parallel Threads is set to 2 or more.
Prerequisites
The database user must be able to read the selected tables and access the database metadata used to discover their partitions.
Configure partition-aware reads in the Flow Designer
In the Database Reader properties, under Fast Snapshot Loading, enable Partition-Aware Read to True.
How partition-aware reads work
Partition-aware reads apply only to partitioned source tables. If a table is not partitioned, Database Reader can still read the table, but partition-aware reads do not provide source-read acceleration.
When Parallel Threads is set to 1, Database Reader reads table partitions sequentially.
When Parallel Threads is set to 2 or more, Database Reader can use available threads to read different table partitions concurrently.
Database Reader emits rows from the original source table. The table partitioning is used only as an internal read strategy.
Note: Partition-aware reads operate at the table level and read every partition. To read only a specific partition or a subset of rows, use Query or Multi Query with database-specific SQL. |
TQL example: Enable partition-aware reads
CREATE SOURCE OrdersSnapshot USING Global.DatabaseReader ( ConnectionURL: 'jdbc:postgresql://<host>:5432/<database>', DatabaseProviderType: 'PostgreSQL', Username: '<username>', Password: '<password>', Tables: 'public.orders', FetchSize: 1000, ParallelThreads: 4, EnablePartitionAwareRead: true, QuiesceOnILCompletion: true ) OUTPUT TO OrdersSnapshotStream;
WAEvent metadata
Database Reader preserves the original table identity in the event metadata and adds the partition name. You can use PartitionName in continuous queries, routers, or monitoring logic when you need partition-level context.
metadata: {
"PartitionName": "public.orders_2026_01",
"TableName": "public.orders",
"EntityName": "public.orders",
"OperationName": "SELECT"
}Partition-aware recovery
Recovery remains table-based even though Database Reader reads a partitioned table one partition at a time. Striim marks the table in progress when any partition starts and marks it completed only after every partition finishes.
After a restart, Database Reader skips tables that completed before the interruption.
If a partitioned table was incomplete, Database Reader rereads every partition of that table.
Restart Behavior on IL Interruption applies to the table, not to individual partitions.
Observability: Mon metrics
The Table Info metric includes partition-level status and rows-read information. Parallel-thread metrics identify the table or partition currently assigned to a thread and retain a history of completed work.
Completed: Database Reader finished reading the partition.
In progress: A thread is currently reading the partition.
Pending: The partition is waiting for an available thread.
Operational considerations
Partition-level recovery is not supported. Select a restart behavior that prevents partially loaded target data from causing duplicates or conflicts when an incomplete table is read again
Fast Snapshot Recovery during initial load
When a Database Reader application with recovery enabled is stopped, halts, or terminates before the initial load has completed, when you restart the application, it will resume reading tables that have not been completely written to the target. Database Reader's Restart Behavior on IL Interruption setting gives you three choices for what the writer will do:
Keep target table data (default): The application will resume loading, from the beginning, the source tables that had not yet been fully written to the target before the application was interrupted, appending the data to any target tables with partial data. This will typically result in duplicate rows in the target or the application halting with a duplicate row error, which will require manual effort (described below) to resolve. This is the default because it is the closest to the behavior of Striim 4.x, in which recovery was not supported for Database Reader.
Replace target table (recommended): Striim will drop and re-create any target tables with partial data or no data, then resume loading, from the beginning, the source tables that had not yet been fully written to the target before the application was interrupted. This option requires Create Schema to be True, and the writer must have DROP permission on tables in the target schema.
Truncate target table: Striim will delete all data from target tables with partial data or no data, then resume loading, from the beginning, the source tables that had not yet been fully written to the target before the application was interrupted. The writer must have TRUNCATE permission on tables in the target schema.
Functionally, Truncate target table is similar to Replace target table except it does not require you to give Striim permission to drop tables in the target.
If you create the Database Reader application using a wizard, to change the Restart Behavior on IL Interruption setting you must modify the application in the Flow Designer.
In detail, depending on the initial load status of each source table, after the application is restarted Striim will do the following or, in the case of Keep target table data, you must do the following.
Table status: description | Keep target table data | Replace target table | Truncate target table |
|---|---|---|---|
WAITING_TO_PROCESS: Database Reader had not read the table before the application was interrupted. | If Create Schema is True, you must manually drop the table before restarting the application. If Create Schema is False, you must manually truncate the target table before restarting the application. | Striim will drop and re-create the target table. | Striim will attempt to truncate the target table. If the table does not exist, the application will halt. |
SCHEMACREATION_COMPLETED: Create Schema is True and the table has been created in the target, but Database Reader had not started reading the table's data before the application was interrupted. | You must manually truncate the target table before restarting the application. (Writing data may have begun even though the status had not yet changed to IL_INPROGRESS.) | Striim will drop and re-create the target table | Striim will truncate the empty target table. |
IL_INPROGRESS: Database Reader was reading the table when the application was interrupted. | You must manually truncate the target table before restarting the application. | Striim will drop and re-create the target table | Striim will attempt to truncate the target table. If the table does not exist, the application will halt. |
IL_COMPLETED: The table was fully written to the target before the application was interrupted. | No action required. | Database Reader will not read the table again. | Database Reader will not read the table again. |
There is no difference in this behavior when Enable Partition-Aware Read is enabled. If one or more partitions have been read before a restart, Striim will still start from the beginning and re-read all partitions.