UNIX Interview Questions | ETL

---

UNIX Commands for ETL Developer/ Tester/ Support

---

• LIST 

1) Unix command to list files in descending order

ls -ltr

2) Unix command to list files based on file size

ls -ls

3) Unix command to list hidden files 

ls -la | grep ^[.]

---

• KILL

1) UNIX command to kill background jobs

KILL $!

---

• PRINT

1) UNIX command to print the first line of the  file

HEAD -1 filename.txt
or 
SED '2$' filename.txt

2) UNIX command to print the last line of the  file

TAIL -1 filename.txt
or 
SED -n '$p' filename.txt

3) UNIX command to print an Nth line of the  file

SED -n '<n> $p' filename.txt

<n> - number of line

4) UNIX command to print a word in reverse

echo 
| rev

5) UNIX command print the last word of a line

echo 'C for Car' | rev | CUT -f 1 d ' ' | rev

---

• REMOVE

1) UNIX command to remove the first line of the file 

SED '1d' filename.txt

2) UNIX command to remove the last  line of the file 

SED  -i '$d' filename.txt

3) UNIX command to remove Nth line of the  file

SED  -i '<n> $d' filename.txt

<n> - number of line

4) UNIX command to remove the empty line from the file 

GREP -v ^"$" filename.txt

5) UNIX command to cut Nth word of a line

 

CUT -f <n> -d ' '

<n> - number of line

---

• COUNT 

1) UNIX command to find the number of lines in the file

filename.txt | wc -l

2) UNIX command to find the length of the Nth line in the file

SED -n '<n> p' filename.txt | wc -L

<n> - number of line

---

• OTHERS

1) View non-printable words in the file

vi filename.txt 

In vi mode you can view the non-printable words in the file

2) Remove BOM characters

SED -i '1s/^\xEF\xBB\xBF//' filename.txt

---

Types Of Dimension Tables with examples | Datawarehousing

---

DIMENSION:

The dimension table contains dimensions of fact.

It contains keys and descriptive attributes for measurements in the fact table.

---

TYPES OF DIMENSION:

Conformed dimension:

Conformed dimensions mean the exact same thing with every possible fact table to which they are joined.

Example: 

The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.

Junk dimension:

A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.

Example: 

Assume that we have a gender dimension and marital status dimension. In the fact table, we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension that has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table.

Degenerate dimension:

A degenerate dimension is a dimension that is derived from the fact table and doesn’t have its own dimension table.

Example: 

A transactional code in a fact table.

Role-playing dimension:

Dimensions that are often used for multiple purposes within the same database are called role-playing dimensions. 

Example: 

A date dimension can be used for “date of sale”, as well as “date of delivery”, or “date of hire”.

SCD – Slowly Changing Dimension

TYPE – 1:  Updates changes without preserving history.

TYPE – 2:  Maintains history by storing the value as a new record.

• Effective date
• Flagging
• Versioning

TYPE – 3:  Maintains history as the current and previous 

version column-wise.

---

Read More :

• Optimization in Informatica | Interview Question
• Performance Tuning of Informatica mapping | Interview Question

---

Part 1 | Informatica Interview Questions

FREQUENTLY ASKED INFORMATICA INTERVIEW QUESTIONS

Part 1 | Snowflake Developer Interview Questions

FREQUENTLY ASKED SNOWFLAKE DEVELOPER INTERVIEW QUESTIONS

In this post, we are going to explore snowflake interview questions. Topics Covered are Snowflake Architecture, warehouse, types of constraints, types of tables,  types of stages, and snowflake indexes.

This will majorly cover all the theoretical questions, starting with the basics and moving to advanced concepts. Hoping that it will be helpful and wishing the best for your interview.

---

Q1: What is Snowflake and how it is different from other databases?

Snowflake database is a cloud-based data warehouse-as-a-cloud-service i.e. SaaS that requires no hardware or software installation.

Advantages of Snowflake databases are

1. Build on cloud
2. Unlimited storage
3. Data is encrypted by default
4. Simple and transparent pricing
5. Moves to idle mode when not used
6. Time travel (1 - 90 days)

Q2: Explain the snowflake architecture?

Snowflake architecture consists of three layers.

    1. Cloud  Services

    2. Query Processing

    3. Database Storage

Snowflake Architecture

Detailed Explanation:

Cloud services

• Collection of services that coordinate activities across snowflake.
• Services managed in this layer include: Authentication, Metadata management, Query parsing and optimization and Access control

Query processing

• Uses virtual warehouses. Each virtual warehouse is a compute cluster connected to multiple nodes allocated by a snowflake.
• Each virtual warehouse is an independent compute cluster that does not share compute resources with other virtual warehouses. As a result, each virtual warehouse has no impact on the performance of other virtual warehouses.

Database storage

• Snowflake reorganize the data into its internal optimized, compressed, and columnar format.
• Data stored in snowflake can be accessed only using SQL queries.

Q3: What is shared disk and shared-nothing architecture?

Shared disk – It has a central repository for persisted data that is accessible from all the computable nodes.

Shared nothing – Uses massive parallel processing (MPP). Compute clusters where each node in the cluster stores a portion of the entire data set locally.

Q4: What are the different types of warehouse size?

• XSMALL
• SMALL
• MEDIUM 
• LARGE
• XLARGE
• XXLARGE 
• XXXLARGE 
• X4LARGE 

Most commonly medium-sized warehouse is used for heavy-duty load users.

Q5: What are the types of constraints in Snowflake?

Unique – To uniquely identify value, NULL is allowed.

Primary key – To uniquely identify each record, NULL is not allowed.

Foreign key - Primary key of another table, used to define the relationship between the tables.

NOT NULL - NULL values are not allowed.

Q6: Does Snowflake support indexes?

Snowflake does not use indexes. This is one of the things that makes Snowflake scale so well for arbitrary queries.

Q7: What is a surrogate key?

The surrogate key is the natural prime key, which means it is not generated based on the database data or values.

Q8: What are the different types of tables in Snowflake?

Temporary Table – Stores non-permanent, transitory data.

• Only exists within the session, will not be visible to other users.

Permanent Table – Available until explicitly dropped.

• Has fail-safe period.

Transient Table - Available until explicitly dropped.

• Doesn’t have a fail-safe period, can prefer when the data doesn’t require the same level of data protection and recovery as the permanent table.

External Tables - When queried, an external table reads the data set from a set of files in an external stage.

Q9: What is the difference between NULL, Blank, and Zero?

NULL - Unavailable, Unassigned, Unknown, or Not applicable

Blank - It is treated as a character (' ')

Zero - It is a number

Q10: What is a  stage and explain its types?

Stage is streamline for bulk loading or unloading data out of database tables.

There are two types of stage,

1. External stage 

2. Internal stage 

Detailed Explanation:

External 

The external stage is used to access the files placed in external locations like S3, Google Cloud, Azure, etc.

Internal Stage

With the internal stage, the files are stored in the Snowflake itself.

---

Read More :

Snowflake Developer Interview Questions and Answers ( Part 2)

Informatica Interview Questions and Answers ( Part 1)

---