The route

What changes in the other databases

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.

  1. Why prefer MySQL Upsert over Replace?