Database Internals & C++

Topological sort and data structure design lead most Snowflake loops, but a few coding prompts are SQL problems in disguise. You might count distinct customers and total revenue per product, reject inserted rows whose foreign key has no parent, or reduce a change log to its net effect so a row that a later delete retracted never comes out. Each one has a short SQL reading, and your job is to write the operator that would run it.

Say the SQL reading out loud before you write code. Revenue per product is a GROUP BY with COUNT(DISTINCT customer_id) over products left-joined to sales, which an engine runs as a hash join feeding a hash aggregate. An integrity check is a semi-join, where each inserted key needs one match in the parent's key column and no parent row comes back out. Compacting the change log means computing the net change per user, the same rule Snowflake Streams apply between two offsets. Once you've named the operator, its memory use and its behavior at scale follow from what the engine already does.

Teams that build the core engine, storage or query execution add a C++ round on top. It covers memory ownership, thread safety and engine pieces like a k-way merge. The round is team-gated, and the general coding rounds let you pick your language. Ask your recruiter whether your loop includes it, and if it does, put real practice time into the C++ round.

The Snowflake angle

Pick the side that stays in memory, and say why. Each operator here keeps one input in memory while the other streams past. Revenue keeps the small products table, the integrity check keeps the set of parent keys, and the change log keeps each user's live rows that a later delete might still retract. Name that side, say why it's small, then say what happens when it stops being small. Snowflake's hybrid tables enforce foreign keys, so an insert there runs a check shaped like the second problem.

SnowflakePremium lesson · Coding round

The 5 Snowflake problems are below.

1. 1A Premium questionCoding · Medium
2. 2A Premium questionCoding · Hard
3. 3A Premium questionCoding · Medium

+ 2 more in this lesson

Each with hints, the follow-up and a tested solution.

Get the Snowflake questions

$49 a year · all 100 companies · cancel anytime

From 111 Snowflake reports, forum threads behind logins and write-ups in Chinese.

Read a free Snowflake lesson firstAlready a member? Sign in