Overview
Snowflake is a cloud-based data platform that lets you securely store, organize, and analyze large amounts of data. By integrating Snowflake with SundaySky, you can use your existing viewer data directly from Snowflake to personalize videos at scale.
When using Snowflake, your data is never stored on SundaySky servers. Instead, each time a viewer clicks to watch a video, SundaySky retrieves the relevant records from Snowflake in real time to generate a personalized video, ensuring the use of the latest data.
Each video link created for a viewer is unique and contains a "sticky" ID. This means that if you update the viewer data in Snowflake or make changes to the video content, the video updates automatically. There's no need to resend links as viewers will always see the latest version when they revisit their original link.
In this article, you'll find complete guidelines for working with the Snowflake data connector, from setting up a data library through to generating video links for your viewers.
Note:
Exceeding your contracted Snowflake API call or rate limits may prevent videos from being personalized. To avoid this, validate your limits and throttle bulk personalized video sends as needed. For details about API limits and best practices, see the Snowflake SQL API documentation and refer to your plan.
Step 1: Select or Create a Data Library
Based on how you want to personalize your video, you'll need to connect the video to a data library that includes the data fields for creating the personalization. SundaySky supports three methods of personalization:
| Personalization method | Use for personalizing: | In the data library, create: | Notes | |
| a. | 1:1 using a Personalization Token |
On-screen text Media Narration Buttons |
Data field only | For narration, values are also required. |
| b. | Message by Audience |
On-screen text Media Narration Buttons |
Data field and values | |
| c. | Show/Skip scene | Showing or skipping a scene | Data field and values | Content types are not relevant for this personalization method. |
Before creating a new data library, review the existing data libraries in your account to determine whether one of them already meets your requirements. The same data library can be used for multiple videos. If you choose to use an existing data library, it must meet all of the following conditions:
- It contains the data fields you need for personalization.
- It is connected to the Snowflake data connector.
- The Snowflake integration points to the database containing your viewers' data.
If you find a data library with the required data fields but it is connected to a data source other than Snowflake, do not change its data connector. Doing so could negatively impact any videos already using this library. In this case, you will need to create a new data library instead.
For instructions, see:
Selecting an Existing Data Library
Creating a New Data Library
At the end of Step 1:
A data library is selected for the video. This can be an existing data library or a new one.
Step 2: Set Up the Snowflake Database
In this step, you'll set up—or make sure you have ready—the Snowflake database, schema, and table that store the data used for personalization. Once these components are in place and your Snowflake warehouse is ready, you can connect them to SundaySky so it can access the data for video personalization.
Below is an example of the Snowflake interface displaying the new_employees table in the employee_onboarding schema, showing all column headers with no data entries yet.
For your convenience, a file with this SQL code, MY_DATABASE_EMPLOYEE_ONBOARDING.txt, is available for download at the end of the article.
Here are the key points to know about the table you create in Snowflake:
- As a best practice, create a table exclusively for SundaySky. This keeps all fields needed for personalization in one place and avoids including viewer data that isn't required. This approach helps keep your data organized and focused, without exposing unnecessary information.
- Include a unique identifier for each viewer. The table must have a field that uniquely identifies each viewer. In the example above, the field ID serves this purpose, but any field can be used as long as each value is unique. SundaySky uses this identifier to match each viewer to their record and personalize the video accordingly. A unique video link is generated for each record.
- All other fields (except the unique identifier) are optional. If a value is missing in any of them, SundaySky will use the default value for the corresponding content in the video.
At the end of Step 2:
The Snowflake table, containing viewer data, is now set up.
Step 3: Select the Snowflake Data Connector
| If you selected a data library that is already connected to the Snowflake data connector, skip to Step 5. |
The connector is what enables the connection between the data fields defined in the data library and the actual viewer data stored in Snowflake. In simple terms, the connector is what determines how data flows from Snowflake to SundaySky.
| a. | Open the relevant video in the Studio. |
| b. | Select Data in the sidebar. |
| c. | Click Select in the data connector section. |
| d. | Select the Snowflake data connector and then click Select at the bottom of the window. |
| e. | (Optional) If you haven't yet integrated Snowflake with your SundaySky account, you'll be prompted to do so now. See Step 4: Integrate Snowflake with SundaySky for step-by-step instructions. |
| A dialog window is displayed, prompting you to enter your Snowflake database details. | |
| f. | From the drop-down lists in the dialog window, select the Snowflake database, schema, and table that contain the viewer data you want to use for personalization. ▶ Make sure you select the correct combination so SundaySky can access the appropriate records. |
| g. | In the ID field, select the column in the Snowflake table that serves as the unique identifier. |
| h. | Click Create & map Data Fields to continue directly to field mapping. ▶ If you prefer mapping at a later time, click Complete authentication and map Data Fields later to return to the data library. |
At the end of Step 3:
The Snowflake data connector is attached to the data library.
Step 4: Integrate Snowflake with SundaySky
| If you have already integrated Snowflake with your SundaySky account, skip to Step 5. |
Here are the essential things to know about integrating Snowflake with SundaySky:
- Before you start the authentication process, you'll need to set up a dedicated Snowflake user and create a programmatic access token. This ensures a secure connection between Snowflake and SundaySky. See Creating a User and Access Token for step-by-step instructions.
- The Snowflake integration only needs to be configured once. After it's set up, you can use Snowflake as a data source with any data library in your account.
- Each SundaySky account can be integrated with only one Snowflake account.
- Integrating with SundaySky involves connecting to a specific database. If you need to change it later, see Changing the Snowflake Database.
- If you need to reauthenticate or remove the Snowflake connector, see Data Connector Management for instructions. Keep in mind that reauthenticating will break the current connection to SundaySky in all videos (draft, approved, and live), and those videos will not be personalized until you integrate Snowflake again.
To integrate Snowflake with SundaySky:
| a. | Select the Snowflake data connector and then click Select at the bottom of the window. |
| b. | Click Log in. |
| c. | Click Begin authentication. |
| d. | In the Account field, enter your Snowflake account identifier. |
|
Show me where to find my Snowflake account identifier
|
|
| e. | In the Username field, enter the dedicated username used for the SundaySky integration. |
| f. | In the Programmatic access token field, paste the token secret generated when you created the user and token using the SQL commands. |
|
View an example of a token secret
|
|
| g. | In the Warehouse field, enter the name of your Snowflake warehouse. ▶ In Snowflake, a warehouse is the compute resource (the engine) that processes queries and retrieves data from your database. |
| h. | Click Create. |
| A green check mark indicates that you are successfully logged in to your Snowflake account. | |
| i. | From the drop-down lists in the dialog window, select the Snowflake database, schema, and table that contain the viewer data you want to use for personalization. ▶ Make sure you select the correct combination so SundaySky can access the appropriate records. |
| j. | In the ID field, select the column in the Snowflake table that serves as the unique identifier. |
| k. | Click Create & map Data Fields to continue directly to field mapping. ▶ If you prefer mapping at a later time, click Complete authentication and map Data Fields later to return to the data library. |
At the end of Step 4:
The Snowflake data connector is connected to your SundaySky account.
Step 5: Map the Data Fields
| If you selected an existing data library and have verified that the mapping is correct, skip to Step 6. |
The mapping process defines which field in your Snowflake table corresponds to each data field in the SundaySky data library. Accurate mapping ensures that the correct data is pulled from Snowflake to populate each personalized video.
Keep in mind:
- If you add a new data field after completing the initial mapping, you'll need to map it as well.
- Renaming a data field that has already been mapped does not affect the existing mapping.
| a. | Open the relevant video in the Studio. |
| b. | Select Data in the sidebar. |
| c. | Click Map Fields in the data connector section. |
| In the mapping window, the data fields created for the data library are displayed on the right. For each of these data fields, you'll need to select the Snowflake field to which it should be mapped. | |
| d. | For each data field, open the Snowflake Field drop-down list and select the field to map it to. ▶ The drop-down list also includes the identifier field you created in your Snowflake table (for example, ID). This field does not need to be mapped. |
| e. | After all the fields are mapped, click Save at the bottom of the window. |
At the end of Step 5:
The Snowflake table fields are mapped correctly to the data fields.
Step 6: Configure the Content
Now that you've set up your data library, you can configure the content in the video's scenes using the data fields.
The on-screen text, narration, and media can be personalized using either a personalization token or audience messaging. In addition, you can also define that a specific scene will be shown or skipped according to an audience segment.
In the example below, a personalization token is used to customize the text according to the first name of the viewer.
In the following articles, you'll find step-by-step instructions on how to configure the different types of content:
Setting Up Personalized Text Using a Personalization Token
Text: Message by Audience
Setting Up Personalized Narration Using a Personalization Token
Narration: Message by Audience
Configuring Media According to Data Feed
Media: Message by Audience
Showing or Skipping a Scene by Audience
At the end of Step 6:
All the relevant content in the video is configured according to the desired personalization.
Step 7: Approve the Video
After you finish creating the video—including configuring all personalized content—we recommend previewing it with viewer profiles to ensure everything looks as intended.
Once you're satisfied with the result, approve the video to lock in your edits. Approval is always required before you can share the video with viewers.
Keep in mind that you'll need to reapprove the video each time you make changes so viewers can see the updated content.
At the end of Step 7:
The status of the video is Approved to share.
Step 8: Get Video Links
In this final step, you're ready to generate the video links so that each viewer can watch a personalized version. The actions in this step vary depending on whether the video will play on a SundaySky landing page or elsewhere.
Generating Video Links for a SundaySky Landing Page
Generating Video Links for a Non-SundaySky Landing Page
As mentioned earlier, each video link is created with a "sticky" ID that is unique to each viewer. Because of this, you don't need to resend links after updating the video content or the viewer's data. Viewers can simply revisit the original link to see the updated content.
Generating Video Links for a SundaySky Landing Page
| a. | On the Video Page, select the Share tab. |
| b. | Make sure that the status of the video is Approved to share. |
| c. | On the right side of the page, click the Share icon next to the Landing Page option. ▶ If you haven't created the landing page yet, click the plus sign (+) to create the landing page. |
| d. | Copy and paste the landing page URL into your distribution platform. |
| e. | Configure your distribution platform to replace INSERT_VALUE with the relevant value needed for personalization. For example: https://myvideo.sundaysky.com/?programId=818ef8d5-5a17-43c3-b691-65ddb2057c73&id=1 ▶ In the URL, id always appears, but it corresponds to the unique identifier you selected in your Snowflake table. For example, even if you chose first_name as the unique identifier, id will still be shown in the URL. |
Generating Video Links for a Non-SundaySky Landing Page
| a. | On the Video Page, select the Share tab. |
| b. | Make sure that the status of the video is Approved to share. |
| c. | On the right side of the page, click the Share icon next to the Embed Video option. ▶ If you haven't created the code snippet yet, click the plus sign (+) to create the snippet. |
| d. | Select the embed method you want to use, iframe or JavaScript. ▶ By default, the iframe option is selected. ▶ To learn more about iframe and JavaScript embed codes, see Embedding the SundaySky Player in a Webpage. |
| e. | Copy and paste the embed code snippet into the page code where you want the video player to appear. |
| f. | Copy the required parameters and add them to the end of the landing page URL. |
| g. | In the URL, replace INSERT_VALUE with the relevant value for each viewer. For example: https://video.YourWebsite.com/?programId=818ef8d5-5a17-43c3-b691-65ddb2057c73&id=1 ▶ In the URL, id always appears, but it corresponds to the unique identifier you selected in your Snowflake table. For example, even if you chose first_name as the unique identifier, id will still be shown in the URL. |
|
|
At the end of Step 8:
The personalized video links are generated, ready to be distributed by the method you prefer.
Creating a User and Access Token
Before integrating Snowflake with SundaySky, you need to set up a dedicated Snowflake user and generate the credentials required for authentication. This includes creating the user, applying an authentication policy, and generating a programmatic access token. These credentials are essential. Without them, SundaySky cannot access your Snowflake data. Completing these steps ensures a secure, fully authenticated connection.
All of the setup in this section is done using the SQL commands below, in the order they should be executed:
| a. | Use the correct role. Before you can create users and tokens, you need to make sure you're using a role with the correct privileges. |
|
SQL command: USE ROLE ACCOUNTADMIN; |
|
| Result: You're now operating with the ACCOUNTADMIN role, which has the privileges needed to create users, roles, and access tokens. | |
| b. | Create a dedicated service user. Before SundaySky can connect to Snowflake, you need a dedicated service user. sundaysky_service_user is the name used in this example, but you can use any name that fits your organization's naming conventions. |
|
SQL command: CREATE USER sundaysky_service_user TYPE = SERVICE |
|
| Result: You've created a new service user named sundaysky_service_user. This user will be used exclusively for the SundaySky integration and isn't tied to an individual login. | |
| c. | Create an authentication policy. This policy defines how the service user will authenticate. Because authentication policies are database objects in Snowflake, you first need to set the database context with USE DATABASE. In our example, the database is MY_DATABASE and the policy is named sundaysky_pat_policy. You can replace these values with the database and policy name that fit your environment. |
|
SQL command: USE DATABASE MY_DATABASE; CREATE OR REPLACE AUTHENTICATION POLICY sundaysky_pat_policy AUTHENTICATION_METHODS = ('PASSWORD', 'PROGRAMMATIC_ACCESS_TOKEN') PAT_POLICY = ( NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED ); |
|
|
Result: You've created an authentication policy named sundaysky_service_user in the MY DATABASE database. This policy allows the service user to authenticate with either a password or a programmatic access token. |
|
| d. | Assign the authentication policy to the service user. This step ensures that the service user follows the rules defined in your authentication policy. In our example, the service user is sundaysky_service_user and the policy is sundaysky_pat_policy. You can replace these names with the user and policy that fit your environment. |
|
SQL command: ALTER USER sundaysky_service_user SET AUTHENTICATION POLICY sundaysky_pat_policy; |
|
| Result: The service user sundaysky_service_user now follows the sundaysky_pat_policy and can authenticate using a password or a programmatic access token. | |
| e. | Create a dedicated role with read-only access. This role grants the SundaySky service user minimal read-only permissions. In our example, the role is named SUNDAYSKY_READONLY. Usage is granted on the warehouse COMPUTE_WH, the database MY_DATABASE, the schema EMPLOYEE_ONBOARDING, and select access on the table NEW_EMPLOYEES. |
|
SQL command: CREATE ROLE SUNDAYSKY_READONLY; GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SUNDAYSKY_READONLY; GRANT USAGE ON DATABASE MY_DATABASE TO ROLE SUNDAYSKY_READONLY; GRANT USAGE ON SCHEMA EMPLOYEE_ONBOARDING TO ROLE SUNDAYSKY_READONLY; GRANT SELECT ON TABLE NEW_EMPLOYEES TO ROLE SUNDAYSKY_READONLY; |
|
| Result: You've created a new role named SUNDAYSKY_READONLY and assigned it only the minimal privileges required for the integration. The role can use the specified warehouse, database, and schema, and it can select data from the NEW_EMPLOYEES table. | |
| f. | Assign the read-only role to the SundaySky service user. The SUNDAYSKY_READONLY role is assigned to the dedicated SundaySky service user, so that the integration can use the permissions defined in the role. In this example, the service user is named sundaysky_service_user. |
|
SQL command: GRANT ROLE SUNDAYSKY_READONLY TO USER sundaysky_service_user; |
|
| Result: The sundaysky_service_user now has the SUNDAYSKY_READONLY role. This allows the service user to access the warehouse, database, schema, and table with read-only permissions, keeping your data secure. | |
| g. | Create a programmatic access token for the SundaySky service user. This token lets SundaySky connect to Snowflake securely without needing a personal password. The token is restricted to the SUNDAYSKY_READONLY role and is set to expire after 365 days. |
|
SQL command: ALTER USER "SUNDAYSKY_SERVICE_USER" ADD PROGRAMMATIC ACCESS TOKEN sundaysky_service_token ROLE_RESTRICTION = 'SUNDAYSKY_READONLY' DAYS_TO_EXPIRY = 365; |
|
|
Result: A programmatic access token named sundaysky_service_token has been created. The token is restricted to the read-only role, which means the integration can only access the specified warehouse, database, schema, and table. It will expire after one year, which helps maintain security. During authentication, the token secret is copied into the Programmatic access token field. |
You can run all the commands at once using the full script below. Make sure to execute them in order and change the warehouse, database, schema, and table names to match your environment.
For your convenience, you can download the file Snowflake User and Access Token_SQL Commands.txt at the end of this article, which contains all the commands exactly as shown.
| USE ROLE ACCOUNTADMIN; CREATE USER sundaysky_service_user TYPE = SERVICE USE DATABASE MY_DATABASE; CREATE OR REPLACE AUTHENTICATION POLICY sundaysky_pat_policy AUTHENTICATION_METHODS = ('PASSWORD', 'PROGRAMMATIC_ACCESS_TOKEN') PAT_POLICY = ( NETWORK_POLICY_EVALUATION = ENFORCED_NOT_REQUIRED ); ALTER USER sundaysky_service_user SET AUTHENTICATION POLICY sundaysky_pat_policy; CREATE ROLE SUNDAYSKY_READONLY; GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SUNDAYSKY_READONLY; GRANT USAGE ON DATABASE MY_DATABASE TO ROLE SUNDAYSKY_READONLY; GRANT USAGE ON SCHEMA EMPLOYEE_ONBOARDING TO ROLE SUNDAYSKY_READONLY; GRANT SELECT ON TABLE NEW_EMPLOYEES TO ROLE SUNDAYSKY_READONLY; GRANT ROLE SUNDAYSKY_READONLY TO USER sundaysky_service_user; ALTER USER "SUNDAYSKY_SERVICE_USER" ADD PROGRAMMATIC ACCESS TOKEN sundaysky_service_token ROLE_RESTRICTION = 'SUNDAYSKY_READONLY' DAYS_TO_EXPIRY = 365; |
Changing the Snowflake Database
If you change the Snowflake database connected to SundaySky, you'll also need to update the associated schema, table, and ID field. These elements define how viewer data is accessed, so they must stay aligned with the new database.
Changing a database affects only draft videos. Videos that are already live will continue playing with the previous data connection until they are reapproved. Once reapproved, they will use the updated database settings.
After changing the database, you'll need to remap all the SundaySky data fields to the new Snowflake fields.
To change the database:
| a. | Open the relevant video in the Studio. |
| b. | Select Data in the sidebar. |
| c. | Click the Options menu in the upper-right corner of the window. |
| |
|
| d. | Select Update Snowflake database. |
| e. | Select the new database. ▶ You can also change the schema, table, or ID field for the current database connection. |
| |
|
| f. | Click Save updates. |
| g. | Remap the data fields according to the new connection. |