Data Engineering Fundamentals Q&A

Answer: The practice of designing, building, and maintaining systems for collecting, storing, and analyzing large data volumes through pipelines and storage optimization.

• Designing and building data pipelines
• Maintaining data warehouses and lakes
• Ensuring data quality and consistency
• Optimizing storage and query performance
• Collaborating with data scientists and analysts
• Implementing data security measures

Answer: A series of processes that move data from sources to destinations, involving extraction, transformation, and loading steps. Can be batch (scheduled) or streaming (real-time).

• Handling large volumes efficiently (scale)
• Ensuring data quality and consistency
• Managing real-time vs batch processing trade-offs
• Integrating diverse data sources
• Maintaining security and compliance
• Handling schema evolution

Answer: Organizing data to reduce redundancy and improve integrity by breaking tables into smaller, related ones using foreign keys.

• 1NF: Atomic values, no repeating groups
• 2NF: 1NF + no partial dependencies
• 3NF: 2NF + no transitive dependencies

Use SQL when: ACID matters, complex queries, structured data Use NoSQL when: Scale > consistency, schema flexibility, high write throughput

Answer: Creating data structures (B-trees, hash indexes) for faster data retrieval without scanning entire tables.

• Pros: Faster reads
• Cons: Slower writes, storage overhead
• Best for: Frequently queried columns, WHERE/JOIN clauses

• Atomicity: All or nothing - transaction completes fully or not at all
• Consistency: Database remains valid after transaction
• Isolation: Concurrent transactions don’t interfere
• Durability: Committed changes persist

Answer: Functions that operate across a set of rows related to the current row.

-- Running total
SUM(amount) OVER (ORDER BY date)

-- Rank within partition
RANK() OVER (PARTITION BY department ORDER BY salary DESC)

-- Previous row value
LAG(value, 1) OVER (ORDER BY date)

Answer: A fast, in-memory distributed data processing engine supporting batch, streaming, ML, and SQL workloads. Why popular:

• 100x faster than MapReduce (in-memory)
• Unified API for batch + streaming
• Rich ecosystem (MLlib, GraphX, Spark SQL)
• Multiple language support (Python, Scala, Java, R)

Answer: A distributed streaming platform for publishing/subscribing to record streams with fault-tolerant storage. Key concepts:

• Topics: Categories of messages
• Partitions: Parallelism within topics
• Consumer Groups: Load balancing reads
• Retention: Configurable message storage

Answer: Dividing datasets into smaller parts for better performance and parallelism. Types:

• Range: By value ranges (e.g., date)
• Hash: By hash of key (even distribution)
• List: By categorical values (e.g., region)

1. Salting: Add random prefix to skewed keys
2. Broadcast join: For small dimension tables
3. AQE: Enable Adaptive Query Execution
4. Repartition: Redistribute data evenly
5. Custom partitioner: Based on data distribution

• Extract: Retrieve data from source systems (APIs, DBs, files)
• Transform: Clean, validate, convert, aggregate, enrich
• Load: Insert into target system (warehouse, lake)

ETL vs ELT:

• ETL: Transform before loading (traditional)
• ELT: Load raw, transform in target (modern, cloud-native)

Answer: A subset of a data warehouse focused on a specific business line or department (e.g., sales mart, HR mart). Faster queries, simpler for end users.

Answer: Object storage service providing scalable, durable storage.

• Storage classes: Standard, IA, Glacier
• Use cases: Data lakes, backups, static hosting
• Key features: 11 9s durability, versioning, lifecycle policies

Answer: Unified analytics platform built on Spark, offering:

• Lakehouse: Combines lake + warehouse
• Delta Lake: ACID on data lakes
• Unity Catalog: Governance and security
• MLflow: ML lifecycle management

Answer: Cloud execution model where provider manages infrastructure. Examples:

• AWS Lambda, Azure Functions, GCP Cloud Functions
• BigQuery, Athena, Synapse Serverless Pros: No infrastructure management, pay-per-use Cons: Cold starts, execution limits, vendor lock-in

• Easy to learn and read
• Rich data libraries (Pandas, NumPy, PySpark)
• Strong community and ecosystem
• Great for prototyping and production
• API integration capabilities

Answer: Python API for Apache Spark enabling distributed data processing using familiar Python syntax.

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("example").getOrCreate()
df = spark.read.parquet("data.parquet")
result = df.groupBy("category").agg({"amount": "sum"})

Answer: Transformations are not executed immediately; Spark builds a DAG (Directed Acyclic Graph) and only executes when an action is called.

• Transformations (lazy): map, filter, select, groupBy
• Actions (trigger): count, collect, write, show

1. Driver: Coordinates execution, holds SparkContext
2. Executors: Run tasks on worker nodes
3. Stages: Groups of tasks between shuffles
4. Tasks: Smallest unit of work (one partition)

Answer: Technique for organizing data in warehouses for easy querying, using:

• Fact tables: Measures/metrics (sales amount, quantity)
• Dimension tables: Context (who, what, when, where)

Answer: The level of detail in each row. Define grain first, then design around it.

• One row per order
• One row per order line item
• One row per customer per day

Answer: Enterprise modeling pattern with:

• Hubs: Business keys (e.g., customer_id)
• Links: Relationships between hubs
• Satellites: Attributes over time

Pros: Flexible, auditable, handles change Cons: Complex queries, needs BI layer on top

Answer: System-generated unique identifier (auto-increment, UUID) vs natural key (business key like SSN). Why use surrogate?

• Business keys can change
• Enables SCD tracking
• Better performance (integer vs string)

Answer: Guarantee that each record is processed exactly one time, not lost or duplicated. How to achieve:

• Idempotent writes
• Transactional producers/consumers
• Checkpointing

Answer: Mechanism to handle late-arriving data by tracking event-time progress.

df.withWatermark("event_time", "10 minutes")

“I’ve seen all events with timestamp <= watermark. Later events may be dropped.”

1. Validation: Schema checks, type enforcement
2. Testing: Unit tests, integration tests
3. Profiling: Statistics, distributions
4. Monitoring: Anomaly detection, alerts
5. Governance: Ownership, documentation
6. Tools: Great Expectations, dbt tests, Soda

Answer: Tracking data flow from source to destination, showing transformations at each step. Benefits:

• Impact analysis (what breaks if source changes?)
• Debugging (where did bad data come from?)
• Compliance (prove data handling for audits)

Answer: EU regulation on data privacy requiring:

• Right to deletion: Must be able to delete user data
• Data minimization: Only collect what’s needed
• Consent: Clear opt-in required
• Breach notification: Report within 72 hours

Engineering impact: Audit logs, deletion pipelines, encryption

Key points:

• Kafka for event ingestion
• Feature store (Redis) for real-time lookups
• ML model for scoring
• Rules engine for known patterns
• Sub-100ms latency requirement
• Exactly-once semantics

Key points:

• Assess current state, dependencies
• Parallel run both systems
• Validate data match (row counts, checksums)
• Incremental migration (table by table)
• Rollback plan
• Documentation and training

Key points:

• Check logs from Monday failures
• Look for patterns (time, data volume, sources)
• Consider: weekend batch jobs, data accumulation
• Monday-specific business events
• Resource contention from other Monday jobs

Key points:

• Use schema-on-read formats (Parquet, Avro)
• Schema registry (Confluent)
• Backward/forward compatibility
• Versioning and migration scripts
• Default values for new fields

Steps:

1. Check Spark UI for bottlenecks
2. Look for data skew (one task much longer)
3. Check for shuffle spill (disk I/O)
4. Verify partition pruning
5. Consider broadcast joins for small tables
6. Enable AQE (Adaptive Query Execution)
7. Tune memory/executor settings