Data Engineer Technical Assessment

What You're Doing

This is a 2-hour technical assessment with two parts:

1. Build & Review — Write a staging model, then review and fix a flawed intermediate model that a junior engineer generated with AI.
2. Architecture — Write a short design document recommending a staging strategy for a real-world scenario.

You are expected to use AI tools. See the AI Usage section below.

---

Repository Structure

├── README.md                              ← You are here
├── dbt_project.yml                        ← Project config (layers, schemas, vars)
├── profiles.example.yml                   ← Snowflake connection template
├── models/
│   ├── 01_staging/demo/salesforce/       ← Your staging model goes here
│   │   └── _sources.yml                  ← Source definition
│   └── 02_intermediate/demo/
│       └── int_demo__gifts_enriched.sql  ← The flawed model to review + fix
└── seeds/
    ├── raw_opportunity.csv               ← Gift data (200 rows)
    └── raw_contact.csv                   ← Contact data (350 rows)

---

Context

This is a sanitized slice of a multi-tenant dbt project built on Snowflake. The project serves analytics for multiple nonprofit organizations, following a layered architecture:

raw (source tables) → staging → intermediate → core → marts

Each client's raw data lands in a separate schema and is unified through cross-client models at the core layer.

---

Project Conventions

These apply to all parts of the assignment:

1. Metadata columns: Every model at every layer must output these four columns:

   • _organization_code — the client/tenant identifier (e.g., 'DEMO')
   • _source_platform — where the data came from (e.g., 'salesforce')
   • _extracted_at — the ingestion timestamp (passed through from staging)
   • _custom_fields — a VARIANT column for client-specific fields not in the canonical schema

2. Multi-tenant architecture: Multiple organizations share the same models. The _organization_code column is how row-level security filters data per client.

3. Incremental strategy: This project uses merge with a 7-day lookback window. When a model joins two incremental sources, think about what happens when only one side has new records.

4. Grain discipline: If a model claims a grain (e.g., "one row per gift"), the SQL must guarantee it — not just assume upstream dedup is sufficient.

5. Naming: Intermediate models should use the same column names as their upstream staging models (e.g., if staging outputs first_name, the intermediate should call it first_name — not contact_first_name).

6. Staging pattern: source → incremental filter (7-day lookback) → dedup by PK (qualify row_number()) → rename/cast → metadata columns.

---

Canonical Schema Reference

Your staging model should output these columns (rename from raw as needed):

Contacts staging (stg_demo_salesforce__contact):

Output column	Type	Notes
contact_id	varchar	PK
first_name	varchar
last_name	varchar
prefix	varchar
suffix	varchar
email	varchar	Primary email
phone	varchar	Primary phone
mailing_city	varchar
mailing_state	varchar
mailing_postal_code	varchar
mailing_country	varchar
is_deceased	boolean
created_at	date	Account creation
wealth_score	number
gift_capacity_rating	varchar
portfolio	varchar
_organization_code	varchar	'DEMO'
_source_platform	varchar	'salesforce'
_extracted_at	timestamp_ntz	From _INGESTED_AT
_custom_fields	variant	Remaining non-canonical columns

Gifts staging (stg_demo_salesforce__opportunity):

The gifts staging model is provided for you — the flawed intermediate model in Part A Step 2 already references it. You do NOT need to build it. Focus on contacts staging and the intermediate review.

---

The Assignment: Two Parts

Part A: Build Staging + Review & Fix the Intermediate

You have two raw tables:

• seeds/raw_opportunity.csv — gift/donation transactions (200 rows)
• seeds/raw_contact.csv — donor contact records (350 rows)

Step 1: Build a staging model for contacts

• models/01_staging/demo/salesforce/stg_demo_salesforce__contact.sql
• Add _models.yml with appropriate tests

Use the canonical schema above and the project conventions. Follow the pattern: source → incremental filter → dedup → rename/cast → metadata columns.

Step 2: Review & fix the intermediate model

A junior engineer used AI to generate models/02_intermediate/demo/int_demo__gifts_enriched.sql. They asked an LLM to "create an intermediate model that enriches gifts with contact information, one row per gift."

The model passed dbt run without errors — but it has multiple issues.

Your task:

1. Review the model and identify all issues — bugs, convention violations, and architectural concerns.
2. Write your critique in a file called REVIEW.md.
3. Fix the model in place. Your corrected version should reference the staging model you built in Step 1 (for contacts) and assume a gifts staging model exists with the columns the intermediate already references.

What to look for:

• Does the SQL actually guarantee the claimed grain?
• Does incremental logic work correctly on re-runs (not just first run)?
• Does it follow the project conventions above?
• Is this the right level of denormalization for an intermediate model?
• Would you be comfortable deploying this to serve real dashboards?

Part B: Architecture Decision — Staging Strategy

The DEMO client has two possible data feeds for contacts:

• Feed A: A full-refresh CSV export dropped nightly. Every row is the complete current state of all contacts. If a contact is updated, the next export has the new values. If a contact is deleted, it simply disappears from the next export.

• Feed B: A CDC (change data capture) stream that sends only changed records in near-real-time, with an _updated_at timestamp on each record. It includes insert, update, and delete events.

You need to decide how to stage this data. Consider:

• What are the tradeoffs of building your staging model against Feed A (full-refresh) vs. Feed B (CDC)?
• What happens when Feed B has a gap (missed events for a few hours)? How do you recover?
• If both feeds are available, how would you reconcile them? Would you use both, or pick one?
• How does your choice affect the incremental logic downstream (intermediate/core models)?
• Given that this is a multi-tenant project where each client may have different feed types, how does your design generalize?

Your task: Write ARCHITECTURE_DECISION.md documenting your recommendation with tradeoffs. There is no single right answer — we are evaluating the quality of your reasoning.

---

AI Usage — Required

We are an AI-forward team. We expect you to use AI tools to complete this assignment. Not using AI would be a negative signal — we want to see how you collaborate with it.

What to submit

1. AI transcript or log — Export the chat history from whichever AI tool you used most during this assignment (Claude, ChatGPT, Cursor chat, CoCo, etc.) and include it as AI_TRANSCRIPT.md or AI_TRANSCRIPT.pdf.

If you used multiple tools, include the transcript from your primary session.

2. One specific moment where AI was wrong — In AI_PROCESS.md, describe one concrete instance where the AI gave you incorrect or incomplete output during this assignment:

• What did you ask?
• What did it produce?
• How did you identify it was wrong?
• What did you change and why?

This is not optional. If the AI never made a mistake, you weren't pushing it hard enough or you weren't validating critically enough.

3. Short reflection — In 2-3 sentences at the bottom of AI_PROCESS.md:

• What's your general philosophy on when to trust vs. question AI output in data engineering?

---

Why we ask this: Our team uses AI daily for code generation, debugging, documentation, and architecture decisions. We care less about whether you can write a model from scratch and more about whether you can effectively direct AI tools, validate their output, and know when human judgment matters.

---

Setup (optional)

If you want to run dbt locally:

1. Copy profiles.example.yml to ~/.dbt/profiles.yml and fill in credentials
2. Run dbt seed to load CSVs into Snowflake
3. Run your models with dbt run

If you can't set up Snowflake access, that's fine — submit the code files.

---

Evaluation Criteria

Criteria	What we're looking for
Convention adherence	Did you follow the staging pattern and include all metadata columns?
Data modeling	Is the grain correct? Are joins safe? Is incremental logic sound?
Code review quality	Did you catch the real bugs? Do you explain WHY they matter, not just what's wrong?
Architectural reasoning	Is your reasoning thorough? Do you consider tradeoffs, failure modes, and generalizability?
AI workflow	Do you use AI effectively and validate critically?
Written communication	Are your docs clear, structured, and professional?

---

Submission

Zip your completed work and send it back to us. Your submission should include:

• Your staging model and _models.yml
• The corrected int_demo__gifts_enriched.sql
• REVIEW.md
• ARCHITECTURE_DECISION.md
• AI_TRANSCRIPT.md (or .pdf)
• AI_PROCESS.md

Deadline: 5 days from receipt.

Good luck — we're looking forward to seeing how you think.