My company started using Looker a couple years ago, and currently are using LookML PDTs for transformations. As our project has grown more complex, and we have had interest from finance team to directly query our data warehouse, it has become clear that we should have a dedicated modeling layer outside of Looker, and DBT seems to be great option. It seems like DBT and Looker are used together frequently, but have not found many detailed examples of strategies to best set up dbt models for use with looker. I found the post on how DBT structures their projects here, but looking for some more insight on how would work specifically with Looker.
For concrete example, lets say my company name is “flatcloud” and we have a “transactions” PDT in Looker that is based on an internal transactions table (extracted and loaded into bigquery through stitch), and a “saleforce_account” view (not a PDT) based on a table in bigquery. The “transactions” PDT does some transformation of the foreign keys, including adding a salesforce_account_id. Then in Looker we have a “flatcloud_transactions” explore that joins in the salesforce_account view. In this situation, when modeling in DBT, should it look somewhat like below?
├── dbt_project.yml └── models ├── marts | ├── salesforce_accounts.sql | └── flatcloud_transactions.sql | └── schema.yml └── staging └── salesforce | └─ base | | ├── schema.yml | | └── base_salesforce_accounts.sql | └── source.yml └─ flatcloud └─ base | ├── schema.yml | └── base_flatcloud_transactions.sql ├── source.yml ├── schema.yml └── stg_flatcloud_transactions.sql
- so each view in LookML view should be based on model in
- I see many people designating models as
dimensiontables, i have high level understanding of fact/dimension modeling, but not currently designated as such in our LookML modeling, is this highly recommended and how would that work with LookML in this example?
- because we are not doing any transformations on salesforce account (just field renaming), is it fine to not have
- Do we really need
stg_flatcloud_transactionsmodel or can we just do the transformation of foreign keys in the
Any insight would be very helpful!