Week 98 – Intermediate

Have you heard about iceberg tables? At the Summit, it has been announced that they are in general availability! However, in this challenge, you’ll focus on DATA_TRANSFER_HISTORY table function. It can detect the history of data transferred from Snowflake tables into different cloud storage provider networks or geographical regions within a specified date range.

Objective

The challenge is to use the DATA_TRANSFER_HISTORY function to analyze data transfer activities within your Snowflake account. This is a new kind of challenge! Hence, be sure to have the following prerequisites and answer the questions below to complete the challenge of week 98!

Prerequisites

• ACCOUNTADMIN role or a role with the MONITOR USAGE global privilege.
• Retrieve data from INFORMATION_SCHEMA schema and fully-qualified function names.

Questions

1. Cloud Providers Involved in Data Transfer: Make a query that draws the 1-hour interval. Identify the cloud providers involved in the data transfer. Provide SOURCE_CLOUD and TARGET_CLOUD for each record.
2. Total Data Transferred in the Last 24 Hours: Calculate the total number of bytes transferred in the last 24 hours. Use the results to sum the BYTES_TRANSFERRED.
3. Peak Data Transfer in the Last 14 Days: Identify the single largest data transfer operation in terms of bytes transferred. Provide START_TIME, END_TIME, SOURCE_CLOUD, TARGET_CLOUD and BYTES_TRANSFERRED for this operation.

Ready? 3 questions, 1 challenge!

P.S. Don’t worry if the execution returns empty spaces or null values. It’s likely because you haven’t set up any regular activity yet. No problem, the challenge encourages you to experiment with this function.

P.P.S. Remember to use the DARK MODE! Undeniably the biggest announcement of the #DataCloudSummit2024!

Remember if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge.