Data Engineer Interview Questions

I'd design a multi-stage pipeline using cloud services like AWS. First, I'd use Lambda functions triggered by S3 events to ingest files, Kinesis Data Streams for real-time API data, and DMS for database replication. The data would land in S3 as raw files partitioned by date. For processing, I'd use Apache Spark on EMR with auto-scaling to handle the 10TB volume, implementing data quality checks using Great Expectations framework to validate schema, check for nulls, and detect anomalies. Failed records would route to a dead letter queue for manual review. I'd use Apache Airflow for orchestration with retry logic and alerting. Clean data would be stored in Redshift using COPY commands for optimal performance, with separate staging and production schemas. Monitoring would include CloudWatch metrics for pipeline health, data volume alerts, and SLA tracking.

```sql WITH customer_revenue AS ( SELECT region, customer_id, customer_name, SUM(revenue) as total_revenue FROM transactions t JOIN customers c ON t.customer_id = c.id WHERE t.transaction_date >= DATEADD(month, -6, CURRENT_DATE) GROUP BY region, customer_id, customer_name ), ranked_customers AS ( SELECT *, DENSE_RANK() OVER (PARTITION BY region ORDER BY total_revenue DESC) as rank FROM customer_revenue ) SELECT region, customer_id, customer_name, total_revenue, rank FROM ranked_customers WHERE rank <= 3 ORDER BY region, rank; ``` For optimization on 100M records, I'd create composite indexes on (transaction_date, customer_id) and (customer_id, region). Consider partitioning the transactions table by date and using columnar storage if available.

Batch processing handles large volumes of data at scheduled intervals with higher throughput but higher latency (minutes to hours). Stream processing handles data continuously with low latency (milliseconds to seconds) but typically lower throughput per message. Spark and Kafka serve different purposes - Kafka is a distributed streaming platform for data ingestion and messaging, while Spark is a processing engine. You'd use Kafka to collect and buffer streaming data, then Spark Streaming or Structured Streaming to process it. For late-arriving data, I'd implement watermarking in Spark Structured Streaming to define how long to wait for late events. Set a watermark like `withWatermark('timestamp', '10 minutes')` and use windowed aggregations. Late data within the watermark window gets processed correctly, while data arriving after the watermark is dropped or sent to a separate late-data stream for special handling.

At my previous company, our daily ETL pipeline processing customer orders failed during Black Friday, causing a 6-hour delay in business reporting. I immediately checked our monitoring dashboards and found the Spark job was failing with out-of-memory errors due to a 300% spike in data volume. I quickly implemented a temporary fix by increasing executor memory and repartitioning the data. For the root cause, I discovered our pipeline wasn't designed to handle traffic spikes. I then implemented several improvements: added auto-scaling to our EMR cluster, implemented data volume monitoring with alerts, and created a circuit breaker pattern to process data in smaller batches during high-volume periods. I also established better communication channels with the business team to get advance notice of expected traffic spikes. This experience taught me the importance of designing systems for peak loads, not average loads, and implementing comprehensive monitoring.

I worked on a customer analytics project where the Marketing team wanted real-time data updates every 15 minutes for campaign optimization, while the Finance team preferred daily batch processing to ensure data accuracy and reduce costs. The real-time approach would cost 3x more in infrastructure. I organized a joint meeting to understand each team's core needs. Marketing needed quick insights for ad spend decisions, while Finance needed accurate, reconciled data for reporting. I proposed a hybrid solution: implement a streaming pipeline for Marketing's key metrics (click-through rates, conversion rates) with a 15-minute SLA, while maintaining the daily batch process for Finance's comprehensive reporting with full data validation. I also created a data quality dashboard showing confidence levels for real-time vs. batch data. Both teams agreed after seeing cost projections and understanding the trade-offs. The project delivered 85% of Marketing's requirements at 40% of the original real-time cost while maintaining Finance's accuracy standards.

When our team decided to migrate from on-premises Hadoop to Google Cloud Platform, I had two weeks to learn BigQuery, Dataflow, and Cloud Composer for a critical customer reporting pipeline. My approach was structured: I started with Google's official documentation and hands-on labs, then built small proof-of-concept projects replicating our existing workflows. I joined GCP community forums and Slack channels for quick answers. The biggest challenge was understanding BigQuery's columnar storage optimization - my initial queries were inefficient and costly. I learned to avoid SELECT * and use partitioning and clustering effectively. I also struggled with Dataflow's Apache Beam programming model initially. To overcome this, I found similar open-source examples and modified them for our use case. By the deadline, I successfully migrated three pipelines, reducing processing time by 60% and costs by 40%. The key was hands-on practice combined with community support and learning from real-world examples rather than just theory.

I would immediately follow a structured incident response approach. First, I'd implement a temporary fix by switching to our backup pipeline or manual data refresh to restore executive dashboard functionality. Next, I'd analyze recent changes, check resource utilization, and examine error logs to identify the root cause. For example, if memory errors appeared, I might scale up cluster resources temporarily. I'd communicate proactively with stakeholders, providing regular updates on progress and estimated resolution time. Once fixed, I'd conduct a post-mortem to document lessons learned and implement preventive measures like enhanced monitoring alerts and automated failover mechanisms. Throughout this process, I'd maintain detailed incident logs for future reference and team learning.

This is a critical data integrity incident requiring immediate action. First, I'd immediately stop the pipeline and quarantine the affected data to prevent further propagation. I'd quickly assess the scope of impact by identifying all downstream systems and stakeholders who consumed this data. Next, I'd work with the analytics team to quantify the extent of incorrect decisions made using this data. I'd prepare a clear, honest communication to executives explaining the issue, timeline, and remediation plan. For the technical fix, I'd trace back to identify the root cause - whether it was a code change, data source modification, or infrastructure issue. I'd implement data validation checkpoints and automated quality tests to prevent recurrence. Finally, I'd lead a comprehensive post-mortem with all stakeholders to strengthen our data governance processes, implement better testing procedures, and establish more robust monitoring systems.

Data lineage is crucial for maintaining trust and compliance in enterprise data systems. In my experience, I've implemented comprehensive lineage tracking using tools like Apache Atlas, DataHub, or cloud-native solutions like AWS Glue Data Catalog. I establish lineage at multiple levels: field-level lineage showing how specific columns transform through the pipeline, table-level lineage mapping source-to-target relationships, and process-level lineage documenting ETL job dependencies. For example, I've built automated lineage capture using Spark listeners that track DataFrame transformations and write metadata to our catalog. I also implement data tagging strategies for PII identification and compliance requirements. To ensure traceability, I maintain detailed documentation of business rules, transformation logic, and data quality checks at each stage. Regular audits verify lineage accuracy, and I've created self-service lineage dashboards for analysts to understand data origins and transformations independently.

Effective capacity planning requires continuous monitoring and proactive optimization. In a recent project, our AWS data platform costs were escalating due to over-provisioned EMR clusters running 24/7. I implemented a comprehensive cost optimization strategy: First, I analyzed historical usage patterns and identified that peak processing occurred only during specific hours. I redesigned our architecture using spot instances for non-critical workloads, achieving 70% cost reduction on compute. I implemented auto-scaling policies and cluster lifecycle management, automatically terminating idle clusters. For storage, I established S3 lifecycle policies moving infrequently accessed data to cheaper storage classes, reducing storage costs by 40%. I also optimized our Spark jobs by tuning partition sizes and caching strategies, reducing overall processing time by 30%. Additionally, I set up CloudWatch dashboards and cost alerts to monitor spending trends. These changes reduced our monthly infrastructure costs from $50K to $28K while actually improving job completion times through better resource utilization.

Scaling an engineering team requires deliberate culture building and robust processes. I'd focus on establishing strong foundations early: First, I'd help create comprehensive onboarding programs including documentation standards, architectural decision records, and hands-on pipeline building exercises. I'd advocate for implementing peer code reviews, pair programming sessions, and regular tech talks to facilitate knowledge transfer. To maintain quality, I'd push for automated testing frameworks, CI/CD pipelines, and infrastructure-as-code practices that scale with the team. I'd organize regular 'lunch and learn' sessions where team members share experiences with new tools or techniques. Additionally, I'd establish cross-team rotation programs so engineers gain exposure to different parts of our data platform. For culture building, I'd promote psychological safety through blameless post-mortems and encourage experimentation with innovation time. I'd also help establish clear career progression paths and mentorship programs pairing senior engineers with newcomers, ensuring everyone has growth opportunities while maintaining our technical excellence.

Effective stakeholder management requires balancing business urgency with technical sustainability. In a previous role, the marketing team urgently needed customer segmentation data for a campaign launching in two weeks. They requested a quick SQL view that would require complex joins across multiple databases, creating performance issues and technical debt. Instead of simply saying 'no,' I first acknowledged their business need and timeline pressure. I then presented two options: Option A was their requested quick solution with clear risks outlined - potential system slowdowns and maintenance burden. Option B was a slightly longer approach involving creating a dedicated marketing data mart that would solve their immediate need while providing a scalable foundation for future requests. I provided timeline estimates and resource requirements for both. By framing it as business options rather than technical constraints, I helped them make an informed decision. They chose Option B after understanding the long-term benefits. I also committed to delivering a temporary solution to meet their immediate deadline while building the proper infrastructure.