Data Engineering Interview Preparation #1: Python, Pandas, SQL and DuckDB
Summary: Start your Data Engineering interview preparation with a practical Kaggle notebook covering Python, Pandas, SQL, DuckDB, data validation and Python/SQL result verification.
If you are preparing for a Data Engineering interview, knowing Python and SQL syntax is only the beginning. You also need to understand how data moves through a workflow, how transformations are validated, and how to explain your technical decisions clearly.
That is the purpose of Data Engineering Interview Preparation #1, the first practical asset in my progressive Data Engineering interview-preparation series.
This notebook is designed to give learners a low-friction starting point. It uses the Kaggle notebook environment and a small Sales Transactions dataset to connect Python, Pandas and SQL through practical examples.
What You Will Learn
In this first notebook, you will practice:
- Python variables and basic data types
- Lists, indexing, slicing and mutation
- Dictionaries and nested dictionaries
- Reusable Python functions
- Basic file input and output
- Assertions and data validation
- CSV data ingestion with Pandas
- Data filtering and aggregation with Pandas
- DuckDB SQL in a notebook environment
- SQL tables, SELECT statements and JOINs
- GROUP BY aggregations
- SQL window functions and ranking
- Moving between Pandas DataFrames and SQL tables
- Python and SQL result-parity verification
- Final smoke testing
Why Start with Python, Pandas and SQL?
Many people preparing for Data Engineering interviews study Python and SQL separately. That can help with individual interview questions, but real data engineering work requires you to connect the pieces.
A data engineer needs to understand the complete flow from input data to transformation, validation and output.
That is why this notebook deliberately connects Python, Pandas and SQL instead of treating them as isolated topics.
The notebook starts with Python fundamentals, moves into Pandas for data processing, and then uses DuckDB to perform analytical SQL operations. Finally, it verifies that equivalent transformations produce consistent results.
Getting Started with the Kaggle Environment
This notebook is designed to run in the Kaggle Notebooks environment. No local deployment is required for this exercise.
The first cells verify the Python runtime and confirm that Pandas and DuckDB can be imported successfully. The executed notebook also reports the versions available in the Kaggle environment.
The Sales Transactions dataset is attached to the notebook through Kaggle's data interface. The notebook searches the available Kaggle input locations for sales_transactions.csv rather than depending on a hard-coded local file path.
This is a useful reproducibility habit. When an expected input is missing, the notebook stops with a clear message instead of silently generating replacement data.
Python Fundamentals for Data Engineers
The first technical section covers Python concepts that are directly relevant to data engineering.
We work with variables and basic data types, followed by lists, indexing, slicing and mutation. These concepts are simple, but they form the foundation for manipulating configuration values, identifiers, column collections and other pipeline data structures.
Dictionaries and nested dictionaries are also introduced using a small record representation. This provides a natural bridge from Python data structures to the structured records encountered in data pipelines.
We then create a reusable format_column_name() function. The function normalizes a column name by removing unnecessary whitespace, converting it to lowercase and replacing spaces with underscores.
This illustrates a basic engineering principle: if a transformation needs to be applied repeatedly, put the logic in a reusable function rather than copying the same code throughout a pipeline.
File I/O and Data Validation
The notebook also demonstrates basic file input and output using a file in the Kaggle working directory.
More importantly, it introduces assertions as a simple form of executable validation.
For example:
assert record_count > 0, "Record count must be positive."The idea is straightforward: important assumptions should be checked rather than merely assumed.
This is the beginning of a defensive engineering mindset. Later in the course, these ideas will become more sophisticated as we move into testing, data quality and pipeline reliability.
Working with the Sales Transactions Dataset
The practical part of the notebook uses a small Sales Transactions dataset containing transaction identifiers, dates, customer names, amounts and categories.
Before processing the data, the notebook checks that the expected columns are present. Customer names are normalized, and the data is then used for filtering and aggregation exercises.
For example, we identify higher-value transactions and filter transactions belonging to the Grocery category. We also calculate customer-level spending and category-level summaries.
The notebook includes a small end-to-end Python workflow that follows a simple pattern:
Ingest → Transform → Validate → Produce Output
This pattern will become increasingly important as the course progresses toward more production-oriented data engineering workflows.
SQL with DuckDB
After working with the data in Pandas, we move to SQL using DuckDB.
DuckDB provides an analytical SQL engine that works directly inside the notebook environment. For this exercise, an in-memory database is sufficient, so there is no need to manage a separate database server.
The notebook creates a transactions table from the Sales Transactions CSV file and then introduces several important SQL concepts.
SELECT and JOIN
We create a small category metadata table and use a LEFT JOIN to combine transaction data with category information.
This provides a practical example of joining transactional data with related metadata, a pattern that appears frequently in real data pipelines and analytical workloads.
GROUP BY and Aggregation
Next, we use GROUP BY with SUM() and COUNT() to calculate total spending and transaction counts for each customer.
The notebook also normalizes customer names during the SQL aggregation, which demonstrates that data-cleaning logic is not limited to Pandas.
Window Functions
Window functions are another important Data Engineering interview topic.
The notebook uses RANK() with PARTITION BY to rank transactions by amount within each category.
This also provides a useful interview distinction: GROUP BY reduces multiple rows into summary rows, while a window function performs calculations across related rows without collapsing the original row-level result.
Python and SQL Result Parity
One of the most useful exercises in this notebook is comparing equivalent transformations implemented in Pandas and SQL.
The same aggregation is calculated using both interfaces, and the resulting DataFrames are compared with:
pd.testing.assert_frame_equal()This is more reliable than simply looking at two outputs and deciding that they appear identical.
The notebook programmatically verifies that the results agree. The final output confirms:
PASS: Pandas and SQL results are equivalent.
This is a small but important step toward production thinking. Data-processing code should not only run. Its assumptions and results should also be verifiable.
Run the Public Kaggle Notebook
The complete notebook is publicly available on Kaggle and can be run directly from the following link:
Data Engineering Interview Preparation #1: Python and SQL Basics on Kaggle
The notebook has been executed successfully with the Sales Transactions dataset attached. It also includes a final smoke test that verifies the dataset, required columns and transaction aggregation.
This makes the notebook more than a collection of code examples. It is a runnable interview-preparation asset that learners can execute and use as a starting point for their own practice.
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 Data Engineering, Gen AI, Agentic AI and ML) in LinkedIn at https://www.linkedin.com/in/inderpsingh/
- Production-oriented Data Engineering templates with playbooks
- Working Data Engineering projects for your portfolio
- Deep-dive hands-on Data Engineering Training
- Data Engineering resume updates
Interview Checkpoint
Before moving to the next notebook, try answering these questions without looking at the code:
- When would you use SQL instead of Pandas?
- What is the purpose of a smoke test?
- How is
GROUP BYdifferent from a window function? - What does a
LEFT JOINpreserve? - Why should data-processing code be validated rather than simply executed?
- How would you make this notebook reproducible for another engineer?
These questions are deliberately connected to the implementation. The objective is not just to memorize definitions, but to explain the concepts using working code.
From Interview Preparation to Production Thinking
This series is designed to progressively move from fundamentals toward production-oriented Data Engineering skills.
Notebook #1 establishes the foundation with Python, Pandas, SQL, DuckDB and basic validation.
The subsequent notebooks will build on this foundation with reproducible ingestion, data transformations, testing, chunked processing, checkpointing, data quality, warehouse modelling and analytical data marts.
The long-term objective is to give learners something more valuable than a list of interview questions. You should be able to explain a coherent engineering project, demonstrate working code, discuss the trade-offs behind your choices and connect your project experience to a target job description.
That is the difference between preparing only for an interview and developing the ability to think like a Data Engineer.
Continue the Data Engineering Interview Preparation Series
Keep this notebook as the first progressive asset in your Data Engineering interview-preparation portfolio. Run it, experiment with the code, answer the interview checkpoint questions, and then move on to the next stage of the series.
The broader learning ecosystem includes runnable Kaggle notebooks, GitHub repositories and playbooks, technical articles and courses, video tutorials, and LinkedIn resources.
The goal is simple: learn the concept, run the code, validate the result, explain the design, and build a portfolio you can discuss confidently in an interview.
Comments
Post a Comment