dbt_utils.pivot function fails on empty table

[aadityahippo]

Describe the bug

dbt_utils.pivot function does expect at least one record to be populated in the source table. If there is no record in source table, this function fails with a nonetype object error as it does not found values to loop through.

Steps To Reproduce

Use dbt_utils.pivot function/macro for an empty table and do the run.

Expected behavior

Function should silently pass with no records generated. It should not fail.

System information

Which database are you using dbt with?

• postgres
• redshift
• [ *] bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.17.0
   latest version: 0.18.0

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

Plugins:
  - bigquery: 0.17.0
  - snowflake: 0.17.0
  - redshift: 0.17.0
  - postgres: 0.17.0

The operating system you're using:
Mac

The output of python --version:
Python 3.7.3

[jtcohen6]

@aadityahippo I'm going to transfer this issue to the dbt-utils repository, where the code defining dbt_utils.pivot exists.

[sungchun12]

I can take this on!

[sungchun12]

@aadityahippo I imagine you get an error like in the screenshot below.

What's the use case for silently passing without error? Would it be more helpful to raise an error message? See example below.

Compilation Error in model dim_suppliers_empty_pivot (models/marts/core/dim_suppliers_empty_pivot.sql)
  Cannot pivot an empty table/view. Ensure the upstream dependency has populated rows.
  
  > in macro pivot (macros/sql/pivot.sql)
  > called by macro default__pivot (macros/sql/pivot.sql)
  > called by model dim_suppliers_empty_pivot (models/marts/core/dim_suppliers_empty_pivot.sql)

[AzeemIqbal]

Hi @sungchun12

I would expect dbt-utils to behave similarly to any other model created with dbt, in that if you try to create a model from another model/table, which is already empty, the resultant model should be an empty table.

I am running 100s of dbt models across multiple environments, some of them will, and are expected to produce empty tables where certain scenarios are not met. We only expect dbt to fail in the case of SQL/DB errors. Raising an error here marks our dbt task as failed within the airflow DAG, and we do not continue on to downstream tasks.

Please could you help me here and provide some method for us to use the handy dbt_utils.pivot function, but expect to deal with empty tables.

[tivoo]

Any update on this? Running in to the same issue with my models and would just want the function to create an empty table instead of give a compilation error. Thanks in advance.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.

[karthik-viswanathan-alludo]

Hi all, did we find a fix for this. I am encountering this issue and would appreciate any help to fix it. Thanks.

[SPTKL]

Is there a solve for this?