American Express Big Data Engineer interview questions

Screening call followed by interview Teams. Interview went for 40 mins. Started with asking my skill and tech stack then current project responsibilities. questions asked on I have worked extensively with **reporting and ETL tools** across different environments. My experience includes working with: * **dbt (Data Build Tool):** Mainly for transformation-focused ELT processes on cloud data warehouses. * **Metallion:** To optimize platforms like **Snowflake, Redshift, and BigQuery**. * **Informatica PowerCenter:** For **advanced data governance** and handling very large datasets in enterprise data warehouses. To optimize ETL/ELT processes, I have implemented various techniques, ensuring better performance and governance. --- ### Example Project: Ford Motors (Data Assessment & Migration) One of the major projects I worked on was with **Ford Motors**, where I was responsible for **data assessment, migration planning, and ETL pipeline development**. **Scope & Responsibilities:** * Analyzed the **SQL Server database schema** and identified critical tables for migration. * Performed **data assessment and migration planning** to move data from SQL Server to Snowflake. * Developed **Azure Data Factory (ADF) pipelines** for batch and streaming data processing. * Handled **deployment, optimization, and version control** for ETL pipelines. * Managed **end-to-end lifecycle**: development, testing, and production deployments. **Key Achievements:** * Successfully migrated **50 TB of data** from SQL Server to Snowflake. * Achieved **30% cost reduction** in storage and compute by optimizing ELT processes. * Improved **query performance by 40%** through optimized **star schema modeling**. --- ### Technical Strengths * Strong experience integrating and implementing ETL on cloud platforms using **SQL and Snowflake**. * Set up **GCP and Snowflake environments** to handle extraction, transformation, and loading of data. * Automated ETL processes using **GCP Cloud Functions**, scheduling, and monitoring. * Advanced SQL skills (self-rated **4 out of 5**). ---