Data Engineer Assessment Template

In a star schema, fact tables typically contain:

• A.Descriptive attributes about business entities
• B.Foreign keys and measurable metrics
• C.Slowly changing data with history
• D.Only denormalized dimensions

Given `sessions(user_id, session_start, page)`, write a query that returns each user's first 3 pages in order of time for each session.

Given `orders(id, user_id, amount, created_at)`, write a query that returns each user's: running 7-day total, running 28-day total, and rank by 28-day total across all users. Single query using window functions.

Write an Airflow DAG definition that: - Runs daily at 2am UTC - Extracts yesterday's data from Postgres - Loads it into S3 as parquet - Triggers a downstream dbt model on success - Retries 3 times with exponential backoff on failure

Your dbt pipeline produces incorrect results one day per week. Walk through how you'd investigate — what you'd check first, what tools you'd use, how you'd reproduce.