You’re Moving Data You Never Had to Move.

Five Snowflake patterns that quietly kill your external ETL — and the gotchas nobody warns you about.

Here’s the loop most data teams are stuck in: extract the data out of the warehouse, haul it into a Python script or a Spark cluster to transform it, then fight to push the results back into production. Hours of movement, a second set of security rules to maintain, and a fresh data silo every time. It’s a gravity problem — and the heaviest object in the room is the data you keep dragging around. The fix isn’t a faster ETL tool. It’s not moving the data at all: transform it where it already lives, inside Snowflake, with SQL, Snowpark, streams, and tasks. Here are five patterns that make external transformation obsolete, each with the gotcha that bites first.

1. Snowpark Pushdown: Your Python Runs Next to the Data

Snowpark’s DataFrame API lets you write Python, Java, or Scala that gets translated into SQL and executed natively on Snowflake’s server-side engine. No separate cluster, no copying data to your laptop’s memory to watch it die. Snowflake’s massively parallel processing (MPP) clusters do the heavy lifting and scale for you.

As the sources put it: “Snowpark pushes down all data transformation and heavy lifting to the Snowflake data cloud, enabling you to efficiently work with data of any size.” If you’ve ever run large-scale operations on your own machine, you know how easy — and how frustrating — it is to run out of memory.

The gotcha: Snowpark is lazily evaluated. Your transformations are queued and batched until you call an explicit action. Running print(df) shows you nothing and executes nothing on the server. You have to append collect(), show(), or save_as_table() to actually trigger the work. Engineers new to Snowpark lose an afternoon to “why is my pipeline doing nothing” before they learn this.

2. Streams and Tasks: Stop Reprocessing the Whole Table

If 1,000 new rows land in a 20-million-row table, you should not be reprocessing 20 million rows. Snowflake Streams capture change data capture (CDC) information — the inserts, updates, and deletes — so you can touch only what changed. Pair them with Tasks (which schedule and run SQL or stored procedures) and you can wire up directed acyclic graphs (DAGs) of incremental steps that slash both compute time and cost.

The gotcha: A SELECT does not consume a stream. “A simple SELECT on a stream does NOT consume it. The stream keeps returning the same change records consistently on every execution.” A stream only advances when its rows are processed by a DML statement — an INSERT, UPDATE, or MERGE — inside a transaction. Worse: if you don’t consume it within the data retention window (14 days by default), the stream goes stale and those tracked changes are gone for good.

3. Build JSON in the Warehouse, Not in a Script

Modern APIs want JSON, and teams still spin up a middle-tier Python service just to assemble it. You don’t need to. OBJECT_CONSTRUCT builds key-value pairs and ARRAY_AGG rolls values into arrays, so you can generate nested JSON payloads dynamically right inside a SELECT.

The gotcha: The keys in OBJECT_CONSTRUCT are strictly case-sensitive. Build an object with a camelCase key like tweetDate and you must extract it with the exact same casing. Ask for tweetdate and the query simply won’t find it — a silent null that sails past basic testing and detonates in a production integration.

4. Governance Stays With the Data

Transform data outside Snowflake and you’ve just signed up to replicate every security policy inside your ETL tool. Keep the transformation in the warehouse and your code inherits the same role-based access control (RBAC) and governance the data already has. Dynamic Data Masking, for instance, obfuscates sensitive columns at query runtime without ever touching the raw data.

The gotcha: Be very careful putting masked columns in a WHERE clause. If a masking policy covers an email column and an unauthorized user runs WHERE email = ‘test@example.com’, Snowflake applies the mask to one side of the comparison. They end up comparing a masked value against an unmasked string — an anti-pattern that silently returns wrong or empty results instead of throwing an error.

5. Scale Up vs. Scale Out: They Solve Different Problems

Snowflake lets you resize compute instantly, and the economics are counterintuitive in your favor: moving to a larger warehouse gives a complex transformation more threads and often costs the same total credits, because the job finishes proportionally faster.

The gotcha: Bigger is not always faster. For small, quick queries, scaling up does nothing. The real decision is about which problem you have. If a query runs out of memory and spills to local or remote disk, scale up — a larger warehouse gives that single query more memory. If queries are queuing because too many pipelines run transformations at once, scale out with a multi-cluster warehouse to absorb the concurrency. Confusing the two is how teams overspend and still stay slow.

The Real Shift

The line between the data lake and the warehouse already dissolved — “the distinction between the data lake and warehouse was removed with Snowflake’s extensible data architecture.” The point of all of this is to stop managing the plumbing: “Everything Snowflake is building is to keep all the bits and pieces around the data in one place, so you don’t have to manage the complex architectures or operational overhead to bring your data apps to life.”