Limitations in using all string columns in BigQuery

First of all - I don't really see any bigger show-stoppers than those you already know and enlisted

Meantime,

though wouldn't really matter if using a query-builder ...

based on above excerpt - I wanted to touch upon some aspect of this approach (storing all as strings)

While we usually concerned about CASTing from string to native type to apply relevant functions and so on, I realized that building complex and generic query with some sort of query builder in some cases requires opposite - cast native type to string for applying function like STRING_AGG [just] as a quick example

So, my thoughts are:

When table is designed for direct user's access with trivial or even complex queries - having native types is beneficial and performance wise and being more friendly for user to understand, etc.

Meantime, if you are developing your own query builder and you design table such that it will be available to users for querying via that query builder with some generic logic being implemented - having all fields in string can be helpful in building the query builder itself.

So it is a balance - you can lose a little in performance but you can win in being able to better implement generic query builder. And such balance depend on nature of your business - both from data prospective and what kind of query you envision to support

Note: your question is quite broad and opinion based (which is btw not much respected on SO) so, obviously my answer - is totally my opinion but based on quite an experience with BigQuery

From the solution below, you might face some storage and performance problems, you can find some guidance in the official documentation:

The main performance problem will come from the CAST operation, remember that the BigQuery Engine will have to deal with a CAST operation for each value per row.
In order to test the compute cost of this operations, I used the following query:

SELECT
  street_number
FROM
  `bigquery-public-data.austin_311.311_service_requests`
LIMIT
  5000

Inspecting the stages executed in the execution details we are able to see the following:

READ    
       $1:street_number
       FROM bigquery-public-data.austin_311.311_service_requests
LIMIT   
       5000
WRITE   
       $1
       TO __stage00_output

Only the Read, Limit and Write operations are required. However if we execute the same query adding the the CAST operator.

SELECT
  CAST(street_number AS int64)
FROM
  `bigquery-public-data.austin_311.311_service_requests`
LIMIT
  5000

We see that a compute operation is also required in order to perform the cast operation:

READ    
       $1:street_number
       FROM bigquery-public-data.austin_311.311_service_requests
LIMIT   
       5000
COMPUTE 
       $10 := CAST($1 AS INT64)
WRITE   
       $10
TO __stage00_output

Those compute operations will consume some time, that might cause problems when escalating the operation size.

Also, remember that each time that you want to use the data type properties of each data type, you will have to cast your value, and deal with the compute operation time required.

Finally, referring to the storage performance, as you mentioned Strings do not have a fixed size, and that might cause a size increase.

Are you OK to store string "33/02/2000" as a date in one row and "21st of December 2012" in another row and "22ое октября 2013" in another row?

Are you OK to store string "45" as age in one row and "young" in another row?

Are you OK when age "10" is less than age "9"?

Data types provide some basic data validation mechanism at the database level.

Does BigQuery databases have a notion of indexes?

If yes, then most likely these indexes become useless as soon as you start casting your strings to proper types, such as

SELECT 
    ...
WHERE 
    age > 10 and age < 30

vs

SELECT 
    ...
WHERE 
    ANY_VALUE(SAFE_CAST(age AS INT64)) > 10 
    and ANY_VALUE(SAFE_CAST(age AS INT64)) < 30

It is normal that with less columns/rows you don't feel the problems. You start to feel the problems when your data gets huge.

Major concerns:

• Maintenance of the code: Think of future requirements that you may receive. Every conversion for data manipulation will add extra complexity to your code. For example, if your customer asks for retrieving teenagers in future, you'll need to convert string to date to get the age and then be able to do the manupulation.

• Data size: The data size has broader impacts that can not be seen at the start. For example if you have N parallel test teams which require own test systems, you'll need to allocate more disk space.

• Read Performance: When you have more bytes to read in huge tables it will cost you considerable time. For example typically telco operators have a couple of billions of rows data per month.

• If your code complexity increase, you'll need to replicate conversions in multiple places.

Even single of above items should push one to distance from using strings for everything.

I would think the biggest issue with this would be if there are other users of this table/data, for instance if someone is trying to write reports with it and do calculations or charts or date ranges it could be a big headache having to always cast or convert the data with whatever tool they are using. You or someone would likely get a lot of complaints about it.

And if someone decided to build a layer between this data and the reporting tool which converted all of the data, then you may as well just do it one time to the table/data and be done with it.