AAtsushi's Blog
Data Engineering

Data Quality Check with Dataplex to Verify Foreign Key Constraints Are Satisfied

→ 日本語版を読む

Since BigQuery's foreign key constraints are not enforced, users are responsible for ensuring that only values referencing the foreign key target (or NULL) are inserted.

With that in mind, I considered whether Dataplex could be used to periodically verify that values comply with foreign key constraints.

After researching, I found that Dataplex can use SQL row check rules to periodically verify whether column values are limited to the referenced foreign key values.

Below are my notes on the verification results.

First, I created two test tables.

The company_list table has columns for company ID and company name (name). The staff table has columns for staff ID, name, and company name (company_name).

The company_name column in the staff table references the name column in the company_list table as a foreign key.

  • company_list table

  • staff table

Below is the Terraform code for creating the dataset and tables.

resource "google_bigquery_dataset" "default" {
  dataset_id                  = "test_dataset"
  friendly_name               = "test"
  description                 = "This is a test description"
  location                    = "US"
  # default_table_expiration_ms = 3600000
}

resource "google_bigquery_table" "company_list" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "company_list"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    primary_key {
      columns = ["name"]
    }
  }
}

resource "google_bigquery_table" "staff" {
  dataset_id = google_bigquery_dataset.default.dataset_id
  table_id   = "staff"

  schema = <<EOF
  [
    {
      "name": "id",
      "type": "INT64",
      "mode": "NULLABLE"
    },
    {
      "name": "person_name",
      "type": "STRING",
      "mode": "NULLABLE"
    },
    {
      "name": "company_name",
      "type": "STRING",
      "mode": "NULLABLE"
    }
  ]
  EOF

  table_constraints {
    foreign_keys {
      referenced_table {
        project_id  = data.google_project.project.project_id
        dataset_id  = google_bigquery_table.company_list.dataset_id
        table_id    = google_bigquery_table.company_list.table_id
      }
      column_references {
        referencing_column = "company_name"
        referenced_column = "name"
      }
    }
  }
}

Next, create a Dataplex data quality check. In SQL, retrieve the distinct company names from the referenced table and verify that each company name is one of those values.

resource "google_dataplex_datascan" "check_foreign_key_staff_table" {
  location     = "us-central1"
  display_name = "Check if satisfy foreign key"
  data_scan_id = "check-foreign-key-staff-table"

  data {
    resource = "//bigquery.googleapis.com/${google_bigquery_table.staff.id}"
  }

  execution_spec {
    trigger {
      on_demand {}
    }
  }

  data_quality_spec {
    sampling_percent = 100

    rules {
      name = "check-foreign-key"
      column = "company_name"
      dimension = "CONSISTENCY"
      row_condition_expectation {
        sql_expression = <<EOL
          company_name IN (
            SELECT
              DISTINCT name
              FROM ${google_bigquery_table.company_list.project}.${google_bigquery_table.company_list.dataset_id}.${google_bigquery_table.company_list.table_id}
              -- Note: if you don't specify the project name in the FROM clause, you will get a table not found error
          )
        EOL
      }
    }
  }
}

Now let's actually run the data quality scan to verify.

The following data was inserted into each table.

Running the data quality scan in this state succeeded.

Next, I also tried a failure scenario. I inserted a record into the staff table with a company name "Quad.Inc" that does not exist in the company_list table.

Running the data quality scan in this state failed as expected.

Incidentally, I also tried the case where the staff table has 0 records, and it succeeded as expected.

Using Dataplex data quality scans in this way, it seems possible to periodically verify that foreign key constraints are satisfied.

If you know of other good methods for verifying foreign key constraint compliance, please let me know.

That is all.

← Back to articles