Analytics Data Modelling (dbt)

A version-controlled dbt project with sources, layered models, tests, docs and pull request review, the routines that keep it healthy, and the skills from incremental models to unit tests and multi-project setups.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Then open the Marketplace in the app, search for Analytics Data Modelling (dbt), and click Install. It goes into your own files, and works offline from then on.

What it installs.

1

Pillar

1

Area

50

Projects

195

Starter tasks

📊 Data & Analytics

Analytics Data Modelling (dbt)

Modelling raw data into clean, tested, documented analytics tables using version-controlled transformation code that the whole team can review.

  1. Inventory of transformation SQL outside version control
  2. dbt project repository and environments set-up
  3. Personal development schema for each contributor
  4. Source declarations for every raw table
  5. Staging, intermediate and marts layer conventions
  6. SQL style guide with an automated linter
  7. Staging models for the first source system
  8. First fact table and dimensions for one business question
  9. Pull request template, code owners and branch protection
  10. Choosing where production dbt builds run
  11. Slim CI build on every pull request
  12. Weekly pull request review rota
  13. Primary key and relationship test coverage standard
  14. Column documentation and docs site upkeep
  15. Quarterly package and minor version upgrades
  16. Monthly build run-time review
  17. Morning production build check and failure log
  18. Quarterly sweep for unused and orphaned models
  19. Monthly changelog for mart consumers
  20. Project health checks with the project evaluator package
  21. dbt fundamentals study plan
  22. Dimensional modelling and choosing the grain
  23. Jinja and macros for repeated SQL
  24. Custom generic tests for business rules
  25. Incremental models and late-arriving data
  26. Snapshots for slowly changing dimensions
  27. Reading compiled SQL and the DAG to debug a model
  28. Git branching and merge conflicts for analysts
  29. Materialisation choice for each layer
  30. Star schema or wide tables for the BI tool
  31. Date dimension with a fiscal calendar seed
  32. Timezone and currency handling standard in staging
  33. Moving BI-tool custom SQL into marts
  34. Replacing legacy stored procedures with tested models
  35. Breaking up a monolithic model
  36. Exposures for every dashboard built on marts
  37. Seed files for business-owned mapping tables
  38. Go-live of the first production marts
  39. Major dbt version migration
  40. Modelling design review for a new source system
  41. Docs site walkthrough for business users
  42. Quarter-end freeze on finance models
  43. Inheriting an undocumented dbt project
  44. An analyst's first merged model
  45. Solo analytics engineer operating rhythm
  46. Onboarding a new contributor to the project
  47. Unit tests for complex transformation logic
  48. Multi-project setup with access levels and model versions
  49. Python models for logic SQL handles badly
  50. Porting the project to a new warehouse adapter

Where it starts.

Each project arrives with its purpose, milestones and first tasks written out. Here is how the first three read.

Inventory of transformation SQL outside version control

Before writing a single model, find where business logic already lives: scheduled warehouse queries, views nobody owns, custom SQL inside BI datasets and the spreadsheet that someone refreshes on Mondays. That list becomes the backlog for the dbt project and shows which numbers will move when logic is consolidated. In most teams it turns up two or three competing definitions of the same table.

First tasks

  • Export the list of scheduled queries and views from the warehouse
  • List every BI dataset that uses custom SQL instead of a table
  • Mark each item keep, migrate or retire in one sheet
  • Rank the ten most-read items by query count over the last 90 days

dbt project repository and environments set-up

A dbt project is only reviewable if it lives in a Git repository with separate development and production targets from day one. Setting up the repository, the profile, a production schema that only the build job writes to and the first pinned packages takes an afternoon and saves months of untangling later.

First tasks

  • Create the repository and initialise a new dbt project inside it
  • Add the target, logs and package folders to the ignore file
  • Define development and production targets using environment variables
  • Pin dbt_utils and the required dbt version range in the project files
  • Build one placeholder model in both targets to prove the set-up

Personal development schema for each contributor

Sharing one development schema means two people overwrite each other's tables and nobody trusts what they are testing. Giving every contributor their own schema, named after them, with read access to raw data and no write access to production, lets people build and break things safely.

First tasks

  • Agree the naming rule for personal development schemas
  • Ask the warehouse admin for a contributor role with the right grants
  • Have each contributor run a full build into their own schema
  • Write the add-a-contributor steps into the README

Make it yours.

Install Analytics Data Modelling (dbt), then tell Aurum the date you're aiming for. It checks whether the plan fits the weeks you actually have.

Free · Mac (Apple silicon) and Windows · v1.0.5 · Other platforms

Blueprints are general information to help you organise your own work. They are not medical, financial, legal or other professional advice. See the Terms.