dbt: Custom Schema Tests and Multi-Database Capabilities

dbt

Custom Schema Tests:

In dbt, schema tests play a crucial role in maintaining data quality and consistency in your data warehouse. While dbt provides a set of built-in schema tests, you can also create your own custom schema tests using Jinja and SQL. This gives you the flexibility to validate your data against specific business rules and requirements.

To create a custom schema test, you can define a new SQL file with the test logic. Inside the SQL file, you can use Jinja templating to reference the relevant tables, columns, and conditions for your test.

Imagine you have a requirement to ensure that a certain column in your orders table contains only positive numeric values. You can create a custom schema test to validate this requirement. Here's an example:

-- tests/numeric_positive_test.sql

-- Custom schema test: Ensure 'amount' column contains only positive numeric values
SELECT *
FROM {{ ref('orders') }}
WHERE amount <= 0 OR amount IS NULL;

In this example, the custom schema test checks if the amount column in the orders table contains any non-positive values or NULL values. If any records fail this test, dbt will raise an error during the build process.

To include this custom schema test in your dbt model, you can use the {{ config(schema_test=...) }} directive. Here's an example of how you can add the test to your orders model:

-- models/orders.sql

{{ config(schema_test='tests/numeric_positive_test.sql') }}

SELECT *
FROM source.orders;

By adding the config(schema_test=...) directive, dbt will execute the custom schema test whenever the orders model is built or refreshed. If the test fails, an error message will be generated, indicating the specific records that do not meet the defined criteria.

By leveraging custom schema tests, you can enforce your own data validation rules and ensure that your data warehouse maintains the desired level of quality and consistency.

Multi-Database Management:

Managing multiple databases can be challenging, especially when you need to keep them synchronized and aligned. dbt provides advanced features that facilitate multi-database management, allowing you to efficiently work with multiple databases in your data warehouse ecosystem.

One key feature is the ability to perform cross-database queries, which enables you to join tables or perform operations across different databases. This is particularly useful when you have shared dimensions or common data that needs to be accessed across multiple databases. By leveraging cross-database queries, you can seamlessly integrate data from various sources without the need for complex data extraction and loading processes.

Suppose you have two databases in your data warehouse: sales_db and finance_db. You want to perform a join operation between the sales table in sales_db and the invoices table in finance_db. Here's an example of how you can accomplish this with dbt:

-- models/sales_invoices.sql

SELECT s.*, i.invoice_number, i.total_amount
FROM {{ ref('sales', database='sales_db') }} AS s
JOIN {{ ref('invoices', database='finance_db') }} AS i
  ON s.customer_id = i.customer_id;

In this example, the ref function is used to reference the sales and invoices tables from their respective databases. The database argument specifies the target database for each table.

By leveraging the ability to perform cross-database queries, you can seamlessly join tables from different databases and combine data from various sources within a single dbt model.

Additionally, you can utilize multi-target configurations to manage multiple databases in different environments. For instance, you can define separate connection settings for your development, staging, and production environments in the profiles.yml file. This allows you to switch between databases easily based on the target environment you are working with.

So, That’s it for now. Brief examples and explanation on custom schemas & multi-database management on dbt.

Happy Building !

Do check out my articles on dbt:

dbt Beyond the Basics: Advanced Strategies for Implementation

Unleashing the Power of dbt: Advanced Techniques and Features

Venturing into Complex Techniques with dbt: Dynamic Data Selection, Lineage, and Governance

Thanks for Reading !

I post about Data , AI , Startups , Leadership, Writing & Culture.

Stay Tuned for my next blog !!