Name: 增量模型（費薩爾-沙米） (Incremental Models (Faisal El Shami))
Uploaded: 2025-03-22T20:03:40.000Z
Duration: 11 min 5 s

情態副詞

I started my data career at TIER as a data analyst, and I slowly progressed into an analytics engineer.

我在 TIER 的數據職業生涯是從數據分析師開始的，後來慢慢晉升為分析工程師。

I work heavily with dbt, which I really love.

Also focus a lot on operational analytics, a lot of stuff around fleet availability and operations outside on the streets and a little fun fact about me is I always say good morning, no matter what time it is.

我還非常關注營運分析、車隊可用性和戶外營運方面的很多事情，關於我的一個小趣事是，無論什麼時候，我都會說早安。

So I want to speak about the table of contents of the things that I'll be discussing today.

所以，我想說說我今天要討論的事情的目錄。

So first I will discuss about what is an incremental model?

那麼，首先我將討論什麼是增量模型？

When do we use incremental models when not to use incremental models and basically some additional tips and tricks, maybe some small definitions around that, and then at the end, how we use incremental models at TIER.

我們什麼時候使用增量模型，什麼時候不使用增量模型，基本上是一些額外的技巧和竅門，也許還有一些圍繞這些技巧和竅門的小定義，最後是我們如何在 TIER 使用增量模型。

So incremental models are built as tables in your data warehouse.

是以，增量模型就像數據倉庫中的表格一樣建立起來。

So as opposed to being materialized as a view, and then what basically happens when you load or when dbt runs the model for the first time, the next time you tell dbt what to filter for.

是以，與作為視圖物化，然後在加載或 dbt 首次運行模型時基本上會發生的情況相反，下一次你告訴 dbt 要過濾什麼。

So what roles should it transform instead of going and transforming the whole data set?

那麼，它應該轉換哪些角色，而不是去轉換整個數據集呢？

And basically this is really nice because this reduces the build time and maybe the computational complexity or heaviness when you're transforming the new records.

從根本上說，這樣做非常好，因為它減少了構建時間，也許還減少了轉換新記錄時的計算複雜性或繁重性。

Of course, when you build incremental models, this requires a bit of extra configuration on your side and you should be a bit careful and incremental models work really good with event style data.

當然，當你建立增量模型時，這需要你進行一些額外的配置，你應該小心謹慎，增量模型與事件風格的數據一起使用效果非常好。

So if you look at the right on the table, you have the source data on the left side and you add an incremental filter, which I will show you how to do later that defines, for example, show me all the data that comes in these two dates, and then dbt will go on to transform only these roles and then add them to the destination table.

是以，如果你看一下右邊的表格，左邊是源數據，然後添加一個增量過濾器，稍後我會向你演示如何定義，例如，向我顯示這兩個日期中的所有數據，然後 dbt 將繼續只轉換這些角色，然後將它們添加到目標表中。

If I want to make it really simple, it's basically to save on time and to save on money and resources.

如果我想說得簡單一點，基本上就是為了節省時間，節省資金和資源。

So basically this stems to all the other reasons.

是以，這基本上是所有其他原因的根源。

So if your source data is huge or your models are taking too long to run, or maybe you're having really heavy computations that are happening there, then incremental models might be the way.

是以，如果你的源數據非常龐大，或者你的模型運行時間太長，或者你正在進行非常繁重的計算，那麼增量模型可能是個不錯的選擇。

Your historical data does not change a lot.

As I mentioned before, event style data is really good for that.

正如我之前提到的，事件式數據在這方面確實很有優勢。

And basically if your models want to be updated frequently, so if you have a lot of models that are getting updated every hour or two, you have them on dbt to be run every hour or two, then maybe using incremental models is the way to go.

基本上，如果你的模型需要經常更新，那麼如果你有很多模型每一兩個小時更新一次，你把它們放在 dbt 上，每一兩個小時運行一次，那麼使用增量模型也許是個好辦法。

And then when not to use incremental models, technically it's the opposite of what I've mentioned.

至於何時不使用增量模型，從技術上講，與我提到的正好相反。

So if your source data is for that extra configuration, if your data is consistently changing and you need to update it, maybe incremental models is not the way.

是以，如果您的源數據是額外配置的數據，如果您的數據一直在變化並且需要更新，那麼增量模型可能不是辦法。

If your models rely heavily on window functions, for example, then incremental models won't work.

例如，如果您的模型嚴重依賴窗口函數，那麼增量模型就無法運行。

It's not necessary not to apply incremental models, but it's just a bit more complex and maybe it's better avoided because you have to integrate some more complex logic or some more steps into the way I'll start off with how to incrementalize a model.

增量模型也不是一定不能用，只是會更復雜一些，也許最好不要用，因為你必須把一些更復雜的邏輯或更多的步驟整合到模型中去。

Just to recap on the incremental filter, there is incremental filter.

簡單說一下增量過濾器，它有增量過濾器。

Basically what it does, it's a macro that assesses a true or false statement.

基本上，它是一個評估真假語句的宏。

So once you write the normal Kaban dbt run model, you have the incremental filter.

是以，一旦編寫了正常的 Kaban dbt 運行模型，就有了增量過濾器。

As you can see on the picture on the right from line 19 and 22, what it tries to check for true is the following.

從右圖第 19 行和第 22 行可以看到，它試圖檢查以下內容是否為真。

So number one is the configuration of the model.

If you look at materialized, it should be a materialized as incremental, right?

如果你看實體化，它應該是實體化的增量，對嗎？

The second thing is that you have a destination table.

And then when you try to configure it, you can also define a unique key.

然後，當你嘗試配置時，也可以定義一個唯一密鑰。

Unique key looks at the newly transformed records.

So the ones under the incremental filter, and then it looks back at the table that you already have and it sees if they're matching and if they're matching, then it will update the existing data on the table.

是以，在增量過濾器下的那些數據，然後它會回看你已有的表，看看它們是否匹配，如果匹配，就會更新表上的現有數據。

So it's very simple to apply incremental models.

是以，應用增量模型非常簡單。

So if you look here on the right, this is a very simple select statement from a source, from a table.

所以，如果你看右邊這裡，這是一個非常簡單的選擇語句，來自一個源，來自一個表。

We've, we're done with our configuration at the end on the where statement.

我們在 where 語句的最後完成了配置。

I just did a incremental clause and this is the syntax of how you do it.

我剛做了一個增量子句，文法是這樣的。

If the timestamp is greater than or equal to select the maximum timestamp from this model.

如果時間戳大於或等於，則從該模型中選擇最大時間戳。

This here refers to the already existing destination table or this model, not the source data, so this is basically it.

這裡指的是已經存在的目標表或此模型，而不是源數據，所以基本上就是這樣。

我現在就來介紹一下這方面的小竅門。

也許還可以在這裡澄清一些事情。

This again, as I mentioned, this refers to the model you're actually working on or the destination table.

正如我提到的，這裡指的是你正在處理的模型或目標表。

If you're using a lot of window functions, you might maybe consider that you have one step that basically loads the data incrementally.

如果您使用了大量窗口函數，您也許可以考慮使用一個步驟來逐步加載數據。

And then you have another model that run these window functions.

然後，你還有一個運行這些窗口功能的模型。

The unique key does not ensure uniqueness in certain cases.

在某些情況下，唯一密鑰並不能確保唯一性。

You don't need to add the unique key because it can be a bit more computationally expensive.

你不需要添加唯一鍵，因為它的計算成本會更高一些。

You have to think sometimes about if you have a lot of CTEs, where should you put the incremental filter?

有時你必須考慮，如果有很多 CTE，應該把增量過濾器放在哪裡？

Because it can impact the performance of your model.

Basically this runs the incremental model.

基本上，這就是運行增量模型。

是以，無需再做其他任何事情。

If you, for example, added the new column, or you want to fully refresh the whole table, like we run the whole table, we run the computations and so on.

例如，如果你添加了新列，或者你想完全刷新整個表，就像我們運行整個表一樣，我們運行計算等。

You can run a command, which is the one you see at the bottom of the presentation, dbt run full refresh, select my incremental model.

你可以運行一條命令，也就是你在演示文稿底部看到的那條命令：DBT 運行完全刷新，選擇我的增量模型。

So now I want to speak about the usages at tier.

So here at tier, we have around more than 400 dbt models out of which around 22% are incremental.

是以，在 Tier，我們有大約 400 多個 dbt 模型，其中大約 22% 是增量模型。

And on the right, this is just a graph on Looker.

We use the dbt API to get some stats and some data around dbt.

我們使用 dbt 應用程序接口獲取有關 dbt 的一些統計和數據。

If you can see that we have different runs that run sometimes frequently, sometimes an hourly, sometimes more than an hour, sometimes at night.

如果你能看到，我們有不同的運行方式，有時頻繁運行，有時每小時運行一次，有時超過一小時，有時在夜間運行。

Also, we have a lot of frequent models that run that way.

此外，我們還有很多頻繁運行的模型。

And a lot of them are incremental to save on time and resources, as I mentioned.

正如我提到的，其中很多都是漸進式的，以節省時間和資源。

And we also run full refreshes on certain models, certain incremental models during the weekends.

我們還在週末對某些機型、某些增量機型進行全面刷新。

These are some of my models that I work with.

So I have a model that really has more than a billion rows.

是以，我的模型確實有超過十億行。

Not computationally expensive, just a lot of case ones and unions.

計算成本並不高，只是需要大量的 "1 "和 "聯合"。

And if I run it normally, or if I run it as a full refresh, it takes around 55 minutes to run.

如果我正常運行，或者以完全刷新的方式運行，運行時間大約需要 55 分鐘。

But now the incremental model, I think I have a period of 24 hours to refresh data.

但現在的增量模型，我想我有 24 小時的時間來刷新數據。

And I have another model that's the same, a bit more computationally expensive or complex, let's say.

我有另一個相同的模型，比方說，在計算上更昂貴或更復雜一些。

It's taking one minute to run incrementally.

And then I have another model that's a bit smaller than that, but it has a lot of heavy computations.

我還有一個比它小一點的模型，但它有很多繁重的計算。

It's running around 25 minutes normally, but now with incremental models, it runs around three minutes.

正常情況下，運行時間約為 25 分鐘，但現在使用增量模型後，運行時間約為 3 分鐘。

So you can see here, this is really good.

所以你可以看到，這真的很不錯。

I want to discuss a few approaches we use it here.

我想討論一下我們在這裡使用它的幾種方法。

First, it's a safe approach, which is the unique key.

首先，它是一種安全的方法，這是獨一無二的關鍵。

是以，我們在這裡定義了一個唯一鍵。

We have the incremental statement and we basically tell the model, Hey, select the late everything that's after the latest timestamp and ensure the unique and ensure that any unique key that's already existed in the destination table can be basically updated.

我們有增量語句，基本上是告訴模型：嘿，選擇最新時間戳之後的所有內容，確保唯一性，確保目標表中已經存在的唯一鍵基本上可以更新。

This is the first safe, straightforward approach.

這是第一種安全、直接的方法。

The second approach is the pre-hook configuration.

As you can see here on the picture on the left, there's a pre-hook at line five, basically a SQL statement that runs before the model runs.

如左圖所示，第五行有一個預掛鉤，基本上是在模型運行前運行的 SQL 語句。

我們在這裡使用了增量過濾器。

So when it runs, we have this running and this is what it basically does.

是以，當它運行時，我們有這個運行，這就是它的基本功能。

It, it starts the model, it deletes 24 hours of data, and then it incremental logic pulls another 24 hours of data to process it.

它啟動模型，刪除 24 小時的數據，然後通過增量邏輯再提取 24 小時的數據進行處理。

This is not a really good way to do things, but sometimes you have to do it because of certain limitations you have or the way the data is.

這並不是一個很好的方法，但有時因為某些限制或數據的方式，你不得不這樣做。