Storing And Rotating Secrets Safely Basics

Why this matters for Analytics Engineers

Analytics Engineers handle credentials for warehouses, BI tools, ELT/ETL jobs, and automation. A single leaked secret can expose customer data, break pipelines, or cause downtime. This lesson shows how to keep secrets out of Git, set up safe local workflows, and rotate credentials quickly when something leaks.

• dbt profiles, warehouse passwords, and service tokens must never be committed.
• CI/CD often needs secrets; you pass them securely, not via repository files.
• Rotation keeps access safe after incidents or team changes.

Concept explained simply

A secret is any value that grants access: API keys, passwords, tokens, private keys, connection strings. Git is permanent and shareable; if a secret enters Git history, assume it's leaked.

Mental model

Think of secrets like physical keys. Keys live on your keyring (environment/secret manager), not glued to your project notebook (the repo). If you lose a key, you change the lock (rotate) and issue a new key.

• Keep secrets out of Git. Store placeholders/templates in Git.
• Load real values at runtime (env vars/secret manager).
• Rotate promptly after any exposure or scheduled interval.

Minimal safe setup (local)

1. Create a local .env with real values, and a committed .env.example with placeholders:

# .env (do NOT commit)
DB_USER=analytics_ro
DB_PASSWORD=supersecret
DB_HOST=warehouse.local

# .env.example (commit this)
DB_USER=YOUR_DB_USER
DB_PASSWORD=YOUR_DB_PASSWORD
DB_HOST=YOUR_DB_HOST

2. Ignore local secret files:

# .gitignore entries
.env
.env.*
!.env.example

3. Commit safe files only:

git add .gitignore .env.example
git rm --cached .env 2>/dev/null || true
git commit -m "chore(security): ignore .env and add template"

4. Load secrets at runtime via your tool or shell (example shown as concept):

# Example (shell): export vars from .env for local run
set -a; [ -f ./.env ] && . ./.env; set +a

# Then run your tool (example)
dbt debug  # it will read env vars via your profiles config

Result: collaborators can run the project using their own local .env, with no secrets stored in Git.

Worked examples

Example 1 — You accidentally committed .env

1. Immediately rotate the exposed secret (create a new token/password, plan to revoke old one).
2. Remove the file from the index and commit the fix:

git rm --cached .env
printf ".env\n.env.*\n!.env.example\n" >> .gitignore
git commit -m "chore(security): stop tracking .env and update .gitignore"

3. Force everyone to use their own local .env. Keep the rotated secret active only after your code/config uses it, then revoke old secret.

Example 2 — Rotating a dbt profile password

1. Generate new DB password in your warehouse (new_secret).
2. Add new_secret to your local environment (.env) and CI secret store.
3. Deploy config change (no secrets in code). Verify with a connection test.
4. Revoke the old password. Monitor logs for failures.

Example 3 — Removing a secret from the last commit

1. Revoke/rotate the secret first.
2. Undo the last commit, keep changes staged:

git reset --soft HEAD~1

3. Unstage/remove the secret file, add safe files only, recommit:

git rm --cached .env
git add .gitignore .env.example other_safe_files
git commit -m "fix(security): remove secret from commit and add ignore rules"

Push the corrected commit. Coordinate with collaborators if they already pulled the bad commit.

Rotation playbook (use this when a secret might be exposed)

Tools and choices (conceptual)

• Environment variables: simplest local approach; pair with .env ignored by Git.
• CI/CD secret store: define secrets in pipeline settings; referenced as environment variables.
• OS keychain/credential store: keep values off disk where possible.
• Encrypted files in Git: possible but risky for basics; key management becomes complex. Prefer secret managers unless you fully understand the implications.
• Scanning: use pre-commit or CI scanners to catch accidental secrets before merge.

Team conventions to adopt

• Never commit real secrets. Commit .env.example with placeholders.
• Use clear commit messages for security work: chore(security) or fix(security).
• Review PRs for secret exposure (humans + scanners).
• Rotate on schedule (e.g., quarterly) and after any suspected leak.
• Document where secrets live and who to contact for rotation.

Exercises

Complete Exercise 1 below. If logged in, your progress on the quick test is saved; exercises are available to everyone.

Common mistakes and how to self-check

• Mistake: Relying on .gitignore after a secret was already committed. Self-check: run git log -p on the offending path; if present in history, rotate and consider rewriting history.
• Mistake: Committing real values in .env.example. Self-check: search for DB_PASSWORD= or API_KEY= patterns; examples must be placeholders.
• Mistake: Rotating without updating all consumers. Self-check: list environments (local, CI, prod) and confirm each has the new secret.
• Mistake: Leaving old secrets active. Self-check: ensure old token is revoked/disabled and cannot be used.

Mini challenge

Your teammate pushed a commit containing a warehouse password 15 minutes ago. Draft a 5-step action note you would post in the team channel to coordinate rotation and cleanup. Include: immediate rotation, code/config update, verification, revocation, and a follow-up checklist.

Who this is for

• Analytics Engineers and BI Developers who manage data pipelines and credentials.
• Anyone pushing to Git who may handle API keys or database access.

Prerequisites

• Basic Git fluency: add, commit, status, log.
• Ability to run shell commands locally.
• Familiarity with environment variables.

Learning path

• Before: Git basics, branching, and PRs.
• Now: Storing and rotating secrets safely (this lesson).
• Next: CI/CD with secret stores; pre-commit secret scanning; incident response drills.

Practical projects

• Create a small dbt or SQL project with .env.example, run locally using env vars only.
• Set up a pre-commit hook that blocks accidental secret patterns.
• Write a team rotation checklist and test it with a dummy token.

Next steps

• Adopt .env.example and .gitignore in all repos.
• Introduce a simple secret scanning step in your workflow.
• Schedule periodic rotations and document ownership.

FAQ

About progress

The quick test and exercises are available to everyone. If you are logged in, your test progress is saved so you can return and continue.