Example 5: Oracle ETL with DbScript prelude and Cache

Workflow examples

Why this example

A finance team in an Oracle-heavy stack runs a nightly ETL that loads transactions from a source schema into a reporting schema. Two recurring needs: the staging table must be empty before each run (so the prior night's data doesn't bleed in), and every transaction row needs a country code enriched from a slow-changing lookup table. The DbScript node handles the truncate; the Cache node holds the lookup in memory so we only hit the lookup table once per run.

Helix Beverages context: Helix's Oracle GL came in with a 2019 acquisition and Finance still closes month-end out of it. This recipe is how the legacy Oracle transactions flow into a reporting schema that Snowflake can read so Finance avoids maintaining two sets of dashboards (see the Helix overview for the wider Oracle migration story).

At a glance

• Connectors used: Oracle
• Nodes used: Start, DbScript, Source (lookup), Cache, Source (transactions), Transform, Load
• Schedule: daily at 03:00 UTC
• Direction: One-way (Oracle source -> Oracle reporting schema)

Canvas

Start

DbScript: truncate STG_TXN

Source: REF.COUNTRIES

Cache: countries by country_id

Source: SRC.TXN

Transform: enrich via Cache

Load: REPORT.STG_TXN

The Cache node populates an in-run lookup map; the downstream Transform node uses the cache key to enrich each transaction row without re-querying Oracle per row.

Step-by-step

#	Node	Action	Key configuration	Output shape
1	Start	--	--	--
2	DbScript: truncate	Oracle DbScript	TRUNCATE TABLE REPORT.STG_TXN. Output mode none (no rows downstream).	--
3	Source: countries	Oracle: query	SELECT country_id, country_code, country_name FROM REF.COUNTRIES.	{ country_id, country_code, country_name }
4	Cache	Cache: write	Cache name countries, key field country_id, value: the whole row. Scope: per-run.	--
5	Source: transactions	Oracle: query	SELECT txn_id, account_id, country_id, amount_usd, posted_on FROM SRC.TXN WHERE posted_on = TRUNC(SYSDATE) - 1	Transaction row
6	Transform	--	For each row, read countries[country_id] from the Cache; emit { txn_id, account_id, country_code, country_name, amount_usd, posted_on }.	Enriched row
7	Load	Oracle: Append	Target REPORT.STG_TXN. Mode: Append (the staging table was truncated in step 2).	--

Variations

• Different DB family -- swap Oracle for PostgreSQL / MSSQL / MySQL / Snowflake. The DbScript node retains a database script per family (TRUNCATE / DELETE syntax differs slightly). The Cache node is connector-agnostic.
• Cache for many lookups -- a single workflow can populate multiple Cache namespaces in parallel (countries, currencies, tax-codes). Each Transform reads only the lookups it needs.
• Persist the cache across runs -- by default Cache is per-run; switch the scope to global if the lookup table truly never changes within the day, so subsequent runs skip the lookup query entirely.
• DbScript chain -- if the prelude needs multiple statements (truncate + reset sequence + analyze stats), chain DbScript -> DbScript to keep each statement readable.