HSBC Interview Questions

Ingestion : Kafka topics partitioned by account region for parallel consumption. Processing : Spark Structured Streaming (or Flink) consuming from Kafka...

• How would you design a real-time data pipeline for millions of financial transactions?

  Ingestion : Kafka topics partitioned by account region for parallel consumption. Processing : Spark Structured Streaming (or Flink) consuming from Kafka — apply fraud rules, enrichment joins with customer dimension, currency normalization. Storage : Write to Delta Lake (Bronze → Silver → Gold medallion). Bronze = raw events, Silver = cleaned/enriched, Gold = aggregated KPIs. Serving : Gold tables exposed to Synapse/BigQuery for reporting; real time fraud alerts via Kafka back to downstream servi

• How do you ensure data consistency across distributed systems?

  Use ACID transactions — Delta Lake or Iceberg on the data lake side; transactional DBs for operational stores Idempotent writes : design pipelines so re runs don't produce duplicates (MERGE over INSERT) Eventual consistency patterns : two phase commit or saga pattern for cross service transactions Reconciliation jobs : periodic count/checksum comparisons between source and target Schema registry : enforce contract between producers and consumers (Confluent Schema Registry)

• You find duplicate records in a production dataset — how do you handle and prevent this?

  Handle : Prevent : Add UNIQUE constraint at the database level Use Delta Lake MERGE (upsert) instead of blind appends Add deduplication step at ingestion with idempotency keys Add data quality assertion (e.g., Great Expectations) to alert on duplicate rates

• A downstream system is failing due to schema changes — how do you manage schema evolution?

  Use a schema registry (Confluent) so producers can't break consumers without a version bump In Delta Lake, enable mergeSchema for additive changes (new columns) For breaking changes (renamed/dropped columns), version the table and notify consumers ahead of time In ADF, enable schema drift to pass unknown columns through without failing Maintain a data contract per dataset — consumers declare which fields they depend on

• How would you optimize a slow PySpark job in Databricks?

  1. Open Spark UI → identify the slow stage (shuffle size, skewed tasks) 2. Broadcast small tables : join(broadcast(dim), "key") 3. Enable AQE : spark.sql.adaptive.enabled=true 4. Fix skew : salt the join key or use AQE skew join handling 5. Tune partitions : spark.sql.shuffle.partitions (default 200 — adjust to data size) 6. Use Parquet/Delta instead of CSV (predicate pushdown + column pruning) 7. Cache DataFrames reused across multiple actions

• How do you handle late-arriving data in a streaming pipeline?

  Use watermarking in Spark Structured Streaming: Data arriving more than 2 hours late is discarded. For critical financial data, write late records to a reprocessing queue instead of discarding — a separate batch job reconciles them against already committed aggregates using Delta MERGE.

• Difference between Delta Lake and traditional Data Lake?

  Feature Traditional Data Lake Delta Lake ACID transactions No Yes Schema enforcement No (schema on read) Yes Time travel No Yes (version history) MERGE/UPDATE/DELETE Not supported Supported Small file problem Accumulates OPTIMIZE compacts files Concurrent writes Race conditions Serializable isolation Delta Lake adds a transaction log ( delta log/ ) on top of Parquet files stored in object storage.

• How do you secure sensitive banking data in Azure?

  Encryption : ADLS Gen2 encrypts at rest (AES 256); TLS 1.2 in transit Access control : Azure RBAC + ADLS ACLs (POSIX style) Key Vault : all secrets, connection strings, and encryption keys stored centrally Managed Identity : service to service auth without credentials PII masking : mask/tokenize columns at the Silver layer before exposure Audit logs : all data access logged via Azure Monitor / Purview Private endpoints : disable public access; use VNet integration