Our Blog

article Leveraging dbt for Enhanced Data Strategy in AI/ML Projects
published on Dec 26, 2023

Optimizing Data Strategies for AI / ML Success

As the year comes to a close, data teams are preparing to plan their data initiatives for the upcoming quarter. AI has been the talk of the town this year, and according to this article by PWC, more than half of the surveyed companies (54%) have already implemented GenAI in certain areas of their business.

That’s just one form of AI, and on a broader scale, AI aims to understand natural language, recognize patterns, solve problems, and learn from experience. The ultimate goal is to develop systems that can imitate or simulate human intelligence to varying degrees.

To achieve this, technologies like machine learning, deep learning, natural language processing, computer vision, and robotics, among others, are used. However, to succeed in AI and ML initiatives, it’s essential to have a well-structured and easily accessible data foundation.

In the realm of data analytics, the conversion of raw data into valuable insights plays a critical role. Traditional methods of handling data can be complex and time-consuming, which is where dbt comes into play, revolutionizing our approach to data operations.

What is DBT?

dbt is an open-source tool that streamlines the data transformation process, allowing data teams to deliver reliable and high-quality datasets for analysis and modeling. By automating repetitive tasks, dbt reduces the time and effort needed to prepare data for analysis.

Advantages of dbt in Modern Data Warehousing

Unlike the traditional practice of "ETL," where data is transformed before being loaded to data platforms, DBT applies "ELT" to load all raw data into a data warehouse (or lake house) and then conducts transformation. This practice leverages the advantages of a modern data warehouse:

  • Cheap data storage compared to OLTP databases like Postgres or MySQL, allowing all data to be loaded in raw format for different types of transformation.

  • Avoidance of data silos and centralization of information between business departments into a single data warehouse, promoting transparency.

  • Encouragement of analysts to take ownership of cleaning and processing data, enabling them to adapt to frequent changes in business logic.

The Scalability Factor of Transforming Business Logic in dbt

One of the key advantages of dbt lies in its versatility across various tools and platforms.

When organizations employ manual or tool-specific methods for data transformation, they encounter hurdles during migrations or transitions to new tools.

For instance, each tool often has its own syntax, functions, and capabilities, posing challenges in replicating identical business logic across diverse environments.

Picture migrating from Postgres to Redshift. Developers must be cognizant of unsupported Postgres features in Redshift and adapt their SQL scripts accordingly. By harnessing dbt, businesses can establish their business logic and transformations using standardized SQL. If migration becomes necessary, they maintain the ability to identify which models require adjustments within the dbt project. This principle is equally applicable when shifting to different BI platforms or orchestrator tools.

dbt projects also incorporate the mart layer to encapsulate business logic and rules. This abstraction layer serves as a singular source of truth between data and business:

  • It captures business logic by units, entities, or departments.

  • It exclusively includes fields essential for BI reporting and those comprehensible to end-users, complete with appropriate descriptions and clarifications (e.g., fields like

    fivetran_synced are omitted in the mart layer as they hold no value for business users).

Best Practices for Optimizing Data Operations with dbt

In traditional data analysis and modeling practices, analysts often directly create logic within the current database or dashboard that end users rely on. However, this approach poses several challenges:

  • Reviewing and testing the logic may not be thorough before presenting it to end users, potentially leading to downtime and disruptions.

  • Changes made to the underlying dataset can unintentionally impact other content or dashboards, resulting in bugs and frustrations for end users.

  • The lack of documentation for this logic often goes unnoticed by both end users and analysts, leading to duplicated efforts as similar logic is repeatedly developed.

To address these challenges, it’s important to adopt a more proactive and systematic approach to logic implementation, review, and documentation. By doing so, organizations can ensure the reliability and transparency of their data analysis and modeling processes, enabling smoother operations and better outcomes for end users.

To optimize your data operations, follow these key practices with dbt:

  1. Separate Production and Development Datasets: Ensure a clear separation between the dataset visible to end users and the dataset used by developers. This promotes data integrity and enhances the user experience.

  2. Organize Transformation Scripts: Structure all transformation scripts within a designated project. This improves the efficiency of your data operations and makes it easier to manage and maintain your codebase.

  3. Implement Version Control: Utilize Git to manage the development and deployment process for your DBT project. This enables collaboration, tracks changes, and facilitates seamless deployment across different environments.

  4. Apply CI/CD Practices: Embrace Continuous Integration/Continuous Deployment (CI/CD) practices to monitor and address bugs or downtime incidents promptly. This ensures the reliability and stability of your deployment environment.

  5. Conduct Comprehensive Testing: Make tests a part of your data pipeline to ensure data quality and analyze the impact of changes. This proactive approach safeguards against potential issues and helps maintain the integrity of your data.

  6. Prioritize Documentation: Keep your data team and business users in sync by

    documenting your logic and processes. This avoids duplication of efforts and promotes efficient collaboration.

  7. Embrace Modularity: Preserve the "Don't Repeat Yourself" (DRY) principle by breaking down transformation logic into reusable models and layers. This enhances the scalability and maintainability of your data operations.

By implementing these practices, you can optimize your data strategies for success in the ever-changing landscape of AI and ML.

A Case Study: dbt's Strategic Role in Building ML-Ready Datasets

In this section, we explore how dbt significantly improves feature engineering and boosts the effectiveness of machine learning models by using the RFM (Recency, Frequency, Monetary) framework. By leveraging these techniques, we’ll explore how businesses can gain deeper insights from customer behavior, forecast future purchasing patterns, and refine their marketing strategies accordingly.

RFM is a widely used framework for segmenting and understanding customers based on their transactional behavior. To break it down further, recency refers to how recently a customer made a purchase, frequency is how often they purchase, and monetary is how much they spent.

To perform RFM analysis, you start with a dataset that includes customer information and transaction details. Each row in the dataset represents a customer's transaction. In this example, you have access to vast raw transactional data, but it's scattered across multiple sources and lacks the necessary structure for analysis.

Step 1: Understanding Feature Engineering in Machine Learning

Feature engineering stands as a critical step in the preparation of data for use in machine learning. This process involves applying your understanding of the subject area to identify and extract useful information from raw data. These extracted elements, or features, are what machine learning models use to make predictions or decisions.

Why It Matters: The effectiveness of a machine learning model is heavily dependent on the quality of its input data. Through feature engineering, you can enhance data quality, ensuring that the model is fed relevant and accurately represented information. This leads to more precise predictions and better decision-making capabilities. Essentially, well-crafted features enable the model to capture the true essence of the underlying data, making the difference between a mediocre model and a highly performant one. This step is not just about feeding data into a model; it's about refining and enriching the data so that the model can provide more value.

In the specific scenario of using the RFM framework, feature engineering means transforming raw transaction data into structured attributes. This could include deriving numerical values that indicate customer behavior patterns or categorizing data into segments for more nuanced analysis. Here are some examples:

  • RFM Combined Score: This is where we integrate the individual R, F, and M scores into a comprehensive score, termed the RFM_Score. It reflects the overall customer value, with higher scores indicating more valuable customers. For instance, a customer scoring 555 is considered highly valuable.

  • RFM Segments: Here, we create distinct customer segments based on their RFM scores. These segments, like "Champions" for customers with high scores across all three components or "At-Risk" for those with low scores, help in tailoring specific marketing strategies.

  • Recency Categories: We transform the numerical recency scores into more intuitive categories such as "Recent," "Intermediate," and "Inactive," which simplifies understanding how recently customers have engaged.

  • Frequency Categories: Similar to recency, we categorize the frequency of purchases into groups like "High," "Medium," and "Low," providing a clearer picture of purchase regularity.

  • Monetary Categories: Categorize the monetary value of transactions to understand customer spending patterns better, grouping them into "High," "Medium," and "Low" spenders.

Step 2: Setting Up Your dbt Environment

There are two options for deploying dbt:

dbt Cloud runs dbt Core in a hosted (single or multi-tenant) environment with a browser-based interface. It comes equipped with turnkey support for scheduling jobs, CI/CD, hosting documentation, monitoring, and alerting. It also offers an integrated development environment (IDE) and allows you to develop and run dbt commands from your local command line (CLI) or code editor.

dbt Core is an open-source command line tool that can be installed locally in your environment, and communication with databases is facilitated through adapters.

Step 3: Staging Models - Data Cleaning and Preparation

This phase focuses on cleaning and standardizing transactional data to ensure it's in the best shape for feature engineering. Key tasks in this process include:

  1. Casting Data Types: If your dataset includes transaction dates, ensure they are consistently formatted as date types. For example, converting string representations of dates (like '2021-01-15') to a standardized date format ensures uniformity across your dataset.

  2. Renaming Fields: In the context of RFM, you might have fields like 'transaction_date' or 'purchase_amount'. Renaming these for clarity and consistency could mean changing 'transaction_date' to 'transaction_at' and 'purchase_amount' to 'total_spent'. This makes it clearer that one represents a timestamp and the other a monetary value.

  3. Handling Missing Values: If some transactions lack a customer ID or purchase amount, you'll need to decide how to handle these gaps. Options include filling missing customer IDs with a placeholder value or imputing missing purchase amounts based on averages or medians.

  4. Removing Duplicates: Duplicate transactions can skew RFM analysis. Identifying and removing these duplicates is essential. For instance, if there are multiple entries for a single transaction, you would keep only one record to maintain data integrity.

Step 4: Intermediate Models - Aggregated Transformations for Features

In dbt, intermediate models are used to aggregate and transform data into a format that's useful for analysis. Here are examples using RFM data:

  1. Pivoting Data by Customer_ID: Create a dbt model that pivots your transaction table by Customer_ID. This model would aggregate all transactions per customer, providing a consolidated view of each customer's interactions.

  2. Calculating RFM Metrics:

    • Recency (R): Calculate the number of days since the customer's last purchase. For instance, you might use a dbt model to compute the difference in days between the most recent transaction date and the current date for each customer.

    • Frequency (F): Determine how many purchases each customer has made. A dbt model could count the number of transactions per customer.

    • Monetary (M): Calculate the total amount spent by each customer. This could involve a dbt model summing up the transaction amounts for each customer.

  3. Creating Customer-Centric Features: Develop dbt models to calculate additional metrics, such as the average order value (total spend divided by the number of transactions) or customer lifetime value (an estimate of the total revenue a business can expect from a customer over time).

Step 5: Mart Models - RFM Score and Customer Segmentation

Mart models in dbt are more refined and are used for specific analytical purposes, like in the examples above. For example:

  1. Creating RFM Score: Create a dbt model that combines the R, F, and M metrics into a single RFM score. This could be a simple concatenation of the R, F, and M category rankings or a more complex scoring algorithm based on your business logic.

  2. Segmentation Features: Develop dbt models to segment customers based on their RFM scores. For instance, you might create segments such as "High-Value Customers" (high R, F, and M scores), "Loyal Customers" (high F, regardless of R and M), or "At Risk" (low R and F). Each segment can be defined based on the combined RFM score or individual RFM components.

Step 6: Using Engineered Features for Machine Learning

Now the features engineered using dbt's transformation capabilities are utilized as inputs for machine learning models.

  • Predicting Customer Lifetime Value: Utilize RFM scores to predict the future value of customers. For instance, a high RFM score could indicate a higher lifetime value.

  • Customer Segmentation for Targeted Marketing: Employ RFM segments, such as 'Champions' or 'At-Risk,' to customize marketing campaigns. This enables targeting specific customer groups with tailored promotions or retention strategies.

  • Personalized Product Recommendations: Use the frequency and monetary aspects of RFM to understand purchasing patterns, helping in making personalized product recommendations to customers.

Step 7: Model Training and Prediction with RFM Data

After integrating the RFM-based features into your dataset, the next step is to train machine learning models using this enriched dataset. Examples include:

  • Churn Prediction Models: Train models to predict which customers are likely to churn based on their RFM scores. Customers with low recency and frequency scores might be at higher risk of churning.

  • Sales Forecasting: Use the RFM data to forecast future sales trends. For instance, a concentration of customers with high monetary scores might indicate potential for increased sales in certain product categories.

  • Dynamic Pricing Strategies: Implement models that adjust pricing based on customer segments identified through RFM analysis, catering pricing strategies to different levels of customer engagement and spending habits.


In summary, it's crucial for your team to have a strong foundation when planning new AI/ML initiatives or scaling reporting within your organization. As the saying goes, "garbage in, garbage out." Recognizing the importance of establishing a single source of truth for datasets and transformation, with centralized data and no data silos, is key. With dbt, you can optimize your data operations and improve efficiency, ultimately driving better insights for your business.

For ongoing insights and the latest trends in data, join our community by signing up for our monthly newsletter. Let's continue exploring and mastering these technologies together!

Data Driven

Stay in the know! Sign up to receive our latest news and updates by filling out our contact form today.

Send us a message

Max. 500 characters