Basic Validations

Run validations on a column to ensure the values adhere to your requirement. Can be set to complex validation logic via SQL expression as well if needed (see here).

Equal

Ensure all data in column is equal to certain value. Value can be of any data type. Can use isEqualCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("year").isEqual(2021),
validation().col("year").isEqualCol("YEAR(date)"),

validation.col("year").isEqual(2021),
validation.col("year").isEqualCol("YEAR(date)"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "year == 2021"

Not Equal

Ensure all data in column is not equal to certain value. Value can be of any data type. Can use isNotEqualCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("year").isNotEqual(2021),
validation().col("year").isNotEqualCol("YEAR(date)"),

validation.col("year").isNotEqual(2021)
validation.col("year").isEqualCol("YEAR(date)"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "year != 2021"

Null

Ensure all data in column is null.

JavaScalaYAML

validation().col("year").isNull()

validation.col("year").isNull

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "ISNULL(year)"

Not Null

Ensure all data in column is not null.

JavaScalaYAML

validation().col("year").isNotNull()

validation.col("year").isNotNull

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "ISNOTNULL(year)"

Contains

Ensure all data in column is contains certain string. Column has to have type string.

JavaScalaYAML

validation().col("name").contains("peter")

validation.col("name").contains("peter")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "CONTAINS(name, 'peter')"

Not Contains

Ensure all data in column does not contain certain string. Column has to have type string.

JavaScalaYAML

validation().col("name").notContains("peter")

validation.col("name").notContains("peter")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "!CONTAINS(name, 'peter')"

Unique

Ensure all data in column is unique.

JavaScalaYAML

validation().unique("account_id", "name")

validation.unique("account_id", "name")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - unique: ["account_id", "name"]

Less Than

Ensure all data in column is less than certain value. Can use lessThanCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("amount").lessThan(100),
validation().col("amount").lessThanCol("balance + 1"),

validation.col("amount").lessThan(100),
validation.col("amount").lessThanCol("balance + 1"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount < 100"
      - expr: "amount < balance + 1"

Less Than Or Equal

Ensure all data in column is less than or equal to certain value. Can use lessThanOrEqualCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("amount").lessThanOrEqual(100),
validation().col("amount").lessThanOrEqualCol("balance + 1"),

validation.col("amount").lessThanOrEqual(100),
validation.col("amount").lessThanCol("balance + 1"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount <= 100"
      - expr: "amount <= balance + 1"

Greater Than

Ensure all data in column is greater than certain value. Can use greaterThanCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("amount").greaterThan(100),
validation().col("amount").greaterThanCol("balance"),

validation.col("amount").greaterThan(100),
validation.col("amount").greaterThanCol("balance"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount > 100"
      - expr: "amount > balance"

Greater Than Or Equal

Ensure all data in column is greater than or equal to certain value. Can use greaterThanOrEqualCol to define SQL expression that can reference other columns.

JavaScalaYAML

validation().col("amount").greaterThanOrEqual(100),
validation().col("amount").greaterThanOrEqualCol("balance"),

validation.col("amount").greaterThanOrEqual(100),
validation.col("amount").greaterThanOrEqualCol("balance"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount >= 100"
      - expr: "amount >= balance"

Between

Ensure all data in column is between two values. Can use betweenCol to define SQL expression that references other columns.

JavaScalaYAML

validation().col("amount").between(100, 200),
validation().col("amount").betweenCol("balance * 0.9", "balance * 1.1"),

validation.col("amount").between(100, 200),
validation.col("amount").betweenCol("balance * 0.9", "balance * 1.1"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount BETWEEN 100 AND 200"
      - expr: "amount BETWEEN balance * 0.9 AND balance * 1.1"

Not Between

Ensure all data in column is not between two values. Can use notBetweenCol to define SQL expression that references other columns.

JavaScalaYAML

validation().col("amount").notBetween(100, 200),
validation().col("amount").notBetweenCol("balance * 0.9", "balance * 1.1"),

validation.col("amount").notBetween(100, 200)
validation.col("amount").notBetweenCol("balance * 0.9", "balance * 1.1"),

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "amount NOT BETWEEN 100 AND 200"
      - expr: "amount NOT BETWEEN balance * 0.9 AND balance * 1.1"

In

Ensure all data in column is in set of defined values.

JavaScalaYAML

validation().col("status").in("open", "closed")

validation.col("status").in("open", "closed")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "status IN ('open', 'closed')"

Matches

Ensure all data in column matches certain regex expression.

JavaScalaYAML

validation().col("account_id").matches("ACC[0-9]{8}")

validation.col("account_id").matches("ACC[0-9]{8}")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "REGEXP(account_id, ACC[0-9]{8})"

Not Matches

Ensure all data in column does not match certain regex expression.

JavaScalaYAML

validation().col("account_id").notMatches("^acc.*")

validation.col("account_id").notMatches("^acc.*")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "!REGEXP(account_id, '^acc.*')"

Starts With

Ensure all data in column starts with certain string. Column has to have type string.

JavaScalaYAML

validation().col("account_id").startsWith("ACC")

validation.col("account_id").startsWith("ACC")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "STARTSWITH(account_id, 'ACC')"

Not Starts With

Ensure all data in column does not start with certain string. Column has to have type string.

JavaScalaYAML

validation().col("account_id").notStartsWith("ACC")

validation.col("account_id").notStartsWith("ACC")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "!STARTSWITH(account_id, 'ACC')"

Ends With

Ensure all data in column ends with certain string. Column has to have type string.

JavaScalaYAML

validation().col("account_id").endsWith("ACC")

validation.col("account_id").endsWith("ACC")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "ENDWITH(account_id, 'ACC')"

Not Ends With

Ensure all data in column does not end with certain string. Column has to have type string.

JavaScalaYAML

validation().col("account_id").notEndsWith("ACC")

validation.col("account_id").notEndsWith("ACC")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "!ENDWITH(account_id, 'ACC')"

Size

Ensure all data in column has certain size. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").size(5)

validation.col("transactions").size(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions, 5)"

Not Size

Ensure all data in column does not have certain size. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").notSize(5)

validation.col("transactions").notSize(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions) != 5"

Less Than Size

Ensure all data in column has size less than certain value. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").lessThanSize(5)

validation.col("transactions").lessThanSize(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions) < 5"

Less Than Or Equal Size

Ensure all data in column has size less than or equal to certain value. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").lessThanOrEqualSize(5)

validation.col("transactions").lessThanOrEqualSize(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions) <= 5"

Greater Than Size

Ensure all data in column has size greater than certain value. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").greaterThanSize(5)

validation.col("transactions").greaterThanSize(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions) > 5"

Greater Than Or Equal Size

Ensure all data in column has size greater than or equal to certain value. Column has to have type array or map.

JavaScalaYAML

validation().col("transactions").greaterThanOrEqualSize(5)

validation.col("transactions").greaterThanOrEqualSize(5)

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "SIZE(transactions) >= 5"

Luhn Check

Ensure all data in column passes luhn check. Luhn check is used to validate credit card numbers and certain identification numbers (see here for more details).

JavaScalaYAML

validation().col("credit_card").luhnCheck()

validation.col("credit_card").luhnCheck

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "LUHN_CHECK(credit_card)"

Has Type

Ensure all data in column has certain data type.

JavaScalaYAML

validation().col("id").hasType("string")

validation.col("id").hasType("string")

---
name: "account_checks"
dataSources:
  ...
    validations:
      - expr: "TYPEOF(id) == 'string'"

Expression

Ensure all data in column adheres to SQL expression defined that returns back a boolean. You can define complex logic in here that could combine multiple columns.

For example, CASE WHEN status == 'open' THEN balance > 0 ELSE balance == 0 END would check all rows with status open to have balance greater than 0, otherwise, check the balance is 0.

JavaScalaYAML

var csvTxns = csv("transactions", "/tmp/csv", Map.of("header", "true"))
  .validations(
    validation().expr("amount < 100"),
    validation().expr("year == 2021").errorThreshold(0.1),  //equivalent to if error percentage is > 10%, then fail
    validation().expr("REGEXP_LIKE(name, 'Peter .*')").errorThreshold(200)  //equivalent to if number of errors is > 200, then fail
  );

var conf = configuration().enableValidation(true);

val csvTxns = csv("transactions", "/tmp/csv", Map("header" -> "true"))
  .validations(
    validation.expr("amount < 100"),
    validation.expr("year == 2021").errorThreshold(0.1),  //equivalent to if error percentage is > 10%, then fail
    validation.expr("REGEXP_LIKE(name, 'Peter .*')").errorThreshold(200)  //equivalent to if number of errors is > 200, then fail
  )

val conf = configuration.enableValidation(true)

---
name: "account_checks"
dataSources:
  transactions:
    options:
      path: "/tmp/csv"
    validations:
      - expr: "amount < 100"
      - expr: "year == 2021"
        errorThreshold: 0.1   #equivalent to if error percentage is > 10%, then fail
      - expr: "REGEXP_LIKE(name, 'Peter .*')"
        errorThreshold: 200   #equivalent to if number of errors is > 200, then fail
        description: "Should be lots of Peters"

#enableValidation inside application.conf