Data Engineer interview questions for freshers

All three functions number the rows inside a window. They differ only in how they treat ties.

• ROW_NUMBER() gives a unique sequence (1,2,3,4). It breaks ties in any order.
• RANK() gives tied rows the same number, then skips numbers (1,2,2,4).
• DENSE_RANK() gives tied rows the same number with no gaps (1,2,2,3).

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

Use ROW_NUMBER to remove duplicates. Use DENSE_RANK for "Nth highest" questions. Use RANK when the gaps after ties matter.

What they are checking: Do you know how each function treats ties, and can you pick the right one?

The safest way is DENSE_RANK. It handles ties and works for any Nth value.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

Other ways to do it:

-- Offset approach
SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET 1 LIMIT 1;

-- Subquery approach
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

Note one difference. The window version returns no row (not NULL) when there is no second value. The MAX version returns NULL. Say which result the caller wants.

What they are checking: Can you handle ties, and do you know more than one way?

GROUP BY collapses rows into one row per group. A window function calculates a value across related rows but keeps every input row.

-- One row per department
SELECT dept, AVG(salary) FROM employees GROUP BY dept;

-- Every employee row, plus the department average beside it
SELECT name, dept, salary,
       AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;

• PARTITION BY sets the group. ORDER BY sets the order inside the group. A frame (ROWS BETWEEN ...) sets which rows are used.
• Window functions run after WHERE, GROUP BY and HAVING. So you cannot filter on them in WHERE. Put the query in a CTE or subquery instead.

What they are checking: Do you know that a window keeps rows and GROUP BY collapses them, and do you know the run order?

Number the duplicates with ROW_NUMBER over the columns that make a row a duplicate. Then delete every row after the first.

DELETE FROM customers
WHERE ctid IN (
  SELECT ctid FROM (
    SELECT ctid,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

ctid is the physical row id in PostgreSQL. It works even when the table has no primary key. In other databases, use the primary key or another unique id.

For a very large table, copying is often faster. Build a clean copy with SELECT DISTINCT ON (email) ... or the ROW_NUMBER filter, then swap it in. After that, add a unique constraint so the problem cannot come back.

What they are checking: Can you remove duplicates safely and stop them coming back?

Group by the columns that define a duplicate. Keep only the groups with more than one row.

SELECT email, COUNT(*) AS cnt
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

To see the full duplicate rows, join back or use a window count:

SELECT *
FROM (
  SELECT c.*, COUNT(*) OVER (PARTITION BY email) AS n
  FROM customers c
) t
WHERE n > 1;

Be clear about what a duplicate means. It could be the same email, or the same email and name. Also think about NULLs. For a unique constraint, NULLs are not equal to each other. But GROUP BY puts all NULLs in one group.

What they are checking: Can you find duplicates and say exactly what a duplicate is?

LAG(col, n, default) returns the value from n rows before the current row, in the window order. LEAD returns the value from n rows after. If there is no default, a missing row gives NULL.

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS pct_change
FROM monthly_revenue;

Notes:

• Use PARTITION BY to compare inside a group, for example per product.
• NULLIF(..., 0) avoids divide-by-zero errors.
• LAG replaces a self join for "previous row" logic. It is usually faster and easier to read.

What they are checking: Can you compare neighbouring rows without a self join?

Rank the rows inside each group with a window function. Then filter in an outer query.

SELECT *
FROM (
  SELECT category, product, sales,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

Pick the ranking function with care. ROW_NUMBER returns exactly 3 rows per group. RANK or DENSE_RANK may return more when there are ties. Say which result the business wants.

PostgreSQL also supports CROSS JOIN LATERAL with ORDER BY ... LIMIT 3. This can be faster when there is an index on (category, sales) and there are few categories.

What they are checking: Can you solve the classic top-N-per-group problem and talk about ties?

• INNER JOIN: only rows that match in both tables. For example, orders with their customers.
• LEFT JOIN: every row from the left table, with NULLs where the right table has no match. For example, all customers and their orders, including customers with no orders.
• RIGHT JOIN: the mirror of LEFT. Most people swap the tables and use a LEFT JOIN instead.
• FULL OUTER JOIN: all rows from both sides, with NULLs where there is no match. Use it to compare two systems and find records missing from either.

SELECT c.id, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

A common bug: filtering the right table in WHERE (WHERE o.status = 'paid') turns a LEFT JOIN into an inner join. Put that condition in the ON clause instead.

What they are checking: Do you know how each join works, and the WHERE versus ON trap?

Use SUM as a window function with ORDER BY. This gives a running total.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

• Add PARTITION BY customer_id to restart the total for each customer.
• State the frame clearly. The default frame with ORDER BY is RANGE ... CURRENT ROW. It treats rows with the same order value as peers and gives them the same running total. ROWS counts physical rows.
• Add a tiebreaker column to the ORDER BY so the result is the same every time.

What they are checking: Do you know window frames and the RANGE versus ROWS trap?

A common table expression (CTE) is a named temporary result. You define it with WITH. It exists only for that one statement.

WHERE filters single rows before grouping. HAVING filters groups after the aggregation.

Use an anti-join. NOT EXISTS is the safest form and is usually the fastest.

Both stack the results of two queries. The queries need the same number of columns and compatible types.

• UNION removes duplicate rows. This needs a sort or hash step.
• UNION ALL keeps every row and is faster.

NULL means unknown. So any comparison with it gives unknown, not true or false.

• DELETE: removes rows. It supports WHERE, is logged row by row, fires row triggers, and can be rolled back. Vacuum frees the space later.
• TRUNCATE: removes all rows fast by dropping the data files. It has no WHERE clause. It resets identity counters with RESTART IDENTITY. In PostgreSQL it can be rolled back. It takes an exclusive lock.
• DROP: removes the whole table, including its structure, indexes and constraints.

x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL. Comparing anything with NULL gives UNKNOWN. So the whole condition is never true, and the query returns no rows. This is three-valued logic.

Use a window aggregate without ORDER BY for the total. Use NTILE for buckets.

Join the table to itself with two aliases. Use a LEFT JOIN so employees without a manager still show up.

CASE WHEN adds conditional logic inside a query. COALESCE returns the first argument that is not NULL.

• COUNT(*) counts all rows, including rows with NULLs.
• COUNT(col) counts rows where col is not NULL.
• COUNT(DISTINCT col) counts distinct values that are not NULL.

ETL changes the data before it loads it into the target. ELT loads the raw data into the target first and changes it there.

ELT became common because warehouses such as Snowflake and BigQuery scale compute on its own. They can run SQL transforms cheaply. Keeping the raw data lets you reprocess it when business rules change.

ETL still fits in two cases. One is when sensitive fields must be masked before they land. The other is when the target cannot do the transformation.

What they are checking: Whether you can explain the shift and the trade-offs

Batch processes a fixed set of data on a schedule, such as hourly or daily. Streaming processes a never-ending flow of events as they arrive.

• Latency: batch takes minutes to hours. Streaming takes seconds or less.
• Complexity: streaming must handle event order, late events, state, and exactly-once results. It is harder to build and run.
• Cost: batch can use cheap spot compute for short windows. Streaming runs all the time.
• Examples: nightly sales reports use batch. Fraud detection and live dashboards use streaming.
• Tools: Airflow with Spark or SQL for batch. Kafka with Flink, Spark Structured Streaming or Kinesis for streaming.

Pick the slowest speed the business can accept. Many systems use micro-batches as a middle path, for example loads every 1 minute.

What they are checking: Whether you choose based on the speed needed, not fashion

A data pipeline is an automatic flow that moves data from sources to a destination. It also prepares the data for use.

Typical stages:

1. Ingest: pull data from databases, APIs, files, or event streams.
2. Land raw: store the data unchanged (in object storage or a raw schema).
3. Clean and transform: fix types, remove duplicates, join tables, apply business rules.
4. Model: build analytics tables such as facts and dimensions.
5. Serve: give the data to BI tools, ML features, or applications.
6. Orchestrate, monitor and test: scheduling, retries, alerts, data quality checks.

A good pipeline is repeatable and idempotent (running it twice gives the same result). It is also easy to watch and well documented.

Give one example. Orders from PostgreSQL land in S3 each day. dbt transforms them, and they load into a warehouse for dashboards.

What they are checking: Whether you see the pipeline from start to end, including operations

A full load reloads the whole dataset each run. An incremental load moves only the new or changed records since the last run.

• Full load: simple and fixes itself, but slow and costly as data grows. Fine for small reference tables.
• Incremental: fast and cheap, but you need a reliable way to detect changes and handle deletes.

Common ways to detect changes:

• An updated_at timestamp.
• An id that always increases.
• A log-based CDC feed (reads the database change log).
• File arrival partitions.

SELECT * FROM orders
WHERE updated_at > :last_watermark
  AND updated_at <= :run_end;

Save the watermark (the last point you loaded) only after the load succeeds. Use a small overlap window, for example read the last 10 minutes again. Use an upsert (update or insert). Then late commits are not missed and duplicates do no harm.

What they are checking: Whether you know watermarks, deletes and overlap

Check at the boundaries: after ingestion and before publishing.

• Completeness: row counts match the source, required columns are not null.
• Uniqueness: primary or business keys have no duplicates.
• Validity: types, formats, allowed values, ranges (age between 0 and 120).
• Referential integrity: every fact foreign key exists in its dimension.
• Freshness: the latest timestamp is inside the expected window.
• Reconciliation: totals match the source system, and sums agree across layers.
• Anomaly checks: sudden drops or spikes in volume compared with recent days.

Tools: dbt tests, Great Expectations, Soda, Deequ.

Decide how serious each failure is. Block the pipeline for critical failures. Only warn for small ones. Put bad rows in quarantine instead of dropping them silently. Send alerts to the owner and keep the results to see trends.

What they are checking: Whether you build quality gates, not just move data

CSV is a plain text format that stores data row by row. It has no schema or types. Parquet is a binary format that stores data column by column. It has a built-in schema, compression and statistics.

Analytics queries touch a few columns of many rows. Columnar reads save I/O, and compression works well on similar values. So Parquet is preferred for analytics. CSV is still common for sharing with vendors and for small files.

What they are checking: Whether you know why columnar formats win for analytics

Plan for it, because source schemas change without warning.

Orchestration schedules tasks and coordinates them with dependencies, retries and monitoring. Cron only starts a command at a set time.

• DAG: a directed acyclic graph. It defines tasks and their dependencies, with a schedule.
• Task: one unit of work in the DAG. A task instance is that task for one run.
• Operator: a template for a type of task (BashOperator, PythonOperator, a SQL or Spark operator). The TaskFlow API lets you write tasks as decorated...

Row stores keep all fields of a record together. Column stores keep all values of one column together.

dbt (data build tool) is the transform step in ELT. You write SELECT statements. dbt builds them as tables or views in the warehouse. It also works out dependencies, runs tests, and creates documentation.

Kafka is a distributed, durable log that only adds new records at the end. It moves event streams between systems.

It organises a lakehouse into layers. Quality goes up with each layer.

Many tiny files (a few KB each) in a data lake slow everything down. Listing and opening each file adds work. Query engines create too many tasks. The metadata grows too big.

Causes: frequent streaming micro-batches, too many partitions, and many small writes.

Lineage shows where data came from and what depends on it. It shows which source tables and jobs made a column, and which dashboards use it. Observability watches the health of the data and the pipelines.

A scheduler starts jobs at fixed times. An event-driven trigger starts them when something happens.

Event time is when the event actually happened. Processing time is when your system handles it.

They differ because of network delays, mobile devices going offline, or retries. A purchase at 23:59 may arrive at 00:03 the next day.

A staging table is a temporary or middle area. Data lands there before it is checked and merged into the final target.

Compare simple measures that would show lost or duplicated data.

A cycle would create a circular dependency. Task A would wait for B, and B would wait for A. Then no task could run first. A directed acyclic graph guarantees a valid run order (a topological order). It also guarantees that every run finishes.

Decide a policy for each field and apply it the same way every time.

OLTP runs the day-to-day transactions of an app. OLAP supports analysis over large amounts of old data.

Keep them separate. Heavy analysis queries on the OLTP database slow the app down. Data moves from OLTP to the warehouse through ETL or ELT.

What they are checking: Basic data platform words.

Both are dimensional models. Each has a central fact table with dimension tables around it.

• Star schema: each dimension is one flat table, joined directly to the fact. It has fewer joins, simpler queries and is faster for BI. Some data is repeated.
• Snowflake schema: dimensions are split into sub-tables (product to category to department). It repeats less data and makes hierarchies easier to maintain. But it needs more joins and the queries are harder.

-- Star: one join to get category
SELECT d.category, SUM(f.amount)
FROM fact_sales f JOIN dim_product d ON d.product_key = f.product_key
GROUP BY d.category;

Columnar warehouses make storage cheap, so a star schema is the better choice in most cases. Use snowflaking only when many dimensions share a hierarchy, or when the hierarchy changes often.

What they are checking: Can you compare the two and explain why star is the default?

A fact table stores measurable events at a chosen grain (the level of detail of one row). A dimension table stores the descriptive details used to filter and group those events.

• Fact: numbers (quantity, amount, duration) plus foreign keys to dimensions. It is very large and narrow, for example one row per order line.
• Dimension: attributes such as customer name, city, product category and date. It is smaller, wide and mostly text.

Example: fact_sales(date_key, product_key, customer_key, quantity, amount) with dim_date, dim_product and dim_customer.

A business question maps neatly: "sales amount (fact) by product category and month (dimension attributes)". Always state the grain of the fact table first. The grain decides which dimensions and measures make sense.

What they are checking: Core words of dimensional modeling.

SCD means slowly changing dimension. It is a way to handle dimension attributes that change over time.

• Type 1: overwrite the old value. There is no history. Use it for corrections, or when history does not matter.
• Type 2: add a new row for each change, with valid_from, valid_to and is_current. It keeps full history. Facts link to the version that was valid at that time through the surrogate key. This is the most common type when you need history.
• Type 3: keep limited history in extra columns (current_city, previous_city). It is simple, but it stores only one earlier value.

Also good to know: Type 0 (never changes), Type 4 (history in a separate table) and Type 6 (a mix of 1, 2 and 3).

Example of Type 2:

What they are checking: Can you describe the SCD types and compare them?

A surrogate key is an ID made by the system. It has no business meaning and is usually an integer or a hash. It is used as the primary key of a dimension.

Reasons to use it:

• It supports SCD Type 2. One natural key (customer C1) has several rows, and each row needs its own key.
• It protects the warehouse from source changes. Natural keys can be reused, change format, or differ between systems.
• It works with many sources. You can combine systems that identify customers in different ways.
• Joins are smaller and faster than with long composite or string keys.
• It allows special members, such as -1 for Unknown.

Fact tables store the surrogate key. During load, look up the key by natural key and business date. Keep the natural key as an attribute so you can trace the data.

What they are checking: Do you know why SCD2 needs surrogate keys?

• Data warehouse: structured, modelled data in a store that checks the schema when data is written (schema-on-write). It is built for SQL analysis and BI. Quality and governance are high. But it is usually limited to structured data, and storage costs more.
• Data lake: raw data of any type (files, logs, images, JSON) in cheap object storage. The schema is applied when data is read (schema-on-read). It is flexible and cheap. Without governance it can turn into a swamp. Its transaction support is weak.
• Lakehouse: lake storage plus open table formats (Delta, Iceberg, Hudi). These add ACID transactions, schema checks and fast SQL. One platform serves BI, ML and streaming.

Many companies use a lake to land raw data. They then use a warehouse or lakehouse layer for clean, ready-to-use data.

What they are checking: Can you compare the three designs and say when to use each?

Both approaches build an enterprise data warehouse, but in opposite directions.

• Kimball (bottom-up): build data marts for each business process as star schemas, using conformed dimensions. The warehouse is the sum of the marts. Value arrives fast. It is easy for users and flexible.
• Inmon (top-down): first build a normalised (3NF) enterprise data warehouse as the single source of truth. Then create department data marts from it. Integration and consistency are strong. But it is slower and costs more at the start.

Most modern teams use Kimball-style marts in the warehouse. Often a raw and staged layer sits in front, and it works like an Inmon integration layer.

What they are checking: Do you know both ideas and their trade-offs?

The grain says exactly what one row in the fact table means. For example: "one row per order line item" or "one row per account per day".

Why it matters:

• It decides which dimensions can attach and which measures are valid.
• Mixing grains in one table causes double counting and wrong totals.
• It sets the size of the table and how detailed the analysis can be.

Process: declare the business process, then the grain, then the dimensions, then the facts. Prefer the lowest practical (atomic) grain. You can always add up detailed data, but you cannot split summary data back into detail. Write the grain in the table description. Test it with a uniqueness check on the grain columns.

What they are checking: Do you start modeling from the grain?

Snowflake separates storage, compute and services.

Warehouses are read-heavy and run big summary queries. Fewer joins give faster and simpler queries. A denormalised dimension keeps related attributes in one table (product, category and brand together). Storage is cheap in columnar systems, because they compress repeated values well.

A common layered design:

Normal forms reduce repeated data and update errors in relational design.

A data mart is a small part of the warehouse data. It focuses on one department or business area, such as sales, finance or marketing. The data warehouse is the company-wide store that joins all areas.

• Degenerate dimension: an identifier with no attributes of its own. It is stored in the fact table, such as an order number or invoice number. It is used to group line items.
• Junk dimension: puts several flags with few values (is_gift, payment_type, channel) into one small dimension. This avoids many tiny tables...

• Additive: can be summed across all dimensions. Examples are sales amount and quantity.
• Semi-additive: can be summed across some dimensions, but not across time. Account balance or inventory level can be summed across accounts. Summing it across days makes no sense. For time, use the average or the last value. -...

A date dimension is a ready-made table with one row per calendar day. It has many attributes that SQL date functions do not give in a consistent way.

MPP spreads one query across many nodes. Each node works on its own slice of data at the same time. Columnar storage keeps each column together.

An ODS is a database that joins current or near-real-time data from several operational systems. It is used for operational reports and lookups. It sits before the warehouse or next to it.

Apache Spark is a distributed engine that processes data in memory. It works for batch jobs, SQL, streaming and machine learning.

Why it is usually faster than MapReduce:

• It keeps temporary data in memory. MapReduce writes every stage to disk (HDFS).
• It builds a DAG (a graph of steps) for the whole job and optimizes it. MapReduce has fixed map then reduce steps.
• Tasks run as threads inside long-lived executors, so no new JVM starts for each task.
• Catalyst and Tungsten optimize SQL plans and memory layout.

Spark still spills to disk when data does not fit. It can read and write HDFS, S3 and other stores. It is only a compute engine, not a storage system.

What they are checking: Whether you know the real technical reasons, not just the slogan 'in memory'

A Spark application has one driver, many executors, and a cluster manager that gives out resources.

• Driver: runs main() and creates the SparkSession. It turns your code into a logical plan and a DAG, splits it into stages and tasks, and schedules them.
• Executors: JVM processes on worker nodes. They run tasks, hold cached data, and write shuffle files.
• Cluster manager: gives out resources. Examples are Standalone, YARN, Kubernetes (and Mesos in older setups).

Flow: the driver asks the cluster manager for executors. Then it sends tasks to them and collects the results. If the driver dies, the application dies. Losing an executor is fine, because lost tasks are recomputed from lineage.

What they are checking: Whether you can say who plans the work and who runs it

A transformation describes a new dataset made from an existing one. It is lazy, so it does not run yet. An action starts the work and returns a result or writes output.

• Transformations: select, filter, map, withColumn, join, groupBy, union. They return a new DataFrame or RDD and run nothing.
• Actions: count, collect, show, take, first, write, foreach. Each one starts a job.

df2 = df.filter(df.age > 30)   # nothing runs
print(df2.count())             # job runs here

Transformations are also split into narrow and wide (shuffle) types. Each action runs the whole chain again unless the data is cached.

What they are checking: The basic words of lazy evaluation

Spark saves transformations as a plan. It runs them only when you call an action.

Why it matters:

• The optimizer sees the whole plan. It can push filters down, skip unused columns, and combine steps (Catalyst).
• Narrow transformations run together in one pass over the data.
• Nothing is computed for results you never use.

Things to remember: an error in a transformation may only show up at the action. Two actions on the same DataFrame compute it twice unless you use cache().

What they are checking: Do you know why Spark code runs late, and what that means for debugging

All three are distributed collections. They differ in how much structure and optimization they have.

In PySpark you almost always use DataFrames. They run on the optimized engine and do not serialize each row through Python. Use RDDs only when you need low-level control or have unstructured data. A DataFrame is a Dataset of Row.

What they are checking: Whether you know why DataFrames are the default

A narrow transformation needs data from only one input partition for each output partition. A wide transformation needs data from many partitions, so it causes a shuffle.

• Narrow: map, filter, select, withColumn, union, coalesce. No data moves, and the steps run together.
• Wide: groupBy, join (unless broadcast or co-partitioned), distinct, repartition, orderBy, reduceByKey.

Each wide transformation creates a stage boundary. Shuffles write to local disk and move data over the network. They are the main cost in most jobs, so optimize them first.

What they are checking: Do you see the link between transformation type, shuffle and stage boundary

A shuffle moves data between partitions so that rows with the same key end up together. Wide transformations cause it.

Why it costs a lot:

• The map side writes shuffle files to local disk.
• The reduce side fetches blocks over the network.
• Data must be serialized and compressed, and it may spill to disk.
• Skew (uneven data) can make a few tasks do most of the work.

How to reduce it: filter early and select only the columns you need. Use broadcast joins and pre-aggregate. Avoid extra repartition calls. Tune spark.sql.shuffle.partitions (default 200).

What they are checking: Do you know the main performance bottleneck and how to attack it

They are three levels of the execution plan.

• Job: created by one action (for example count() or write).
• Stage: a group of tasks that can run without a shuffle. A new stage starts at each shuffle.
• Task: the smallest unit. There is one per partition per stage, and it runs on one executor core.

Example: read -> filter -> groupBy -> count is one job with two stages. Stage 1 reads, filters and does a partial aggregation. Stage 2 reads the shuffled data and finishes the aggregation. The Spark UI shows all three, so start debugging there.

What they are checking: Whether you can match code to what you see in the Spark UI

Both change the number of partitions. repartition does a full shuffle and can increase or decrease the number. coalesce merges existing partitions without a full shuffle and can only decrease the number.

• repartition(n) gives partitions of roughly equal size. It can also take columns, like repartition(200, "user_id").
• coalesce(n) is cheaper, but partitions may be uneven.

df.repartition(100)      # shuffle, even sizes
df.coalesce(10)          # no shuffle, fewer files

Typical use: coalesce before writing, to reduce small files. repartition is for more parallelism, or to put the same keys together before a join or partitioned write.

What they are checking: A classic tuning question with a trade-off

A broadcast join sends a small table to every executor. The large table is then joined locally, with no shuffle.

from pyspark.sql.functions import broadcast
result = big_df.join(broadcast(small_df), "country_code")

Use it when one side is small enough to fit in executor memory. Spark does it automatically below spark.sql.autoBroadcastJoinThreshold (default 10 MB). The other choice is a sort-merge join, which shuffles both sides. Do not broadcast a large table. It can cause out-of-memory errors on the driver or executors.

What they are checking: The most common join optimization

cache() is persist() with the default storage level. persist() lets you choose the level.

• DataFrame cache() uses MEMORY_AND_DISK.
• Other options include persist(StorageLevel.DISK_ONLY), MEMORY_ONLY and MEMORY_AND_DISK_SER.

Use spark.read and df.write with a format.

The DAG (directed acyclic graph) is the graph of steps made from your transformations. The driver's DAG scheduler builds it when you call an action.

Both are RDD wide operations on key-value pairs. reduceByKey first combines values inside each partition (map-side combine). Then it shuffles only the partial results. groupByKey shuffles every value and then groups them. This uses much more network and memory.

Parquet is columnar, compressed and stores a schema. Analytics queries read less data and run faster.

A UDF is a user-defined Python function that you use on DataFrame columns.

Spark supports inner, left (outer), right (outer), full (outer), left_semi, left_anti, and cross.

• left_semi: rows of the left side that have a match. It returns only left columns, with no duplicates from the right.
• left_anti: rows of the left side with no match. Good for 'new records only'.

Use isNull, na.fill, na.drop and coalesce.

collect() pulls all rows from all executors into the driver's memory as a Python list. On a large DataFrame this can cause a driver out-of-memory error and a long network transfer.

map returns exactly one output element for each input element. flatMap returns zero or more output elements for each input, and joins them into one flat list.

The Spark UI shows how the job ran. Use port 4040 for a running app, and the history server afterwards.

All three combine DataFrames, but in different ways.

• pd.merge(left, right, on=..., how=...): works like a SQL join on column values. how can be inner, left, right or outer.
• df.join(other): a shortcut that joins on the index by default.
• pd.concat([df1, df2], axis=0|1): stacks frames on top of each other (rows) or side by side (columns). It lines them up by index. It does not match keys.

import pandas as pd
c = pd.DataFrame({"id": [1, 2], "name": ["A", "B"]})
o = pd.DataFrame({"id": [1, 1, 3], "amt": [10, 20, 30]})
print(pd.merge(c, o, on="id", how="left"))
print(pd.concat([c, c], ignore_index=True))

Use validate="one_to_many" in merge to catch duplicate keys by mistake.

What they are checking: Everyday pandas joins

groupby splits the data by one or more keys, runs an aggregation on each group, and joins the results back (split, apply, combine).

import pandas as pd
df = pd.DataFrame({"dept": ["A", "A", "B"], "sal": [10, 20, 30]})
out = df.groupby("dept").agg(total=("sal", "sum"), avg=("sal", "mean"), n=("sal", "size")).reset_index()
print(out)

Named aggregation (above) gives clean column names. size counts all rows, including nulls. count counts only non-null values. Use transform when you want a result with the same shape as the original rows. For example: df["pct"] = df.sal / df.groupby("dept").sal.transform("sum").

What they are checking: Core pandas skills

First measure the missing values. Then decide for each column whether to drop, fill or flag them.

import pandas as pd, numpy as np
df = pd.DataFrame({"a": [1, np.nan, 3], "b": ["x", None, "z"]})
print(df.isna().sum())               # count nulls per column
df2 = df.dropna(subset=["a"])         # drop rows
df3 = df.fillna({"a": df["a"].median(), "b": "unknown"})
df["a"] = df["a"].ffill()              # forward fill for time series

The right choice depends on what the value means. A missing amount might be 0. A missing key should be rejected. Keep an is_missing flag if the gap carries meaning. Never fill values silently in a pipeline. Log how many values you changed.

What they are checking: Data cleaning judgment

They differ in order, whether you can change them, and how fast lookup is.

What they are checking: Python basics

A generator gives values one at a time, only when asked (lazily). It does not build the whole collection in memory.

def read_lines(path):
    with open(path) as f:
        for line in f:
            yield line.rstrip("\n")

def parse(lines):
    for ln in lines:
        yield ln.split(",")

for row in parse(read_lines("data.csv")):
    pass  # process one row at a time

Chained generators make a streaming pipeline that uses a constant amount of memory. You can process files bigger than RAM. There are two downsides. A generator can be read only once. You also cannot use an index on it.

What they are checking: Memory-efficient processing

Use pandas with the pyarrow engine (or fastparquet). You can also use PyArrow directly.

import pandas as pd
df = pd.DataFrame({"id": [1, 2], "city": ["KTM", "PKR"]})
df.to_parquet("cities.parquet", engine="pyarrow", compression="snappy")
back = pd.read_parquet("cities.parquet", columns=["id"])

Parquet stores data by column, compresses it, and keeps the types. So reading only some columns is fast. For partitioned datasets use df.to_parquet("out/", partition_cols=["city"]). For files bigger than memory, read one row group at a time with pyarrow.parquet.ParquetFile(...).iter_batches().

What they are checking: Column-based file handling

Sort by the timestamp. Then call drop_duplicates on the key and keep the last row.

import pandas as pd
df = pd.DataFrame({"id": [1, 1, 2], "ts": ["2025-01-01", "2025-02-01", "2025-01-05"], "v": ["a", "b", "c"]})
latest = (df.sort_values("ts")
            .drop_duplicates(subset="id", keep="last"))
print(latest)

Another way is groupby: df.loc[df.groupby("id")["ts"].idxmax()]. Convert timestamps with pd.to_datetime first. Otherwise, odd formats sort as plain text.

What they are checking: Common coding task

Create a client or resource. Then use get_object / put_object, or the helpers upload_file / download_file.

Split the work into three functions: extract, transform and load. This makes each step easy to test. Log the row counts.

loc selects by label (index values and column names). iloc selects by integer position.

pivot changes long data to wide data without aggregating. It needs unique index and column pairs. pivot_table does the same, but it also aggregates duplicates. melt changes wide data to long data.

The Global Interpreter Lock (GIL) is a lock in CPython. It lets only one thread run Python bytecode at a time. It keeps reference counts safe.

import pandas as pd

def csv_to_parquet(src, dst, min_amount=100):
    df = pd.read_csv(src, usecols=["id", "amount", "date"], parse_dates=["date"])
    out = df[df["amount"] >= min_amount]
    out.to_parquet(dst, index=False)
    return len(df), len(out)

Add checks: the file exists and the expected...

A shallow copy makes a new container, but it points to the same inner objects. A deep copy copies everything, level by level.

== compares values (it calls __eq__). is compares identity: do both names point to the same object?

A list comprehension builds the whole list at once, with square brackets. A generator expression, with parentheses, gives items lazily.

Use pd.json_normalize to flatten nested dictionaries. Use record_path for nested lists.

Adding a row at a time to a DataFrame copies the whole frame each time. This is O(n squared), which is very slow. DataFrame.append was removed in pandas 2.0. Instead, collect the pieces in a list and call pd.concat once. Or build the frame from a list of dicts.

Never hard-code or commit credentials. Read settings from the environment or a secrets manager.

Type hints document the expected types, and tools such as mypy can check them. Dataclasses write the repeated setup code for classes that mostly hold data.

A module is a single .py file. A package is a directory of modules. It has an __init__.py file, or it is a namespace package. A script is a file you run directly.

Airflow is an open-source platform to write, schedule and watch workflows. You write the workflows as Python code. A DAG (directed acyclic graph) is one workflow. It is a set of tasks with dependencies, and no loops.

from datetime import datetime
from airflow.decorators import dag, task

@dag(schedule="@daily", start_date=datetime(2025, 1, 1), catchup=False)
def daily_report():
    @task
    def extract():
        return [1, 2, 3]

    @task
    def load(rows):
        print(sum(rows))

    load(extract())

daily_report()

Airflow decides when and in what order tasks run. The heavy work should run in other systems, such as Spark, a warehouse or a container.

What they are checking: Do you know the core idea, with a real example?

Airflow has these parts:

• Scheduler: reads the DAG files, finds which task instances are due, and queues them.
• Executor: decides how and where tasks run. It is part of the scheduler process.
• Workers: processes or pods that run the tasks.
• Webserver / API server: the UI where you watch and trigger runs.
• Metadata database: PostgreSQL or MySQL. It stores DAG runs, task states, connections and variables.
• DAG folder: the Python files that hold the workflows.
• Triggerer: runs deferrable operators in the background (async).

The scheduler and workers talk through the metadata database. With Celery, a broker such as Redis is also used.

What they are checking: Do you know how the parts fit together?

• Operator: a template for one unit of work, such as BashOperator, PythonOperator, S3ToRedshiftOperator.
• Task: an operator used inside a DAG, with its arguments and a task_id.
• Task instance: one run of a task for one DAG run (one logical date). It has a state such as success, failed, running.

from airflow.operators.bash import BashOperator
t = BashOperator(task_id="say_hi", bash_command="echo hi")

So the operator is the class. The task is the object in the DAG. The task instance is that task at one point in time.

What they are checking: Do you know the basic words?

Sensors are operators that wait for something to happen. For example, a file lands in S3, or another DAG finishes. Then the sensor succeeds.

from airflow.providers.amazon.aws.sensors.s3 import S3KeySensor
wait = S3KeySensor(task_id="wait_file", bucket_name="my-bucket",
                   bucket_key="in/{{ ds }}/data.csv",
                   mode="reschedule", poke_interval=300, timeout=6*3600)

There are two modes. In poke mode, the sensor keeps a worker slot busy and sleeps between checks. In reschedule mode, it frees the slot between checks. For long waits, use reschedule or a deferrable sensor. Always set a timeout.

What they are checking: Do you know waiting patterns and how they use resources?

XCom (cross-communication) lets tasks pass small pieces of data to each other. The data is stored in the metadata database.

from airflow.decorators import task

@task
def a():
    return {"rows": 120}      # pushed as XCom automatically

@task
def b(info):
    print(info["rows"])        # pulled via argument

b(a())

Use it for small values: ids, paths, counts and status flags. Do not pass DataFrames or big files. Write the data to S3 or a database and pass only its location. Large XComs make the metadata DB big and slow the scheduler. A custom XCom backend can store them in object storage.

What they are checking: Do you know the limit of XCom?

Catchup: the DAG's start_date is in the past and catchup=True. Then the scheduler creates a run for every missed interval, from the start date until now. With catchup=False, it schedules only the latest interval.

Backfill: you run a DAG by hand for a range of dates. You do this for old data or after a fix:

airflow dags backfill -s 2025-01-01 -e 2025-01-31 my_dag

In Airflow 3, you can also start a backfill from the UI and the API. Backfills need tasks that are idempotent (running twice gives the same result). They must use the logical date, not the current time. Set max_active_runs so a backfill does not overload the system.

What they are checking: Do you know how scheduling handles past dates?

Set retries for each task or in default_args:

from datetime import timedelta
default_args = {
    "retries": 3,
    "retry_delay": timedelta(minutes=5),
    "retry_exponential_backoff": True,
    "execution_timeout": timedelta(hours=1),
}

A failed task goes to up_for_retry. It runs again after the delay, until the retries are used up. Then it is failed. execution_timeout kills a task that runs too long.

SLA is a target for how late a task or DAG may finish. If it is missed, Airflow sends a callback or email, but it does not stop the task. Task-level SLAs were deprecated in Airflow 2.x. They were removed in 3.0 and replaced by deadline-style alerts, so check your version. Use on_failure_callback for alerts.

What they are checking: Do you know the settings that make a pipeline reliable?

Use the bit-shift operators or helper functions.

Fix the cause. Then clear only the failed task, and its downstream tasks if you want, in the UI or CLI. Airflow reruns just those task instances.

• Connections store credentials and endpoints for outside systems (host, login, password, extras). Operators and hooks refer to them by conn_id.
• Variables are global key-value settings, such as a bucket name or a feature flag.

TaskFlow is the decorator style (Airflow 2.0+) for writing DAGs with plain Python functions. @task turns a function into a task. When you pass a return value to another task, Airflow makes the dependency and the XCom for you.

PythonOperator runs a Python function inside the worker. BashOperator runs a shell command. Both are generic. Provider packages add operators for specific services:

• Trigger: TriggerDagRunOperator starts another DAG. It can pass conf and can wait for the run to finish.
• Wait: ExternalTaskSensor waits for a task or DAG in another DAG run. The logical dates must match.
• Data-aware scheduling (Datasets/Assets, Airflow 2.4+): the producer task declares outlets. The consumer DAG uses schedule=[dataset]. It runs when the data is updated, and you need no sensors.

Templated operator fields are filled in at runtime with Jinja. This adds run details to your code.

Use several layers of monitoring:

Cron only starts a command at a set time. Airflow adds more. It has dependencies between tasks, retries and alerts. It has backfills and a UI with logs and history. It uses the logical date as a parameter. It can scale to many workers.

Pools limit how many tasks can run at the same time against a shared resource, such as a database or an API. The limit works across all DAGs. A pool has a number of slots. Each task takes slots (pool_slots) while it runs.

• depends_on_past=True: a task instance runs only if the same task succeeded in the previous DAG run.
• wait_for_downstream=True: a task instance also waits until the tasks right after it in the previous run have succeeded.

Define params with default values on the DAG. Override them when you trigger the DAG by hand. You can use the UI form, the CLI --conf, or the API.

Do not put passwords in DAG files or plain Variables. Use Connections with a secrets backend (AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault). Then Airflow looks up the secrets at runtime.