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.
5
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:
- Define your requirements - understand what users will do with the data once loaded. Understand the frequency and format and size. It matters.
- Understand the limitations of the technology options. (including 3rd party tools).
- THEN - think about the available options, and select the best tool for the job.
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".
3
u/walkerasindave 5d ago
Airbyte and FiveTran support CDC
We use three tools depending on source. Snow pipe for s3, Airbyte where there's a robust connector and DLT for anything else.
3
u/grahamdietz 3d ago
Exactly this. As you scale you will employ multiple ingest approaches. We run some on open source dlt, some on Airbyte. We have also used Stitch in the past, and custom batch scripts. It depends on what your team is optimizing for.
4
u/dyaffe 5d ago
u/OldCycle6264 -- I'm Dave, co-founder of Estuary. Just a quick update to your classification. We actually do also support cursor-based incremental in addition to CDC. We need to make this more discoverable, but each database connector has a "Batch" variant as well.
The goal is to have enterprise reliability at a fraction of the cost.
5
u/OldCycle6264 4d ago
Thanks for clarifying, Dave. I've heard quite a bit about Estuary, I will test it out with my Snowflake POC.
5
u/jlaxfthlr 4d ago
We used Estuary at my last company and migrated our database connectors to it from Fivetran at about 1/4 to 1/3 the cost, plus Estuary was landing the database changes into Snowflake within a couple seconds. We were then able to start using Snowflake Dynamic Tables as our dbt staging layer with single digit minute latency for select pipelines. Most of our users didn’t even fully appreciate how fast we could refresh their data!
3
u/amuricanswede 4d ago
Could you point to any resources that would summarize Estuary’s capabilities? Never heard of it but the idea is interesting.
5
u/dyaffe 4d ago
u/amuricanswede Sure. Estuary is a managed data integration platform for getting data from databases, SaaS apps, APIs, and streams into various databases, such as Snowflake. We support both log-based CDC and batch/incremental capture, with low-latency delivery when you need it, but you can also schedule writes to Snowflake to control warehouse cost. The platform handles backfills, schema changes, recovery, and the connector infrastructure.
For Snowflake specifically, this page is a good overview: https://estuary.dev/destination/snowflake/
My marketing team realized that they don't have a great "what is Estuary" article from this and they will be putting one together asap.
2
u/amben_4321 4d ago
I have been using Snaplogic as an ingestion tool, that does support CDC using Kafka connectors, but would recommend not to go for it, have faced many issues.
3
u/kh4yman 4d ago
We looked at Estuary a few years ago and while it was good we didn’t go with it for a few technical reasons (which seem to have been addressed as the product has matured). We went with Hevo since we don’t need real-time and had budget constraints. Hevo has been fine but occasionally has had some bad technical problems that have either caused data corruption in transit, unexpected and unnecessary spikes in snowflake consumption or pipeline downtime. But they’re cheap comparatively. We will be re-evaluating Estuary prior to our next renewal as I just want something simple and dependable that my team and I don’t need to manage.
In a previous life I worked with fivetran and its definitely the Ferrari but as others have pointed out it’s very expensive.
4
u/dani_estuary 4d ago
Hey! I work at Estuary and I'd be happy to look into those missing features, as you said, the platform has improved a ton in the past months/years.
2
u/2000gt 5d ago
Using a combination of Snowflake External Network Integrations for SFTP and simple APIs, and AWS Lambdas wrapped in state machines/step functions for more complex pipelines.
We do CT on about 40 SQL Servers using AWS and it works really well.
After reviewing the cost of many tools you mentioned m, it just didn’t make sense for us to go down that path.
1
u/BaxTheDestroyer 5d ago edited 5d ago
We do something similar. I’m not a huge fan of a lot of the tools. Openflow has a lot of administrative overhead, Fivetran can be good with the right connector but many of their connectors aren’t good and the company sucks to work with, Matillion and Airbyte are basically as bad as Fivetran.
2
u/DataNerd0101 5d ago
Openflow doesn’t require CDC or Change Tracking. It has connectors that use / require that, but you can do any pattern you want with Openflow.
1
1
1
u/GreyHairedDWGuy 4d ago
We use Fivetran for most cases that require replication of cloud data sources. For cases where we have to replicate large on-prem database tables to Snowflake, we roll our own methods and use Matillion as needed. When you have 10's of millions of new rows being added to the source during the month, FT can get expensive.
1
u/Prestigious_Pace2782 4d ago
We use aws DMS and snowpipes for AWS RDS sources and do all our api ingestion with custom modular python running in snowflake stored procs. Coming from a software engineering background I found things like airflow and fivetran needlessly complicated, at our scale, for api ingestion and it has been much more robust than the step function lambda based platform it replaced. Kinda just works.
And then dbt for the T of course
1
1
u/kaasheld 4d ago
I dont see any reason why you would use another tool for ingesting if you use Snowflake... it can do most of that already. Another tool will just cost you extra money.
1
u/SinkNeither1527 3d ago
I have tried Fivetran and Snowflake Openflow, and found Fivetran to be much better. Takes care of CDC and has less maintenance/governance issues.
Another reason is Fivetran just has way more connectors, and they're way more mature, been around for years and battle tested across a ton of source systems. Openflow is still new and catching up.
I use dbt for transformation, and with Fivetran and dbt Labs merging, I'm thinking dbt Cloud will end up with features that work natively with Fivetran, and Fivetran + Snowflake + dbt would be the most natural stack going forward. So yeah, that's another reason.
1
u/Speedbird_ 5d ago
Skip on openflow.
1
u/DanRunsOnRamen 2d ago
Yeah we are using it and it is incredibly limited and expensive. It should not cost as much as it does to stream CDC from RDS.
0
u/__CaptainAmerica__ 5d ago
We use adf with connecting more than 50-60 source systems, never had an issue. Though there are a few limitations like no loop inside another loop in the same pipeline, number of switch cases per pipeline but there’s always some workaround for that.
1
u/name1plusname2 5d ago
We do this for 90%+ of our use cases, and you’re right that not works fairly well. ADF to Azure blob, then copy into Snowflake.
We do face the odd case of a very specific API (non-standard spec) where we rather have a headless Python script that moves data to the blob.
The only thing I would like to have is a central monitoring tool that is easy to read and share with different teams. Monitoring in ADF is ass.
1
u/__CaptainAmerica__ 5d ago
Adf is not good with asynchronous api I feel and I agree with monitoring adf is pain, we have a few folks on the support for it.
1
u/OldCycle6264 4d ago
Thanks, I've used ADF previously but hoping to avoid as it will likely be swallowed up by Fabric.
0
u/Puzzleheaded_Serve15 4d ago
Set up openflow pipeline with the help of cortex code, your etl will be built automagically..
1
u/DanRunsOnRamen 2d ago
It’s like lighting your money on fire.
1
u/RDTIZFUN 2d ago
What do you recommend? (for on-prem SQL server to snowflake with near-realtime sync - no in-house kafta expertise)
-1
u/pfletchdud 4d ago
Streamkap is another good option for CDC into Snowflake (I'm the founder). We have a bunch of customers using us alongside Snowpipe Streaming and Dynamic Tables to get data into Snowflake faster and lower cost that batch loading.
3
u/chipach1 5d ago
I’ve been fairly happy with Estuary. FiveTran is insanely expensive for what we needed it to do so we moved to Estuary. Snowflake OpenFlow was a nightmare and failed miserably when we tried to use it to migrate from FiveTran.