Snap data engineer interview, pipelines and SQL, what the loop

Snap data engineer interview, pipelines and SQL, what the loop looks like

de_derek (Primly starter) · 6 replies

Just wrapped a Snap DE loop. Posting because most DE interview content out there is either ancient or generic. Snap's data infra is genuinely interesting and the interviews reflect that.

For context: I'm a senior DE, 7 years, mostly Spark + Kafka + dbt stacks. Interviewed for a senior role on the data platform team.

Phone screen (45 min): Mixed technical and behavioral. They walked through my resume, asked about the largest pipeline I'd managed (I described an event processing pipeline handling ~2TB/day), and then did two SQL questions live. One was a window function problem, the other was a recursive CTE for a hierarchy traversal. Neither was insane, but the recursive one caught me a bit off guard. Know your CTEs.

Onsite (4 rounds): SQL deep dive (60 min). More complex than the screen. Got a messy multi-join scenario with a tricky deduplication requirement. They specifically asked about performance, not just correctness. I talked through indexing strategy and why I'd avoid certain subquery patterns. Data modeling and architecture (60 min). This felt like a softer system design round. They gave me a scenario: Snap has a new ad product that generates events at high volume. Design the data pipeline from raw ingestion to reporting. I walked through Kafka ingestion, Spark streaming vs. batch tradeoffs, schema evolution, and how you'd build the downstream aggregation tables for the analytics team. Lots of good discussion on schema-on-read vs. schema-on-write. Coding (45 min). Python. Data processing problem, not LC algorithmic. More like: here's a dataset, clean it, aggregate it, return the result efficiently. Felt closer to real work than typical LC. Behavioral (45 min). Cross-functional stuff. How do you work with data scientists and analysts who have different quality bars than you? How do you handle a data incident that affects a downstream dashboard? Standard but worth prepping.

They use Spark internally and it came up naturally in round 2. You don't need to be a Spark expert, but knowing the fundamentals helps.

Overall: harder than some DE loops I've done, more interesting than most. The SQL rounds are real, not decorative.

6 replies

ae_andre (Primly starter)

recursive CTEs are so underrated on DE interview prep lists. i see people study window functions for weeks and then blank on a simple hierarchy traversal. adding this to my list.

analyst_ana (Primly starter)

the schema-on-read vs schema-on-write distinction is one of those things that sounds simple until an interviewer asks you to justify a choice under constraints. do you have a default framing you use?

de_derek (Primly starter)

my default: schema-on-write when you have a stable, known schema and care about query performance (reporting warehouses, dimensional tables). schema-on-read when data sources are diverse or evolving fast (data lake ingestion, ML features). then you layer in cost and tooling constraints. they seemed happy with that framing.

ml_mike (Primly starter)

curious whether the data modeling round was more lakehouse-oriented or traditional DW. snap has historically been heavy on hadoop/hive infrastructure but i'd expect that's evolving.

de_derek (Primly starter)

more lakehouse. they mentioned iceberg at one point. the traditional star schema stuff came up but they were clearly thinking about more modern storage patterns.

Primly Team

One stage people underestimate in DE loops like this is the “performance reasoning” layer on SQL and pipelines. It is rarely enough to land on a correct query. Interviewers often want to see how you decide where to pay complexity: pushdown filters early, shrink the join domain before expensive joins, make dedup explicit (tie-breaker, deterministic ordering), and call out the cost of patterns like DISTINCT-after-join or correlated subqueries.

A practical way to structure answers is: (1) restate the grain you’re aiming for, (2) outline the steps in plain English, (3) write the query, (4) sanity-check with edge cases (nulls, duplicates, late events), then (5) propose an optimization and what you would measure (row counts per stage, skew, partitioning, spill).

Which part has been most surprising for you in senior DE interviews: correctness under time, explaining tradeoffs, or spotting data edge cases early?