Top 10 commonly used commands in Snowflake

        Are you looking for details about commonly used commands in Snowflake? Are you also interested in knowing what are DML, DDL snowflake commands? Then you have reached the right place. In this article, we will explore more about snowflake commands.

A) Types of commands in Snowflake

          There are four types of commands present in snowflake and those are 

          1. DDL - Data Definition Language

          2. DML - Data Manipulation Language

          3. DCL - Data Control Language

          4. TCL - Transaction Control Language

         In this article, we will focus on DDL and DML commands use in Snowflake

B) What commonly used DDL commands in Snowflake?

            Here is the list of DDL commands used in snowflake

           1. ALTER

           2. CREATE

           3. DROP 

           4. USE

           5. SHOW

           Let's see each of these commands one by one -

1. AFTER - AFTER command is used to modify metadata of on account level, parameters for session or metadata of the database object

Syntax : 

              AFTER <object_type> <object_name> <actions>

e.g  AFTER SESSION SET < params >

2. CREATE - CREATE command is used to create new object

Syntax : 

              CREATE <object_type> <object_name>

e.g.  CREATE  DATABASE ABC

3. DROP - DROP command is used for removing object from system.

Syntax : DROP <object_type> [IF EXISTS] <identifier>

e.g  DROP USER [IF EXISTS] abc_ user

4. USE - USE command is used to specify role, warehouse database ,or schema for current session

Syntax : USE WAREHOUSE <name>

e.g   USE WAREHOUSE xyz 

5. SHOW - SHOW command is used to provide metadata for the object.

Syntax : SHOW <object_ type_plural> [LIKE '<pattern>']

e.g  SHOW PARAMETERS [LIKE '<pattern>']

C) What are commonly used DML commands in Snowflake?

         Here is the list of DDL commands used in Snowflake

          1. INSERT

          2. MERGE

          3. UPDATE

          4. DELETE

          5. TRUNCATE

             Let's see each of these commands one by one.

1. INSERT - INSERT command is used to insert one or more rows into the table.

       Syntax :

       INSERT INTO <table_name> [< column_name>]

           VALUES (<value>|DEFAULT|NULL,...)

        

       e.g

        INSERT INTO TAB_ABC  (id, name)

         VALUES ( 100 , ' DRONA')

2) MERGE - MERGE command is used to Insert, delete and update values in a table based on values in a subquery or another table.

         Syntax : 

         MERGE INTO <table_name> USING <source> ON <join_exp>L

         e.g

         MERGE INTO TAB_ABC USING TAB_PQR ON TAB_ABC.ID=TAB_PQR.ID WHEN MATCHED Then 

         Update set TAB_ABC .NAME = TAB_PQR.NAME

3. UPDATE: The UPDATE command is used to update rows in the table.

       Syntax : 

              UPDATE <table_name> SET <field>=<value>

       e.g 

            UPDATE TAB_ABC SET NAME = 'XYZ'

4. DELETE : DELETE command is used to delete records from the table.

         Syntax : 

              DELETE FROM <table_name > WHERE <condition>

          e.g 

              DELETE FROM TAB_ABC WHERE NAME='BOB'

5) TRUNCATE: TRUNCATE command is used to remove records from the table including privileges and constraints.

          Syntax :

              TRUNCATE TABLE <table _name>

          e.g 

              TRUNCATE TABLE TAB_ ABC.

Top 10 things you need to know before implementing Informatica MDM?

           Are you planning to implement Informatica Master Data Management aka MDM? Are you not sure what are the things you need to consider before considering MDM solution? If so, then you reached the right place. In this article, we will see the top 10 things which you need to know before implementing Informatica Master Data management in your organization. 

 1.Data Quality Measurement 

            You need to know how you are currently measuring data quality not only in a single project but also across the enterprise. This will give you two benefits one, you will know better options for quality of data measurement, and second, a baseline to measure the quality of data after MDM implementation.

 2.MDM and Data Quality

              Is there a relationship between master Data management and Data Quality? Can MDM help in improving data quality? The answer is Yes. However MDM and Data Quality are two distinct processes in any organization. You need to know what is the relationship between Master Data Management and Data Quality.

 3.Returns of Improved data quality?

               We initiative various projects for improvement in the processes and to achieve better results on Investment. You need to know what is the return you will be getting after improving data quality.

 4.Data Governance 

              Data governance is a crucial part of the business. Are you aware of how is data governance is implemented in your enterprise? You need to have proper data governance to get optimum benefits from MDM implementation.

 5.Data for business strategy 

               It is not new that this Era of data. We are in data 4.0 where the majority of the businesses are data-driven. You need to plan your business strategies based on data that is of great quality and well maintained.

 6.Data enrichment

               Why do you need data enrichment? One may ask this question. The answer is to make better decisions and recommendations we need to take important steps towards data enrichment.

 7.Privacy regulations 

               These rules and regulations we need to follow a business. We need to be fully aware of those rules and regulations and consider those implementing any MDM solutions.

 8.Customer satisfaction 

                Is your customer satisfied with your services? What are your customer's preferences and how are you managing these? How are addressing your customer's concerns and feedback? These are important questions you need to answer so that you improve those with MDM.

 9.Risk measurement and assessment 

                Informatica MDM defiantly plays a vital role in risk assessment and measurement. However, you need to know your current solutions and look for better opportunities to improve those.

 10.Future Perspective

             While implementing Informatica MDM, you need to look for long-term benefits instead of short-term MDM with great benefits for the long run.

Learn more about information MDM here

       

Howto fix - "ORA-00245: control file backup failed" issue

 Are you looking for an article about fixing "ORA-00245: control file backup" error? If so, then you reached the right place. In this article, we will see what is root cause of "ORA-00245: control file backup" error message at the database side and how to fix it?

What is "ORA-00245: control file backup" error ?

This error message occurs when Archive logs backup fails. This is Oracle database level functionality which takes backup of archive logs so that you can restore your device quickly and seamlessly in the event of data loss.

Whar the error messages are associated with "ORA-00245: control file backup"?

Here is the list of error messages associated with "ORA-00245: control file backup" -

• RMAN-03009: Failure of full resync command on default channel
• RMAN-03002: Failure of configuring command
• RMAN-03014: implicit resync of recovery catalog failed
• ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

 

What is the root cause of "ORA-00245: control file backup" error message?

The root cause can be the failure of   SNAPSHOT CONTROLFILE due to local file system configuration e.g. /abc CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/<oracle home>/snapcf_pqr1.f'

However, you need to find out all possible other root causes.

 

How to fix  "ORA-00245: control file backup" error?

In order to fix this error message change SNAPSHOT CONTROLFILE NAME  RAMN parameter to shared storage e.g. +RECO_PROD

Learn more about Oracle here -

 

What is Active VOS Central , Active Console and Active VOS Process designer ?

     Are you looking for details about Active VOS Central, Active VOS Console, and Active VOS designer? Are you also would like to know what is process designer? If so, then you reached the right place. In this article, we explore these tools.

A) Active VOS Central

      Active VOS Central aka process central is used by business users. The business user uses process central to work on tasks such as approving or rejecting the requests. Once tasks are generated by automated business process engines, those will be queued to pool for business users. The request form can be submitted to start an automated business process.

        We can perform the following actions in Active VOS Central -

         a) View Task 

         b) Claim Task 

         c) Approve Or Reject Task

         d) Provide Comments

         e) Attach Files 

         f) Refresh Task

         g) View Task history

         h) Assign Tasks to other users  

B) Active VOS Console

       The Active VOS Console is used by an administrator to monitor & Fix processes related to the task. The administrator can also perform actions such as deployment of workflows, test user connectivity, configure URN mapping, delete or schedule faulted tasks, Fix any active VOS related issues using Active VOS Console.

       The Active VOS Console is a thin client application that can be accessed using the browser.

C) Active VOS Designer

       Active VOS designer is also known as process designer. Process designer is used by developers to create a new business workflow. It can be also used to update or customize existing workflow. Process designer contains drag & drop components for easy development and least programming. However, it extensively uses expression language.

       Active VOS central, Active VOS Console, and Active VOS designer are part of the Informatica Product suit. Learn more about Active VOS - here

How to achieve better stage job performance in Informatica MDM

      Are you working on MDM and want to understand its different fields, then this is the right place in this article we are going to know about what are the product recommendation, thread setting properties, and database recommendations of MDM stage job performance

 A. Thread setting for MDM Stage Job Performance

       In this article, we will see what are the different reasons for issues and their solutions regarding stage job performance.

     1) Post/ Pre Stage UE

          The reason for this issue is it we are going to write the query regarding inserting or updating and record, it will create locks and because of this it slows down the performance to avoid this there is a need to put logger statement in UE  code. If doable rerun the jobs by removing the UE code.

     2) Cleanse Function 

            Each in every record will have to go through a cleansing process so here need to check that if any cleanse function is taking more time to process.

           Depends on which type of cleanse function you are using there is a need to check network latency also we need to check of IDQ  end.

     3) Directory 

            The main reason for the directory issue is having a shared directory within 2 different instances of the process server.

           To avoid file locks each process should have its own directory.

     4) Tables  

            The system will reduce the performance if the tables like RAW, REJ, C_REPOS_JOB_CONTROL table contain a huge amount of data.

           We can keep the important data and archive old data. 

   5) Log level

           We will have to keep the logging level to INFO.

B. Thread setting for MDM Stage Job Performance

          The configuration that can be updated :

       1) Threads for cleanse operation (HUB Console)

          Max value = ( Number of CPU cores -1 )

        2) In cmxcleanse properties

            (cmx.server.cleanse.min_size_for_distribution)

            Default size is 1000

        3) com.informatica.mdm.batchcontroller.Batchjob.min_rec_for_multithreading

            The default size is 1000. We can decrease the size if multithreading is enabled and no. of records are lesser than 1000.

C. DB Recommendations for MDM Stage Job Performance.

        1) collect AWR reports.

        2) For the DB performance, collect TESTIO results

D. Appserver Recommendation for MDM Stage Job Performance

        1) when we run the processing server and DB server jobs, check that the CPU is going high.

        2) Have to check basic java arguments such as - xmx value.

           

 

What is Business Entity Services in Informatica MDM?

    Are you looking for details about the Business Entity Services aka BES in the Informatica MDM? Are you also interested in knowing how to use Business Entity Services in your project? If so, then you reached the right place. In this article, we will explore more about Business Entity Services. Let's start.

A) What are Business Entity Services in MDM?

         Informatica MDM is used to master the data from various source systems. We onboard the data from sources to the MDM landing table. The data is moved to the staging table from the MDM landing table and then to the base object tables by executing stage and load jobs. Once we consolidate the data in the MDM, it will be ready to consume by consuming systems. Consuming can consume a golden copy of the records by consuming ETL or JMS Queue or Business Entity Services. Business Entity Services are web services that can be used for real-time integration of external systems with Informatica MDM.

B) What are operations can be performed using Business Entity Services?

      The various operations such as Read, Write, the search can be performed using Informatica BES.

      1) Read BE Business Entity Service-

          It is used to read the data from the base object. the result does not include soft-deleted records.

      2) Write BE Business Entity Service- 

            It is used to create, update and delete parent and child business entity elements. we cannot perform trust override with this service.

      3) Search BE Business Entity Service-

            It is used to root record in the business entity. We can use match operation while searching records to achieve fuzzy searches.

C) Understanding Business Entity Services Architecture 

        Business Entity Services is an integral part of Informatica MDM and it is available as wed service. The consuming system can write web service consumer and consume BES, for reading and write operation. Once the operation is invoked when the data is searched against the base object or updated against the base object.

D) What are the REST Methods Supported 

      Here is the list of HTTP methods supported in Business Entity Services

           GET - used to retrieve details about record, task, or a file 

           POST - used to create task or record 

           PUT -  used to update root, child records, or a file.

          PATCH - used to update task partially 

          DELETE - used to delete records or file

Learn more about Business Entity Services here -  

What is Common Table Expression (CTE) in Snowflake?

    Are you working on Snowflake technology and would like to understand Common Table Expression also known as CTE? If so, then you reached the right place. In this article, we will understand what is Common Table Expression. We will also explore how to use CTE in queries.

A) What is Common Table Expression i.e CTE?

      A Common Table Expression (CTE) is a named subquery. It is defined in a WITH Clause. It is equivalent to a temporary view.

      The  CTE Contains an optional list of columns and a SELECT Statment. The output of CTE is a table with a column defined.

B) What is the syntax for CTE?

     The syntax for CTE is as below

      WITH

            TEMP_CTE (COL_1 ,COL_2) AS

              (SELECT col_nm1 , col_nm2 From TABLE)

     SELECT COL_1 , COL_2 FROM TEMP_CTE

C) What is recursive CTE?

      A recursive CTE is a CTE that references itself. In a recursive CTE, we can join Table to itself as many times as needed.

D) Benefits of CTE?

      1) CTE increases the modularity of the SQL programs

       2) CTE is helpful for simplified maintenance

       3) Recursive CTE can be used to process hierarchical data in the table.

E) Naming conventions for CTE?

      We need to avoid CTE names that match with database views or table the reason for this is - if we define query with CTE names then CTE takes precedence over table or view names which will produce unexpected results.

         For more details about snowflake refer to this video