Master data engineering and AI like a big tech engineer

Real Time Formula 1 Analytics

Big Bag Data

Chess.com Analytics

Trading Strategy for Crypto Currency - Analytics Engineering Capstone Submission

BetFlow - Real Time Sports Betting App

Showcase of student projects

Capstone Projects

Book a discovery call with Zach

Discovery call

Our students have gone on to work at companies like Meta, Airbnb and Amazon. As well as achieve 100% raises!

Fast Track Your Career

Immediate free cloud access to Databricks, AWS, Snowflake, Astronomer, and more!

Free Cloud Access with tons of hands on exercises

Weekly Guest Speaker Sessions

Why Choose DataExpert.io Academy?

A themed six-round mock interview simulating a real OpenAI AI Engineer loop. Covers SQL for ML monitoring and usage analytics, system design for LLM-powered infrastructure, evaluation framework architecture, behavioral questions around OpenAI's mission of safe AGI, and Python problems involving token optimization and embedding search.

OpenAI AI Engineer Interview

OpenAI tracks every API request in a table called `api_requests`:

  api_requests (
    request_id UUID PRIMARY KEY,
    org_id VARCHAR(64),
    model VARCHAR(50),          -- 'gpt-4o', 'gpt-4o-mini', 'o1', 'o3-mini', etc.
    prompt_tokens INT,
    completion_tokens INT,
    total_tokens INT,
    latency_ms INT,
    status_code INT,            -- 200, 429 (rate limit), 500, etc.
    created_at TIMESTAMP
  )

  model_pricing (
    model VARCHAR(50) PRIMARY KEY,
    input_price_per_1k DECIMAL(10, 6),   -- $/1K input tokens
    output_price_per_1k DECIMAL(10, 6),  -- $/1K output tokens
    effective_from DATE
  )

Write a SQL query that computes a daily cost attribution report for the last 30 days:

Output: date, org_id, model, total_requests, successful_requests, total_input_tokens, total_output_tokens, estimated_cost_usd, p50_latency_ms, p99_latency_ms, error_rate_pct

Order by date DESC, estimated_cost_usd DESC.

Use the most recent pricing that was effective on or before the request date.

**Expected Output:**

| Column | Type |
|--------|------|
| date | TEXT (YYYY-MM-DD) |
| org_id | TEXT |
| model | TEXT |
| total_requests | INTEGER |
| successful_requests | INTEGER |
| total_input_tokens | INTEGER |
| total_output_tokens | INTEGER |
| estimated_cost_usd | REAL (4 decimal places) |
| p50_latency_ms | INTEGER |
| p99_latency_ms | INTEGER |
| error_rate_pct | REAL (percentage 0-100, 2 decimal places) |

Order by `date` DESC, `estimated_cost_usd` DESC.

For percentile calculations, use the nearest-rank method. If a group has only 1 request, p50 = p99 = that request's latency.

API Usage Analytics: Token Cost Attribution — OpenAI

OpenAI runs continuous evaluation suites against deployed models. Results are stored in:

  eval_runs (
    run_id UUID PRIMARY KEY,
    eval_suite VARCHAR(100),     -- 'mmlu', 'humaneval', 'math_olympiad', 'safety_refusals', etc.
    model_version VARCHAR(50),   -- 'gpt-4o-2025-06-01', etc.
    score DECIMAL(5, 4),         -- 0.0000 to 1.0000
    sample_size INT,
    run_timestamp TIMESTAMP
  )

Write a SQL query that detects potential model regressions — defined as:
- A model_version's latest score on any eval_suite is more than 2 standard deviations below the trailing 30-day average for that suite+model combination
- Only flag if there are at least 5 prior runs to establish the baseline

NOTE: This SQLite environment has no SQRT or STDDEV function. Work with the population
variance instead: variance = AVG(score*score) - AVG(score)*AVG(score). "More than 2
standard deviations below the mean" is exactly:
  (latest_score - trailing_avg) * (latest_score - trailing_avg) > 4 * variance
  AND latest_score < trailing_avg

Output: eval_suite, model_version, latest_score, trailing_avg, z_squared, latest_run_timestamp, flagged (1 if regression)

where z_squared = (latest_score - trailing_avg)^2 / variance, rounded to 2 decimals.

Only include flagged rows. Order by z_squared DESC (most severe regression first).

**Expected Output:**

| Column | Type |
|--------|------|
| eval_suite | TEXT |
| model_version | TEXT |
| latest_score | REAL (4 decimal places) |
| trailing_avg | REAL (4 decimal places) |
| z_squared | REAL (2 decimal places) |
| latest_run_timestamp | TEXT (ISO 8601) |
| flagged | INTEGER (1 = true) |

Only return rows where `flagged = 1`. Order by `z_squared` DESC (most severe regression first).

If all prior scores are identical the variance is (numerically almost) zero and z is
undefined — exclude such rows by requiring `variance > 1e-12` (floating-point arithmetic
can leave a tiny nonzero residue where the true variance is 0).
The "trailing 30-day" window means runs in the 30 days BEFORE the latest run (exclusive of the latest run itself).

Detecting Model Regression: Eval Score Anomalies — OpenAI

Fact table at request-level grain with lifecycle timestamps (received_at, queue_start, prefill_start, generation_start, delivered_at), infrastructure dimensions (dim_datacenter, dim_gpu_cluster, dim_model_deployment), organization dimension, content safety fact or bridge table, measures for token counts and latency breakdowns, periodic snapshot fact for GPU utilization at cluster-minute grain.

LLM Inference Observability Data Model — OpenAI

Production traffic sampler → evaluation queue (SQS/Kafka), orchestration engine (Airflow/Temporal), judge model calls via internal API with structured output parsing, metric store (time-series DB or data warehouse), statistical comparison engine for regression detection, deployment gate integration (CI/CD hook), cost attribution by eval_suite/model, dashboard layer with drill-down from suite → query → individual judgment.

RAG Evaluation Pipeline Architecture — OpenAI

Concrete technical system (not abstract), specific safety/correctness risk (data leakage, hallucination, failure modes, etc.), quantitative analysis (benchmarks, error rates, blast radius), evidence of principled decision-making, shows ability to communicate trade-offs to non-technical stakeholders, demonstrates intellectual honesty about whether the decision was right.

OpenAI Mission: Safety vs. Capability Trade-off

Real project with genuine pivots (not minor scope changes), shows ability to identify sunk cost vs. reusable components, demonstrates pragmatic decision-making (not perfectionism), evidence of communication with leadership about trade-offs, lessons learned applied to future work (e.g., modular architecture, feature flags, incremental rollouts).

OpenAI Culture: Building Under Deep Uncertainty

OpenAI's API serves requests where many users send prompts with identical 
system message prefixes (e.g., the same "You are a helpful assistant..." 
followed by different user messages).

Design a prompt caching system that identifies and deduplicates common 
prefixes to reduce redundant computation. The cache operates at the 
token level — two prompts share a cache hit if their token sequences 
share a common prefix of length >= min_prefix_tokens.

Implement:

class PromptCache:
    def __init__(self, max_entries: int, min_prefix_tokens: int = 128):
        """
        max_entries: maximum number of unique prefixes to cache
        min_prefix_tokens: minimum shared prefix length to qualify for caching
        """
        pass
    
    def lookup(self, tokens: List[int]) -> Tuple[Optional[str], int]:
        """
        Given a token sequence, find the longest cached prefix.
        Returns: (cache_key or None, prefix_length that matched)
        If no prefix of length >= min_prefix_tokens matches, return (None, 0)
        """
        pass
    
    def insert(self, tokens: List[int], cache_key: str) -> None:
        """
        Insert a new prefix into the cache. 
        If cache is full, evict the least-recently-used entry.
        The cached prefix is tokens[0:len(tokens)] — the full sequence.
        """
        pass
    
    def get_stats(self) -> Dict[str, int]:
        """
        Return: {
            "total_lookups": int,
            "cache_hits": int,
            "total_tokens_saved": int,  # sum of matched prefix lengths on hits
            "evictions": int
        }
        """
        pass

Requirements:
- lookup must be O(min_prefix_tokens + log(max_entries)) or better for typical cases
- Use a trie or hash-based prefix matching approach
- LRU eviction when cache is full
- Thread-safety is NOT required (single-threaded usage)

Token-Efficient Prompt Caching System — OpenAI

Numpy-based batch cosine similarity (dot product on normalized vectors), inverted index for metadata (tag → set of doc_ids) for fast pre-filtering, correct retry-with-expansion logic, proper normalization of embeddings, handles edge cases (no results after max retries, empty filter dict means no constraint), efficient set intersection for multi-filter AND logic.

OpenAI AI Engineer Interview

In order. Round N+1 unlocks when Round N passes.