Snowflake Secure Data Sharing

Snowflake Secure Data Share enables companies to access and share PredictHQ’s data in a controlled and efficient way. Access to a Secure Share of our events means you will experience a familiar and high-performance SQL interface with an up-to-date, clean, and complete set of PredictHQ’s data. This means you can immediately incorporate the data into your models, removing or greatly simplifying the need for ELT/ETL processes to pull event data into your data warehouse. You can check out Introduction to Secure Data Sharing page if you're interested to read more on Snowflake's Secure Data Sharing.

• PredictHQ's Data Offerings on Snowflake Marketplace

• Data Description For Snowflake Data Shares

• Snowflake SQL Query Examples

• Frequently Asked Questions

PredictHQ's Data Offerings on Snowflake Marketplace

PredictHQ has partnered with Snowflake to make forecast-grade data more easily available. Events API's data is the first to be offered on the Snowflake Marketplace. We are working to make other endpoints (eg: "Entities", "Live TV Events") data available on Snowflake Marketplace soon.

Sample Data Shares

PredictHQ offers multiple up-to-date event Data Shares on Snowflake's Marketplace. This, bundled up with Snowflake's 30-day trial, offers you a commitment-free opportunity to try PredictHQ's data for free. You can view and try these sample Data Shares on PredictHQ's page on Snowflake's Marketplace or by clicking on any of the links below:

• Attended Events Sample Data Shares

  • Data Type: (Event's API Alternative)

  • Categories: sports, concerts, expos, conferences, festivals, performing-arts and community

  • Location: City of Seattle

  • Historical Visibility Time Window: 1 Year

  • Future Visibility Time Window: 30 Days

• Non-Attended Events Sample Data Shares

  • Data Type: Events (Event's API Alternative)

  • Categories: public-holidays, school-holidays, observances, politics, daylight-savings and academic

  • Location: City of Seattle

  • Historical Visibility Time Window: 1 Year

  • Future Visibility Time Window: 30 Days

• Unscheduled Events Sample Data Shares

  • Data Type: Events (Event's API Alternative)

  • Categories: severe-weather, disasters, airport-delays, terror and health-warnings

  • Location: State of California

  • Historical Visibility Time Window: 1 Year

  • Future Visibility Time Window: 30 Days

Customized Data Shares

Customized Data Shares can be set up to match your preferences in terms of data type, location, time window and business use case. They are secure, easy to set up and usually don't require business resources for data integrations if you're already in the Snowflake platform. Get in touch with us to discuss your needs and we will come back to you as soon as possible.

Accessing Customized Data Shares In Snowflake's Snowsight Web UI

Once a Customized Data Share has been set up for you, head over to the Snowflake's Snowsight Web UI. You might need to enter your Snowflake "Account Name" and your account credentials to log-in.

Once you have logged into Snowsight, navigate to Data >> Shared Data >> Shared With Me to see the new incoming or previously shared data shares. As you can see below, the incoming data shares would be under the Ready To Get section. You might need to switch to another Role from the top left corner of the navbar to be able to see the Ready To Get section.

Snowflake requires a user with the ACCOUNTADMIN role or with IMPORT SHARE privileges to perform data sharing tasks including viewing and accepting an incoming data share. If you cannot see anything listed under the Data >> Shared Data >> Shared With Me >> Ready to Get, make sure that there isn't another role you can select or reach out to your Snowflake administrator.

PredictHQ's Customized Data Shares would be in CUSTOMER_<ORG> format where the <ORG> would be your business name. You would see the pop-up below to accept the incoming data share once you click on it:

A custom name can be chosen for the database which would be created to contain the data share. You can also add additional roles who should be able to access the newly created database.

Data Description For Snowflake Data Shares

Event Data Shares:

The following data description applies to both sample and Customized Data Share for Events. All dates are in UTC but would displayed in Snowflake session's timezone.

Snowflake SQL Query Examples

Using SQL queries in Snowflake the shared data can be manipulated to fit into a variety of tasks. Feeding the data into analyses, dashboards and machine learning models are only a few examples that are easily possible with PredictHQs data and SQL data manipulation in Snowflake.

The following SQL examples are based on the Sample Data Shares we described above. Here our database name is marketplace but in your account, it would be the custom name that was chosen when the sample data shares were being added to your Snowflake account.

• Filtering on the ARRAY column labels, to find events with a construction label, using ARRAY_CONTAINS:

SELECT event_id,
       title,
       category,
       phq_rank,
       labels
FROM   marketplace.predicthq.seattle_attended_events AS EVENTS
WHERE  ARRAY_CONTAINS('construction' :: variant, EVENTS.labels);

• Filtering the nested ARRAY column place_hierarchies to find events at King County using FLATTEN: (Note that this uses the place id value, which is described further here)

SELECT event_id,
       title,
       category,
       labels,
       place_hierarchies,
       place_hierarchies_flattened.value                           AS place_hierarchy_list,
       place_hierarchy_flattened.value                             AS place_hierarchy_item
FROM   marketplace.predicthq.seattle_attended_events               AS events,
       LATERAL FLATTEN(input => events.place_hierarchies)          AS place_hierarchies_flattened,
       LATERAL FLATTEN(input => place_hierarchies_flattened.value) AS place_hierarchy_flattened
WHERE  place_hierarchy_flattened.value = '5799783'
QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY event_id) = 1; --Return distinct results only. There maybe multiple place heirarchies for the given placeid

• Filtering the GEOGRAPHY column geo to find events around a 500-meter radius of 47.623257, -122.336498 using ST_DISTANCE and ST_MAKEPOINT:

SELECT event_id,
       title,
       category,
       phq_rank,
       labels,
       phq_rank,
       geo
FROM   marketplace.predicthq.seattle_attended_events AS EVENTS
WHERE  ST_DISTANCE(EVENTS.geo, ST_MAKEPOINT(-122.336498, 47.623257)) <= 500;

• Using the GEOGRAPHY column geo to find all events within a user defined area (eg: within a polygon) using ST_WITHIN, ST_MAKEPOLYGON and TO_GEOGPRAPHY

First, we define the shape we are interested in restricting the search to, as shown in the image below.

The coordinates of this shape are used in the following query.

SELECT seattle_events.event_id,
       title,
       category,
       phq_rank,
       geo
FROM marketplace.predicthq.seattle_attended_events seattle_events
WHERE st_within(seattle_events.geo, st_makepolygon(to_geography(
 ('LINESTRING(-122.30263710021973 47.62467785241324,-122.33980178833006 47.60442694445526,
  -122.34134674072267 47.58451555263637,-122.27954864501955 47.616925574159424,
  -122.30263710021973 47.62467785241324)'))))

Using the geo field of the events, these can be plotted on a map as shown below.

Frequently Asked Questions

• How does costing work on the data shares? Snowflake's architecture separates data warehousing into different distinct layers: storage, virtual warehouses (compute), and cloud services. By using PredictHQ's data through Snowflake, you would only pay for the amount of computation that you would perform on the shared data. In other words, there is no cost for you to store the data and you would only be billed for running queries on the shared data. You can read more about Snowflake's costing on their website.

• How frequently is the shared data updated? PredictHQ's data shares get updated at midnight in the region's timezone. This frequency is generally sufficient for most business use cases. If the business use case requires more up-to-date data, it can be provided down to minute-level frequency. However, this would incur extra computation costs.

• Can I make a change in the shared data or re-share it? All database objects shared between accounts are read-only. They cannot be directly modified or deleted, including adding or modifying table data. Shared databases and all the objects in the database cannot be re-shared with other accounts.