Question 1

Explain the different types of SQL JOINs and when you would use each one.

Accepted Answer

SQL JOINs combine rows from two or more tables based on a related column. Understanding which JOIN to use is fundamental for querying relational databases effectively.

INNER JOIN returns only the rows where there is a match in both tables. If you have a customers table and an orders table, an INNER JOIN on customer_id returns only customers who have placed orders. Customers without orders and orders without valid customers are excluded.

LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, NULL values appear for the right table colum

Question 2

What is the difference between a primary key and a foreign key? Why are they important in database design?

Accepted Answer

Primary keys and foreign keys are fundamental constraints that enforce data integrity and establish relationships between tables in relational databases.

A primary key uniquely identifies each row in a table. Every table should have a primary key. The primary key must contain unique values and cannot contain NULL. A table can have only one primary key, but it can consist of multiple columns, called a composite key. Common examples include auto-incrementing integers like user_id or natural keys like email addresses.

A foreign key is a column in one table that references the primary key of

Question 3

What is the difference between a Pandas Series and a DataFrame? When would you use each?

Accepted Answer

A Pandas Series and DataFrame are the two primary data structures in the Pandas library, and understanding their differences is fundamental to working with data in Python.

A Series is a one-dimensional labeled array that can hold data of any type. Think of it as a single column of data with an index. You can create a Series from a list, dictionary, or NumPy array. Each element in a Series has a label called an index, which allows for fast lookups and alignment during operations.

A DataFrame is a two-dimensional labeled data structure with columns of potentially different types. Think of

Question 4

Why would you use NumPy arrays instead of Python lists for data processing? What are the main advantages?

Accepted Answer

NumPy arrays offer significant advantages over Python lists for data processing, making them essential for any data engineering work.

Memory efficiency is the first advantage. NumPy arrays store elements in contiguous memory blocks with a fixed data type. This means a NumPy array of integers uses far less memory than a Python list of integers. Python lists store pointers to objects scattered in memory, which adds overhead. For large datasets, this memory difference becomes substantial.

Performance and speed is where NumPy really shines. NumPy operations are implemented in C and optimized

Question 5

What does ETL stand for, and can you explain each step of the process?

Accepted Answer

ETL stands for Extract, Transform, and Load. It is a data integration process that moves data from source systems into a target system, typically a data warehouse. Each step serves a specific purpose in preparing data for analysis.

The Extract step involves reading data from various source systems. These sources can include relational databases, APIs, flat files like CSV or JSON, spreadsheets, or cloud services. During extraction, data is pulled from the source and temporarily stored in a staging area. The goal is to retrieve the data without impacting the performance of source systems, oft

Question 6

What is the difference between ETL and ELT? When would you choose one over the other?

Accepted Answer

ETL and ELT are both data integration patterns, but they differ in where the transformation happens and are suited for different scenarios.

In ETL, transformation happens before loading. Data is extracted from sources, transformed in a separate processing layer or ETL tool, and then loaded into the target system in its final form. The transformation engine is typically outside the target database. Traditional ETL tools like Informatica, Talend, and SSIS follow this pattern.

In ELT, data is loaded first in its raw form, and transformation happens afterward inside the target system. Data i

Question 7

What is a data pipeline, and why are data pipelines important in modern data architecture?

Accepted Answer

A data pipeline is a series of automated steps that move data from one or more sources to a destination, typically applying transformations along the way. Think of it as an assembly line for data, where each station performs a specific operation before passing the data to the next stage.

A typical data pipeline includes several components. Sources are where data originates, such as databases, APIs, files, streaming platforms, or IoT devices. Processing steps clean, transform, enrich, or aggregate the data. The destination is where processed data lands, often a data warehouse, data lake, or

Question 8

What is the difference between batch processing and stream processing? When would you use each?

Accepted Answer

Batch processing and stream processing are two fundamental approaches to handling data, each suited to different requirements.

Batch processing handles data in discrete chunks or batches at scheduled intervals. Data accumulates over a period, such as an hour or a day, and then the pipeline processes all of it at once. The processing runs on a schedule, like every night at midnight, and completes before the next batch begins. Traditional ETL workflows typically use batch processing.

Stream processing handles data continuously as it arrives, processing each event or small groups of events

Data Engineer

Topics

SQL & Database Fundamentals

Python for Data

ETL/ELT Concepts

Data Pipelines

Mock Interview

Quick Stats