Managing complex data pipelines is a major challenge for data-driven organizations looking to accelerate analytics initiatives. While AI-powered, self-service BI platforms like ThoughtSpot can fully operationalize insights at scale by delivering visual data exploration and discovery, it still requires robust underlying data management.
Now, that’s changing. Snowflake's new dynamic tables feature redefines how BI and analytics teams approach data transformation pipelines. With dynamic tables automating ETL workflows directly in Snowflake and ThoughtSpot surfacing those insights to all users through multi-modal, AI-Powered Analytics, you can now optimize your stack to deliver the most value for your entire business.
What are dynamic tables?
Snowflake dynamic tables are a special type of table that automatically updates based on a defined SQL query. When created, Snowflake materializes query results into a persistent table structure that refreshes whenever underlying data changes. These tables provide a centralized location to host both your raw data and transformed datasets optimized for AI-powered analytics with ThoughtSpot.
By leveraging ThoughtSpot with Snowflake's dynamic tables, you’ll uncover new opportunities to leverage your data for enhanced analytics and decision-making. This article provides a technical overview of this integration so you can make the most of your data investment.
How dynamic tables work
TL;DR: Dynamic tables provide an automated and scheduled refresh of transformation query results, using compute resources to execute the refreshes and target freshness to define the refresh frequency.
When creating a dynamic table in Snowflake, you specify the SQL query that will transform the source data. Snowflake then executes this query on a scheduled basis to refresh the contents of the dynamic table based on a target ‘lag’ you specify. The refresh process automatically computes changes in the source data and merges those updates into the dynamic table. To power these refreshes, compute resources are associated with each dynamic table.
A target freshness window can also be defined—this allows you to specify how delayed the dynamic table contents can be compared to the source. For example, specifying a 5-minute freshness means the table refreshes try to stay within 5 minutes of updates to the source data. The target freshness impacts costs, as longer windows require less frequent refreshes.
How to connect ThoughtSpot to Snowflake dynamic tables:
1. Create Snowflake dynamic tables
In Snowflake, create dynamic tables by writing SQL queries that define how data should be transformed and materialized. Set refresh schedules as needed.
2. Grant ThoughtSpot access
In Snowflake, grant the ThoughtSpot service account USAGE privileges on the schemas containing the dynamic tables. This allows ThoughtSpot to see and query the tables.
3. Configure Snowflake Database Connection in ThoughtSpot
In ThoughtSpot, go to Admin > Data Connectors and create a new Snowflake database connection.
Enter the Snowflake credentials, URL, warehouse, and database.
Hit ‘Continue’. Select the schema, dynamic tables, and columns you want to expose to ThoughtSpot.
Create a worksheet.
4. Start searching your real-time data with AI-Powered Analytics
Now, you can use ThoughtSpot’s robust relational search engine to ask and answer any business question you have about your Snowflake data. Even better, any business user can leverage our natural language search experience to get trusted insights and make smarter business decisions.
Leveraging dynamic tables for ThoughtSpot
There are a number of applications for this strategic integration between Snowflake’s dynamic tables and Thoughtspot. To kick you off, we’ve identified some key ways use cases to try out:
Generate optimized datasets directly in Snowflake to shape insights in ThoughtSpot's semantic model graph.
Schedule refreshes to keep ThoughtSpot analytics up to date by automatically incorporating new data into Liveboards, NL Searches, and Answers.
Rapidly experiment and Iteratively improve datasets without ETL code changes by modifying SQL directly in Snowflake.
Simplifiy multi-structured data integration by federating JSON, XML, and other formats through Snowflake for analysis.
Ease disaster recovery with the ability to recreate datasets on failover targets through their SQL definitions.
Govern self-service in ThoughtSpot by using multi-structured and transformed data hosted alongside transactional systems in Snowflake.
Using Snowflake dynamic tables with ThoughtSpot allows you to streamline data preparation while also accelerating insight consumption across lines of business. Leveraging ThoughtSpot's simplicity, accuracy, speed, and storytelling capabilities, you can fully operationalize and scale GenAI for your data and analytics operations.
Learn more about AI-Powered Analytics for your Snowflake Data Cloud.
