---
title: "Top SQL, PySpark, and AWS Cluster Interview Questions with Answers"
description: "Prepare for data engineering and data science interviews with these top SQL, PySpark, Python, and AWS vs Spark Cluster interview questions. Includes examples on SCDs, email masking, duplicates, window functions, and cluster comparisons."
url: https://srinimf.com/2025/09/19/top-sql-pyspark-and-aws-cluster-interview-questions-with-answers/
date: 2025-09-19
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/09/pexels-photo-6185464.jpeg?fit=1880%2C1290&ssl=1
categories: ["PySpark"]
tags: ["AWS vs Spark Cluster Interview Questions", "Top SQL"]
type: post
lang: en
---

# Top SQL, PySpark, and AWS Cluster Interview Questions with Answers

Preparing for data engineering or data science interviews? Below is a structured blog post that highlights **frequently asked questions** around **SQL, PySpark, Python, and AWS vs Spark Clusters**, with answers and explanations.

Related[Top 10 Nike Data Engineer Interview Questions on Spark, SQL, and Delta Lake (2025)](https://srinimf.com/2025/09/17/top-10-nike-data-engineer-interview-questions-on-spark-sql-and-delta-lake-2025/)

## 🔹 SQL [Interview Questions](https://srinimf.com/2025/09/11/altimetrik-interview-questions-2025-python-sql-and-pyspark-explained/)

### 1. How do you find the maximum total earnings for employees?

```
WITH cte AS (
    SELECT MAX(salary * months) AS max_total_earning
    FROM employee
)
SELECT COUNT(*) AS employee_count, max_total_earning
FROM employee, cte
WHERE salary * months = cte.max_total_earning;
```

Related[13 Tricky Azure Databricks Interview Question Asked in Mphasis](https://srinimf.com/2024/02/02/13-tricky-azure-databricks-interview-question-asked-in-mphasis/)

👉 This returns the maximum earnings and the number of employees who achieved it.

### 2. How to get month-wise total sales and the top customer for each month?

```
WITH sales_cte AS (
    SELECT customer_id, 
           DATE_FORMAT(order_date, '%Y-%m') AS month,
           SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m')
)
SELECT *
FROM (
    SELECT *,
           RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) AS rnk
    FROM sales_cte
) ranked
WHERE rnk = 1;
```

👉 Uses **[window functions](https://srinimf.com/2016/09/13/how-to-write-great-sql-for-rank-and-denserank/)** to get the highest spender per month.

### 3. How do you handle duplicates using `DENSE_RANK`?

```
SELECT name
FROM (
    SELECT name,
           DENSE_RANK() OVER (PARTITION BY name ORDER BY id) AS rnk
    FROM employees
) t
WHERE rnk > 1;
```

👉 Identifies duplicate names by ranking them.

### 4. How do you find duplicates based on multiple columns (name, dob, email)?

```
SELECT name, dob, email, COUNT(*) AS scount
FROM emp
GROUP BY name, dob, email
HAVING COUNT(*) > 1;
```

👉 Returns only those rows where the same **name, dob, and email** combination appears more than once.

## 🔹 [PySpark Interview Questions](https://srinimf.com/2024/03/03/6-top-data-engineer-interview-questions-hexware/)

### 1. Difference between RDDs and DataFrames?

- **RDD**: Low-level, untyped distributed collection. Good for complex transformations.
- **DataFrame**: High-level, optimized via **Catalyst Optimizer** and **Tungsten**.👉 Prefer DataFrames unless you need fine-grained control.

### 2. How do you implement SCD Type 2 in PySpark?

```
from delta.tables import DeltaTable
from pyspark.sql.functions import current_date, lit

tgt = DeltaTable.forName(spark, "employees")
src = spark.table("staging_employees")

(tgt.alias("tgt")
 .merge(src.alias("src"), "tgt.emp_id = src.emp_id AND tgt.is_active = true")
 .whenMatchedUpdate(
     condition="tgt.emp_name <> src.emp_name OR tgt.dept <> src.dept",
     set={"is_active": lit(False), "end_date": current_date()}
 )
 .whenNotMatchedInsert(values={
     "emp_id": "src.emp_id",
     "emp_name": "src.emp_name",
     "dept": "src.dept",
     "start_date": current_date(),
     "end_date": lit(None),
     "is_active": lit(True)
 })
 .execute())
```

👉 This keeps historical versions of employee records.

### Implementing Slowly Changing Dimension (SCD) Type 2 in Spark SQL

### 📌 What is SCD Type 2?

In Data Warehousing, **Slowly Changing Dimensions (SCD)** handle changes in dimension data over time.

- **SCD Type 1**: Overwrites old data with new values (no history).
- **SCD Type 2**: Maintains historical versions of a record by adding new rows with validity dates.
- **SCD Type 3**: Stores limited history (previous value in a new column).

In **SCD Type 2**, we track changes using fields like:

- `start_date` → when the record became active
- `end_date` → when the record was closed (NULL = current active)
- `is_active` → flag to indicate the current version

### ⚡ Example: SCD Type 2 in Spark SQL

Suppose we have two Delta tables:

- **`t1`** → target dimension table (history + active records)
- **`t2`** → source table (latest snapshot of employees)

We can write a **MERGE statement** like this:

```
MERGE INTO t1 AS tgt
USING t2 AS src
ON tgt.emp_id = src.emp_id AND tgt.is_active = true   -- match only active record
WHEN MATCHED AND (
      tgt.emp_name <> src.emp_name OR tgt.dept <> src.dept  -- detect changes
) THEN UPDATE SET
    tgt.is_active = false,
    tgt.end_date = current_date   -- close old record

WHEN NOT MATCHED THEN
  INSERT (emp_id, emp_name, dept, start_date, end_date, is_active)
  VALUES (src.emp_id, src.emp_name, src.dept, current_date, NULL, true);
```

### ✅ Explanation

1. **Matching Active Records**: We only match against records where `is_active = true`.
2. **Detecting Changes**: If `emp_name` or `dept` has changed, we close the old record by setting:
   - `is_active = false`
   - `end_date = current_date`
3. **Inserting New Record**: If no active record exists for that employee, we insert a new row with:
   - `start_date = current_date`
   - `end_date = NULL`
   - `is_active = true`

### 📊 Example Data Flow

**Before update (`t1`):**

| emp_id | emp_name | dept | start_date | end_date | is_active |
| --- | --- | --- | --- | --- | --- |
| 101 | John | HR | 2023-01-01 | NULL | true |

**Incoming snapshot (`t2`):**

| emp_id | emp_name | dept |
| --- | --- | --- |
| 101 | John | IT |

**After MERGE (`t1`):**

| emp_id | emp_name | dept | start_date | end_date | is_active |
| --- | --- | --- | --- | --- | --- |
| 101 | John | HR | 2023-01-01 | 2025-09-19 | false |
| 101 | John | IT | 2025-09-19 | NULL | true |

### 🚀 Key Takeaways

- SCD Type 2 allows tracking **historical changes** in dimension data.
- Spark SQL with Delta Lake’s `MERGE INTO` is ideal for handling SCD2 efficiently.
- Always include **validity dates and active flags** to simplify queries.

### **Scd Type 2 with DLT**

here’s a **DLT SCD Type 2 pipeline** that tracks **exact change timestamps** using a `last_modified` or `event_time` column from the source data instead of just the pipeline run date. This is more accurate for historical auditing.

## 🟢 DLT SCD Type 2 with Timestamp-Based History

```
import dlt
from pyspark.sql.functions import col, current_timestamp

# ----------------------------
# 1️⃣ Staging Table (Raw Source)
# ----------------------------
@dlt.table(
    comment="Staging employees with event timestamp"
)
def staging_employees():
    df = (
        spark.read.format("json")
        .load("s3://my-bucket/raw/employees/")  # Replace with your S3 path
    )

    # Assume source has 'event_time' column indicating change timestamp
    # If not, use current timestamp as event time
    if "event_time" not in df.columns:
        df = df.withColumn("event_time", current_timestamp())

    # 🔹 Data quality checks
    dlt.expect("emp_id_not_null", "emp_id IS NOT NULL")(df)
    dlt.expect("salary_positive", "salary > 0")(df)

    return df

# --------------------------------------
# 2️⃣ Dimension Table (SCD Type 2)
# --------------------------------------
@dlt.table(
    comment="SCD Type 2 Employee Dimension with timestamp-based history",
    table_properties={"delta.enableChangeDataFeed": "true"}
)
def dim_employees():
    return spark.createDataFrame([], """
        emp_id INT,
        name STRING,
        dept STRING,
        salary INT,
        start_time TIMESTAMP,
        end_time TIMESTAMP,
        is_current BOOLEAN
    """)

# -------------------------------
# 3️⃣ Apply SCD Type 2 Merge
# -------------------------------
dlt.apply_changes(
    target="dim_employees",          # Target dimension table
    source="staging_employees",      # Source table
    keys=["emp_id"],                 # Business key
    sequence_by="event_time",        # Use event_time for ordering
    apply_as_deletes=False,          # Do not delete missing records
    column_list=["name", "dept", "salary"],  # Columns to track changes
    stored_as_scd_type=2,            # Enable SCD Type 2 behavior
    track_history_column_list=["name", "dept", "salary"],  # Columns whose changes are tracked
    start_time_column="event_time"   # Use exact event timestamp as start
)
```

### 🔹 How This Differs from Pipeline-Date SCD2

1. **Event-based history**
   - Uses `event_time` or `last_modified` from source.
   - Records show the **actual change time**, not just pipeline run time.
2. **Better accuracy**
   - Multiple changes in a single day are tracked correctly.
   - Historical reports reflect the real change order.
3. **End time & current flag**
   - `end_time` is automatically set when a new version is inserted.
   - `is_current = true` for the latest version, `false` for older ones.

### 🔹 Example Output

| emp_id | name | dept | salary | start_time | end_time | is_current |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | John | HR | 5000 | 2025-09-22 08:00 | NULL | true |
| 2 | Mary | Finance | 6000 | 2025-09-22 09:15 | 2025-10-01 12:30 | false |
| 2 | Mary | Finance | 7000 | 2025-10-01 12:30 | NULL | true |

⚡ **Notes**

- `sequence_by="event_time"` ensures changes are applied in the **correct chronological order**.
- `start_time_column="event_time"` tells DLT to use the actual change timestamp instead of pipeline execution time.
- This works best if your source system provides **event timestamps**. Otherwise, `current_timestamp()` acts as a fallback.

> When `dlt.apply_changes` runs:
>
>
>
>
> It detects a change in the tracked columns (`salary` changed).
>
>
>
> The old record is updated automatically:
>
> `end_date` (or `end_time`) = timestamp of the new change (`[2025](https://srinimf.com/2025/10/13/top-25-snowflake-interview-questions-answers-for-2025-snowflake-quiz/)-10-01 12:30`).
>
>
>
> `is_current = false`.
>
>
>
>
>
> The new record is inserted:
>
> `start_date` = timestamp of new change
>
>
>
> `end_date = NULL`
>
>
>
> `is_current = true`.

### 3. Example of Pivot/Unpivot in PySpark

```
from pyspark.sql.functions import sum

# Pivot
sales.groupBy("month").pivot("category").agg(sum("amount"))

# Unpivot (melt)
unpivoted = sales.selectExpr("month", "stack(3, 'A', A, 'B', B, 'C', C) as (category, amount)")
```

Related[PIVOT vs UNPIVOT: Must-Know Concepts for PySpark Developers](https://srinimf.com/2024/09/20/pivot-vs-unpivot-a-guide-for-pyspark-and-pandas-users/)

## 🔹 Python Interview Questions

### 1. How do you mask an email address?

```
def mask_email(email: str) -> str:
    username, domain = email.split("@")
    if len(username) > 2:
        masked_username = username[0] + "*" * (len(username) - 2) + username[-1]
    else:
        masked_username = username[0] + "*"
    return masked_username + "@" + domain

# Example
print(mask_email("johndoe@example.com"))  # Output: j*****e@example.com
```

👉 Masks the email by keeping the first and last characters of the username visible, replacing the rest with `*`.

## 🔹 AWS Cluster vs Spark Cluster

### 1. What is an AWS Cluster?

- AWS cluster generally refers to a **group of EC2 instances** or services (like EMR, Redshift, ECS, or EKS) working together.
- Example: **Amazon EMR cluster** manages big data workloads.
- Example: **Redshift cluster** for data warehousing.

### 2. What is a Spark Cluster?

- Spark cluster = **distributed Spark runtime** with:
  - **Driver** → coordinates tasks.
  - **Executors** → perform computation.
- Can run **standalone, on YARN, Mesos, Kubernetes, or EMR**.

### 3. AWS Cluster vs Spark Cluster Differences

| Feature | AWS Cluster (e.g., EMR) | Spark Cluster |
| --- | --- | --- |
| **Definition** | Group of AWS compute nodes/services | Runtime for Spark distributed jobs |
| **Management** | Managed by AWS (EMR, Redshift) | Managed by Spark master node |
| **Use Case** | Data warehouse, ETL, ML workloads | Distributed processing of big data |
| **Flexibility** | Limited to AWS ecosystem | Runs anywhere (on-prem/cloud/hybrid) |

Related[Master These 20 PySpark Functions to Land Your Next Big Data Job](https://srinimf.com/2025/05/27/master-these-20-pyspark-functions-to-land-your-next-big-data-job/)

👉 In short: **AWS cluster is infrastructure; Spark cluster is execution runtime.**

## 🔹 Final Tips [for Interviews](https://srinimf.com/2024/10/24/top-10-essential-pyspark-commands-for-interviews/)

1. **SQL** → Practice CTEs, window functions, aggregations.
2. **PySpark** → Focus on DataFrame API, Delta Lake `MERGE`, window functions.
3. **Python** → Be ready with string manipulations, masking, data cleaning.
4. **AWS vs Spark** → Be clear on infra (AWS services) vs execution (Spark runtime).

✅ With these examples, you’ll be ready to tackle common SQL, PySpark, Python, and architecture [interview questions with](https://srinimf.com/2025/10/07/top-8-python-data-engineering-interview-questions-with-sql-and-pandas-solutions/) confidence!

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F09%2F19%2Ftop-sql-pyspark-and-aws-cluster-interview-questions-with-answers%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F09%2Fpexels-photo-6185464.jpeg%3Ffit%3D1200%252C823%26ssl%3D1&description=Top%20SQL%2C%20PySpark%2C%20and%20AWS%20Cluster%20Interview%20Questions%20with%20Answers)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/09/19/top-sql-pyspark-and-aws-cluster-interview-questions-with-answers/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/09/19/top-sql-pyspark-and-aws-cluster-interview-questions-with-answers/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/09/19/top-sql-pyspark-and-aws-cluster-interview-questions-with-answers/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/09/19/top-sql-pyspark-and-aws-cluster-interview-questions-with-answers/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Top%20SQL%2C%20PySpark%2C%20and%20AWS%20Cluster%20Interview%20Questions%20with%20Answers&body=https%3A%2F%2Fsrinimf.com%2F2025%2F09%2F19%2Ftop-sql-pyspark-and-aws-cluster-interview-questions-with-answers%2F&share=email)
-

### Like this:

Like Loading…