Skip to content 
For enterprises which already have Snowflake as the cloud data warehouse, the Snowflake dynamic tables, coupled with Native Snowflake Connector for PostgreSQL, could substantially reduce latency with minimal rewrite of existing models. In particular, dbt 1.9 has broadened the support for Snowflake dynamic tables, enabling refresh mode selection.
We find the end-to-end latency of a chain of dynamic tables, being incrementally refreshed, to be well within a couple of minutes, when data consists of a couple of million rows and joins are simple. However, when the rows increase to 200 million, and when joins become more complicated, the latency deteriorates beyond the target lags.
Let’s see what we can do about it.
Understanding Snowflake dynamic tables
Snowflake dynamic tables are a powerful feature that allows you to create tables that automatically update as their underlying data changes. Unlike traditional tables that require manual refreshes or scheduled batch jobs, dynamic tables leverage Snowflake’s compute capabilities to maintain up-to-date data representations with minimal intervention.
Snowflake dynamic tables’ key features:
- Incremental refreshes: Only new or changed data is processed, reducing the computational overhead and speeding up refresh times;
- Automated dependencies: Dynamic tables can automatically manage dependencies between different data models, ensuring consistency and accuracy.
- Seamless integration with dbt: With dbt version 1.9, managing dynamic tables becomes more intuitive, as data engineers can define refresh behaviours directly within their dbt models.
Leveraging the Native Snowflake Connector for PostgreSQL
The Native Snowflake Connector for PostgreSQL facilitates direct communication between PostgreSQL databases and Snowflake, enabling seamless data transfer and synchronisation. This integration is pivotal for enterprises looking to minimise latency without overhauling their existing data infrastructure.
Using the Snowflake Connector for PostgreSQL comes with several major benefits. Fist, it allows for data synchronisation with little to no modification of existing PostgreSQL queries or schemas, meaning minimal code changes. Moreover, Snowflake’s optimised storage and compute resources help enhance performance through swift data transfers and low latency.
Finally, there are the advantages that come with scalability. The connector efficiently handles large data volumes thus ensuring that performance remains consistent as your datasets grow.
Enhanced support with dbt 1.9
dbt has revolutionised the way data engineers manage and transform data within data warehouses. dbt 1.9 offers unprecedented support for Snowflake dynamic tables, as well as high flexibility and control over data refresh processes.
An example dbt configuration for dynamic tables would be:
New capabilities that deserve your attention:
- Refresh mode selection: Data engineers can now specify how and when Dynamic Tables should refresh, choosing between options like incremental or full refreshes, based on the specific needs of each model;
- Improved configuration options: Enhanced settings allow for finer control over the behaviour of dynamic tables, enabling more precise optimisations tailored to different use cases. dbt also makes it easy to implement existing features, such as the post hooks for clustering keys, which further speed up any future dynamic table refreshes;
- Seamless integration: The updated dbt version ensures that dynamic tables work harmoniously within existing dbt workflows, reducing the learning curve and the implementation time.
Performance insights: from millions to tens of millions of rows
Low latency with moderate data volumes
Our tests clearly demonstrate that in scenarios where datasets comprise a few million rows with simple joins, the combination of Snowflake dynamic tables, the Native Snowflake Connector for PostgreSQL, and dbt 1.9 delivers impressive performance.
Let’s consider:
- End-to-end latency: The time from data ingestion to availability in dynamic tables was consistently within a couple of minutes;
- Incremental refresh efficiency: Only the new or modified data was processed during refresh cycles, significantly reducing processing times compared to full table reloads;
- Resource utilisation: Optimised compute resource allocation ensured that the data pipeline remained efficient without unnecessary consumption.
Challenges with larger datasets and complex joins
As the row count scales to 200 million and joint complexities increase, maintaining low latency becomes more challenging.
Some of our observations include:
- Increased processing times: The latency for end-to-end data availability began to exceed the initial target, stretching beyond a few minutes;
- Resource constraints: Larger datasets and more intricate joins demand greater computational resources, requiring a bigger warehouse size like XL, which can lead to bottlenecks if not adequately provisioned;
- Query optimisation needs: Complex joins necessitate more sophisticated query optimisation strategies to maintain performance levels.
Strategies to mitigate latency with large and complex datasets
To address the challenges associated with scaling, enterprises can adopt several strategies to optimise performance and maintain low latency:
- Query optimisation:
- Simplify join conditions: Review and refine join conditions to ensure they are as efficient as possible. Avoid unnecessary joins and leverage indexes where applicable;
- Selective column retrieval: Instead of using SELECT *, specify only the necessary columns to reduce data processing overhead;
- Clustering and partitioning: Implement clustering keys on frequently joined columns to enhance query performance and reduce scan times.
- Resource scaling and allocation
- Allocating dedicated warehouse: Try to allocate a dedicated warehouse for handling refreshes of dynamic tables, as this allows easier management of resources and measurement of costs. Furthermore, particularly demanding tables can use their own dedicated warehouses to avoid long queues, which degrade performance;
- Rightsizing the warehouse: A larger warehouse (like L or XL) can help performance a lot because there is more memory available for joints happening during refreshes.
- Incremental and full refreshes
- Delta loading: Focus on processing only the data that has changed since the last refresh, minimising the volume of data handled in each cycle;
- Selective refresh strategies: Tailor refresh strategies based on the nature of the data and the specific requirements of each dynamic table. In some cases, full refresh could be a more performant alternative than incremental refresh.
- Advanced dbt Configurations
- Materialisations: Leverage dbt’s materialisation options to optimise how models are built and maintained within Snowflake;
- Optimised dependencies: Carefully manage model dependencies to ensure that data transformations are executed in the most efficient order.
- Monitoring and continuous improvement
- Performance monitoring: Implement robust monitoring tools to track query performance, resource utilisation, and overall pipeline health;
- Regular audits and reviews: Periodically review and audit the data pipeline to identify and address performance bottlenecks proactively.
Optimising dynamic tables for high-volume data: let’s see it in action
To see how all of this comes together, let’s look at a real-world project case we had at Infinite Lambda.
Context
An enterprise in the retail technologies space utilises Snowflake as its central data warehouse, managing extensive transactional data from multiple sources. With an initial dataset of 5 million rows in the sandbox, dynamic tables provided timely insights with minimal latency.
However, as the business expanded out of the sandbox into full production, the dataset grew to 200 million rows, and the complexity of joins increased to accommodate more sophisticated analytics requirements.
Challenges
The data volume increased, scaling from 5 million to 200 million rows, introducing significant processing overhead. Complex joins came into play. Here, multiple join conditions and aggregated metrics required more computational power. It was also far more difficult to ensure that data remained fresh and accessible within the desired latency thresholds.
Solutions
- Enhanced query optimisation: We simplified join conditions and reduced the number of nested joins. Implementing selective column retrieval allowed us to minimise data processing. We also chose to use business-id as clustering keys to speed up the refreshes.
- Resource scaling: We upgraded Snowflake warehouses to higher compute and memory tiers during peak processing times. To remove queues, we opted for a dedicated warehouse to dynamic table refreshes, and even to the refresh of a specific table. It worked like a charm.
- Incremental refresh strategies: Delta loading was only adopted to process new or updated data when incorporating new delta requires trivial computations. We stayed with full refreshes for tables that require complicated joins and extensive computations for incorporating deltas. To balance load and resource availability, we scheduled full refreshes during off-peak hours.
- Advanced dbt configurations: dbt’s materialisation and refresh strategies for dynamic tables yielded the optimal configuration we were aiming for. We managed any model dependencies to ensure efficient execution order and resource utilisation. We used upstream as target lag so that the real lags can be deduced and imputed.
Results
- Reduced latency: Successfully maintained end-to-end latency within 5 minutes, even with 200 million rows and complex joins;
- Resource efficiency: Optimised compute resource allocation led to reduced costs and improved processing efficiency;
- Scalability: The data pipeline became more resilient and scalable, capable of handling future data growth and increasing analytical demands.
Final words on reducing latency
Snowflake dynamic tables, when paired with the Native Snowflake Connector for PostgreSQL and enhanced by dbt 1.9, offer a compelling solution for enterprises aiming to reduce data latency with minimal modifications to existing dbt models.
While the initial performance gains are significant for moderate datasets and simple joins, scaling to larger datasets and more complex joins necessitates strategic optimisations and resource management.
Best practices, such as query optimisation, dynamic resource scaling, incremental processing, and leveraging advanced dbt configurations, empower organisations to maintain low latency even as their data volumes and analytical requirements grow.
This ensures that businesses can continue to derive timely and actionable insights from their data warehouses, driving informed decision-making and sustaining a competitive edge in a highly dynamic environment.
Further reading
Get you copy of Infinite Lambda's book Data & AI: Fast and Slow and explore our proprietary methodology for leveraging data and AI for efficient problem-solving across the entire organisation.
Visit the Infinite Lambda Blog for the latest insights into data and AI technology.
