dbt hub

dbt test: expect_column_values_to_have_consistent_casing

USE CASE

String format

APPLIES TO

Column

This page details the expect_column_values_to_have_consistent_casing test from the dbt-expectations package. This test ensures that all entries within a specific column use consistent text casing, whether all upper, lower, or proper case. It's particularly important for maintaining uniformity in text data, essential for accurate searches, comparison operations, and reporting.

How it Works

The expect_column_values_to_have_consistent_casing test checks the casing of each entry in a specified column to confirm that it matches a consistent casing style. This check is important to avoid discrepancies that could affect data integrity and analysis accuracy.

Steps and Conditions:

  1. Column Specification: Choose the column whose values' casing needs verification.
  2. Configure Display Options:
    • Display Inconsistent Columns: If set to true, this optional setting will report the number of inconsistent casing entries in the column directly in the terminal.
  3. Test Execution: The test runs through each entry in the specified column to examine the text casing consistency.
  4. Outcome:
    • Pass: If all entries in the column maintain consistent casing, the test passes.
    • Fail: If any entries exhibit inconsistent casing, the test fails, identifying the need for data normalization.

Example Usage: E-commerce

For an E-commerce platform, consistent data entry in product descriptions or titles is key for effective search functionality and customer satisfaction.

Consider a scenario where the product_details table contains a product_name column which must maintain consistent casing (e.g., all titles start with a capital letter followed by lower case).


models:
  - name: product_details
    columns:
      - name: product_name
        tests:
          - dbt_expectations.expect_column_values_to_have_consistent_casing:
              display_inconsistent_columns: true

In this example, the expect_column_values_to_have_consistent_casing test verifies that each entry in the product_name column of the product_details model adheres to a consistent casing format. This standardization assists in achieving reliable search results and maintaining a professional appearance in product listings.

The only data observability platform built into your dbt code

  • Get monitors on your production tables out-of-the-box with zero configuration
  • Add tests to your code in bulk with a simple UI
  • Track test results over time
  • Set owners and create meaningful alerts
  • Triage incidents faster using our end-to-end column-level lineage graph