CODEWITHSUNDEEP
postgresqldbtdbt-postgres
sazary
sazary

Reputation: 966

Split single run of `dbt materialize` by temporal field and commit them separately

i'm using dbt to materialize a table on a postgresql. this table has a temporal field (timestamp).

the problem is that when this table is being created for the first time dbt runs all of it using a single sql query (as defined in my model) and it gets timed out as it exceeds statement_timeout, and no data is inserted in the table even as a lot of proccessing has been done.

is there any solution which breaks up the data into several steps and commits each step as it gets completed? this way the dbt run would still take a long time but each step is a separate query and it wouldn't surpass statement_timeout

i know about the incremental option of dbt and i use it in next runs, but my problem is about the initial run.

thanks

Upvotes: 0

Views: 145

Answers (1)

L.GAYET
L.GAYET

Reputation: 106

is there any solution which breaks up the data into several steps and commits each step as it gets completed?

Currently there is no dbt native solution to do so. As you said, it will open one transaction to create your whole table. You can have a look at Custom materialisation to code your own behaviour but i guess it will be quite difficult.

i know about the incremental option of dbt and i use it in next runs, but my problem is about the initial run.

I guess you have the right solution using incremental. A little tips maybe to achieve what you want and avoid loading the whole table during the first run is to use a combinaison of LIMIT and ORDER BY, something like that :

{% if is_incremental() %}

  -- this filter will only be applied on an incremental run
  where id::int >= (select max(id) from {{ this }})
  ORDER BY id ASC
  limit 1000

{% else %}

ORDER BY id ASC
LIMIT 1000

{% endif %}

This way, the first time you run your model it will load the 1000 rows, then every next run you will load the next 1000 rows. You can adapt it to your need, its a way to simulate breaking the data into "multiple steps".

Upvotes: 0

Related Questions