Lecture-style notes on every problem faced building this pipeline and how each was solved.
Pipeline: GitHub API -> Fivetran -> Snowflake -> dbt -> Looker Studio
Problem: Confusion between two profiles.yml files. One in ~/.dbt/profiles.yml (global) and one created by dbt Cloud that contained credentials and wanted to be committed to GitHub.
Key concepts:
- dbt needs a
profiles.ymlto know how to connect to the warehouse. - The "profile name" is the top-level YAML key in profiles.yml. It must match the
profile:value indbt_project.yml. - Local dbt CLI reads from
~/.dbt/profiles.ymlby default, OR from a directory set byDBT_PROFILES_DIR.
Solution:
- Keep ONE source of truth. Use a project-level
profiles.ymlinsidetransform/that usesenv_var()references instead of hardcoded credentials. - Never commit a profiles.yml that contains passwords.
Problem: Error Profile 'default' not found in profiles.yml.
Cause: dbt_project.yml had profile: 'default' but the top-level key in profiles.yml was something else (for example user:).
Solution: The profile name is the TOP-LEVEL key in profiles.yml, not default, not dbname. Make dbt_project.yml's profile: match that exact key.
Problem: Wanted a clean local dev setup without passwords sitting in profiles.yml.
Solution: Use Jinja env_var() in profiles.yml and store secrets in a .env file at the project root.
default:
outputs:
dev:
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
role: "{{ env_var('SNOWFLAKE_ROLE') }}"
warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
database: GITHUB_STAGING
schema: dev
threads: 1
target: devThis profiles.yml is safe to commit because it holds no secrets. The .env stays gitignored.
Problem: Error showing a doubled path profiles.yml/profiles.yml.
Cause: Set DBT_PROFILES_DIR to the file path. dbt appends profiles.yml automatically, so it must point to the DIRECTORY.
Solution:
export DBT_PROFILES_DIR=/path/to/transformPoint it to the folder that contains profiles.yml, not the file itself.
Problem: dbt threw env_var SNOWFLAKE_ACCOUNT not found even though the values were in .env.
Cause: Variables set without export are not passed to child processes (dbt runs as a child process).
Solution: Auto-export everything when sourcing:
set -a && source .env && set +aOr prefix each line in .env with export. For convenience, direnv can auto-load .env on entering the directory.
Problem: parse error near '&' when sourcing .env.
Cause: Special characters in values (like &) are interpreted by the shell.
Solution: Wrap all values in double quotes.
export SNOWFLAKE_PASSWORD="my&pass"Problem: dbt generated transform/.user.yml. Unsure what it is.
Solution: It only holds an anonymous user id for dbt. It is a local file and should be gitignored. Already covered by the .user.yml entry in .gitignore.
Question: What is the target/ folder in dbt?
Answer: It is generated output. dbt compiles models into runnable SQL and stores artifacts (compiled SQL, run results, manifest) there. Never edit it by hand. It is gitignored.
Problem: Could not tell which object in the Snowflake explorer was a database and which was a schema.
Key concept: Snowflake has a 3-level hierarchy:
Database -> Schema -> Table
Project structure (medallion architecture):
GITHUB_RAW= Bronze, raw data from FivetranGITHUB_STAGING= Silver, cleaned staging viewsGITHUB_ANALYTICS= Gold, business-ready marts
Problem: Configured the dbt source as GITHUB_RAW.FIVETRAN, but data actually landed in GITHUB_RAW.GITHUB. Error: Schema 'GITHUB_RAW.FIVETRAN' does not exist.
Cause: Fivetran names the destination schema after the connector type (github), and that name is LOCKED after the first save. It ignored the intended FIVETRAN name.
Solution: Update the dbt source in _staging.yml to match reality:
sources:
- name: github_raw
database: GITHUB_RAW
schema: GITHUBLesson: GITHUB is arguably the better name anyway since it describes the data origin. The production way to handle this is to align dbt sources to whatever the ingestion tool actually produces.
Problem: Error Schema 'GITHUB_RAW.GITHUB' does not exist or not authorized even after granting the role.
Cause: In Snowflake, access needs USAGE on the database AND on each schema, plus SELECT on tables. The schema-level USAGE grant was missing.
Solution: Add schema grants (including FUTURE schemas so new ones are covered automatically):
GRANT USAGE ON ALL SCHEMAS IN DATABASE GITHUB_RAW TO ROLE DBT_ROLE;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE GITHUB_RAW TO ROLE DBT_ROLE;Problem: Errors like invalid identifier EMAIL, OWNER_LOGIN, OPEN_ISSUES, UPDATED_AT.
Cause: The tutorial's model SQL assumed GitHub API field names, but Fivetran's connector does not load all of those fields. The tutorial code did not match the actual Fivetran output.
Solution: Inspect the real columns in Snowflake and rewrite models to use only what exists.
| Tutorial assumed | Fivetran actually provides |
|---|---|
| owner_login | owner_id only |
| stargazers_count | watchers_count |
| open_issues_count | not synced |
| updated_at, pushed_at | not synced |
| email, public_repos, followers, following | not synced (email lives in a separate USER_EMAIL table) |
Lesson: Never trust tutorial column names. Always verify against the source.
Problem: following is a reserved keyword in Snowflake and broke the query.
Solution: Quote it: "following". (Ultimately removed since the column did not exist anyway.)
Problem: email column did not exist on the USER table.
Cause: Fivetran splits email into a separate USER_EMAIL table. One user can have multiple emails.
Solution: Create a dedicated stg_user_emails staging model from the user_email source table.
Concept: A dbt test is a SQL query that looks for rows that should NOT exist. 0 rows returned = pass. Any rows = fail.
Built-in generic tests used: unique, not_null.
Problem found: stg_user_emails had a unique test on user_id. But one user can have many emails, so user_id is not unique here. The test would fail.
Solution: Put unique on the column that is actually one-row-per-value (the grain). For emails, that is email, not user_id.
- name: stg_user_emails
columns:
- name: user_id
tests:
- not_null
- name: email
tests:
- unique
- not_nullLesson: Always ask "what is the grain of this table?" before adding a unique test.
Running tests:
dbt test # all tests, after dbt run
dbt build # runs models AND tests in DAG orderProblem: Worried there was no date column for Looker Studio, and fct_daily_stats used a non-existent updated_at.
Solution: Use created_at (exists on USER and REPOSITORY) or _fivetran_synced. For fct_daily_stats, group by created_at to show repos created per day by language. Still a valid, meaningful metric.
Problem: dbt built everything into GITHUB_STAGING with prefixed schemas FIVETRAN_STAGING and FIVETRAN_MARTS. The marts were supposed to be in GITHUB_ANALYTICS, and GITHUB_ANALYTICS.MARTS was empty.
Cause (two separate issues):
- dbt writes every model into the
databasefrom the active connection (GITHUB_STAGING). The+schemaconfig changes the schema only, never the database. dbt does not readdb_structure.sql. - dbt's default
generate_schema_namemacro concatenates<connection_schema>_<custom_schema>. Connection schemaFIVETRAN+ custommarts=FIVETRAN_MARTS. The prefix is a team safety feature.
Solution part A: Route layers to the right database with +database:
models:
de-project-end-to-end:
staging:
+database: GITHUB_STAGING
+schema: staging
+materialized: view
marts:
+database: GITHUB_ANALYTICS
+schema: marts
+materialized: tableSolution part B: Override the schema naming macro to use names verbatim. Create transform/macros/generate_schema_name.sql:
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- else -%}
{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}Permissions follow-up: marts now write to a new database, so grant it:
GRANT USAGE, CREATE SCHEMA ON DATABASE GITHUB_ANALYTICS TO ROLE DBT_ROLE;Cleanup: old objects do not auto-delete:
DROP SCHEMA GITHUB_STAGING.FIVETRAN_STAGING;
DROP SCHEMA GITHUB_STAGING.FIVETRAN_MARTS;Lesson: dbt creates schemas automatically. You do not need manual CREATE SCHEMA statements.
Goal: Run transforms daily without keeping the laptop on.
Mental model: Locally, you are the runtime. In dbt Cloud, dbt Cloud is the runtime. It clones the repo, uses credentials stored in the Cloud UI (not your .env), and runs on a schedule.
Setup:
- Connect the GitHub repo. Set the project subdirectory to
transformsince the dbt project is not at repo root. - Configure connection (account, warehouse) and deployment credentials in the UI. dbt Cloud does NOT use profiles.yml; it generates the profile from these settings.
- Create a Deployment environment with a target schema (for example PROD).
- Create a Job:
- Command:
dbt build - Schedule: cron such as
0 6 * * *(06:00 UTC daily). Schedules are in UTC.
- Command:
Ordering: Fivetran must sync before dbt runs. Simplest approach is a time offset (Fivetran at 05:00 UTC, dbt at 06:00 UTC). Advanced approach is trigger chaining via Fivetran calling the dbt Cloud API.
Production touches: failure notifications, dbt source freshness checks, and a separate PROD schema isolated from dev.
- The profile name is the top-level key in profiles.yml and must match
dbt_project.yml. - Keep secrets in
.env, reference them withenv_var(), never commit credentials. DBT_PROFILES_DIRpoints to a directory, not a file.- Use
export(orset -a) so env vars reach the dbt child process. - Snowflake hierarchy is Database -> Schema -> Table; access needs USAGE at every level.
- Ingestion tools name things their own way (Fivetran used GITHUB). Align dbt sources to reality.
- Never trust tutorial column names. Verify against the actual source.
- dbt tests look for rows that should not exist. Match unique tests to the table grain.
- dbt writes to the connection database by default. Use
+databaseand a customgenerate_schema_namemacro to control placement. - dbt creates schemas automatically and never reads your manual SQL setup scripts.
- dbt Cloud becomes the runtime for scheduled jobs; credentials live in the UI, not your
.env.
The two files confuse people because both are YAML and both configure dbt, but they answer different questions.
| profiles.yml | dbt_project.yml | |
|---|---|---|
| Answers | WHERE do I connect? | WHAT do I build and HOW? |
| Concerns | Connection / credentials | Project structure / behavior |
| Contains | account, user, password, role, warehouse, database, schema, threads | project name, model paths, materializations, +schema, +database, tests, seeds config |
| Holds secrets? | Yes (passwords) | No, never |
| Lives where | ~/.dbt/ or a private dir; gitignored when it has creds | Repo root of the dbt project (transform/); committed |
| One per | Machine / environment | Project |
Analogy: profiles.yml is the key to the building (how you get into Snowflake). dbt_project.yml is the blueprint of what you build once you are inside.
The link between them: dbt_project.yml has a profile: line, and that name must match the top-level key in profiles.yml. That is the handshake.
dbt_project.yml: profile: 'default' ----+
| must match
profiles.yml: default: <-------------+
Where models land (database/schema) is a WHAT/HOW decision, so it belongs in dbt_project.yml, not profiles.yml. The connection (profiles.yml) only sets the default landing zone.
Two kinds of work dbt does:
- dbt run = builds your models. It takes each .sql file, wraps it in CREATE VIEW / CREATE TABLE, and executes it in Snowflake. Produces STG_USERS, DIM_REPOSITORIES, etc. It does NOT run any tests.
- dbt test = runs your tests (the unique, not_null, etc. from _staging.yml). It queries tables/views that already exist, so it is useless until dbt run has built them.
Manual two-step workflow:
dbt run # build everything
dbt test # then check everythingProblem with two steps: dbt run builds everything first, ignoring quality. If stg_users has duplicate IDs, dim_users gets built on top of bad data anyway, and you only find out at the very end.
dbt build = run + test (plus seeds and snapshots) interleaved in DAG order, model by model.
DAG = the dependency graph dbt builds from your ref() and source() calls. It knows dim_users depends on stg_users, so it orders them.
dbt build walks that graph and, for each node:
build stg_users -> test stg_users -> (pass?) -> build dim_users -> test dim_users ...
(fail?) -> SKIP dim_users (and anything downstream)
Key advantage: if a model's tests fail, dbt build stops bad data from flowing downstream. You never build dim_users on top of a broken stg_users.
| Command | Builds models | Runs tests | Stops on test failure |
|---|---|---|---|
| dbt run | Yes | No | n/a |
| dbt test | No | Yes | reports, but nothing was building |
| dbt build | Yes | Yes | Yes, skips downstream |
- dbt run while iterating fast on one model's SQL and you do not care about tests yet. Often with --select:
dbt run --select stg_users. - dbt test when you changed only tests, or want to re-check without rebuilding.
- dbt build as the default for any real run, and always in the scheduled dbt Cloud job. It is the production-correct command because it guarantees tested data.
Five business questions and how they map to the marts.
Available marts:
- DIM_REPOSITORIES: repository_id, repository_name, full_name, owner_username, description, language, stars, forks, repo_created_at
- DIM_USERS: user_id, username, display_name, company, location, bio, user_created_at
- FCT_DAILY_STATS: stat_date, language, repo_count, total_stars, total_forks, avg_stars, avg_forks
SELECT
language,
COUNT(*) AS repo_count,
SUM(stars) AS total_stars,
ROUND(AVG(stars), 1) AS avg_stars
FROM GITHUB_ANALYTICS.MARTS.DIM_REPOSITORIES
WHERE language IS NOT NULL
GROUP BY language
ORDER BY repo_count DESC;SELECT
full_name,
language,
stars,
forks
FROM GITHUB_ANALYTICS.MARTS.DIM_REPOSITORIES
ORDER BY stars DESC
LIMIT 20;SELECT
stat_date,
SUM(repo_count) AS repos_created,
SUM(SUM(repo_count)) OVER (ORDER BY stat_date) AS cumulative_repos
FROM GITHUB_ANALYTICS.MARTS.FCT_DAILY_STATS
GROUP BY stat_date
ORDER BY stat_date;repos_created = new repos per day; cumulative_repos = running total (the growth curve). This measures repo creation over time, since created_at is the only date Fivetran gives. There is no historical star snapshot.
Needs a commits model (see next section). Until then, a proxy from existing marts is who owns the most repositories:
SELECT
u.username,
u.display_name,
u.company,
COUNT(r.repository_id) AS repos_owned,
SUM(r.stars) AS total_stars_earned
FROM GITHUB_ANALYTICS.MARTS.DIM_REPOSITORIES r
JOIN GITHUB_ANALYTICS.MARTS.DIM_USERS u
ON r.owner_username = u.user_id -- owner_username actually holds owner_id
GROUP BY u.username, u.display_name, u.company
ORDER BY repos_owned DESC
LIMIT 20;Needs an issues model (see next section). There is no issues mart yet; issue exists only as a raw source.
Lesson: the current marts cover repos and users well, but not commits or issues. Some questions expose gaps in the model layer, not the SQL.
Goal: build stg_commits, stg_issues and matching marts so questions 4 and 5 read cleanly from GITHUB_ANALYTICS.MARTS.
DESCRIBE TABLE GITHUB_RAW.GITHUB.ISSUE;
DESCRIBE TABLE GITHUB_RAW.GITHUB.COMMIT;Same trap as before: do not assume column names. COMMIT is the least predictable because git commits store an author name/email, not always a GitHub user_id. Adjust the models to what you actually see.
Likely columns:
- ISSUE: id, number, state, title, user_id, repository_id, created_at, closed_at
- COMMIT: sha, author_name, author_login (maybe), repository_id, author_date or committed_at
stg_issues.sql:
WITH source AS (
SELECT * FROM {{ source('github_raw', 'issue') }}
),
cleaned AS (
SELECT
id AS issue_id,
number AS issue_number,
state AS issue_state, -- 'open' / 'closed'
title,
user_id,
repository_id,
created_at,
closed_at
FROM source
WHERE id IS NOT NULL
)
SELECT * FROM cleanedstg_commits.sql (adjust to DESCRIBE output):
WITH source AS (
SELECT * FROM {{ source('github_raw', 'commit') }}
),
cleaned AS (
SELECT
sha AS commit_sha,
author_name, -- or author_login if it exists
repository_id,
author_date AS committed_at -- whatever the real date column is
FROM source
WHERE sha IS NOT NULL
)
SELECT * FROM cleanedcommit and issue are already in _staging.yml sources, so only the models are new.
- name: stg_issues
description: "Cleaned GitHub issues"
columns:
- name: issue_id
tests:
- unique
- not_null
- name: issue_state
tests:
- accepted_values:
values: ['open', 'closed']
- name: stg_commits
description: "Cleaned GitHub commits"
columns:
- name: commit_sha
tests:
- unique
- not_nullNew test: accepted_values fails if issue_state ever holds anything other than open or closed. A data-quality guard before dashboarding.
fct_issues.sql (answers Q5):
WITH issues AS (
SELECT * FROM {{ ref('stg_issues') }}
)
SELECT
repository_id,
COUNT(*) AS total_issues,
COUNT_IF(issue_state = 'open') AS open_issues,
COUNT_IF(issue_state = 'closed') AS closed_issues,
ROUND(COUNT_IF(issue_state = 'closed') / COUNT(*) * 100, 1) AS closed_pct
FROM issues
GROUP BY repository_idCOUNT_IF is a Snowflake shortcut for counting rows where a condition is true, cleaner than SUM(CASE WHEN ... THEN 1 END).
fct_contributors.sql (answers Q4):
WITH commits AS (
SELECT * FROM {{ ref('stg_commits') }}
)
SELECT
author_name,
COUNT(*) AS commit_count,
COUNT(DISTINCT repository_id) AS repos_contributed_to
FROM commits
WHERE author_name IS NOT NULL
GROUP BY author_nameRegister both in _marts.yml with at least a description.
cd transform
dbt build --select stg_issues stg_commits fct_issues fct_contributors--select builds just these four (plus their tests) instead of the whole project.
Q5, issues closed vs open (account-wide):
SELECT
SUM(open_issues) AS open_issues,
SUM(closed_issues) AS closed_issues,
ROUND(SUM(closed_issues) / SUM(total_issues) * 100, 1) AS closed_pct
FROM GITHUB_ANALYTICS.MARTS.FCT_ISSUES;Q4, most active contributors:
SELECT
author_name,
commit_count,
repos_contributed_to
FROM GITHUB_ANALYTICS.MARTS.FCT_CONTRIBUTORS
ORDER BY commit_count DESC
LIMIT 20;The why behind every modeling decision, so the choices are repeatable on the next project.
dbt ships with exactly four generic tests out of the box:
| Test | Fails when | Use it on |
|---|---|---|
| not_null | any NULL exists in the column | every key, plus any column a dashboard depends on |
| unique | a value repeats | the column that defines the table grain (one row per X) |
| accepted_values | a value is outside an allowed list | low-cardinality category/status columns |
| relationships | a value has no match in a parent table | foreign keys (referential integrity) |
Beyond these four, you have two more sources of tests:
- Singular tests: a hand-written .sql file in tests/ that returns the bad rows. Use for one-off business rules (for example "closed_at must be after created_at").
- Package tests: install dbt_utils or dbt_expectations for dozens more (accepted_range, expression_is_true, not_null_proportion, etc.).
Ask these questions in order:
- Is it the primary key / grain of the table? -> add unique + not_null. Example: issue_id in stg_issues, email in stg_user_emails.
- Is it a key that other models join on, but rows can repeat? -> not_null only, not unique. Example: user_id in stg_user_emails (one user has many emails, so it repeats).
- Is it a foreign key into another table? -> relationships (and usually not_null). Example: repository_id pointing at stg_repositories.
- Is it a status/category with a known fixed set of values? -> accepted_values. Example: issue_state in ['open', 'closed'].
- Will a dashboard break or mislead if it is NULL? -> not_null. Example: a metric column you SUM.
- None of the above? -> usually no test. Do not test descriptive free-text columns like bio or description. Testing everything adds noise and slows runs.
The single most common mistake (and one we hit): putting unique on the wrong column. Always state the grain in one sentence first.
- stg_repositories grain = one row per repository -> unique on repository_id.
- stg_user_emails grain = one row per email -> unique on email, NOT user_id.
If the sentence has "per X", X is your unique column.
Every staging model follows the same shape on purpose:
WITH source AS (
SELECT * FROM {{ source(...) }} -- 1. pull raw, untouched
),
cleaned AS (
SELECT ... -- 2. rename, cast, filter
FROM source
WHERE ...
)
SELECT * FROM cleaned -- 3. expose the resultWhy split it up: the source CTE isolates the one place the raw table is named (easy to repoint), the cleaned CTE holds all the logic, and the final SELECT makes the output obvious. Readable and debuggable.
- Staging models read from sources via
{{ source('github_raw', 'x') }}. One staging model per raw table. Never reference GITHUB_RAW directly by name; the source() function makes dependencies visible to dbt and centralizes the table location. - Marts read from other models via
{{ ref('stg_x') }}. Never reference raw sources from a mart; always build on staging. ref() is what lets dbt order the DAG and lets dbt build skip downstream models when an upstream test fails.
Rule of thumb: source() at the staging layer only, ref() everywhere above it.
Renaming in staging is deliberate, not cosmetic:
- Consistency: raw GitHub gives id, login, name. We alias to user_id, username, display_name so every downstream model speaks the same vocabulary. id is ambiguous across tables; user_id is not.
- Hide source quirks: watchers_count AS stars and forks_count AS forks present a clean business name even though Fivetran used a different one. If the source column ever changes, you fix it in one staging model and nothing downstream notices.
- Reserved words: aliasing also dodges keyword collisions (the "following" problem).
Staging is the translation layer: raw vendor names in, clean business names out.
The filter WHERE id IS NOT NULL appears in staging for one specific reason: drop rows with no primary key. A row with a NULL id is unusable, cannot be joined, and would fail the not_null test anyway. Filtering it here keeps junk out of the warehouse and keeps the test green.
Decision rule:
- Filter the primary key for NULL (WHERE id IS NOT NULL) -> always, in staging. This is row-level garbage removal.
- Do not filter other columns for NULL by default. A repo with a NULL language is still a valid repo; dropping it would hide data. Instead, filter NULLs only at the point of use, for example WHERE language IS NOT NULL inside fct_daily_stats because grouping by NULL language is meaningless for that one metric.
The distinction: filter the key everywhere (data integrity); filter attributes only where a specific query needs it (analysis choice).
In dbt_project.yml: staging is +materialized: view, marts is +materialized: table.
- Views are cheap and always reflect the latest source, good for the thin cleaning layer.
- Marts are queried by dashboards repeatedly, so materializing them as tables makes Looker Studio fast. You trade storage and build time for query speed where it counts.
Problem: Built new marts (fct_contributors, fct_issues) with dbt build and they appeared in Snowflake, but the Looker Studio dashboard did not show the new data.
Cause: Looker Studio refreshes data within existing data sources automatically, but it never scans Snowflake for new tables. Each table is a separate data source that must be added manually.
Solution: In the existing report: Edit > Resource > Manage added data sources > Add a data source > select Snowflake connector > pick the new table. Repeat for each new table.
Lesson: New table in Snowflake = add it once as a new data source in Looker Studio. After that, it stays live and refreshes automatically.
Problem: Added committed_at to a SELECT that had COUNT(*) and GROUP BY author_name. Snowflake error: 'COMMITS.COMMITTED_AT' in select clause is neither an aggregate nor in the group by clause.
Cause: SQL rule: when using aggregate functions (COUNT, SUM, AVG, etc.), every column in SELECT that is NOT an aggregate must appear in GROUP BY. committed_at was a raw column, not wrapped in an aggregate, and not in GROUP BY.
Solutions (depending on intent):
- Want the raw date as a grouping dimension? Add it to GROUP BY:
GROUP BY author_name, commit_date. This changes the grain from "one row per author" to "one row per author per day". - Want date info without changing the grain? Use an aggregate:
MIN(committed_at) AS first_commit_at, MAX(committed_at) AS last_commit_at.
Lesson: Before adding a column to SELECT, ask: "is this an aggregate or a dimension?" If it is a dimension, it must go in GROUP BY and it will change the grain of the table.
Problem: Wanted one table to serve both a contributor leaderboard AND a commits-over-time chart. Tried to add a date column to fct_contributors, but that changed the grain from "one row per author" to "one row per author per day", making the leaderboard numbers wrong.
Key concept: The grain of a table is "what does one row represent?" Tables with different grains answer different questions:
| Model | Grain | Dashboard use |
|---|---|---|
| fct_contributors | One row per author | Leaderboard / bar chart |
| fct_commit_activity | One row per day | Time-series line chart |
Solution (two approaches):
- Separate models (textbook correct): keep each model at one clean grain. More models, but each is simple and reusable.
- Combined model (pragmatic): group by author + date. Works fine for small projects but some metrics become less intuitive (repos_contributed_to per day vs total).
Lesson: For small projects, combining is fine. At scale, separate models per grain keeps things clean. Always state the grain in one sentence before building: "this table has one row per ___."
Problem: Ran direnv allow and then dbt build, but still got env_var SNOWFLAKE_ACCOUNT not found.
Cause: The .envrc file in transform/ was empty (0 bytes). direnv allow approved a blank file, so no variables were exported.
Solution: Add dotenv ../.env to the .envrc file. This tells direnv to load the .env file from the project root (one directory up) and export all its variables.
# transform/.envrc
dotenv ../.envLesson: direnv reads .envrc, not .env. The .envrc must contain an instruction like dotenv to load the env file. An empty .envrc is silently useless.
Problem: Ran dbt build --select fact_issues and got a warning instead of an error: The selection criterion 'fqn:fact_issues' does not match any enabled nodes.
Cause: The model file is fct_issues.sql, not fact_issues.sql. dbt does not error out when the selector matches nothing; it just warns and exits with zero work done.
Lesson: If dbt says "Nothing to do" or "does not match any enabled nodes", check for typos in the model name. The model name comes from the filename, not the YAML description.
Problem: Dashboard showed commits from people who are not the repo owner. The contributor data was a mix of the owner's commits and other people's.
Cause: When you fork a repository, the fork carries the entire git history of the original repo. Fivetran syncs all commits from every repo you own, including forks. So forking facebook/react would pull thousands of commits by Facebook engineers.
Solution options:
- Filter by author name:
WHERE author_name = 'your_name' - Filter out forked repos by joining with the repository table (if a
forkboolean column exists in the raw data) - Keep it as-is: showing all contributors to your repos is valid data
Lesson: Understand what the ingestion tool actually pulls. Fivetran's GitHub connector syncs every commit in every repo linked to your account, regardless of who authored it.
Problem: Confused by two separate credential screens in dbt Cloud. One at /settings/profile/credentials/ and another when creating a deployment environment.
Key concept: dbt Cloud has two types of credentials for two different purposes:
| Profile Credentials | Deployment Environment | |
|---|---|---|
| Used when | You use the Cloud IDE (web editor) to develop | A scheduled job runs automatically |
| Triggered by | You, manually | The scheduler |
| Think of it as | Your dev laptop in the cloud | The production server |
In a team, each developer has their own profile credentials (writing to their own dev schema), while the deployment environment uses shared credentials writing to production. For a solo project, the values are the same.
Also important: dbt Cloud ignores profiles.yml entirely. It generates the profile from the UI settings. profiles.yml and .env are for local CLI only.
Problem: Unsure what schema to put in dbt Cloud credentials. Should it match profiles.yml? Match the actual Snowflake schema?
Cause: Confusion between the connection-level default schema and the model-level schema overrides.
How it works:
dbt Cloud schema field: FIVETRAN <-- default fallback (rarely used)
overridden by dbt_project.yml:
staging: +schema: staging <-- staging models go here
marts: +schema: marts <-- marts models go here
The custom generate_schema_name macro makes overrides work verbatim. So the schema in the connection (FIVETRAN) is only used for models that do not have an explicit +schema in dbt_project.yml.
Solution: Use FIVETRAN to match the local profiles.yml. Since all models already have +schema overrides, this value is never actually used.
Lesson: The schema in profiles.yml / dbt Cloud credentials is a fallback default. The real routing happens in dbt_project.yml with +schema and +database.