Q: Explain the concept of a Data Mesh and its benefits.

Data Mesh is an architectural approach that decentralizes data ownership — treating data as a product owned by domain teams rather than a centralized data engineering team. Four principles : 1. Domain ownership : each business domain (sales, marketing, logistics) owns and publishes their own data products 2. Data as a product : domain teams treat their datasets like products — SLAs, documentation, quality guarantees 3. Self serve infrastructure : a central platform team provides infrastructure tools (compute, storage, cataloging) that domains use independently 4. Federated governance : global standards (security, schema conventions, access control) with local autonomy Benefits : Eliminates the central DE team bottleneck Domain teams have context to ensure data quality Faster iteration — no handoffs to a central team Trade offs : requires mature engineering culture; discovery and lineage across domains is harder; standards enforcement is complex.

Q: How do you choose the right file format for a big data pipeline (Parquet vs ORC vs Avro)?

Format Storage Best For Parquet Columnar Analytics, BI queries, Spark/Presto/Athena ORC Columnar Hive heavy workloads, better Hive compression Avro Row based Kafka serialization, write heavy ingestion, schema evolution Rules : Analytical reads (aggregate many rows, few columns) → Parquet or ORC Streaming ingestion (write one record at a time, Kafka) → Avro Row by row processing (export to external systems) → Avro or CSV Parquet vs ORC : Parquet: wider ecosystem support (Spark, Presto, BigQuery, Athena) ORC: slightly better compression, tighter Hive integration Default choice: Parquet (broader compatibility) Compression : Snappy for speed; ZSTD for better ratio when storage cost matters.

Q: What is the purpose of Z-ordering in Databricks Delta tables?

Z ordering is a multi dimensional clustering technique that co locates related rows across multiple columns in the same set of files. How it works : maps rows to file locations using a Z curve (space filling curve) so that rows with similar customer id AND date values end up in the same files. When you query: Delta Lake's data skipping reads only the files whose min/max statistics cover those values — massively fewer files scanned. vs single column partitioning : Partitioning works on one column (usually date) — great for date filters Z ordering adds secondary clustering within each partition for additional columns Best used on : high cardinality columns you frequently filter on (user id, product id) that you can't partition by (too many distinct values → too many small files).

Q: Explain how caching works in Spark and when to use it.

cache() / persist() : tells Spark to store the computed DataFrame/RDD in memory (or disk) after first evaluation, so it doesn't recompute from scratch on subsequent actions. Storage levels : MEMORY ONLY : fastest, evicts on OOM MEMORY AND DISK : spills to disk if memory full DISK ONLY : use when memory is very tight When to cache : DataFrame is used in multiple downstream actions Expensive recomputation (complex joins, large file reads) When NOT to cache : DataFrame used only once Very large DataFrames that don't fit in memory — cache thrashing is worse than recomputation

Question 1

What are the main differences between Data Lake and Data Warehouse?

Accepted Answer

Data Lake Data Warehouse Schema Schema on read (applied at query time) Schema on write (enforced at load time) Data types Any — raw files, JSON, images, logs Structured data only Storage Cheap object storage (S3, ADLS) More expensive compute+storage Processing Flexible — Spark, Presto, ML SQL optimized Governance Harder to control (data swamp risk) Strong structure and governance Modern trend : Data Lakehouse (Delta Lake, Iceberg) combines both — cheap object storage + ACID transactions + SQL performance. Bronze/Silver/Gold layers replace the traditional lake→warehouse ETL hop.

Question 2

How do you implement Slowly Changing Dimensions (SCD) in ETL?

Accepted Answer

SCD Type 1 — overwrite (no history): SCD Type 2 — track history with versioning: dbt snapshots (automated SCD Type 2): dbt handles the expire+insert logic automatically based on the source's updated at column.

Question 3

Explain Delta Lake and its advantages over traditional data lakes.

Accepted Answer

Delta Lake is an open source table format that adds reliability features on top of Parquet files stored in object storage. Key advantages : ACID transactions : concurrent reads and writes are safe — no partial writes seen by readers Schema enforcement : rejects writes that violate the table schema Schema evolution : mergeSchema=true allows adding new columns without breaking existing readers Time travel : every write creates a versioned snapshot. Query any past version: DML support : UPDATE , DELETE , MERGE INTO on Parquet files Audit log : DESCRIBE HISTORY table shows every write operation Auto optimize : OPTIMIZE compacts small files; Z ordering clusters related data vs plain Parquet : plain Parquet has no transactions, no deletes, no schema enforcement — Delta fixes all of these.

Question 4

How do you optimize a PySpark job for performance?

Accepted Answer

Avoid wide shuffles : Use broadcast() for small dimension tables (< 200 MB) — eliminates shuffle entirely Repartition on join key before joining: df.repartition(200, "customer id") Reduce data volume early : Filter and project ( select ) as early as possible in the DAG Use partition pruning — filter on partition columns Partition tuning : Target 128 MB–1 GB per partition Tune spark.sql.shuffle.partitions (default 200 — often too many for small data) Use built in functions over UDFs : Cache wisely : df.cache() only if the DataFrame is reused multiple times — caching unused data wastes memory. Adaptive Query Execution (AQE) : set spark.sql.adaptive.enabled=true — auto tunes shuffle partitions and join strategies at runtime.

Question 5

Explain the concept of a Data Mesh and its benefits.

Accepted Answer

Data Mesh is an architectural approach that decentralizes data ownership — treating data as a product owned by domain teams rather than a centralized data engineering team. Four principles : 1. Domain ownership : each business domain (sales, marketing, logistics) owns and publishes their own data products 2. Data as a product : domain teams treat their datasets like products — SLAs, documentation, quality guarantees 3. Self serve infrastructure : a central platform team provides infrastructure tools (compute, storage, cataloging) that domains use independently 4. Federated governance : global standards (security, schema conventions, access control) with local autonomy Benefits : Eliminates the central DE team bottleneck Domain teams have context to ensure data quality Faster iteration — no handoffs to a central team Trade offs : requires mature engineering culture; discovery and lineage across domains is harder; standards enforcement is complex.

Question 6

How do you choose the right file format for a big data pipeline (Parquet vs ORC vs Avro)?

Accepted Answer

Format Storage Best For Parquet Columnar Analytics, BI queries, Spark/Presto/Athena ORC Columnar Hive heavy workloads, better Hive compression Avro Row based Kafka serialization, write heavy ingestion, schema evolution Rules : Analytical reads (aggregate many rows, few columns) → Parquet or ORC Streaming ingestion (write one record at a time, Kafka) → Avro Row by row processing (export to external systems) → Avro or CSV Parquet vs ORC : Parquet: wider ecosystem support (Spark, Presto, BigQuery, Athena) ORC: slightly better compression, tighter Hive integration Default choice: Parquet (broader compatibility) Compression : Snappy for speed; ZSTD for better ratio when storage cost matters.

Question 7

What is the purpose of Z-ordering in Databricks Delta tables?

Accepted Answer

Z ordering is a multi dimensional clustering technique that co locates related rows across multiple columns in the same set of files. How it works : maps rows to file locations using a Z curve (space filling curve) so that rows with similar customer id AND date values end up in the same files. When you query: Delta Lake's data skipping reads only the files whose min/max statistics cover those values — massively fewer files scanned. vs single column partitioning : Partitioning works on one column (usually date) — great for date filters Z ordering adds secondary clustering within each partition for additional columns Best used on : high cardinality columns you frequently filter on (user id, product id) that you can't partition by (too many distinct values → too many small files).

Question 8

Explain how caching works in Spark and when to use it.

Accepted Answer

cache() / persist() : tells Spark to store the computed DataFrame/RDD in memory (or disk) after first evaluation, so it doesn't recompute from scratch on subsequent actions. Storage levels : MEMORY ONLY : fastest, evicts on OOM MEMORY AND DISK : spills to disk if memory full DISK ONLY : use when memory is very tight When to cache : DataFrame is used in multiple downstream actions Expensive recomputation (complex joins, large file reads) When NOT to cache : DataFrame used only once Very large DataFrames that don't fit in memory — cache thrashing is worse than recomputation

Capgemini Interview Questions