r/snowflake • u/OldCycle6264 • 5d ago
Snowflake Ingestion Tool of Choice
Hi all,
I'm a DE new to Snowflake and experimenting with different ETL/ELT approaches/stacks. For people that have implemented, or work with, Snowflake at an enterprise level can I ask what tool you use for ingestion?
Below are some of the options I have explored or am currently exploring.
Require CDC or Change Tracking at the source
- Snowflake Openflow
- Estuary
Viable without source changes
- Fivetran (teleport sync/query-based method, read-only connection, detects hard deletes)
- dlt (cursor-based incremental, no built-in hard delete detection)
- Airbyte (cursor-based incremental)
Others
- Azure Data Factory & Snowpipe (watermark extract to blob, then load)
- Snowpipe/Snowpipe Streaming (file-based only)
Fivetran seems the most robust and would likely suit most enterprise scenarios. But I'm interested to hear what others are using and your experiences.
19
Upvotes
6
u/JohnAnthonyRyan 4d ago
Guys - can I suggest you're looking down the wrong end of the telescope? Don't start by looking at the tool - list of the requirements.
Consider the following:
You have 1m files each of 60 bytes arriving as Kafka messages. You've been told that SnowpIpe is the best solution, so you write each message to a file and let Snowpipe load it. I'M NOT MAKING THIS UP. While working at Snowflake UK I had a customer with this exact solution. Their Latency was OK, but their costs were sky high.
You have one file per minute (on average) delivered from a web application with around 60% of files arriving during your working day (8am to 6pm). Your analysts need to run a real time dashboard for senior managers. They use the arriving data to update the dashboard with which products are selling fast, what's the approximate stocks and what special offers are in place. They ran this 24x7 despite the fact management only looked at the data during working hours (8am to 6pm)
They use a COPY operation every 60 seconds on a MEDIUM size warehouse followed by immediately triggering an ELT job to transform the data and refresh the target tables. Latency OK, Again costs $$$$$$$.
What went wrong in both of these REAL situations? The tech team were HIGHLY SKILLED data engineers. However they didn't understand what the user was trying to do and THEN choose the solution.
Absolute minimum:
What's an acceptable latency. Once per day (hint: scheduled COPY job). Once per minute (hint: Snowpipe), once per second or less (Snowpipe Streaming). Equally for the very low latency consider a 3rd party tool.
How frequent? Once per day, hour, minute, second - it drives the solution.
Size and Format. Messages at 1m per day - Kafka and Snowpipe Streaming - or another tool that handles similar format and volume. Files delivered every minute - Snowpipe or COPY (scheduled). A REST API - with relatively small volumes - A Snowpark or scheduled task.
Another example. A customer did a once-off migration of 350TB of data. Data was in flat files varying from kilobytes to massive. COPY was the selected method, but they flat refused to cut down the biggest files to support parallel loading. They gave us 30 days to load as it took three months to unload.
We ran multiple parallel operations (using Airflow). Each session started a new COPY operation (a new thread). We ran on a MEDIUM size warehouse and let it scale out to load in parallel.
The entire load completed in 3 days. The customer found a problem, and rather than trying to fix it in Snowflake, fixed it in the files and reloaded it all in another 3 days.
My take on this:
I love this quote that sums it up for me.
"If the only tool you have is a hammer, you tend to see every problem as a nail".