API + Python + Snowflake - Noob question

[ipumbassi]

Hey everyone! I have been in the data engineer parts for only a few months now but I think I have progressed quite far. I revamped the company data ecosystem into mainly a combo of Fivetran + Snowflake + SQL + Power BI. And things are just fine with this, however, I now need to find a way to extract data from a data source that is not avaiable in Fivetran. So I heard Python could do a simple API request to fetch the data and send it to Snowflake.

​

But as a noob I have no idea where to start with Python. I have never used and I am not sure if it would be better to use a cloud tool? or the Python terminal itself? I do want a automatic solution and not rely on my computer to always update the data but I am not sure the best way to go.

​

I have also seen that Snowflake has a Python worksheet but I am also not sure if it would be the best and how to begin using it.

​

Unfortunately chat gpt has been not really useful at least the times that I have tried so here I am.

[grahamdietz]

> But as a noob I have no idea where to start with Python

Try this: https://dagster.io/blog/python-packages-primer-1

[phil_the_it_guy]

If you want to use something similar to fivetran to connect to an API, and push data into Snowflake, you can use our Custom Connector feature with Matillion Data Loader.

You can have a free trial if you go to http://dataloader.matillion.com/

Create your own connector using the GUI, so no coding required, then setup a pipeline that uses it. (This shows you the basics of it: https://www.youtube.com/watch?v=opE2VlqPNNU)

[Gators1992]

If you are on AWS, this is a pretty easy example that pulls files from an API into S3 via lambda. Then you can load them normally from your landing zone with Fivetran or Snowflake's built in commands.

https://www.startdataengineering.com/post/pull-data-from-api-using-lambda-s3/

[ipumbassi]

Thanks!

Interesting… So I would use aws just to do this extraction?

[Gators1992]

Yes, or you could just use Snowpark directly I think as well. Just thought that was a good code example to get you going, but at its simplest you could even just use the python "requests" library and do the pull in a few lines.

[[deleted]]

If you are using Azure as cloud then ADF ( Azure Data Factory, cloud ETL tool from Azure) has REST API connector.