Move MySQL rows into Snowflake on a schedule.

Why this pair comes up so often

MySQL still runs a huge share of transactional apps, and Snowflake is where most of those teams eventually want the same data for reporting. The two don't talk to each other directly, so someone has to move rows from one to the other on a schedule that finance or product can actually rely on. A cron job with a Python script and the Snowflake connector library works, but it's one more thing to maintain, monitor, and explain to whoever inherits it later.

Before you start

• A MySQL connection and a Snowflake connection, both already added under Databases.
• Pipelines tier. Data Sync isn't part of Studio.
• Know your write mode ahead of time: a first load is usually Insert, an ongoing refresh is Update or Upsert.

Steps

1. Add your MySQL connection: click the + next to Databases, pick MySQL, and enter host, port 3306, user and password.
2. Add your Snowflake connection if you haven't: account identifier, warehouse, and a Programmatic Access Token.
3. Open Pipelines → Build and click New Sync.
4. On the left, pick the MySQL connection as your source and write or paste the query that defines the rows to sync.
5. On the right, pick your Snowflake connection and the target table, or create one.
6. Drag columns across, or click AI Map to pair them by name.
7. Pick a MODE: Insert for a first load, Update or Upsert once the table already has rows you're refreshing.
8. For Update or Upsert, set a MATCH ON field, a column that's unique per row, like an order or customer ID.
9. Click Dry Run, check the preview against a few known rows, then Save or run it now.

A worked example

Say you're syncing a MySQL orders table into a Snowflake fact table, keeping existing rows updated as their status changes:

-- MySQL source query
SELECT order_id, customer_id, order_total, status, updated_at
FROM `shop`.`orders`
WHERE updated_at >= CURDATE() - INTERVAL 2 DAY;

-- Snowflake target
CREATE TABLE IF NOT EXISTS ANALYTICS.PUBLIC.ORDERS (
  ORDER_ID NUMBER,
  CUSTOMER_ID NUMBER,
  ORDER_TOTAL NUMBER(10,2),
  STATUS VARCHAR,
  UPDATED_AT TIMESTAMP_NTZ
);

Map order_id to ORDER_ID and so on, set MODE to Upsert, and MATCH ON to ORDER_ID. The two-day window in the source query keeps each run cheap: only orders that could plausibly have changed get re-sent, and Upsert handles the rest without duplicating rows already in Snowflake.

Check it worked

Dry Run shows the rows that would write before anything does. After a real run, compare counts on both sides:

SELECT COUNT(*) AS row_count FROM ANALYTICS.PUBLIC.ORDERS;

against the equivalent MySQL count for the same window, and check the job's history for any rows the sync skipped, usually a type mismatch between a MySQL column and its Snowflake target.

Troubleshooting

What this doesn't do

This is a scheduled, batch sync, not real-time replication. Rows move on whatever interval you set, not the instant they change in MySQL. If you need sub-second freshness, that's a job for a change-data-capture pipeline, not a Data Sync built around a WHERE clause and a schedule. For daily or hourly reporting refreshes, which is most analytics use cases, batch is simpler to reason about and cheaper to run.

Turning it into a recurring job

Once the mapping and MATCH ON are right, save the sync as a job and set a schedule, hourly for a fast-moving orders table, nightly for something like a customer dimension that changes rarely. The query's date window does the filtering; the schedule just decides how often it runs. If the source table's schema changes, a column renamed or dropped, revisit the mapping rather than assuming the old one still lines up.

Type mapping worth double-checking

MySQL and Snowflake don't map every type one to one, and a few are worth checking by hand before trusting AI Map's guess. MySQL's TINYINT(1) is commonly used as a boolean, and it's easy to end up with it landing as a small integer in Snowflake rather than a true BOOLEAN, which breaks any downstream query written assuming true or false. ENUM columns come across as their text value, not the underlying MySQL enum definition, which is usually what you want anyway. DATETIME without a timezone needs a decision: MySQL stores it naive, so confirm whether your application writes it in UTC or local server time before assuming the Snowflake TIMESTAMP_NTZ column means what you think it means.

A second worked example: an append-only events table

Not every sync needs Upsert. For an events table where rows are only ever inserted and never edited, Insert with an incrementing ID filter is simpler and cheaper than tracking a MATCH ON key:

SELECT event_id, user_id, event_name, occurred_at
FROM `app`.`events`
WHERE event_id > 4820193
ORDER BY event_id ASC;

Because event_id only ever increases and rows never change after insert, tracking the last synced ID and filtering above it each run is enough. There's no need for Upsert's extra overhead when nothing in the source ever gets updated in place.

Related syncs

See also: Integrations Sync BigQuery to Snowflake CSV to Snowflake on Mac.