Scenario-Based Q&A

Interview Answer: In this situation, I would first identify duplicate records using the primary key or a combination of business keys. In Spark, I would use dropDuplicates() to remove duplicates before loading the data. I would also check the source to understand why duplicates were generated. Finally, I would add data quality checks so duplicates are detected automatically in future pipeline runs.

Interview Answer: First, I would check the Glue job logs in CloudWatch to identify the bottleneck. Then, I would check for data skew, unnecessary shuffles, or an increase in input data size. I would optimize the job using partitioning, caching if needed, and converting files to Parquet. Finally, I would monitor the job again to verify the improvement.

Interview Answer: I would first check the Spark UI to identify which stage is causing the memory issue. Then, I would avoid collect() on large datasets, increase executor memory if required, reduce data shuffling, and repartition the data properly. If the issue is caused by data skew, I would use salting or broadcast joins.

Interview Answer: I would enable schema evolution so the pipeline can handle new columns automatically. In AWS Glue, I would update the Glue Crawler or Data Catalog. In Spark, I would use schema merging while reading the data. This ensures the pipeline continues to run even when new columns are added.

Interview Answer: I would choose the correct Distribution Key and Sort Key, use column compression, and load data in bulk using S3. I would also convert frequently used queries into optimized SQL and monitor query performance. This improves both performance and reduces storage and compute costs.

Interview Answer: I would first check the Redshift query plan and system tables to identify the slow operation. Then, I would verify the Distribution Key and Sort Key, create or update statistics using ANALYZE, and remove unnecessary joins. I would also rewrite the query if required to improve performance.

Interview Answer: I would first check the Airflow logs to identify the missing connection. Then, I would verify the connection details in the Airflow UI under Admin → Connections. After updating the correct credentials, I would test the connection and rerun the failed task. Finally, I would document the configuration to avoid similar issues.

Interview Answer: I would add validation checks before starting the ETL process. The pipeline should verify that all three source files are available and complete. If any source is missing, the pipeline should stop, send an alert, and avoid loading incomplete data. This ensures data accuracy.

Interview Answer: I would redesign the pipeline using streaming technologies such as Kafka, Spark Structured Streaming, or AWS Kinesis. Incoming data would be processed continuously and stored in the target system. This would allow dashboards to refresh every few seconds or minutes instead of waiting for batch jobs.

Interview Answer: I would convert the CSV files into Parquet format using AWS Glue because Parquet is columnar and much faster for Athena queries. I would also partition the data based on columns like date or region and compress the files. This reduces the amount of data scanned, improving both query performance and cost.

Interview Answer: I would first identify the skewed key using the Spark UI or by checking the data distribution. Then, I would use salting to split the heavy key into multiple partitions or use a broadcast join if one table is small. This helps distribute the data evenly and improves job performance.

Interview Answer: I would identify all PII columns such as name, email, and phone number. Sensitive data would be masked or encrypted before storing it. I would also use IAM roles and encryption for S3 to ensure only authorized users can access the data.

Interview Answer: I would compare the source and target data to identify changed records. The old record would be marked as inactive, and a new record would be inserted with the updated information. I would store the data in Parquet format and partition it to improve query performance.

Interview Answer: I would optimize Spark jobs by reducing shuffles, using partitioning, and processing only incremental data instead of the full dataset. I would also monitor job performance and increase cluster resources if needed. This helps the pipeline finish within the SLA.

Interview Answer: I would package all required Python libraries before deployment and include them with the Glue job or Lambda function. I would test the deployment in the development environment before releasing it to production. This ensures all dependencies are available during execution.

Interview Answer: I would implement retry logic with delays if the API limit is reached. I would load only new or updated records using timestamps or IDs instead of fetching all data every time. This reduces API calls and improves pipeline performance.

Interview Answer: I would first verify whether the ETL pipeline completed successfully. Then, I would compare the source and target data, check for partial loads, and validate row counts. Finally, I would add data quality checks and alerts to ensure only complete data is available for reporting.

Interview Answer: I would divide the large pipeline into smaller independent ETL jobs. This makes the pipeline easier to maintain, test, and troubleshoot. Each job can run separately, making deployments faster and failures easier to isolate.

Interview Answer: I would collect data from all sources, clean and validate it, remove duplicates, and standardize the format. Then, I would join the datasets using common keys and perform data quality checks before storing the final dataset in the data lake.

Interview Answer: I would store backup data in another AWS region using cross-region replication. I would enable automated backups and design the pipeline to fail over to the secondary region if the primary region becomes unavailable. This ensures business continuity with minimum downtime.

Interview Answer: I would use Amazon Kinesis to capture streaming data, process it using AWS Lambda or Spark Streaming, and apply fraud detection rules. If a suspicious transaction is found, I would send an alert using Amazon SNS or email. This provides near real-time notifications.

Interview Answer: First, I would check the job logs in CloudWatch or Airflow to find the root cause. After fixing the issue, I would rerun only the failed job instead of the entire pipeline. Then, I would validate the loaded data and notify the stakeholders once the job is completed.

Interview Answer: Cross Join: Every row of TABLE1 joins with every row of TABLE2 (6 × 5 = 30 rows). Left Join: Returns all rows from TABLE1 and matching rows from TABLE2. Unmatched rows show NULL. Right Join: Returns all rows from TABLE2 and matching rows from TABLE1. Full Outer Join: Returns all matching and non-matching rows from both tables.

Interview Answer: I would use checkpointing or incremental processing. The pipeline stores the last successfully processed record or batch. After fixing the issue, it resumes from that point instead of starting from the beginning. This saves both time and compute cost.

Interview Answer: I would process the file in smaller chunks instead of loading the entire file into memory. Depending on the requirement, I would use Python generators, Pandas chunk processing, or batch processing. This prevents memory issues and improves performance.

Interview Answer: I would schedule the API calls using Airflow or Lambda. Each store would have its own task with retry logic and error handling. Failed API calls would be logged and retried without affecting the successful stores. Finally, all data would be stored in S3.

Interview Answer: I first extract data from the source system or API. Then I clean the data by removing duplicates, handling missing values, and validating business rules. Finally, I load the cleaned data into Redshift or S3, ensuring the KPI calculations are accurate.

Interview Answer: I remove duplicate records using business keys or primary keys before loading the data. In Spark, I use dropDuplicates() and perform data quality checks. This ensures only unique records are sent to downstream systems.

Interview Answer: I would create separate ingestion jobs for each vendor and store the raw data in S3. Then I would build transformation jobs to standardize the data and calculate the required metrics. Airflow would schedule the jobs, and the final data would be loaded into Redshift for reporting.

Interview Answer: I would create tables such as Customers, Products, Orders, Order_Items, Payments, and Shipments. Important columns include Customer_ID, Product_ID, Order_ID, Quantity, Price, Payment_Status, Order_Date, and Delivery_Status. The pipeline extracts data, cleans it, and loads it into the data warehouse for analytics.

Interview Answer: I would use incremental loading instead of full loading, process data in parallel, optimize batch size, and reduce unnecessary transformations. I would also monitor the pipeline to identify bottlenecks and tune resources. This helps reduce the processing time significantly.

Interview Answer: One production issue I faced was duplicate records being loaded because the source system resent the same file. I identified the issue using row count validation and removed duplicates using business keys before loading the data. I also added duplicate validation checks to prevent the issue in future runs.

Interview Answer: Our project SLA was that the complete ETL pipeline had to finish before 8:00 AM every day so that business users could access fresh reports. We continuously monitored the pipeline, optimized Spark jobs, and configured alerts to ensure the SLA was always met.