You won't set up five more databases for this course. You don't need to: every database node in Odara has the same shape as the PostgreSQL ones you just used — a connection, a query or a table, a table operation and a data operation. What changes between databases is a handful of connection fields and the names of the write modes. This level is a map of those differences, so the first time you meet MySQL or Snowflake at work, you already know where to look.
Goal
Know, for each of the other main databases, what's different in the connection and which data operation plays the role of PostgreSQL's Insert and Merge.
You'll need
- About 12 minutes. No exercise files — read, then take the quiz.
The same everywhere
- Read nodes take a SQL query (MongoDB: a collection plus an optional filter or pipeline).
- Write nodes take a table (MongoDB: a collection), a Table Operation — None, Truncate, Create Table, Drop Table, Drop & Create — and a Data Operation.
- Connections are saved once in Resources ▸ DATA STORES and picked in Use Connection.
- Anything that updates or deletes "by key" needs Key Columns.
Connections: what's different
| Database | Beyond host / port / user / password |
|---|---|
| MySQL | same fields as PostgreSQL |
| SQL Server | instance name, trusted connection (Windows login), trust server certificate |
| Oracle | service name instead of a database name |
| MongoDB | authSource — the database the user is defined in, often admin |
| Snowflake | no host: account, warehouse, database, schema and an optional role |
Write modes: the same idea, different names
| Database | Append rows | Insert-or-update by key | Fast bulk load |
|---|---|---|---|
| PostgreSQL | Insert | Merge (PostgreSQL 15+) | — |
| MySQL | Insert · Insert Ignore (skips duplicates) | Upsert (ON DUPLICATE KEY UPDATE) · Replace (delete + insert) |
— |
| SQL Server | Insert (max 1,000 rows per batch) | Merge | Bulk Insert |
| Oracle | Insert | Merge | — |
| MongoDB | Insert Many | Upsert · Replace | — |
| Snowflake | Insert | Merge | Copy Into |
Every one of them also has Update and Delete by key (MongoDB: Update Many, Delete Many).
What to watch for when you switch
Join keys across databases.
Snowflake and Oracle NUMBER columns arrive as decimals. Joining one to an integer key from another
source can silently match nothing. Cast both sides to the same type — CAST(x AS BIGINT) in an SQL
node — before joining.
Identifier case. Oracle and Snowflake fold unquoted names to upper case; PostgreSQL folds them to lower case. A table Odara created with a quoted lower-case name may need quotes when you query it by hand.
Bulk modes for big loads. Where a database has one — SQL Server's Bulk Insert, Snowflake's Copy Into — it's the choice for full reloads of large tables, for the same reason as 4.3: row-at-a-time writes are limited by the round trip.
MySQL's two "upserts". Upsert updates the existing row in place. Replace deletes it and inserts a new one — which fires delete triggers and can break foreign keys. Prefer Upsert unless you know you want Replace.
Expected result
You can answer, without looking: which mode reloads a Snowflake table fastest, what MySQL calls Merge, and which extra field an Oracle connection needs.
What you built
A translation table in your head. The pipelines you built in 4.1–4.4 port to any of these databases by changing the connection and picking the matching write mode — the design doesn't change.
Check yourself
One question, drawn from a larger bank. Get it right to pass.
Why prefer MySQL Upsert over Replace?