Capgemini Data Engineer Interview Questions, Process, and Tips

Q1. How to remove Duplicates in Data frame using pyspark?
Ans. 
Use dropDuplicates() function in pyspark to remove duplicates in a data frame.
• Use dropDuplicates() function on the data frame to remove duplicates based on all columns.

• Specify subset of columns to remove duplicates based on specific columns.

• Use the distinct() function to remove duplicates and keep only distinct rows.
Answered by AI
Add your answer
Q2. What is Broadcast join ? How is this useful
Ans. 
Broadcast join is a type of join operation in distributed computing where one smaller dataset is broadcasted to all nodes for efficient processing.
• Reduces data shuffling by sending smaller dataset to all nodes

• Useful when one dataset is significantly smaller than the other

• Improves performance by reducing network traffic and processing time
Answered by AI
Add your answer
Q3. What is Re-Partition and Coalesce? How are these used?
Ans. 
Re-Partition and Coalesce are methods used to control the number of partitions in a dataset in Apache Spark.
• Re-Partition is used to increase or decrease the number of partitions in a dataset by shuffling the data across the cluster.

• Coalesce is used to decrease the number of partitions in a dataset without shuffling the data, which can improve performance.

• Re-Partition is typically used when there is a need to increase p...read more
Answered by AI
Add your answer
Q4. Write Python code to Extract Pincode from Address Field in Dataframe using Pyspark?
Ans. 
Extract Pincode from Address Field in Dataframe using Pyspark
• Use pyspark.sql.functions regexp_extract() function to extract pincode from address field

• Create a new column in the dataframe to store the extracted pincode

• Specify the regular expression pattern for pincode extraction

• Example: df.withColumn('pincode', regexp_extract(df['address'], '\b\d{6}\b', 0))
Answered by AI
Add your answer
Q5. Write a SQL to get Student names who got marks>45 in each subject from Student table
Ans. 
SQL query to retrieve student names with marks > 45 in each subject
• Use GROUP BY and HAVING clauses to filter students with marks > 45 in each subject

• Join Student table with Marks table on student_id to get marks for each student

• Select student names from Student table based on the conditions
Answered by AI
Add your answer
Q6. How to Enable Hive support in spark?
Ans. 
Enable Hive support in Spark for seamless integration of Hive tables and queries.
• Set 'spark.sql.catalogImplementation' to 'hive' in SparkConf

• Include 'spark-hive' dependency in the Spark application

• Ensure Hive configuration files are available in the classpath

• Use HiveContext or enable Hive support in SparkSession
Answered by AI
Add your answer
Q7. Explain Joins in spark using pyspark
Ans. 
Joins in Spark using PySpark are used to combine data from two different DataFrames based on a common key.
• Joins are performed using the join() function in PySpark.

• Common types of joins include inner join, outer join, left join, and right join.

• Example: df1.join(df2, df1.key == df2.key, 'inner')
Answered by AI
Add your answer
Q8. How will you Join if two tables are large in pyspark?
Ans. 
Use broadcast join or partition join in pyspark to join two large tables efficiently.
• Use broadcast join for smaller table and partition join for larger table.

• Broadcast join - broadcast the smaller table to all worker nodes.

• Partition join - partition both tables on the join key and join them.

• Example: df1.join(broadcast(df2), 'join_key')

• Example: df1.join(df2, 'join_key').repartition('join_key')
Answered by AI
Add your answer
Q9. What is df.explain() in pyspark
Ans. 
df.explain() in pyspark is used to display the physical plan of the DataFrame operations.
• df.explain() is used to show the execution plan of the DataFrame operations in pyspark.

• It helps in understanding how the operations are being executed and optimized by Spark.

• The output of df.explain() includes details like the logical and physical plans, optimizations applied, and stages of execution.
Answered by AI
Add your answer
Q10. Explain spark architecture
Ans. 
Spark architecture is a distributed computing framework that consists of a driver program, cluster manager, and worker nodes.
• Spark driver program coordinates the execution of tasks and maintains the overall state of the application.

• Cluster manager allocates resources for the application and monitors its execution.

• Worker nodes execute the tasks assigned by the driver program and store data in memory or disk.

• Spark archit...read more
Answered by AI
Add your answer
Q11. What Purpose of Lineage graph ?
Ans. 
Lineage graph is used to track the flow of data from source to destination, helping in understanding data dependencies and impact analysis.
• Helps in understanding data dependencies and relationships

• Tracks the flow of data from source to destination

• Aids in impact analysis and troubleshooting

• Useful for data governance and compliance

• Can be visualized to easily comprehend complex data pipelines
Answered by AI
Add your answer
Q12. External Table vs Internal table
Ans. 
External tables store data outside the database while internal tables store data within the database.
• External tables reference data stored outside the database, such as in HDFS or S3, while internal tables store data within the database itself.

• External tables are typically used for data that is not managed by the database system, while internal tables are used for data that is managed by the database system.

• External ta...read more
Answered by AI
Add your answer
Q13. Assume below Dataframes DF1 (UserID,Name) DF2 (UserID,PageID,Timestamp,Events) Write code to Join the DF's, Count the No of Events and filter Users with 0 Events
Ans. 
Join DF's, count events, filter users with 0 events
• Use join operation to combine DF1 and DF2 on UserID

• Group by UserID and count the number of events

• Filter out users with 0 events
Answered by AI
Add your answer

Q1. What are the optimization techniques used in Apache Spark?
Add your answer
Q2. 2 SQL queries , 1 PySpark code and 1 Python Code .
Add your answer
Q3. 2-3 Scenario Based questions from ADF and databricks .
Add your answer

Q1. Basic question on AWS and SQL,Pyspark,Python
Add your answer

Q1. What are the SQL concepts
Add your answer
Q2. Phython data frame concepts and all
Add your answer

Q1. Describe yourself
Ans. 
I am a detail-oriented data engineer with a passion for problem-solving and a strong background in programming and data analysis.
• Experienced in designing and implementing data pipelines

• Proficient in programming languages such as Python, SQL, and Java

• Skilled in data modeling and database management

• Strong analytical skills and ability to work with large datasets

• Excellent communication and teamwork skills
Answered by AI
Add your answer
Q2. Summary of your formation
Ans. 
I have a Bachelor's degree in Computer Science and a Master's degree in Data Engineering.
• Bachelor's degree in Computer Science

• Master's degree in Data Engineering
Answered by AI
Add your answer

Some DSA problems medium level

Q1. Spark archi, hadoop ecosystem, hive
Add your answer
Q2. Some sql questions also
Add your answer