Data Engineering
ETL / ELT & Pipelines
1. ETL vs ELT
ETL — Extract, Transform, Load. Transform data before loading into the destination.
ELT — Extract, Load, Transform. Load raw data first, transform inside the warehouse using SQL.
ELT is now preferred when the warehouse is powerful enough (Snowflake, BigQuery, Redshift) — cheaper and more flexible.
2. Batch vs Streaming
Batch — processes data periodically (hourly, daily). Simpler, cheaper. Used when latency tolerance is high.
Streaming — processes continuously in near real-time (Kafka, Kinesis). Complex, more expensive. Used when freshness matters.
3. Data Lake vs Data Warehouse
| Data Lake | Data Warehouse | |
|---|---|---|
| Data type | Raw, structured + unstructured | Cleaned, structured |
| Schema | Schema-on-read | Schema-on-write |
| Cost | Lower | Higher |
| Tools | S3 + Glue + Athena | Snowflake, Redshift |
| Use case | Exploration, ML | Analytics, BI |
4. Star Schema vs Snowflake Schema
Star schema — denormalized dimensions. Simpler joins, faster queries. Preferred for OLAP.
Snowflake schema — normalized dimensions. More tables, more joins. Less redundancy.
5. Slowly Changing Dimensions (SCD)
Techniques for managing historical dimension data when source attributes change over time.
- Type 1 — overwrite (no history)
- Type 2 — add new row with version/date (full history) — most common
- Type 3 — add new column (limited history)
6. CDC (Change Data Capture)
Captures only changed records from the source instead of reloading everything. More efficient for large tables.
Common implementations: Debezium (log-based), timestamp watermark, trigger-based.
7. Incremental Load vs Full Load
Incremental — loads only new or changed records since the last run. Efficient for large datasets.
Full load — reloads the complete dataset on every run. Simpler but expensive for large tables.
8. Idempotency in Pipelines
A pipeline is idempotent if running it multiple times produces the same result as running it once. Critical for safe retries and backfills.
Implementation: delete + reinsert for a date partition, use MERGE/UPSERT instead of INSERT.
Data Formats & Storage
9. Parquet vs CSV
| Parquet | CSV | |
|---|---|---|
| Format | Columnar | Row-based |
| Compression | Built-in, high | None by default |
| Analytics speed | Much faster | Slower |
| Human-readable | No | Yes |
| Schema support | Yes | No |
Use Parquet for anything going into a data lake or warehouse. Use CSV only for small data exchange or debugging.
10. Schema Evolution
Allows adding or modifying columns without rewriting all existing data. Supported by Parquet, Delta Lake, and Apache Iceberg. Critical for long-lived pipelines.
11. Partitioning in Data Lakes
Organizes files by columns (e.g., year=2024/month=08/day=01) to enable partition pruning — only the relevant directories are scanned.
s3://my-bucket/events/year=2024/month=08/day=01/part-0001.parquet
Data Quality
12. Data Quality Checks
Typical checks run at ingestion and after transformation:
- NULL validation — required fields not null
- Duplicate detection — no duplicate primary keys
- Referential integrity — foreign keys exist in dimension tables
- Range checks — dates/amounts within expected bounds
- Schema validation — column names and types match expected
- Freshness checks — data arrived within the expected SLA window
- Row count checks — compare source vs target counts
Pipeline Design
13. How would you design a scalable ETL pipeline?
Source (API / DB / S3 / Kafka)
→ S3 Landing Zone (raw, partitioned)
→ Airflow (orchestration + scheduling)
→ Glue / Spark / ECS (transform + validate)
→ Snowflake / Redshift (warehouse)
→ CloudWatch + Alerts (monitoring)
Key principles:
- Separate ingestion from transformation
- Store raw data before any transformation (make it replayable)
- Idempotent jobs — safe to rerun
- Partition by date/entity
- Retry failed partitions, not entire jobs
- Monitor row counts, execution time, SLA compliance
14. How would you process 700 million records?
- Partition data by date or entity
- Parallelize across ECS tasks or Spark workers
- Use Parquet (not CSV) — columnar reads are dramatically faster
- Process incrementally — not as one monolithic job
- Auto-scale compute to match volume
- Retry only failed partitions
Never process 700M records in a single sequential job.
15. How do you handle pipeline failures?
- Automatic retries with backoff
- Checkpointing — resume from last successful step
- Dead-letter queues for unprocessable records
- Idempotent processing — safe to rerun
- Monitoring and alerting on failure
- Audit logs per run
16. How do you monitor a data pipeline?
Key metrics to track:
- Success/failure rate per DAG run
- Execution time vs SLA baseline
- Data freshness (time since last successful load)
- Row counts (source vs target)
- Error logs and exception details
- Resource utilization (CPU, memory, DPU)
17. Walk through the architecture of a large-scale data platform you designed (e.g., a 700M-record system).
Experience-based question. Prepare to discuss:
- What problem the platform solved
- The technologies chosen (e.g., AWS, Spark, Airflow) and why
- How you handled partitioning, file formats, and scalability
- The measurable impact of the system
18. Walk through an incremental data framework you built (e.g., using dbt) and explain how it handles incremental loads.
Experience-based question. Prepare to discuss:
- How you identified new/changed records (watermarks, CDC)
- The transformation process
- How you ensured idempotency
- Performance improvements compared to full loads
19. How did you approach migrating 5 TB of data? What challenges did you encounter?
Experience-based question. Prepare to discuss:
- The migration strategy (batch vs streaming, downtime constraints)
- Data validation and ensuring integrity between source and target
- Network or throughput bottlenecks encountered
- How failures were handled and resumed
GenAI Basics
20. What is an LLM?
A Large Language Model is a deep learning model trained on massive text datasets to understand and generate human-like language (e.g., GPT-4, Claude, Gemini).
21. What is RAG (Retrieval-Augmented Generation)?
RAG combines an LLM with external knowledge retrieval — relevant documents are fetched and provided as context to the model at inference time. Reduces hallucination for domain-specific knowledge.
22. What are Embeddings?
Numerical vector representations of text that capture semantic meaning. Similar texts produce similar vectors, enabling similarity search.
23. What is a Vector Database?
A database optimized for storing and searching embeddings using similarity metrics (cosine, dot product). Examples: Pinecone, Milvus, Weaviate, pgvector.
24. What is Hallucination?
When an LLM generates incorrect or fabricated information while presenting it confidently. RAG and grounding with retrieved context reduce hallucination.
25. What is Prompt Engineering?
Designing clear and structured prompts to guide an LLM toward accurate and relevant outputs. Includes techniques like few-shot examples, chain-of-thought, and system instructions.
26. What is Function Calling?
Allows an LLM to invoke predefined functions or APIs to retrieve data or perform actions instead of generating everything from its training data. Used for real-time lookups and actions.
27. What is MCP (Model Context Protocol)?
Open protocol standardizing how AI models interact with external tools, APIs, and data sources, enabling secure and structured tool usage.
28. How would you build a document Q&A system?
- Ingest and chunk documents
- Generate embeddings for each chunk
- Store embeddings in a vector database
- At query time: embed the user question → retrieve top-k similar chunks
- Provide retrieved chunks as context to the LLM (RAG pattern)
- Return the generated answer with source references
29. What is LangChain?
A framework for developing applications powered by language models. It provides components for chaining together LLMs, vector stores, prompt templates, and tools/agents.
30. How would you deploy an LLM application on AWS?
- Use Amazon Bedrock for managed access to foundation models without managing infrastructure.
- Use SageMaker JumpStart to fine-tune or deploy open-source models (like Llama) on dedicated endpoints.
- Serve the application via ECS/Fargate or Lambda (if calling external APIs).
- Store vectors in OpenSearch Serverless or pgvector on RDS/Aurora.