Project structure · Data and DevOps
dbt project project structure
Opens with creditsA dbt project that turns raw tables into tested, documented models, using the staging, intermediate and marts layers from dbt's own structure guide. It runs on DuckDB on your laptop, so you can learn analytics engineering for free, and grows into a governed project with checks, contracts, slim CI and a hosted docs site that moves to BigQuery or Snowflake unchanged.
Curated by Tauseef FayyazIntermediate144 files and foldersdbt 2.0 (Rust engine, GA September 2026), DuckDBChecked October 2026How it grows
Each stage opens the explorer there
- 1Day 1What the scaffold and your first hour give you47 files and folders
- 2GrowingWhat a real app with features and tests adds+46 files and folders
- ProductionWhat running it for real users adds+27 files and foldersOpens with credits
- ExpertWhat a team needs to keep it healthy for years+24 files and foldersOpens with credits
How it is organised
- Three layers, each with one job. Staging: one model per source table, renamed and cast, nothing joined. Intermediate: reusable steps that join or aggregate. Marts: the tables people query, one row per business entity.
- Data only flows forward: marts ref intermediate or staging models, staging models ref sources or seeds. A staging model never refs a mart.
- Every model gets a YAML file next to it with a description and tests. At minimum, the primary key is unique and not_null.
- Use ref() and source() for every table, never a hard-coded schema name, so dbt knows the dependency graph and can build into any environment.
- Name files so they explain themselves: stg_[source]__[entity]s, int_[entity]s_[verb]s, and plain business names (orders, customers) in marts.
- Group marts by business area (finance/, marketing/) once there are more than a handful; a single-team project can stay flat, as the dbt sample does.
- Test at three levels: unit tests prove the SQL logic on hand-written rows, data tests check the real data on every run, and checks (dbt v2) enforce project rules such as "every public model has an owner" before anything runs.
- Governance comes last. Groups, access, contracts and model versions make changes slower on purpose, so add them when other teams or dashboards depend on a model, not on day one.
- dbt v2 supports Snowflake, BigQuery, Databricks, Redshift and DuckDB. On Postgres or another warehouse, use dbt-core 1.x with its adapter: the layout is the same.
At the top of the project
Open one in the explorer for the full story
- shop/
- .devcontainer/A ready-made dev environment: Python, dbt and the dbt extension in a container.1 inside
- .vscode/Editor settings. dbt init adds a recommendation for the official dbt extension.2 inside
- analyses/SQL that dbt compiles but never runs: one-off questions and audit queries.1 inside
- dbt_packages/Packages downloaded by dbt deps (here dbt_utils).
- logs/dbt.log and the query log from each run.
- macros/Jinja macros: reusable SQL snippets and overrides of dbt's built-in behaviour.3 inside
- models/Every model: SQL select statements that dbt turns into views and tables, in layers.4 inside
- seeds/Small CSV files that dbt loads as tables. In the sample, the whole raw dataset.8 inside
- skills/dbt v2 agent skills: SKILL.md files that teach coding agents your repeatable tasks.1 inside
- snapshots/Snapshots: keep the history of rows that change in place (type 2 slowly changing dimensions).1 inside
- target/Everything dbt writes on each run: compiled SQL, manifest.json, run_results.json, the docs site.
- tests/Singular data tests, custom generic tests and unit test fixtures.3 inside
- .editorconfigIndentation and line endings for SQL, YAML and Markdown in any editor.
- .envYour local values for the variables profiles.yml reads with env_var().
- .env.exampleEvery variable the project needs, with safe placeholder values. Committed.
- .gitignoreIgnores dbt_packages/, dbt_internal_packages/, logs/, target/ and .env.
- .pre-commit-config.yamlGit hooks that lint SQL and parse the project before each commit.
- .sqlfluffSQL style rules. dbt v2's built-in dbt lint reads this file and SQLFluff rule codes.
- .sqlfluffignoreFolders the linter skips: target/, dbt_packages/, logs/.
- AGENTS.mdInstructions for AI coding agents: layers, naming, tests, commands.
- CLAUDE.mdClaude Code's instructions file, often a single line importing AGENTS.md.
- dbt_project.ymlThe project's main config: name, profile, folder paths and per-folder defaults.
- MakefileShort names for the commands everyone runs: setup, build, lint, docs.
- package-lock.ymlThe exact package versions dbt deps installed. Commit it.
- packages.ymldbt packages the project depends on. The sample adds dbt_utils.
- profiles.ymlWarehouse connections per target (dev, ci, prod). Usually in ~/.dbt/, here in the project for CI.
- README.mdHow to set up, run and contribute to the project. The sample's README is a placeholder.
- requirements.txtPins the dbt version (and pre-commit) for CI and teammates.
- shop.duckdbYour local DuckDB database, created on the first dbt build.
Learn more on Sefism
Roadmaps, cheat sheets and projects that go with dbt project.
- Roadmap· Google CloudData engineering roadmapPipelines, warehouses, Spark, dbt
- Roadmap· PythonData analytics roadmapSQL, spreadsheets, dashboards, insight
- Roadmap· PostgreSQLSQL and databases roadmapQueries, design, indexes, transactions
- Cheat sheetData engineering cheat sheetPipelines, warehouses, Spark, dbt.
- Cheat sheetData analytics cheat sheetSQL, spreadsheets, dashboards, insight.
- Cheat sheetSQL and databases cheat sheetQueries, design, indexes, transactions.
- FYP idea· AdvancedColumn-Level Data Lineage and Impact Analysis from SQL CodeA final year project that builds a column-level data lineage tool for analytics teams: it parses SQL and dbt models, works out which source columns feed every dashboard field, and warns in a pull request when a change will break or alter downstream reports. A data engineering project with a compiler-style core.
- FYP idea· IntermediatePipeline That Turns Provincial Statistical PDFs into a Versioned Open DatasetGovernments publish important statistics as PDF yearbooks and reports: schools, hospitals, crops, prices and population by district. Build a data pipeline that extracts tables from these PDFs, cleans and checks them, tracks every change between editions, and publishes them as tidy, documented, versioned datasets that journalists, students and researchers can query directly.
- FYP idea· AdvancedPublic Procurement Red-Flag Analytics with Supplier Entity ResolutionA final year project that builds an open data analytics platform for public procurement contracts, linking the many spellings of the same supplier into one entity and computing integrity red flags such as single bidding and contract splitting. Built for journalists, auditors and civic groups working with open contracting data.
See 9 moreShow fewer
- QuestionHow do I switch from data analyst to data engineer?You already have the hardest part: SQL, business context and knowing what good data looks like. Add Python for pipelines, data modelling, an orchestrator, a cloud warehouse, Git and testing. Build one end-to-end pipeline, then move internally by taking on engineering work in your current team before applying outside.
- QuestionWhat is the difference between a data analyst, a data scientist and a machine learning engineer?Analysts answer business questions with SQL, spreadsheets, dashboards and reports. Data scientists add statistics, experiments and predictive models. ML engineers build and run models inside production software. Titles vary by company, so read the work in the job advert, and pick by the kind of problem you enjoy, not the title that sounds most senior.
- QuestionHow do I learn Git and GitHub properly instead of just copying commands?Learn the model first: commits are snapshots, branches are pointers, and remotes are copies. Then use Git daily on your own projects, practise a team workflow with branches and pull requests, and break things on purpose, such as merge conflicts, so you learn to recover calmly.
- QuestionHow do I learn to read a large codebase when I can handle small projects but get lost in real ones?Nobody reads a large codebase from top to bottom. You follow one real path through it, such as a single request or button click, from entry point to database and back. Map what you find, ignore everything off that path for now, and repeat with a different path. Understanding builds in layers, and a first small change teaches more than a week of reading.
- Resource· Guidedbt Documentation (Developer Hub)The official dbt documentation, reference and best-practice guides. Keep it open while you build: the pages on project structure, materialisations, incremental models, tests and sources answer most of the questions a new dbt user has.
- Resource· Coursedbt LearnThe official, free, self-paced courses for dbt, the most common tool for transforming data inside a warehouse with SQL. Start with dbt Fundamentals: it covers models, sources, tests, documentation and deployment, which is most of what a junior role uses day to day.
- Resource· ToolDuckDB DocumentationDuckDB is a free, in-process analytical database that runs fast SQL over Parquet, CSV and JSON files on your laptop, with no server to set up. It is the easiest way to practise analytical SQL, columnar formats and even dbt without a cloud bill.
- Resource· GuideBigQuery DocumentationThe official documentation for BigQuery, Google Cloud's serverless data warehouse. Beyond the SQL reference, the pages on partitioned and clustered tables, pricing and cost control are essential reading for any data engineer on Google Cloud.
- Resource· GuideApache Airflow DocumentationThe official documentation for Apache Airflow, the most widely used open source orchestrator for data pipelines. Start with the tutorial and the core concepts pages, and make sure you are reading the Airflow 3 docs, because many older tutorials use Airflow 2 syntax that has changed.
Checked against 16 official sources, October 2026. If something has changed, the docs win.Show sourcesHide sources
- How we structure our dbt projects (dbt best practices)
- Staging layer guide
- Install dbt
- Upgrading to dbt v2 (checks, dbt lint, Docs v2, batch tests, agent skills)
- Checks
- About dbt docs commands (dbt Docs v2)
- Connection profiles (profiles.yml lookup order)
- Snapshots
- test-paths
- Unit tests
- Exposures
- Model access and groups
- Model contracts
- YAML selectors
- Semantic models (latest metrics spec)
- MetricFlow time spine
Best practices and checklists for this stack
Picked from the other libraries by shared topics.
Best practices
See all- Data Pipeline Best PracticesHow to build data pipelines you can re-run, test and trust: idempotent tasks, orchestrator hygiene, layered transformations, data tests, freshness, ownership and monitoring.
- Data Analysis Best PracticesAn analyst's workflow in SQL, pandas and Polars: framing the question, reading data correctly, joins and grain, aggregation pitfalls, reproducible notebooks, checking results and sharing findings.
- Data Modeling Best PracticesDimensional data modeling for analytics: declaring the grain, fact and dimension tables, slowly changing dimensions, dbt marts, naming, tests, contracts and a semantic layer.
Checklists
See all- Data Quality ChecklistA data quality checklist for warehouse tables: ownership, freshness, volume, schema, nulls, uniqueness, referential checks, anomaly monitoring and data incidents, with how to verify each.
- Analytics Dashboard ChecklistAn analytics dashboard checklist for launch day: purpose and audience, metric definitions, data freshness, correctness, performance, access, accessibility, ownership and adoption.
- ML Model Deployment ChecklistAn ML model deployment checklist: baselines, training and serving consistency, validation, model registry, canary rollout, rollback, drift monitoring and ownership, with how to verify each.
Work with me
Stuck on something specific?
Writing only gets you so far. If you want an answer to your situation rather than the general case, book a session and we will work through it together. Sessions are free for approved Sefism members, and a few slots open each week.
- 1:1 Mentorship
- Fresh Graduate Mentorship
- Career Guidance
Brand deals, promos and partnerships: tauseeffayyaz@gmail.com