ICICI Bank Data Analytics Interview Questions & Answers
Curated interview practice questions with answers. These are practice scenarios, not a verified list of past interview questions.
About ICICI Bank hiring for Data Analytics roles
ICICI Bank is a large Indian private sector bank offering retail, corporate, digital and international banking. Its technology, analytics and operations teams work on products like iMobile Pay, credit decisioning, fraud detection and customer analytics. It hires for data analysts, risk analysts, testers, software engineers and management trainees.
For Data Analytics roles, this ICICI Bank practice set focuses most on Power BI (7), SQL (5), Data Storytelling (5), Data Modelling & Warehousing (4). It has 6 easy, 29 medium, 5 hard questions, so you can start easy and work up to the harder ones.
Company type
Banking & finance
Headquarters
Mumbai, India
Main India offices
Mumbai, Hyderabad, Chennai, Bengaluru
ICICI Bank Data Analytics interview process
Online assessment (aptitude + domain)
Technical or functional interview
Managerial round
HR round
Typical pattern based on public candidate experiences. Rounds vary by role, team and year.
Solved for ICICI BankStart practicing
3 QUESTIONS FREESign up to unlock the full question bank and track your progress.Unlock every question and answer, then continue practising company-wise and package-wise.
✓ 3 questions free • Unlock the rest in seconds
01
What is the medallion architecture (bronze, silver, gold)?
Medallion architecture organises a lakehouse into three layers. Bronze holds raw data exactly as ingested, for audit and reprocessing. Silver holds cleaned, deduplicated, typed and joined data. Gold holds business-ready tables such as star schemas and aggregates used by dashboards. Each layer is built from the previous one by repeatable jobs, so if a cleaning rule changes you can rebuild silver and gold from bronze.
SUMIFS adds values that meet several conditions; COUNTIFS counts rows that meet several conditions.
Example: =SUMIFS(Sales, Region, "North", Month, "Jan")
03
What are relationships and cardinality in Power BI?
Links between tables on key columns; cardinality can be one-to-many, many-to-one or many-to-many, and cross-filter direction controls how filters flow.
Keep a cleaning log or README listing each rule, why it exists and its impact, for example removed 1,240 test orders flagged by email domain. Code-based cleaning in SQL, Python or Power Query steps is itself documentation if steps are named clearly. Record counts before and after each major step. This lets reviewers trust the numbers, helps a colleague rerun the work, and answers the question why your total differs from the raw file.
05
Sales dropped 20% last month. How will you analyse it?
Confirm data accuracy first → break down by region, product, channel and customer segment → compare with seasonality and last year → check pricing, stock-outs, marketing changes and funnel conversion → present the root causes with clear charts.
06
What is the difference between a t-test and a z-test?
Both compare means. A z-test assumes the population standard deviation is known or the sample is large, so the normal distribution applies. A t-test estimates standard deviation from the sample and uses the t-distribution, which has heavier tails for small samples. In practice analysts mostly use t-tests: one-sample, independent two-sample (Welch's version if variances differ) or paired, for before-after measurements on the same users.
07
Write pandas code to calculate customer acquisition cost and payback period in months for each channel.
ROW_NUMBER gives a unique sequence. RANK gives ties the same rank and skips the next (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
09
You must present to the sales team that they missed their quarterly target by 18%. How do you handle it?
Lead with the fact clearly, then show where the gap came from, such as which regions, products or deal stages, so it becomes actionable rather than a blame session. Separate controllable causes like lower conversion from uncontrollable ones like a delayed product launch. Highlight what did work, and end with two or three specific actions and the metrics to track them. Share the numbers with sales leaders privately first, to avoid surprises.
10
Checkout conversion fell from 3.2% to 2.6% the week after an app release. How do you investigate?
First confirm it is real: check tracking events, data delays and whether the denominator changed. Then segment by app version, platform, device, payment method and traffic source. If the drop is concentrated in the new version or one payment option, it points to a bug or UX change. Look at funnel steps to find where users drop. Compare with seasonality and campaigns, then share findings with product and engineering quickly.
11
How would you present the same churn analysis to the CEO and to the customer support team?
For the CEO, lead with the headline and money: churn rose 2 points, costing about 3 crore annually, mainly from one segment, with two recommended actions and their expected impact, all on one or two slides. For the support team, go deeper into drivers they control: ticket types, resolution times and specific customer pain points, with operational next steps and how progress will be tracked. Same data, different level and call to action.
COALESCE returns the first non-null value from its arguments, for example COALESCE(phone, alt_phone, 'Not given') fills missing phone numbers. NULLIF(a, b) returns NULL when a equals b, otherwise a. Its classic use is avoiding division by zero: revenue / NULLIF(orders, 0) returns NULL instead of an error when orders is zero. Together they handle most null-related reporting problems cleanly.
13
Many reporting queries in your team use SELECT DISTINCT to remove duplicate rows. What is the risk?
SELECT DISTINCT often hides a real problem, such as a join on the wrong key or a one-to-many join that multiplies rows. The output looks clean, but sums computed before the DISTINCT, or legitimate repeated rows, can be wrong, and DISTINCT adds a costly sort on large tables. Instead, find why duplicates appear, fix the join or aggregate the child table first, and add a uniqueness test on the key.
14
Your manager wants a visual that always shows the top 5 products for whatever region is selected in a slicer. How do you build it?
The simplest way is a visual-level Top N filter on Product, by the Total Sales measure, set to 5; it recalculates for every slicer selection. If the number must be user selectable, create a numeric what-if parameter and a ranking measure using RANKX over ALLSELECTED(Product), then filter the visual where rank is less than or equal to the parameter. I would also add an Others row if the business wants totals to still match.
15
What is bidirectional cross-filtering in Power BI and when is it risky?
Normally filters flow from the one side to the many side, for example from Product to Sales. Bidirectional filtering lets filters also flow back, so a Sales filter can limit Product. It helps in some many-to-many or bridge-table cases. The risks are ambiguous filter paths, unexpected results and slower performance. Prefer single direction and use CROSSFILTER inside a specific measure when you need both directions only there.
16
Tell me about a time a dashboard you built changed a business decision.
Situation: our operations team assumed late deliveries were evenly spread. Task: I built a delivery performance dashboard. Action: I added a breakdown by courier partner and pin code cluster with a weekly trend, and reviewed it with the operations manager. Result: it showed one partner caused 60 percent of delays in two zones. The company shifted those zones to another partner, and on-time delivery rose from 84 to 92 percent in two months.
17
Which KPIs would you include in an HR attrition dashboard?
Core KPIs: headcount, monthly and annualised attrition rate, voluntary versus involuntary split, and early attrition within the first 90 days or first year. Breakdowns by department, location, manager, tenure band, performance rating and salary band show where it concentrates. Add hiring metrics such as time to fill and offer acceptance rate, and exit reasons from exit interviews. Protect privacy by hiding very small groups.
Broadcasting lets NumPy perform operations on arrays of different shapes without copying data, by virtually stretching the smaller array. For example, subtracting a 1D array of column means from a 2D array centres every column in one line: X - X.mean(axis=0). Shapes are compatible when trailing dimensions are equal or one of them is 1. It is a key reason vectorised code is much faster than Python loops.
19
A stakeholder wants 20 more visuals added to an already crowded dashboard page. How do you respond?
I would ask which decisions each new visual supports and how often they will look at it. Usually a few are essential and the rest are nice to have. I would propose keeping the main page focused on core KPIs and moving detail into drill-through pages, tooltips or a separate analysis tab. Showing a quick mock-up of both versions helps them see that the cleaner page answers questions faster.
A bridge table resolves a many-to-many relationship between a fact and a dimension. For example, one bank account can have several holders and one customer can hold several accounts, so a bridge table stores account_key, customer_key and often an allocation weight. Without it, joining facts to customers duplicates amounts. Use weights to split balances, or report clearly that totals across customers will exceed the true total.
21
What is the difference between a calculated table in DAX and a table created in Power Query?
A Power Query table is built during data refresh using M, before data enters the model, and is compressed like any imported table. A DAX calculated table is computed after load from other model tables, using functions such as CALENDAR, SUMMARIZE or DISTINCT. Prefer Power Query for shaping and cleaning source data. Use calculated tables for model-dependent needs, such as a date table from existing data or a small what-if table.
22
What are pre-attentive attributes and how do you use them in charts?
Pre-attentive attributes are visual properties the brain notices in a fraction of a second before conscious thought, such as colour, size, position, length and bold weight. Use them to direct attention: make all bars grey and highlight only the declining region in a strong colour, or bold the one number that matters. Using too many highlights cancels the effect, so emphasise one or two things per chart.
23
Write a Tableau calculated field that keeps only sales from the last 30 days.
// Calculated field: Last 30 Days Flag
DATEDIFF('day', [Order Date], TODAY()) <= 29
AND [Order Date] <= TODAY()
// Put this field on Filters and keep True.
// For a sales measure instead of a filter:
// IF DATEDIFF('day', [Order Date], TODAY()) <= 29 THEN [Sales] END
Partition the table by date so the engine can prune partitions outside the filtered week, and cluster or sort by frequently filtered columns such as store or customer. Make sure the filter is on the partition column directly, not wrapped in a function like CAST or DATE_TRUNC that blocks pruning. Select only needed columns, because storage is columnar. For dashboards, also consider a pre-aggregated daily summary table.
26
What is a snowflake schema and when would you choose it over a star schema?
A snowflake schema normalises dimensions into several related tables, for example product linked to subcategory linked to category, instead of keeping one flat product dimension. It saves some storage and avoids repeating attribute values, but needs more joins and is harder for users and BI tools. Most analytics teams prefer a star schema; snowflaking makes sense when a hierarchy is huge, shared by many dimensions or maintained by a separate team.
A calculated column is computed row by row and stored in the model. A measure is computed at query time using the filter context, e.g. Total Sales = SUM(Sales[Amount]). Prefer measures for aggregations.
28
Leadership wants a single revenue number for next quarter, but your forecast is uncertain. How do you present it?
Show ranges, not just a single number: a forecast line with a shaded prediction interval, or a statement like revenue will likely be between 48 and 55 crore, most likely 51. Explain the main assumptions and what would change the outcome, perhaps with best, base and worst scenarios. Use plain words such as likely or unlikely consistently. Hiding uncertainty makes a forecast look precise but leads to bad decisions when reality differs.
Build PivotTables from a clean table, add PivotCharts and slicers, link KPIs with formulas, and refresh from Power Query so everything updates together.
Define tests as code that run after every load: not-null and unique checks on keys, accepted value lists, referential integrity, row count within an expected range compared with recent days, and freshness of the latest timestamp. Store results in a table and alert the team on failures before dashboards refresh. Start with checks on the few tables behind key KPIs, then expand. This catches issues before stakeholders do.
MATCH finds the position of a value in a range and INDEX returns the item at that position, giving a flexible lookup that works left or right and survives column insertions.
34
What is the difference between a live connection and an extract in Tableau?
A live connection queries the source database every time a user interacts, so data is always current but performance depends on the database. An extract is a compressed snapshot stored in Tableau's hyper format, usually much faster, refreshed on a schedule and usable offline. Use live for near real-time needs on a fast warehouse, and extracts for speed, slow sources or when you want to reduce load on production systems.
35
What is the difference between a clustered and a non-clustered index?
A correlated subquery refers to a column from the outer query, so it is logically evaluated once per outer row. Example: find employees earning above their department average with WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept_id = e1.dept_id). It is readable but can be slow on large tables. The same result is often faster with a window function such as AVG(salary) OVER (PARTITION BY dept_id).
37
Users say a Power BI report still shows yesterday morning's data, although a refresh is scheduled every few hours. What do you check?
Open the semantic model's refresh history in the Service to see whether refreshes failed, for example due to expired credentials, an offline gateway or a timeout. Check whether the report is connected to a different copy of the model, whether the source itself was updated, and whether incremental refresh only covers some partitions. Also confirm the schedule time zone. Finally, add a 'data as of' timestamp on the report.
38
A senior stakeholder insists the data supports their conclusion, but your analysis does not. How do you handle it?
Stay respectful and focus on evidence, not opinions. Ask what they are seeing that leads to their view, as they may know context you lack. Walk through your method transparently, show the specific numbers, and test their hypothesis together on a cut they suggest. If the data still disagrees, present both views with their assumptions to the decision maker. Never adjust the analysis to fit a preferred answer.
39
Tell me about a time a stakeholder rejected your first dashboard design.
Situation: I built a Tableau operations dashboard, and the warehouse head said it was too complex to use on the floor. Task: make it useful for them. Action: I watched how supervisors worked, learned they needed three numbers on a tablet every hour, and rebuilt it with large KPI tiles and one drill-down. Result: daily usage went from a handful of users to every shift supervisor within two weeks.
DAX functions like TOTALYTD, SAMEPERIODLASTYEAR and DATEADD compare periods; they need a proper continuous date table marked as a date table.
Tips to crack the ICICI Bank Data Analytics interview
Learn Indian banking basics such as loan types, NPAs, CASA ratio and RBI regulations for interviews.
Use iMobile Pay and think about features you would test or improve as a QA or analyst candidate.
Revise SQL and Excel with examples on customer segmentation, credit risk or campaign analysis.
Keep a one-minute story ready for every project on your CV: problem, your part, tools, result.
Practise the hard questions on this page aloud; ICICI Bank interviewers usually ask follow-ups on your first answer.
Frequently asked questions
How many rounds are there in the ICICI Bank Data Analytics interview?
Candidates usually go through 4 stages: Online assessment (aptitude + domain); Technical or functional interview; Managerial round; HR round. The exact rounds change with the role, team, location and your experience, so confirm the process with the recruiter.
Which topics should I prepare for the ICICI Bank Data Analytics interview?
This practice set leans on Power BI (7), SQL (5), Data Storytelling (5), Data Modelling & Warehousing (4). Revise those first, then practise explaining one or two of your own projects end to end, because most ICICI Bank interviewers ask you to walk through real work.
Are these the exact questions asked at ICICI Bank?
No. These are curated practice questions that match the skills and difficulty usually tested in banking & finance interviews like ICICI Bank's. They are not a leaked or verified list of past questions.
Where does ICICI Bank hire Data Analytics professionals in India?
ICICI Bank has large teams in Mumbai, Hyderabad, Chennai, Bengaluru. Openings and locations change often, so check the ICICI Bank careers page and filter by city and experience.