OA - Bank System

[ OK ] a95497fa-a6b2-445a-9d3b-d1bc88be43b3 — full content available

[ INFO ] category: Coding difficulty: unknown freq: first seen: 2026-03-13

[UNKNOWN][CODING]High Frequency

$ cat problem.md

In a Snowflake interview, a "Bank System" problem statement typically focuses on data warehousing architecture, transactional consistency, and secure data management rather than just general software engineering. Reddit +1 2 4

Typical Problem Statement: "Designing a Scalable Banking Analytics Platform"

You are tasked with designing a system that ingests daily transaction data from multiple branch legacy systems (OLTP) into Snowflake (OLAP) to provide real-time fraud detection and executive reporting. Reddit +1 2 8

Key Requirements & Constraints:

Data Ingestion: Handle massive volumes of structured (transaction logs) and semi-structured (JSON clickstream from mobile apps) data.
Accuracy & Consistency: Ensure "Single Version of Truth" for account balances across all reporting layers.
Security: Mask sensitive Customer Identifiable Information (PII) like SSNs and account numbers based on user roles (e.g., tellers vs. analysts).
Performance: Reports must load in seconds, even during peak hours when concurrent ETL jobs are running.
Auditing: Maintain a 7-year history of all changes for regulatory compliance (e.g., GDPR, SOX). Reddit +8

Core Areas to Address in Your Design

Feature | Snowflake-Specific Solution to Discuss --- | --- Ingestion | Use Snowpipe for near real-time ingestion of files from S3/Azure Blob. Concurrency | Separate Virtual Warehouses (e.g., a "Load_WH" for ingestion and a "Reporting_WH" for analysts) so compute resources don't compete. Historical Data | Use Time Travel (up to 90 days) and Fail-safe (7 days) for data recovery and point-in-time auditing. Data Privacy | Implement Dynamic Data Masking and Row Access Policies to control visibility of sensitive banking data. Cost Control | Set up Resource Monitors to automatically alert or suspend compute if a budget is exceeded.

Potential Follow-up Scenarios

Account Types: How would you model different account behaviors (e.g., "deposit only" vs. "interest-only" accounts) using a Snowflake Schema vs. Star Schema?
Scalability: If a sudden global marketing campaign doubles the user base, how do you handle the sudden spike in query volume? (Answer: Discuss Multi-cluster Warehouses)
Data Sharing: How would you securely share "Anonymized Transaction Trends" with a third-party partner without copying the actual data? (Answer: Direct Data Sharing) VisualCV +4

Would you like to walk through a specific SQL implementation for one of these scenarios, such as setting up a fraud-detection stream?

user@intervues:~/coinbase$