Getting started with dbt

查看原文 / View Original 编辑 / Edit

Getting started with dbt

dbt 入门指南

As a contract data engineer, I sometimes experience — ahem — let’s just say, periods of inactivity. When I browse the online market for suitable roles during those times, one of the most in-demand skills I keep seeing is experience with a tool called dbt. So, to give myself the best possible chance of obtaining work, I decided to learn as much about dbt as I thought I needed to, to at least be confident enough to talk about it in general terms to a fellow techie at an interview stage should the need arise. This article distils that process and what I’ve learnt. Of course you can’t learn a subject just by reading about it, so as usual, I’ll show plenty of practical code and real-world examples.

作为一名合同制数据工程师，我有时会经历——咳咳——怎么说呢，一段“空窗期”。当我在网上浏览合适的职位时，我不断看到最热门的技能之一就是一种名为 dbt 的工具。因此，为了给自己争取到最好的工作机会，我决定尽可能多地学习 dbt，至少要达到在面试中与同行交流时能自信地谈论它的程度。本文提炼了我的学习过程和心得。当然，光靠阅读是无法掌握一门学科的，所以像往常一样，我将展示大量的实用代码和真实案例。

To be clear, I have no affiliation or commercial association with dbt, DuckDB, or their creators. dbt Core is a free open-source software released under the Apache 2.0 licence, and you can run it locally without a dbt account. DuckDB is also free to use under the permissive MIT licence.

需要说明的是，我与 dbt、DuckDB 或其创建者没有任何隶属或商业关联。dbt Core 是一款基于 Apache 2.0 协议发布的免费开源软件，你无需 dbt 账户即可在本地运行它。DuckDB 同样可以根据宽松的 MIT 协议免费使用。

dbt provides a broad range of capabilities, but as this is an introduction to the topic, I’m concentrating on explaining the basics. This includes using dbt models and sources, and using it to test data and create documentation. More about all of these later.

dbt 提供了广泛的功能，但由于本文是该主题的入门介绍，我将重点解释其基础知识。这包括使用 dbt 模型（models）和源（sources），以及利用它进行数据测试和创建文档。稍后会详细介绍这些内容。

If you’ve worked on any reasonably sized analytics or data engineering project, you’ve probably ended up with a folder full of SQL scripts. When your project first starts up, everything feels manageable. You run the scripts manually or schedule them in whatever orchestration tool your company uses. Everything is good. Then the project grows.

如果你参与过任何规模适中的分析或数据工程项目，你可能最终会得到一个装满 SQL 脚本的文件夹。项目刚启动时，一切都井井有条。你可以手动运行脚本，或者在公司使用的任何编排工具中进行调度。一切都很顺利。然后，项目开始增长。

A column gets renamed in one table, and suddenly some downstream report or dashboard stops working or, worse still, your nightly 10 million record data ingestion job fails, and your whole system grinds to a halt. The list of issues that a wrongly applied bit of SQL or a table change can do to a database system is scary. And you know what, it happens all the time.

某个表中的一列被重命名了，突然间，下游的某些报告或仪表板就停止工作了；更糟糕的是，你每晚处理 1000 万条记录的数据摄取任务失败，整个系统陷入瘫痪。一段错误的 SQL 或一次表结构变更可能给数据库系统带来的问题清单令人恐惧。而且你知道吗，这种情况时有发生。

Part of the problem is that, traditionally, SQL has been treated as a collection of isolated scripts rather than as a software project. If this sounds all too familiar, the folks behind dbt think they have a solution.

问题的一部分在于，传统上 SQL 被视为一组孤立的脚本，而不是一个软件项目。如果这听起来很耳熟，那么 dbt 的开发团队认为他们有一个解决方案。

What is dbt?

什么是 dbt？

dbt (data build tool) was created in the mid 2010s by a group now known as dbt Labs. It grew from an internal analytics workflow into a widely used open-source, free (on the developer plan) CLI tool called dbt Core, alongside a fully managed, paid version called dbt Platform. I’ll be using the free version.

dbt (data build tool) 由现在被称为 dbt Labs 的团队在 2010 年代中期创建。它从一个内部分析工作流发展成为一种广泛使用的开源、免费（开发者计划）的命令行工具，即 dbt Core，此外还有一个完全托管的付费版本，称为 dbt Platform。我将使用免费版本。

dbt is used to transform data already stored in a database, warehouse or lakehouse. It does this by creating tables or views based on user-supplied SQL, but it also handles the following:

  • Testing data quality
  • Documenting datasets and lineage
  • Reusing SQL through macros
  • Managing development, testing and production environments
  • Running transformations through scheduled jobs or CI/CD pipelines

dbt 用于转换已经存储在数据库、数据仓库或数据湖仓中的数据。它通过基于用户提供的 SQL 创建表或视图来实现这一点，同时还处理以下任务：

  • 测试数据质量
  • 记录数据集和血缘关系
  • 通过宏（macros）重用 SQL
  • 管理开发、测试和生产环境
  • 通过定时任务或 CI/CD 流水线运行转换

dbt is widely used by teams operating enterprise-grade data storage platforms such as Snowflake, BigQuery, Redshift and Databricks. But for my examples, I’m going to be using a local DuckDB database.

dbt 被运营企业级数据存储平台（如 Snowflake、BigQuery、Redshift 和 Databricks）的团队广泛使用。但在我的示例中，我将使用本地的 DuckDB 数据库。

Why do data teams use dbt?

为什么数据团队使用 dbt？

Mainly because it’s good at what it does. Imagine you’re building a sales reporting platform. Raw order data lands in your data warehouse every hour, say. You write one SQL script to clean the data, another to calculate customer totals, another to build daily sales figures, and another to generate executive dashboards. At first, the project has four or five SQL files, and it’s easy to keep track of them. Six months later, there are fifty, and the order in which they run is no longer obvious.

主要是因为它在处理这些任务时非常出色。想象一下你正在构建一个销售报告平台。假设原始订单数据每小时进入你的数据仓库。你编写一个 SQL 脚本来清洗数据，另一个来计算客户总额，再一个来构建每日销售额，还有一个来生成高管仪表板。起初，项目只有四五个 SQL 文件，很容易跟踪。六个月后，文件增加到了五十个，它们的运行顺序不再显而易见。

Which script runs in which order? What breaks if someone renames a column? How do you check that the data is still valid? Could a new developer understand the project without opening every SQL file? Often, analytics teams solved these problems with naming conventions, handwritten notes passed around and a lot of shared systems knowledge.

哪个脚本先运行？如果有人重命名了一列，什么会坏掉？你如何检查数据是否仍然有效？新加入的开发人员能在不打开每个 SQL 文件的情况下理解项目吗？通常，分析团队通过命名规范、手写笔记传递以及大量的共享系统知识来解决这些问题。

As organisations became more data-driven, analytics projects started looking more and more like software projects. Teams needed version control, automated testing, documentation and dependency management because they were writing thousands of lines of SQL. Rather than treating SQL scripts as independent files, dbt treats them as components of a single project, where every transformation has a defined purpose, and every dependency is understood.

随着组织变得更加数据驱动，分析项目开始越来越像软件项目。团队需要版本控制、自动化测试、文档和依赖管理，因为他们正在编写成千上万行的 SQL。dbt 不再将 SQL 脚本视为独立文件，而是将它们视为单个项目的组件，其中每个转换都有明确的目的，并且每个依赖关系都是可理解的。

Prerequisites

前置条件

I’m using Windows as my operating system and have Python 3.13 installed. Everything should work in the same way if you’re on Linux or macOS but you definitely need to have Python installed. You’ll also need access to a suitable database for dbt to act on. Each database will have differences in how you set it up to use dbt. I’ll be using DuckDB as my database and will show you the set up for that. Consult the dbt docs (linked at the end) if you’re using a different data store.

我使用的是 Windows 操作系统，并安装了 Python 3.13。如果你使用的是 Linux 或 macOS，一切操作方式应该相同，但你必须安装 Python。你还需要访问一个合适的数据库供 dbt 操作。每个数据库在设置使用 dbt 的方式上会有所不同。我将使用 DuckDB 作为我的数据库，并向你展示其设置方法。如果你使用其他数据存储，请查阅 dbt 文档（链接在文末）。

Installing dbt

安装 dbt

Now that we have a better understanding of dbt, in the rest of this article I’ll show you how to install it and, by way of example code, demonstrate the most common dbt commands you’ll use in your day-to-day job. The first thing we should do is set up a separate Python development environment to keep our projects siloed.

现在我们对 dbt 有了更好的了解，在本文的其余部分，我将向你展示如何安装它，并通过示例代码演示你在日常工作中会用到的最常见的 dbt 命令。我们首先要做的是建立一个独立的 Python 开发环境，以保持项目的隔离。

PS C:\Users\thoma> cd projects
PS C:\Users\thoma\projects> mkdir dbt-demo 

Directory: C:\Users\thoma\projects

Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
d-----         03/08/2026   16:21                dbt-demo

PS C:\Users\thoma\projects> cd dbt-demo
PS C:\Users\thoma\projects\dbt-demo> python3 -m venv .venv
...
PS C:\Users\thoma\projects\dbt-demo> .\.venv\Scripts\Activate.ps1
(.venv) PS C:\Users\thoma\projects\dbt-demo>

You can install dbt using a simple pip command… 你可以使用简单的 pip 命令安装 dbt……

← 返回新闻列表 / Back to news list