Hook
Magic ETL got a quiet but meaningful upgrade in Spring 2024 — and if you're still building transforms the old way, you're leaving time and clarity on the table.
Why It Matters
For analysts who live in Domo, the gap between "I know SQL" and "I can use SQL inside my pipeline" has historically been frustrating. The new SQL-in-Magic features close that gap — but they come with real constraints around row limits and dataset sizes that, if ignored, will silently kill your pipeline performance. Knowing when to reach for SQL-in-Magic versus Adrenaline versus a standard dataflow tile is the difference between a pipeline that scales and one that breaks at 3 AM.
What You'll Learn
- Understand how SQL tiles inside Magic ETL actually execute and where the row-count ceiling sits
- Know when Adrenaline is the right tool and when it's overkill
- Use the new JOIN feature correctly and avoid the most common join configuration mistakes
- Set up UPSERT steps and debug them when the key column goes wrong
- Get concrete guidance on dataset sizes before performance starts to degrade
SQL in Magic, JOINs, and UPSERT: What Actually Changed
Andrea Henderson, PM over Magic ETL at Domo, walked through the quality-of-life improvements announced at Domopalooza 2024 — grounded in her background as both a Domo consultant and a data pipeline SME.
The biggest addition is SQL in Magic: you can now write SQL directly inside a Magic ETL dataflow instead of reaching for a separate tool or chaining datasets. The caveat is real though — there's a row-limit threshold for what SQL-in-Magic can process before performance degrades. Andrea's guidance: know your dataset size before you choose this path. If you're working with large volumes, Adrenaline (Domo's high-performance compute layer) is still the right call. SQL-in-Magic is best suited for mid-sized transforms where the flexibility of writing SQL outweighs the overhead of spinning up Adrenaline.
The new JOIN tile cleans up one of the most error-prone parts of building Magic flows — join configuration is now more explicit, reducing the "I thought I was doing a left join" bugs that produce silent data drops.
UPSERT is the feature most likely to save analysts from manual dataset maintenance. Instead of rebuilding a full output dataset on every run, UPSERT lets you insert new rows and update existing ones based on a key column. The debugging note here matters: if your key column has type mismatches or nulls, the UPSERT will fail in ways that aren't immediately obvious. Andrea's recommendation is to validate your key column data quality before trusting UPSERT in production — and to check the error logs explicitly rather than assuming a quiet failure means success.
On performance generally: Andrea's rule of thumb is that Magic ETL starts showing strain in the tens of millions of rows range depending on transform complexity. If you're approaching that ceiling, it's worth benchmarking your specific flow rather than guessing.
