Custom column in query editor gives wrong answer for view sourced in dremio but works for same view sourced in SQL

Dremio Cloud
1

Our IT team migrated our tables and views to dremio today and I am switching our data sources from the SQL storage to dremio.

In the query editor, I repeated the applied steps for the new source that I used for the table downloaded from the SQL source:
(Dremio) = Table.AddColumn(vwfactlaborbyhoursummary_Table, “Filter”, each if [totalhours]=0 and [totalcost]=0 then 0 else 1)

vs
(SQL) = Table.AddColumn(dbo_vwFactLaborByHourSummary, “Filter”, each if [totalHours]=0 and [totalCost]=0 then 0 else 1)

The original version correctly gives me 1s and 0s. The new version shows me -1s, 0s, 256s, and 511s in the query editor view. When I click “Load More” in the new column’s drop down menu, it only shows 3 and 256. When I go to the report view, it again only shows 3 and 256 as values in the new custom column.

What is causing this and how do I fix it?

2

@Jillian When you says “migrated tables to Dremio”, do you mean CTAS from SQL source (is this MS-SQL?) which will write ICEBERG files to your configured distributed storage? What is the SQL you are writing? Do you have a job profile?

3

I’m waiting to hear back from my IT person, since I’m not sure what you mean. From my perspective, where before I would bring in tables by going to Get data>>SQL Server, I now go to Get data>>Dremio Cloud, to bring in the table (or view).

4

My IT guy says “I think he thinks we are trying to migrate tables from sql server to dremio but what were really doing is migrating sql server to postgress and THEN youre connecting to postgress via dremio which acts as a semantic layer.”

This is Greek to me, by the way. I have no idea what either of you are talking about, lol.

5

@Jillian What your IT guy is saying makes sense but it does not buy you much as both SQL Server snd Postgres are RDBMS and Dremio does a push down and executes the query on the RDBMS server. Unless the Postgres server is much more powerful. Instead writing the SQL server tables as Iceberg tables or even add a third step of moving doing a CTAS like below would be much more performant

Option1: Create Iceberg table directly from SQL Server

  • Add SQL server source to Dremio
  • Add a Glue/Hive or S3 source to Dremio
create table <glue_source_name>.<table_name> AS select * from <sql_server_table_name>

The above will write the SQL server table in Columnar format (Iceberg). How many tables do you have in SQL server that you want to query using Dremio?

Option 2: Create Iceberg tabes from Postgres

  • Add Postgres source to Dremio
  • Add a Glue/Hive or S3 source to Dremio
create table <glue_source_name>.<table_name> AS select * from <postgres_table_name>

The above will write the Postgres table in Columnar format (Iceberg). How many tables do you have in Postgres that you want to query using Dremio?