Nested fields for BigQuery

[charles-astrafy]

Description

The "lightdash generate" CLI does not handle STRUCT fields in BigQuery. It only creates an entry in the yaml file for the top level field.

What problem does this solve?

It would allow data teams to have complete dbt properties files for their BigQuery tables that contain STRUCT fields. At the time being, those nested fields need to be added manually.

The logic to recursively go through the nested fields and adding those to a yaml file is present in the macro of this codegene package:
https://github.com/dbt-labs/dbt-codegen/blob/main/macros/generate_model_yaml.sql

[TuringLovesDeathMetal]

Thanks for opening this @charles-astrafy !

Have you tested using unnested structs in Lightdash as well? (e.g. if you add a dimension as one of the keys in the struct, does Lightdash complain?).

I have a feeling that we don't actually unnest the structs so they still might appear as dictionaries/containers in a single column...so there might be an issue to solve before this which is handling unnested structs in Lightdash.

[charles-astrafy]

Hello @TuringLovesDeathMetal ,

Structs are not supported by Lightdash and it throws an error when we try to use tables that have structs. Definitely a feature that would be good as well to see implemented.

But here our concern is more about generating the dbt property files correctly even if the struct are not supported yet. We would like the property files to have already the good structure so we can document it. For instance if there is the following struct in BigQuery:

Address:
-city
-zip_code

that the dbt property file generated by lightdash be as follows:

• name: address
  description: "structure containing address information"
• name: address.city
  description: "..."
• name: address.zip_code
  description: "..."

Having Lightdash being then able to handle the structs is I think more complex and would hopefully come in a later iteration.

[stale]

Is this issue still relevant? There have been no updates for 60 days, please close the issue or keep the conversation going!

[crmne]

This is very interesting for us and it is blocking our adoption of lightdash, since we heavily use STRUCTS in our data warehouse.

[yu-iskw]

+1

[tonytusharjr]

+1, I have a client solution relying on structs and this would unlock migrating that client off of Looker Studio.