# Column-Level Data Profiling for Google BigQuery Datasets using dbt

URL: https://rittmananalytics.com/blog/2020/06/03/2020-6-3-column-level-data-profiling-for-google-bigquery-datasets-using-dbt
Description: One of the challenges when centralising data from a new source is how to efficiently audit the data it provides, and one of the most fundamental tasks in a data…
Author: Mark Rittman
Published: 2020-06-03T23:29:58+00:00
Modified: 2020-06-03T23:29:58+00:00

---

One of the challenges when centralising data from a new source is how to efficiently audit the data it provides, and one of the most fundamental tasks in a data audit is to understand the content and structure of each of those data source tables.

The data profiling feature within the RA Warehouse dbt Framework we [blogged about](https://rittmananalytics.com/blog/2020/5/28/introducing-the-ra-warehouse-dbt-framework-how-rittman-analytics-does-data-centralization) and [published to Github](https://github.com/rittmananalytics/ra_data_warehouse) last week makes it easy to capture the following column-level object stats and metadata for an entire dataset (schema) of tables and views in Google BigQuery

-   Count of nulls, not nulls and percentage null
    
-   Whether column is Not Nullable
    
-   Count of unique values and percentage unique
    
-   Data Type
    
-   Min, Max and Average values
    
-   Most frequently occurring value, and count of rows containing most frequent value
    
-   Whether column is used for partitioning
    

The profiler also generates two recommendations for dbt model tests:

-   Based on a configurable % threshold (default 90%) whether the column should be considered Not Nullable
    
-   Again based on a configurable % threshold (default 90%) whether the column should be considered as a “unique values” column
    

You can then use the recommendations generated by the profiler to indicate where you might want to add UNIQUE or NOT\_NULL tests into the models section of your dbt schema.yml file, with the small percentage (configurable and set by default at 90%) of non-compliant rows then considered as data errors.

### **How Does the Data Profiler Work?**

Data Profiling is based around a dbt macro that, for every view or table (“relation”) in a given schema, generates an SQL query that creates a series of SQL query blocks that generate stats for each database object, unions those queries together and then joins the results to another query against the INFORMATION\_SCHEMA.COLUMNS to obtain each object’s metadata.

{%- macro profile\_schema(table\_schema) -%}{{ config(schema=’profiles’) }}{% set not\_null\_profile\_threshold\_pct = “.9” %}{% set unique\_profile\_threshold\_pct = “.9″ %}{% set tables = dbt\_utils.get\_relations\_by\_prefix(table\_schema, ”) %}SELECT column\_stats.table\_catalog,column\_stats.table\_schema,column\_stats.table\_name,column\_stats.column\_name,case when column\_metadata.is\_nullable = ‘YES’ then false else true end as is\_not\_nullable\_column,case when column\_stats.pct\_not\_null \> {{ not\_null\_profile\_threshold\_pct }} then true else false end as is\_recommended\_not\_nullable\_column,column\_stats.\_nulls as count\_nulls,column\_stats.\_non\_nulls as count\_not\_nulls,column\_stats.pct\_not\_null as pct\_not\_null,column\_stats.table\_rows,column\_stats.count\_distinct\_values,column\_stats.pct\_unique,case when column\_stats.pct\_unique \>\= {{ unique\_profile\_threshold\_pct }} then true else false end as is\_recommended\_unique\_column,column\_metadata.\* EXCEPT (table\_catalog,table\_schema,table\_name,column\_name,is\_nullable),column\_stats.\* EXCEPT (table\_catalog,table\_schema,table\_name,column\_name,\_nulls,\_non\_nulls,pct\_not\_null,table\_rows,pct\_unique,count\_distinct\_values)FROM({% for table in tables %}SELECT \*FROM(WITH\`table\` AS (SELECT \* FROM {{ table }} ),table\_as\_json AS (SELECT REGEXP\_REPLACE(TO\_JSON\_STRING(t), r’^{|}$’, ”) AS ROW FROM \`table\` AS t ),pairs AS (SELECT REPLACE(column\_name, ‘”‘, ”) AS column\_name, IF (SAFE\_CAST(column\_value AS STRING)=’null’,NULL, column\_value) AS column\_valueFROM table\_as\_json,UNNEST(SPLIT(ROW, ‘,”‘)) AS z,UNNEST(\[SPLIT(z, ‘:’)\[SAFE\_OFFSET(0)\]\]) AS column\_name,UNNEST(\[SPLIT(z, ‘:’)\[SAFE\_OFFSET(1)\]\]) AS column\_value ),profile AS (SELECTsplit(replace(‘{{ table }}’,’\`’,”),’.’ )\[safe\_offset(0)\] as table\_catalog,split(replace(‘{{ table }}’,’\`’,”),’.’ )\[safe\_offset(1)\] as table\_schema,split(replace(‘{{ table }}’,’\`’,”),’.’ )\[safe\_offset(2)\] as table\_name,column\_name,COUNT(\*) AS table\_rows,COUNT(DISTINCT column\_value) AS count\_distinct\_values,safe\_divide(COUNT(DISTINCT column\_value),COUNT(\*)) AS pct\_unique,COUNTIF(column\_value IS NULL) AS \_nulls,COUNTIF(column\_value IS NOT NULL) AS \_non\_nulls,COUNTIF(column\_value IS NOT NULL) / COUNT(\*) AS pct\_not\_null,min(column\_value) as \_min\_value,max(column\_value) as \_max\_value,avg(SAFE\_CAST(column\_value AS numeric)) as \_avg\_value,APPROX\_TOP\_COUNT(column\_value, 1)\[OFFSET(0)\] AS \_most\_frequent\_value,MIN(LENGTH(SAFE\_CAST(column\_value AS STRING))) AS \_min\_length,MAX(LENGTH(SAFE\_CAST(column\_value AS STRING))) AS \_max\_length,ROUND(AVG(LENGTH(SAFE\_CAST(column\_value AS STRING)))) AS \_avr\_lengthFROMpairsWHEREcolumn\_name

<

\>

”AND column\_name NOT LIKE ‘%-%’GROUP BYcolumn\_nameORDER BYcolumn\_name)SELECT\*FROMprofile){%- if not loop.last %}UNION ALL{%- endif %}{% endfor %}) column\_statsLEFT OUTER JOIN(SELECT\* EXCEPT(is\_generated,generation\_expression,is\_stored,is\_updatable)FROM{{ table\_schema }}.INFORMATION\_SCHEMA.COLUMNS) column\_metadataON column\_stats.table\_catalog = column\_metadata.table\_catalogAND column\_stats.table\_schema = column\_metadata.table\_schemaAND column\_stats.table\_name = column\_metadata.table\_nameAND column\_stats.column\_name = column\_metadata.column\_name{%- endmacro -%}

Then, within each data source adapter (folder within a dbt git repo) you will find a model definition such as this one for the Asana Projects source :

{% if not var(“enable\_asana\_projects\_source”) %}{{config(enabled=false)}}{% endif %}{% if var(“etl”) == ‘fivetran’ %}{{ profile\_schema(var(‘fivetran\_schema’)) }}{% elif var(“etl”) == ‘stitch’ %}{{ profile\_schema(var(‘stitch\_schema’)) }}{% endif %}

These models when run will automatically create views within the the “profile” dataset (e.g. analytics\_profile) that you can use to audit and profile the data from newly-enabled data source adapters (note that you will need to create corresponding model files yourself for any new, custom data source adapters).

There is also a “profile\_wh\_tables.sql” model within the /models/utils folder that runs the following jinja code:

{{ profile\_schema(target.schema) }}

that automatically profiles all of the fact and dimension tables in the warehouse at the end of dbt processing.

See the [RA Warehouse dbt Framework](https://github.com/rittmananalytics/ra_data_warehouse) on Github for more details, and our [Data Centralisation consulting service](https://rittmananalytics.com/data-centralisation) if you’d like us to implement dbt or a centralized SaaS data warehouse at your organization.