Databricks & Snowflake: AI / ML Engineer

SELECT DATE_TRUNC('month', t.created_at) AS month,
       COUNT(DISTINCT t.user_id)          AS active_users,
       COUNT(*)                           AS transactions,
       SUM(t.quantity * p.price)          AS order_amount
FROM transactions t
JOIN products p ON p.id = t.product_id
WHERE t.created_at >= DATE '2025-01-01' AND t.created_at < DATE '2026-01-01'
GROUP BY 1 ORDER BY 1;

• A sargable date range (no function on the column in WHERE) lets the engine prune partitions.

• Data: documents in Delta tables governed by a catalog (lineage, ACLs); an incremental pipeline for parse → chunk → embed.
• Vector index: synced from the Delta table (it updates automatically as data changes), filtered by permissions.
• App: chain/agent code logged as a model (MLflow), served behind an endpoint with guardrails and an AI gateway (rate limits, usage tracking).
• Evaluation: MLflow LLM evaluation / an LLM judge on a curated set; tracing in production; feedback tables.
• Governance: one permission model for data and AI assets; PII tagging; cost monitoring.

1. Read the query plan: full scans? Shuffles? Skew?
2. Prune data: partition/clustering keys, date filters, select only the needed columns (columnar formats).
3. Joins: broadcast small tables, filter before joining, avoid exploding many-to-many joins, handle skewed keys (salting).
4. Aggregations: pre-aggregate or materialise; avoid SELECT DISTINCT on huge sets when not needed.
5. Caching / Z-ordering / clustering; check file sizes (compact small files).

• Working end to end: a clear entry point, reproducible environment (requirements file and a Makefile), sample run.
• Correctness and tests: unit tests on key functions, an evaluation script with metrics.
• Clean code: modules, typed functions, configuration over hard-coding, logging.
• README: the problem, the approach and its trade-offs, results, limitations, "what I'd do with more time".
• Respect the time box. Polish matters more than extra features.

1. Users and questions: collect 30 real questions; split them into document Q&A (RAG) and analytics questions (text-to-SQL).
2. Data readiness: where the data lives, quality, permissions, semantic definitions (metrics layer).
3. Approach: RAG for documents; text-to-SQL with a curated semantic layer, few-shot examples, query validation and row-level security for tables.
4. Success metrics: answer accuracy on the question set, time saved, adoption.
5. Plan: a 2-week proof of concept → pilot with one team → production hardening (monitoring, evaluation, governance).

• Feature engineering in SQL / Snowpark (Python DataFrames) inside the warehouse, so data never moves.
• Training: Snowpark ML or a model registry; UDFs or containers for inference.
• LLM functions (Cortex-style) for summarisation and classification over tables; vector search for RAG.
• Governance: role-based access, masking policies on PII, cost control via warehouse sizing.