← Plaid Interview Insights

Plaid·Software Engineer·Onsite - System Design / Architecture·Senior

SeniorPrefer not to say
May 2026

Summary

Plaid data engineering interview built around a take-home assignment. You show up with SQL already written, then spend the session defending every decision while the interviewer throws increasingly annoying edge cases at you. Less about writing code on the spot, more about whether you actually understand why you made the choices you did.

Questions Asked (9)

Q1

Walk me through how your pipeline moves data from raw source through to the final analytical layer. Why did you structure it that way?

Data ModelingSystem DesignTechnical Trade-offs
Author's notes

This was the opening and I thought I had it locked.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start with a high-level overview of your pipeline stages, then dive into the rationale behind each design choice, emphasizing trade-offs and alignment with business needs. Use a concrete example from your experience to illustrate the flow and decisions.

Pro tip: Quantify the impact of your design choices (e.g., reduced latency by X%, improved data quality) to demonstrate tangible results and maturity.

1. Outline the pipeline stages

Briefly describe each stage from raw source to analytical layer, mentioning key technologies and data transformations.

2. Explain the rationale for each stage

Discuss why you chose specific tools, patterns, or architectures (e.g., batch vs. streaming, normalization vs. denormalization) at each stage.

3. Highlight trade-offs and alternatives

Acknowledge other possible approaches and explain why your chosen structure was optimal given constraints like scalability, cost, or latency.

4. Connect to business outcomes

Tie the pipeline design to business goals such as data freshness, reliability, or enabling specific analytics use cases.

5. Summarize with lessons learned

Conclude with key takeaways or improvements you would make, showing reflection and continuous learning.

Key Points to Mention

  • Data ingestion methods (e.g., batch, streaming, CDC) and why they were chosen
  • Data storage and processing technologies (e.g., data lake, warehouse, ETL vs. ELT)
  • Data modeling techniques (e.g., star schema, data vault) and their impact on analytics
  • Orchestration and monitoring tools (e.g., Airflow, dbt) for reliability
  • Scalability and performance considerations (e.g., partitioning, indexing)
  • Data quality and governance practices (e.g., validation, lineage)

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

How would you handle incremental loads here? Walk me through the tradeoffs between a full reload, CDC, and a high-watermark approach on an updated_at column.

Data ModelingTechnical Trade-offsSystem Design
Author's notes

Went with high-watermark first and explained it, then they pushed on late-arriving data and I fumbled a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data volume, update frequency, and latency requirements, then compare the three approaches on correctness, cost, and operational complexity. Recommend a hybrid strategy (e.g., high-watermark for most tables, CDC for high-churn or delete-heavy tables) and justify it with concrete tradeoffs.

Pro tip: Mention that high-watermark on updated_at can miss rows if updates aren't monotonic (e.g., backdated updates, clock skew) and that CDC requires handling schema evolution and exactly-once semantics. Showing awareness of these edge cases signals production experience.

1. Clarify requirements and constraints

Ask about data volume, update frequency, acceptable latency, source system capabilities (e.g., binlog access), and whether deletes must be captured.

2. Explain each approach briefly

Define full reload (truncate and reload), CDC (capture row-level changes from logs), and high-watermark (incremental based on updated_at > last max).

3. Compare tradeoffs across dimensions

Evaluate correctness (handling deletes, late-arriving data), performance (load on source, latency), cost (compute, storage, network), and operational complexity (setup, monitoring, failure recovery).

4. Recommend a solution with justification

Propose a hybrid or specific approach based on the requirements, and explain why it balances the tradeoffs best for the given scenario.

5. Address edge cases and monitoring

Discuss how to handle schema changes, data quality checks, idempotency, and how to monitor for missed or duplicate rows.

Key Points to Mention

  • Full reload: simple but expensive; causes downtime and high load; not scalable for large datasets.
  • CDC: near real-time, captures deletes and all changes, but complex to set up and maintain; requires log access and handling of schema evolution.
  • High-watermark: efficient and simple, but misses deletes and updates that don't change updated_at monotonically; vulnerable to clock skew and backdated updates.
  • Hybrid approach: use CDC for critical tables and high-watermark for append-only or low-churn tables.
  • Idempotency and exactly-once processing: ensure deduplication and transactional writes to avoid duplicates.
  • Monitoring and alerting: track lag, row counts, and data freshness to detect issues early.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q3

Your model assumes events arrive in order. What breaks if they don't, and how does your design handle that?

Data ModelingTechnical Trade-offs
Author's notes

Rough one.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Acknowledge that out-of-order events are common in distributed systems and can break assumptions like monotonic timestamps or sequential state updates. Explain the specific failure modes (e.g., incorrect aggregations, race conditions) and then describe your design's mitigation strategies such as event-time processing, watermarks, and idempotent operations. Emphasize trade-offs and how you validate correctness.

Pro tip: Mention that you explicitly test for out-of-order events by injecting delays or reordering messages in integration tests, and that you monitor for late events in production to catch issues early.

1. Identify Assumptions

State the specific assumptions your model makes about event ordering, such as monotonic timestamps or sequential delivery, and why they matter.

2. Describe Failure Modes

Explain what breaks when events arrive out of order: e.g., incorrect state, lost updates, duplicate processing, or invalid aggregations.

3. Outline Mitigation Strategies

Detail how your design handles out-of-order events, such as using event-time processing with watermarks, buffering, reordering, or idempotent operations.

4. Discuss Trade-offs

Acknowledge the trade-offs of your approach, like increased latency, complexity, or resource usage, and why they are acceptable.

5. Validate and Monitor

Explain how you test for out-of-order scenarios and monitor for late events in production to ensure correctness.

Key Points to Mention

  • Event-time vs. processing-time semantics
  • Watermarks and allowed lateness
  • Idempotent operations and deduplication
  • State management and windowing
  • Backpressure and buffering
  • Monitoring and alerting for late events

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q4

How do you handle duplicate rows coming from the source system? Where in the pipeline do you deal with them and why there specifically?

Data ModelingSystem Design
Author's notes

Pretty comfortable with this one.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the definition of a duplicate (exact row vs. business key) and the pipeline stage where deduplication is most appropriate. Then walk through a layered strategy: detect duplicates early, deduplicate at the right layer (e.g., staging or silver), and ensure idempotency and data quality checks. Emphasize trade-offs between deduplicating at ingestion vs. transformation vs. serving, and justify your choice based on cost, latency, and correctness.

Pro tip: Mention that deduplication should be idempotent and ideally handled as close to the source as possible to avoid propagating errors downstream, but sometimes late deduplication is needed for late-arriving data. Also, highlight the importance of a deterministic tie-breaker (e.g., latest timestamp) to avoid non-deterministic results.

1. Clarify the duplicate definition and source

Ask whether duplicates are exact row copies or share a business key, and understand the source system's behavior (e.g., retries, CDC, batch loads). This determines the deduplication key and method.

2. Choose the deduplication layer

Decide where to deduplicate: at ingestion (staging), during transformation (silver), or at serving (gold). Consider factors like data volume, latency requirements, and whether downstream consumers need raw data.

3. Implement deduplication logic

Use window functions (ROW_NUMBER) or DISTINCT on the chosen key, with a deterministic ordering (e.g., latest timestamp). For streaming, use stateful deduplication with watermarks or idempotent writes.

4. Ensure idempotency and handle late data

Make the pipeline idempotent so re-runs don't create duplicates. For late-arriving data, consider a late deduplication step or periodic compaction.

5. Monitor and validate

Add data quality checks to detect duplicates and alert on anomalies. Track metrics like duplicate rate and deduplication latency.

Key Points to Mention

  • Definition of duplicate: exact row vs. business key (e.g., transaction_id)
  • Deduplication techniques: DISTINCT, ROW_NUMBER() OVER (PARTITION BY key ORDER BY timestamp DESC), GROUP BY
  • Trade-offs: early deduplication reduces downstream cost but may lose raw data; late deduplication handles late-arriving data but increases complexity
  • Idempotency: ensuring re-runs don't create duplicates (e.g., MERGE, upsert, or overwrite partitions)
  • Streaming vs. batch: different approaches (e.g., Flink state, Kafka compaction, Spark Structured Streaming watermarks)
  • Data quality monitoring: duplicate rate metrics, alerts, and automated tests

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q5

What happens to your model when the source schema changes? Say a column gets added, or worse, a column gets renamed.

Data ModelingTechnical Trade-offsAdaptability & Ambiguity
Author's notes

Schema evolution questions always feel abstract until they don't.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Acknowledge that schema changes are inevitable and can break models if not handled proactively. Describe a layered defense strategy: detection, validation, and graceful degradation, with concrete examples of how you'd handle added vs. renamed columns. Emphasize the trade-offs between strict schema enforcement and flexibility, and how you'd balance them for reliability.

Pro tip: Mention that you'd treat schema changes as a data contract violation and implement automated alerts with clear ownership, so issues are caught before they impact downstream consumers. Also, highlight the importance of versioning your model's expectations to allow safe rollouts.

1. Detect and Alert

Implement schema monitoring that compares incoming data against expected schema and triggers alerts on any deviation. This ensures you're aware of changes immediately, not after failures.

2. Assess Impact

Determine whether the change is additive (new column) or breaking (renamed/removed column). For additive changes, the model may ignore the new column; for breaking changes, the model will likely fail or produce incorrect results.

3. Handle Gracefully

For additive changes, ensure the model can tolerate extra columns (e.g., by selecting only needed columns). For renamed columns, use schema evolution techniques like aliasing or mapping old to new names, or fail fast with a clear error.

4. Validate and Test

Run validation checks on the new schema and test the model with sample data to ensure it still behaves correctly. Automate this in CI/CD pipelines to catch issues early.

5. Communicate and Iterate

Coordinate with upstream teams to understand the change, update documentation, and adjust the model as needed. Treat schema changes as part of the data contract and establish a process for managing them.

Key Points to Mention

  • Schema evolution strategies: backward/forward compatibility, versioning, and schema registry
  • Data contracts and SLAs with upstream data producers to manage changes
  • Defensive programming: selecting only required columns, using explicit column mappings, and avoiding SELECT *
  • Monitoring and alerting for schema drift, with automated tests in CI/CD
  • Trade-offs between strict schema enforcement (reliability) and flexibility (adaptability)
  • Graceful degradation: fallback mechanisms or default values when columns are missing

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q6

Your pipeline has a partial-day partition at the end of a run. How do you make sure downstream consumers don't query incomplete data?

Data ModelingSystem Design
Author's notes

Talked about partition readiness flags and not exposing a partition until it's complete.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by acknowledging the problem: partial-day partitions can lead to incomplete data being consumed. Then describe a multi-layered strategy: prevent incomplete data from being published, mark partitions as complete only when ready, and enforce checks on the consumer side. Emphasize communication and monitoring to ensure downstream awareness.

Pro tip: Use a 'completeness marker' (e.g., a _SUCCESS file or a metadata flag) that downstream systems check before querying. This is a common pattern in data engineering and shows you understand both producer and consumer responsibilities.

1. Prevent premature publication

Ensure the pipeline only writes to a temporary location and atomically moves data to the final partition once the run is complete. This avoids partial data being visible.

2. Mark partition completeness

Write a sentinel file (e.g., _SUCCESS) or update a metadata table to indicate the partition is complete and ready for consumption.

3. Enforce consumer-side checks

Downstream consumers should validate the completeness marker before querying. This can be done via a view, a query filter, or an orchestration dependency.

4. Monitor and alert

Set up monitoring to detect when partitions are incomplete or when consumers attempt to access incomplete data, and alert the responsible teams.

5. Communicate and document

Clearly document the partition lifecycle and completeness guarantees, and communicate any changes or delays to downstream consumers.

Key Points to Mention

  • Atomic writes or staging areas to avoid partial data visibility
  • Sentinel files (e.g., _SUCCESS) or metadata flags to indicate partition readiness
  • Consumer-side validation of completeness markers
  • Orchestration dependencies (e.g., Airflow sensors) to wait for partition completion
  • Monitoring and alerting for incomplete partitions
  • Documentation and communication of data SLAs and partition lifecycle

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q7

How do you handle deletes in the source system? Does your model support hard deletes, and if not, why not?

Data ModelingTechnical Trade-offs
Author's notes

Soft deletes were baked into my model so this was fine.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by explaining that deletes are handled via soft deletes (tombstones) in the source system, which are propagated to the model as status changes or deletion flags. Then justify why hard deletes are avoided: they break auditability, complicate recovery, and can cause data inconsistencies in distributed systems. Finally, discuss how you ensure correctness and performance, such as periodic compaction or archival.

Pro tip: Mention that soft deletes also help with idempotency and replayability in event-driven pipelines, which is crucial for financial data integrity at Plaid. Also, note that you can implement hard deletes later via a separate purge process if compliance requires it.

1. Define delete semantics

Clarify what a delete means in the source system: is it a physical removal or a logical invalidation? Explain that you capture deletes as events or flags.

2. Propagate deletes to the model

Describe how deletes are represented in your model, e.g., a deleted_at timestamp, is_deleted boolean, or a status column. Mention that you use change data capture (CDC) or event streams to propagate deletes.

3. Justify soft deletes over hard deletes

Explain the trade-offs: soft deletes preserve history, enable auditing, support recovery, and avoid breaking foreign key relationships. Hard deletes can cause data loss and are often irreversible.

4. Address performance and storage

Acknowledge that soft deletes can bloat tables and slow queries. Describe mitigation strategies like partitioning, indexing on deletion flags, and periodic archival or compaction.

5. Handle edge cases and compliance

Discuss how you handle GDPR/CCPA right-to-be-forgotten requests, possibly via a separate hard-delete process, and how you ensure consistency across replicas or downstream systems.

Key Points to Mention

  • Soft deletes (tombstones) vs. hard deletes
  • Change data capture (CDC) for delete propagation
  • Auditability and data lineage
  • Idempotency and replayability in event-driven systems
  • Performance implications: table bloat, query slowdown, need for indexing/partitioning
  • Compliance (GDPR, CCPA) and right-to-be-forgotten

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q8

You have NULLs appearing in a join key. What do you do, and what does it mean for the rows that can't join?

Data ModelingRoot Cause Analysis
Author's notes

Classic but they framed it specifically around my model's fact table, so it felt less generic.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, clarify that NULLs in a join key never match in standard SQL, so those rows are silently dropped from inner joins and appear as unmatched in outer joins. Then discuss how to diagnose the root cause (e.g., missing data, ETL issues, optional relationships) and propose solutions like COALESCE, sentinel values, or fixing the data pipeline, while considering the business impact of unmatched rows.

Pro tip: Mention that NULL handling varies by database (e.g., Oracle treats empty strings as NULL, while others don't) and that using NULL-safe joins (e.g., `IS NOT DISTINCT FROM`) can be a quick fix but may mask deeper data quality issues.

1. Acknowledge the behavior

State that NULLs in join keys do not match each other in standard SQL, so rows with NULL keys are excluded from inner joins and appear as unmatched in outer joins.

2. Diagnose the root cause

Investigate why NULLs exist: missing source data, ETL bugs, optional foreign keys, or data entry errors. Determine if NULLs are expected or a symptom of a problem.

3. Assess the impact

Quantify how many rows are affected and what business logic depends on those joins. Unmatched rows might represent lost revenue, incomplete reports, or broken features.

4. Choose a remediation strategy

Decide whether to fix the data (e.g., backfill, correct ETL), handle NULLs in the query (e.g., COALESCE to a sentinel, use NULL-safe join), or change the data model to avoid NULL keys.

5. Validate and monitor

After implementing a fix, verify that joins produce expected results and set up monitoring to catch future NULL key occurrences early.

Key Points to Mention

  • NULLs are not equal to each other in SQL, so they never match in joins.
  • Inner joins drop rows with NULL keys; outer joins keep them but with NULLs on the other side.
  • Root causes: missing data, ETL issues, optional relationships, or data quality problems.
  • Solutions: COALESCE to a sentinel value, NULL-safe joins (e.g., IS NOT DISTINCT FROM), or fixing the data pipeline.
  • Impact: unmatched rows can lead to incorrect analytics, lost revenue, or broken application logic.
  • Prevention: enforce NOT NULL constraints, use default values, or redesign the schema to avoid NULLs in join keys.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q9

Timestamps in your source data are inconsistent across time zones. How does that affect your model and where do you normalize it?

Data ModelingTechnical Trade-offs
Author's notes

Honestly the one I was least prepared for in terms of specifics.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Acknowledge that inconsistent time zones can cause incorrect temporal ordering, misaligned joins, and skewed feature distributions, which degrade model performance. Then explain that normalization should happen at the earliest possible stage—ideally during data ingestion—by converting all timestamps to a single canonical time zone (e.g., UTC) and storing them with explicit time zone metadata. Finally, discuss how to handle edge cases like daylight saving time and ensure downstream consumers are aware of the normalization.

Pro tip: Mention that you would also validate the normalization by checking for anomalies like timestamps in the future or duplicate events, and consider using a library like pytz or zoneinfo to handle time zone conversions robustly. This shows attention to data quality and production readiness.

1. Identify the impact

Explain how inconsistent time zones can lead to incorrect event ordering, misaligned joins, and biased features, ultimately affecting model accuracy and reliability.

2. Choose a canonical time zone

Recommend using UTC as the single source of truth for all timestamps, as it avoids daylight saving complexities and is widely supported.

3. Normalize at ingestion

Describe converting all timestamps to UTC as early as possible—ideally during data ingestion or ETL—to prevent inconsistencies from propagating downstream.

4. Preserve original information

Suggest storing the original time zone offset or the raw timestamp alongside the normalized UTC timestamp for auditability and debugging.

5. Validate and monitor

Outline checks for anomalies (e.g., future timestamps, duplicates) and monitoring to ensure normalization works correctly in production.

Key Points to Mention

  • Impact on temporal ordering and joins
  • UTC as the canonical time zone
  • Normalization at ingestion/ETL
  • Handling daylight saving time
  • Storing original time zone metadata
  • Validation and monitoring for data quality

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.