errr merr gersh! this was so helpful! Thank you sooo much! Mainly the bit for the sqs arn being in the snowpipe object and that one just needs to plug and play it - thank you! #10outof10 #wouldrecommend
This is pretty good video. Can you please provide details on or share info if you have come across this scenario 1. Once data is loaded into Snowflake stage, How to move the file from main S3 folder to an "archive" S3 folder. How to trigger that process after successful ingestion. 2. If we have to do some custom validation (like file type, header etc..) of the files before ingestion into snowpipe. where can this validation be done.
1. You'll need to write a script yourself or use an ETL tool to do this. The COPY into statement can purge files once loaded, but it cannot move it to another folder. 2. Use SQL scripts or your ETL tool to prepare and validate the files before you make it available for Snowflake to load.
Hi Adam.Have gone through your videos on snowflake.Good explanation.👍 I have one query though. Can u help me understand if snowflake architecture would be good option to support oltp and olap systems and the reason being?
Snowflake is primarily designed for large analytical queries, such as aggregations and calculations over very large data sets. They fare less well when users run, ‘point lookup’ queries, or in other words, queries which are highly selective, and therefore look up just a small number of records. If you have OLTP workloads I'd be exploring other technologies.
Hi Adam, I have a small question, I am trying to build a dwh pipeline, I got 2 json source files in on an s3 bucket, I created a pipe from s3 to my staging schema but I am not sure how do I continue from here, is there a way to create a pipline between two schemas\ tables or should I create a task + procedure to refresh every few minutes to load the data into prod? Thanks Alex
Hi Alex, that should be all there is to it. If you have created the pipe with the auto ingest set to true it will periodically run as new files arrive in s3 and copy them into your target table. Hope this helps!
I have this in mind but I'm skeptical. I'm using Postman to pull data coming from an application API. I'd like to then push that data to individual Snowflake tables. Not sure if I need to Snowpipe or I can get away with just using the initial Postman to Snowflake (haven't even tested this yet). Using this template (in the video), I'd have to push the data from Postman to Snowflake and then grab it with Snowpipe. @mastering_snowflake what do you think?
Is it mandatory to use a storage integration ? Because my bucket is full public. When i copy the arn of my pipe into sqs arn, it tells me that is the wrong arn format ...
If the file contents is different it should recognise this and load it. You should be using a regex pattern to be picking up your files. If the contents are the same it won’t load it again by default within 64 days.
We have lots of code templates and examples available as part of the mastering snowflake program. If you’re interested in finding out more visit www.masteringsnowflake.com
The purpose of this is to load the data from your text file into a table in Snowflake. If your talking about the actually file and not the data then you’d leave the file in place and query it as an external table. Check out my video on this channel about how to do this
@@mastering_snowflake yes I found this article, it was really helpful. I was just wondering if there were a way to use directly snowpipe but it seems that I first need to bring the data to a stage ?
Thanks! It's so clear to explain steps with a sample and well documented.
Glad it was helpful!
Snowflake's documentation on this is quite good, but this video was critical for me to cover the gaps! Thank you!
Thanks Aaron. I’m glad it helped!
Very nicely explained. Amazing detail and clarity. Thank you!
You have excellent Teaching attributes. Thanks for this.
Best video out there on snowpipe
Thanks for explaining this so clearly.
errr merr gersh! this was so helpful! Thank you sooo much! Mainly the bit for the sqs arn being in the snowpipe object and that one just needs to plug and play it - thank you! #10outof10 #wouldrecommend
This is pretty good video. Can you please provide details on or share info if you have come across this scenario
1. Once data is loaded into Snowflake stage, How to move the file from main S3 folder to an "archive" S3 folder. How to trigger that process after successful ingestion.
2. If we have to do some custom validation (like file type, header etc..) of the files before ingestion into snowpipe. where can this validation be done.
1. You'll need to write a script yourself or use an ETL tool to do this. The COPY into statement can purge files once loaded, but it cannot move it to another folder.
2. Use SQL scripts or your ETL tool to prepare and validate the files before you make it available for Snowflake to load.
@@mastering_snowflake Regarding point 1, can that script be written in Java Script and called from Snowflake Procedure?
Hi Adam.Have gone through your videos on snowflake.Good explanation.👍
I have one query though.
Can u help me understand if snowflake architecture would be good option to support oltp and olap systems and the reason being?
it is OLAP, SnowFlake is a Cloud Data Warehousing
Snowflake is primarily designed for large analytical queries, such as aggregations and calculations over very large data sets.
They fare less well when users run, ‘point lookup’ queries, or in other words, queries which are highly selective, and therefore look up just a small number of records.
If you have OLTP workloads I'd be exploring other technologies.
helpful !!
Hi Adam,
I have a small question,
I am trying to build a dwh pipeline, I got 2 json source files in on an s3 bucket, I created a pipe from s3 to my staging schema but I am not sure how do I continue from here, is there a way to create a pipline between two schemas\ tables or should I create a task + procedure to refresh every few minutes to load the data into prod?
Thanks Alex
Hi Alex, that should be all there is to it. If you have created the pipe with the auto ingest set to true it will periodically run as new files arrive in s3 and copy them into your target table.
Hope this helps!
@@mastering_snowflake Thanks it works
I have this in mind but I'm skeptical. I'm using Postman to pull data coming from an application API. I'd like to then push that data to individual Snowflake tables. Not sure if I need to Snowpipe or I can get away with just using the initial Postman to Snowflake (haven't even tested this yet). Using this template (in the video), I'd have to push the data from Postman to Snowflake and then grab it with Snowpipe. @mastering_snowflake what do you think?
Is it mandatory to use a storage integration ? Because my bucket is full public. When i copy the arn of my pipe into sqs arn, it tells me that is the wrong arn format ...
The storage integration isn’t mandatory, but it’s best practice within an enterprise scale environment.
@@mastering_snowflake All right thank you :)
how to bring in incremental data ?
how can i configure a snowpipe to grab the same filename from an s3 bucket when the file is refreshed and re-uploaded?
If the file contents is different it should recognise this and load it. You should be using a regex pattern to be picking up your files.
If the contents are the same it won’t load it again by default within 64 days.
@@mastering_snowflake Thank you, Adam for responding. Do you have some sample code for it?
We have lots of code templates and examples available as part of the mastering snowflake program. If you’re interested in finding out more visit www.masteringsnowflake.com
Can the text file from S3 be moved to snowflake?
Of course!
The purpose of this is to load the data from your text file into a table in Snowflake. If your talking about the actually file and not the data then you’d leave the file in place and query it as an external table.
Check out my video on this channel about how to do this
sir,how can we know the name of the snowpipe while loading data
Sure you name the pipe when you create it. Check this article out: docs.snowflake.com/en/sql-reference/sql/create-pipe
How does it work if the data I want to ingest into Snowflake is on a RDS MySQL database?
www.propeldata.com/blog/how-to-get-your-data-from-an-aws-rds-database-into-snowflake
@@mastering_snowflake yes I found this article, it was really helpful. I was just wondering if there were a way to use directly snowpipe but it seems that I first need to bring the data to a stage ?
Yes that’s right
#SnowPipe #AWSS3
HELP