What is Virtual Private Snowflake ?

                 Are you looking for details of Virtual Private Snowflake or VPS? Are you also interested in knowing how to use an account locator for a VPS account? If so, then you reached the right place. In this article, we will learn about Virtual Private Snowflake.

A) What is Virtual Private Snowflake?

                  Snowflake comes in various editions such as Standard Edition, Enterprise Edition, Business Critical Edition etc. Virtual Private Snowflake or VPS is another snowflake edition.

                  Virtual Private Snowflake provides the highest level of securities for organizations that have strict security requirements e.g Financial Institutions. It helps great help while dealing with sensitive data for analyzing and sharing such sensitive data.

                  Virtual Private Snowflake (VPS) comes with all the services and features of Business Critical Edition. However, all these Functionalities come in a completely separate environment i.e Isolated from all other snowflake accounts. In other words, the VPS accounts do not share resources with accounts outside VPS.

B) Account Identifier for VPS account 

                 The account identifier for the VPS account is different than other snowflake editions. As we know the other snowflake editions have specific formats for Account Identifier, you can click here for more details. As VPS uses a different structure for hostnames and URLs hence the format for VPS account identifier is also different. In order to get a specific format for the VPS account, we need to reach out to the snowflake support team.

                 However, we have another alternative option that we can use for VPS account Identifier and that format is 

                     <organization Name > _ <account Name>

C) Does VPS support secure Data sharing?

                 The answer is NO . the VPS or Virtual Private Snowflake currently does not support secure data sharing. On another hand, Enterprise and standard edition support secure Data sharing.

D) What are the important features of VPS?

                 Along with features of Business Critical Edition, the edition provides the following features 

                1) Use of  Tri -secret service for customer-managed encryption keys.

               2) Support for private connectivity to snowflake services using Azure Private Link, AWS                           Private Link, Google Cloud  Private service connect. 

               3) Dedicated metadata store and pool for compute resources 

               4) Support for FedRAMP for US government regions.

              Learn more about snowflake here -

Snowflake interview questions and answers - Part III

                       Are you preparing for your snowflake interview? Are you looking for Snowflake interview questions and answers? If so, then you reached the right place. In this article, we will focus on snowflake caching interview questions and answers. You can visit the previous article on Snowflake interview questions - Part II here.

                                                                                                            

Q.1  Explain caching in Snowflake ? or How caching works in Snowflake?

                        Snowflake provides caching at two levels - one at the cloud services layer and the second one at compute level. When we execute SQL query against Snowflake. the result from the cloud services layer will be fetched. If cloud services layer cache is disabled then compute layer level cache will be used.

                        The important thing to note here is that cache scenarios work only when the time of execution falls within AUTO_SUSPEND specified time.

Q.2  How does cache work if the underlying table gets updated?

                         As we know when we submit a SQL query to a virtual warehouse in Snowflake, it gets executed against database storage and results are returned back to the cloud services layer. Apart from it, the data gets cached at compute layer and cloud services layer when we execute the same query again the result will be returned using cache.

                        Now let's assume that we either updated the underlying table by deleting a record or updating a record. After updating the table, if we run the same query again the cache will not be used. Instead, the virtual warehouse will be connected to database storage to fetch the data.

Q.3  Is it a good idea to run a select query after updating the table and after deleting the record separately?

                        When we update the table by deleting or updating a record then such change is made in the storage layer. that means, if we execute a query against Snowflake, it needs to connect the storage layer to fetch the latest data. Whenever we connect database storage through compute layer it incurs expenses. so it makes sense to perform all your DML operations such as delete, update as a single unit of work, and then query the results instead of making separate select query calls. This will be helpful to achieve cost-effectiveness.

Q. 4  Does the user's cache share across multiple users?

                         Assume user 1 executes a SQL query against snowflake using virtual warehouse VM1 and after successful execution, if the same SQL query is executed by user 2 with vm2, in such case will cache be used for user2 query execution? the answer is YES As long users use the same or different virtual warehouse and time of execution are under Auto_Suspend timeframe then cache from either result cache or local disk cache will be used.

Q. 5  How long the query results will be cached? 

                         The query results are retained for 24 hrs from last the time of execution. Result cache layer holds queries for 24 hrs. 

Q.6  How much snowflake charges for storing cache? 

                         Snowflake does not charge for storing cache.

Q. 7  Explain more about Remote disk?

                         The remote disk is nothing but database storage which can be achieved using cloud providers like AWS S3. The remote disk is nothing but the blob storage area

Q. 8  How to disable local disk cache 

                     We cannot disable the local disk cache.

Q. 9  How to disable Result cache 

                      In order to disable Result cache use statement below -

                    ALTER SESSION SET USE_CACHED_RESULT=FALSE ;

Q. 10 Can we use the Result cache even if we suspend the warehouse?

                        The answer is YES. We can use the Result cache when the virtual warehouse is in a suspended or Inactive state.

                     Learn more about snowflakes here...

What is difference between Star and Snowflake schema ?

                    Are you looking for the details about star schema and snowflake schema? If so, then you reached the right place. In this article, we will see details about it along with the differences between the Star & Snowflake schema.

A) What is star schema? 

                    The star schema is the simplest schema used to develop dimensional data marts and the data warehouse star schema consists of one or more fact tables referencing the multiple numbers of dimension tables.

                    The star schema separates business process data into facts and dimensions facts holds measurable, quantitative data on the other hand dimensions provides descriptive attribute related to fact data.

                   1) Benefits:- The star schema is denormalized and the benefits of star schema are the queries are simple, business logic reporting is simplified, better query performance,                                         improved performance for aggregation operation.

                   2) Disadvantages:- star schema is not flexible for complex analytical needs. It does not support many to many relationships. The data integrity is not well enforced due to the denormalized state.

B) Snowflake schema 

                   The Snowflake schema is a logical arrangement of tables in a multidimensional database with entity relationships that resemble a snowflake shape.

                  Snowflake schema has centralized fact tables and those are connected to multiple dimensions.

                  The snowflake schema is similar to the star schema but in the Snowflake schema dimensions are normalized into multiple related tables.

                 1) Benefits:- Below are the benefits of snowflake schema Better storage savings due to normalization. optimization of some OLAP database models with snowflake schema.

                 2) Disadvantages:- Complexity in SQL query due to normalization. Data loads into the snowflake schema must be highly controlled and managed.

C) Difference between snowflake and star schema 

                   The snowflake and star schema are similar in nature. However, the Snowflake schema is normalized for some dimensions. On other hand, the logical dimensions are denormalized in the star schema.

                             Learn more about snowflake here 

What is Cold and Hot virtual warehouses ?

                            The virtual warehouse in snowflake goes through various states. The states are cold warehouse, warm warehouse, and Hot warehouse. In this article, we will learn about what does each state of virtual warehouses in snowflake. We will also see what impact of states of virtual warehouses on query performance.

A) What is a virtual warehouse in snowflake?

                            The virtual warehouse in snowflake is a cluster of computing resources that are used to perform activities such as DML operations and SQL execution. The compute resources consist of temporary storage, memory, and CPU. The majority of expenses occur due to the use of a virtual warehouse or compute layer.

B) What are the states of the virtual warehouse in snowflake?

                             The virtual warehouses go through various states and these are -

                            a) Cold virtual warehouse 

                            b) Warm virtual warehouse 

                            c) Hot virtual warehouse 

                      In the next section, we will more about all these states of the virtual warehouse.

C) Cold virtual warehouse :

                             Starting a new virtual warehouse without using local disk cashing and executing the query is called a cold virtual warehouse. When our virtual warehouse is suspended or not active and if we run the query then it will start a new instance of the virtual warehouse.

D) Warm virtual warehouse : 

                             It is the state of the virtual warehouse during which the virtual warehouse is active and running for a while and processed queries. Assume that, the virtual warehouse is in a warm state and we disable the result cache and execute the query then it will use local disk caching. This caching is called warm caching.

E) Hot virtual warehouse :

                            The hot virtual warehouse is a state of virtual warehouse during which the virtual warehouse is active and running for a while and processed queries. However, in this case, the result cache is on. If we execute the query in this state, the query result is returned from the result cache. This is the most efficient operation.

F) Impact of virtual warehouse state on query performance 

                   1) Cold warehouse 

                                The query takes longer than a warm and hot virtual warehouse. It uses a remote disk. Local disk cache is not used. Result cache is not used.

                   2) Warm warehouse 

                               The query takes lesser than a cold warehouse but more than a hot virtual warehouse. It does not use a remote disk, however, it uses a local disk. It does not use result cache.

                  3) Hot virtual warehouse 

                               The query takes lesser time for execution than a cold virtual warehouse and a warm virtual warehouse. It does not use both remote disk and local disk cache. The result is returned using the result cache. from the cloud services layer. It is the most efficient way of getting the result of the query.

                         Learn more about snowflake here -

Snowflake Interview Questions and Answers - part II

                      This is the second part of the series of snowflake interview questions and answers. For the first part i.e Snowflake interview questions and answers - part I click here .

Q1. What are the types of data warehouses? What type of snowflake data warehouse is it? 

                       Before understanding the types of data warehouses, we need to know what is a data warehouse? A data warehouse is a central data repository used for data analyses and reporting .

                       Following are the types of data warehouses

                  a) Enterprise Data Warehouse (EDW) which is a centralized warehouse used for decision making across enterprises. EDW is used for tactical and strategic decision purposes.

                 b) Operational Data Store (ODS) which is a centralized database that is the complementary element to EDW and often acts as a source to EDW, ODS gets refreshed in real-time and used for operational reporting and decision making.

                 c) Data Mart is a subset of a data warehouse and is normally used by a specific team or business line.

                    Snowflake is an analytic data warehouse i.e can be used as Enterprise Data Warehouse and it is implemented as a software As A service i.e SaaS service.

Q2. Is it possible to use data from the local system to load in Snowflake?

                   No, we can not load from the local system we need to use Amazon S3 bucket or Microsoft Azure BLOB, or Google cloud storage.

Q3. What are the important features of Snowflake? 

                  The listed below are the features of the snowflake -

                1. Database and object closing

                2. External Table 

                3. Geospatial data support 

                4. XML support

               5. Cashing

               6. Search optimization services

               7. Integration with Hive meta store 

               8. Data protection and security 

               9. Time Travel

             10. Data sharing

Q4. Can we use an external database such as Oracle or  DB2 for Snowflake storage layer ?

                     No, we can not use an external database for snowflake storage layer snowflake comes with an inbuilt database which is built on SQL database. It is a columnar stored relational database. The snowflake database works well with Tableau, Extel, and many other tools. Snowflake database provides all the services which come with SQL database such as role-based security, query tool, multi-statement transactions, etc.

Q5. What are the cashing areas in snowflake architecture? 

                     The data fetched from the storage layer is cashed at two locations  1) Compute layer 2) Cloud services layer 

                     If cloud services layer cashing is disabled then compute layer cashing is used

                  

               Learn more about snowflake here 

     

       

                               

Snowflake Interview Questions and Answers - Part I

                 This is the first article on a series of Interview Questions and their answers on Snowflake. Through these questions and answers we will learn more about Snowflake so let's start.

Q 1 . What is the architecture of Snowflake?

                  Snowflake architecture is a hybrid architecture of shard-disk and shared-nothing database architectures. As like shared-nothing architecture, snowflake processes queries using massively processing compute clusters where each node store some portion of data locally. on other hand, as like shard-disk architecture, snowflake uses a central repository for data & it is accessible from compute nodes.

                 Snowflake architecture has three layers i.e database storage to store data, Query processing or compute layer for processing queries and the third layer is cloud services which provide services such as security, metadata, and optimizer.

Q2. What are cloud platforms are supported by Snowflake?

                  Snowflake supports the following cloud platforms -

                 1. Amazon Web Services (AWS)

                 2. Google cloud platform (GCP)

                 3. Microsoft Azure ( Azure)

Q3. Do you consider snowflake as an ETL tool? 

                Yes, snowflake can be considered an ETL tool because it performs extract, transform, and loads operations like other ETL tools.

                a) Extract Process: With help of this process, snowflake extracts data from the source and creates data files. these data files support various data formats like CSV, XML, JSON, etc.

                b) Transform Process: We can write a custom transform process in snowflake to pull data from the source and apply some transformations for cleansing and standardization and then load the data.

               c) Load Process: With the load process, we can load data to the internal or external stage. we can use Microsoft  Azure Blob, Amazon S3 bucket or snowflake managed location for staging data. The data is copied to snowflake storage using the COPY INTO command.

Q4. What are the different Editions of Snowflake?

               Here is list of snowflake Editions 

             a) Standard Edition 

             b) Enterprise Edition 

             c) Business -critical Edition

             d) Virtual Private snowflake 

Q5. What kind of SQL does snowflake use?  

             Snowflake uses ANSI SQL which is a common standard version of SQL.

     

         

      

            

        Learn more about snowflake here 

What are the phases in BES External call in Informatica MDM ?

                 Are you looking for the details about various phases in BES External calls? Are you also interested in knowing what are categories under which these phases come? If so, then you reached the right place. In this article, we will learn about BES  External calls and their phases.

A) What BES External calls?

                BES External calls are the web service configured in the provisioning tool in order to achieve customization in Entity 360 or Customer 360 application. we need to develop a custom web service and deploy it on the application server. Once it is deployed, we can register the endpoint URL in the provisioning tool in Informatica MDM.

B) Categories of phases of BES External calls? 

                 The phases of BES External calls are categorized as -

                      a) Read calls

                     b) Write calls 

                     c) Merge calls  

               a)  Read calls: This category is used for read and search operations.

              b)  Write calls: This category uses write co for insert, update and delete operations.

              c)  Merge calls: This category uses preview merge co which is designed for merge-operations.

C)  Phases of External calls 

                   Let's understand detailed phases for the above categories 

              i) Read call phases : 

                        The phases for read calls are

                      * Read Co.Before Everything 

                      * Read Co.After Everything 

                      * Read view.Before Everything

                      * Read view.After Everything

             ii) Write call Phases

                      * Write Co.Before Everything

                      * Write Co.Before Validate

                      * Write Co. After Validate

                      * Write Co.After Everything  

                      * Writeview. Before Everything 

                      * Writeview. Before Vadidate

                      * Writeview. After Vadidate

                      * Writeview. After Everything

               iii) Merge call phases

                      * Perview Merge Co. Before Everything

                      * Preview Merge Co.After Everything

                      * Merge Co. Before Everything

                      * Merge Co. After Everything

                 Learn more about Informatica here