Git and Version Control for Data Projects

WORKING DIRECTORY          STAGING AREA             REPOSITORY
(what you edit)            (what will be committed)  (committed history)

orders_pipeline.py ──────── git add ──────────────► [staged snapshot]
                                                            │
                                                       git commit
                                                            │
                                                            ▼
                                                     [commit object]
                                                     in .git/objects/

git status         → shows difference between working dir and staging
git diff            → shows changes in working dir NOT yet staged
git diff --staged   → shows changes staged but NOT yet committed

The staging area (index) lets you craft commits precisely: you can stage
parts of a file, or stage some files but not others, choosing exactly
what goes into each commit.

# SETUP (once per machine):
git config --global user.name "Sarah Mitchell"
git config --global user.email "sarah@company.com"
git config --global init.defaultBranch main

# STARTING A PROJECT:
git init                                       # initialise new repo
git clone git@github.com:org/repo.git          # clone via SSH (preferred)
git clone https://github.com/org/repo.git      # clone via HTTPS

# CHECKING STATE:
git status                        # what has changed? what is staged?
git status -s                     # short format: M=modified, A=added, ?=untracked
git diff                          # unstaged changes (working dir vs staging)
git diff --staged                 # staged changes (staging vs last commit)
git diff main..feature-branch     # difference between two branches
git log --oneline -20             # last 20 commits, one line each
git log --oneline --graph --all   # visual branch graph
git show abc1234                  # show a specific commit's changes

# STAGING AND COMMITTING:
git add models/silver/orders.sql       # stage specific file
git add -p                             # interactive staging: choose hunks to stage
                                       # (the most powerful add option)
git commit -m "feat: add orders deduplication in Silver layer"
git commit --amend -m "fix: correct commit message"
# --amend: modify the LAST commit (only before pushing!)

# Conventional commit prefixes (industry standard):
# feat: new feature   fix: bug fix   refactor: no behaviour change
# test: test changes  docs: docs only   chore: maintenance   ci: CI config

# REMOTE OPERATIONS:
git remote -v                          # show configured remotes
git fetch origin                       # download remote changes, do NOT merge
git pull origin main                   # fetch + merge (or rebase if configured)
git pull --rebase origin main          # fetch + rebase (cleaner history)
git push origin feature/orders-dedup   # push branch to remote
git push -u origin feature/orders-dedup  # push and set upstream tracking

# CREATING AND SWITCHING:
git branch                             # list local branches (* = current)
git switch -c feature/customer-metrics # create AND switch (most common)

# Always branch from the latest main:
git switch main
git pull origin main
git switch -c feature/orders-backfill

# MERGING:
git switch main
git merge feature/orders-backfill          # merge feature into main
git merge --no-ff feature/orders-backfill  # force a merge commit (preserves history)
git merge --squash feature/orders-backfill # squash all commits into one

# REBASING — replays your commits on top of another branch (linear history):
git switch feature/orders-backfill
git rebase main          # replay feature commits on top of latest main
                         # if conflicts: fix, then git rebase --continue
                         # to abort:     git rebase --abort

# RULE: never rebase commits that have been pushed to a shared branch.
# Rebasing rewrites history — safe on your local feature branch, dangerous
# on main or any branch others have pulled.

# DELETING BRANCHES:
git branch -d feature/orders-backfill     # delete (safe — refuses if unmerged)
git push origin --delete feature/orders-backfill  # delete remote branch

# STASHING — save work in progress without committing:
git stash push -m "WIP: orders backfill logic"   # stash with a name
git stash list                    # list all stashes
git stash pop                     # apply most recent stash and remove it

1. MAIN IS ALWAYS DEPLOYABLE
   main branch = what is running in production right now
   Never commit directly to main — it is branch-protected
   Every push to main automatically deploys (via CI/CD)

2. CREATE A FEATURE BRANCH FOR EVERY CHANGE
   git switch main && git pull origin main
   git switch -c feat/add-customer-ltv-model
   # Branch naming: feat/..., fix/..., refactor/..., hotfix/...

3. MAKE SMALL, FOCUSED COMMITS
   git add models/gold/customer_ltv.sql
   git commit -m "feat: add customer lifetime value Gold model"
   git add tests/gold/customer_ltv.yml
   git commit -m "test: add not_null and positive_value tests for LTV"
   # Why small commits? Easier to review, easier to revert, easier to bisect.

4. PUSH AND OPEN A PULL REQUEST
   git push -u origin feat/add-customer-ltv-model
   # PR description should include: what changed and why, tests added,
   # how to verify the output, and any downstream impact.

5. CI RUNS AUTOMATICALLY ON PUSH
   # dbt compile (SQL is valid), dbt test on changed models
   # (data quality checks pass), sqlfluff lint (SQL style)
   # If CI fails, fix before requesting review

6. CODE REVIEW
   # At least one other data engineer checks: is the SQL logic correct?
   # Are edge cases (NULLs, duplicates) handled? Are tests comprehensive?
   # Is naming consistent with existing conventions?

7. SQUASH AND MERGE (or merge commit)
   # Most data teams squash feature branch commits into one:
   # "feat: add customer lifetime value Gold model (#47)"

8. DEPLOY
   # Merge to main → CI runs dbt run + dbt test in production
   # If tests fail in production → revert the merge immediately

# ── SECRETS — never commit these ──────────────────────────────────────────
.env                    # local environment variables (DB passwords, API keys)
.env.*                  # .env.local, .env.production, etc.
*.pem                   # SSH private keys
*.key                   # private keys
*_credentials.json      # GCP / AWS credential files
profiles.yml            # dbt profiles (contains DB connection strings!)
                        # EXCEPTION: profiles.yml.example (template, no real values)

# ── DATA FILES — data does not belong in git ──────────────────────────────
*.csv
*.parquet
*.json.gz
data/
raw/
output/
# EXCEPTION: small fixture/seed files used in tests (< 1 MB)
# Unignore with: !tests/fixtures/small_sample.csv

# ── GENERATED OUTPUTS — rebuilt by running the pipeline ───────────────────
target/                 # dbt compiled SQL and run artifacts
dbt_packages/           # dbt dependencies (like node_modules)
logs/
*.log

# ── PYTHON ──────────────────────────────────────────────────────────────
__pycache__/
*.py[cod]
.venv/
venv/
.pytest_cache/
.coverage
.mypy_cache/
.ruff_cache/

# ── JUPYTER NOTEBOOKS — output cells can contain data ─────────────────────
# Option 1: ignore all notebooks (*.ipynb)
# Option 2: commit notebooks but strip outputs first:
#   pip install nbstripout && nbstripout --install
.ipynb_checkpoints/

.DS_Store
Thumbs.db
.idea/
.vscode/settings.json   # personal settings (commit .vscode/extensions.json instead)

# ── AIRFLOW ──────────────────────────────────────────────────────────────
airflow.db              # local SQLite Airflow database
airflow-webserver.pid

# ── TERRAFORM ────────────────────────────────────────────────────────────
*.tfstate
.terraform/
*.tfvars                # may contain secrets

# ── CHECKING WHAT WOULD BE IGNORED ────────────────────────────────────────
git check-ignore -v filename                 # why is this file being ignored?
git ls-files --ignored --exclude-standard    # list all ignored files

# SITUATION: you committed a .env file with a real API key

# STEP 1: Immediately rotate/revoke the secret
# Go to Stripe/AWS/GCP console and revoke the leaked key RIGHT NOW.
# Rotation takes 2 minutes; remediation takes 2 hours — do it first.

# STEP 2: Remove from history
pip install git-filter-repo
git filter-repo --path .env --invert-paths
# This rewrites ALL history, removing .env from every commit
# (BFG Repo Cleaner is a faster alternative for large repos)

# STEP 3: Force push (coordinate with team first!)
git push origin --force --all
git push origin --force --tags

# STEP 4: Notify all collaborators — everyone who cloned the repo must
# re-clone, since their local .git/objects still has the secret.

# PREVENT RECURRENCE:
# 1. Add .env to .gitignore (and commit the .gitignore)
# 2. Add a pre-commit secret scanner:
#    pip install detect-secrets
#    detect-secrets scan > .secrets.baseline
# 3. Enable GitHub secret scanning in repo settings
# 4. Use git-secrets or gitleaks in pre-commit hooks

git lfs install                      # enable LFS (once per machine)

git lfs track "*.parquet"            # track all .parquet files with LFS
git lfs track "*.pkl"                # track model pickle files

# The above commands update .gitattributes:
cat .gitattributes
# *.parquet filter=lfs diff=lfs merge=lfs -text

# IMPORTANT: commit .gitattributes to the repo
git add .gitattributes
git commit -m "chore: configure Git LFS for binary files"

# After tracking is configured, git add/commit works normally:
git add tests/fixtures/sample_orders.parquet
git commit -m "test: add 50k row sample fixture for integration tests"
git push origin main
# → LFS stores the large file on the LFS server
# → Git history contains only a 134-byte pointer file

git lfs ls-files           # list files currently managed by LFS
git lfs status             # LFS status of working directory

# LFS LIMITS: GitHub Free gives 1 GB LFS storage + 1 GB bandwidth/month.
# For data engineering: LFS is for files under ~500 MB.
# Anything larger → object storage (S3/ADLS) + reference by URL.

# WHAT NEVER USES LFS (goes to object storage instead):
# Production data files (terabytes of Parquet), pipeline outputs,
# archived historical data, ML training datasets.
# → Reference these in your pipeline config as S3/ADLS paths —
#   never check the files themselves into git.

freshcart_dbt/                    # git repository root
├── .gitignore                    # includes target/, dbt_packages/, logs/
├── .github/workflows/
│   ├── ci.yml                    # run dbt compile + test on PR
│   └── deploy.yml                # run dbt run + test on merge to main
├── dbt_project.yml               # project config (committed)
├── profiles.yml.example          # TEMPLATE — no real credentials (committed)
│                                 # actual profiles.yml is gitignored
├── models/
│   ├── staging/                  # stg_ models: raw → typed
│   ├── intermediate/             # int_ models: business logic
│   ├── marts/
│   │   ├── core/dim_customers.sql
│   │   └── finance/fct_orders.sql
│   └── _schema.yml               # model documentation + tests (committed)
├── seeds/                        # small reference CSVs (committed — these are code)
│   └── store_mapping.csv         # 10-row mapping table, fine in git
└── snapshots/                    # SCD2 snapshot definitions

# WHAT IS gitignored: target/, dbt_packages/, logs/, profiles.yml

# Step 1: Branch from latest main
git switch main && git pull origin main
git switch -c feat/customer-ltv-gold-model

# Step 2: Create the model — models/marts/finance/fct_customer_ltv.sql

# Step 3: Add schema.yml entry with tests
# models:
#   - name: fct_customer_ltv
#     columns:
#       - name: customer_id
#         tests: [not_null, unique]
#       - name: total_revenue
#         tests: [not_null, {dbt_utils.accepted_range: {min_value: 0}}]

# Step 4: Test locally before committing
dbt compile -s fct_customer_ltv              # check SQL compiles
dbt run -s fct_customer_ltv --target dev     # run against dev database
dbt test -s fct_customer_ltv --target dev    # run data quality tests

# Step 5: Commit with a clear message
git add models/marts/finance/fct_customer_ltv.sql models/marts/finance/_schema.yml
git commit -m "feat: add customer lifetime value fact model

- Aggregates total revenue, order count, and first/last order date
- Used by Finance dashboard LTV widget"

# Step 6: Push and open PR
git push -u origin feat/customer-ltv-gold-model

# Step 7: CI runs automatically (dbt compile + test)

# Step 8: Reviewer checks:
# - Does the SQL handle NULLs correctly?
# - Are there tests for the new columns?
# - Does it join to the correct Silver tables?
# - Does dbt docs show correct lineage?

# Step 9: Merge and deploy
# Squash and merge → triggers deploy.yml → dbt run + test in production

# Step 10: Verify in production
dbt run -s fct_customer_ltv --target prod
dbt test -s fct_customer_ltv --target prod

# BREAKING CHANGE: orders.total_amount is used in 12 downstream models.
# Renaming it directly breaks all 12 at once. Instead:

# Step 1: add the new column alongside the old one
#   SELECT order_amount AS order_revenue,   -- new name
#          order_amount AS total_amount,    -- OLD name kept for transition
git commit -m "feat: add order_revenue column (deprecating total_amount)"

# Step 2: announce deprecation in schema.yml
#   - name: total_amount
#     description: "DEPRECATED — use order_revenue instead. Removed 2026-04-01."

# Step 3: update all 12 downstream models, each in its own reviewed PR

# Step 4: once every consumer is updated, remove the old column
git commit -m "breaking: remove deprecated total_amount column from fct_orders

All downstream models have been updated to use order_revenue.
Verified in production on 2026-03-31."

# WHY GIT MAKES THIS SAFE: each step is a separate, revertible commit —
# the transition is visible in history, and any step can be rolled back
# independently with git revert.

name: dbt CI
on:
  pull_request:
    branches: [main]
    paths: ['models/**', 'tests/**', 'macros/**', 'dbt_project.yml']

jobs:
  dbt-ci:
    runs-on: ubuntu-latest
    timeout-minutes: 30
    steps:
      - name: Checkout code
        uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'
      - name: Install dependencies
        run: |
          pip install dbt-snowflake==1.8.0
          dbt deps

      - name: Write dbt profiles
        run: |
          mkdir -p ~/.dbt
          cat > ~/.dbt/profiles.yml << 'PROFILES'
          freshcart:
            target: ci
            outputs:
              ci:
                type: snowflake
                account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
                user: ${{ secrets.SNOWFLAKE_CI_USER }}
                password: ${{ secrets.SNOWFLAKE_CI_PASSWORD }}
                database: FRESHCART_CI
                schema: dbt_ci_${{ github.run_id }}
          PROFILES
        # Each CI run gets its own schema — deleted at end of job

      - name: dbt compile
        run: dbt compile --target ci
        # Catches SQL syntax errors before running anything

      - name: dbt run (changed models only)
        run: dbt run --target ci --select state:modified+ --defer --state ./prod-manifest
        # state:modified+: only run models that changed and their downstream deps
        # --defer: use production results for unmodified upstream models

      - name: dbt test (changed models only)
        run: dbt test --target ci --select state:modified+ --defer --state ./prod-manifest

      - name: Cleanup CI schema
        if: always()   # run even if previous steps failed
        run: dbt run-operation drop_schema --args '{schema: dbt_ci_${{ github.run_id }}}'

name: dbt Deploy
on:
  push:
    branches: [main]

jobs:
  dbt-deploy:
    runs-on: ubuntu-latest
    environment: production   # requires manual approval in GitHub settings
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - run: |
          pip install dbt-snowflake==1.8.0
          dbt deps
      - name: Write production profiles
        run: |
          mkdir -p ~/.dbt
          cat > ~/.dbt/profiles.yml << 'PROFILES'
          freshcart:
            target: prod
            outputs:
              prod:
                type: snowflake
                account: ${{ secrets.SNOWFLAKE_ACCOUNT }}
                user: ${{ secrets.SNOWFLAKE_PROD_USER }}
                password: ${{ secrets.SNOWFLAKE_PROD_PASSWORD }}
                database: FRESHCART_PROD
          PROFILES

      - name: dbt run
        run: dbt run --target prod
      - name: dbt test
        run: dbt test --target prod

      - name: Notify on failure
        if: failure()
        run: |
          curl -s -X POST ${{ secrets.SLACK_WEBHOOK }} \
            -H 'Content-type: application/json' \
            -d '{"text": ":red_circle: dbt deploy failed on main — check GitHub Actions"}'

      - name: Generate and upload docs
        if: success()
        run: dbt docs generate --target prod

name: Pipeline Tests
on:
  pull_request:
    paths: ['pipelines/**', 'tests/**', 'requirements*.txt']

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - name: Cache pip dependencies
        uses: actions/cache@v4
        with:
          path: ~/.cache/pip
          key: ${{ runner.os }}-pip-${{ hashFiles('requirements.txt') }}
      - run: pip install -r requirements.txt -r requirements-dev.txt
      - name: Lint and type-check
        run: |
          ruff check pipelines/ tests/
          mypy pipelines/ --ignore-missing-imports
      - name: Run unit tests with coverage
        run: pytest tests/unit/ --cov=pipelines --cov-report=xml --cov-fail-under=80 -v

git revert abc1234            # create a new commit that reverses abc1234
git revert HEAD               # revert the most recent commit

# Example: a bad model was deployed to production
# git log --oneline shows:
# f8a3b2c  feat: update customer LTV formula ← this is wrong
# abc1234  feat: add store revenue model

git revert f8a3b2c            # creates: "Revert feat: update customer LTV formula"
git push origin main          # safe to push — history is intact

# NEVER use on a shared branch that others have pulled.
git reset --soft HEAD~1       # undo last commit, KEEP changes staged
                              # use to: re-commit with a different message

git reset --mixed HEAD~1      # undo last commit, KEEP changes unstaged (default)

git reset --hard HEAD~1       # undo last commit, DISCARD all changes
                              # DESTRUCTIVE: changes are gone

git restore orders.sql           # discard all unstaged changes to orders.sql
git restore --staged orders.sql  # unstage a file (keep changes in working dir)

# DECISION TREE:
# Has the commit been pushed to a shared branch?
#   YES → git revert (adds new commit, history preserved)
#   NO  → git reset --soft/--mixed/--hard, depending on what to keep
# Just want to discard file changes (not commits)? → git restore filename
# Accidentally staged a file? → git restore --staged filename

git reflog                    # show all recent HEAD positions
# f8a3b2c (HEAD -> main) HEAD@{0}: commit: feat: update LTV formula
# 9f8e7d6 HEAD@{2}: reset: moving to HEAD~1   ← you reset here
# 1b2c3d4 HEAD@{3}: commit: fix: correct NULL handling  ← this was "lost"

# Recover the "lost" commit after a reset --hard:
git reset --hard 1b2c3d4      # go back to the state before the reset
# or: git checkout -b recovery-branch 1b2c3d4

# reflog entries expire after 90 days by default

# SCENARIO: a critical bug fix was committed on feature/orders-fix but
# needs to be deployed to main NOW without waiting for the full PR

git switch main
git cherry-pick abc1234        # apply commit abc1234 to main
git push origin main           # deploy the fix

git cherry-pick --no-commit abc1234   # applies changes but does not commit
git status                            # review what was applied
git commit -m "hotfix: cherry-pick orders fix from feature branch"

COMMON RECOVERY SCENARIOS:

"I committed to main instead of my feature branch"
  git reset --soft HEAD~1 && git switch -c fix/my-feature && git commit -m "..."

"I pushed a broken commit to main and need to revert urgently"
  git revert bad_commit_hash && git push origin main

"I accidentally deleted a branch"
  git reflog | grep feat/deleted-branch
  git checkout -b feat/deleted-branch recovered_hash

Title: feat: add daily store revenue Gold model (#47)

## What
Adds gold.daily_store_revenue — aggregates delivered order revenue per
store per day, with 7-day moving averages and month-to-date totals.

## Why
Powers the FreshCart Revenue Dashboard. Currently a direct 4-minute
Snowflake query — this pre-aggregated model reduces it to <1 second.

## Changes
- models/marts/finance/daily_store_revenue.sql (new)
- models/marts/finance/_schema.yml (updated — new model + tests)

## How to verify
dbt run -s daily_store_revenue --target dev
SELECT * FROM dev.daily_store_revenue WHERE order_date = '2026-03-17' LIMIT 10
Expected: 10 rows (one per store), all revenue values > 0

## Downstream impact
The Revenue Dashboard will use this model once deployed. No existing
models reference it.

1. CORRECTNESS
   Does the SQL logic match the description? Are NULLs handled explicitly?
   Could the JOINs produce duplicates? Are edge cases handled?

2. PERFORMANCE
   Does it filter early (before JOINs)? Correlated subqueries that should
   be JOINs? For Snowflake: does it filter on the clustering key?

3. TESTS
   not_null on required columns? unique on grain columns? relationship
   tests for FK columns?

4. NAMING AND CONVENTIONS
   snake_case columns? Correct model prefix (stg_/int_/fct_/dim_)?
   Schema.yml documentation for all new columns?

git merge feature/orders-fix
# CONFLICT (content): Merge conflict in models/silver/orders.sql

# In the conflicted file:
#  WITH base AS (SELECT * FROM raw.orders
# <<<<<<< HEAD (main branch version)
#      WHERE status IN ('placed', 'confirmed', 'delivered', 'cancelled')
# =======
#      WHERE status IN ('placed', 'confirmed', 'delivered', 'cancelled', 'refunded')
# >>>>>>> feature/orders-fix (incoming branch version)
#  )

# <<<<<<< HEAD: your current branch's version. =======: separator.
# >>>>>>> branch: the incoming branch's version.
# Choose which to keep (or write a new version combining both), then:
git add models/silver/orders.sql    # mark as resolved
git commit                          # complete the merge

git mergetool                       # opens a configured visual merge tool
git merge --abort                   # abandon the merge, go back to pre-merge state

# PREVENTING CONFLICTS:
# 1. Keep feature branches short-lived (< 1 week)
# 2. Pull and rebase frequently: git pull --rebase origin main
# 3. Communicate if two people need the same file
# 4. One dbt model per file — conflicts are per-file, so isolated files
#    mean isolated changes

A colleague merged a PR at 8:55 AM that refactored the fct_orders model. The merge triggered the deploy CI which ran dbt — all tests passed. At 9:15 AM the finance team calls: “The revenue dashboard shows zero for March 17th. Something is wrong.”

Step 1 — identify the bad commit

git log --oneline -5
# f8a3b2c  (HEAD -> main) refactor: simplify fct_orders CTE chain (#52)
# abc1234  feat: add store_tier dimension (#51)

git show f8a3b2c -- models/marts/finance/fct_orders.sql
# Shows the diff — you spot it immediately:
# -  WHERE o.status = 'delivered'
# +  WHERE o.status = 'complete'
# The status value was changed to 'complete', which does not exist —
# zero rows match.

Steps 2-6 — revert, deploy, verify, and fix properly

# Step 2: Revert immediately (do NOT reset — this is shared main)
git revert f8a3b2c --no-edit
# Creates: Revert "refactor: simplify fct_orders CTE chain (#52)"

# Step 3: Push the revert — this triggers another deploy
git push origin main

# Step 4: Monitor CI — deploy runs dbt run + test, tests pass, deploy succeeds

# Step 5: Verify the dashboard recovered
# SELECT COUNT(*) FROM prod.fct_orders WHERE order_date = '2026-03-17'
# Returns: 48,234 rows ← correct

# Step 6: Fix the original PR properly — correct the WHERE clause and add
# a recency test that would have caught this:
#   - dbt_utils.recency: {datepart: day, field: order_date, interval: 1}
# Re-open the PR with the fix.

Total time from alarm to recovery: 8 minutes. The revert was safe because
it added a new commit rather than rewriting history — CI/CD could
immediately redeploy it just like any other push to main. A git reset
would have required a force push, coordination with everyone who had
pulled main, and risked confusing CI about what state to deploy.

The lesson: git revert is the production recovery tool. It is the only undo command that is safe on a shared branch and plays nicely with CI/CD pipelines. Know this before you need it at 9 AM.