1. Explain your current Snowflake project architecture end to end.
I am working on a data engineering project currently, using Snowflake as our Cloud Data Warehouse.
The main objective of our project is to collect data from multiple source systems, transform that data, and make it available for business reporting and analytics.
We pull data from various sources (SQL Server, Oracle, Flat Files) using Azure Data Factory. This data is loaded into Azure Data Lake and then using COPY INTO commands, loaded into Snowflake.
Inside Snowflake, we have 3 levels of data – Raw or Landing, Staging, Curated areas. The source data is loaded into Raw layer. Duplicate data, NULL values and some cleaning is done on the data inside Staging layer. Business logic, joins, aggregations and SCD Type 1 and Type 2 is applied in Curated Layer.
To do incremental loads we use MERGE statements, Streams and Tasks depending on requirements. The transformed data is then used by the reporting team for Power BI dashboards.
My roles and responsibilities in my current project are
I written SQL queries/views/stored procs based on business requirements
I developed incremental processes to load and transform data
I Validated the data from source to target and resolving any data discrepancies
Monitored daily jobs, fixing any issues in production and working with the team to deliver data successfully.
Overall my role is focused around developing Snowflake, transformations, data validation and providing production support.
2. Explain one of the most challenging issues you faced in your Snowflake project. How did you resolve it?
One of the challenging issues I faced in my project was related to duplicate records during incremental data loading.
We were receiving daily data from the source system, and sometimes the same records were coming multiple times. Because of this, duplicate records were getting loaded into our Snowflake target tables, which was affecting the reporting data.
Initially, I checked the source data and identified that multiple records were coming with the same business key.
To resolve this, I used the ROW_NUMBER() function with PARTITION BY to identify duplicate records and kept only the latest record based on the last updated timestamp. Then I used the MERGE statement to perform the incremental load into the target table.
After implementing this logic, duplicate records were eliminated, and the data was loaded correctly.
This issue helped me understand the importance of data validation and proper incremental loading logic in Snowflake.
3. Suppose your daily ETL pipeline, which normally takes 30 minutes, suddenly starts taking 3 hours. How would you investigate and resolve the issue?
No, you cannot add two targets in data synchronization task. But you can add multiple sources in synchronization task.
How to convert column to rows in IICS?
By using Normalizer transformation, we can convert columns to rows in IICS.
How does IICS support data governance?
Informatica has implemented new tool called Cloud Data Governance and Catalog products.
How to Parameterize source and target in Informatica IICS?
We can create input parameters in a mapping to parametrize the source and Target connections in Informatica cloud IICS.
Define the source and target Connection parameter values in Param file.
What is dynamic lookup and how it can remove duplicates in Informatica IICS?
Dynamic lookup is used when you want to insert or update the lookup cache at run time. Normally we will use Dynamic lookup cache, when lookup is a target table.
Dynamic lookup can remove the duplicates when NewLookupRow becomes zero.
What is the resources provided by Informatica Cloud REST API.?
Informatica Cloud REST API lets you can access the (metadata) information from your Informatica Intelligent Cloud Services organization.
It also allows you to perform tasks such as create, update, delete connections, schedules and tasks.
What is the default timeout for the listener to wait for file in Informatica IICS?
If a file event does not occur, by default, the taskflow waits for 5 minutes.
After that, the File Watch Task step completes and the taskflow proceeds to the subsequent steps.
The maximum value that you can define in the Time Out field is 7 days.
How do you keep up with the most recent updates in Informatica IICS?
- There are several ways to get and use the information on updates and maintenance
- As soon as the date is fixed, an email will be received. The aim is to be a few weeks before the update so there is enough time to prepare
- In addition to mail notifications, information on maintenance or updates is made available in the Events and the status webpage to subscribe.
How to remove symbols and untranslatable characters (junk characters) from source in Informatica IICS?
Below are the few examples we can use in expression transformation to remove the junk characters.
1. REG_REPLACE (PORT_NAME, ‘[^\w,-. ]’, ”) : This function removes the special characters and retains only alphanumeric characters, commas, dashes, and periods.
2. REG_REPLACE(PORT_NAME,'[^a-z0-9A-Z]’,”) : This function retains alphanumeric characters only.
3. REG_REPLACE(PORT_NAME,'[^[:print:]]’,”) : This function looks for ‘[^[:print:]]’ which means its searching for non-printable characters in the field which we are passing and its been replaced with ” (NULL).
Can we invoke Taskflow using file listener in Informatica IICS?
You can invoke a taskflow through a file listener with the connector source type, and select the file listener as the event source. When you publish the taskflow, the taskflow subscribes to the file listener that is defined in it.
When a file event occurs, the file listener invokes the taskflow.
Why update strategy and union transformations are Active in Informatica IICS?
Update Strategy: If any transformation can change the Insert/Update/Delete status of records, that is called an active transformation.
Because update strategy can change the Insert/Update/Delete status of records, that’s why it is an active transformation.
Union: If any transformation has more than one input group or more than one output group, that is called an active transformation. Because Union is having multiple input groups, that’s why union is an active transformation.
How to reverse the data input in Informatica IICS?
We can use a Informatica built-in function called REVERSE (string) to reverse a column in IICS.
What is partitioning in Informatica Cloud Data Integration?
- Partitioning is a process of enabling the parallel processing of the data through separate pipelines.
- Currently partitioning can be configured in the Source transformation in the Mapping Designer.
- You should see partitioning in the mapping source properties. When you configure partitions in the Source transformation, partitioning occurs throughout the mapping.
How do you load first and last records into target table? How many ways are there to do it? Explain through mapping flows in Informatica IICS.?
Method 1: use the below mapping flow.
Src –> Exp –> Filter –> Target (For first record)
–> Aggregator –> Target
In Expression : v_COUNT = v_COUNT + 1
out_COUNT =v_COUNT
In first flow filter use condition as out_COUNT=1
In aggregator No group by port selected here. It returns the last row.
Method 2: We can write the shell script using the below commands.
head -1
tail -1
We call it either in command task or pre and post session shell commands in session.
In the Informatica Cloud, how does an update strategy transformation operate?
- There is no Update strategy transformation available in Information Cloud IICS.
- In the target transformation in a mapping, Informatica Cloud Data Integration provides the option for the action to be performed on the target – Insert, Update, Upsert, Delete and Data Driven.
What is your role in present project as well previous one?
My Role in Present and previous project is Informatica ETL Developer.
What type of communicators you can use in your office?
We will use normally either Teams or Skype for business as an office communicator.
Do you have any knowledge about Informatica Cloud Architecture?
Yes, i have knowledge on Informatica Cloud Architecture.
In last three years, you work with only one project or?
Based on your resume, tell that you have worked on 1 project or 2 projects.
What are the DB's you use? and which version you use?
We were using oracle 19c, SQL Server 2019 and snowflake cloud database in out project.
Which Informatica IICS transformations you can use daily scenarios? and is there any difference in last project?
I have worked on the below Informatica IICS transformations on daily basis.
Source, Filter, Lookup, Router, Exp, Aggregator, Sorter, Rank, Joiner and union, etc.
In the current project i have used two new transformations like Hierarchy Builder and Hierarchy parser as we deal with JSON files.
Are you flexible with SQL joins? If, explain in detail?
Yes, i am comfortable in working on all types of joins supported by Oracle.
Read the joins in oracle interview questions in my website.
How many joins you can use in real time scenario and explain about cross join?
In Realtime, we mostly used Equi join, left outer join and right outer join to join the sources in IICS mapping. Also, I have used these joins in SQL override query in Source transformation.
Read the joins in oracle interview questions in my website.
Have you schedule any jobs in Informatica IICS and explain it?
I have used Informatica IICS schedular to schedule mapping tasks or taskflows in IICS. We can schedule mapping task while we creating a task under Schedule tab in IICS.
To Scheule the taskflow perform the below steps.
- Navigate to Data Integration > Explore
- Select the taskflow you want to schedule from its Project/Folder.
- Either right click on the selected taskflow or click on the Action menu (three dots) next to the taskflow.
- Select the Schedule option.
Do you have any knowledge on coding languages like python or java.! or you are pure in these informatica?
Yes, basically I am an Informatica ETL developer. I do not have any knowledge on coding languages like python or java.
But if give me an opportunity, then i am happy learn and work on python.
Leave a comment