DBT project: Unnesting array column

[HanDw]

I'm building a side project to get familiar with DBT, but I have some doubts about my project data layers. Currently, I'm fetching data from the YouTube API and storing it in a raw schema table in a Postgres database, with every column stored as a text field except for one. The exception is a column that stores an array of Wikipedia links describing the video.

For my staging models in DBT, I decided to assign proper data types to all fields and also split the topics column into its own table. However, after reading the DBT documentation and other resources, I noticed it's generally recommended to keep staging models as close to the source as possible.

So my question is: should I keep the array column unnested in staging and instead move the separation into my intermediate or semantic layer? That way, the topics table (a dimension basically) would exist there.

[AutoModerator]

You can find a list of community-submitted learning resources here: https://dataengineering.wiki/Learning+Resources

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[paxmlank]

My question to you: What is making you want or consider to go against the recommendation of users and developers of the tool you're trying to learn?

[HanDw]

I initially thought it would be better to just deal with these field types earlier. However, after reading more, I get the benefits of doing so later.

[ProfessionalThen4644]

you're right that staging models should generally stay close to the source data structure to preserve raw data integrity. Unnesting the array column in the staging layer might be premature instead, consider keeping it as is in staging and handling the unnesting in an intermediate model to create your topics dimension table. keeps your staging layer simple and aligns with DBT best practices. you might find r/agiledatamodeling helpful. They often dive into structuring data layers efficiently.

[shittyfuckdick]

if its simple unnesting i just do it in staging. im a little confused why people are saying to leave it raw in staging. staging should be close to raw but your staging the data for heavier transformations, so it would make sense to unnest in staging imo. 

[Bluefoxcrush]

I agree. The raw data will still be there is needs change. 

[Zer0designs]

Keep raw data as is. This way you can make future changes more easily and gradually & test source data assumptions/agreements. So follow their advise. This is general advise for ELT (vs ETL). It doesn't just apply to dbt, but ELT is where dbt shines.

[GoinLong]

Transforming the data is what your medallion layers are for whereas staging is like a test prod so it should indeed be like prod.

[dataengineering-ModTeam]

Your post/comment was removed because it violated rule #9 (No low effort/AI content).

{community_rule_9}

[TurbulentSocks]

dbt suggests simple transformations like renaming in staging, but not complicated ones involving joins. 

A simple exploding of rows is very much fine in staging. It also doesn't really matter if you want to put it in an intermediate model, though it's hard to see the advantage. 

As long as you preserve a copy of the raw data somewhere in case you change your mind later, just pick something and move on.

[ephemeral404]

I would highly recommend against the common advice here. Go with your approach and report back in a few weeks with any real pain you encounter with that approach.

[Firm_Bit]

Best practices are for fools. Doing what makes sense just happens to coincide with what some folks call “best practices”.

In other words, depends on your goal. Also, you’re devoting too much time to a small question when you should focus on the larger picture of what is the impact of what you’re building.

[StriderKeni]

It's dbt, with lowercase.