How to Replicate MySQL Data to Snowflake

Need MySQL data in Snowflake for analytics, reporting, or a migration? Supaflow handles the initial load and keeps new and updated rows in sync without a custom export job. This guide walks through the setup, the first sync, and the checks that confirm your Snowflake tables match the source.

Before you start, decide how each table records changes and how you want to handle deleted rows. Supaflow uses a date, datetime, or timestamp column to find inserts and updates. It does not read the MySQL binary log, so hard deletes require a separate approach.

For the shorter product and capability overview, see the MySQL to Snowflake connector page.

What You Will Build

You will create a pipeline that:

• Reads selected tables and columns from one MySQL database
• Loads the initial snapshot into a Snowflake database and schema
• Uses a reliable date, datetime, or timestamp column for later incremental runs
• Reads each incremental interval as a bounded [start, cutoff) window
• Optionally re-reads up to 86,400 seconds of history to catch late writes
• Merges rows in Snowflake using the source primary key
• Preserves MySQL-specific values such as TINYINT(1), BIGINT UNSIGNED, and SET

This is cursor-based replication, not binary-log CDC. It captures a changed row only when the selected cursor advances. It does not detect a row that was hard-deleted from MySQL because that row is no longer available to query.

Choose How Each Table Should Sync

Use this table to decide whether each MySQL table should run incrementally or as a full refresh.

Source table shape	Recommended mode	Reason
Has a primary key and an updated_at column that changes on every insert and update	Historical + incremental, then merge	The cursor finds changes; the primary key updates the existing Snowflake row
Append-only table with a reliable created_at column	Historical + incremental	Every new row advances the cursor; updates are not expected
Small lookup table with no reliable change timestamp	Full refresh	Re-reading the table is safer than pretending an unreliable cursor is complete
Rows can be hard-deleted and Snowflake must match exactly	Cursor sync plus reconciliation, or another CDC path	A cursor query cannot return a row after MySQL deletes it
Writes can arrive with an older timestamp	Incremental with lookback and merge	The overlap re-reads late rows; merge prevents duplicate destination rows

Do not select created_at for a table whose rows can change later. Use a column such as updated_at that advances whenever a business-relevant value changes; otherwise later updates can be missed.

The connector seeds date and datetime cursor windows at January 1, 2000. Use full refresh for tables that contain older records so the first load does not exclude them.

Prerequisites

• A Supaflow account (start free)
• A MySQL database reachable from the Supaflow agent
• A read-only MySQL user with SELECT on the tables you want to copy
• A Snowflake warehouse, database, schema, role, and credentials
• A stable primary key on tables you want to merge
• A date, datetime, or timestamp cursor on tables you want to run incrementally

See the MySQL source documentation for every connection field and the Snowflake destination documentation for destination permissions.

Step 1: Create a Read-Only MySQL User

Create a dedicated account instead of reusing an application administrator:

CREATE USER 'supaflow_reader'@'%' IDENTIFIED BY 'replace_with_a_secure_password';
GRANT SELECT ON `production_db`.* TO 'supaflow_reader'@'%';

For tighter access, grant only the tables required by the pipeline:

GRANT SELECT ON `production_db`.`customers` TO 'supaflow_reader'@'%';
GRANT SELECT ON `production_db`.`orders` TO 'supaflow_reader'@'%';
GRANT SELECT ON `production_db`.`order_items` TO 'supaflow_reader'@'%';

Replace % with the host or network range that should be allowed to connect when your MySQL account policy requires it. If the database is behind a firewall, allow the Supaflow network path or run the sync agent inside your VPC.

Test the credentials from an allowed network location:

mysql -h mysql.example.com -P 3306 -u supaflow_reader -p production_db

Step 2: Audit Primary Keys and Cursor Columns

List the tables and primary keys in the configured database:

SELECT
  t.TABLE_NAME,
  k.COLUMN_NAME AS primary_key_column
FROM information_schema.TABLES AS t
LEFT JOIN information_schema.KEY_COLUMN_USAGE AS k
  ON k.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND k.TABLE_NAME = t.TABLE_NAME
 AND k.CONSTRAINT_NAME = 'PRIMARY'
WHERE t.TABLE_SCHEMA = 'production_db'
  AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY t.TABLE_NAME, k.ORDINAL_POSITION;

Then inspect candidate cursor columns:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'production_db'
  AND DATA_TYPE IN ('date', 'datetime', 'timestamp')
ORDER BY TABLE_NAME, ORDINAL_POSITION;

For each mutable table, confirm that its cursor changes on both insert and update. Check for nulls and old values before relying on it:

SELECT
  COUNT(*) AS total_rows,
  SUM(updated_at IS NULL) AS rows_without_cursor,
  MIN(updated_at) AS earliest_cursor,
  MAX(updated_at) AS latest_cursor
FROM production_db.orders;

A nullable cursor can leave rows outside later windows. Backfill the missing values or use full refresh until the application maintains the field consistently.

Step 3: Create the Snowflake Destination

In Supaflow, open Destinations, choose Snowflake, and provide:

1. Authentication type and credentials
2. Account identifier
3. Warehouse
4. Database
5. Schema
6. Role

Click Test & Save. The Snowflake role must be able to use the warehouse and create or update tables in the selected database and schema.

Step 4: Create the MySQL Source

Open Sources, choose MySQL, and enter:

1. Host — the MySQL hostname or IP address
2. Port — 3306 unless your server uses another port
3. User — the read-only account
4. Password — the account password
5. Database Name — one MySQL database per source
6. Lookback Period — 0 to 86400 seconds
7. Schema Refresh Interval — 60 minutes by default, 0 before every pipeline run, or -1 to disable automatic refresh

Click Test & Save. Supaflow discovers base tables, columns, data types, and primary keys from the configured database. Views are not exposed by this connector.

Each MySQL connection is initialized with SET time_zone = '+00:00'. That makes timestamp cursor comparisons independent of the MySQL host's session default. It does not restore a named business timezone that the source column never stored, so validate time-sensitive columns during the first load.

Step 5: Create the MySQL-to-Snowflake Pipeline

Open the project whose destination is Snowflake and create a pipeline with the MySQL source.

For tables with a reliable cursor and primary key, use:

• Ingestion mode: Historical + Incremental
• Load mode: Merge
• Schema evolution: the policy that matches how you want Snowflake to handle compatible source changes

Select the source tables and columns. Choose the cursor for each incremental table. Use full refresh for tables that do not expose a trustworthy date, datetime, or timestamp change marker.

Avoid Duplicates When Using Lookback

Suppose a run ends at 10:03:00 and the lookback is 120 seconds. The next run begins at 10:01:00, not 10:03:00, so rows from the overlap can be read again. Merge uses the source primary key to update those rows in Snowflake. Append mode would create duplicates unless another downstream process removed them.

The overlap is intentional. It trades repeated reads for protection against writes that land slightly out of timestamp order.

Step 6: Run the First Sync

Start the pipeline and monitor its Jobs view. The first run reads the historical rows selected for each table and creates the corresponding Snowflake tables.

Watch for:

• MySQL connection or grant errors
• Tables missing because the source user lacks SELECT
• Cursor columns with null or non-advancing values
• Snowflake permission or warehouse errors
• Type errors on unusually large numeric values

Supaflow also handles several MySQL data types that commonly cause Snowflake load failures:

MySQL value	What Supaflow does	Why it matters
TINYINT(1)	Emits JSON true or false	Boolean values load correctly instead of arriving as incompatible integers
BIGINT UNSIGNED	Maps to DECIMAL(20,0)	Large identifiers retain their full value
SET	Converts the Python set to a sorted array	Multi-value fields load in a stable order
DATE	Keeps a date value	Calendar dates do not shift through timestamp conversion
DATETIME / TIMESTAMP	Uses typed local-datetime values with a UTC-pinned source session	Cursor comparisons remain consistent across environments

Step 7: Check the Data in Snowflake

Start with row counts, but do not stop there.

In MySQL:

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT id) AS distinct_ids,
  MIN(updated_at) AS earliest_update,
  MAX(updated_at) AS latest_update,
  SUM(total_amount) AS total_amount
FROM production_db.orders;

Run the equivalent query in Snowflake using your destination database, schema, and table names:

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT id) AS distinct_ids,
  MIN(updated_at) AS earliest_update,
  MAX(updated_at) AS latest_update,
  SUM(total_amount) AS total_amount
FROM ANALYTICS_DB.MYSQL_SCHEMA.ORDERS;

Then sample business-critical primary keys and compare every important field:

SELECT *
FROM production_db.orders
WHERE id IN (101, 2507, 98420);

Use values near edge cases: the largest unsigned ID, a boolean false and true, a multi-value SET, a decimal with maximum scale, and timestamps around a daylight-saving transition if the application treats them as local time.

Step 8: Test the Next Incremental Sync

After the first load:

1. Insert one MySQL row with a cursor later than the saved cutoff.
2. Update one existing row and advance its cursor.
3. Run the pipeline again.
4. Confirm that Snowflake contains the new row and the updated values.
5. Run once more without source changes and confirm that no new business rows appear.

To test lookback, write a row whose cursor is slightly older than the previous cutoff but still inside the configured overlap. The next run should read it. A row older than the lookback boundary will not be recovered by that window.

Decide How to Handle Deleted Rows

Cursor replication cannot query a row after MySQL deletes it. Choose one of these patterns when Snowflake needs delete awareness:

• Soft delete: keep the row and advance updated_at when is_deleted changes
• Tombstone table: write deleted primary keys and deletion timestamps to a separate append-only table
• Periodic reconciliation: compare source and destination keys on a schedule and remove or flag missing destination rows
• Different capture path: use a binary-log CDC system when low-latency hard-delete capture is mandatory

If Snowflake must reflect hard deletes quickly, use a binary-log CDC system instead of cursor-based replication. Cursor sync and CDC solve different problems, and choosing the wrong one can leave deleted source rows in Snowflake.

Keep the Pipeline Healthy

After the pipeline is scheduled:

• Alert when the source cursor stops advancing unexpectedly
• Keep lookback shorter than the period you can afford to re-read
• Use merge with a stable primary key on tables that have overlap
• Refresh source schema after new MySQL tables or columns are deployed
• Revalidate row counts and business totals after type or schema changes
• Run full refresh for tables whose cursor contract becomes unreliable
• Reconcile deletes if the source uses hard deletion

Build Your MySQL-to-Snowflake Pipeline

Start by creating the MySQL source, connect the Snowflake destination, and build the pipeline from your Snowflake project. For a shorter overview of supported sync behavior, MySQL data types, current limits, and pricing, see the MySQL to Snowflake connector page.