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

0

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.

answered Jan 29, 2024 at 1:42

Sushma Jeerahalli

925 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1