# 🗺️ PIPELINE EXPLORER'S MAP **Onboarding Readiness Score:** 6.5/10 | **Complexity Level:** Moderate-High | **Data Quality Checks:** 12 core checks, ~70% coverage estimated | **Time to Productivity:** 5 weeks @ 8 hrs/week | **Pipeline Stages:** 5 (ingestion → transform → quality → warehouse → publish) | **Spike Risks:** 4 identified **30-sec summary:** Moderate-high complexity — five well-known SaaS/DB sources rather than 50+, which lowers ingestion sprawl risk, but the dual dbt+Spark transformation layer plus Airflow orchestration means real tooling breadth. Primary Challenge: SQL proficiency (you're starting from beginner SQL while the pipeline leans on window functions and incremental logic from day one). Learning Path: 5 weeks. Hidden Dependencies: 3 (Finance reports off Stripe reconciliation, Tableau off Snowflake materialized views, internal APIs off warehouse freshness). --- # 📊 SECTION 1 — CODEBASE ASSESSMENT | Dimension | Rating | Why | Spike Risk | |---|---|---|---| | Data paradigm shift | 🟠 | Batch mindset (nightly runs) vs. your request-response backend instincts | Medium | | SQL proficiency | 🔴 | Beginner SQL meeting window functions, CTEs, incremental filters immediately | High | | Tool ecosystem | 🟠 | dbt + Spark + Airflow + Snowflake + Great Expectations + GitHub Actions = 6 tools | Medium | | Data volume | 🟡 | Not TB-scale by default with 5 sources, but Stripe/GA can spike in row count | Low | | Quality concerns | 🟢 | Great Expectations already in stack — good habit to inherit early | Low | | CI/CD (GitHub Actions) | 🟡 | New layer vs. typical Airflow-only setups — dbt CI runs need understanding | Medium | **Domain Characteristics:** Business criticality is high — Finance reports depend on Stripe reconciliation accuracy, and Tableau dashboards are watched by non-technical stakeholders who notice stale data fast. Data volume is moderate (5 sources, not 50+), so distributed processing exists but isn't the dominant daily concern the way it would be in a 50-source shop. Hidden dependencies: internal APIs likely serve cached Snowflake views to other backend services — a transformation bug propagates further than it looks. **Onboarding Difficulty:** MODERATE-HIGH, achievable in 5 weeks at 8 hrs/week. - SQL is the real gate — window functions and dbt's Jinja-templated SQL will feel foreign to a backend engineer used to ORMs. - Batch mindset shift — errors surface hours after a nightly run, not synchronously like an API 500. - Tool count (6) creates real but manageable cognitive load given your Python background — dbt and Airflow are both Python-adjacent. - GitHub Actions + dbt CI is a layer many onboarding guides skip; worth deliberate attention since it gatekeeps merges. --- # 🏗️ SECTION 2 — ARCHITECTURE FIRST **High-Level Data Flow:** Salesforce / PostgreSQL / Stripe / HubSpot / Google Analytics → **Ingestion** (API connectors + CDC replication + GA export, landing in S3) → **Staging** (raw Parquet, partitioned by date) → **Transformation** (Spark for heavy dedup/joins, dbt for business logic models) → **Warehouse** (Snowflake materialized views) → **Consumption** (Tableau, Finance reports, internal REST APIs). **Orchestration (Airflow):** Nightly master DAG. Expect roughly 5 ingestion tasks (one per source) → transformation tasks (Spark then dbt run) → 2 quality tasks (Great Expectations suite) → 2 publish tasks (refresh materialized views, invalidate API cache). On failure: alert channel, pipeline halts, exponential backoff retry. **CI/CD Layer (GitHub Actions):** This is new relative to the base template — expect dbt tests and SQL linting to run on every PR before merge. This is your safety net: a broken model should fail CI before it ever reaches the nightly DAG. **Data Quality Gates:** Row counts shouldn't drop >10% day-over-day per source. No nulls in customer/key IDs. Stripe revenue figures never negative. Salesforce lead IDs match expected format. Failed validation halts the pipeline for analyst review. **Key Concept — Partitioning:** Everything partitioned by `date`. A Spark job for `2026-07-05` only touches that day's data; Snowflake queries filtered by partition cut scan cost dramatically (unpartitioned scans on even moderate tables can turn a $5 query into a $500 one). **Incremental Loads:** First run processes full history; subsequent runs process only new/changed rows per source, tracked via a "last synced" watermark — this is what keeps a 5-source pipeline fast instead of re-processing everything nightly. --- # 📂 SECTION 3 — REPOSITORY NAVIGATION - **/ingestion** — `connectors/salesforce_connector.py`, `connectors/stripe_connector.py`, `connectors/hubspot_connector.py` (API polling, incremental sync, rate-limit handling), `connectors/postgres_replicator.py` (CDC off a read replica, never master), `connectors/ga_export.py` (Google Analytics data export API), `config/connections.yaml` (credentials via secrets manager — this is where you register the 5th source if one's added). - **/transformations** — `dbt_project/models/staging/` (per-source cleaning, deduplication, null handling), `dbt_project/models/marts/` (business-facing models — e.g. a `fct_revenue` blending Stripe + Salesforce, a `dim_customer` as single source of truth), `dbt_project/macros/` (shared SQL templates — high blast radius, treat as senior-review territory), `spark_jobs/` (heavy dedup/join logic that's cheaper to run distributed than in dbt/Snowflake directly). - **/orchestration** — `dags/daily_warehouse_load.py` (task order matters — wrong sequencing means stale data reaching Tableau). - **/quality** — `expectations/*.yml` (Great Expectations suites per source/model — failed checks halt + alert). - **/warehouse** — `views/*.sql` (materialized views joining upstream marts, indexed for BI query speed). - **/api** — internal REST endpoints querying Snowflake views, likely with a short-TTL cache layer. - **/docs** — look for a data lineage doc first; this answers "where did this metric come from?" faster than tracing code. - **.github/workflows/** — not in the original template but relevant here: this is where dbt CI, SQL linting, and test-on-PR logic lives. Read this in week 1 even though it's not listed in STRUCTURE — it gatekeeps every future PR you submit. --- # 📚 SECTION 4 — PERSONALIZED LEARNING PATH (5 weeks, 8 hrs/week) **Week 1 — SQL Fundamentals + Lineage (8 hrs):** Days 1-2: SQL basics refresh (SELECT/WHERE/JOIN/GROUP BY), read the data lineage doc, sketch the 5-source flow by hand. Days 3-4: window functions specifically (`ROW_NUMBER`, `SUM() OVER PARTITION BY`) since dbt marts lean on these — write 5 practice queries against a staging table. Day 5: read one full staging model SQL file end to end, annotate every clause you don't understand. **Week 2 — dbt + Spark Basics (8 hrs):** Days 1-2: dbt fundamentals — models, refs, `dbt run`, `dbt test`. Trace one staging model's compiled SQL. Days 3-4: Spark basics via local PySpark REPL — load a sample DataFrame, run `.dropDuplicates()`, understand why Spark handles the heavy joins instead of dbt. Day 5: modify one Spark job locally (add a filter), test on a sample, don't touch production config. **Week 3 — Orchestration + CI (8 hrs):** Days 1-2: Airflow DAG structure — trace the daily DAG task-by-task, note dependency order. Days 3-4: GitHub Actions — read the workflow file, understand what dbt CI checks on a PR, open a trivial PR (e.g. a doc fix) just to watch the pipeline run. Day 5: add a GE test locally and confirm it runs in CI. **Week 4 — Data Quality + Debugging (8 hrs):** Days 1-3: Great Expectations — write a new expectation (e.g. Stripe revenue never negative), test locally, add to CI. Days 4-5: debugging scenario — simulate a row-count drop, trace through Airflow logs → Snowflake counts → Spark output → dbt logs, in that order. **Week 5 — First Production Contribution (8 hrs):** Build one new staging or mart model end to end (e.g. a `stg_google_analytics_sessions` model), write its dbt tests + GE checks, open a PR, get it through CI, merge, and monitor the next nightly run through to Tableau. --- # 👨‍💻 SECTION 5 — HANDS-ON PRACTICE TASKS 🟢 **Beginner (Weeks 1-2):** 1. Write a read-only query: top 10 customers by revenue from the revenue mart. 2. Add row-count logging (before/after) to one Spark dedup job — no logic change. 3. Add YAML column descriptions to one staging model — documentation only. 🟡 **Intermediate (Weeks 3-4):** 4. Fix a data quality issue: filter null customer IDs before aggregation in one mart model. 5. Add an incremental filter to a Spark job so it only processes the last 24 hours. 6. Create a new staging model for Google Analytics sessions data, following the pattern of an existing staging model. 🔴 **Advanced (Week 5):** 7. Debug a simulated pipeline failure: dbt run fails, trace root cause across Airflow/Spark/Snowflake logs. 8. Optimize a slow Tableau-facing query by adding an index to a materialized view. 9. Ship a new metric end-to-end: design it, implement as a dbt model with tests, deploy, verify in Tableau. --- # 🧠 SECTION 6 — KNOWLEDGE DEPENDENCY MAP **Prerequisite Chain:** SQL fundamentals → Data lineage understanding → dbt model reading/writing → Spark basics → Airflow orchestration → GE validation → CI/CD flow → Production debugging → Independent contribution. **Risky Modules:** Ingestion connectors (🔴 — bugs cause duplication/data loss, always test on samples). dbt macros (🟠 — shared logic, changes ripple everywhere, needs senior review). Airflow DAG task ordering (🟠 — wrong sequence = stale downstream data). Snowflake materialized views (🟠 — cached by Tableau and internal APIs, test refresh in dev first). **Safe to Modify:** Staging dbt models (isolated blast radius). Read-only warehouse views. New GE checks (additive). Airflow task timeout/retry configs. Documentation. --- # 🚨 SECTION 7 — COMMON PITFALLS **Pitfall 1 — Partial Stripe Sync:** Connector times out mid-sync, only partial transaction data lands. Next day's revenue mart joins incomplete data with fresh data, understating revenue. *Prevention:* validate raw row count against Stripe API expected count. *Fix:* rerun with backfill for the missing window. **Pitfall 2 — Spark OOM on Join:** A dedup/join step across 5 sources exceeds executor memory, fails silently, pipeline halts with no new metrics. *Prevention:* partition by date, process incrementally. *Fix:* increase executor memory or reduce partition size. **Pitfall 3 — Window Function Off-By-One:** Using `ORDER BY` instead of pure `PARTITION BY` in a dedup query causes duplicate customer rows in `dim_customer`. *Prevention:* test dedup logic on a small sample before running against full data. *Fix:* verify with a `GROUP BY ... HAVING COUNT(*) > 1` check. **Debugging Shortcuts (in order):** Airflow UI task logs → raw row counts in Snowflake/S3 → Spark job logs (OOM/timeout?) → dbt logs (SQL error, zero rows?) → compare row counts stage-by-stage to isolate where data was lost. --- # 📈 SECTION 8 — MILESTONES - **End Week 1:** Can trace the 5-source data flow, run local SQL confidently. Readiness 15%. - **End Week 2:** Can read/modify a dbt model and a Spark job safely. Readiness 35%. - **End Week 3:** Understands DAG structure and CI gating, can open a passing PR. Readiness 50%. - **End Week 4:** Can write GE tests and debug a simulated failure independently. Readiness 70%. - **End Week 5:** Has shipped one production model end-to-end and monitored it live. Readiness 85% — independent contributor. --- # 🏆 FINAL REPORT | Category | Score | Notes | |---|---|---| | Architecture Clarity | 7/10 | 5-source flow is tractable to learn fully, unlike a 50-source shop | | Documentation Coverage | 6/10 | Assumed adequate; verify lineage doc exists and is current | | Learning Path Quality | 8/10 | Compressed to 5 weeks given 8 hrs/week and Python background | | Hands-on Tasks | 8/10 | Progressive blast-radius scaling well matched to SQL skill gap | | Debugging Support | 7/10 | GE + Airflow logs give good signal; CI adds an extra early check | **Top improvements to consider:** (1) Confirm a data lineage doc actually exists before week 1 — if not, build one first. (2) Set up a sandbox dbt target so early PRs don't risk touching shared dev schemas. (3) Since GitHub Actions CI isn't in most standard onboarding docs, write a short "what CI checks and why it failed" cheat sheet — this will save real time in weeks 3-4. Given SQL is your steepest gap, I'd start there even before day 1 of the plan above — want me to build out a focused week-0 SQL drill set (window functions, CTEs, incremental filter patterns) tailored to what dbt models actually use?