# Incremental Load - First Run, Check Table Exists/Contains Rows

**URL:** <https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584>\
**Category:** Help\
**Tags:** incremental\
**Created:** [December 13, 2022, 2:06pm UTC](https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584 "2022-12-13T14:06:53Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![markooo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/markooo/32/1373_2.png) [@markooo](https://discourse.getdbt.com/u/markooo)\
**Post date:** [December 13, 2022, 2:06pm UTC](https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584/1 "2022-12-13T14:06:53Z")

</div>

I understand on the first run of an incremental model all source rows will be added and a table created if it doesn’t exist:

> Incremental models are built as tables in your data warehouse. The first time a model is run, the table is built by transforming _all_ rows of source data. On subsequent runs, dbt transforms _only_ the rows in your source data that you tell dbt to filter for, inserting them into the target table which is the table that has already been built.

In my case, the source model contains multiple rows per ID:

| ID | Region | Timestamp |
| --- | --- | --- |
| 1 | X | 01/01/2023 |
| 1 | Z | 02/01/2023 |
| 1 | A | 03/01/2023 |

Within my incremental model I’d like to include a single row per ID that’s associated with the latest timestamp. In the example provided above only the following row would appear in the incremental model:

| ID | Region | Timestamp |
| --- | --- | --- |
| 1 | A | 03/01/2023 |

Each time I run the incremental model I’d like to:

- Check if the SQL table generated by the incremental model already exists on the database
- For robustness, if the SQL table exists is not empty/contains rows

If the table doesn’t exists or the table does not contain any rows the following script would run:

```auto
SELECT a.*
FROM {{ref('orders')}} a
INNER JOIN (SELECT id, MAX(timestamp) AS MAX_timestamp 			
            FROM {{ref('orders')}} 
			GROUP BY id) AS max_a ON a.id = max_a.id

```

I believe the initial checks above would be common design pattern and was wondering if there are any standard design patterns or set of functions to use?

I found the following thread helpful but would like to ask if others have any alternative suggestions/ best practices?

> [@Writing packages when a source table may or may not exist](https://discourse.getdbt.com/t/writing-packages-when-a-source-table-may-or-may-not-exist/1487):
>
> Note — this article is intended for: anyone that writes dbt modeling packages (likely a consultant or vendor) OR anyone who likes seeing fancy things done with dbt winkIn other words: you probably don’t need this. Scenario Let’s say you’re a consultant that’s working with a number of merchants that use the same ecommerce software — Shopiary™. Your clients replicate the data into their warehouses using their favorite EL tool, and most of the time, the tables (and columns within the table…

Any pointers would be much appreciated 👍

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [December 14, 2022, 12:57am UTC](https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584/2 "2022-12-14T00:57:59Z")

</div>

dbt incremental models are the same as any other model type, in that you don’t need to (and shouldn’t!) spend any time thinking about whether the table already exists, and minimal time thinking about whether the table is empty or not.

This manifests as a query that transforms data in the same way whether or not the table exists/contains rows, with the only difference being _how much data is processed_.

Have you looked into the [`is_incremental()`](https://docs.getdbt.com/docs/build/incremental-models#understanding-the-is_incremental-macro) macro? Basically what you’d do is

```sql
with orders as (
  select * from {{ ref('orders') }}
),

windowed as (
  select   
    id, 
    region, 
    timestamp, 
    row_number() over (partition by id order by timestamp desc) as rownum
  from orders

  {% if is_incremental() %}
    -- if the table doesn't exist, then every order row will be processed. 
    -- If it does, then only rows that have changed since the last invocation need to be processed.
    where exists (
      select null   
      from {{ this }} as this
      where this.id = orders.id
      and orders.timestamp > this.timestamp
    )
  {% endif %}
),

final as (
  select 
    id,
    region,
    timestamp
  from orders
  where rownum = 1
)

select * from final

```

The article you linked refers to _source tables_ existing or not, i.e. the raw untransformed tables created by your ETL tool.

---

<div class="post-metadata">

**Author:** ![markooo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/markooo/32/1373_2.png) [@markooo](https://discourse.getdbt.com/u/markooo)\
**Post date:** [December 14, 2022, 5:51pm UTC](https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584/3 "2022-12-14T17:51:05Z")

</div>

Many thanks for this.  
My initial SQL query to populate the incremental model wasn’t in a particularly dbt friendly format.

Your example has been a great help and I can use that as a template in the future. 👍

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [December 21, 2022, 5:51pm UTC](https://discourse.getdbt.com/t/incremental-load-first-run-check-table-exists-contains-rows/5584/4 "2022-12-21T17:51:38Z")

</div>

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.