Things to consider while configuring snowflake account - part 2

In the previous article on Things to consider while configuring Snowflake Account - part I, we understood two important factors i.e Data retention and timezone. In this article, we will see security, cost savings, and connection performance. Let's start.

A] Security -

        The security requirement for Snowflake has two parts.

a) Storage integration

        We should not create external stages without storage integration. A Storage integration is secure means of creating connectivity between snowflake and cloud. storage provider. We need to set the below properties to `true ' so that we can prevent the exposure of access tokens or secret keys to snowflake users, the properties are 

•  REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION 
•  REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION

b) Network Policies.

        It is important to prevent access from unwanted networks to the snowflake account. The network policies help to define a list of valid network locations for user connection.

     We can configure account level and user-level network policies.

        NETWORK_POLICY is a parameter associated with network policies.

B] Connection Performance

      In order achieve better connection performance we need to set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX to value `TRUE'. This helps to reduce the amount of information used for JDBC and ODBC connections.

C] Cost Savings.

      Cost  of Snowflake implementation is dependent upon two factors 1) Users  2) Warehouses

1) User: In the snowflake account there are system users and human users. Set different configurations for each of these. Below are helpful parameters for users - 

•  LOCK_TIMEOUT
•  STATEMENT_TIMEOUT_IN_SECONDS
•  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS

2) Warehouse: The optimum use of Snowflake warehouses can be achieved by resource monitoring, setting the appropriate warehouse size, and setting proper cluster count

      The mentioned below are parameters of Warehouse

• RESOURCE MONITOR
• AUTO_SUSPEND 
• WAREHOUSE_ SIZE 
•  MIN_CLUSTER_COUNT

 Learn more about snowflake here-

Things to consider while configuring snowflake account - Part I

Are you planning to use snowflake in your project? Are you interested in knowing all things you need to consider while configuring a snowflake account? If so, then you reached the right place. In this article, we will focus on five important factors for snowflake Configuration. This is part I of the article.

Introduction:-

With the snowflake chargeback model, you can quickly shorten your project but we have to consider several factors before starting configuring and using the snowflake account. Let's understand these factors one by one 

Factors to consider:-

These five important factors that need to consider for the snowflake account and are

1. Data Retention

2. Timezone

3. Security

4. Connection Performance

5. Cost Savings

          In this article, we will focus on Data Retention and Timezone. We will learn more about the remaining factors in Things to consider while configuring snowflake account-part 2

A] Data Retention 

     What is data retention? It is the time for which snowflake will retain a historical view of the data.

a) Important Points -

          • For cost-saving, we can set value for each  database. for non-production data set it to fewer days

          • For development environment one day is adequate 

          • The default data retention time is one day

          • For enterprise production environment we can set it to go days so that we can traverse data for a longer duration

b) Parameter

        DATA - RETENTION_TIME_IN_DAYS

B] Timezone 

         The time-related values are presented with the timezone in the Snowflake configuration.

a) Important Points -

         •- The default timezone is Los Angeles.

         •- We can change timezone based on our needs

         •- As normal practice companies set the value to if companies presence is across the globe.

         •- Set timezone same at on account level and at the user level.

         •-  Provide time-related values with timezone to consuming systems.

 

How to define chargeback model for snowflake ?

 Are you planning to implement snowflake and interested in knowing about how to plan your snowflake expenses ? Are you also would like to know what are the expenses occurs for snowflake implementation? If so,then you reached right place. In this article we will see what is snowflake chargeback model, what are the snowflake components where expenses occurs and how to plan those.so Let's start 

What is snowflake chargeback model?

snowflake operates with a flexible pay-as-you-go model. For traditional data solution we have initial fixed cost and later adds with service and maintains cost . Here snowflake gives flexibility to increase or decrease the cost based on usage. what we have to do is , just create an account and start using it . However , if we do not plan properly the snowflake bill will go high.

Factors to consider about pay 

a) How to Pay : We  have to decide in advance about paying snowflake credits. We need to decide whether each project will pay for usage or enterprise level payment.

b) Snowflake discount : Snowflake provides discount on the volume of purchased credit so consider making consolidated purchase.

Snowflake component with expenses?

Here is list of Snowflake components which produces expenses -

1. Warehouse

2. Snowflake

3. Materialized views

4. Cloud services

5. Data transfers

6. Storage

How to reduce snowflake expenses?

a) Very first thing to consider for reducing snowflake expenses is minimize the usage across enterprise i.e use only much you need

b) Plan for all potential expenses from each of the snowflake components

c) Track snowflake expenses to determine how much consumption has occurred & how to charge it.

d) Create naming convention for database objects which will help to identify owner and associate budget .

e) Maintain the snowflake expenses tracking

Learn more about snowflake here -

Join Conditions in Snowflake

 Are you looking for an article about the type of Join conditions in Snowflake? Are you also would like to understand these different types of join conditions in detail? If so, then you reached the right place. In this article, we will understand various join conditions used in Snowflake.

Introduction:

As like DBMS, Snowflake supports various types of join conditions. Join condition allow us to combine data from two or more tables. In Snowflake, there is no association between the tables using the primary key and foreign keys but we can still use the join condition.

Type of Join in Snowflake:

• Inner Join
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join

Let's understand each Type of Join with help of the Venn diagram

Understanding Syntax for Join Condition

Using the join condition in the SELECT statement is command practice. After writing the SELECT statement in the FROM clause we need to provide a table name then followed by the JOIN keyword. We also need to provide details on which column we need to perform join by using an ON clause. Here is the syntax for the join condition in Snowflake.

A. Inner Join

The Inner join in Snowflake is used to return rows from both tables which satisfy the condition. 

Example:

SELECT CUST.CUST_NBR

, CUST.FIRST_NAME

, CUST.LAST_NAME

, ADDR.AD_LINE_1

, ADDR.COUNTRY

FROM CUSTOMER CUST

INNER JOIN ADDRESS ADDR

ON CUST.CUST_NBR = ADDR.CUST_NBR.

The above query returns the elements which common between the CUSTOMER and ADDRESS tables.

B. Outer Join

If two tables do not contain all the records then we use an OUTER JOIN. Each table contains records even if the other table does not contain any corresponding record. The outer Join is subcategorized as 

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Let's understand these Outer join one by one.

1. Left Outer Join

The LEFT JOIN in Snowflake returns all the rows from the table on the left side of the equation even if there are no matching rows in the table on the right side of the equation.

Syntax

SELECT CUST.CUST_NBR

, CUST.FIRST_NAME

, CUST.LAST_NAME

, ADDR.AD_LINE_1

, ADDR.COUNTRY

FROM CUSTOMER CUST

LEFT OUTER JOIN ADDRESS ADDR

ON CUST.CUST_NBR = ADDR.CUST_NBR.

2. Right Outer Join

The RIGHT JOIN in Snowflake returns all the rows from the table on the right side of the equation even if there are no matching rows in the table on the left side of the equation

.

Syntax

SELECT CUST.CUST_NBR

, CUST.FIRST_NAME

, CUST.LAST_NAME

, ADDR.AD_LINE_1

, ADDR.COUNTRY

FROM CUSTOMER CUST

RIGHT OUTER JOIN ADDRESS ADDR

ON CUST.CUST_NBR = ADDR.CUST_NBR.

3. Full Outer Join

The FULL OUTER JOIN in Snowflake returns all the rows from both tables irrespective of condition match.

Syntax

SELECT CUST.CUST_NBR

, CUST.FIRST_NAME

, CUST.LAST_NAME

, ADDR.AD_LINE_1

, ADDR.COUNTRY

FROM CUSTOMER CUST

FULL OUTER JOIN ADDRESS ADDR

ON CUST.CUST_NBR = ADDR.CUST_NBR.

Learn more about Snowflake here