business intelligence
BI, dashboards, data visualization

metadata

real time

batch processes

dbt

• examples
  

  #python -m pip install dbt dbt-mysql 
  cd ~/lav/src/sawmill/
  mkdir -p dbt_sawmill
  cd dbt_sawmill
  dbt init 
  

  Installing dbt project and profile setup
  Info Created .vscode/extensions.json with dbt extension recommendation
  Success Project created successfully!
  Info Project name: jaffle_shop
  Info Project directory: jaffle_shop
  Info Setting up your profile...
  Info Creating new profile...
  
  ==================== Execution Summary =====================
  Finished 'init' with 1 error [336ms]
  

  dbt_project.yml
  name: my_project
  profile: my_dbt_profile
  model-paths: ["models"]
  analysis-paths: ["analyses"]
  test-paths: ["tests"]
  seed-paths: ["seeds"]
  target-paths: ["target"]
  clean-paths: ["clean"]
  models:
    my_project:
      my_model:
        +materialized: table
  

  • Defining Models

  One of the most common use cases is defining transformation models in SQL files. dbt allows you to write modular SQL that builds upon other models, creating a layered approach to data transformation.

  -- models/raw/my_table.sql
  SELECT
      customer_id,
      order_date,
      amount,
      product_category
  FROM raw_source_table
  

  -- models/stg/stg_customer_orders.sql
  {{ config(materialized='table') }}
  
  SELECT
      customer_id,
      DATE(order_date) as order_date,
      ROUND(amount, 2) as amount
  FROM {{ ref('my_table') }}
  WHERE amount > 0
  

  -- models/marts/customer_lifetime_value.sql
  {{ config(materialized='table') }}
  
  SELECT
      customer_id,
      SUM(amount) as total_spend,
      COUNT(order_date) as order_count,
      AVG(amount) as avg_order_value
  FROM {{ ref('stg_customer_orders') }}
  GROUP BY customer_id
  

  • Testing and Quality Assurance

  dbt provides built-in testing capabilities that run automatically when you execute your data pipeline. This ensures data quality before it reaches business intelligence tools.

  -- models/stg/stg_customer_orders.sql
  SELECT
      customer_id,
      DATE(order_date) as order_date,
      ROUND(amount, 2) as amount
  FROM {{ ref('my_table') }}
  WHERE amount > 0
  
  {{ config(materialized='table') }}
  
  -- tests/customers_have_orders.sql
  {{ config(materialized='table') }}
  SELECT *
  FROM {{ ref('stg_customer_orders') }}
  WHERE customer_id IS NULL
  
  # test unique customers
  {{ test_unique('customer_id') }}
  
  # test not null on customer_id
  {{ test_not_null('customer_id') }}
  
  # test amount is positive
  {{ test_where('amount > 0') }}
  
  # test that orders come after a specific date
  {{ test_where('order_date >= date(\'2023-01-01\')') }}
  

  • Documentation Generation

  dbt automatically generates documentation based on your model definitions and tags. This documentation includes data lineage, field descriptions, and test coverage, making it easier for stakeholders to understand your data assets.

  -- models/marts/customer_lifetime_value.sql
  {{ config(
      materialized='table',
      description='Aggregate metrics for customer lifetime value calculation',
      tags=['finance', 'customer-analytics', 'revenue']
  ) }}
  
  SELECT
      customer_id,
      SUM(amount) as total_spend,
      COUNT(order_date) as order_count,
      AVG(amount) as avg_order_value
  FROM {{ ref('stg_customer_orders') }}
  GROUP BY customer_id
  
  # dbt generate docs will create documentation automatically
  

  dbt docs generate

  • Using Jinja for Dynamic Logic

  dbt supports Jinja templating for dynamic SQL generation. This allows you to create more flexible and reusable transformations without duplicating code.

  -- models/marts/sales_report.sql
  {{ config(materialized='table') }}
  
  WITH daily_sales AS (
      SELECT
          DATE_TRUNC('day', order_date) as sale_day,
          SUM(amount) as daily_revenue
      FROM {{ ref('stg_customer_orders') }}
      GROUP BY 1
  )
  SELECT
      sale_day,
      daily_revenue,
      ROW_NUMBER() OVER (ORDER BY sale_day DESC) as rn
  FROM daily_sales
  WHERE rn <= 30
  
  -- Parameters can be passed via dbt run command
  -- dbt run --target production --vars '{"report_start_date": "2024-01-01"}'
  

  • Running and Scheduling

  Execute dbt commands to build, test, and deploy your data models. This workflow ensures changes go through a proper validation pipeline before production use.

  # Run all models in development
  dbt run --target development
  
  # Run a specific model
  dbt run --select models/marts/customer_lifetime_value.sql
  
  # Run tests only
  dbt test
  
  # Clean up and re-run
  dbt clean && dbt run
  
  # Schedule with cron
  # 0 2 * * * cd /path/to/dbt && dbt run > dbt.log 2>&1
  

  # Schedule via cron job
  (dbt "run" "--profiles-dir" "/home/user/.dbt")
  

  • dbt Core and Cloud

  dbt offers two versions: Core for open-source usage and Cloud for managed infrastructure.

  # Core installation
  pip install dbt-core
  
  # Install from source
  pip install dbt-core[all]
  
  # Cloud installation
  pip install dbt-cloud
  

  • Common Use Cases

  Here are practical scenarios demonstrating dbt’s capabilities in business intelligence workflows:

  
  - Revenue Analysis Pipeline
    - Extract: Pull transaction data from the source system
    - Transform: Clean, aggregate, and enrich with product categories
    - Load: Write to a star schema for BI tool consumption
    - Test: Validate revenue totals match financial reports
  
  - Customer Segmentation
    - Raw: Import customer transaction history
    - Intermediate: Calculate recency, frequency, monetary metrics
    - Final: Create RFM segments for marketing campaigns
    - Document: Explain segmentation logic to business team
  
  - Inventory Forecasting
    - Historical: Extract sales and inventory data
    - Aggregation: Group by product and time period
    - Prediction: Apply forecasting models
    - Action: Flag items needing reorder
  
  - End-to-End Example
    - dbt run --models marts.*
    - dbt test --models marts.*
    - dbt docs generate
    - dbt docs serve
  

  • Integration with BI Tools

  Once dbt models are built and validated, export them for use in visualization tools.

  
  - SQL Direct
    - Most BI tools can connect directly to the data warehouse
    - Reference dbt models by their names
    - Example: =SELECT * FROM {{ ref('customer_lifetime_value') }}=
  
  - dbt Analytics Hub
    - Export models to Snowflake or BigQuery
    - Share with teams who don't have dbt access
    - Maintain version control and lineage tracking
  
  - Data Warehouse Views
    - dbt can create views on top of materialized tables
    - Simplify queries for end users
    - Example: =CREATE VIEW mart.customer_summary AS SELECT * FROM {{ ref('customer_lifetime_value') }}=
  

  • Advanced Patterns

  For more sophisticated use cases, dbt supports complex patterns including incremental models, snapshots, and semantic layer definitions.

  -- models/incremental/sales_incremental.yml
  {{ config(
      materialized='incremental',
      unique_key='transaction_id',
      target_partitions='date',
      incremental_strategy='merge',
      on_schema_change='sync_all_columns'
  ) }}
  
  SELECT
      transaction_id,
      customer_id,
      amount,
      CURRENT_TIMESTAMP as updated_at
  FROM source_table
  {{ get_incremental_clause() }}
  
  

  -- Semantic Layer
  {{ config(
      enabled=True,
      meta={
          "dbt_semantic_layer": {
              "default_time_dimension": "order_date",
              "default_time_grain": "day",
              "metrics": ["total_amount", "unique_customers"]
          }
      }
  ) }}
  

  • Best Practices

  To maximize dbt effectiveness in business intelligence projects, follow these guidelines:

  
  1. Model Organization
     - Raw: Extract data as-is from sources
     - Staged: Clean and normalize for consistency
     - Mart: Business-ready aggregated views
  
  2. Naming Convention
     - Prefix with source system (e.g., raw_stripe_customers)
     - Use clear model names (e.g., stg_customer_orders)
     - Group by business domain (e.g., marts_revenue)
  
  3. Version Control
     - Track all SQL changes in Git
     - Use meaningful commit messages
     - Tag releases for major business changes
  
  4. Collaboration
     - Write clear, documented SQL
     - Add tests for data quality
     - Generate and maintain documentation
  
  5. Performance
     - Materialize large models as tables
     - Use incremental updates where possible
     - Partition by time when appropriate
  

• Conclusion **
  

  dbt transforms how teams approach data engineering and analytics by bringing software development practices to data pipelines. It enables rapid iteration, maintains data quality, and improves collaboration between technical and business teams. For organizations serious about business intelligence, dbt represents a significant advantage in building reliable, scalable data infrastructure.

query engine

agentic