Channels
e
Eugene Krall
12/22/2021, 2:27 PMI've exported Airbyte normalization dbt models, edited them a little, pushed them to github repository and am trying to make them run during sync. getting this error
Database Error in model messages (models/airbyte_incremental/whatsapp/messages.sql)Syntax error: Expected "(" or keyword SELECT or keyword WITH but got keyword CREATE at [9:5]Hi @Eugene Krall do you mean the changes you made are not used by the custom transformation and you thing the basic normalization is still used and is producing this error?
Nope, my custom transformation are applied but they fail and it looks like I understand why. The generated models I extracted from Airbyte contain lines with "create" and "replace" statements, while "classic" dbt models don't allow statements like that. So obviously I extracted the compiled code but as of yet I haven't been able to figure out where these line go if not in dbt models
Copy code
create or replace view `link-179609`._airbyte_whatsapp.`messages_stg`u
[DEPRECATED] Marcos Marx
12/22/2021, 8:08 PM
If you export the Normalization project I recomend to you not edit the basic structure of the project; only the casting types or renaming columns. Looks you edit a SQL file and forgot to add a
(r
Remi Salmon
12/22/2021, 9:25 PMThe query runs fine in BQ interface, I guess the problem is that dbt doesn't expect create statements in models. Judging by the documentation, the models should only contain SELECT statements. It indeed adds create statements with additional info like partitioning and clustering during the runtime.
Should probably mention that I'm trying to apply exported models as my custom transformations
There is a chance I exported the wrong files. Is there probably an example of the right ones. Mine look like already compiled SQL
Here's an example:
Copy code
create or replace table `link-179609`.whatsapp.`messages`
partition by timestamp_trunc(_airbyte_emitted_at, day)
cluster by _airbyte_unique_key, _airbyte_emitted_at
OPTIONS()
as (
-- Final base SQL model
-- depends_on: `link-179609`.whatsapp.`messages_scd`
select
_airbyte_unique_key,
_id,
text,
type,
price,
bot_id,
status,
is_paid,
user_id,
campaign,
currency,
direction,
contact_id,
created_at,
updated_at,
channel_data,
reject_reason,
price_country_code,
_airbyte_ab_id,
_airbyte_emitted_at,
CURRENT_TIMESTAMP() as _airbyte_normalized_at,
_airbyte_messages_hashid
from `link-179609`.whatsapp.`messages_scd`
-- messages from `link-179609`.whatsapp._airbyte_raw_messages
where 1 = 1
and _airbyte_active_row = 1
);these lines don't look like something that should go into a dbt model
Copy code
create or replace table `link-179609`.whatsapp.`messages`
partition by timestamp_trunc(_airbyte_emitted_at, day)
cluster by _airbyte_unique_key, _airbyte_emitted_at
OPTIONS()u
[DEPRECATED] Marcos Marx
12/23/2021, 2:06 AM
This is a file created by normalization right? If you have a better way to express this in dbt could you transfer as a new issue in the project? This will help us improving this module 😄
d
developersteve
12/23/2021, 10:41 AMSo I finally figured that I extracted the wrong models (those were compiled ones instead of the "raw" ones. But now I'm having this issue. All I did was extract normalizations from Airbyte and applying them as custom ones (by copying them into my github repo without any changes), just to check that they would apply correctly
Copy code
Additional properties are not allowed ('dispatch' was unexpected)Hi Eugene, wish DBT image are you using for your custom transformation, our basic normalization runs with
fishtownanalytics/dbt:0.21.1fishtownanalytics/dbt:0.19.1
looks like this one
yes please try 0.21.1 and check if you have the same problem
should I just edit the input? I suppose airbyte will update the version automatically?
Copy code
dbt found 1 package(s) specified in packages.yml, but only 0 package(s) installed in /tmp/dbt_modules. Run "dbt deps" to install package dependencies.that's what I get after changing the version
looks like there is a missing dependency, but airbyte runs it's own normalizations without any errors. Should I install the dependencies like the error message suggests or there's a deeper problem?
@Chris (deprecated profile) do you have an idea of what's happening here? 😄
I've tried to run the models in dbt cloud, installed dependencies and everything worked fine, but still don't understand why those dependancies are missing in airbyte since it can run it's own normalizations just fine (which are indentical to the exported ones applied as custom normalizations)
c
Chris (deprecated profile)
12/23/2021, 1:47 PM
Airbyte uses docker images for normalization produced by airbyte so it already includes all required dependencies and the correct dbt version already
If you re going to use the generated dbt project with a different docker image (official from dbt) then you need to use the correct dbt version for your dbt project file (0.21.0+) and also install dbt depemdencies by running 'dbt deps'
Make sure to change the modules path, marcos did a video to explain that if you use it in the custom transformations
d
developersteve
12/23/2021, 1:57 PMWould love to see the video, but can't find it
Thanks. It helped. Everything worked like a charm!
trying to modify a model so it extracts data from a nested json object. my idea was to change that
Copy code
json_extract_scalar('_airbyte_data', ['campaign'], ['campaign']) }} as campaign Copy code
json_extract_scalar('_airbyte_data', ['campaign']['messages'], ['campaign']['messages']) }} as campaign,