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.
Now it allows me to usen Trend, but is unable to recognize the two dates I'm querying:
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.
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:
@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:
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.
@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.