Ingesting On-Prem Data with JDBC in Openflow: The 6-Processor Pattern

2026-07-27 · Source: Data Engineering on Medium · Field: Technology & Digital — Software Development & Engineering, Data Science & Analytics, Cloud Computing & IT Infrastructure · Depth: Intermediate, short

Summary

This article details a "6-Processor Pattern" for building a reusable, incremental data ingestion pipeline from on-prem SQL Server to Snowflake using Openflow. The pattern leverages a watermark-driven extraction to pull only changed rows, employing paginated JDBC reads via `GenerateTableFetch` and `ExecuteSQLRecord`. Data is transported using Avro and streamed into Snowflake via `PutSnowpipeStreaming`. The design emphasizes parameterization for table-by-table reuse and safe promotion across environments. Prerequisites include an Openflow runtime, SQL Server JDBC driver, a Snowflake target table, parameter contexts, and a reliable source watermark column. The walkthrough covers controller services like `HikariCPConnectionPool` and `SnowflakeConnectionService`, and record services such as `JsonRecordSetWriter`, `AvroRecordSetWriter`, and `AvroReader`. It also notes considerations for `PutSnowpipeStreaming` versus `PublishSnowpipeStreaming` and outlines limitations like watermark quality, `GenerateTableFetch` statefulness, and schema drift.

Key takeaway

For Data Engineers building robust data pipelines, adopting the 6-Processor Pattern for JDBC ingestion into Snowflake provides a standardized, incremental solution. You should parameterize your flows to ensure reusability and simplify onboarding new tables, minimizing technical debt. Be mindful of watermark column reliability and plan for deduplication if using "at least once" delivery, especially with `PutSnowpipeStreaming`. Consider `PublishSnowpipeStreaming` for new builds to align with current platform direction.

Key insights

A reusable 6-processor Openflow pattern enables incremental, parameterized SQL Server to Snowflake data ingestion.

Principles

• Repeatable patterns reduce technical debt.
• Watermark quality dictates incremental accuracy.
• Stateful processors require single-threaded execution.

Method

The 6-processor pattern involves `GenerateFlowFile`, `ExecuteSQLRecord` (for watermark), `EvaluateJsonPath`, `GenerateTableFetch`, `ExecuteSQLRecord` (for data), and `PutSnowpipeStreaming` to move incremental data.

In practice

• Parameterize ingestion flows for reuse.
• Configure `HikariCPConnectionPool` for source.
• Use `SnowflakeConnectionService` for target.

Topics

• Openflow
• JDBC Ingestion
• Snowflake Data Loading
• SQL Server Integration
• Incremental Data Pipelines
• Avro Data Format

Best for: Data Engineer, MLOps Engineer, AI Architect

Related on AIssential

• Building Declarative Data Pipelines with Snowflake Dynamic Tables: A Workshop Deep Dive — Declarative data pipelines with Snowflake Dynamic Tables automate ETL, reducing complexity and manual orchestration.
• Apache Airflow Document Ingestion Pipeline for RAG Systems — Production RAG ingestion requires robust orchestration, idempotency, and observability for reliable document processing.
• 10 Data Ingestion Optimization Techniques for Faster and Scalable Processing — Parallelizing data ingestion workloads is critical for achieving faster, more scalable processing and reducing latency by over 50%.
• Bad Data Will Find You: Validation Patterns for Streaming Pipelines — Robust data validation is critical for streaming pipelines to prevent silent corruption and state pollution from inevitable bad data.
• How Figma Upgraded Data Pipeline from Multi-Day Latency to Real-Time — Incremental synchronization via Change Data Capture significantly improves data freshness and reduces costs for large-scale data pipelines.
• Microsoft Open-Sources PostgreSQL Extension for In-Database Durable Execution — "pg_durable" enables resilient, in-database SQL workflows, eliminating external orchestration for fault tolerance.

See Counsel's argued verdicts on the open AI decisions leaders are weighing →

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.