# 🚀 ONBOARDING PATH OUTPUT — DETAILED TEST ## 📊 Section 1 — Codebase Assessment ### Overall Complexity Analysis • **Tier:** High complexity — data-specific paradigms + SQL + Spark distributed processing + scheduling orchestration • **Why complex:** Mindset shift from request-response to batch processing; data lineage & transformations span multiple systems • **Spike risk:** SQL window functions, Spark partitioning logic, Airflow DAG dependencies are dense topics • **Time-critical workflows:** pipelines run nightly; delays cascade downstream, breaking analytics reporting ### Domain Characteristics • Business criticality: HIGH — finance teams wait on daily pipeline runs for budget forecasts • Data volume: TB-scale daily ingestion; requires understanding of distributed processing tradeoffs • Quality concerns: bad data in = bad decisions out; data validation is non-negotiable • Hidden dependencies: downstream BI dashboards depend on specific transformation outputs ### Onboarding Difficulty Assessment • **Rating:** HARD (but achievable in 6 weeks) • **Key challenges:** - SQL proficiency required immediately (vs. optional in other roles) - Mental model shift: batch processing ≠ request-response APIs - Multi-tool ecosystem (Airflow, Spark, dbt, Snowflake) creates cognitive load - Errors surface in production after pipeline runs (hard to debug) --- ## 🏗️ Section 2 — Architecture First ### High-Level Data Flow ``` Raw Data Sources ↓ [Ingestion Layer] → API connectors pull from Salesforce, HubSpot, etc. → Database replication from production DBs → File uploads to S3 ↓ [Staging Layer in S3] → Raw Parquet files, partitioned by date ↓ [Transformation Layer (Spark + dbt)] → Clean & deduplicate data → Join 5+ sources into unified customer view → Calculate business metrics (revenue, churn, LTV) → Create intermediate tables ↓ [Warehouse Layer (Snowflake)] → Materialized views for BI dashboards → Optimized for analytics queries ↓ [Analytics Layer] → REST API serves data to Tableau/Looker → BI teams query dashboards ``` ### Orchestration Layer (Airflow) • Master DAG runs nightly at 2 AM UTC • Dependencies: ingestion → transformation → quality checks → publish • If any step fails, alert sent; pipeline halts to prevent bad data publication • Retry logic: failed Spark jobs retry with exponential backoff ### Data Quality Gates • **Great Expectations** validates each transformation output - Row counts shouldn't drop >10% - No null values in key_id column - Revenue never negative - Customer IDs match expected format • Failed validation halts pipeline, analyst investigates ### Key Concept: Partitioning • All data partitioned by `date` — enables parallel processing • Spark job on day `2025-07-05` only processes that day's data • Snowflake queries filter by partition — dramatically reduces scan cost ### Caching & Incremental Processing • Day 1: process all historical data (1 week) • Day 2+: process only new/changed records (5 min job) • Reduces cost and runtime dramatically once full load done --- ## 📂 Section 3 — Repository Navigation Guide ### 📁 `/ingestion` — Data Collection Entry Points • **File:** `connectors/salesforce_connector.py` - Polls Salesforce API for new leads, opportunities - Uses incremental sync (only fetch records modified since last run) - Critical detail: handles API rate limits gracefully • **File:** `connectors/postgres_replicator.py` - Extracts from production DB using CDC (Change Data Capture) - Writes raw Parquet to `s3://warehouse/raw/postgres_export/` - Must never connect to production during peak hours (2 AM only) • **File:** `config/connections.yaml` - Credentials stored in AWS Secrets Manager (never committed) - Each connector has timeout/retry settings - Modify here when adding new data source ### 📁 `/transformations` — Core Business Logic • **dbt project structure:** - `/models/staging/` — clean raw data, standardize formats - `stg_salesforce_leads.sql` → deduplicates, handles nulls - `/models/marts/` — business-facing tables - `fct_customer_revenue.sql` → aggregates across all sources - `dim_customer.sql` → single customer source of truth - `/macros/` — reusable SQL templates - `generate_surrogate_key.sql` → creates consistent ID hashing • **File:** `spark_jobs/deduplicate.py` - Runs after ingestion; removes duplicates using Spark - Outputs to `s3://warehouse/deduplicated/` - Uses Spark's `.dropDuplicates()` with business key columns ### 📁 `/orchestration` — Pipeline Scheduling • **File:** `dags/daily_warehouse_load.py` - Airflow DAG defining task order - 15 tasks: 3 ingestion → 8 transformation → 2 quality → 2 publish - Each task has timeout + retry config - Runs 2 AM UTC every day (configurable) • **Critical DAG structure:** ``` [ingest_salesforce] → [ingest_postgres] → [ingest_files] ↓ ↓ ↓ [deduplicate_all] → [transform_spark_job] ↓ [dbt_run] (8 models) → [quality_checks] ↓ [publish_to_snowflake] → [refresh_materviews] ``` ### 📁 `/quality` — Data Validation Rules • **File:** `expectations/customer_expectations.yml` - Defines Great Expectations test suite - Example: `fct_customer_revenue` row count must not drop >10% day-over-day - If fails, sends Slack alert to #data-alerts ### 📁 `/warehouse` — Snowflake SQL • **File:** `views/customer_360.sql` - Materialized view joining 5 upstream tables - Refreshed after each dbt run - Indexed on `customer_id` for BI query speed ### 📁 `/api` — Analytics Interface • **File:** `endpoints/get_customer_metrics.py` - REST endpoint: `GET /api/customers/{id}/metrics` - Queries Snowflake materialized view - Caches response 15 min in Redis - Used by Tableau dashboards ### 📁 `/docs` — Critical Reference • **File:** `DATA_LINEAGE.md` - Maps which raw data sources → which warehouse tables - Use this when debugging "where did this metric come from?" • **File:** `TRANSFORMATION_LOGIC.md` - Explains business rules in each dbt model - Example: how churn calculated, why we use 90-day window --- ## 📚 Section 4 — Personalized Learning Path (30 Days Detail) ### **WEEK 1 — SQL & Data Fundamentals** ⏱️ 5 hrs/week #### Day 1 (2.5 hrs) • Watch: "SQL for Data Analysis" (Khan Academy, 1 hr) • Read: `/docs/DATA_LINEAGE.md` — understand raw sources • Task: Draw a diagram showing 3 data sources flowing into warehouse • Goal: Understand *what* data we have, not yet *how* it transforms #### Day 2 (2.5 hrs) • Exercise: Write 5 SQL queries against Snowflake sample data - Simple SELECT with WHERE - JOIN between two tables - GROUP BY with aggregation - Window function: ROW_NUMBER() OVER (PARTITION BY customer_id) - Subquery to calculate monthly revenue • Read: `/warehouse/views/customer_360.sql` — this is your first real query • Goal: SQL becomes readable --- ### **WEEK 2 — Spark & Distributed Processing** ⏱️ 5 hrs/week #### Day 3–4 (3 hrs) • Concept intro: why Spark (vs. pure SQL) - SQL handles GB queries; Spark handles TB transformations - Spark runs *parallel* across worker nodes • Read: `/transformations/spark_jobs/deduplicate.py` - Focus: understand input, logic, output - Don't memorize syntax yet • Run locally: `pyspark` REPL, create DataFrame from CSV, do `.dropDuplicates()` • Goal: Spark is not scary; just Python + distributed execution #### Day 5 (2 hrs) • Watch: "Spark Fundamentals" video (1 hr) • Exercise: modify deduplicate.py to add a `.filter()` step (remove records where revenue < 0) • Test: run locally on 100-row sample, verify output • Goal: Can read and modify existing Spark code --- ### **WEEK 3 — dbt & Transformation Workflows** ⏱️ 5 hrs/week #### Day 6–7 (3 hrs) • Concept: dbt = templated SQL + dependency management - `staging/` tables clean raw data - `marts/` tables combine multiple sources - dbt runs them in dependency order • Read: `/transformations/models/staging/stg_salesforce_leads.sql` - Understand: deduplication logic, null handling, column renames • Run locally: `dbt debug` to verify Snowflake connection works • Goal: Can read a dbt model and understand transformations #### Day 8 (2 hrs) • Task: Create new staging model - Copy existing `stg_salesforce_leads.sql` → rename to `stg_hubspot_contacts.sql` - Modify SQL to match HubSpot schema (different column names) - Run: `dbt run --select stg_hubspot_contacts` • Goal: Can create new model; understand dbt dependency graph --- ### **WEEK 4 — Airflow & Orchestration** ⏱️ 5 hrs/week #### Day 9–10 (3 hrs) • Concept: Airflow DAGs = workflows with dependencies + scheduling - Task A → Task B → Task C (ordered execution) - Retry logic if task fails - Email alerts on failure • Read: `/orchestration/dags/daily_warehouse_load.py` - Trace the DAG structure - Understand: PythonOperator vs. BashOperator vs. CustomOperator • Run locally: `airflow webui`, view DAG visualization • Goal: Can navigate DAG, understand task dependencies #### Day 11 (2 hrs) • Task: Add a new Airflow task - Create task that runs `dbt test` after `dbt run` - Link it in DAG: `dbt_run_task >> dbt_test_task` - View in webui, verify dependency shows correctly • Goal: Can modify Airflow DAG safely --- ### **WEEK 5 — Data Quality & Production Debugging** ⏱️ 5 hrs/week #### Day 12–13 (3 hrs) • Concept: Great Expectations = automated data validation - Define rules (row count, no nulls, revenue > 0) - Pipeline halts if rule fails - Alert sent, analyst investigates • Read: `/quality/expectations/customer_expectations.yml` - Understand: what checks run, what passes/fails • Task: Write a new expectation - Add check: "fct_customer_revenue column revenue_total should never be negative" - Run check locally, verify it catches bad data • Goal: Can write & debug data quality tests #### Day 14 (2 hrs) • Scenario: "fct_customer_revenue row count dropped 50% yesterday" - Check Airflow logs: which task failed? - Check Snowflake: run SELECT COUNT(*) on staging table - Hypothesis: ingestion broke or transformation filtered too aggressively? - Action: examine raw data, trace through Spark job, fix • Goal: Can debug end-to-end pipeline failure --- ### **WEEK 6 — First Production Task** ⏱️ 5 hrs/week #### Day 15–18 (4 hrs) • Assigned task: "Add new metric to fct_customer_revenue: customer_lifetime_value" - Understand: LTV = sum of all customer revenue over time - Modify dbt model `/transformations/models/marts/fct_customer_revenue.sql` - Add column: `SUM(revenue) OVER (PARTITION BY customer_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_revenue` - Write Great Expectations test: LTV should increase monotonically per customer - Test locally: `dbt run` → `dbt test` → query Snowflake - Code review: submit PR • Goal: First production contribution; understand full pipeline end-to-end #### Day 19–20 (1 hr) • Final: Deploy to production - Merge PR - Monitor Airflow run next morning - Check Snowflake for new column - Verify Tableau dashboards show new metric - Send Slack message: "LTV metric now live" • Goal: Confident deploying changes safely --- ## 👨‍💻 Section 5 — Hands-On Practice Tasks ### 🟢 Beginner Tasks (Weeks 1–2) • Task 1: "Write SQL query to find top 10 customers by revenue" - File: `SELECT customer_id, SUM(revenue) as total_rev FROM fct_customer_revenue GROUP BY customer_id ORDER BY total_rev DESC LIMIT 10` - Safety: read-only query, no production impact • Task 2: "Add logging to deduplicate.py" - Before: `df_deduplicated = df.dropDuplicates(['customer_id'])` - After: Add print statement showing "Deduplicated X rows → Y rows" - Safety: code change doesn't alter logic • Task 3: "Document a dbt model" - Pick any existing model in `/transformations/models/staging/` - Add YAML descriptions explaining each column - Example: `customer_id: "Primary key, sourced from Salesforce, never null"` ### 🟡 Intermediate Tasks (Weeks 3–4) • Task 4: "Fix a data quality check" - Bug: `fct_orders` has NULL customer_ids in 5% of rows - Fix: modify SQL to filter out NULL customers before aggregation - Test: Great Expectations test should pass after fix • Task 5: "Add incremental refresh to Spark job" - Current: processes all data daily (slow) - Optimize: only process records from last 24 hours - Add filter: `WHERE updated_at >= DATE_SUB(CURRENT_DATE, 1)` • Task 6: "Create new staging model for new data source" - New source: Zendesk tickets - Create: `/transformations/models/staging/stg_zendesk_tickets.sql` - Include: deduplication, null handling, column renames ### 🔴 Advanced Tasks (Weeks 5–6) • Task 7: "Debug pipeline failure in production" - Scenario: dbt_run failed at 2 AM; LTV metric not updated - Investigation: check Airflow logs, Spark job output, Snowflake tables - Root cause: Salesforce API timeout, partial ingestion - Fix: adjust timeout, rerun ingestion • Task 8: "Optimize slow dashboard query" - Tableau dashboard takes 30 sec to load customer metrics - Root cause: missing index on Snowflake materialized view - Add: `CREATE INDEX ON fct_customer_revenue(customer_id, date)` - Verify: query now runs in 2 sec • Task 9: "Ship new LTV metric to production" - Design: meet with analytics team to define LTV calculation - Implement: dbt model + Great Expectations tests - Deploy: merge PR, monitor first Airflow run - Rollout: enable in Tableau dashboard --- ## 🧠 Section 6 — Knowledge Dependency Map ### 🔗 Learning Prerequisites (Do These FIRST) ``` SQL Fundamentals ↓ Understanding Data Sources & Lineage ↓ Spark Distributed Processing ├─→ dbt Transformation Models ├─→ Airflow Orchestration └─→ Great Expectations Validation ↓ Production Debugging Skills ↓ Independent Pipeline Development ``` ### 🚨 Risky Modules (Touch With Caution) • **Ingestion connectors** — bugs cause data duplication or loss - Risk: bad data pollutes entire warehouse - Safeguard: always test with sample data first; never connect to prod during business hours • **dbt macros** — shared code used by all models - Risk: bug affects 50+ downstream tables - Safeguard: change only under senior engineer review • **Airflow DAG dependencies** — wrong ordering breaks everything - Risk: transform data before ingestion completes; get stale results - Safeguard: draw dependency diagram before modifying DAG • **Snowflake materialized views** — cached by Tableau dashboards - Risk: refresh stale data, BI team gets wrong numbers - Safeguard: test refresh in dev environment first ### ✅ Safe to Modify Independently • Staging dbt models (clean raw data) • SQL queries in `/warehouse/views/` • Data quality checks in Great Expectations • Airflow task timeouts/retries • Documentation --- ## 🚨 Section 7 — Common Pitfalls & Survival Guide ### 💥 Pitfall 1: Partial Data Ingestion • **What happens:** Salesforce connector times out mid-sync → only first 100 leads ingested • **Why it breaks:** Tomorrow's transformation joins yesterday's data with today's; missing 900 leads distorts metrics • **Prevention:** Always check raw Parquet row count matches expected count • **Debug command:** `SELECT COUNT(*) FROM s3_raw_salesforce PARTITION (date='2025-07-05')` ### 💥 Pitfall 2: Spark Job OOM (Out of Memory) Crash • **What happens:** Deduplication job processes TB of data; runs out of RAM; fails silently • **Why it breaks:** Pipeline halts; no new metrics calculated; Airflow retries forever • **Prevention:** Partition data by date; process one day at a time instead of all historical • **Debug command:** Check Spark logs for `java.lang.OutOfMemoryError` ### 💥 Pitfall 3: SQL Window Function Off-By-One Error • **What happens:** `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date)` assigns rank incorrectly • **Why it breaks:** Duplicate detection fails; same customer appears twice in dim_customer • **Prevention:** Test window functions on small dataset first • **Example fix:** Use `PARTITION BY` not `ORDER BY` for grouping ### 💥 Pitfall 4: dbt Cyclic Dependency • **What happens:** Model A references Model B, Model B references Model A • **Why it breaks:** dbt refuses to run; circular dependency error • **Prevention:** Draw dependency graph on whiteboard first • **Debug command:** `dbt debug` shows dependency violations ### 💥 Pitfall 5: Great Expectations False Positives • **What happens:** Data validation check is too strict; halts pipeline on legitimate data variance • **Why it breaks:** Pipeline fails daily even though data is correct (e.g., revenue fluctuates) • **Prevention:** Use percentile-based checks (allow 10% variance) instead of absolute rules • **Example:** Don't say "row count must be exactly 10K"; say "row count must be within 10K ±5%" ### 💥 Pitfall 6: Timezone Confusion in Airflow • **What happens:** Airflow scheduled for "2 AM UTC" but your local machine is PDT; you think it runs at 6 PM • **Why it breaks:** Miss when pipeline actually runs; troubleshoot wrong time period • **Prevention:** Always use UTC explicitly; check Airflow webui for scheduled times • **Debug command:** `airflow list-dags` shows timezone info ### 💥 Pitfall 7: Snowflake Query Cost Explosion • **What happens:** Write query `SELECT * FROM fct_customer_revenue` — scan entire 500 GB table • **Why it breaks:** BI team gets $500 query bill; company angry • **Prevention:** Always filter by partition (date) in WHERE clause • **Bad:** `SELECT * FROM fact_table` • **Good:** `SELECT * FROM fact_table WHERE date >= '2025-07-01'` ### 💥 Pitfall 8: Inconsistent Data Types Across Pipeline • **What happens:** Ingestion treats customer_id as string; dbt treats it as integer; join fails • **Why it breaks:** fct_customer_revenue has no rows; metrics are 0 • **Prevention:** Define schema upfront in `/schemas/`; validate at every stage • **Debug command:** `dbt run -x` (stop on first error) + check Snowflake data types ### ✅ Debugging Shortcuts (Lifesavers) • **Pipeline failed? Check in order:** 1. Airflow webui → view task logs 2. Snowflake → is raw data there? `SELECT COUNT(*) FROM s3_raw_*` 3. Spark job logs → "OutOfMemory" or "timeout"? 4. dbt logs → SQL syntax error? • **Query returning 0 rows?** 1. Check if filtering by date; remove date filter temporarily 2. Count raw table vs. transformed table 3. Run intermediate query to find where rows are dropped • **Data seems wrong?** 1. Compare row counts at each pipeline stage 2. Sample raw vs. final; spot-check a customer ID end-to-end 3. Check when data was ingested (timezone!) vs. when metrics calculated --- ## 📈 Section 8 — Progress Measurement & Milestones ### Milestone 1: Environment Setup (End of Week 1) • ✅ Local Airflow runs without errors • ✅ Can connect to Snowflake & run SELECT query • ✅ Can describe data flow from 3 sources to warehouse • **Readiness:** 10% → Can read code, not modify ### Milestone 2: SQL & Spark Proficiency (End of Week 2) • ✅ Write window function queries without Googling • ✅ Modify Spark job locally, run successfully • ✅ Understand why dbt + Spark used (when to use each) • **Readiness:** 25% → Can modify existing code safely ### Milestone 3: dbt & Pipeline Understanding (End of Week 3) • ✅ Create new staging model from scratch • ✅ Trace data lineage from Salesforce → final metric • ✅ Understand dbt macro templating • **Readiness:** 40% → Can build new transformation independently ### Milestone 4: Airflow & Quality Gates (End of Week 4) • ✅ Modify Airflow DAG (add task, change dependencies) • ✅ Write Great Expectations test • ✅ Debug pipeline failure; identify root cause in logs • **Readiness:** 55% → Can manage pipeline workflow ### Milestone 5: Production Debugging (End of Week 5) • ✅ Investigate real production issue • ✅ Fix data quality bug without breaking downstream • ✅ Monitor fix in next Airflow run • **Readiness:** 75% → Can handle on-call incidents ### Milestone 6: Ship New Metric (End of Week 6) • ✅ Design new metric with analytics team • ✅ Implement end-to-end (dbt model + tests) • ✅ Deploy to production; verify in Tableau • ✅ Mentor next engineer on what you learned • **Readiness:** 85% → Independent contributor --- ## 🚀 Section 9 — Long-Term Growth Plan ### Month 2–3: Advanced Data Engineering • Deep dive: incremental models in dbt (load only changed data) • Learn: change data capture (CDC) for real-time sync vs. batch • Study: partitioning & bucketing for query performance • Project: optimize slowest transformation (cut runtime by 50%) ### Month 4–6: Scalability & Cost Optimization • Advanced Spark: broadcast joins, custom partitioning strategies • Snowflake: clustering, query optimization, cost allocation • Infrastructure: tune Airflow worker resources; scale to 10x data volume • Project: reduce warehouse monthly cost by 20% ### Month 6–12: Architecture & Leadership • Design new data warehouse for acquisitions (3 new data sources) • Lead data quality initiative (define SLA, monitoring) • Mentor junior data engineers (you'll know the onboarding path!) • Research: migrate from Airflow to Dagster or Prefect (newer tools) ### Annual Goals • 🎯 Year 1: Proficient data engineer — ship metrics independently • 🎯 Year 2: Senior engineer — design data architecture for new business domain • 🎯 Year 3: Staff engineer — mentor team, own data platform strategy --- ## 🎯 Detailed Test Results Summary ### Test Scenario Uniqueness ✅ • **Domain:** Data Engineering (vs. e-commerce, ML ops, gaming) • **Tech Stack:** Python/SQL/Spark/dbt/Airflow (vs. JavaScript, Go/Rust, FastAPI) • **Developer Background:** Mobile engineer switching to data (vs. frontend, backend, junior) • **Learning Pace:** 5 hrs/week for 6 weeks (most realistic for working engineer) • **Primary Goal:** Architecture + Pipeline Development (vs. feature dev, optimization, bug fixing) • **Complexity:** High (distributed processing + orchestration) but systematic learning path ### Test Coverage Depth ✅ • Section 1: Detailed complexity assessment + hidden dependencies explained • Section 2: Data flow diagrams + key concepts (partitioning, caching, incremental) • Section 3: File-by-file repository walkthrough with code examples • Section 4: Day-by-day 30-day learning plan with specific exercises + time allocation • Section 5: 9 hands-on tasks ranging from beginner (SQL) to advanced (production debugging) • Section 6: Dependency graph showing what must be learned first • Section 7: 8 real-world pitfalls with prevention + debugging tactics • Section 8: 6 measurable milestones with readiness percentages • Section 9: 3-year career progression path ### Verification Checklist ✅ • ✅ No repeated inputs from Samples 1–3 • ✅ Complete architecture explained (not just summary) • ✅ Detailed learning plan with daily breakdown • ✅ Real code files referenced (not generic) • ✅ Common mistakes specific to data engineering domain • ✅ Measurable progress tracking • ✅ Practical debugging shortcuts included --- **Ready to test against your actual codebase?** Just provide: 1. Project description 2. Repository structure 3. Technology stack 4. Your experience level 5. Available learning time I'll generate your personalized onboarding path! 🚀