How to handle NULL values in Snowflake

                   Are you facing challenges while handling NULL values in snowflake? Are you also interested in knowing what are the things we need to consider while columns in the query contain NULL values? In this article, we will learn details about handling NULL values.

A) What are NULL values?

                   Many times NULL value is referred to as no value. some systems do not have NULL concepts. In technical terms, the NULL value is a reference to an empty area of the memory. some systems handle differently than others. Snowflake has its own way of handling NULL values.

B) What are the rules for handling NULL values in Snowflake?

                  Here is the list of rules  -

             1. Not null to null value comparison :

                   we compare not null value with the null value it returns NULL

                   e.g  'ABC ' =NULL returns NULL

             2. Equality or inequality comparison :

                   If we use inequality comparisons such as less than or greater than it results in a NULL                              value.

                   e.g 'ABC' > NULL returns NULL

             3. Comparision of NULL values :

                   If we compare one NULL value with another NULL value then it results in a NULL value.

                   e,g . NULL= NULL results NULL

             4. Best way of comparing NULL values is 

                     IS NULL or IS NOT NULL

             5. Function to null safe equality 

                  We can use the EQUAL_NULL function to check NULL safe equality.

                  e.g. equal_null ('ABC' , null) will return false.

             6. Aggregate function dismisses NULL values.

                  Assume that one of the fields in the snowflake contains the below values -

                  1,2, NULL , 3, NULL 6, 1

                 If we use aggregate function 4 AVG on this field then it will return the result as                                              3 i.e(1+2+3+6)/4

                 i.e. It ignores NULL values.

             7. Real average value 

                  In case we need a real average in the above example we need to use the SUM function, where the NULL value is 0

                  i.e. SUM(<value>)/count (*)

                 e.g. sum(1+2+0+3+0+6)/6

                  i.e. 12/6 = 2

             8. The empty string in snowflake is NOT NULL

                 e.g. "IS NULL will returns false.

             9. Count (*) returns the total count of rows in the table 

            10. Count (<column_name>) returns the count of rows with not null values only

                                  Learn more about snowflake here -

What are the components of snowflake architecture ?

                Are you looking for an article on snowflake architecture? Are you also looking for the components of snowflake architecture? If so, then you reached the right place. In this article, we will explore database storage, Query processing, and cloud services in detail.

A) What is the Architecture of Snowflake?

               Snowflake architecture is a hybrid of a shared-nothing database and shared disk.

       1. Snowflake uses a central data repository which is similar to shared-disk architecture.

       2. Snowflake processes queries using massively parallel processing compute clusters. In this kind of architecture each node in the cluster, stores a portion of the entire data set. This is similar to shared-nothing architecture.

B) What are the components of Snowflake Architecture?

              The components of snowflake architecture are as below 

        1. Database Storage

        2. Query Processing 

        3. Cloud Services 

                 Let's understand each of these components one by one

1. Cloud  Services 

               It is the topmost layer in snowflake architecture. It consists of a collection of services that coordinates various activities across the Snowflake platform. The cloud services join various components of a snowflake in order to fulfill requests such as login or giving a response back to the user.

               Here is the list of services that are handled in this layer.

         1. Authentication 

         2. Infrastructure Management 

         3. Metadata Management 

         4. Query Parsing 

         5. Query optimization 

         6. Access Control

2. Query Processing 

               In this layer, query execution is handled. It is the most common and widely used component of the snowflake.  The queries are processed using a virtual warehouse. Each virtual warehouse is massively parallel processing compute cluster. It consists of multiple compute nodes provided by snowflake from the cloud provider.

3. Database Storage 

              It is cloud storage where optimized data is stored. What is optimized data? The optimized data is nothing but the data which is reorganized by snowflake into the compressed and columnar format.

             What are aspects handled by snowflake related to data? here is a list which is taken care of by snowflake 

         1. File Size 

         2. Structure of the data 

         3. Compression of the data 

         4. Metadata

         5. Statistics of the data

         6. Organization of the data

 The important thing here is the data stored by snowflake is not visible or accessible directly by customers. It can only be accessed using SQL query operations.

                 Learn more about snowflake here 

What are application of Artificial Intelligence?

           Are you looking for an article that lists currently available applications which use Artificial Intelligence? If so, then you reached the right place. In this article, we will explore the applications which leverage the benefits of Artificial Intelligence.

1) Google

          Google has a predictive search engine that predicts the next word when a user types a keyword to search on the Google page. This recommendation suggested by Google search is one of the best examples of Artificial Intelligence aka AI. It uses predictive analysis to achieve it.

2) JP Morgan chase's contact Intelligence platform

          Artificial intelligence, machine learning, and image recognition is used to implement JP Morgan chase's contact Intelligence platform to analyze legal documents. This system is very efficient compared to the manual review of each and every legal document.

3) IBM Watson 

              It is another Implementation of AI. IBM Watson technology is used by Healthcare organizations for medical diagnosis.

4) Google Eye Doctor

              The condition called diabetic retinopathy which can cause blindness can be diagnosed by using this AI-based technology named Google Eye Doctor

5) Facebook 

              It is one of the social media platforms which uses artificial Intelligence for face verification. Internally it uses machine learning and deep learning to detect facial features and tag friends.

6) Twitter

              Twitter uses Artificial Intelligence to detect hate speeches and terroristic languages in the twits.

7) Siri or Alexa

               these virtual assistance devices use Artificial intelligence for speech recognition.

8)  Tesla 

               Now a day we hear the buzzword autonomous driving or self-driving cars. Tesla is the leader in it Tesla uses computer vision, image recognization, deep learning in order to build smart cars. Which detects obstacles and drive around them without human interaction.

              Learn more about Artificial Intelligence here

Types of Artificial intelligence

         Are you looking for an article on what are types of artificial intelligence? Are you also interested in knowing what are stages of artificial intelligence are? If so then you reached the right place. In this article, we will focus on various types of artificial intelligence.

A) what is Artificial Intelligence or AI?

          The System which is capable of performing tasks that require normally human intelligence e.g decision making, object detection, complex problem solving, etc is called an Artificial Intelligence system, and capability with which it performs is called Artificial Intelligence.

B) Stages of Artificial Intelligence

           The stages of artificial intelligence are 

       1. An Artificial Narrow Intelligence (Weak AI) 

            Artificial narrow Intelligence is also called weak  AI. It is a stage of AI that involves machines that can perform specific tasks.

            e.g Alexa or Siri in iPhone

       2. Artificial General Intelligence

              Artificial General Intelligence is also known as stage AI. In this stage, the machine will possess the ability to think and make decisions.

               There is no implementation of strong AI yet.

        3. Artificial super Intelligence

               It is a stage of AI when the capability of computers will surpass human beings.

               This is still considered a hypothetical situation.

C) Types of Artificial Intelligence

            The types of Artificial Intelligence are as below-

          1. Reactive machine AI :

                   In this, machine operators solely based on the taking current situation and data 

                   e.g IBM chess machine - Deep Blue

            2. Limited memory AI 

                    In this, the machine uses post data and its memory to make informed and improved decisions.

                     e.g self-driving car - Tesla car

               3. Theory of mind AI 

                       In this, the human believes and thoughts can be comprehended by considering emotional intelligence in this type of artificial intelligence.

                 4. Self-aware AI 

                          In this, the machines will have their own consciousness and become self-aware. This type of AI does not exist yet.

           Learn more about Artificial Intelligence and data science here

How to design landing table in the Informatica MDM ?

          Are you planning to implement Informatica MDM in your project and starting designing a landing table? Are you also interested in knowing the types of landing table designs? If so, then you have reached the right place. In this article, we will explore factors that need to consider while designing a landing table in Informatica MDM.

A) what is the landing table in Informatica MDM?

             Are landing tables are the tables where data from the source is loaded in order to process the data and sent through a stage process to cleanse and standardize it. For the stage process, the landing tables act as source and stage tables as targets.

B) Factors to be considered for landing table design

               We need to consider the following factors

While designing landing tables in MDM

             1. How many source systems are involved

             2. What is the volume from each source system

             3. Impact of development timelines

             4. Maintenance requirements

             5. Partition requirements

C) Type of landing table designs

             Based on the Information capture in the previous section, we can design landing tables in two ways

             1. One landing table for each source

             2. One landing table for multiple sources

1. One landing table for each source

             If each source is having different types of data ( e.g.one source is customer-centric and another source is Account centric), if the volume in each source is almost equal, or if we have good development and maintenance bandwidth then we can design one landing table for each source.

2. One landing table for multiple sources

             If multiple sources are having similar attributes and data types and volume in each source system is low and if we need to expertise the development time then we can design one landing table for multiple source systems. 

                                 

Learn more about MDM landing table here

What is difference between TRUNCATE and DELETE

         Are you working on databases or learning the database concepts, and want to know about the basic commands related to table like DELETE, TRUNCATE, then this is the right place. In this article, we are going to learn what is the meaning of these commands and the purpose of using these. TRUNCATE and DELETE command does the same job but slightly in a different manner. In this article, we will see what is the difference between the DELETE and TRUNCATE commands.

TRUNCATE command

- TRUNCATE command is DDL (Data Definition Language) command.

- If you use truncate command, then it will delete the data in a table and not a table itself.

- TRUNCATE locks the table but does not locks the rows, as it removes all the data from the table.

- It removes all the rows of the table, but the structure of the table remains as it is. It does not delete the   

   table structure, columns, indexes and constrains.

- This command does not require where clause.

- After truncate operation rollback process is not possible because this command does not maintain any log file from which we can rollback the data.

                                •  Syntax –

                                TRUNCATE TABLE table_name;

                                 Example –

                                 TRUNCATE TABLE Customer;

DELETE command

-                           DELETE command is DML(Data Manipulation Language) command.

-                          DELETE command deletes all the records from a table.

-                          DELETE locks the rows, for deletion each row in the table is locked.

-                         If we want to delete a specific row/record from a table then we can use the WHERE clause.

-                       The table structure, indexes, attributes will not be deleted after the DELETE operation.

-                        Rollback operation can be possible after DELETE operation but we have to rollback the data before the COMMIT statement. After the COMMIT statement we can not rollback the data.

 

·                                                                   •  Syntax –

                         DELETE FROM table_name;

                                     Example -

                         DELETE FROM Customer;

 

                                    Syntax (Using WHERE clause)-

                        DELETE FROM table_name WHERE condition;

                                    Example-

                                    DELETE FROM Customer WHERE id=1;

 The above delete operation will delete all the records from the ‘Customer’ table without deleting the ‘Customer’ table itself.

Difference between TRUNCATE and DELETE command

-                   DELETE command deletes the specific commands based on conditions defined by the WHERE clause,        but it does not free up the space.

-                 TRUNCATE command does not require a WHERE clause. After executing the TRUNCATE statement it releases all the memory along with the removal of data.

-                DELETE command maintains the log but the TRUNCATE command does not maintain the logs, hence the TRUNCATE command is faster than the DELETE command.

-               TRUNCATE command uses less Transaction space.

 

 

What is data science and use of it ?

           Are you looking for details of science? Are you also interested in knowing what is the use of Data Science in the real world? If so, then you reached the right place. In this article, we will explore Data science and we will also learn about predictive analytics and prescriptive analytics.

A) What is Data Science? 

              Data science is a blend of computer science, Business domain knowledge, and mathematics & statistics. It also includes machine learning, data analytics, and advanced analytics Data science is used to discover various patterns present in the data.

B) What is the difference between data statistics and data science?

                 Data statistics or data analytics includes business administration and exploratory data analysis. On the other hand, data science includes data product engineering, machine learning, advanced algorithms along with exploratory data analysis. In short, data statistics explains what is going on by using data history. However, data science also explains what is going on along with identifying future events by using machine learning.

                Data science uses predictive casual analytics, prescriptive analytics and machine learning.

C) What is predictive causal analytics?

                Predictive causal analytics is used to build a model that can predict the possibilities of a particular event in the future for example if you are a banker and giving a loan to the customer and would like to know the probability of a customer making a loan payment on time. Here, we can develop a model that can perform predictive analytics over a period of time.

D) What is prescriptive analytics?

                Prescriptive analytics is used to build a model that has the intelligence of taking decisions and also the ability to modify itself based on dynamic parameters. For example, Tesla's self-driving car collects data for the history of driving of thousands of miles by a different scenario like signal light, turns etc. By applying intelligence it will enable the car to take decisions like when to take turns etc.

E) Machine learning

                  Machine learning is a method of analyzing is used for making predictions. Machine learning is also used for pattern discovery.

                 Various tools using machine learning to enhance capabilities such as