SQL Server Openflow CDC Demo

Real-time data replication from SQL Server to Snowflake using Change Data Capture

Note: For full end-to-end Openflow replication, SQL Server must be deployed on a publicly accessible host. This repo automates that on DigitalOcean (make do-up, see deploy/do/). Openflow connectors run inside Snowflake and need network access to your database.

┌─────────────────┐         ┌─────────────────┐         ┌─────────────────┐
│   SQL Server    │         │    Openflow     │         │   Snowflake     │
│                 │ ──CDC──▶│   (Apache NiFi) │ ──────▶ │                 │
│  Change Tracking│         │   Connectors    │         │  Destination    │
└─────────────────┘         └─────────────────┘         └─────────────────┘

The connector used in this demo is sqlserver-multidatabase (v0.26.0). A verified export of its flow (59 processors across Snapshot Load, Incremental Load, and Stream Staleness Prevention) is vendored at connector-flow/sqlserver-multidatabase.flow.json.

Webinar flow

This repo is Stages 1-2 of a three-stage build. Stage 3 lives in a separate framework repo.

Stage	What	Where
1. Ingest	Deploy SQL Server, load data, replicate into Snowflake with Openflow CDC	this repo (deploy/, loader/, containers/)
2. Explore	Profile the landed tables, infer keys/relationships, draft the business questions	workflows/data-exploration.md
3. Build agent	Semantic view + Cortex Agent + evaluations, as code	cortex-agents-dbt-project-template

Point Cortex Code at workflows/data-exploration.md once data has landed; it profiles the data and hands off to the template's WORKING-SESSION.md.

What is Openflow?

Openflow is Snowflake's managed data integration platform built on Apache NiFi. It moves data from external sources into Snowflake in real-time, with no ETL code required.

Feature	Description
Pre-built Connectors	A growing catalog of database and SaaS sources (PostgreSQL, MySQL, SQL Server, Salesforce, Kafka, Google Drive, and more)
Real-time CDC	Changes appear in Snowflake within seconds
Schema Evolution	Automatic handling of ALTER TABLE and DDL changes
Idempotent MERGE	Changes land in journal tables and are applied with MERGE, so re-running a cycle converges without duplicate rows

Documentation (docs-site)

The canonical connector documentation is a self-contained static site in docs-site/. No build step or CDN: open docs-site/index.html directly in a browser, or run make docs to serve it on http://localhost:3000.

Page	Covers
Overview	Value, pain points, and where the connector fits
CT vs CDC	SQL Server Change Tracking vs Change Data Capture
Use Cases	Representative workloads and industries
Network (+ EAI Setup, Troubleshooting)	Protocol/ports, external access integration, diagnostics
Snapshot (+ Performance)	Initial bulk load and how to tune it
Incremental (+ Performance)	Change capture, journal merge, and how to scale it
Staleness	Stream staleness prevention
Technical (Snapshot / Incremental / Staleness)	Per-workflow processor inventory from the verified flow

---

Quick Start

Option A: Deploy SQL Server to DigitalOcean (recommended for end-to-end)

make do-up      # provision a droplet, build + run SQL Server, write .env

This creates three demo databases on boot and writes SQL_HOST / SQL_PASSWORD into .env. Then load data:

cd loader && uv sync && uv run cdc-demo-loader load --scale dev

Tear down when finished: make do-down. See deploy/do/.

Option B: Point at an existing SQL Server

cp .env.example .env

Edit .env with your SQL Server connection:

SQL_HOST=your-sql-server-host
SQL_PORT=1433
SQL_USER=sa
SQL_PASSWORD=your-password

Databases (RetailDB, PaymentsDB, BillingDB) are created by the container on boot; the loader fills them.

---

Databases

Three realistic OLTP verticals, each with IDENTITY primary keys, foreign keys, audit columns, and Change Tracking enabled (Openflow requirement). Schema DDL lives in containers/sqlserver/sql/schema/.

Database	Vertical	Largest fact table
RetailDB	E-commerce / order management	OrderLineItems
PaymentsDB	Fintech double-entry ledger	LedgerEntries
BillingDB	SaaS subscription billing	UsageEvents

Data is generated and bulk-loaded by the Python loader in loader/ (seeded Faker data, tens of millions of rows at --scale demo).

Applications

The interactive Wizard SPA plus its backend. Connector docs are the static docs-site/ (served with make docs, not a container).

Component	Port	Description
containers/workflows/	3001	Vite React Wizard: Connect, Discover, Readiness, Config, Deploy, Live CDC. Interactive.
containers/fastapi/	8000	Python backend: all SQL Server access (connection, discovery, change simulator, stats, SSE).

The Wizard talks to FastAPI over REST + SSE (VITE_API_URL / VITE_SSE_URL).

What's Included

Component	Description
docs-site/	Canonical static connector documentation (open index.html or make docs)
connector-flow/	Vendored, verified sqlserver-multidatabase flow export (source of truth for the docs)
containers/sqlserver/	SQL Server image + schema DDL (sql/schema/) for the three demo databases
containers/workflows/	Vite React Wizard (Connect / Discover / Readiness / Config / Deploy / Live CDC)
containers/fastapi/	Backend API for all SQL Server access
loader/	Python (uv) loader: generates and bulk-loads Faker data; emits a live change stream
deploy/do/	DigitalOcean automation (make do-up / make do-down)
workflows/	Cortex Code skills: connector-setup.md (interactive connector deploy), data-exploration.md (Stage 2 runbook), and the vendored Stage 3 agent template

---

Commands

Command	Description
make up	Build + start workflows (3001) + FastAPI (8000)
make docs	Serve the static docs-site on http://localhost:3000
make down	Stop all containers
make logs	Follow container logs
make dev	Run workflows + FastAPI + docs locally without Docker

---

SQL Server Requirements

For Openflow CDC to work, your SQL Server tables need:

1. Primary keys on all replicated tables
2. Change Tracking enabled at database and table level

-- Enable on database
ALTER DATABASE YourDB SET CHANGE_TRACKING = ON 
  (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- Enable on each table
ALTER TABLE dbo.YourTable ENABLE CHANGE_TRACKING;

Leave TRACK_COLUMNS_UPDATED at its default (OFF): the connector does not use column-level change information, so enabling it only adds source storage and per-DML overhead.

---

Learn More

• Openflow Documentation: Official Snowflake docs
• SQL Server Connector: Setup and configuration