Wells Fargo · Primly Community

Wells Fargo data engineer interview, pipelines and SQL: what they actually test

analyst_ana · 5 replies

Just finished a data engineer loop at Wells Fargo. Role was on their enterprise data platform team, managing pipelines that feed regulatory reporting. Seven years in data engineering so I was going for a senior DE position. Here's the breakdown.

Pipeline / architecture round: This is where WF gets different from fintech or startup DE interviews. They care deeply about data lineage, auditability, and failure recovery. The question was: design a pipeline that ingests transaction data from multiple core banking systems, transforms it, and feeds a regulatory report. Constraints: data must be immutable once ingested (append-only), transformations must be auditable (you can trace a report number back to the raw transaction that produced it), and the pipeline must detect late-arriving data.

I drew out a medallion architecture (bronze/silver/gold lake layers) with Kafka for ingestion, Spark for transformation, Delta Lake for ACID compliance and time-travel. They pushed hard on the late-arriving data question. What's your SLA for the regulatory report? How do you handle a source system that delivers yesterday's transactions today?

SQL round: Heavy. Window functions, recursive CTEs (yes, recursive), and a query optimization question where they gave me a slow query and asked me to identify why it was slow and fix it. The query had a missing index on a join key and was using SELECT *. They wanted me to articulate the execution plan in words before proposing the fix.

Tools question: They asked specifically about my experience with Informatica and Ab Initio, which WF uses in legacy systems. I don't have deep Informatica experience. I was honest about that and pivoted to how I'd ramp on it. They seemed fine with it but I'd brush up on ETL tool concepts if you have time.

Behavioral: Two behavioral questions, both around handling ambiguity and working across teams. One specifically: tell me about a time you had to negotiate SLAs with an upstream team that wasn't meeting them. Banks have a lot of internal politics around data ownership. This question is testing whether you can work diplomatically without escalating everything.

Offer I got: $161k base, 10% bonus target, Charlotte in-office 3 days. Comparable to what I was making at my previous company but with better benefits package.

5 replies

mobile_mara

Recursive CTEs in an interview is actually kind of rare. Was this a take-home problem or live coding?

qa_quinn

Live coding on a shared screen, HackerRank environment. The recursive CTE was for a hierarchy problem: given an org table with employeeid and managerid, find all employees who report (directly or indirectly) to a given manager. Classic use case, but banks have a lot of org hierarchy queries so it makes sense they'd test it.

jordan_pm

Informatica and Ab Initio are very much legacy bank tools. I've seen them mentioned in WF job descriptions but not always tested. Probably depends on the team. If the role description mentions either specifically, worth at least knowing what they do conceptually.

consultant_cam

Late-arriving data handling is such a classic regulatory reporting problem. Did they ask about any specific solutions like watermarks in Spark Structured Streaming, or was it more architectural?

ops_omar

Both. I mentioned Spark Structured Streaming watermarks and they knew exactly what I was talking about. Then asked me to explain the trade-off between a 2-hour watermark and a 24-hour one for a daily regulatory file. It's a legitimate question because a longer watermark means more completeness but delays your processing. In regulatory reporting, timeliness and completeness are both requirements and they sometimes conflict.