Data Engineer Take-Home Examples

Two-phase: ingest writes to SQLite with FTS5 full-text index. Updates write to a small change log table; a background process applies them every 5 seconds. Query layer is FastAPI with three endpoints (search, filter, nearby).

Trade-off documented: SQLite chosen over Elasticsearch for self-contained simplicity at this data size. Production version would use Elasticsearch or OpenSearch with a Kafka-fed update stream.

Trap 1: Building a clever inverted index from scratch instead of using SQLite FTS5 or Whoosh. Wrong choice: reviewers care about the end-to-end design, not your custom index code.

Trap 2: Skipping the update path. Many candidates ingest the initial dump and forget the 5-second freshness requirement. The freshness path is half the score.

Trap 3: Geo distance with naive Pythagoras. Use haversine. Document that you considered geohash bucketing for performance at larger scale.

Use spark.read.json with explicit schema (skip auto-inference; it's slow). Repartition by date before write. Use approx_count_distinct for distinct types (mention as cost-saving choice in README).

Output Parquet with snappy compression. Partition by date. Coalesce to control file count (avoid the small-file problem).

README discusses: why approx vs exact distinct (cost vs accuracy), why Parquet over CSV (column pruning, compression), why partition by date (downstream queries almost always filter by date).

models/
├── staging/
│   ├── stg_events.sql       # 1:1 from raw, type cast, rename
│   ├── stg_users.sql
│   └── stg_products.sql
├── intermediate/
│   └── int_user_actions_enriched.sql
├── marts/
│   ├── core/
│   │   ├── dim_user.sql      # SCD Type 2 via dbt snapshots
│   │   ├── dim_product.sql
│   │   ├── dim_date.sql
│   │   └── fact_user_actions.sql
│   └── core.yml              # tests + docs
snapshots/
└── dim_user_snapshot.sql    # SCD Type 2 logic via dbt snapshot
tests/
└── assert_no_duplicate_user_action_per_ts.sql
docs/
└── README.md

Use Faust agent with @app.agent stream processing. Maintain in-memory dict of (user_id) to last_event_ts and accumulating events. On new event: if gap exceeds 30 min, emit previous session and start new. Background task expires sessions on TTL.

State persistence: Faust uses RocksDB by default (mention in README). Without persistence, restart loses in-progress sessions; with it, sessions resume cleanly.

Failure modes documented in README: consumer crash mid- session (state recovers from RocksDB), producer outage (consumer stays alive, processes whatever arrives), late events (more than 1 hour past current head: emit to dead-letter topic).

One file per question (q1.sql ... q8.sql) instead of one giant file. Each file has a top comment with the question, the approach, and any assumptions. README has a table linking questions to file paths.

For the gap-and-island question (consecutive spending days), candidates who use the ROW_NUMBER subtraction trick score 5/5. Candidates who use a self-join approach score 4/5. Candidates who use a procedural loop score 2/5.

For the LTV ranking question, DENSE_RANK over RANK is the right choice (preserves tied users). Mentioning the distinction is a senior signal.