MySQL to Snowflake: how to replicate production data into your warehouse
10 min read Data engineering The Adapters team
Last updated August 2026
Field mapping auto-plugged · tap a port to rewire
Plug a source port into
Transform on this cable
JSON in
JSON out
5 sample records ready
To replicate MySQL to Snowflake, extract only the rows that changed (an updated_at watermark
for most tables, binlog CDC when you need deletes and sub-minute latency), stage the batch as compressed
files, cast MySQL types to Snowflake (DECIMAL to NUMBER at its declared
precision, TINYINT(1) to BOOLEAN, DATETIME to
TIMESTAMP_TZ in UTC, JSON to VARIANT), and load with
MERGE on the primary key so a replayed batch updates instead of duplicating.
Key takeaways
- Replicate, do not let analysts query production. A read replica under BI load competes with the application for exactly the resources it needs at month end.
- MERGE on the key is what makes retries safe. A plain
COPY INTOafter a failed run double counts revenue, and nobody notices until a board deck is wrong. - MySQL DATETIME carries no timezone. Decide the source zone once, normalize to UTC on the way in, and your warehouse dates stop landing hours off.
- Batch size beats sync frequency for cost. Snowflake bills warehouse seconds, so many tiny loads keep a warehouse awake more than a few well-sized ones.
How do I connect MySQL to Snowflake?
You connect MySQL to Snowflake by moving rows through a staging area, because Snowflake cannot read a
MySQL server directly. A connector or scheduled job authenticates to MySQL, selects the changed rows,
writes them as compressed files to an internal or cloud stage, and runs a
COPY INTO plus MERGE against the target table.
Three pieces need to exist. On the MySQL side, a read-only user scoped to the tables you replicate, plus network access from wherever the job runs (usually an allowlisted IP or a private link, not a database open to the internet). On the Snowflake side, a role that can create and write to the landing schema, and a warehouse sized for loading rather than for analytics: an X-Small warehouse loads staged files perfectly well, and paying for a larger one during ingestion is money burned.
In the middle sits the sync loop that tracks what it already moved. That state is the part teams underestimate, because a load script without a durable watermark is a script that either re-reads everything or silently skips rows after a restart. The managed path is the MySQL to Snowflake connector, which owns the credentials, the watermark, the type casting, the staging, and the merge, so the pipeline is not one engineer's cron job.
How do MySQL data types map to Snowflake?
MySQL types mostly map cleanly to Snowflake, with three that need care: DECIMAL must keep
its declared precision or money rounds, DATETIME has no timezone so you must decide the
source zone, and TINYINT(1) is a boolean in practice even though it is an integer on
paper. Everything else is largely mechanical.
| MySQL type | Snowflake type | Note |
|---|---|---|
| TINYINT(1) | BOOLEAN | Treated as a boolean by every common ORM. Cast explicitly rather than landing 0 and 1 integers analysts must remember to interpret. |
| INT, BIGINT | NUMBER(38,0) | Snowflake has one integer family. Unsigned BIGINT still fits comfortably. |
| DECIMAL(p,s) | NUMBER(p,s) | Carry precision and scale across. This is the money column; do not let it become FLOAT. |
| FLOAT, DOUBLE | FLOAT | Snowflake FLOAT is double precision. Fine for measurements, wrong for currency. |
| DATETIME | TIMESTAMP_TZ (UTC) | No zone stored in MySQL. Pick the source zone once, convert on ingest, document it. |
| TIMESTAMP | TIMESTAMP_TZ (UTC) | MySQL stores UTC internally and converts on read. Read as UTC and skip the second conversion. |
| DATE | DATE | Direct. Map 0000-00-00 to NULL, which Snowflake will not accept as a date. |
| VARCHAR, TEXT | VARCHAR | Snowflake VARCHAR is variable length with no storage penalty for a generous limit. |
| ENUM | VARCHAR | Land the label as text. Add a dbt test if you want the allowed set enforced downstream. |
| JSON | VARIANT | Keeps nested payloads queryable with dot notation instead of flattening at load time. |
| BLOB, BINARY | BINARY | Usually better excluded. Blobs bloat the load and analysts rarely query them. |
| BIT | BOOLEAN | Cast at load so filters read naturally. |
One Snowflake-specific detail catches people on the first query: unquoted identifiers fold to
uppercase. A MySQL column named total_amount becomes TOTAL_AMOUNT in
Snowflake unless you quote it on creation. Let it fold, keep everything unquoted, and stay consistent.
Mixing quoted and unquoted objects in the same schema is the source of most "column does not exist"
confusion in a fresh warehouse.
How do I replicate MySQL to Snowflake incrementally?
Pick one of three methods based on how fast the data must arrive and whether you need deletes: full
reload for small reference tables, an updated_at watermark for the great majority of
tables, or binlog CDC when you need near real time and hard deletes propagated. Most pipelines end up
mixing the first two.
| Method | How it works | Catches deletes | Best for |
|---|---|---|---|
| Full reload | Truncate and re-copy the whole table each run | Yes, implicitly | Small reference tables under a few hundred thousand rows |
| Watermark on updated_at | Select rows changed since the last run, MERGE on the key | No | Almost everything: simple, cheap, and safe to retry |
| Binlog CDC | Read the MySQL binary log and apply insert, update, and delete events | Yes | Large tables, sub-minute latency, and audit-grade change history |
| Primary key range chunking | Walk id ranges in bounded batches | No | The initial backfill of very large tables |
The watermark path is the default for a reason. It needs only a column that changes on every update,
which many MySQL schemas already have as updated_at with
ON UPDATE CURRENT_TIMESTAMP, and it degrades gracefully: if a run fails, the next one
re-reads an overlapping window and the merge absorbs the duplicates. Add a small lookback, a few
minutes, so rows committed during the previous read are not missed by a strict greater-than filter.
Binlog CDC gives you what a watermark cannot: deletes, and a true ordered change stream. The cost is
operational. Binlog retention has to be long enough to survive an outage, the reader needs
REPLICATION SLAVE privileges, and a schema change on a busy table can stall the stream.
Start with watermarks, and move a table to CDC when it earns the complexity. When a table
does earn it, the shortlist of
CDC tools splits cleanly by method, and the
Postgres side of the same problem is worked through in
Postgres change data capture.
How do I stop duplicate rows in Snowflake?
Make the load idempotent with MERGE instead of a bare COPY INTO. Land each
batch in a staging table, then merge into the target matched on the primary key: update when matched,
insert when not. Because the key is stable, replaying an overlapping window or retrying a failed batch
updates rows in place rather than appending copies.
Deduplicate inside the staging table first. If a row changed twice during your sync window, the batch
contains both versions, and Snowflake raises an error when a merge matches one target row more than
once. A window function that keeps the latest version per key, ordered by the source
updated_at, solves it in one statement and prevents an alert at 3am.
Then monitor that the result stays correct. Table freshness, row count deltas, and unexpected schema changes are the three signals worth alerting on, and watching freshness and volume on every loaded table is what turns a silently broken pipeline into a page. A merge that has not run in two days looks exactly like a quiet business week until someone checks.
How often should I sync MySQL to Snowflake?
Match the interval to the decision the data supports. Hourly suits most operational reporting, every fifteen minutes suits dashboards people watch during the day, and daily is plenty for finance tables that close monthly. Syncing faster than anyone acts on the data spends warehouse credits for nothing.
Snowflake bills per second of warehouse runtime with a minimum charge per resume, so many tiny loads cost more than fewer well-sized batches even though they move the same rows. Group tables into a single scheduled run that reuses one warm warehouse rather than staggering twenty jobs that each wake it. Set the auto-suspend low, sixty seconds is common, so the warehouse is not idling between loads.
This is also where per-row pipeline pricing distorts good engineering. When each synced row is metered, the cheapest choice is to sync less often, which is exactly backwards from what the business wants. Our flat pricing is by plan rather than by row, so tightening the interval changes the freshness and not the invoice.
Should I use MySQL or Snowflake for analytics?
Use Snowflake for analytics and MySQL for the application. MySQL is row-oriented and tuned to fetch and write single records fast; Snowflake is columnar and tuned to scan and aggregate billions of rows. Running dashboards against MySQL means full table scans on a database that also has to take orders.
A read replica is a partial fix that hides the real problem. It removes the load from the primary, but analysts still write joins against a normalized OLTP schema that was never designed for reporting, every dashboard re-derives the same business logic, and there is nowhere to combine MySQL data with Stripe, HubSpot, or Shopify. The warehouse is where those tables sit side by side.
The practical setup is both: MySQL keeps serving the application, and a replica of the tables that matter lands in the warehouse where modeling happens. If your analytics stack is on Google Cloud instead, the same reasoning applies to a warehouse load there, and the mechanics of watermarks, MERGE and partition pruning are walked through in loading a database into BigQuery. And if the operational database itself is the thing you are moving, that is a different project entirely, covered in migrating MySQL to PostgreSQL.
To see the pair running against your own schema, map it in the browser with the MySQL to Snowflake connector or book a walkthrough. The type casting, the watermark, and the merge are already handled.
Get MySQL tables into Snowflake without a hand-built loader
Watermark or CDC incrementals, MySQL to Snowflake type casting, and MERGE loads that survive a retry. Map the pair in the browser and pay a flat price from $49 a month.
No credit card required.