Metabase not recognizing BigQuery date column

Hi,

I've connected metabase to a bigquery database and created a dashboard. It was working fine. It is basically a set of trend cards and tables.

Then , yesterday I have decided to delete the table I was querying on BQ and upload a new partitioned table with same name, same fields and same information. After that, all trends in my previously working dashboard stop working and metabase are not recognizing the date column as a date.

Even if the date column is, aparently, correct.

image

I'm using Metabase 0.41

Hi @wellerson
Post "Diagnostic Info" from Admin > Troubleshooting.

What has the exact database column type?

Metabase doesn't have full support for partitioned tables:
https://github.com/metabase/metabase/issues?q=is%3Aissue+is%3Aopen+partitioned+label%3ADatabase%2FBigQuery

Here it is:

{
"browser-info": {
"language": "pt-BR",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.14.1+1",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.14.1",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.14.1+1",
"os.name": "Linux",
"os.version": "4.14.35-1902.300.11.el7uek.x86_64",
"user.language": "en",
"user.timezone": "America/Recife"
},
"metabase-info": {
"databases": [
"h2",
"postgres",
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "12.1"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.2.23"
}
},
"run-mode": "prod",
"version": {
"date": "2022-02-17",
"tag": "v0.42.1",
"branch": "release-x.42.x",
"hash": "629f4de"
},
"settings": {
"report-timezone": "America/Sao_Paulo"
}
}
}

Update:

I've updated my metabase version to 0.42.1

The error has changed, but I believe that is the same root error.

Now it allows me to usen Trend, but is unable to recognize the two dates I'm querying:

image

Also, I do believe that is something to do with partitioned tables. The query that I'm writing in metabase should retrieve data from february 2022 and in metabase is getting January. Query runs correctly in BQ console.

@wellerson Since you are not providing the actual database column type, then it's difficult to say.
Perhaps you're seeing this issue: https://github.com/metabase/metabase/issues/12380
It would require a lot more debugging and troubleshooting from your side to figure out.

Oh, my mistake

The column in BQ is TIMESTAMP.

@wellerson I don't know which timezone you are using:
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type

I have a fact table with column named "id_data" which is joined with a "dim_data" table which is the table that have "data" column being TIMESTAMP type. Like this:

select min(data) as data,	
        sum(a.valor_cmm)
from `dw.fato_faturamento_full` a
    inner join `dw.dim_data`
        using(id_data)
    inner join `dw.dim_centro`
        using(id_centro)
    inner join `dw.dim_material`
        using(id_material)
    inner join `dw.dim_comprador_material`
        on `dw.dim_centro`.id_centro = `dw.dim_comprador_material`.id_centro and `dw.dim_material`.cod_nivel_1 = `dw.dim_comprador_material`.cod_nivel_1 and `dw.dim_material`.cod_nivel_2 = `dw.dim_comprador_material`.cod_nivel_2
    inner join `dw.dim_comprador`
        on `dw.dim_comprador_material`.id_comprador = `dw.dim_comprador`.id_comprador
    left join `dw.dim_fornecedor`
        on `dw.dim_material`.id_fornecedor = `dw.dim_fornecedor`.id_fornecedor
where   1 = 1
        [[and {{data_passado}} --]] and ano = extract(year from current_date - 1) - 1 and mes = extract(month from current_date - 1) and dia <= extract(day from current_date - 1)
        and {{divisao}}
        and {{desc_nivel_1}}
        and {{desc_nivel_2}}
        and {{desc_nivel_3}}
        and {{desc_nivel_4}}
        and {{comprador}}
        and {{nome_gerente}}
        and {{centro}}
        and {{fornecedor}}
        and {{cod_produto}} and {{nome_produto}}
--
union all
--
select  min(data) as data,	
        sum(a.valor_cmm)
from `dw.fato_faturamento_full` a
    inner join `dw.dim_data`
        using(id_data)
    inner join `dw.dim_centro`
        using(id_centro)
    inner join `dw.dim_material`
        using(id_material)
    inner join `dw.dim_comprador_material`
        on `dw.dim_centro`.id_centro = `dw.dim_comprador_material`.id_centro and `dw.dim_material`.cod_nivel_1 = `dw.dim_comprador_material`.cod_nivel_1 and `dw.dim_material`.cod_nivel_2 = `dw.dim_comprador_material`.cod_nivel_2
    inner join `dw.dim_comprador`
        on `dw.dim_comprador_material`.id_comprador = `dw.dim_comprador`.id_comprador
    left join `dw.dim_fornecedor`
        on `dw.dim_material`.id_fornecedor = `dw.dim_fornecedor`.id_fornecedor
where   1 = 1
        [[and {{data_atual}} --]] and ano = extract(year from current_date - 1) and mes = extract(month from current_date - 1) and dia <= extract(day from current_date - 1)
        and {{divisao}}
        and {{desc_nivel_1}}
        and {{desc_nivel_2}}
        and {{desc_nivel_3}}
        and {{desc_nivel_4}}
        and {{comprador}}
        and {{nome_gerente}}
        and {{centro}}
        and {{fornecedor}}
        and {{cod_produto}} and {{nome_produto}}

However, I've tried to replace the "min(data)" function with some pre-defined date, such as:

select timestamp('2021-01-01') as data

And still I get the same error.

I'm using UTC

@wellerson There's no errors. Metabase cannot compare the same date.
Try looking in the BigQuery query log, or adjust timezones manually, since Metabase has no control over SQL generated questions.
https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#timestamp

But if you say that it used to work, then with a normal table, then change it back to that.

@flamber But there is an error. Even if date returned by BQ is not correct because timezone, it still generate data for two times separated by year:

image

Then, trend card should show the first line value compared with second line. However, it doesn't, saying that doesn't have nothing to compare in the previous, even when it have.

image

@wellerson Okay, since it worked before you made changes to your structure, then revert those changes.

Okay. I'll do that.

@flamber it looks like using old table doesn't work. It appears to be a general problem now.

I've create a table to store just some sales data. Then, I changed the table 'dim_data' to store dates as DATE, instead of TIMESTAMP.

Then I performed the following query in metabase:

image

The generated data is:

image

And still, trend won't work:

image

@wellerson You wrote that everything used to work until you made some change. It's impossible for me to know what you have changed, but revert that - or restore to a backup from before yesterday.