Reference model in SQL query: model missing records

#1

When models are referenced in SQL queries using {{#173}} syntax, they drop records from the original model leading to incorrect aggregates.

Here is my original model. If I create a question on top of it using question builder to get a simple count of all records in model, the count is returned as 1.7M. Screenshot below.

image

If I then reference that model (#173) in a SQL query and run the same aggregate count on that same model, I get a count of 1.04M. Screenshot below.

image
image.jpg4050×1706 278 KB

Any idea what might be happening here? There are no filters in place on this model. It is just a direct reference of a table in my DB.

#2

Hi @sam
There's a limit in the 173 model, perhaps just the default max, and since the SQL is including the entire query, then it also includes the limit.
Use database Views instead of Models.

#3

Ok got it. Thanks. One followup question: I noticed that if I reference the model I created in another Metabase question (as opposed to a SQL query), it works fine and the limit doesn't come into play. Is that expected?

I'd like to keep our view layer modeled in metabase as Models to keep it easier to update and manage with the rest of our Metabase data, so want to understand whether there's a path to being able to reference models in SQL queries or whether I'll need to use DB views for that use case.

#4

@sam I don't know how you have created your Model, I would need to see the metadata for it by going to the URL /api/card/173
Nor do I know which database type it is. Include "Diagnostic Info" from Admin > Troubleshooting.

There's different limits. Have a look in your database query log, then you can see what Metabase sends to the database, so you have a better understanding of what is happening.