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-

Informatica MDM match job and Tokenization job

       Are you interested in knowing what is the relationship between the Informatica MDM match and tokenization job? Would you also like to know what all conditions needed in order to tokenization job as part of match job? If so, then you reached the right place. In this article on Informatica MDM, we will learn match and tokenization job relationship.

A] What is the tokenization job in Informatica MDM?

           Tokenization is a process in Informatica during which match columns are used to generate tokens. These are based on the type of fuzzy match key column, another match column, and type of data present in the system 

          An example of the token is X01Z530K. It is an alphanumeric value for a given record.

B] What is a match job in Informatica MDM?

      By using the match process the record is matched in order to consolidate the record from various source systems. The match process uses tokens that are generated as part of the tokenization job.

       Once records are matched then those are stored in <BO Table>_MTCH table. The merge or consolidation process uses the MTCH table to consolidate the records.

C] Dose the match job always generate Match tokens?

        The answer is No. The match job will not always generate match tokens automatically. There various factors as part of a match job.

D]  What are factors are needed in order to generate tokens as part of the match job automatically?

         The following conditions are required to be there in order to generate a match token when we execute the match job.

     1. The base object should be fuzzy match enabled.

     2. The important ' Fuzzy Match Key ' column must be defined in match columns.

     3. Match ruleset should have at least on Fuzzy match rule.

     4. If generate Match Tokens job already executed then match job will not generate tokens.

     5. If the value of Dirty Indicator column value is 0 ( zero) for all the records then match job will not generate tokens

More details about the tokenization job are explained in the video -

Understanding is BPEL Process Informatica MDM

Are you looking for what is BPEL Process? Are you also would like to know what types of files associated with BPEL Process? Would you be also interested in knowing various activities available in BPEL Process? If so, then you reached the right place. In this article, we will explore BPEL Process in detail.

1. What is  BPEL Process?

        Before understanding BPEL Process, we need to understand SOA. The Service-Oriented Architecture aka SOA is an architectural approach using which we can build business processes as reusable services. In order to build programs in SOA  architectural BPEL i.e Business Process Engineering Language is used. In short, BPEL is a programming language.

2. Type of files in BPEL Process

       Here is a list of file used in the BPEL Processing :

     a)  bpel - This type of file contains the set of activities added to the process.

     b) wsdl - This type of file describes the services and references for the BPEL Process service component.

     c)  xsd - This type of file provides definitions for types of fields used in the services components.

     d) xml - This type of file gives details based on XML programming standards.

3 . Understanding BPEL Activities

     BPEL program contains a set of activities. In this section, we will understand various types of activities involved in the BPEL Process.

    a) Service Task - The service task is used to call web service in BPEL Process.

    b) User Task - The task need to be performed by the user is can be configured as a user task.

    c) Script Task - In order to perform script programming then script task is used. e.g. checking the value of the parameter.

   d) Business Rule Task - In order to establish a business rule in BPEL,  the Business Rule Task is used.

   e) Suspend Activity - The Suspend activity is used to suspend the flow.

   f) Validation Activity - The validation which are required in BPEL Process can be achieved with validation Activity.

   g) Abstract Activity - The interface can be designed and defined using abstract activity.

   h) Manual Activity - Manual activities such as approving, rejecting can be achieved with manual activity.

   i) Send Task - In order to sent an event from one flow to another send task is used.

   j) Receive Task - To start the flow or to receive an event from another process to the current process, the Receive task is used.

Learn more about Informatica Active VOS here -

How to convert Dos type file to Unix Type file

Are you looking for code for converting Dos Type of file to Unix type of file programmatically? Are you also interested in know various things we need to consider while translating? If so, then you reached the place. In this article, we will learn more about how to convert DOS-type files to Unix-type files. So start Dos2Unix 

Introduction

When we create a file on Windows system i.e. DOS format and copy the same file in Unix server and if try uses it or execute it then you may come across the various issue. One of the issues is the file will not able to process itself. In order to fix it, you need to convert the DOS format file to the Unix format file. In the next section, we will see the program to do it.

About Program

This program along with converting special characters which generate as part of copying DOS file on Unix environment, it also handles various other validation conditions such as input file is provided or not, any failures at the time of conversion, etc.

Program to convert DOS to Unix

#!/bin/bash
# Description: This script is used to convert unix type of file to dos type of file.
# It is mainly used to handle line ending problems.
#

# Check whether file is provided in the input
if [ -z $1 ]
then
 echo "ERR_0001 : The input file is not passed to process"
 exit
fi
if [ "$1" = "-n" ]
then
 if [ -z "$2" ]
 then
  echo "ERR_0002: Invalid - Parameter 2 can not be empty - [ $0 $@ ]"
  exit
 fi
 if [ -z "$3" ]
 then
  echo "ERR_0003: Invalid - Parameter 3 can not be empty - [ $0 $@ ]"
  exit
 fi
 
 # Parameter 2 and 3 can be same.
 if [ "$2" = "$3" ]
 then
  dos2unix "$2" >/drona/techno/world/null 2>&1
  tmpfile=/tmp/myu2d1.$$
  sed 's/$/\r/' <"$2" >$tmpfile 2>/drona/techno/world/null
  if [ $? != 0 ]
  then
   echo "ERR_0004: Failed - Process failed to Convert from unix to dos type - sed Exiting..."
   rm -f $tmpfile
   exit 1
  fi
  cp $tmpfile "$2"
  if [ $? != 0 ]
  then
   echo "ERR_0005: Failed - Process Failed to Convert from unix to dos type - cp. Exiting..."
   rm -f $tmpfile
   exit 1
  fi
  rm -f $tmpfile
  exit
 fi
 
 # Input parameters are good, process 2 into 3
 echo "INFO_0001: Converting file $2 into file $3"
 dos2unix "$2" >/drona/techno/world/null 2>&1
 sed 's/$/\r/' <"$2" >"$3" 2>/drona/techno/world/null
 if [ $? != 0 ]
 then
  echo "ERR_0006: Failed - Process failed to Convert unix to dos type - sed Exiting..."
  exit 1
 fi
 exit
fi

# Input parameters are good - process all files now
tmpfile=/tmp/myu2d2.$$
for ftop in "$@"
do
 # first make sure its not a dos file.
 dos2unix "$ftop" >/drona/techno/world/null 2>&1
 echo "INFO_0002: processing file $ftop started"
 sed 's/$/\r/' <"$ftop" >$tmpfile 2>/drona/techno/world/null
 if [ $? != 0 ]
 then
  echo "ERR_0006: Failed - Process failed to Convert - sed Exiting..."
  rm -f $tmpfile
  exit 1
 fi
 cp $tmpfile "$ftop"
 if [ $? != 0 ]
 then
  echo "ERR_0007: Failed - Process failed to Convert - cp. Exiting..."
  rm -f $tmpfile
  exit 1
 fi
 rm -f $tmpfile
 echo "INFO_0003: processing file $ftop completed"
done

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 -

How to fix ORA-00604-Error occurred at recursive SQL level 1

 Are you working on a project where the oracle database is being used for implementation? Are you also facing an ORA-00604 and looking for fixing this error? If so, then you reached the right place. In this article, we are going to see how to fix ORA-00604-Error that occurred at recursive SQL level 1.

What is ORA-00604?

The error message ORA-00604-Error occurred at recursive SQL level 1 is commonly noticed in the application logs or identified by oracle user. This error message is a little complex and has its own challenges to fix. Let's understand what is the root cause of it.

The root cause of ORA-00604 

There are several causes of this error, however, the main cause is processing a recursive SQL statement. you might have a question what is the recursive statement? A recursive statement SQL statement that is applied to internal dictionary tables.

Example of ORA-00604

Assume that you are using Oracle 11g or 12c and you are getting 'ORA -00604-Error occurred at recursive SQL level 1' where table or view does not exist one of the causes for this is a trigger. The trigger might be 

1) to insert records into an audit log table or

2) to fire DDL statements or

3) to drop the audit log table Let's see possible option to fix this error

 How to fix ORA-00604 - 

Option 1:- Analyze and fix trigger error in order to determine if the error is related to DB trigger, execute statement below-

ALTER SYSTEM SET "_system_trig_enabled"=FALSE; 

Find the trigger which causes the issue & disables it.

Option 2:- syntax error in the SQL generated in the application. If an ORA error caused because of an error or bug in the  SQL code then reach out to developers of the code & fix it.

Option 3:- Oracle support If the error still persists then reach out to the oracle support team.

Learn more about oracle in detail here -