Sigmoid Senior Data Engineer Interview Questions and Answers

Q1. Given a non-decreasing array, how can I determine the indices of an element X within it? If the element is not present, the output should be [-1, -1]. For example, for the array [1,2,3,3,5,5,7,8] and X=5, ...read more
Ans. 
Find indices of an element in a non-decreasing array
• Iterate through the array and keep track of the indices where the element X is found

• Return the list of indices or [-1, -1] if element X is not found

• Handle edge cases like empty array or X not present in the array
Answered by AI
Add your answer
Q2. From the employee table, how can I return the employee_name, department, age, and salary of the employee who is older than 35 and has the highest salary within each department?
Add your answer

Q1. What is scd in dw?
Ans. 
SCD stands for Slowly Changing Dimension in Data Warehousing.
• SCD is a technique used in data warehousing to track changes to dimension data over time.

• There are different types of SCDs - Type 1, Type 2, and Type 3.

• Type 1 SCD overwrites old data with new data, Type 2 creates new records for changes, and Type 3 maintains both old and new values in separate columns.

• Example: In a customer dimension table, if a customer chan...read more
Answered by AI
Add your answer
Q2. Inferschema in pyspark when reading file
Ans. 
inferschema in pyspark is used to automatically infer the schema of a file when reading it.
• inferschema is a parameter in pyspark that can be set to true when reading a file to automatically infer the schema based on the data

• It is useful when the schema of the file is not known beforehand

• Example: df = spark.read.csv('file.csv', header=True, inferSchema=True)
Answered by AI
Add your answer
Q3. Triplet program
Add your answer
Q4. Rank vs dense rank
Ans. 
Rank assigns unique ranks to each distinct value, while dense rank assigns ranks without gaps.
• Rank function assigns unique ranks to each distinct value in a result set.

• Dense rank function assigns ranks to rows in a result set without any gaps between the ranks.

• Rank function may skip ranks if there are ties in values, while dense rank will not skip ranks.
Answered by AI
Add your answer
Q5. Some numbers gave how many rows for inner join,left,right,full join
Ans. 
Understanding the number of rows returned by different types of SQL joins is crucial for data analysis.
• Inner Join: Returns rows with matching values in both tables. Example: 10 rows from Table A and 15 from Table B may yield 5 rows.

• Left Join: Returns all rows from the left table and matched rows from the right. Example: 10 rows from A and 5 matches in B yield 10 rows.

• Right Join: Returns all rows from the right table an...read more
Answered by AI
Add your answer
Q6. Read parquet file and remove duplicates
Ans. 
Read a parquet file using PySpark and remove duplicates based on specified columns.
• Use PySpark to read the parquet file: `df = spark.read.parquet('path/to/file.parquet')`.

• Identify the columns to check for duplicates, e.g., `['column1', 'column2']`.

• Use the `dropDuplicates()` method: `df_unique = df.dropDuplicates(['column1', 'column2'])`.

• Write the cleaned DataFrame back to a parquet file: `df_unique.write.parquet('path/...read more
Answered by AI
Add your answer
Q7. Optimizing techniques in spark
Ans. 
Optimizing techniques in Spark involve partitioning, caching, and tuning resources for efficient data processing.
• Use partitioning to distribute data evenly across nodes for parallel processing

• Cache frequently accessed data in memory to avoid recomputation

• Tune resources such as memory allocation and parallelism settings for optimal performance
Answered by AI
Add your answer
Q8. Repartition vs coalesce
Ans. 
Repartition is used to increase the number of partitions in a DataFrame, while coalesce is used to decrease the number of partitions.
• Repartition involves shuffling data across the network, which can be expensive in terms of performance and resources.

• Coalesce is a more efficient operation as it minimizes data movement by only merging existing partitions.

• Repartition is typically used when there is a need for more paralle...read more
Answered by AI
Add your answer
Q9. Normalization in db and types
Ans. 
Normalization in databases is the process of organizing data in a database to reduce redundancy and improve data integrity.
• Normalization is used to eliminate redundant data and ensure data integrity.

• It involves breaking down a table into smaller tables and defining relationships between them.

• There are different normal forms such as 1NF, 2NF, 3NF, and BCNF.

• Normalization helps in reducing data redundancy and improving qu...read more
Answered by AI
Add your answer
Q10. Transformation vs action
Ans. 
Transformation involves changing the data structure, while action involves performing a computation on the data.
• Transformation changes the data structure without executing any computation

• Action performs a computation on the data and triggers the execution

• Examples of transformation include map, filter, and reduce in Spark or Pandas

• Examples of action include count, collect, and saveAsTextFile in Spark
Answered by AI
Add your answer

Share price related question.most profit