Ignore Nulls - Postgres

adrianruhdental commented 1 year ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

It seems the Ignore Nulls feature has been remedied in this update: https://github.com/fivetran/dbt_intercom/pull/25/files

However, it has not been propagated across all scripts in the package. This is causing my transformation to fail in the intercom__conversation_enhanced.sql script

Relevant error log or model output

Database Error in model intercom__conversation_enhanced (models/intercom__conversation_enhanced.sql)
14:57:31    syntax error at or near "ignore"
14:57:31    LINE 120:     first_value(author_id ignore nulls) over (partition by c...
14:57:31                                        ^
14:57:31    compiled Code at target/run/intercom/models/intercom__conversation_enhanced.sql

Expected behavior

Error in the intercom__conversation_enhanced.sql script is preventing the table intercom.intercom__conversation_enhanced table from being created. This is also preventing other tables from being created.

dbt Project configurations

name: 'Intercom_transform'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'Intercom_transform'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

target-path: "target"  # directory which will store compiled SQL files
clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"

# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/ directory
# as tables. These settings can be overridden in the individual model files
# using the `{{ config(...) }}` macro.
models:
  Intercom_transform:
    # Config indicated by + and applies to all files under models/example/
    example:
      +materialized: view

Package versions

packages:

package: fivetran/intercom version: [">=0.4.0", "<0.5.0"]

What database are you using dbt with?

postgres

dbt Version

1.3.0

Additional Context

No response

Are you willing to open a PR to help address this issue?

[ ] Yes.
[X] Yes, but I will need assistance and will schedule time during our office hours for guidance
[ ] No.

fivetran-joemarkiewicz commented 1 year ago

Hi @adrianruhdental thanks for raising this issue and sorry you are experiencing the issue on your end.

You pointed out that in PR #25 we addressed the ignore nulls issue for postgres compatibility with the package. While this is the case, this feature is only available on the most recent v0.5.0 release of the package. I noticed from your issue description that you are using the [">=0.4.0", "<0.5.0"] range. However, you should use the following range instead to capture the postgres updates:

packages:
  - package: fivetran/intercom
    version: [">=0.5.0", "<0.6.0"]

I noticed our latest README instructs you to install [">=0.4.0", "<0.5.0"]. I have just opened Issue #34 for our team to address this.

Let me know if upgrading your version does the trick for you!

adrianruhdental commented 1 year ago

That seems to have done it! Thank you

Sent from Outlook for iOShttps://aka.ms/o0ukef

From: Joe Markiewicz @.> Sent: Monday, November 14, 2022 3:32:33 PM To: fivetran/dbt_intercom @.> Cc: adrianruhdental @.>; Mention @.> Subject: Re: [fivetran/dbt_intercom] Ignore Nulls - Postgres (Issue #33)

Hi @adrianruhdentalhttps://github.com/adrianruhdental thanks for raising this issue and sorry you are experiencing the issue on your end.

You pointed out that in PR #25https://github.com/fivetran/dbt_intercom/pull/25 we addressed the ignore nulls issue for postgres compatibility with the package. While this is the case, this feature is only available on the most recent v0.5.0https://github.com/fivetran/dbt_intercom/releases/tag/v0.5.0 release of the package. I noticed from your issue description that you are using the [">=0.4.0", "<0.5.0"] range. However, you should use the following range instead to capture the postgres updates:

packages:

package: fivetran/intercom version: [">=0.5.0", "<0.6.0"]

I noticed our latest README instructs you to install [">=0.4.0", "<0.5.0"]. I have just opened Issue #34https://github.com/fivetran/dbt_intercom/issues/34 for our team to address this.

Let me know if upgrading your version does the trick for you!

— Reply to this email directly, view it on GitHubhttps://github.com/fivetran/dbt_intercom/issues/33#issuecomment-1313940290, or unsubscribehttps://github.com/notifications/unsubscribe-auth/A2SVKGYHSM767T65OJHFDBDWIJLRDANCNFSM6AAAAAAR4XAKIA. You are receiving this because you were mentioned.Message ID: @.***>

fivetran-joemarkiewicz commented 1 year ago

Brilliant! I will close this issue and work with my team to address the documentation issue highlighted in the new issue linked above. 😄

fivetran / dbt_intercom