The Talent Funnel

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5
Asked in
PORTAL

Problem

A job marketplace tracks candidate activity from the moment a job listing is viewed through to an accepted offer. The analytics team needs to measure funnel drop-off rates at each stage, compare conversion by job type and location, track time-to-hire by company, and attribute sourcing channel credit. Design a schema that supports all of this.

Thousands applied. One accepted. Where did the rest go?

Worked solution and explanation

Why this problem exists in real interviews

This probes whether you model a multi-stage conversion workflow as an event log rather than a wide row with stage-named columns. The signal is picking one row per stage transition so new stages become data, not a schema migration, and time-to-hire becomes a window function instead of a subtraction across fixed columns.

Break down the requirements

Step 1: Declare the event grain

fact_funnel_events records one row per stage reached per candidate per job. New stages append rows; no schema change needed.

Step 2: Conform company and location as dimensions

Company size and industry belong on dim_company, not on the event. This lets conversion rates split cleanly by firmographic or geography.

Step 3: Attribute sourcing via dim_source_channel

The source_channel_sk on the first event for a (candidate, job) pair carries attribution. Last-touch attribution is a query choice, not a schema choice.

Step 4: Compute time-to-hire from event timestamps

Time-to-hire is offer_accepted.event_ts - application_submitted.event_ts per (candidate, job). Window functions over the event fact make this a few lines of SQL.

The solution

Below is one defensible model. The conceptual anchor is event-log grain: new stages are rows, not columns.

`fact_funnel_events` grain = one row per stage event per candidate per job
  • dim_job: job_sk BIGINT PK, job_nk TEXT, job_type TEXT, salary_bucket TEXT, company_sk BIGINT FK, location_sk BIGINT FK
  • dim_candidate: candidate_sk BIGINT PK, candidate_nk TEXT, signup_ts TIMESTAMP, seniority TEXT
  • dim_company: company_sk BIGINT PK, company_nk TEXT, industry TEXT, size_bucket TEXT
  • dim_location: location_sk BIGINT PK, city TEXT, country TEXT, metro_area TEXT
  • dim_source_channel: source_channel_sk BIGINT PK, channel_name TEXT, channel_type TEXT
  • fact_funnel_events: funnel_event_id BIGINT PK, candidate_sk BIGINT FK, job_sk BIGINT FK, company_sk BIGINT FK, location_sk BIGINT FK, source_channel_sk BIGINT FK, stage_name TEXT, event_ts TIMESTAMP
  • dim_candidate references fact_funnel_events
  • dim_job references fact_funnel_events
  • dim_company references fact_funnel_events
  • dim_location references fact_funnel_events
  • dim_source_channel references fact_funnel_events
  • dim_company references dim_job
  • dim_location references dim_job

The analysis pattern

Funnel drop-off by job type
WITH stages AS (
  SELECT candidate_sk, job_sk, stage_name, MIN(event_ts) AS first_ts
  FROM fact_funnel_events
  GROUP BY candidate_sk, job_sk, stage_name
)
SELECT
    j.job_type,
    COUNT(*) FILTER (WHERE s.stage_name = 'view') AS views,
    COUNT(*) FILTER (WHERE s.stage_name = 'apply') AS applies,
    COUNT(*) FILTER (WHERE s.stage_name = 'offer_accepted') AS hires
FROM stages s
JOIN dim_job j ON j.job_sk = s.job_sk
GROUP BY j.job_type

Trade-offs and alternatives

Event-log fact
Flexible schema, additive aggregation, easy stage additions. Reporting needs window functions and idempotent ingestion keys. Storage scales with stage count.
Accumulating snapshot fact
One row per candidate-job with timestamp columns per stage. Very fast stage-to-stage timing queries. Every new stage triggers a schema migration and an update to in-flight rows.

Common follow-up questions