Question 1

Have you worked on Data Warehousing projects?

Accepted Answer

**Architectural context**: A data warehouse is the semantic layer between raw data and business decisions. Design choices—star vs snowflake, SCD strategy, partitioning—directly impact query latency, storage cost, and maintenance burden. **Key responsibilities**: (1) **Schema design**: Star for BI simplicity, snowflake for normalized flexibility. SCD Type 2 for slowly changing dimensions (audit trail, point-in-time correctness)....

Question 2

Retrieve the most recent sale_timestamp for each product (Latest Transaction).

Accepted Answer

**SQL (full row)**: `SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_timestamp DESC) AS rn FROM sales) t WHERE rn = 1` — Returns the full record. **SQL (timestamp only)**: `SELECT product_id, MAX(sale_timestamp) FROM sales GROUP BY product_id` — Use when you only need the timestamp. **PySpark**: `df.withColumn('rn', F.row_number().over(Window.partitionBy('product_id').orderBy(F.desc('sale_timestamp')))).filter(F.col('rn') == 1).drop('rn')` — Full row....

Question 3

What is the difference between OLTP and OLAP?

Accepted Answer

**Why the distinction exists**: They serve different access patterns. OLTP = many small, random writes and point reads. OLAP = few, large sequential scans and aggregations. Optimizing for one degrades the other. **OLTP**: Row-oriented storage (fast single-row access). Normalized schema (3NF) to avoid update anomalies. Indexes for lookup (B-tree). ACID for consistency. High concurrency via locking/mvcc. Examples: PostgreSQL, MySQL, Oracle....

Question 4

Are you open to relocation or a hybrid work model?

Accepted Answer

**STAR**: Situation: Work models vary. Task: Find mutual fit. **Action**: I prefer hybrid—balances collaboration and focus. I have delivered in remote and colocated teams. For the right opportunity, I am open to relocation. I ask about expectations and flexibility. **Result**: I value outcomes over presence; I have delivered in distributed teams....

Question 5

Are you willing to relocate to Bangalore?

Accepted Answer

**STAR**: Situation: Role may require Bangalore. Task: Evaluate. **Action**: I am open based on role fit, team, and growth. I consider team presence, project impact, and trajectory. I have worked in distributed teams. I ask about timeline and relocation support. **Result**: For the right opportunity, I am flexible....

Question 6

Build an executive dashboard for reporting.

Accepted Answer

**Architecture**: Define KPIs with stakeholders. Source data—pipeline to curated layer. Design schema (star/snowflake). Build pipeline (idempotent, incremental). Visualize (Tableau, Looker). Refresh schedule (daily/hourly). **Key**: Actionable metrics; clean design; filters and drill-downs. **Scalability**: Pre-aggregate for large datasets; use materialized views....

Question 7

Command to Read JSON Data and Options

Accepted Answer

**Spark**: spark.read.json('path') or spark.read.format('json').load('path'). **Options**: schema (explicit StructType for perf), multiLine (pretty-printed), dateFormat, primitivesAsString. **Nested**: Provide schema or from_json. **Streaming**: readStream.schema(schema).json(path)....

Question 8

Compare batch processing and stream processing for financial data.

Accepted Answer

**Batch**: Reporting, reconciliation, EOD. Higher latency; simpler; better for large scans. **Stream**: Fraud, trading, alerts. Low latency; complex; exactly-once critical. **Trade-off**: Batch cheaper; stream real-time. **Best practice**: Use both—batch for EOD; stream for time-sensitive....

Question 9

Count occurrences of a specific word in a file

Accepted Answer

**Linux**: grep -c 'word' file.txt or grep -o 'word' file | wc -l. **Python**: open().read().split().count('word') or Counter. **Spark**: textFile.flatMap(split).filter(lambda w: w=='word').count(). **Best practice**: Case-insensitive with lower(); word boundaries with \b; streaming for large files.

Question 10

Data Security in BFSI - encryption, IAM, auditing

Accepted Answer

**BFSI requirements**: Encryption at rest and in transit. IAM with MFA. Audit logging. Data masking for non-prod. Compliance: PCI-DSS, SOC2. Tokenization for PII. **Architecture**: Zero trust; classify data; least privilege....

Hard General Interview Questions

Companies asking these questions

All 49 Questions

More Interview Prep Guides

Get All Answers in PDF Format

Hard General Interview Questions

Companies asking these questions

All 49 Questions

More Interview Prep Guides

Get All Answers in PDF Format