Before the First Pipeline: How I Set Up a Data Engineering Project From Zero: Jimmy Okoth

Most tutorials skip this part. They start with a clean database that already exists, a Python environment that already works, and a connection that never fails. Then you sit down to build something real and lose an entire evening to command not found.

This post is the evening I lost, written down so I never lose it again.

I'm building the Newcomer Credit Data Platform, a data platform that turns messy financial records into fair, point-in-time affordability features for people who've moved to a new country and have little or no local credit history. You can follow the code on GitHub. Before I could write a single pipeline, I needed a machine and a project that could support one.

This guide does two jobs. For anyone reading, it shows how I set up a data engineering project and why each decision was made. For me, it's a playbook. On my next project, I want to open this page and go from an empty folder to a working, tested project base without guessing.

My machine: MacBook Air M2. Mac instructions come first in each step, with links for Windows and Linux.

What I ended up with

By the end, one command proved the whole setup worked:

$ make check
[OK  ] Python 3.12
[OK  ] Virtual environment active
[OK  ] Library 'numpy' installed
...
[OK  ] .env is ignored by Git
[OK  ] Connected to PostgreSQL 17.11 (Homebrew)
[OK  ] Schema 'raw' exists
[OK  ] Schema 'staging' exists
[OK  ] Schema 'intermediate' exists
[OK  ] Schema 'marts' exists

All checks passed. Your machine is ready.

Behind that output sits a local PostgreSQL database with a dedicated project user, a four-layer warehouse structure, an isolated Python environment, a dbt connection, and a Git repository that keeps secrets out of version control.

The stack: Python 3.12, PostgreSQL 17, dbt Core, Make, Git and GitHub.

Two rules before touching anything

I learned both of these the hard way, so they go first.

Rule 1: Read the end of the prompt before typing. The Terminal can host different programs, and each speaks a different language. When the prompt ends in %, I'm in zsh, the Mac command line, which understands commands like make, git and cd. When it ends in =# or =>, I'm inside psql, which understands SQL. Typing SQL at a % prompt produces a pile of confusing errors. I did exactly that, and it's in the errors section below.

Rule 2: One command at a time, and never paste the prompt. I type or paste only the command itself, press Enter, and wait for the prompt to come back before typing the next one. A command is the text after the %. Copying old Terminal output, including the jimmyokoth@Jimmys-MacBook-Air ... % part, makes zsh try to run my own username as a command.

The project map

This is the structure the setup produces. Every later step refers back to it.

newcomer-credit-data-platform/
├── .env                  # my secrets (never committed)
├── .env.example          # a blank template of .env (committed)
├── .gitignore            # what Git must never track
├── LICENSE               # MIT
├── Makefile              # short names for long commands
├── README.md
├── requirements.txt      # Python libraries for this project
├── sql/
│   ├── 00_bootstrap.sql  # creates the database user and database
│   └── 01_schemas.sql    # creates the four warehouse layers
├── scripts/
│   └── check_setup.py    # proves the machine is ready
├── dbt/
│   ├── dbt_project.yml   # describes the dbt project
│   ├── profiles.yml      # tells dbt how to connect (no secrets inside)
│   ├── models/  tests/  macros/  seeds/
├── generator/  ingestion/  orchestration/  api/
├── tests/  docs/  features/
└── data/labelled/

Empty folders each contain a file called .gitkeep, for a reason I explain in Step 6.

Step 1: Apple's developer tools

xcode-select --install

A window pops up. I clicked Install and waited. This installs Git, Make and a compiler that some Python libraries need during installation.

Check:

git --version
make --version

Windows: I'd use WSL, which runs a real Linux system inside Windows, then follow the Linux notes in this guide. Setup: Install WSL. Linux (Ubuntu/Debian): sudo apt update && sudo apt install -y git make build-essential

Lesson: Most software depends on system tools you never think about. When an install fails with a cryptic compiler error, a missing system dependency is the first suspect.

Step 2: Homebrew, the package manager

Homebrew is an app store for developer tools. The install command comes from brew.sh:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

When it finished, it printed "Next steps" with commands to add Homebrew to my PATH. On Apple Silicon they look like this:

echo >> ~/.zprofile
echo 'eval "$(/opt/homebrew/bin/brew shellenv)"' >> ~/.zprofile
eval "$(/opt/homebrew/bin/brew shellenv)"

Check:

brew --version

PATH, explained once properly: PATH is a list of folders the shell searches when I type a command. If a program is installed in a folder that isn't on that list, the shell reports command not found, even though the program exists. That's what the lines above fix for Homebrew.

Windows/Linux: No Homebrew needed. Linux uses apt (or your distribution's package manager), and WSL behaves the same way.

Lesson: "Command not found" right after an install is almost always a PATH problem, not a failed install.

Set my identity once per machine:

git config --global user.name "Jimmy Okoth"
git config --global user.email "my-github-email@example.com"
git config --global init.defaultBranch main

brew install gh
gh auth login

I chose GitHub.com, then HTTPS, then Yes to authenticate Git, then "Login with a web browser."

Create the repository on GitHub. On the "New repository" page I set three things:

Add .gitignore: I picked Python from the dropdown. It covers virtual environments, Python caches and .env, but not the project-specific folders. I add those in Step 6.
Add a license: MIT License. Without a license, nobody can legally reuse the code, and MIT is the standard choice for portfolio projects.
Add a README: yes.

Clone it:

mkdir -p ~/projects
cd ~/projects
gh repo clone themanbuildingpipelines/newcomer-credit-data-platform
cd newcomer-credit-data-platform

Check:

gh auth status
git status

Windows/Linux: Install Git from git-scm.com and the GitHub CLI using the official instructions.

Lesson: Commit history is public evidence of how I work. Same name and email everywhere, so every contribution links to my profile.

Step 4: Python 3.12

brew install python@3.12
python3.12 --version

I pin a specific version because data tools like dbt sometimes lag behind the newest Python. Pinning avoids surprises.

Windows/Linux: python.org downloads. On Ubuntu, Python 3.12 is available through apt install python3.12 python3.12-venv, depending on the release.

Lesson: "It works on my machine" doesn't count when a pipeline runs on a server at 3am. Pinned versions are the first step toward reproducibility.

Step 5: PostgreSQL 17

brew install postgresql@17
echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc
brew services start postgresql@17

Homebrew marks versioned PostgreSQL as keg-only, meaning it isn't added to PATH automatically, so several versions can live side by side. The export PATH line adds it myself. That's PATH lesson number two.

brew services start runs PostgreSQL in the background and restarts it whenever the Mac boots.

Check:

psql --version
brew services list
psql postgres -c "SELECT version();"

What I needed to understand here: PostgreSQL is a server, a program running in the background, like music playing with the app closed. psql is a separate program I use to talk to it, like a phone I use to call it. Homebrew also created a superuser named after my Mac username. That superuser holds the master key to everything.

Windows: PostgreSQL for Windows, or install inside WSL using the Linux instructions. Linux: PostgreSQL for Ubuntu. One difference matters: on Linux the superuser is called postgres, not your username. To make this guide's commands work unchanged, create a superuser matching your login once:

sudo -u postgres createuser --superuser $USER

Lesson: A database is a server I connect to, not a file I open. "Connection refused" means the server isn't running or I'm knocking on the wrong host or port. That diagnosis works for every database.

Step 6: The project files

This is where the project takes shape. Each file below shows exactly where it goes.

Folders, and why empty ones need .gitkeep

From the project root:

mkdir -p sql scripts dbt/models dbt/tests dbt/macros dbt/seeds \
         generator ingestion orchestration api tests docs features data/labelled

for d in dbt/models dbt/tests dbt/macros dbt/seeds generator ingestion \
         orchestration api tests docs features data/labelled; do
  touch "$d/.gitkeep"
done

Git doesn't track empty folders, only files. An empty placeholder file called .gitkeep makes each folder exist for anyone who clones the repo. It's a naming convention, not a Git feature.

.gitignore additions (project root)

GitHub's Python template doesn't know about dbt, Dagster, generated data or Mac system files. I append these rules to the end of the existing file:

cat >> .gitignore <<'EOF'

# ---- Project additions ----
dbt/target/
dbt/dbt_packages/
dbt/logs/
logs/
.dagster/
dagster_home/
.tmp_dagster_home*/
data/raw/
data/generated/
data/public/
*.parquet
.DS_Store
.vscode/
EOF

>> adds to the end of a file. A single > would wipe the file and replace it, which is a mistake worth avoiding once and remembering forever.

I ignore the generated data folders rather than every .csv, because data/labelled/ will hold hand-labelled data that can't be regenerated and must be committed.

requirements.txt (project root)

numpy>=2.0
pandas>=2.2
psycopg[binary]>=3.2
python-dotenv>=1.0
pytest>=8.0
dbt-postgres>=1.9

.env.example (project root)

# Copy this file to .env and fill in the password:  cp .env.example .env
# .env is ignored by Git. This example file IS committed, with no real secrets.
PGHOST=localhost
PGPORT=5432
PGDATABASE=newcomer_credit
PGUSER=ncp_dev
PGPASSWORD=

The variable names aren't random. PGHOST, PGPORT, PGDATABASE, PGUSER and PGPASSWORD are PostgreSQL's standard environment variables. psql, Python's psycopg and many other tools read them automatically, so I define the connection once and everything uses it.

Makefile (project root)

The indented lines must start with a Tab character, not spaces. If you copy this from a web page and your editor converts tabs to spaces, Make fails with missing separator. After pasting, I check with cat -t Makefile: indented lines should begin with ^I, which is how a Tab displays.

# Short names for the commands you'll run every day.
# IMPORTANT: every indented line below must start with a TAB, not spaces.

# Load settings from .env and pass them to every command
ifneq (,$(wildcard .env))
include .env
export
endif

# Your Mac username is the database superuser Homebrew created
SUPERUSER ?= $(USER)
PYTHON := .venv/bin/python
PIP    := .venv/bin/pip

.PHONY: setup db schemas check dbt-debug clean

setup:            ## create the virtual environment and install libraries
	python3.12 -m venv .venv
	$(PIP) install --upgrade pip
	$(PIP) install -r requirements.txt

db:               ## create the project role and database (run once)
	@echo "Creating role and database as $(SUPERUSER)..."
	@PGUSER=$(SUPERUSER) PGDATABASE=postgres psql -v ON_ERROR_STOP=1 -v app_password="$(PGPASSWORD)" -f sql/00_bootstrap.sql

schemas:          ## create the four warehouse layers
	psql -v ON_ERROR_STOP=1 -f sql/01_schemas.sql

check:            ## confirm everything is ready
	$(PYTHON) scripts/check_setup.py

dbt-debug:        ## confirm dbt can reach the database
	cd dbt && ../.venv/bin/dbt debug

clean:            ## remove build output
	rm -rf dbt/target dbt/logs logs

What each target does:

Command	What it does
`make setup`	Creates `.venv` with Python 3.12 and installs the libraries
`make db`	Creates the project user and database (once)
`make schemas`	Creates the four warehouse layers
`make check`	Runs the setup check script
`make dbt-debug`	Tests dbt's database connection
`make clean`	Deletes build output

Two details matter. The block at the top loads .env and passes every setting to each command. And the @ before the psql line in db stops Make from printing that command, because the command contains the database password. Without it, the password appears on screen and in any saved logs.

sql/00_bootstrap.sql

-- 00_bootstrap.sql
-- Creates the project's login role and database.
-- Run ONCE as your Mac superuser:
--   psql -d postgres -v app_password="$PGPASSWORD" -f sql/00_bootstrap.sql
-- Safe to re-run: each statement only fires if the thing doesn't exist yet.

SELECT format('CREATE ROLE ncp_dev LOGIN PASSWORD %L', :'app_password')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ncp_dev')
\gexec

SELECT 'CREATE DATABASE newcomer_credit OWNER ncp_dev'
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'newcomer_credit')
\gexec

sql/01_schemas.sql

-- 01_schemas.sql
-- The four layers of the warehouse. Run as ncp_dev inside newcomer_credit.
-- IF NOT EXISTS makes this safe to run any number of times.

CREATE SCHEMA IF NOT EXISTS raw;           -- data exactly as received, never edited
CREATE SCHEMA IF NOT EXISTS staging;       -- cleaned and typed
CREATE SCHEMA IF NOT EXISTS intermediate;  -- business logic building blocks
CREATE SCHEMA IF NOT EXISTS marts;         -- final outputs: features, reports

scripts/check_setup.py

"""
check_setup.py: confirms your machine is ready to build the project.

Run it with:  make check   (or: python scripts/check_setup.py)
Every line should show OK. Anything marked FAIL tells you what to fix.
"""
import importlib
import os
import subprocess
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
results = []


def check(name, passed, fix=""):
    results.append(passed)
    status = "OK  " if passed else "FAIL"
    print(f"[{status}] {name}")
    if not passed and fix:
        print(f"       fix: {fix}")


# 1. Python version
check(
    f"Python {sys.version_info.major}.{sys.version_info.minor}",
    sys.version_info >= (3, 11),
    "install Python 3.12 with: brew install python@3.12",
)

# 2. Running inside the project's virtual environment
in_venv = sys.prefix != sys.base_prefix
check("Virtual environment active", in_venv, "run: source .venv/bin/activate")

# 3. Required libraries installed
for pkg in ["numpy", "pandas", "psycopg", "dotenv", "pytest"]:
    try:
        importlib.import_module(pkg)
        check(f"Library '{pkg}' installed", True)
    except ImportError:
        check(f"Library '{pkg}' installed", False, "run: pip install -r requirements.txt")

# 4. .env exists and holds the settings we need
from dotenv import load_dotenv  # noqa: E402

env_file = ROOT / ".env"
check(".env file exists", env_file.exists(), "run: cp .env.example .env  then fill it in")
load_dotenv(env_file)
for var in ["PGHOST", "PGPORT", "PGDATABASE", "PGUSER", "PGPASSWORD"]:
    check(f"Setting {var} present", bool(os.getenv(var)), f"add {var}=... to .env")

# 5. .env is protected by .gitignore
ignored = subprocess.run(
    ["git", "check-ignore", "-q", ".env"], cwd=ROOT, capture_output=True
).returncode == 0
check(".env is ignored by Git", ignored, "add a line '.env' to .gitignore")

# 6. Database connection and the four schemas
try:
    import psycopg

    # With no arguments, psycopg reads the PG* settings loaded from .env
    with psycopg.connect(connect_timeout=5) as conn:
        version = conn.execute("SHOW server_version").fetchone()[0]
        check(f"Connected to PostgreSQL {version}", True)
        found = {
            row[0]
            for row in conn.execute(
                "SELECT schema_name FROM information_schema.schemata"
            )
        }
        for schema in ["raw", "staging", "intermediate", "marts"]:
            check(f"Schema '{schema}' exists", schema in found, "run: make schemas")
except Exception as exc:  # show the real error, it's the best clue
    check("Connected to PostgreSQL", False, f"{type(exc).__name__}: {exc}".strip())

print()
if all(results):
    print("All checks passed. Your machine is ready.")
else:
    print(f"{results.count(False)} check(s) failed. Fix the first one, then run again.")
    sys.exit(1)

dbt/dbt_project.yml

name: newcomer_credit
version: "1.0.0"
profile: newcomer_credit

model-paths: ["models"]
test-paths: ["tests"]
macro-paths: ["macros"]
seed-paths: ["seeds"]

target-path: "target"
clean-targets: ["target", "dbt_packages"]

dbt/profiles.yml

# dbt connection settings. Safe to commit: it holds NO secrets.
# Every value is read from environment variables, which come from your .env file.
newcomer_credit:
  target: dev
  outputs:
    dev:
      type: postgres
      host: "{{ env_var('PGHOST') }}"
      port: "{{ env_var('PGPORT') | as_number }}"
      user: "{{ env_var('PGUSER') }}"
      password: "{{ env_var('PGPASSWORD') }}"
      dbname: "{{ env_var('PGDATABASE') }}"
      schema: staging
      threads: 4

Many guides keep profiles.yml out of Git because it often contains passwords. Mine doesn't. Every value is read from environment variables, so the file is safe to commit, and anyone who clones the project (including an automated test runner later) gets the same configuration. More in the dbt profiles documentation.

Lesson for the whole step: Configuration belongs in Git. Secrets never do. Keeping the two separate is one of the most important habits in this field.

Step 7: Secrets in .env

cp .env.example .env
openssl rand -hex 16

The second command prints a random 32-character password. I opened .env with code .env, pasted it after PGPASSWORD= with no spaces and no quotes, and saved.

.env is hidden because its name starts with a dot. ls -la shows hidden files in the Terminal, and Cmd+Shift+. toggles them in Finder.

Check that Git will never see it:

git status

.env must not appear in the list.

Lesson: I never type a password into code, into a command I'll save, or into a chat message. Secrets live in the environment.

Step 8: The virtual environment

make setup
source .venv/bin/activate

The prompt now begins with (.venv). I run source .venv/bin/activate every time I open a new Terminal window for this project.

A virtual environment is a private box of Python libraries for one project. Without it, every project shares one global set, and upgrading something for one project silently breaks another. requirements.txt records what goes in the box, so anyone can rebuild it. More in the Python venv docs.

Windows (native, not WSL): activation is .venv\Scripts\activate.

Lesson: Reproducibility starts with isolated, recorded dependencies.

Step 9: Building the database as code

make db
make schemas

This is the step I had to slow down for, so here it is properly.

The building analogy. The PostgreSQL server is a building. A role is a person with a key card. A database is a flat. Schemas are rooms inside the flat. Tables are the furniture. Setup has three jobs: create a person, give them a flat, and build rooms inside it.

Why two people. I could do everything as my superuser, but that's like using the building's master key for daily errands. One careless delete and every flat is at risk. So the master key is used once, to create ncp_dev, a normal user who owns only the newcomer_credit database. All project work happens as ncp_dev. That's least privilege: everyone gets the smallest access that lets them do their job. I can even see it in the psql prompt. =# means I'm the superuser. => means I'm a normal user.

How the bootstrap script works. It combines three small ideas, and I tested each one by hand inside psql (psql postgres from zsh):

SELECT can return plain text. SELECT 'CREATE ROLE test_person LOGIN'; prints those words but creates nothing. It's an instruction written on a sticky note.
\gexec follows the instruction. SELECT 'CREATE ROLE test_person LOGIN' \gexec actually creates the role.
WHERE NOT EXISTS (...) decides whether the note gets written at all. If test_person already exists, the SELECT returns zero rows, \gexec has nothing to run, and nothing happens. No error.

(I cleaned up afterwards with DROP ROLE test_person; and left psql with \q.)

So the first command in 00_bootstrap.sql reads, in plain English: "Write an instruction to create a user called ncp_dev with this password, but only if no such user exists. Then follow the instruction."

How the password travels. It starts in .env. Make loads .env and hands the password to psql with -v app_password=..., which is like passing a sealed envelope. Inside the SQL, :'app_password' opens the envelope. format(... %L ...) slots it into the command as safely quoted text, so a password containing a quote mark can't break anything. The psql documentation covers variables and \gexec.

Why make db connects as me but make schemas connects as ncp_dev. Only a superuser can create users and databases, so make db overrides the user with PGUSER=$(SUPERUSER) and stands in the built-in postgres database, which works like the building's lobby. Once the flat exists, ncp_dev owns it and can build rooms without the master key.

The four rooms:

raw is the delivery shelf. Data lands exactly as received and is never edited, so I can always check what actually arrived.
staging is where data gets cleaned and typed.
intermediate is where business logic combines pieces into building blocks.
marts holds finished outputs, like features and reports.

Check, by running both commands a second time:

make db
make schemas

make db printed only its first line, and make schemas printed "already exists, skipping" four times. Both are success. Then I looked inside:

set -a; source .env; set +a
psql
\dn
\q

The first line loads .env into the current Terminal session. set -a marks every variable for export to programs I start next. After that, plain psql connects as ncp_dev to newcomer_credit, and \dn lists the four schemas.

Lessons, three big ones at once:

Infrastructure as code. The setup lives in files, in Git. It can be reviewed, repeated and rebuilt from nothing. If my laptop dies, I lose nothing.
Idempotency. Running a script twice must be as safe as running it once. Pipelines get retried after failures constantly, so every step should survive a re-run.
Least privilege. Use the master key once, then work with the smallest access that gets the job done.

Step 10: Proving it with a check script

make check

The script checks Python, the virtual environment, each library, every .env setting, that Git ignores .env, the database connection and the four schemas. When something fails, it prints the fix, and for connection failures it shows the real error message instead of hiding it.

Lesson: Write checks instead of trusting memory. Real teams call these "doctor" or "preflight" scripts. The same instinct later becomes data testing: never assume data is right, prove it.

Step 11: Connecting dbt

make dbt-debug

Success looks like:

profiles.yml file [OK found and valid]
Connection test: [OK connection ok]
All checks passed!

{{ env_var('PGHOST') }} in profiles.yml is Jinja templating, which dbt uses everywhere. It means "read this value from the environment." dbt looks for profiles.yml in the folder it runs from first, which is why make dbt-debug runs cd dbt before calling dbt. Install reference: dbt Core with pip.

Lesson: Professional tools separate what to run (project files) from where to run it (connection targets). The same code can point at development or production by switching one setting.

Step 12: Editor and database viewer

I opened the project in VS Code with code .. If that command doesn't exist yet, Cmd+Shift+P and "Shell Command: Install 'code' command in PATH" fixes it. Then Cmd+Shift+P, "Python: Select Interpreter," and I picked the one inside .venv so the editor and Terminal use the same Python.

Extensions I installed: Python (Microsoft), dbt Power User, SQLTools with the PostgreSQL driver, and GitLens.

When a database tool asks for a connection name, it's just a label for my own list. PostgreSQL never sees it. The real settings come from .env:

Field	Value
Connection name	newcomer_credit (local)
Host	localhost
Port	5432
Database	newcomer_credit
Username	ncp_dev
Password	the value of `PGPASSWORD` in `.env` (I choose "ask on connect")

Step 13: The first commit

Git works like posting a parcel. git add . puts changed files in the box (the dot means everything in this folder). git commit seals the box with a label. git push ships it to GitHub.

git pull
git status
git add .
git status
git commit -m "Set up project scaffold: Makefile, database bootstrap, setup checks, dbt connection"
git push

I run git status twice on purpose. The first shows what's changed. The second shows exactly what's about to be sealed. .env, .venv/, .DS_Store and .vscode/ must not be in that list.

Lesson: Read git status before every commit. It takes three seconds and it's the last line of defence against publishing secrets.

The errors I actually hit

This is the most useful section for future me. Each one is the real message I saw.

1. Python 3.12 wasn't installed

make: python3.12: No such file or directory
make: *** [setup] Error 1
source: no such file or directory: .venv/bin/activate

I'd skipped Step 4. Make couldn't find python3.12, so .venv was never created, which made the second error a knock-on effect of the first.

Fix: brew install python@3.12, confirm with python3.12 --version, then rerun make setup. Lesson: When errors arrive in a chain, fix the first one. The rest are often just consequences.

2. I couldn't find .env

The file existed, but I couldn't see it, because names starting with a dot are hidden.

Fix: ls -la in the Terminal, or Cmd+Shift+. in Finder. Open it with code .env. If cp .env.example .env says no such file, the example didn't get copied: dragging files in Finder skips hidden ones, but cp -R source/. destination/ includes them. Lesson: Hidden files are how most tools store configuration. Get comfortable seeing them.

3. I typed SQL into zsh

zsh: unknown file attribute: C
zsh: number expected
zsh: command not found: SELECT
zsh: command not found: gexec

I pasted the contents of 00_bootstrap.sql at a % prompt. zsh tried to run SELECT as a Mac command.

Fix: SQL only goes into psql, after the prompt changes to =# or =>. And the bootstrap SQL doesn't need typing at all, because make db runs the file. Lesson: Check which program you're talking to before you speak.

4. I pasted the prompt along with the command

zsh: parse error near `jimmyokoth@Jimmys-Ma...'

I copied old Terminal output, including my own prompt, and pasted it back in. zsh tried to run my username.

Fix: Copy only the text after the %. Clear the screen with Cmd+K so old output isn't tempting.

5. The virtual environment showed up twice

My prompt read ((.venv) ). The environment had been activated twice.

Fix: deactivate, then source .venv/bin/activate. One (.venv) again.

6. The password was empty, but the connection still worked

[FAIL] Setting PGPASSWORD present
       fix: add PGPASSWORD=... to .env
[OK  ] Connected to PostgreSQL 17.11 (Homebrew)

Two lessons hid in this one.

First, the connection passed because Homebrew configures PostgreSQL to trust connections from the same machine, so it never checked a password locally. Convenient on a laptop, but servers and test runners do check, so the password still has to be right. More on trust authentication.

Second, I couldn't fix it by rerunning make db. ncp_dev already existed, so WHERE NOT EXISTS skipped it and the password never got set. An idempotent "create if missing" script never updates something that already exists. Changing an existing thing needs its own command.

Fix: I put a password in .env, then:

set -a; source .env; set +a
echo "ALTER ROLE ncp_dev PASSWORD :'pw';" | PGUSER=$USER PGDATABASE=postgres psql -v pw="$PGPASSWORD"

That sends one SQL command as my superuser and passes the password through a psql variable, so the actual password never appears in my shell history.

7. I typed the next command before the first finished

make dbt-debug appeared in the middle of make check's output, because I typed it while the check was still running.

Fix: Wait for the % prompt to return.

8. git add with nothing after it

Nothing specified, nothing added.
hint: Maybe you wanted to say 'git add .'?

Without the dot, nothing went in the box, so git commit then reported nothing added to commit.

Fix: git add .

9. The push was rejected

 ! [rejected]        main -> main (fetch first)
hint: Updates were rejected because the remote contains work that you do not
hint: have locally.

I'd changed something directly on GitHub after cloning, so GitHub had a commit my Mac didn't. Git refused to overwrite it.

Fix: git pull first, then commit and push. Lesson: Pull before you push. Always bring the shared version down before sending yours up.

10. Files that didn't belong were waiting to be committed

git status listed .DS_Store (a Mac system file) and .vscode/ (my personal editor settings) as untracked.

Fix: Added both to .gitignore. Lesson: This is exactly what reading git status is for.

The lessons I'm keeping

The terminal is universal. Every server, container and test runner speaks it.
"Command not found" usually means PATH. Installed is not the same as findable.
One virtual environment per project, recorded in requirements.txt.
Pin the versions that matter.
Configuration goes in Git. Secrets never do.
If I set something up by hand, I can't review, repeat or rebuild it. Script it.
Every step should be safe to run twice.
"Create if missing" never updates what already exists.
Use the master key once, then work with least privilege.
Verify with checks, not memory.
Show the real error. Hiding it turns a five-minute fix into an afternoon.
Watch what gets printed. Logs leak secrets more often than code does.
Read the prompt before typing, and run one command at a time.
Fix the first error in a chain. The rest often disappear.
Read git status before every commit, and pull before every push.

Reusing this for my next project

This is the part I'll actually come back to. To start a new project from this base:

Create the repo on GitHub with the Python .gitignore, MIT license and a README. Clone it.
Create the folders and .gitkeep files from Step 6, and append the .gitignore additions.

Copy in the files from Step 6, then rename everything project-specific:

Find	Replace with	Where
`ncp_dev`	the new project's user name	`.env.example`, `.env`, `sql/00_bootstrap.sql`
`newcomer_credit`	the new database name	`.env.example`, `.env`, `sql/00_bootstrap.sql`, `dbt/dbt_project.yml`, `dbt/profiles.yml`

One trap to watch: in dbt, the profile: line in dbt_project.yml must match the top-level name in profiles.yml exactly. Rename one without the other and dbt debug reports Connection test skipped since no profile was found. I hit this while testing this guide, so rename both together.

Adjust the schema names in sql/01_schemas.sql and scripts/check_setup.py if the new project needs different layers.
cp .env.example .env, generate a password with openssl rand -hex 16, paste it in.
Run, in order: make setup, source .venv/bin/activate, make db, make schemas, make check, make dbt-debug.
git pull, git status, git add ., git status, commit, push.

If every check passes, the project base is done.

The real test of reproducibility: clone the repo into a fresh folder, copy .env across, and run step 6 again. If it works from scratch, the setup is genuinely reproducible, not just working on one machine by luck.

Daily quick reference

cd ~/projects/newcomer-credit-data-platform
source .venv/bin/activate         # every new Terminal window
make check                        # is everything still healthy?
set -a; source .env; set +a       # load .env into this Terminal for psql
psql                              # connect as the project user
brew services restart postgresql@17   # if the database stops responding

What's next

With the base in place, the next stage of the Newcomer Credit Data Platform is the first vertical slice: generating a synthetic population of customers, loading their transactions into the raw layer without ever creating duplicates, and building the first dbt model. I'll write that one up the same way, errors included.

What I ended up with

Two rules before touching anything

The project map

Step 1: Apple's developer tools

Step 2: Homebrew, the package manager

Step 3: Git identity, GitHub login and the repository

Step 4: Python 3.12

Step 5: PostgreSQL 17

Step 6: The project files

Folders, and why empty ones need .gitkeep

.gitignore additions (project root)

requirements.txt (project root)

.env.example (project root)

Makefile (project root)

sql/00_bootstrap.sql

sql/01_schemas.sql

scripts/check_setup.py

dbt/dbt_project.yml

dbt/profiles.yml

Step 7: Secrets in .env

Step 8: The virtual environment

Step 9: Building the database as code

Step 10: Proving it with a check script

Step 11: Connecting dbt

Step 12: Editor and database viewer

Step 13: The first commit

The errors I actually hit

1. Python 3.12 wasn't installed

2. I couldn't find .env

3. I typed SQL into zsh

4. I pasted the prompt along with the command

5. The virtual environment showed up twice

6. The password was empty, but the connection still worked

7. I typed the next command before the first finished

8. git add with nothing after it

9. The push was rejected

10. Files that didn't belong were waiting to be committed

The lessons I'm keeping

Reusing this for my next project

Daily quick reference

What's next