21 August, 2026 (Last Updated)

Top Data Engineer Interview Questions and Answers

Data engineering is becoming a core skill for companies building analytics, AI, machine learning, and real-time data systems. The global Big Data Engineering Services market is estimated at USD 105.38 billion in 2026 and is projected to reach USD 213.07 billion by 2031, growing at a 15.12% CAGR. This shows why freshers and professionals must prepare strong Data Engineer interview questions and answers around SQL, ETL, pipelines, cloud, big data, and data reliability. This article will cover practical Data Engineer interview questions and answers across beginner, intermediate, advanced, and scenario-based levels.

Beginner-Level Data Engineering Interview Questions

Here are the beginner-level Data Engineering interview questions that help freshers build a strong foundation in pipelines, SQL, ETL, data validation, and storage systems. These questions focus on practical concepts used in real data workflows, not just basic definitions.

1. A sales team receives daily CSV files from different regions. How would you design a basic data ingestion flow?

A basic ingestion flow should collect the files, validate them, clean them, and load them into a storage system such as a database, data warehouse, or data lake.

A simple flow would be:

The first step is to store raw files safely without changing them. Then, checks can be added for file format, column names, missing values, duplicate records, and invalid data types. After validation, the data can be transformed into a standard format and loaded into a target table.

For example, if one region sends sale_date as DD-MM-YYYY and another sends it as YYYY/MM/DD, the pipeline should convert both into one standard date format before loading.

2. Explain ETL and ELT using a real data pipeline example.

ETL means Extract, Transform, Load. Data is first extracted from sources, transformed before loading, and then stored in the target system.

ELT means Extract, Load, Transform. Data is extracted and loaded first, then transformed inside the target system, usually a modern data warehouse.

Process	Flow	Best Used When
ETL	Extract → Transform → Load	Transformation is done before storage
ELT	Extract → Load → Transform	Warehouse can handle large transformations

Example: In an ETL flow, customer data from an API is cleaned using Python before loading into PostgreSQL. In an ELT flow, raw customer data is loaded into BigQuery or Snowflake first, and SQL transformations are done later.

Modern data engineering often uses ELT because cloud warehouses can process large data efficiently.

3. A table contains duplicate customer records. How would you identify and handle them?

Duplicate records can be identified using business keys such as customer_id, email, phone_number, or a combination of fields. The correct method depends on what makes a record unique in the business context.

Example SQL:

After identifying duplicates, they can be handled by keeping the latest record, keeping the most complete record, or merging useful fields.

Example approach:

Situation	Handling Method
Same customer ID repeated	Keep latest updated record
Same email with different names	Review business rule
Exact duplicate rows	Remove duplicates
Partial duplicates	Merge carefully

Deduplication should be done with clear rules because deleting data blindly can remove valid records.

4. Why are primary keys and foreign keys important in data engineering?

Primary keys and foreign keys help maintain data accuracy and relationships between tables. A primary key uniquely identifies each record in a table, while a foreign key connects one table to another.

Example:

Table	Key
customers	customer_id as primary key
orders	customer_id as foreign key

This means every order can be linked to a valid customer.

Without keys, data may become inconsistent. For example, an order may exist for a customer who is not present in the customer table.

In data engineering, keys are important for joins, data modelling, referential integrity, deduplication, and building reliable reporting systems. They also help design fact and dimension tables in warehouses.

5. A pipeline loads data every night. How would you make sure only new records are processed?

To process only new records, an incremental loading strategy can be used. Instead of loading the full dataset every night, the pipeline tracks which records are new or updated.

Common methods include:

* Timestamp column such as updated_at
* Auto-increment ID
* Change Data Capture
* File arrival date
* Watermark tracking

Example:

The last successfully processed timestamp can be stored as a watermark. During the next run, the pipeline loads only records after that point.

Incremental loading reduces processing time, saves storage, and avoids repeatedly processing the same data. It is useful for large production datasets.

6. Compare batch processing and real-time processing.

Batch processing handles data in groups at scheduled intervals. Real-time processing handles data continuously as events arrive.

Feature	Batch Processing	Real-time Processing
Processing style	Scheduled	Continuous
Example frequency	Hourly, daily, weekly	Seconds or milliseconds
Tools	SQL jobs, Airflow, Spark batch	Kafka, Flink, Spark Streaming
Use case	Daily sales report	Live fraud detection

Example: A retail company may use batch processing to calculate daily revenue at midnight. A payment company may use real-time processing to detect suspicious transactions immediately.

Batch is simpler and cost-effective for reports. Real-time is useful when immediate action is required.

7. A dashboard shows wrong revenue numbers. Which data engineering checks would you perform?

Wrong dashboard numbers can come from source data, transformation logic, joins, filters, duplicate records, or delayed pipeline runs.

Checks to perform:

Area	What to Check
Source data	Did the source send correct records?
Pipeline status	Did the latest job complete successfully?
Transformations	Are revenue calculations correct?
Joins	Are rows duplicated due to wrong joins?
Filters	Are date, region, or status filters correct?
Duplicates	Are orders counted more than once?

For example, if an orders table is joined with an order_items table incorrectly, revenue may multiply because one order has many items.

A good investigation compares source totals, transformed totals, and dashboard totals step by step.

8. Explain the difference between a data warehouse and a data lake.

A data warehouse stores structured, cleaned, and organized data for reporting and analytics. A data lake stores raw or semi-structured data in its original form.

Feature	Data Warehouse	Data Lake
Data type	Mostly structured	Structured, semi-structured, unstructured
Data state	Cleaned and modelled	Raw or lightly processed
Users	Analysts, BI teams	Data engineers, data scientists
Examples	Snowflake, BigQuery, Redshift	S3, ADLS, GCS

Example: A company may store raw website logs in a data lake and cleaned sales tables in a data warehouse.

Warehouses are better for fast business reporting. Data lakes are better for storing large volumes of diverse data for future processing, ML, or exploration.

9. A source API allows only 1000 records per request. How would you extract 1 million records?

The extraction should use pagination or batching. Instead of requesting all records at once, the pipeline should fetch records in smaller chunks.

Example flow:

A typical API may support parameters like:

Important engineering checks include:

* Handle rate limits
* Retry failed requests
* Store progress
* Avoid duplicate pages
* Validate total record count
* Log failed pages
* Use incremental extraction when possible

For 1 million records, the pipeline must be reliable because one failed request should not force the entire extraction to restart from the beginning.

10. What is partitioning in databases or data lakes, and why is it useful?

Partitioning means dividing large data into smaller logical parts based on a column such as date, region, or category. It improves query performance and data management.

Example partitioning by date:

If a query needs only July 2026 data, the system can scan only that partition instead of the entire dataset.

Benefits include:

* Faster queries
* Lower processing cost
* Easier data deletion
* Better organization
* Efficient incremental loads

Common partition columns include date, country, region, and event_type.

However, choosing the wrong partition column can create too many small partitions or poor query performance. Partitioning should match query patterns.

11. A CSV file has missing values in important columns. How should a data pipeline handle it?

A pipeline should handle missing values based on business rules, not random assumptions. First, it should identify which columns are mandatory and which are optional.

Example rules:

Column	Rule
order_id	Reject row if missing
customer_email	Allow null if not mandatory
order_amount	Reject or send to error table
delivery_note	Allow missing

Possible handling methods include:

* Drop invalid records
* Fill default values
* Move bad records to a quarantine table
* Raise pipeline alert
* Ask source team to correct data
* Load with nulls if allowed

For critical fields like transaction ID or amount, guessing values is unsafe. A good pipeline separates valid and invalid records and logs the reason clearly.

12. Explain schema evolution with an example.

Schema evolution means the structure of data changes over time. For example, a new column may be added to a source file or an existing column may change type.

Example:

A good pipeline should handle expected schema changes without breaking. Adding a nullable column is usually safer than renaming or removing an existing column.

Common schema evolution cases:

Change	Risk Level
Add nullable column	Low
Rename column	High
Remove column	High
Change data type	High
Add required column	Medium to high

Data engineers must validate schema changes because downstream dashboards, tables, and ML pipelines may depend on existing columns.

13. A table query is running slowly. What beginner-level checks would you perform?

For a slow SQL query, the first step is to understand what the query is doing and how much data it scans.

Basic checks include:

* Is the query scanning the full table?
* Are filters applied correctly?
* Are joins using proper keys?
* Are unnecessary columns selected?
* Is there an index on filter or join columns?
* Is the table very large?
* Are aggregations happening before filtering?

Example improvement:

Selecting only required columns and applying filters early can improve performance. For large analytical systems, partitioning and clustering may also help.

14. What is orchestration in data engineering?

Orchestration means scheduling, coordinating, and monitoring data pipeline tasks. A pipeline often has multiple dependent steps, and orchestration ensures they run in the correct order.

Example workflow:

If transformation depends on extraction, it should not start before extraction completes successfully.

Orchestration tools help with:

* Scheduling jobs
* Managing dependencies
* Retrying failed tasks
* Sending alerts
* Tracking pipeline status
* Logging task history

Common tools include Airflow, Prefect, Dagster, and cloud-native schedulers.

Without orchestration, teams may rely on manual scripts, which becomes risky when pipelines grow in number and complexity.

15. A pipeline has loaded data successfully, but the business team says records are missing. What would you verify?

A successful pipeline run only means the job completed technically; it does not always mean the data is complete. I would verify completeness across every stage.

Checks include:

Stage	Validation
Source	Count records available at source
Extraction	Count records extracted
Transformation	Count records after cleaning
Loading	Count records inserted into target
Reporting	Count records visible in dashboard

I would also check filters, date ranges, rejected records, failed API pages, late-arriving data, duplicate removal logic, and timezone conversion.

Example: If the source sends data in UTC but the dashboard filters by IST date, some records may appear missing.

Data completeness checks are essential for reliable pipelines.

Intermediate-Level Data Engineering Interview Questions

Here are the intermediate-level Data Engineering interview questions that test your understanding of incremental loads, CDC, data modelling, warehouse design, orchestration, and pipeline reliability. These questions are useful for candidates who already know the basics and want to explain real project workflows clearly.

1. A pipeline receives both new and updated customer records daily. How would you design the loading logic?

For this case, a simple append-only load is not enough because existing customer records may change. The pipeline should use an upsert strategy, which means inserting new records and updating existing records.

A common flow would be:

Example SQL logic:

This approach is useful when customer profiles, product prices, employee records, or account details change over time.

2. Explain the difference between full load and incremental load.

A full load processes the entire dataset every time, while an incremental load processes only new or changed records after the last successful run.

Feature	Full Load	Incremental Load
Data processed	Entire dataset	Only new/changed data
Speed	Slower for large data	Faster
Cost	Higher	Lower
Complexity	Simpler	More complex
Best for	Small datasets	Large production datasets

Example: If a table has 10 million records and only 20,000 change daily, incremental load is better.

Incremental loads usually use timestamps, IDs, CDC logs, or watermarks. Full loads are easier to build but become inefficient as data grows.

3. A source system does not provide an updated_at column. How can you still detect changes?

If there is no updated_at column, changes can be detected using hashing, Change Data Capture, source audit logs, or snapshot comparison.

One practical method is to create a hash value from important columns.

Example:

Then compare the new hash with the hash stored in the target table.

Scenario	Method
Source has database logs	CDC
Source gives daily full files	Snapshot comparison
No timestamp available	Row hash comparison
Small table	Full comparison

Hash-based comparison is useful, but it must include all columns that matter for change detection.

4. What is Change Data Capture, and why is it useful?

Change Data Capture, or CDC, captures inserts, updates, and deletes from a source database and sends only those changes to the target system.

Instead of scanning the full table repeatedly, CDC reads database logs or change streams.

Example flow:

CDC is useful for:

* Near real-time data sync
* Reducing source database load
* Capturing deletes
* Building event-driven pipelines
* Keeping warehouses updated

Example tools include Debezium, AWS DMS, Fivetran, and database-native CDC features.

CDC is commonly used when businesses need fresh data without running heavy full-table extraction jobs.

5. A daily pipeline fails halfway after loading some records. How would you prevent duplicate data during rerun?

The pipeline should be designed to be idempotent, meaning rerunning it should not create duplicate or incorrect data.

Common methods include:

* Load data into a staging table first
* Use batch IDs or run IDs
* Delete and reload only the affected partition
* Use merge/upsert instead of blind insert
* Apply unique constraints where possible
* Track processed files
* Commit only after successful validation

Example approach:

If the job fails before completion, the same batch can be rerun safely.

For partitioned data, a common method is to delete the failed date partition and reload it completely.

6. Compare star schema and snowflake schema.

Star schema and snowflake schema are data warehouse modelling techniques.

Feature	Star Schema	Snowflake Schema
Structure	Fact table connected to denormalized dimensions	Dimensions are normalized into multiple tables
Query speed	Usually faster	May need more joins
Storage	More redundancy	Less redundancy
Simplicity	Easier for analysts	More complex
Best for	BI reporting	Structured enterprise modelling

Example: In a sales warehouse, the fact table may store sales transactions. Dimension tables may include customer, product, date, and store.

Star schema is often preferred for reporting because it is easier to query. Snowflake schema reduces duplication but can make queries more complex.

7. What are fact tables and dimension tables?

Fact tables store measurable business events, while dimension tables store descriptive context.

Example in a sales system:

Table Type	Example	Contains
Fact table	fact_sales	sales_amount, quantity, discount
Dimension table	dim_customer	customer_name, city, segment
Dimension table	dim_product	product_name, category, brand

A fact table usually contains foreign keys that connect to dimensions.

Example:

This design helps analysts answer questions like “What was total sales by city and product category last month?”

Fact and dimension modelling is important for building clean, query-friendly data warehouses.

8. Explain Slowly Changing Dimensions with an example.

Slowly Changing Dimensions, or SCD, manage changes in dimension data over time.

Example: A customer changes city from Chennai to Bengaluru.

SCD Type	Behaviour
Type 1	Overwrite old value
Type 2	Keep historical record
Type 3	Store limited previous value

SCD Type 1 example:

customer_id = 101, city = Bengaluru

The old city is lost.

SCD Type 2 example:

customer_id	city	start_date	end_date	current_flag
101	Chennai	2024-01-01	2026-02-10	N
101	Bengaluru	2026-02-11	NULL	Y

SCD Type 2 is useful when historical reporting matters, such as tracking sales by customer’s city at the time of purchase.

9. A data warehouse query has multiple joins and is slow. What optimization steps would you consider?

First, I would check the query plan to understand where time is spent. Then I would optimize joins, filters, scanned columns, and table design.

Useful checks include:

* Are joins using correct keys?
* Are filters applied early?
* Is the query selecting only required columns?
* Are tables partitioned or clustered properly?
* Are large tables joined before filtering?
* Are there duplicate rows causing row explosion?
* Are statistics updated?
* Can pre-aggregated tables help?

Example improvement:

Query optimization should reduce scanned data and unnecessary computation.

10. What is data lineage, and why does it matter?

Data lineage shows where data comes from, how it changes, and where it goes. It helps teams trace data movement across pipelines.

Example:

CRM Database → Raw Customer Table → Cleaned Customer Table → Customer Dashboard

Data lineage matters because it helps with:

* Debugging wrong reports
* Understanding data dependencies
* Impact analysis before schema changes
* Compliance and audits
* Root cause analysis
* Trust in business metrics

For example, if a revenue dashboard shows incorrect numbers, lineage helps identify whether the issue came from the source system, transformation logic, or reporting layer.

In modern data platforms, lineage is often tracked through orchestration tools, dbt, data catalogs, or metadata platforms.

11. A pipeline depends on five upstream tables. How would you handle dependency management?

Dependency management ensures that a task runs only after required upstream data is available and valid.

A typical dependency flow:

This can be handled using orchestration tools like Airflow, Prefect, Dagster, or cloud schedulers.

Good dependency management includes:

* Defining task order clearly
* Checking upstream job success
* Validating data freshness
* Retrying failed tasks
* Sending alerts
* Avoiding hardcoded sleep delays
* Using sensors or data availability checks

For example, a sales model should not run just because it is 2 AM. It should run only after required source tables are ready.

12. Explain the difference between data validation and data quality checks.

Data validation checks whether data follows expected rules before or during loading. Data quality checks evaluate whether the data is accurate, complete, consistent, and useful.

Area	Data Validation	Data Quality
Focus	Rule compliance	Trustworthiness
Example	order_id is not null	Total revenue matches source
Timing	Pipeline load stage	Across pipeline/reporting
Purpose	Prevent bad data entry	Ensure reliable data

Examples of validation:

* Required fields are not null
* Date format is valid
* Amount is numeric
* File schema matches expected format

Examples of quality checks:

* Record count matches source
* Duplicate rate is acceptable
* Revenue is within expected range
* No sudden drop in daily users

Both are needed for reliable data pipelines.

13. What is a data contract in data engineering?

A data contract is an agreement between data producers and data consumers about the structure, meaning, and quality expectations of data.

A data contract may define:

* Table or topic name
* Column names
* Data types
* Required fields
* Accepted values
* Update frequency
* Ownership
* SLA
* Breaking change rules

Example:

orders.order_id must be non-null and unique.

orders.amount must be numeric and greater than or equal to 0.

orders.created_at must be in UTC.

Data contracts help prevent unexpected schema changes from breaking pipelines, dashboards, or ML models.

They are useful in companies where many teams produce and consume shared data.

14. A data pipeline processes files from cloud storage. How would you avoid processing the same file twice?

The pipeline should track file processing status. This can be done using metadata tables, file manifests, checksums, or event IDs.

Example metadata table:

file_name	file_hash	status	processed_at
sales_2026_07_15.csv	abc123	SUCCESS	2026-07-15 01:00

Before processing a file, the pipeline checks whether the file name or hash already exists with successful status.

Good practices include:

* Store processed file metadata
* Use file checksum for duplicate detection
* Move processed files to archive
* Use atomic status updates
* Track failed files separately
* Avoid relying only on file name if names can repeat

This prevents duplicate loading during retries or repeated file arrivals.

15. A data team wants faster dashboard performance. Would you always query raw transaction tables directly?

No, raw transaction tables are usually not ideal for dashboards because they may be large, detailed, and expensive to query repeatedly.

A better approach is to create curated or aggregated tables.

Example:

Raw Orders → Clean Orders → Daily Sales Summary → Dashboard

Instead of calculating revenue from millions of rows every time, the dashboard can read a summary table like:

date	region	total_sales	order_count

This improves speed and reduces warehouse cost.

Raw tables are important for audit and detailed analysis, but dashboards often need cleaned, modelled, and optimized tables. For frequent reports, pre-aggregations, materialized views, partitioning, or semantic layers can help.

Prepare for data engineer interviews with stronger big data foundations through HCL GUVI’s Big Data Engineering Course. Learn data pipelines, distributed computing, big data workflows, processing tools, and practical engineering concepts through structured training designed for learners preparing for data engineering roles.

Advanced-Level Data Engineering Interview Questions

Here are the advanced-level Data Engineering interview questions that focus on big data processing, Spark optimization, streaming pipelines, lakehouse architecture, data quality, cost control, governance, and production reliability.

These questions are designed to test how well you can handle large-scale data systems and complex engineering challenges.

1. A Spark job is taking 3 hours to process daily logs. How would you optimize it?

A slow Spark job can be caused by poor partitioning, data skew, unnecessary shuffles, large joins, inefficient file formats, or bad cluster configuration. The first step is to check the Spark UI to identify slow stages, shuffle size, task distribution, and failed/retried tasks.

Key optimization areas:

Area	What to Check
Data format	Use Parquet/ORC instead of CSV/JSON
Partitioning	Avoid too few or too many partitions
Joins	Use broadcast join for small tables
Shuffle	Reduce unnecessary groupBy/orderBy operations
Skew	Handle hot keys separately
Caching	Cache only reused DataFrames

Example:

Optimization should be based on metrics, not assumptions. Spark performance improves when data movement, shuffle, and skew are controlled properly.

2. A dataset has one customer ID with millions of records while others have few. How would you handle data skew?

Data skew happens when one key has much more data than others, causing some tasks to run much longer than the rest. In distributed processing, this creates an uneven workload.

Example:

Ways to handle skew:

* Identify skewed keys using frequency analysis
* Add salting to distribute hot keys
* Use broadcast joins where possible
* Process skewed keys separately
* Repartition carefully
* Avoid grouping heavily skewed columns directly

Salting example:

Then joins or aggregations can be distributed across salted keys.

Skew handling is important because even a large cluster can be slow if one partition carries most of the workload.

3. A real-time fraud pipeline must process transactions within seconds. What architecture would you propose?

For real-time fraud detection, the architecture should support low-latency ingestion, stream processing, feature enrichment, model scoring, alerting, and monitoring.

A possible flow:

Core components:

Layer	Example
Ingestion	Kafka
Processing	Flink, Spark Structured Streaming
Storage	Redis, Cassandra, data lake
Serving	API, alert system
Monitoring	Lag, latency, error rate

The pipeline should handle duplicate events, late-arriving events, retries, and failure recovery. For fraud use cases, exactly-once or effectively-once processing is important because duplicate alerts or missed transactions can create business risk.

Low latency should not come at the cost of data correctness.

4. How would you design a CDC-based pipeline from PostgreSQL to a cloud data warehouse?

A CDC pipeline should capture inserts, updates, and deletes from PostgreSQL without repeatedly scanning full tables. A common design uses PostgreSQL WAL logs, a CDC tool, a message broker, and a warehouse loader.

Example architecture:

Important design points:

* Enable logical replication in PostgreSQL
* Capture insert, update, and delete events
* Include primary keys for merge logic
* Preserve event ordering per table/key
* Store raw CDC events for replay
* Apply changes to warehouse using merge/upsert
* Track schema changes
* Monitor lag from source to warehouse

Deletes should not be ignored. They can be handled using soft-delete flags or delete events in the target warehouse.

CDC is useful when near real-time data freshness is required.

5. A data lake has become a “data swamp.” How would you reorganize it?

A data lake becomes a data swamp when raw files are dumped without structure, ownership, metadata, quality checks, or lifecycle management. Reorganizing it requires layered architecture and governance.

A better design:

Raw Zone → Clean Zone → Curated Zone → Consumption Zone

Zone	Purpose
Raw	Original unmodified data
Clean	Validated and standardized data
Curated	Business-ready tables
Consumption	BI, ML, APIs, dashboards

Improvements:

* Define folder naming standards
* Use columnar formats like Parquet
* Add schema and metadata catalog
* Track ownership and freshness
* Apply access controls
* Add data quality checks
* Remove unused duplicate datasets
* Create lifecycle policies

A good data lake should be discoverable, governed, and reliable, not just a storage dump.

6. Explain the medallion architecture in modern data platforms.

Medallion architecture organizes data into progressive quality layers: bronze, silver, and gold. It is commonly used in lakehouse platforms.

Layer	Meaning	Example
Bronze	Raw ingested data	API logs, raw CSV, CDC events
Silver	Cleaned and standardized data	Deduplicated customer records
Gold	Business-ready data	Daily revenue, customer 360, KPI tables

Example flow:

Bronze stores the original data for traceability. Silver applies validation, type casting, deduplication, and standardization. Gold contains aggregated or modelled tables for analytics and reporting.

This architecture improves maintainability because every layer has a clear purpose. It also helps debugging because teams can trace issues from gold back to silver and bronze.

7. A streaming pipeline receives events late and out of order. How would you handle them?

Late and out-of-order events are common in streaming systems because network delays, device issues, retries, and source failures can change event arrival order.

The solution depends on using event time instead of only processing time.

Important concepts:

* Event time: when the event actually happened
* Processing time: when the system processed it
* Watermark: allowed delay threshold
* Windowing: grouping events by time period

Example:

If the event arrives within the watermark, it can still update the correct window. If it arrives too late, it may go to a late-event table.

This is important for accurate dashboards, fraud detection, IoT analytics, and user activity tracking.

8. How would you design data quality checks for a production pipeline?

Production data quality checks should validate structure, completeness, uniqueness, freshness, consistency, and business rules.

A strong quality framework includes:

Check Type	Example
Schema	Columns and data types match expected structure
Completeness	order_id and amount are not null
Uniqueness	transaction_id is unique
Freshness	Data arrived within SLA
Range	amount >= 0
Reconciliation	Source count matches target count
Business rule	Delivered order must have delivery date

Example SQL check:

Quality checks should run before data reaches reporting or ML systems. Failed checks should trigger alerts, quarantine bad records, or stop downstream jobs based on severity.

9. A data warehouse cost suddenly increased. What would you investigate?

A sudden cost increase can come from inefficient queries, full-table scans, repeated dashboard refreshes, unoptimized storage, poor partition pruning, or new workloads.

Investigation areas:

Area	Possible Issue
Queries	Expensive joins or scans
Dashboards	Too frequent refreshes
Storage	Duplicate large tables
Pipelines	Full loads instead of incremental loads
Partitions	Filters not using partition columns
Users	Ad hoc heavy queries
Materialization	Too many temporary or intermediate tables

Cost optimization methods include:

* Use partitioning and clustering
* Select only required columns
* Create aggregated tables for dashboards
* Avoid repeated full refreshes
* Set query limits and alerts
* Remove unused tables
* Use incremental models

Cost control is a core responsibility in modern cloud data engineering because compute usage directly affects billing.

10. How would you design a data platform for both BI reporting and machine learning?

BI and ML have different needs, so the platform should support both curated reporting tables and feature-ready datasets.

A possible design:

Sources → Lake/Warehouse → Transformations → BI Tables + Feature Store → Reports/ML Models

BI needs:

* Clean dimensions and facts
* Aggregated KPI tables
* Consistent business definitions
* Fast dashboard performance

ML needs:

* Historical data
* Feature engineering
* Point-in-time correctness
* Training and inference datasets
* Feature reuse
* Drift monitoring

A shared data platform should avoid duplicate logic. For example, customer lifetime value should not be calculated differently by the BI team and ML team.

A feature store can help maintain reusable, consistent features for machine learning while the warehouse serves analytics users.

11. What is point-in-time correctness in feature engineering?

Point-in-time correctness means using only the data that was available at the time a prediction would have been made. It prevents future information from leaking into training data.

Example:

Total missed payments before prediction date

This is critical in ML pipelines because data leakage can make model performance look unrealistically good during training but fail in production.

Point-in-time joins are often required when building features from historical tables. Feature timestamps, event timestamps, and snapshot dates must be handled carefully.

12. A source team changes a column type from integer to string. How would you protect downstream pipelines?

This is a schema-breaking change if downstream systems expect the column to remain integer. A good data pipeline should detect such changes before they break reports or jobs.

Protection methods:

* Schema validation at ingestion
* Data contracts with source teams
* Compatibility checks
* Quarantine unexpected schema files
* Alert pipeline owners
* Use schema registry where applicable
* Maintain backward-compatible transformations
* Add automated tests for critical columns

Example:

The pipeline can either fail fast, cast safely if possible, or route the data to a review area.

Source schema changes should not silently flow into production tables because they can break dashboards, joins, and ML models.

13. Explain compaction in lakehouse table formats.

Compaction combines many small files into fewer larger files to improve query performance. In data lakes, frequent writes from streaming or incremental jobs can create many small files.

Small file problem:

10,000 tiny Parquet files → slow metadata listing and query planning

After compaction:

100 optimized Parquet files → faster reads

Lakehouse formats like Delta Lake, Apache Iceberg, and Apache Hudi support table maintenance operations that help manage files and metadata.

Benefits of compaction:

* Faster queries
* Lower metadata overhead
* Better scan efficiency
* Improved downstream processing
* Reduced cloud storage API calls

Compaction should be scheduled carefully because it uses compute resources. It is especially important for streaming tables and frequently updated datasets.

14. How would you handle GDPR-style delete requests in a data platform?

A delete request requires removing or anonymizing a user’s personal data from all relevant systems. This is complex because data may exist in raw, cleaned, curated, backup, and downstream tables.

A proper approach includes:

* Identify user using stable keys
* Locate data across systems using lineage/catalog
* Delete or anonymize records
* Update derived tables
* Handle backups based on policy
* Log the deletion request
* Validate deletion completion
* Prevent re-ingestion from old sources

Example:

Soft deletion may not be enough if regulations require actual removal.

Data privacy compliance needs strong lineage, governance, access control, and deletion workflows.

15. A critical pipeline has a 99.5% SLA. What engineering practices would you apply?

A pipeline SLA means the data must be available and correct within a defined time. For a 99.5% SLA, reliability must be designed into the pipeline.

Key practices:

Area	Practice
Reliability	Retries, idempotent loads, checkpoints
Monitoring	Alerts for delay, failure, freshness
Recovery	Rerun from safe checkpoints
Quality	Automated validation checks
Scalability	Handle peak data volume
Ownership	Clear on-call and escalation
Observability	Logs, metrics, lineage
Testing	Unit, integration, and data tests

The pipeline should not only “run.” It should be measurable and recoverable.

For example, if source data is late, the system should alert early instead of failing silently after the SLA is missed.

Conceptual Data Engineering Interview Questions

Here are the conceptual and scenario-based Data Engineering interview questions that check your problem-solving approach in real business situations.

These questions focus on metric mismatches, migration risks, auditability, timezone issues, data discovery, retention policies, and undocumented pipeline challenges.

1. Two teams report different “active users” numbers from the same data warehouse. How would you approach this?

This is usually not just a pipeline issue; it is often a metric definition issue. One team may define active users as users who logged in, while another may define them as users who completed an action.

A good approach is to compare:

Area	Check
Metric definition	What counts as “active”?
Date logic	Is it based on UTC, IST, or business timezone?
Filters	Are bots, test users, or internal users excluded?
Source table	Are both teams using the same table?
Aggregation logic	Daily active, weekly active, or monthly active?

The fix is to create a governed metric layer or approved KPI table so all teams use the same business definition.

2. A company wants to migrate reports from an old warehouse to a new cloud warehouse. What risks should be handled?

Warehouse migration is not only about copying tables. The main risks are data mismatch, broken dashboards, query behaviour changes, access issues, and performance differences.

Important checks include:

* Compare row counts between old and new systems.
* Validate important business metrics.
* Recreate views, procedures, and permissions.
* Test dashboard compatibility.
* Check data types and timezone handling.
* Run old and new systems in parallel for some time.
* Document changed table names or logic.

Example flow:

A safe migration should include reconciliation reports before business users fully switch to the new warehouse.

3. A business report depends on data from three countries with different time zones. How would you design date handling?

Date handling should be standardized early because wrong timezone logic can affect revenue, orders, user activity, and SLA reports.

A strong design should store:

* Event timestamp in UTC
* Source timezone if available
* Business reporting timezone
* Local date derived from business rules

Example:

Field	Purpose
event_timestamp_utc	Standard storage
source_timezone	Original context
business_date	Reporting date
processed_at	Pipeline processing time

For global reporting, UTC is useful for consistency. For country-level business reports, local business date may be needed.

Timezone conversion should happen through clear rules, not dashboard-level manual fixes.

4. A finance team needs every number in a report to be auditable. What should the data pipeline include?

For finance reporting, the pipeline must support auditability, traceability, and reconciliation. Every final number should be traceable back to its source records.

The pipeline should include:

* Raw data storage without modification
* Batch ID or run ID
* Source file or source system reference
* Transformation logs
* Record count checks
* Reconciliation between source and target
* Error record table
* Data lineage
* Access logs
* Versioned transformation logic

Example:

If the final revenue is ₹10 crore, the team should be able to explain which records contributed to that number and which logic was applied.

5. A source API is unstable and sometimes returns incomplete data without failing. How would you protect the pipeline?

This is dangerous because the pipeline may show success even when the data is incomplete. The pipeline should include completeness and reasonableness checks, not only HTTP success checks.

Useful protections include:

* Check expected record count if available.
* Compare with previous day’s volume.
* Validate mandatory fields.
* Track API response metadata.
* Add retry logic.
* Store raw API responses.
* Alert on sudden drops.
* Mark batch as suspicious instead of successful.

Example:

This should trigger an alert because a successful response code does not guarantee complete data.

6. A company has many datasets, but analysts cannot find the right tables. What data engineering solution would help?

This is a data discoverability problem. A data catalog and metadata management process can help analysts find trusted datasets.

A useful data catalog should show:

Metadata	Why It Helps
Table owner	Who to contact
Description	What the table contains
Column definitions	Meaning of each field
Freshness	Last updated time
Lineage	Source and downstream usage
Quality status	Whether data is trusted
Sample queries	How to use the table

Without metadata, teams may create duplicate tables or use outdated ones.

Good data engineering is not only about pipelines; it also includes making data understandable and usable for others.

7. A product team wants raw clickstream data stored forever. Would you agree?

Not immediately. Storing all raw data forever can increase cost, privacy risk, and maintenance burden. The decision should depend on business value, compliance, and access needs.

A better approach is to define a retention policy.

Example:

Data Type	Suggested Retention
Raw clickstream events	90–180 days
Aggregated behaviour metrics	Longer term
Compliance logs	As per legal requirement
Personal data	Based on privacy policy

Older raw data can be archived to cheaper storage if needed. Sensitive fields can be masked or removed.

Data retention should balance analytics value, storage cost, legal requirements, and user privacy.

8. A table has 500 columns and is used by many dashboards. What problems can this create?

A very wide table can become hard to maintain, expensive to query, and confusing for users. It may also contain mixed business logic from different domains.

Problems include:

* Slow query performance
* High storage and scan cost
* Unclear column ownership
* Duplicate or similar columns
* Difficult schema changes
* Confusing dashboard usage
* Higher risk of wrong joins or filters

A better design may split the table into focused domain tables or curated marts.

Example:

A wide table may be useful for some ML use cases, but for BI and governance, cleaner modelling is usually better.

9. A pipeline change improves speed but slightly changes historical numbers. Should it be deployed?

Not directly. Any change that affects historical numbers must be reviewed carefully because business users may depend on those numbers.

Before deployment, the team should compare old and new outputs.

Checks include:

* Which metrics changed?
* Why did they change?
* Are the new numbers more correct?
* Which dashboards are affected?
* Is finance or leadership reporting impacted?
* Is a backfill required?
* Should users be informed?

Example:

Old revenue logic excluded cancelled orders.

New logic includes them accidentally.

Even if the pipeline becomes faster, incorrect numbers are not acceptable. Performance improvements should not silently change business meaning.

10. A data engineer joins a project with no documentation. How should they understand the pipeline safely?

The engineer should first study the pipeline without making direct changes. The goal is to understand sources, transformations, dependencies, outputs, and business usage.

A safe discovery process includes:

* Identify source systems.
* Read orchestration DAGs or schedules.
* Check table lineage.
* Review transformation SQL or scripts.
* List downstream dashboards and users.
* Check failure history.
* Compare row counts at each stage.
* Identify owners of critical datasets.
* Document assumptions.
* Create small validation queries.

Example flow:

Before changing anything, the engineer should understand which datasets are business-critical. In undocumented systems, even small changes can break important reports.

How to Prepare for Data Engineering Interview Questions

Data engineering interview preparation should focus on both fundamentals and hands-on practice.

You should not prepare only definitions; you should also understand how data moves from source systems to warehouses, dashboards, ML models, and business reports.

1. Build Strong SQL Skills: Practise joins, subqueries, window functions, CTEs, aggregations, indexing basics, query optimization, and data cleaning queries. SQL is one of the most commonly tested skills in Data Engineer interviews.* Understand ETL and ELT Pipelines: Learn how data is extracted from sources, transformed, validated, loaded, and monitored. You should be able to explain batch pipelines, incremental loads, full refreshes, and error handling clearly.
2. Learn Data Warehousing and Data Modelling: Focus on facts, dimensions, star schema, snowflake schema, normalization, denormalization, slowly changing dimensions, and partitioning. These topics are frequently asked in analytics and warehouse-based roles.
3. Practise Python for Data Engineering: Learn file handling, APIs, Pandas basics, data cleaning, JSON parsing, error handling, logging, and automation scripts. Python is useful for building data ingestion and transformation workflows.
4. Work on Beginner Data Engineering Projects: Build small hands-on projects using CSV files, APIs, databases, cloud storage, and dashboards. You can refer to these data engineering project ideas for beginners to create portfolio-ready practice work.
5. Prepare for Career Switch Questions: If you are from a non-CS or BCA background, focus on SQL, Python, databases, cloud basics, and project explanation. This BCA to Data Engineer guide can help you understand the transition path.
6. Practise Your Interview Introduction: Data Engineer interviews often begin with “Tell me about yourself.” Prepare a short introduction covering your skills, projects, tools, and learning path. You can use these self-introduction examples for Data Engineer freshers for reference.
7. Use AI Tools Smartly: AI tools can help with SQL debugging, data cleaning logic, documentation, pipeline planning, and code explanation. Explore these AI tools for data engineering to improve your workflow.
8. Follow a Structured Roadmap: Learn skills step by step instead of jumping between random tools. Start with SQL and Python, then move to databases, ETL, cloud, Spark, Kafka, Airflow, and projects. You can follow GUVI’s data engineering career roadmap for structured learning.
9. Learn Big Data Tools: For advanced roles, learn Hadoop, Spark, Kafka, Hive, Airflow, and cloud-based big data platforms. GUVI’s Big Data Engineering course can help you build these skills with guided learning.
10. Practise Mock Tests and Technical Questions: Solve MCQs, SQL problems, pipeline questions, and scenario-based interview questions regularly. Use PlacementPreparation.io to practise Data Engineer interview questions, mock tests, and placement-focused exercises.
11. Choose Mentor-led Learning if Needed: If you need structured guidance, projects, mentor support, and placement preparation, GUVI’s Zen Class Data Science course can help you strengthen data, analytics, and engineering fundamentals.

Conclusion

Data engineering is a strong career path for learners interested in SQL, Python, pipelines, cloud, big data, and analytics systems.
To prepare well, practise Data Engineer interview questions and answers, build projects, understand real pipeline issues, and revise scenario-based questions regularly. Strong hands-on practice will help you answer interviews with confidence.

FAQs