Data Architect (ERP)

We are looking for Data Engineer who can connect technical fundamentals with real project delivery. The candidate should be able to write PySpark and SQL code independently, explain Spark execution and performance concepts, describe an end-to-end AWS-based ETL pipeline, and troubleshoot hypothetical production failures in a structured manner. Must-Have Skills - ETL and end-to-end data pipeline understanding - Strong hands-on PySpark and independent coding ability - Strong SQL fundamentals and independent query writing - Apache Spark architecture, execution and optimization concepts - AWS understanding for data engineering workloads - Hands-on AWS Glue Job knowledge - Python fundamentals - Relevant past Data Engineering project experience - Structured troubleshooting and scenario-based problem solving Good-to-Have Skills - Databricks - Hadoop ecosystem, including HDFS and Hive 2. PySpark - Must Have Candidates should be able to receive a problem statement and produce working DataFrame-based PySpark code with minimal prompting. Assessment should cover both coding correctness and awareness of execution cost. Core Coding Topics - DataFrame API: select, filter, withColumn, groupBy and aggregations - Joins: inner, left, right, full outer, left semi and left anti - Window functions and ranking logic - NULL handling and duplicate removal - union and unionByName - Reading and writing common formats, especially Parquet - Partitioning, repartition and coalesce - Caching and persistence - Transformations and actions - Lazy evaluation Performance and Optimization Topics - Narrow vs wide transformations - Shuffle: why it happens and how to reduce unnecessary shuffle - Data skew and salting - Broadcast join and when it is appropriate - Join strategy selection - Partition sizing and data distribution - Executor/resource awareness - Identifying slow stages and high-cost operations Spark Architecture - Must Have Candidates should understand what happens behind PySpark code and should be able to connect architecture to troubleshooting and performance decisions. Architecture and Execution Topics - Driver and executors - Cluster manager - SparkSession and SparkContext awareness - Jobs, stages and tasks - DAG and lineage - Lazy evaluation - Transformations vs actions - Narrow vs wide transformations - Partitions and parallelism - Shuffle operations - Executor memory and cores - Caching and persistence - Serialization awareness - Spark UI and execution metrics . SQL - Must Have Candidates should have strong SQL fundamentals and should be able to write complete queries independently during the interview. SQL Fundamentals - SELECT, WHERE, ORDER BY and DISTINCT - GROUP BY, HAVING and aggregate functions - CASE WHEN and NULL handling - Inner, left, right, full and self joins - Subqueries and Common Table Expressions (CTEs) - Window functions: ROW_NUMBER, RANK, DENSE_RANK, LEAD and LAG - UNION vs UNION ALL - Date functions - Duplicate identification and removal - Primary and foreign key fundamentals - Basic query optimization awareness Sample Coding Checks - Find the latest transaction for each customer. - Identify duplicates using multiple business-key columns. - Return the second-highest value with a clear tie-handling rule. - Calculate a running total by customer and transaction date. - Compare source and target tables to identify inserts and updates. 5. ETL and End-to-End Pipeline - Must Have The candidate should be able to explain a real production pipeline, including their own contribution, operating model, failure handling, and business purpose. ETL Topics - ETL vs ELT - Source ingestion and target loading - Full load vs incremental load - Batch processing - Data validation and data quality - NULL and duplicate handling - Schema management and schema changes - Upsert logic and change handling - Error/reject handling - Pipeline dependencies and orchestration - Logging, monitoring and alerting - Restart, recovery and reprocessing - Idempotent pipeline design Expected End-to-End Explanation - Business problem and expected output - Source systems and file/table formats - Data volume and arrival pattern - Ingestion mechanism - Storage layers and S3 paths - PySpark and SQL transformations - Data quality controls - AWS Glue Job configuration and scheduling/orchestration - Output target and downstream consumers - Monitoring, logging, alerts and operational support - Failure recovery and safe rerun approach - Candidate's personal responsibilities and design decisions AWS and AWS Glue, Lamda, EMR and other - Must Have AWS knowledge should be assessed from a Data Engineering perspective. The candidate should understand how AWS services participate in the pipeline and should be able to diagnose Glue Job failures. AWS Understanding - Amazon S3 storage and data organization - Lamda, Limitations of lambda - AWS Glue and PySpark processing - AWS Glue Data Catalog awareness - Glue Crawlers awareness - IAM roles, permissions and access fundamentals - Amazon CloudWatch logs and monitoring - How S3, Glue, IAM and catalog metadata interact within a pipeline AWS Glue Job Topics - Reading input data from Amazon S3 - Applying PySpark transformations - Writing processed data to the target location - Job arguments and runtime configuration - Job bookmarks / incremental-processing awareness - Glue Data Catalog integration - Logging and monitoring - Worker/resource awareness - Handling schema or data-format changes - Investigating failed and slow Glue Jobs AWS Glue Scenario Questions - A Glue Job that succeeded yesterday fails today. What would you check first? - The job cannot read from or write to an S3 path. How would you isolate the issue? - Input volume increases significantly and the job exceeds its normal runtime. What would you investigate? - A source column changes data type and the transformation fails. How would you handle the incident and prevent recurrence? - The job completes but expected output is missing or incomplete. How would you validate each pipeline stage? Expected Troubleshooting Approach - Read the exact error and review CloudWatch/Glue logs - Confirm input arrival, path, format and schema - Validate IAM role and S3 permissions - Check code, arguments, catalog metadata and recent deployment changes - Review data volume, partitioning, skew, shuffle and resource pressure - Validate output paths, write mode and partial writes - Explain recovery, rerun safety and prevention measures Python - Must Have Python assessment should focus on practical fundamentals needed to produce maintainable data engineering code. Easy-to-medium coding questions are appropriate. Topics - Data types and variables - Lists, tuples, sets and dictionaries - Loops and comprehensions - Functions and lambda functions - String manipulation - Exception handling - File-handling basics - Modules and packages basics - OOP: classes, objects, inheritance, encapsulation and polymorphism Example Coding Checks - Count the frequency of each item in a list. - Remove duplicates while preserving input order. - Find the second-largest distinct number. - Group records by a key using a dictionary. - Create a class that demonstrates simple inheritance and method overriding.