3. Architecture & Database

Yes, as BLOB (Binary Large Object) data, but it is generally recommended to store the files in a file system or cloud storage (like AWS S3) and store only the reference path/URL in the database.

SELECT * FROM table_name; (Retrieve data), INSERT INTO table_name VALUES (...); (Add data), UPDATE table_name SET column=value; (Modify data), DELETE FROM table_name WHERE condition; (Remove data).

A Function App is event-driven — it only runs when triggered by specific events (HTTP requests, timers, queue messages, etc.) and shuts down when not in use. A Web App stays running continuously. Additionally, a Web App can expose a complete application to the user (both front end and back end), while a Web Service (or Function App) typically only exposes back-end logic. A Web App can also be configured to expose only the back end, but its key distinction is that it remains always on.

Relational databases are characterized by the ACID properties (Atomicity, Consistency, Isolation, Durability), which guarantee reliable transaction processing. Non-relational databases, on the other hand, are characterized by the CAP theorem (Consistency, Availability, Partition Tolerance), which states that a distributed system can only guarantee two of these three properties simultaneously.

ACID stands for Atomicity, Consistency, Isolation, and Durability, and these are the key properties that relational databases guarantee for transactions. For non-relational or distributed databases, the relevant concept is the CAP theorem. The CAP theorem states that in a distributed database system, you cannot simultaneously guarantee all three of the following: Consistency (C) – every read receives the most recent write, Availability (A) – every request receives a response, and Partition Tolerance (P) – the system continues to operate despite network partitions. A distributed system must choose to prioritize two of these three guarantees at the expense of the third.

Apply clean architecture principles, specifically hexagonal architecture with ports and adapters. The goal is to keep the domain layer decoupled from both the framework and the infrastructure. For external providers such as payment systems, define ports (interfaces) in the domain layer and implement adapters in the infrastructure layer. For inter-service communication, use event-driven patterns: publish events and subscribe to them. This approach ensures the core business logic remains independent of external concerns, making it easier to swap providers or change infrastructure without affecting the domain.

The recommended approach is to implement the Strategy pattern for the notification channels. Each channel (SMS, Email, Push) is implemented as a separate strategy class. For the take-home challenge, the actual sending can be simulated with a log message indicating the notification was sent through the specified channel along with the relevant data. The key objective is to make the code extensible so that new channels can be added in the future without modifying the existing channel implementations, following the Open/Closed Principle.

Node.js inherently handles concurrency through the event loop. For high traffic, I would first configure a load balancer to scale horizontally and handle large user loads. Then implement rate limiting to prevent a single IP from making excessive requests. Use caching with Redis to store API responses in memory. For payment processing, evaluate whether to build the bank communication layer internally or use a microservice that complies with PCI standards by tokenizing cards. If the market extends to Europe, ensure compliance with SCA (Strong Customer Authentication) regulations, and if the bank requires 3D Secure, display that prompt to the user. Use MongoDB to store transaction references and analyze whether to persist customer data for subscriptions or automatic card charging. Implement token validation for security. Follow the Repository pattern to decouple business logic from database connections or external API consumption. Adopt an event-driven architecture to react to events such as verifying whether a user exists in real life. Use idempotency to prevent duplicate payments or duplicate user creation. Start with a monolith, and only consider extracting a microservice for non-core functionality to avoid violating the YAGNI principle.

One key strategy is implementing caching for data that does not change frequently. By caching relatively static data, you reduce unnecessary repeated queries or computations, which can significantly improve response times. Other common strategies include optimizing database queries, using indexes, implementing lazy loading, applying pagination, using CDNs for static assets, and profiling the application to identify bottlenecks.

One approach is to use WebSockets to maintain a persistent, synchronous connection between clients, with messages ordered by their publication timestamp. Alternatively, a message queue such as Apache Kafka can be used to ensure ordered and reliable message delivery, maintaining synchronism between producers and consumers. Both approaches handle the core requirement of keeping messages consistent and in order across participants.

Key considerations for this design include: (1) Queue-based purchase management — place incoming purchase requests in a queue to establish a fair ordering and ensure only one request processes a given seat at a time, preventing overselling. (2) Load balancing — distribute the high volume of simultaneous incoming requests across multiple server instances to avoid a single point of failure and reduce latency. (3) Scalability — architect the system to scale horizontally (adding more instances) to handle sudden traffic spikes that occur when tickets go on sale. Concurrency controls such as optimistic or pessimistic locking on the ticket inventory records are also critical to guarantee consistency.

When designing a system to bulk-insert 1 million records from a CSV into a SQL database, consider the following approach: 1. **Avoid ORM**: At this scale, ORM layers introduce unnecessary overhead. Use direct database access instead. 2. **Chunked / buffered reading**: Parse the CSV in chunks rather than loading the entire file into memory at once. 3. **Binary file reads**: Prefer binary I/O over text-wrapper libraries for better file-read performance. 4. **Native bulk insert**: Leverage the database engine's built-in bulk insert capability (e.g., Oracle bulk insert). This approach can load 1 million records in roughly 50 seconds. 5. **Data validation**: Before inserting, validate every row — check data types, required fields, and value constraints — to prevent malformed data from reaching the database and to minimize transaction rollbacks. 6. **Asynchronous processing**: Because a payload of this size cannot be handled in a single synchronous HTTP request, process the file asynchronously and immediately return a response to the client indicating that the import is in progress. 7. **Transaction management**: Wrap inserts in transactions and collect the rows that fail (e.g., due to constraint violations) so they can be returned to the caller as an error report rather than aborting the entire operation. 8. **Batch splitting**: Instead of one enormous request, split the workload into smaller batches (e.g., 100 requests of 10,000 records each) to improve resilience and manageability.

The CAP theorem states that a distributed database system cannot simultaneously guarantee all three of the following properties: Consistency (C) — every read receives the most recent write or an error; Availability (A) — every request receives a non-error response, though not necessarily the most recent data; and Partition Tolerance (P) — the system continues to operate even when network partitions occur between nodes. At most two of these three properties can be fully guaranteed at the same time. This contrasts with relational databases, which are governed by ACID properties: Atomicity, Consistency, Isolation, and Durability. ACID focuses on transaction integrity within a single database, while CAP addresses trade-offs in distributed systems.

Two main approaches can be considered. First, use WebSockets to maintain a persistent, synchronous connection between participants and order messages by their publication timestamp. Second, use a message queue system such as Apache Kafka to maintain message ordering and ensure synchronism across consumers. The best choice depends on the scale and specific requirements of the system.

For NoSQL databases in general, you should study the CAP theorem along with common questions specific to that category of database. For MongoDB in particular, focus on: CRUD operations (MongoDB Academy offers a concise course), the aggregation framework, views, document maintenance strategies, correct indexing techniques, and query plan optimization (the approach is analogous to relational databases). Sharding is another important topic that frequently appears as an interview question. For SQL and relational databases, the equivalent foundational concept to master is the ACID principles.

Technical architecture assessments for Data Engineering roles typically fall into two categories. The first is a conceptual type, where you are expected to explain how you would approach the data process and which tools or technologies you would use to build the solution. The second is a more hands-on type, where you are required to produce actual work such as writing code, designing data pipelines, handling configuration, implementing Spark jobs, building data quality validators, applying normalization techniques, and similar tasks. The specific deliverables depend on the tools and stack the company uses. It is advisable to study both the conceptual side (architectural patterns, tool selection rationale) and the practical side (coding in frameworks like Spark, data quality checks, pipeline design).

Relational databases are characterized by the ACID properties: Atomicity (a transaction is treated as a single unit — it either completes fully or not at all), Consistency (a transaction brings the database from one valid state to another valid state), Isolation (concurrent transactions execute as if they were sequential, preventing interference), and Durability (once a transaction is committed, it persists even in the event of a system failure). Non-relational (NoSQL) databases are described by the CAP theorem, which states that a distributed system can only guarantee two of the following three properties simultaneously: Consistency (every read receives the most recent write or an error), Availability (every request receives a response, though not necessarily the most recent data), and Partition Tolerance (the system continues operating even if some network messages are lost or delayed between nodes).

The CAP theorem states that a distributed database system cannot simultaneously guarantee all three of the following properties: Consistency (C) — every read receives the most recent write or an error; Availability (A) — every request receives a response (though not necessarily the most recent data); and Partition Tolerance (P) — the system continues to operate despite network partitions between nodes. Unlike relational databases, which aim to satisfy ACID properties, distributed and NoSQL databases are designed around the trade-offs defined by the CAP theorem, meaning a system can only fully guarantee two of the three properties at any given time.

The core AWS services can be summarized as follows: - EC2: Rent virtual servers in minutes (managed compute instances). - S3: Object storage — store any type of file at any time. - RDS: Managed relational database service — no server patching headaches. - Lambda: Serverless compute — run code without provisioning or managing servers. - API Gateway: Expose backend APIs securely to external consumers. - CloudWatch: Monitoring and observability — track metrics, logs, and alerts to see what is breaking. - VPC: Virtual Private Cloud — your isolated private network within AWS. - IAM: Identity and Access Management — define who can do what across your AWS resources. - CloudFront: Content Delivery Network — distribute content globally for low-latency access. - DynamoDB: Managed NoSQL database designed to scale horizontally without operational overhead. - SQS: Simple Queue Service — reliable, decoupled messaging between application components. - SNS: Simple Notification Service — broadcast alerts and notifications instantly to multiple subscribers.