Trees & Hierarchies

Most of Snowflake's data is organized as hierarchies. An account holds databases, a database holds schemas, a schema holds tables, and access control is a graph of roles granted to other roles. A query plan is a tree of operators too, and when a warehouse runs one in parallel, partial results move up that tree toward a single coordinator. The four problems in this lesson use those shapes: a Premium question, a Premium question edit, deletions that cap a tree's height, and a node count over async messages.

None of them gives you a node class with left and right pointers. The tree shows up as an edge list you have to root yourself, a flat parent array, grant pairs keyed by role name, or processes on separate hosts that can only message each other. Work out the representation first. After that, the pass over it is usually short, and the sections below show how to walk each one in the right order.

The Snowflake angle

Say the invariant before you touch the structure. Each problem here has a rule its output must keep. In the forest, a parent index never exceeds its child's, and every role a grant reaches belongs in the closure. The distributed total counts each node once, even when a message arrives twice. An edit that keeps the rule for most entries still corrupts the catalog, so write a five-line checker for the rule and run it on your own output. It finds an off-by-one in the compaction faster than rereading the loop, and it gives you something concrete to show when the interviewer asks how you know the edit is safe.

SnowflakePremium lesson · Coding round

The 4 Snowflake problems are below.

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

+ 1 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