Welcome back. In this lesson, I'll provide a quick review of Snowpipe and its intended use as a tool for continuous data ingestion. Let's begin. Snowpipe is a cloud-hosted service that operates within your Snowflake account. When in flight, it continuously fetches data from a pre-configured data source. If the data source is cloud-hosted, for example, either Amazon S3, Microsoft Azure Blob storage, or Google Cloud Storage, then Snowpipe ties into the event notification capabilities of that particular storage service. With this type of eventing in place, it is literally just a matter of minutes before that data becomes available as table hosted data within Snowflake, ready to be queried upon.

Snowpipe under the hood is a serverless component which is operated by Snowflake, but for which has a running cost associated with it, which you must pay for if you're using it. Charging is based on the amount of actual compute resource utilization involved in any data loading pipes that you have configured. Billing is tracked at a per second and per core granularity and is very economical for continuous data loading jobs. Snowpipe provides two types of integration, the first of which is seen here. This type of integration simply relies on the event notifications that cloud storage services such as Amazon S3 provide.

In this particular case, Snowpipe is simply configured to trigger on Amazon SQS notifications. Configuring this type of integration is simple and quick and can be completed typically within 5-10 minutes. In the background, Snowflake operates and maintains a fleet of servers which asynchronously gather the data and distribute it into Snowflake target tables. A key part of this design is that the operational server fleet is completely abstracted away from you, meaning your involvement with Snowpipe is purely configuration-based.

The second type of integration facilitates more advanced requirements, often those that involve a custom on-prem hosted data source. The Snowpipe integration option is shown here, provides a REST API endpoint that allows you to push notification messages to it. Declaring that you have dropped some new files into a cloud storage location, such as an Amazon S3 bucket. On the receiving side, the serverless loader internal to the Snowpipe service reacts and goes out to the cloud storage location and retrieves the new data files. This type of integration will typically require a little bit of custom code or scripting to coordinate the REST API calls in the placement of the data files. The good thing about this type of integration is that it does cover you for any obscure and/or edge case data sources that you may have and for which you need to continuously publish data into, and then to have it also be received by Snowflake.

Central to Snowpipe and how it is applied within a worksheet within Snowflake is the concept of a pipe. Creating a pipe is as simple as running a Snowflake create pipe SQL statement. A pipe definition simply wraps around a copy statement. In the example shown here, a Snowpipe is established by specifying the ARN of an AWS SNS topic, which hasn't turned behind the scenes being configured to receive notifications when data files are placed in a similarly named S3 bucket. Following on, the create pipe statement includes a copy command to specify the destination location of where the data should end up. In this case, the data is expected to be saved in the Cloud Academy skills table via a stage named Skills Stage. Behind the scene, Snowpipe uses a combination of the file name in a checksum performed over the file to determine and track new files, and therefore ensure that only new data gets loaded in.