Without check constraint how Bigquery validate data?

Question

We don't have application running on server,

so all the logic need to be done on Bigquery.

I know Microsoft SQL Server have check constraint like:

CREATE TABLE MyCleanJapanClient ( Phone varchar(255) CHECK (Phone LIKE '+81%'), Email varchar(255) CHECK (Email LIKE '%_@__%.__%') );

So when I use query like

INSERT INTO `MyCleanJapanClient` SELECT Phone AS Phone, Email AS Email FROM `YourDirtyJapanClient`;

I got expected error The INSERT statement conflicted with the CHECK constraint

But with Bigquery,

how can I validate data like using check constraint?

To the best of my knowledge, there is no equivalent to the T-SQL dialect "CHECK" capability in BigQuery. What you can do is use the Data Quality capabilities and periodically validate/detect whether or not your table rows meet quality considerations. See: cloud.google.com/dataplex/docs/auto-data-quality-overview — Kolban
– Kolban, Commented Jan 26, 2024 at 6:00

Sushma Jeerahalli · Accepted Answer · 2024-01-29 01:42:07Z

As mentioned by @Kolban, there is no equivalent to the T-SQL dialect "CHECK" capability in BigQuery.

You can use the Data Quality capabilities and periodically validate/detect whether or not your table rows meet quality considerations.

For more information you can refer to the following link.

1 Answer 1