Current experience: 5 years

Total rounds: 1 + 4
Round 1:
Initial screening of 1 hour.
Previous projects for 10 mins
Concepts of Data warehousing, Data modelling, SCD
2 -3 Medium difficulty SQL questions (Not able to recall exactly what all but the focus was on joins & case statements)
Leetcode 2 sum in python

Post this, the HR informed that the next round would be a 4hour session & asked to prepare 2 things for the next rounds:

1. A small portfolio of projects in my career I'm proud of.
2. 2 problem statements that had to be solved with Spark

I made a ppt for 2 of my projects(flow diagram of all the components).
Solved the problem statements with pyspark and showed the files.

The next 4 rounds took place in a 4 hour stretch on the same day from 9am to 1pm.

Round 2 (1hr):
Questions revolving around my project (the ppt I made) & the my solutions to the problem statement

Round 3 (1 hr):
A little more in depth about data warehousing, spark, sql, data modelling & SQL

Round 4 (1hr):
This was somewhat of a cultural fitment round.

Round 5(45 mins):
Hiring manager round. This felt the toughest as the hiring manager was very proficient in Big data and system architect.
Casual chat for 5-10 mins then he went very deep into the internals & architecture of my projects.