HSBC India Data Consultant Interview: Rounds, Questions and Preparation

What the job involves

A Data Consultant in GCCs, consulting, and Big 4 firms helps clients turn raw data into business decisions. Day to day, you gather requirements, clean and analyse data using SQL, Python, and BI tools, build dashboards or models, and present findings to stakeholders. You often work on regulatory, risk, or customer analytics projects for banks and large enterprises.

Role and Technical Knowledge

1. 1

   What is the difference between a primary key and a foreign key, and why does it matter in a client dataset?

   Explain uniqueness vs referential integrity, and give an example like customer_id in a transactions table linking to a customer master.

2. 2

   How would you handle missing values in a dataset for a banking client?

   Mention options: drop rows, impute with mean/median/mode, or use model-based imputation. Discuss trade-offs and business impact.

3. 3

   Explain a star schema and when you would use it over a normalised model.

   Describe fact and dimension tables, and say star schema is better for query performance and BI reporting.

4. 4

   What is the difference between WHERE and HAVING in SQL?

   WHERE filters rows before aggregation, HAVING filters after GROUP BY. Give a quick example with COUNT.

Practical Scenarios

1. 5

   A client says their monthly sales report is always late. How would you investigate and fix it?

   Ask about data sources, ETL schedule, and manual steps. Propose automation, validation checks, and a dashboard refresh schedule.

2. 6

   You are given a dataset of 1 million customer transactions. How would you find the top 10% of customers by revenue?

   Outline steps: clean data, group by customer, sum revenue, use window function or percentile rank, then filter top 10%.

3. 7

   A stakeholder wants a dashboard but cannot define the KPIs. What do you do?

   Ask about business goals, suggest common KPIs for their industry (e.g., NPS, churn rate, transaction volume), and prototype a mock-up.

4. 8

   How would you validate that a data migration from legacy systems to a new cloud warehouse was successful?

   Compare row counts, checksums, and sample records. Run reconciliation queries and check for nulls or duplicates.

Behavioural and HR

1. 9

   Tell me about a time you worked in a team to solve a problem. What was your role?

   Use STAR format. Highlight your specific contribution, communication, and the outcome.

2. 10

   Why do you want to work as a Data Consultant in a GCC or Big 4 firm?

   Link your interest in data-driven consulting, exposure to multiple clients, and the learning culture of these firms.

3. 11

   Describe a situation where you had to explain a technical concept to a non-technical person.

   Show empathy, use analogies, and focus on the business impact rather than jargon.

4. 12

   Where do you see yourself in three years in this role?

   Show ambition to grow into a senior consultant or subject matter expert, and mention continuous learning in data and domain.

Sector and Client Context

1. 13

   How would you approach a data quality issue for a bank's regulatory reporting?

   Mention data lineage, validation rules, reconciliation with source systems, and audit trails. Stress accuracy and compliance.

2. 14

   What are some common KPIs you would track for a retail banking client?

   Examples: customer acquisition cost, net promoter score, loan default rate, digital adoption rate, and cross-sell ratio.

3. 15

   How can data analytics help reduce customer churn in a telecom or banking context?

   Talk about predictive models, segmentation, early warning signals, and targeted retention offers.

4. 16

   What is the role of a data consultant in a digital transformation project?

   Explain bridging business and technology, defining data strategy, ensuring data governance, and enabling self-service analytics.