Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

generate_surrogate_key macro adds unnecessary empty lines before the code #755

Closed
1 of 5 tasks
akv-akv opened this issue Jan 10, 2023 · 0 comments · Fixed by #756
Closed
1 of 5 tasks

generate_surrogate_key macro adds unnecessary empty lines before the code #755

akv-akv opened this issue Jan 10, 2023 · 0 comments · Fixed by #756
Labels
bug Something isn't working triage

Comments

@akv-akv
Copy link
Contributor

akv-akv commented Jan 10, 2023

Describe the bug

generate_surrogate_key macro adds unnecessary empty lines before the compiled code. These empty lines cause fails of sqlfluff linting.

Steps to reproduce

  1. Create a model with the following code:
SELECT
    1 AS COLUMN_1,
    2 AS COLUMN_2,
    {{ dbt_utils.generate_surrogate_key(['COLUMN_1', 'COLUMN_2']) }} AS PK_ID
  1. Compile this model

Expected results

Compiled code should look like this:

SELECT
    1 AS COLUMN_1,
    2 AS COLUMN_2,
    md5(cast(coalesce(cast(COLUMN_1 as TEXT), '_dbt_utils_surrogate_key_null_') || '-' || coalesce(cast(COLUMN_2 as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) AS PK_ID

Actual results

The actual compiled code looks like this:

SELECT
    1 AS COLUMN_1,
    2 AS COLUMN_2,
    
    
md5(cast(coalesce(cast(COLUMN_1 as TEXT), '_dbt_utils_surrogate_key_null_') || '-' || coalesce(cast(COLUMN_2 as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) AS PK_ID

Screenshots and log output

System information

The contents of your packages.yml file:

packages:
  - package: calogica/dbt_expectations
    version: 0.8.2
  - package: dbt-labs/dbt_utils
    version: 1.0.0
  - package: dbt-labs/codegen
    version: 0.9.0

Which database are you using dbt with?

  • postgres
  • redshift
  • bigquery
  • snowflake
  • other (specify: ____________)

The output of dbt --version:

Core:
  - installed: 1.3.0
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.3.0 - Up to date!

Are you interested in contributing the fix?

Yes, I'm interested in contributing. The local tests showed that the problem can be solved by adding whitespace control to the macro code https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/generate_surrogate_key.sql
If lines 7 to 11 of the macro code look like in the example below dbt compile produce the expected results:

{%- if var('surrogate_key_treat_nulls_as_empty_strings', False) -%}
    {%- set default_null_value = "" -%}
{%- else -%}
    {%- set default_null_value = '_dbt_utils_surrogate_key_null_' -%}
{%- endif -%}
@akv-akv akv-akv added bug Something isn't working triage labels Jan 10, 2023
joellabes pushed a commit that referenced this issue Jan 25, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working triage
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant