What is the GenerateTableFetch and QueryDatabaseTable pattern for incremental database ingestion?

QueryDatabaseTable and GenerateTableFetch are the two primary patterns for incrementally ingesting data from relational database tables. Each has different performance characteristics and use cases.

QueryDatabaseTable: A simpler, single-processor approach. It issues a SELECT query using a configurable Maximum Value Columns setting to track the last seen value (typically a timestamp or auto-increment ID). On each execution, it queries only rows where the tracked column exceeds the stored state — an incremental read. It produces one FlowFile per execution containing all new rows. Suited for moderate-size increments on single tables.

GenerateTableFetch + ExecuteSQL: A scalable, parallelizable pattern for large tables. GenerateTableFetch queries the database to determine the range of new rows (min and max of the tracking column), then generates one SQL SELECT statement per partition chunk as FlowFile attributes. Each generated FlowFile is routed to ExecuteSQL, which executes the SQL and returns that chunk's result set. Multiple ExecuteSQL processors can run in parallel, fetching different partitions simultaneously — dramatically improving throughput for large incremental loads.

Both processors use NiFi's State Management to persist the last processed value across restarts, ensuring no rows are missed or re-read on processor restart.