Q: Implement a string compression algorithm. Example: Input "AAABBBCCCDDDDAAA" → Output "A3B3C3D4A3"

This is run length encoding (RLE) — compress consecutive repeating characters. Complexity: O(n) time, O(k) space where k is the number of distinct runs. Edge cases to handle: Empty string → return empty string Single character → "A" → "A1" No consecutive repeats → each char gets count 1 Follow up variation: Only compress if result is shorter than original: This is used in image compression (BMP), network protocols, and genomics sequence encoding.

Q: Write a query to find the 5th highest salary from the employee table.

Method 1: Using DENSE RANK() (recommended) DENSE RANK handles ties correctly — if two employees share the 4th highest salary, the 5th distinct salary is still returned correctly. Method 2: Using LIMIT/OFFSET Method 3: Subquery (works in older SQL) Generalizing to Nth highest: Important: If there are fewer than 5 distinct salaries, all methods return no rows — handle this with COALESCE or a default value in application code.

Q: Find the number of employees earning more than their manager.

Use a self join to compare each employee's salary with their manager's salary: To list the employees (not just count): Schema assumed: where manager id references employee id in the same table (self referential FK). Edge cases: Employees with manager id IS NULL (top level executives) are excluded from the join — correct behavior If an employee's manager doesn't exist in the table, INNER JOIN excludes them; use LEFT JOIN ... WHERE e.salary COALESCE(m.salary, 0) to include orphaned records Result interpretation: A high count suggests either flat salary bands or poorly structured compensation tiers.

Q: Replicate the above SQL logic using PySpark.

Key PySpark concepts used: alias() — creates named references for self join disambiguation col("e.salary") — references columns from specific alias how="inner" — excludes top level managers with no manager id match

Question 1

Explain the types of data you've worked with (structured vs semi-structured).

Accepted Answer

Structured data has a fixed schema stored in tabular format — rows and columns with predefined data types. Examples: relational database tables (MySQL, PostgreSQL), CSV files, data warehouse tables in Redshift or BigQuery. Semi structured data has flexible schema with self describing tags/keys. Examples: JSON (REST API responses, Kafka events), XML (legacy systems), Avro (schema embedded), Parquet/ORC (columnar with embedded schema). Unstructured data has no schema: raw logs, images, PDFs, free text. In practice as a data engineer: Ingesting JSON events from Kafka using PySpark's spark.read.json() with schema specified upfront to avoid schema inference cost Processing CSV files from S3 with explicit schema validation Working with Avro in Kafka for schema registry integration Storing intermediate results in Parquet for columnar compression and predicate pushdown Key challenge with semi structured data: Schema drift — when JSON fields are added/removed. Handled by using from json() with explicit schema in PySpark and enabling schema evolution in Delta Lake. Data engineers typically need to convert semi structured data into structured form for analytical workloads — this is the core of most ETL pipelines.

Question 2

Describe the data flow from ingestion to storage in your recent pipeline.

Accepted Answer

A typical modern data pipeline follows this flow: 1. Ingestion layer Source systems (transactional DBs, APIs, IoT devices, Kafka streams) emit data For batch: Airflow triggers ingestion jobs on a schedule For streaming: Kafka producers push events; Spark Structured Streaming or Flink consumes them 2. Raw/Bronze layer (landing zone) Data is written as is to cloud storage (S3/ADLS/GCS) in Parquet or Avro format No transformations — this is the source of truth for replays Partitioned by ingestion date 3. Cleansed/Silver layer PySpark jobs apply: deduplication, null handling, type casting, schema standardization Written as Delta Lake tables for ACID guarantees and time travel Partitioned by business date 4. Aggregated/Gold layer Business level aggregations and joins: daily KPIs, customer 360 views, revenue summaries Consumed by BI tools (Tableau, Power BI), ML models, APIs 5. Orchestration Airflow DAGs manage dependencies between Bronze → Silver → Gold jobs Alerts configured for SLA breaches 6. Monitoring Row count checks at each layer boundary Schema drift detection alerts Data quality scores tracked in a metadata table

Question 3

What tech stack did you use and what were the key challenges?

Accepted Answer

Typical data engineering stack: Layer Technologies Ingestion Kafka, Debezium (CDC), REST APIs, Airbyte Processing Apache Spark (PySpark), dbt, Pandas Orchestration Apache Airflow, Prefect Storage S3/ADLS/GCS (raw), Delta Lake/Iceberg (processed), Snowflake/Redshift/BigQuery (warehouse) CI/CD GitHub Actions, Terraform, Docker Monitoring Great Expectations, Monte Carlo, CloudWatch Common key challenges: 1. Data skew — Uneven partition sizes causing Spark executor OOM. Solved with salting or broadcast joins. 2. Schema drift — Source systems adding/changing columns without notice. Solved with schema registry (Confluent) and Delta Lake schema evolution. 3. Late arriving data — Events arriving after the processing window. Solved with watermarking in Spark Structured Streaming and reprocessing logic. 4. Pipeline reliability — Intermittent failures from flaky APIs or network issues. Solved with retry logic, dead letter queues, and Airflow retry configs. 5. Scaling cost — EMR/Dataproc clusters running idle. Solved by switching to spot instances, autoscaling, and serverless options (Databricks Serverless, Athena). 6. Cross team data ownership — Unclear who owns fixing data quality issues. Solved by establishing data contracts and SLAs per domain team.

Question 4

Describe the size and scale of datasets you've handled.

Accepted Answer

Scale benchmarks give context for architecture decisions: Small scale (< 1 GB / < 1M rows): Processed with Pandas or standard SQL No distributed computing needed Example: daily config sync tables, dimension tables Medium scale (1 GB – 1 TB / millions of rows): PySpark on a small cluster (4 8 nodes) or Databricks serverless Partitioning by date is critical for query performance Example: daily transaction logs for a mid size e commerce platform Large scale (1 TB – 100 TB / billions of rows): PySpark on a large cluster (20 100 nodes) Delta Lake for ACID + Z ordering for query optimization Partition pruning and predicate pushdown are essential Example: clickstream events for a large retailer Very large scale (100 TB+ / trillions of events): Requires careful partitioning strategy, compaction jobs, and tiered storage Snowflake/BigQuery for the warehouse layer Example: telemetry data from IoT devices Common scale related challenges: At 10B+ rows, even COUNT( ) can be slow without proper statistics Shuffle partitions need tuning ( spark.sql.shuffle.partitions ) File sizes matter: aim for 128 MB–1 GB files to avoid small file problem Cost management becomes critical — storage tiering and lifecycle policies

Question 5

Implement a string compression algorithm.
Example: Input "AAABBBCCCDDDDAAA" → Output "A3B3C3D4A3"

Accepted Answer

This is run length encoding (RLE) — compress consecutive repeating characters. Complexity: O(n) time, O(k) space where k is the number of distinct runs. Edge cases to handle: Empty string → return empty string Single character → "A" → "A1" No consecutive repeats → each char gets count 1 Follow up variation: Only compress if result is shorter than original: This is used in image compression (BMP), network protocols, and genomics sequence encoding.

Question 6

Write a query to find the 5th highest salary from the employee table.

Accepted Answer

Method 1: Using DENSE RANK() (recommended) DENSE RANK handles ties correctly — if two employees share the 4th highest salary, the 5th distinct salary is still returned correctly. Method 2: Using LIMIT/OFFSET Method 3: Subquery (works in older SQL) Generalizing to Nth highest: Important: If there are fewer than 5 distinct salaries, all methods return no rows — handle this with COALESCE or a default value in application code.

Question 7

Find the number of employees earning more than their manager.

Accepted Answer

Use a self join to compare each employee's salary with their manager's salary: To list the employees (not just count): Schema assumed: where manager id references employee id in the same table (self referential FK). Edge cases: Employees with manager id IS NULL (top level executives) are excluded from the join — correct behavior If an employee's manager doesn't exist in the table, INNER JOIN excludes them; use LEFT JOIN ... WHERE e.salary COALESCE(m.salary, 0) to include orphaned records Result interpretation: A high count suggests either flat salary bands or poorly structured compensation tiers.

Question 8

Replicate the above SQL logic using PySpark.

Accepted Answer