Data Engineering Interview Questions and Answers: Part 1

Summary: Prepare for data engineering interviews with practical questions and answers covering Python, Pandas, DuckDB SQL, data validation, aggregations, window functions, and reproducible data pipelines. This Part 1 guide includes two hands-on coding challenges: finding the top three users by monthly spending and verifying that Pandas and SQL return equivalent results.

Introduction

Preparing for a data engineering interview involves more than writing code that produces the correct output. You should also be able to explain your approach, validate assumptions, identify edge cases, and demonstrate that your results are reliable.

This post will test essential data engineering concepts through practical questions and coding exercises. You will explore reusable Python functions, data-quality checks, Pandas transformations, DuckDB SQL queries, aggregations, and window functions.

You will also solve two hands-on problems. The first identifies the top three users by total spending for each month. The second compares Pandas and SQL implementations to verify that they identify the same highest-spending user.

Basic Data Engineering Interview Questions and Answers

Question 1: What does the ingest, transform, validate, and produce output pattern mean?

Answer: It describes a simple, reusable workflow for processing data.

Ingest: Read data from a source, such as a CSV file, database, API, or data stream.
Transform: Clean, normalize, aggregate, or otherwise modify the data to meet the requirements.
Validate: Check data quality, required fields, business rules, and expected results.
Produce output: Write the processed data to a destination, such as a database table, CSV file, or Parquet file.

For example, a transaction-processing pipeline might read transaction records, normalize timestamps, calculate monthly spending, validate the results, and produce an analytical table. A network-traffic pipeline might additionally validate port ranges and verify the resulting Parquet file.

Question 2: Why should repeated transformation logic be placed in a reusable function?

Answer: Reusable functions reduce duplication and make data-processing logic easier to test, maintain, and reuse.

For example, suppose several datasets require consistent column-name normalization. Instead of repeating the same operations in multiple scripts, we can create a function named format_column_name().

def format_column_name(name):
    return (
        name.strip()
        .lower()
        .replace(" ", "_")
    )

print(format_column_name(" Customer Name "))
# customer_name

Now the same function can be used wherever column names need to be normalized. If the naming convention changes, we can update the function in one place.

Question 3: Why are assertions and data validation important in data engineering?

Answer: Assertions turn assumptions into executable checks. They help identify unexpected data conditions before invalid results move further through a pipeline.

For example, we might verify that:

The input dataset is not empty.
Required columns are present.
Important identifiers are not null.
Transaction amounts satisfy the expected business rules.
Network ports fall within the valid range of 0 to 65,535.
Output row counts and aggregation results match expectations.

If a validation check fails, the pipeline can stop with a clear error message instead of silently producing unreliable output.

In production pipelines, data-quality checks should usually be implemented as explicit validation rules with meaningful error messages. Python assertions are useful for development and testing, but they can be disabled when Python runs with optimization enabled.

Question 4: When would you use Pandas, and when would you use SQL?

Answer: Pandas is useful for data manipulation and analysis through Python DataFrames. SQL is well suited to relational operations such as filtering, joining, grouping, aggregation, and ranking.

For example, I might use Pandas to clean a dataset, normalize column names, and prepare data for analysis. I might then use SQL to calculate monthly spending, join transaction data with reference tables, or rank users by their total spending.

The choice depends on data volume, performance requirements, the existing infrastructure, and the team's skills. For analytical workflows, implementing the same aggregation in Pandas and SQL can also help verify that both approaches produce equivalent results.

Question 5: What is DuckDB, and how is it used in a Python analytics workflow?

Answer: DuckDB is an analytical database that can execute SQL directly within a Python application. It is particularly useful for analytical queries without requiring a separate database server for a local data analysis.

DuckDB can work with Pandas DataFrames and common data formats such as CSV and Parquet. It supports operations including joins, aggregations, common table expressions, and window functions.

For example, a Python script can prepare a DataFrame, register or expose it to DuckDB, execute a SQL aggregation, and return the result as a Pandas DataFrame for further processing or comparison.

Question 6: What is the difference between GROUP BY and a window function?

Answer: GROUP BY combines input rows into summary rows for each group. A window function calculates a result across related rows while retaining the individual rows in the query result.

For example, consider a transaction table containing a user ID, transaction amount, and transaction date.

Using GROUP BY, we can calculate total spending for each user. Using RANK() with PARTITION BY, we can rank those users within each month according to their spending.

Window functions are particularly useful when we need rankings, running totals, moving averages, or comparisons between rows without collapsing the entire result into one row per group.

Question 7: How can LEFT JOIN be used to enrich records?

Answer: A LEFT JOIN combines records from a primary table with matching records from another table. It retains every row from the left table, even when no matching row exists in the right table.

For example, we might join network events with a protocol lookup table to add protocol descriptions. Similarly, transaction records can be joined with category metadata to include additional descriptive fields.

After the join, I check whether the expected records matched and whether the join introduced unexpected duplicates. If a lookup field is null, I investigate whether the reference data is incomplete or the join key is incorrect.

Question 8: How do you verify that Pandas and SQL produce equivalent results?

Answer: I implement the same transformation in both interfaces, make sure the data types and business rules are consistent, and compare the results.

For DataFrame comparisons, Pandas provides pd.testing.assert_frame_equal(). Before comparing, I ensure that the column selection and row ordering are consistent.

For example, if both implementations calculate monthly spending by user, I can sort both outputs by user_id and compare the identifier and amount columns.

Sorting is important because two DataFrames can contain the same records in a different order. Also, when comparing floating-point values, consider whether the application requires exact equality or an appropriate numerical tolerance.

Question 9: Which data-quality checks can be applied to network traffic data?

Answer: Network traffic data can be validated using structural, completeness, and business-rule checks.

Verify that the dataset contains the expected records.
Check for null values in required fields, such as source IP address or protocol.
Validate port values against the range 0 to 65,535 when the fields represent network ports.
Ensure fields such as bytes sent and duration are not negative when the data model requires non-negative values.
Validate protocol values against a reference table where appropriate.
After writing a Parquet file, verify its schema, row count, and selected values.

Not every column must be non-null, and not every numeric field must be non-negative. The checks should reflect the actual data contract and business requirements.

Question 10: What makes a data-processing example reproducible?

Answer: A reproducible example uses predictable inputs, clearly defined transformations, consistent dependencies, and verifiable outputs.

To improve reproducibility, I check that the expected input files exist, confirm that required columns are present, and make file paths and configuration settings explicit.

If a required input file is missing, the script should report the problem rather than silently generate replacement data. For development or testing purposes, synthetic data can be generated (I can use Mock Data Generator) deliberately, but that behavior should be clearly separated from the normal processing workflow.

A final validation step can confirm that the output contains the expected columns, includes valid records, and satisfies the key business rules.

Question 11: What does idempotency mean in a data-processing exercise?

Answer: Idempotency means that repeating an operation produces the same intended result without introducing unintended additional effects.

For example, a script that creates a DuckDB table may fail on its second execution if the table already exists. Using DROP TABLE IF EXISTS before recreating the table can make that particular table-creation step repeatable.

However, dropping and recreating a table is not a universal solution for production pipelines. Depending on the workflow, an idempotent design may require deterministic keys, deduplication, transactional updates, or carefully controlled upserts.

Problem 1: Find the Top Three Users by Monthly Spending

Problem statement

Suppose you have a file named transactions.csv containing the following columns:

tx_id: Unique transaction identifier.
user_id: Identifier of the user who made the transaction.
amount: Transaction amount.
ts: Transaction timestamp.

Your task is to calculate the top three users by total spending for each month. First, implement the transformation using Pandas. Then, implement the same logic using DuckDB SQL.

Finally, display the top-three users for February 2026 and verify that both implementations return matching results.

Solution approach

We can solve this problem in a few steps:

Convert the transaction timestamps into a consistent datetime format.
Extract the year and month from each timestamp.
Calculate total spending for each user in each month.
Rank users by monthly spending in descending order.
Keep users whose rank is three or less.
Filter the results for February 2026 and compare the Pandas and SQL outputs.

In this example, we use RANK() semantics. If users tie at the third position, the result can contain more than three users. If the requirement is to return exactly three users per month, including a defined tie-breaking rule, ROW_NUMBER() may be more appropriate.

Code Window 1: Create deterministic sample data

For this exercise, we will create a small, reproducible dataset and save it as a CSV file. In a real interview, you may be asked to read an existing file instead.

import pandas as pd
import duckdb

data = {
    "tx_id": range(1, 16),
    "user_id": [
        101, 102, 103, 104, 101,
        102, 105, 101, 102, 103,
        104, 105, 101, 102, 103
    ],
    "amount": [
        100, 150, 200, 50, 300,
        50, 400, 100, 200, 300,
        400, 500, 100, 50, 200
    ],
    "ts": [
        "2026-01-01", "2026-01-02",
        "2026-01-03", "2026-01-04",
        "2026-01-05", "2026-02-01",
        "2026-02-02", "2026-02-03",
        "2026-02-04", "2026-02-05",
        "2026-03-01", "2026-03-01",
        "2026-03-02", "2026-03-03",
        "2026-03-04"
    ]
}

df_raw = pd.DataFrame(data)
df_raw.to_csv("transactions.csv", index=False)

print(df_raw.head())

The sample data includes transactions from January, February, and March 2026. We will use it to demonstrate monthly aggregation and ranking.

Code Window 2: Solve the problem using Pandas

First, convert the timestamps to datetime values and derive a month column. Then group the records by month and user, sum the transaction amounts, and rank the resulting totals.

# Convert timestamps and derive the month.
df_raw["ts"] = pd.to_datetime(df_raw["ts"])
df_raw["month"] = df_raw["ts"].dt.strftime("%Y-%m")

# Calculate total spending by month and user.
pandas_agg = (
    df_raw.groupby(["month", "user_id"])["amount"]
    .sum()
    .reset_index()
)

# Rank users within each month.
pandas_agg["rank"] = (
    pandas_agg.groupby("month")["amount"]
    .rank(method="min", ascending=False)
)

# Retain users ranked third or higher.
pandas_top3 = pandas_agg[
    pandas_agg["rank"] <= 3
].copy()

# Filter February 2026 and sort for comparison.
pandas_final = (
    pandas_top3[pandas_top3["month"] == "2026-02"]
    .sort_values("user_id")
    .reset_index(drop=True)
)

print(pandas_final)

The groupby() operation calculates each user's monthly spending. The ranking step then determines which users qualify for the top three positions within each month.

Using method="min" assigns tied users the same minimum rank. This is consistent with the SQL RANK() function used in the next implementation.

Code Window 3: Solve the same problem using DuckDB SQL

Next, we will expose the Pandas DataFrame to DuckDB and execute the equivalent SQL query. The query uses a common table expression to calculate monthly spending, followed by a window function to rank users within each month.

con = duckdb.connect()

con.execute("DROP TABLE IF EXISTS tx")
con.execute("CREATE TABLE tx AS SELECT * FROM df_raw")

sql_query = """
WITH monthly_spend AS (
    SELECT
        strftime(CAST(ts AS DATE), '%Y-%m') AS month,
        user_id,
        SUM(amount) AS amount
    FROM tx
    GROUP BY 1, 2
),
ranked_spend AS (
    SELECT
        month,
        user_id,
        amount,
        RANK() OVER (
            PARTITION BY month
            ORDER BY amount DESC
        ) AS rank_in_month
    FROM monthly_spend
)
SELECT
    month,
    user_id,
    amount,
    rank_in_month
FROM ranked_spend
WHERE rank_in_month <= 3
  AND month = '2026-02'
ORDER BY user_id
"""

sql_final = con.execute(sql_query).df()

print(sql_final)

The monthly_spend common table expression calculates the total amount spent by each user in each month. The ranked_spend expression ranks those totals within each month.

Finally, the query filters the results for February 2026 and returns users whose ranks are three or less.

Code Window 4: Verify that both implementations match

Producing results from two implementations is not enough. We should also verify that they agree on the fields that matter to the problem.

pd.testing.assert_frame_equal(
    pandas_final[["user_id", "amount"]],
    sql_final[["user_id", "amount"]],
    check_dtype=False
)

print("Status: Parity verified. Results match.")

The assertion compares the user identifiers and spending totals. Both DataFrames have already been sorted by user_id, so the comparison uses a consistent row order.

The check_dtype=False option allows differences in column data types without ignoring differences in the actual values. If the assertion fails, investigate the aggregation, ranking, filtering, and sorting logic.

Expected result for February 2026

The three highest-spending users in the sample data are:

User 105: 400
User 103: 300
User 102: 250

Because the displayed results are sorted by user_id for comparison, the rows appear in this order:

month      user_id  amount
2026-02    102      250
2026-02    103      300
2026-02    105      400

The assertion confirms that the Pandas and DuckDB implementations return the same user identifiers and spending totals for February 2026.

Evaluation criteria

Correctness: Monthly totals and rankings are calculated correctly.
Consistency: Pandas and SQL use equivalent grouping and ranking rules.
Validation: The implementations are compared programmatically rather than visually.
Clarity: You can explain the purpose of each transformation in a few sentences.
Efficiency: The solution avoids unnecessary operations and is appropriate for the data size.

For this small dataset, execution should be quick. In a real interview, avoid claiming a general performance guarantee based on a tiny sample. Performance depends on data volume, hardware, query design, and the execution environment.

Problem 2: The Cross-Interface Challenge

Problem statement

Given a transaction dataset, identify the user with the highest total spending. Implement the calculation in Pandas and SQL, then verify that both approaches return the same user identifier.

For consistency, both implementations will use the columns user_id and amount.

Solution approach

In Pandas, group the records by user_id, calculate the total spending for each user, and identify the user associated with the maximum total.

In SQL, perform the same grouping and aggregation, order the totals from highest to lowest, and select the first result. Finally, compare the identifiers using an assertion.

The following code assumes that a Pandas DataFrame named df already exists and contains the required columns. It also assumes that con is an open DuckDB connection.

Code Window 5: Compare the Pandas and SQL results


# Assumptions:
# df contains user_id and amount.
# con is an open DuckDB connection.

top_pandas = (
    df.groupby("user_id", as_index=False)["amount"]
    .sum()
    .sort_values(
        ["amount", "user_id"],
        ascending=[False, True]
    )
    .iloc[0]["user_id"]
)

con.execute("DROP TABLE IF EXISTS transactions")
con.execute(
    "CREATE TABLE transactions AS SELECT * FROM df"
)

top_sql = con.execute("""
    SELECT user_id
    FROM transactions
    GROUP BY user_id
    ORDER BY SUM(amount) DESC, user_id ASC
    LIMIT 1
""").fetchone()[0]

assert top_pandas == top_sql

print(f"Winner: {top_sql}")

The Pandas implementation calculates total spending for each user, sorts users by total spending in descending order and user_id in ascending order, and selects the first row. This ensures that ties are resolved consistently with the SQL implementation.

The SQL implementation calculates the same totals and selects the user with the highest spending. The additional user_id ASC ordering provides a deterministic tie-breaker in SQL when multiple users have the same total.

This exercise does not define a separate input dataset or a fixed winning user, so the result depends on the contents of df.

Evaluation criteria

Use the same identifier and amount columns in both implementations.
Apply the same aggregation logic in Pandas and SQL.
Handle ties consistently when selecting a single winning user.
Use an assertion to verify that the results match.
Be prepared to explain why the SQL query uses grouping, ordering, and a limit.

Key Takeaways

These two problems demonstrate several important data engineering skills. First, a good solution separates ingestion, transformation, validation, and output. Second, Pandas and SQL can express similar analytical operations, but their results should be compared using consistent rules.

Third, aggregations and window functions solve different problems. Aggregations calculate summary values, while window functions help rank or analyze rows within groups. Finally, assertions make validation repeatable and help catch discrepancies before they become harder to investigate.

When preparing for data engineering interviews, practice explaining not only what your code does but also why you chose that approach, how you validate the output, and what happens when the data contains ties, nulls, or unexpected values.

Have you encountered a data engineering interview question that tested your ability to validate results across Python and SQL? Share your experience or questions in the comments.

If you want any of the following, send a message using the Contact Us (left pane) or message Inder P Singh (7 years' experience in AI, Data Science/ Engineering and ML) in LinkedIn at https://www.linkedin.com/in/inderpsingh/

Production-oriented templates with playbooks
Runnable Projects for your portfolio
Deep-dive hands-on Training
Effective Resume Updates

Search This Blog

Fourth Industrial Revolution