Getting DBT and Metabase to work nicely

Hi there,

At my company we’re adopting DBT to structure our queries, and we are transitioning out Tableau in lieu of Metabase.

I have faced a few issues when having this setup:

  1. When a DBT model (which is just a .sql script wrapped in Jinja) is deleted, Metabase does not seem to detect this even after resyncing the database.

  2. I’m also facing issues with getting new DBT models which are materialized as scripts. As with many other community posts I’ve seen here - resyncing doesn’t seem to be allowing me to view the new data tables. Are there common practices regarding fixing these sync issues?

What I expected was manually resyncing would allow me to immediately (or at least within a few minutes…) see the changes I’m making within DBT.

  1. A third issue I faced was dropping a column within my DBT model - resyncing the database for that particular table does not update to the updated columns for that table - not only that, Metabase just doesn’t allow me to access the table as the expected columns are missing. I tried discarding the cached values within the database but wasn’t able to resolve this.

I tried the solution of creating a new DBT model that was the exact same sql script, but renamed to a different name. I expected this to work, but I don’t see my updated table a-n-y-w-h-e-r-e.

Appreciate if anyone can help a newbie out on what I’m probably doing wrong!

1 Like

Hi @shshnkg

Please post “Diagnostic Info” from Admin > Troubleshooting.

I don’t know what DBT is, and it would be helpful to know which database you are querying.

You need to check the log for errors during manual sync - Admin > Troubleshooting > Logs.

And you might need to enable debug logging if you don’t see any errors - example:
java -Dlog4j.configurationFile="https://log4j.us/v2/templates/metabase?trace=metabase.sync" -jar metabase.jar

Hi @flamber,

Just to note, DBT is a method to create views/tables, at its core, it’s simply a method that creates the tables I need. When new tables are created, they are just not appearing on Metabase, but strangely when I query it in the SQL editor on Metabase, I’m actually able to query the tables (which aren’t displaying in the Simple Question mode or Custom Question mode.

Here’s the diagnostic information:

“browser-info”: {
“language”: “en-GB”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.141 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.7+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.7”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.7+10”,
“os.name”: “Linux”,
“os.version”: “4.14.186-146.268.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”
],
“hosting-env”: “unknown”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.7”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-11-16”,
“tag”: “v1.37.2”,
“branch”: “release-x.37.x”,
“hash”: “25e5f70”
},
“settings”: {
“report-timezone”: null
}
}
}

I’m querying from a Postgres database.

Additionally. I’m using the paid hosted instance of Metabase.

Referring to this Metabase help article here on Configuring Logging Level, it seems the method requires me to pass the argument you mentioned above when running Metabase - could I check how I can do that since I’m not hosting it myself?

One of the errors I’m seeing is:

ef7856a7-749d-42d5-bb8a-fc0a0dc9f514] 2021-01-11T12:07:48+08:00 ERROR metabase.sync.util Error fingerprinting Table 257 ‘dbt_shashank.er__answers_base’
org.postgresql.util.PSQLException: ERROR: could not write to hash-join temporary file: No space left on device

Could this be why I cannot see my updated tables?

@shshnkg That sounds like the problem. Now we just need to figure out why this is happening, since I’m unsure if the Postgres error comes from your database or the hosted application database, so can you check your side and I’ll make sure that we check our side later today?

Basically when sync doesn’t work, then it’s not possible to use the GUI, but it’s possible to use SQL, since that is sending queries directly to your database.

Thanks @flamber, yes I’ll look into it.

Hope it resolves through this troubleshooting!

Hi @flamber, thanks for taking the time to think through this yesterday.

For anyone else out there experiencing this in the future using postgres, do check if your DBT model is stuck in any process - and kill those processes using SELECT pg_cancel_backend(pid)

There were some Metabase processes that were also running for nearly 15 days, that I also killed - which helped to free up disk space/CPU utilisation.

1 Like

@shshnkg It would be very interesting to know which query those 15 days old processes were running. That would help us fixing that problem, so it doesn’t happen in the future.

@flamber Unfortunately I didn’t capture a screenshot before I killed the process - if a similar issue reemerges I will try my best to document it.

(Unless you know of a method to check history of a pid in postgres!)

@shshnkg Fairly sure it doesn’t keep the history of processes unless you are specifically logging that, since that would probably cause a lot of logging (normally only needed during debugging).

1 Like