Incremental PDTs?

I’ve spent a lot of time talking to people who have implemented incremental “SQL pipelines” using DBT, Airflow, and homemade systems. My main takeaways are:

1. Building incremental transforms is hard. Don’t do it until you actually have a performance problem.


2. All of the correct pipelines that I’ve seen worked the same way: partition the data on a date column. Every time the pipeline is run, rebuild all the partitions that have changed.

Here’s a toy example of what I’m talking about: suppose we have the following tables:

• 
  events (time, kind) raw table of events


• 
  hourly_totals (date, hour, kind, count) aggregated version of events


• 
  cursor (date) keeps track of progress in converting events into hourly totals
  

begin transaction;



-- Identify all new data, with a resolution of 1 day

create temp table new_data as 

select * from events 

where date(time) >= (select date from cursor);



-- Delete updated dates from hourly_totals

delete from hourly_totals 

where date >= (select date from cursor);



-- Re-materialize the updated dates

insert into hourly_totals

select date(time) as date, extract(hour from time) as hour, kind, count(1) as count

from new_data

group by 1, 2, 3;



-- Advance the cursor

update cursor set date = (select max(date(time)) from new_data);

 

commit;

In theory, if your data arrives in-order, you could implement this pipeline at single-row resolution. The problem is that real pipelines are much more complicated, and in my experience when people try to implement “perfect” incremental updates, they inevitably have correctness problems. Rebuilding the most-recent-partition is a simple trick that gives up a little bit of efficiency, to gain a lot of robustness.

Addendum: if you want to really go down a rabbit-hole of the subtleties of computing SQL queries incrementally, the @julian.hyde, is now at Looker! So he might have some interesting things to add to this discussion 😁

Hi @Dan! This is a great question that we run into very frequently. @George_Fraser–thanks for the shout out and giving a bit of a preview of my answer 😄

I’m one of the maintainers of an open source product called dbt (data build tool). dbt is very similar to Looker’s Persistent Derived Table functionality, but is more sophisticated in a couple of key areas. The one that’s relevant to your question is that dbt natively supports incremental table rebuilds: it’s baked into the core of the tool. As a result, it only requires a couple of lines of code to go from full table rebuilds to incremental rebuilds in most cases.

I linked the relevant section of the docs below, but the short version is that you need to do two things to make a table incremental in dbt:

1. Write a dynamic where condition in the model that performs date limiting. This where condition should limit records to only those that are new or changed since your prior table load.


2. Specify a table unique key.

That’s it: dbt takes care of the rest. It uses the same conceptual approach that @George_Fraser outlines, where updated records are deleted and new data is inserted, but dbt writes all of the inserts and deletes for you. This keeps your code simple, maintainable, and extremely performant (and very much resembles a PDT!).

dbt is starting to see quite a lot of usage in the Looker community; there are over 200 companies using dbt in production as of this writing and many of them are Looker customers. dbt models present a very natural transition for when you’re ready to take the next step beyond PDTs, whether that’s to improve performance with incremental rebuilds, build an explicit DAG, or much more. You can transition PDTs over to dbt models 1:1 just using copy/paste, and then refactor from there. EZPZ.

Docs on incremental dbt models are here. If you run into any trouble, just ping us in Slack.

Any updates on this issue? I think it will be extremely helpful to give the option to build incrementally, the developer can then determine which option better fits the situation.

Thanks @zckymc this looks promising! I will look into it and let you know