dbt run fails after first run

[hiltercoty]

Hello,

I'm new in dbt world but I've been using Athena since it's inception. Maybe it's a silly question but I only can execute dbt run just for once.

$ dbt run       
09:12:14  Running with dbt=1.2.1
09:12:14  Unable to do partial parsing because profile has changed
09:12:15  Found 2 models, 4 tests, 0 snapshots, 0 analyses, 245 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
09:12:15  
09:12:18  Concurrency: 1 threads (target='dev')
09:12:18  
09:12:18  1 of 2 START incremental model coty_dbt.my_first_dbt_model ..................... [RUN]
09:12:21  1 of 2 OK created incremental model coty_dbt.my_first_dbt_model ................ [OK -1 in 3.16s]
09:12:21  2 of 2 START view model coty_dbt.my_second_dbt_model ........................... [RUN]
09:12:23  2 of 2 OK created view model coty_dbt.my_second_dbt_model ...................... [OK -1 in 1.67s]
09:12:23  
09:12:23  Finished running 1 incremental model, 1 view model in 0 hours 0 minutes and 8.20 seconds (8.20s).
09:12:23  
09:12:23  Completed successfully
09:12:23  
09:12:23  Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

It fails like this after that:

$ dbt run
09:12:47  Running with dbt=1.2.1
09:12:47  Found 2 models, 4 tests, 0 snapshots, 0 analyses, 245 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
09:12:47  
09:12:50  Concurrency: 1 threads (target='dev')
09:12:50  
09:12:50  1 of 2 START incremental model coty_dbt.my_first_dbt_model ..................... [RUN]
09:12:51  1 of 2 ERROR creating incremental model coty_dbt.my_first_dbt_model ............ [ERROR in 0.03s]
09:12:51  2 of 2 SKIP relation coty_dbt.my_second_dbt_model .............................. [SKIP]
09:12:51  
09:12:51  Finished running 1 incremental model, 1 view model in 0 hours 0 minutes and 3.41 seconds (3.41s).
09:12:51  
09:12:51  Completed with 1 error and 0 warnings:
09:12:51  
09:12:51  Compilation Error in model my_first_dbt_model (models/example/my_first_dbt_model.sql)
09:12:51    When searching for a relation, dbt found an approximate match. Instead of guessing 
09:12:51    which relation to use, dbt will move on. Please delete coty_dbt.my_first_dbt_model, or rename it to be less ambiguous.
09:12:51    Searched for: coty_dbt.my_first_dbt_model
09:12:51    Found: coty_dbt.my_first_dbt_model
09:12:51    
09:12:51    > in macro load_cached_relation (macros/adapters/relation.sql)
09:12:51    > called by macro load_relation (macros/adapters/relation.sql)
09:12:51    > called by macro materialization_incremental_athena (macros/materializations/models/incremental/incremental.sql)
09:12:51    > called by model my_first_dbt_model (models/example/my_first_dbt_model.sql)
09:12:51  
09:12:51  Done. PASS=0 WARN=0 ERROR=1 SKIP=1 TOTAL=2

I've tested dbt with some other connectors like Postgres and Spark, all work fine. They can rebuild the models all the time.

I've also tested different configurations like insert_overwrite but didn't work either.

{{ config(
    materialized='table',
    incremental_strategy='insert_overwrite',
) }}

What am I missing here? Do we need to delete all the tables/views manually before applying dbt run?

Thanks.

[grhaonan]

I am facing the same issue with 1.0.1 dbt-athena and 1.0.8 dbt

Update:
My issue was not exactly the same as reported by @hiltercoty but the error message is same.

In my case, the root cause is that it seems the adapter doesn't support upper case well so I changed all model names, and tables name in sources.yml and {{re()}} to lower case and now the issue is gone.

[raghub1]

I'm facing the issue but the sql model file what is being provided for the dbt to run is being overwritten and it is also not being partitioned.

the original model sql:

 {{
            config(
                partitioned_by = ['year', 'month', 'day']
            )
            }}
            select *, 
            coalesce(year(quote_date), 1970) as year,
            coalesce(month(quote_date), 1) as month,
            coalesce(day(quote_date), 1) as day
            from (

before the first run execution when it ran successfully, the next run gets failed because now the model sql file gets turned into:

create table
    gst_prod.overall_ent_sales_data

    with (
        format='orc'
    )
  as      
            select *, 
            coalesce(year(quote_date), 1970) as year,
            coalesce(month(quote_date), 1) as month,
            coalesce(day(quote_date), 1) as day
            from (

why is the config part being removed the original file and also why is the file being updated ? is there a process that is updating it behind the scenes ?

[hiltercoty]

I tested the same thing today and it's magically working but that's really weird. You can close this if you want. Thanks.