---
# Best practices for Snowflake implementation
**URL:** https://www.sigmoid.com/ebooks-whitepapers/snowflake-implementation/
Date: 2022-06-15
Author: Sigmoid
Post Type: page
Summary: Best practices for snowflake implementation Chapter- 1 Introduction Chapter- 2 The architecture Chapter- 3 The implementation journey Chapter- 4 The data engineering...Read More...
Featured Image: https://www.sigmoid.com/wp-content/uploads/2023/04/Best-practices-for-snowflake-implementation.jpg
---
# Best practices for snowflake implementation
[fluentform id="143"]
[Chapter- 1 Introduction](#chapter-1)
[Chapter- 2 The architecture](#chapter-2)
[Chapter- 3 The implementation journey](#chapter-3)
[Chapter- 4 The data engineering best practices](#chapter-4)
## 1. Introduction
Businesses that thrive on advanced analytics are continuously improving the way they manage increasing volumes of data. Often the prime factor for business success – effective data management, has been a cornerstone of holistic enterprise growth and development.
Even before analytics can yield effective business outcomes, the underlying data has to be hosted and managed effectively. Snowflake enables every organization to mobilize their data with Snowflake’s Data Cloud. It allows for data storage, processing, and analytic solutions and offers enterprises a single-stop [cloud-data warehouse solution.](/cloud-migration/)
## 2. The architecture
Snowflake is built on top of existing public cloud infrastructure and is ideal for organizations unwilling or unable to dedicate resources towards setup, maintenance, and support in-house servers. But perhaps, the most defining factor that sets Snowflake apart is its data sharing capabilities and architecture. Furthermore, organizations are able to share secure and governed data quickly and in real time with the sharing functionality.
Snowflake also offers flexibility in terms of organizational requirements. The ability to decouple storage and compute functions means they can choose which function to focus on instead of paying for bundles. Overall there are three, independently scalable layers of Snowflake architecture:
Database storage: Runs independently of compute resources and manages data storage, organization, metadata, statistics, compression, structure, and file sizes automatically on all stored structured and semi- structured data.
Compute layer: Consists of virtual warehouses that can access all data in the storage layer and execute query specific data processing. This enables automatic and non-disruptive scaling.
Cloud services: This eliminates the need for manual data warehouse tuning and management by coordinating the entire system using ANSI SQL. Services include:
- Access control
- Query optimization and parsing
- Metadata management
- Infrastructure management
- Authentication
Here’s a diagrammatic overview of the overall system architecture on Snowflake and the most common [data pipeline](/etl-and-data-pipeline/) flow on Snowflake
Fig 1: System architecture on Snowflake
## 3. The implementation journey
As with all changes, there are a few caveats that businesses will do well to account for before plunging into a full-fledged Snowflake implementation and get the most out of it. Here are the key considerations:
Fig 2: Key considerations when implementing Snowflake
### 1. Data acquisition and loading
Efficient data loading is paramount especially since 94% of all workloads are processed in cloud data centers as of 2021. Faster data loading can not only mean getting faster business value, it also saves valuable organizational resources. Businesses can look into tools that simplify bulk loading, running complex queries, and parallel data loading through multiple threads to achieve faster data loading.
### 2. Data transformation
After loading the data in the Snowflake landing table, companies can leverage several tools to clean, enrich and transform the data. For instance,
ETL tools: With [ETL tools](/etl-and-data-pipeline/), companies often have the liberty to leverage the existing data engineering team and its skillset. With this approach, companies need to follow the “push-down” principle where the ETL tools run the search queries on the data pushed to Snowflake to drive maximum advantage from the architecture that is scaled up.
Stored procedures: Besides JavaScript API, Snowflake is expected to soon launch a procedural language referred to as Snowflake Scripting which includes support for dynamic SQL, cursors and loops. These features in cominationation with functions like Java User Defined Functions and External Functions can be leveraged to create high-end data transformation logic in Snowflake.
Tasks & streams: An effective yet simple method of implementing change data capture (CDC) in Snowflake is through Streams. Companies can combine Snowflake Tasks and Streams on the ingested data to achieve near real-time processing.
Java and Spark on snowflake: By leveraging Snowflake API which has been recently released Data engineers and scientists can now load data using InteliJ, SBT, Visual Studio, Scala and Jupiter where Spark DataFrames are translated and executed automatically as Snowflake SQL.
Transient tables: Companies can break down complex pipelines into smaller steps to write intermediate results to transient tables. This can make it easier for data teams to test and debug the ingested data.
### 3. Choosing the right data warehouse
While selecting an effective and efficient warehouse companies can experiment with diverse query types and warehouse sizes to determine the right combination that caters to specific workload and query needs. Companies need not essentially focus on the size of the warehouse. Snowflake runs on a per-second billing mode and so companies can leverage larger warehouses depending on the requirement and suspend usage when they are not in use.
### 4. Data security
Companies need to ensure that during Snowflake implementation data is safeguarded both at transit or rest. This should be aligned with the overall data lake security strategy that takes into account both current and future threats across layers such as physical infrastructure, access controls, external network interfaces and data storage.
### 5. Data governance and quality
The incredible scalability that Snowflake offers can also hamper the data quality – in excessive amounts and of non-validated low quality. It is critical to ensure that the data going into the pipelines are managed and validated to gain valuable, trustworthy, and actionable insights at the end of the process. Here, enterprises might consider leveraging data validator tools that can not only define data quality rules visually but also enable them to follow up on data issues while providing insights into failure. Maintaining pipeline data quality can reduce process complexity and help counter costs as well.
## Looking to migrate to Snowflake?
[lc_get_post post_type="lc_block" slug="whitepaper-middle-contact-form"]
## 4. The data engineering best practices
To make best use of Snowflake and optimize data, here are seven important considerations:
- Evaluate virtual warehouse options
- Select the right warehouse sizing
- Optimize cost for resources, compute & storage
- Set up robust network security policy
- Split user authentication based on system defined roles
- Develop watertight security & access controls
- Streamline data loading & enhance query performance
Let us take a detailed look at each of the considerations and how the [data engineering best practices](/ebooks-whitepapers/data-engineering-overcome-challenges-in-enterprise-analytics/) can lead to Snowflake success.
### 4.1- Evaluate virtual warehouse options
Virtual warehouses are a cluster of compute resources in Snowflake providing required resources to execute SQL SELECT statements and perform DML operations. It is important to execute relatively homogeneous queries (size, complexity, data sets, etc.) in the same warehouse. The best practices also include experimenting with different warehouse sizes before deciding on the size that suits the requirements and the usage of “auto-suspend” and “auto-resume” features depending on the workloads to save costs. Warehouse suspension and resumption take time, which is noticeable for larger warehouses. It is therefore necessary to keep the warehouse running if the enterprise needs an immediate response.
Some of the other considerations are:
Fig 3 Evaluation criteria for selecting the right type of data warehouse
### 4.2- Select the right warehouse sizing
For the virtual warehouse to function on optimal level and provide desired outcomes, the size of the warehouse must be intelligently ascertained. For that, the warehouse must be aligned with the workload. Here is a look at the various sizes of warehouses:
Table 1: Snowflake warehouse sizing
For warehouse sizing success, here are the three things that enterprises must do:
- Right sizing: Recognizing workload patterns, linear performance improvements, gradually increasing warehouse size
- Scaling up and out: Improving individual query performance and data load performance concurrency, improving session/query concurrency and identifying the right cluster size to avid queuing
- Automating suspend/resume: Offering on-demand, end-user workloads and suspending idle time settings, programmatically suspending/resuming scheduled jobs where process orchestration is controlled
Here is a snapshot of warehouse alignment with workloads:
Table 2: Snowflake warehouse alignment with workloads
### 4.3- Optimize cost for resources, compute & storage
In order to optimize costs, here are the key areas of consideration:
Resources: The resource monitors include aligning with team-by-team warehouse separation for granular cost governance, setting it on account level if team-by-team quotas are not needed, leveraging tiered triggers with escalating actions, and enabling proactive notifications.
Compute: The resource monitors and viewing usage
Storage: There are two types of storage:
- Time-travel storage: The focus areas include dimensional tables, persistent staging areas, materialised relationships, derivations, and other business rules. The action points include detection of high churn, verification of retention period on all large or high churn tables, reduction of retention period if data can be regenerated/reloaded and time/effort to do so is within acceptable boundaries/SLAs, and use of periodic zero copy cloning.
- Failsafe storage: The focus areas include staging tables, intermediate result tables, increased responsibilities for developers, analysts and data scientists, and tool materialised results. The action points include following permanent tables with full CDP() lifecycle, not using failsafe for temporary/transient tables, utilizing temporary tables for session-specific intermediate results in complex data processing workflow, dropping temporary tables as soon as the session ends, utilizing transient tables for staging where the truncate/reload operations occur frequently, and considering designation databases/schemas as transient to simplify table creation.
---
## Navigation
- [Company](/about-sigmoid)
- [Newsroom](/newsroom)
- [Life at Sigmoid](/careers)
- [Takshashila](/takshashila)
- [Contact Us](/contact-us)
- [AI Strategy Blueprint your AI advantage](/enterprise-ai-strategy/)
- [Generative AI Drive innovation with Generative AI](/generative-ai/)
- [Responsible AI Build trust with ethical AI practices](/responsible-ai-in-enterprise/)
- [Agentic AI Reshape business with scalable agentic systems](/agentic-ai-solutions/)
- [AI Managed Services Ensure reliable AI performance](/ai-managed-services/)
- [Advanced Analytics Transform your business with data-driven insights](/advanced-data-analytics-solutions/)
- [Start Assessment](/agentic-ai-readiness-index/)
- [Data Strategy Strong data foundations for scalable AI](/data-analytics-strategy/)
- [Data Management Leverage data as a strategic asset](/ai-data-management-services/)
- [Data Ops Automate data for speed and quality](/data-devops/)
- [Data Engineering Deliver insights faster with scalable pipelines](/data-engineering/)
- [Cloud Transformation Modernize data to maximise efficiency](/cloud-migration/)
- [Download Whitepaper](/ebooks-whitepapers/building-data-products-in-a-data-mesh-to-drive-business-value/)
- [Data Modeling Structure data for better decisions](/data-modeling-services/)
- [Data Visualization Transform data into actionable stories](/data-visualization-service/)
- [BI Migration Enhance decision making with modern BI tools](/bi-migration/)
- [Data Observability Build trust with healthy, accurate data](/data-observability/)
- [Automated Insights Make smarter decisions with auto-generated insights](/automated-insights/)
- [Download Whitepaper](/ebooks-whitepapers/power-bi-hacks/)
- [CPG & Retail End-to-end analytics for planning, operations, and commercial excellence](/industries/cpg-analytics/)
- [Life Sciences Trusted intelligence across clinical, commercial, and operational workflows](/industries/life-sciences/)
- [Financial Services AI-powered analytics for risk, compliance and customer experience](/industries/banking-financial-analytics-services/)
- [Read case study](/case-studies/data-clean-room-enables-real-time-insights-to-improve-operational-efficiency/)
- [MediaIQ Advanced platform for in-flight marketing measurement](/accelerators/sigmoid-mediaiq-multi-touch-attribution-tool/)
- [CampaignIQ AI-driven platform for optimized campaign budget allocation](/accelerators/sigmoid-campaigniq/)
- [AssistBot GenAI email assistant that automates human-like responses](/accelerators/sigmoid-assistbot-for-ai-email-assistant/)
- [CreativeBot GenAI tool for personalized and brand-aligned creative design](/accelerators/sigmoid-creativebot/)
- [SocialBot GenAI platform to analyze digital conversations and trends](/accelerators/#marketing|socialbot)
- [DemandIQ Predict trends accurately and optimize inventory management](/accelerators/sigmoid-demandiq/)
- [NetworkIQ Track and optimize logistics operations in real-time to quickly address disruptions](/accelerators/sigmoid-networkiq/)
- [SupplyIQ End-to-end platform to optimize supply chain operations](/accelerators/sigmoid-supplyiq/)
- [ProcurementIQ Automated procurement operations for maximum savings, compliance and efficiency](/accelerators/sigmoid-procurementiq/)
- [RapidML Accelerated deployment for machine learning models](/accelerators/sigmoid-rapidml/)
- [DataGuard Comprehensive platform for proactive data quality management](/accelerators/data-quality-tool-sigmoid-dataguard/)
- [CloudPulse Cloud cost optimization platform with multi-cloud management](/accelerators/sigmoid-cloudpulse/)
- [RAPID GenAI foundation with built-in governance and cost clarity](/accelerators/sigmoid-rapid/)
- [AnalyticsBot GenAI based platform to streamline decision-making in analytics](/accelerators/sigmoid-analyticsbot/)
- [DataConnect Seamlessly ingest, integrate and harmonize data from diverse sources](/accelerators/sigmoid-dataconnect/)
- [Reconica AI-powered data harmonization and reconciliation engine](/accelerators/sigmoid-reconica/)
- [ConverseBot GenAI driven insights generation for automated insights from reports](/accelerators/#sales|conversebot)
- [iNRM Cross-lever revenue growth optimization platform](/accelerators/sigmoid-inrm/)
- [AssortmentIQ Optimize shelf layouts and assortment mix at scale with AI-based insights](/accelerators/sigmoid-assortmentiq/)
- [Read Whitepaper](/ebooks-whitepapers/building-agentic-ai-chatbots-for-business-process-transformation/)
- [Listen Podcast](/events/podcast/how-jack-in-the-box-is-redefining-personalization-and-supply-chain-with-ai/)
- [Blogs](/blogs/)
- [White Papers](/ebooks-whitepapers/)
- [Case Studies](/case-studies/)
- [Podcast](/events/podcast/#Podcasts)
- [Read Blog](/blogs/the-genai-adoption-triad-responsibility-ethics-and-explainability/)
- [ConverseBot](/accelerators/#sales|conversebot/)
---
## Footer Links
- [Talk to our AI experts](/contact-us/)
- [AI Strategy](/enterprise-ai-strategy/)
- [Agentic AI](/agentic-ai-solutions/)
- [Generative AI](/generative-ai/)
- [AI Managed Services](/ai-managed-services/)
- [Responsible AI](/responsible-ai-in-enterprise/)
- [Advanced Analytics](/advanced-data-analytics-solutions/)
- [Data Strategy](/data-analytics-strategy//)
- [Data Engineering](/data-engineering/)
- [Data Management](/ai-data-management-services/)
- [Cloud Transformation](/cloud-transformation/)
- [Data Ops](/data-devops/)
- [Data Visualization](/data-visualization-service/)
- [Automated Insights](/automated-insights/)
- [BI Migration](/bi-migration/)
- [Data Modeling](/data-modeling-services/)
- [Data Observability](/data-observability/)
- [CPG & Retail](/industries/cpg-analytics/)
- [Financial Services](/industries/banking-financial-analytics-services/)
- [Life Sciences](/industries/life-sciences/)
- [Case Studies](/case-studies/)
- [Thought Leadership](/ebooks-whitepapers/)
- [Blogs](/blogs/)
- [Company](/about-sigmoid/)
- [Newsroom](/newsroom/)
- [Accelerators](/accelerators/)
- [Careers](/careers/)
- [Privacy Policy |](/privacy-policy/)
- [Cookie Policy](/cookie-policy/)