search

dbt-labs / dbt-core

dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.
https://getdbt.com
Apache License 2.0
9.64k stars 1.6k forks source link

Guidance for dynamic model (feature?) #1418

Closed rmgpinto closed 5 years ago

rmgpinto commented 5 years ago

Feature

Feature description

We have a series of operational databases that are copied to our data lake. Is there a way to generate a very simple dbt model that loops through every table and creates views with SELECT * FROM <SCHEMA>.<TABLE> ? The benefit of this approach is to avoid managing the model manually, ie, every change in the operational database tables is reflected automatically on the dbt model.

Who will this benefit?

Users with the same architecture as described above.

drewbanin commented 5 years ago

Hey @rmgpinto - are you looking to automate the creation of model files? Or bypass the model files entirely?

It's super important to dbt that these model files are defined in code. Models are the anchor for things like tests and documentation, and without a model resource, you can't really make use of any of these features! Models are one-to-one with database relations, so I don't think dbt will support the use case of a single model that materializes into many different database views any time soon.

If you're looking to automate the creation of model files, I think that's something that you could definitely do in a small python script. This repo might be a helpful starting point for something like that!

Let me know if I have the wrong idea here, happy to discuss further!

rmgpinto commented 5 years ago

I was looking into using jinja with a macro that fetches the table names and looping through them. Perhaps that's too complex and outside of the scope of dbt, as I am new to dbt I was just wondering if there was any way of doing this that I was overlooking. I'll write a small script to generate the files, shouldn't be too hard. Thank you!

drewbanin commented 5 years ago

Cool! Yeah, I think that's a really good solution :+1:

bashyroger commented 4 years ago

I still think that DBT should support this pattern @drewbanin. IMO ETL automation capabilities, generating a series of objects based on metadata is a crucial feature to have for any modern data engineering tool!