Author Image

CEO & Co-founder of Visivo

dbt Local Development for Faster Analytics Workflows

Set up local dbt development to accelerate analytics workflows with instant feedback and isolated testing.

dbt local development setup

Local dbt™ development revolutionizes analytics workflows by enabling data engineers to develop and test transformations on their machines. According to VentureBeat, "87% of data science projects never make it to production," often due to slow development cycles and lack of immediate feedback.

Instead of waiting for cloud resources or sharing development environments, engineers get instant feedback and complete isolation. When combined with Visivo, this local development approach extends beyond just data transformations to include complete dashboard development and testing—all running locally for maximum speed and flexibility.

Why Local Development Matters for Modern Analytics

Traditional analytics development suffers from significant friction:

  • Cloud delays: Wait 30-60 seconds for every model run in cloud warehouses
  • Resource contention: Shared development environments create bottlenecks
  • High costs: Every test run consumes expensive compute credits
  • Limited experimentation: Fear of breaking shared resources stifles innovation
  • Offline limitations: No development capability without internet connectivity

As MIT research shows, "Companies using data-driven strategies have 5-6% higher productivity," but only when development workflows enable rapid iteration and experimentation.

Local development eliminates these friction points while maintaining production fidelity through proper tooling and workflows.

The Modern Local Stack: dbt™ + Visivo + DuckDB

This powerful combination provides enterprise-grade analytics development entirely on your laptop:

  • dbt™: Handles data transformations and modeling
  • Visivo: Manages dashboards, visualizations, and deployment
  • DuckDB: Provides fast, embedded analytical database
  • Git: Enables version control and collaboration

This approach aligns with developer-first BI workflows and enables faster feedback cycles.

Complete Local Development Setup

1. Installing the Full Stack

# Install dbt with your adapter (DuckDB for local development)
pip install dbt-core dbt-duckdb

# Install Visivo CLI
pip install visivo

# Or use the installation script
curl -fsSL https://visivo.sh | bash

# Verify installations
dbt --version
visivo --version

2. Project Structure for Local Development

Organize your project to support both dbt™ and Visivo development:

# Integrated dbt + Visivo project structure
analytics-project/
├── dbt_project.yml              # dbt configuration
├── project.visivo.yml           # Visivo configuration
├── profiles.yml                 # Database connections
├── .env                         # Environment variables
├── data/                        # Sample data files
│   ├── sample_orders.csv
│   └── sample_customers.csv
├── models/                      # dbt models
│   ├── staging/
│   │   ├── stg_orders.sql
│   │   └── stg_customers.sql
│   ├── intermediate/
│   │   └── int_customer_metrics.sql
│   └── marts/
│       ├── customer_analytics.sql
│       └── revenue_analysis.sql
├── tests/                       # dbt tests
│   └── assert_revenue_positive.sql
├── macros/                      # dbt macros
│   └── calculate_ltv.sql
└── visivo/                      # Visivo configurations
    ├── traces/
    │   ├── customer_traces.yml
    │   └── revenue_traces.yml
    └── dashboards/
        ├── executive.yml
        └── analytics.yml

3. Database Configuration for Local Development

Configure DuckDB as your local development database:

name: visivo_project

# profiles.yml
analytics_project:
  outputs:
    dev:
      type: duckdb
      path: ./dev.duckdb
      threads: 4
      extensions:
        - parquet
        - httpfs  # For reading from S3/URLs if needed

    # Optional: Local PostgreSQL for advanced testing
    local_postgres:
      type: postgres
      host: localhost
      port: 5432
      user: postgres
      password: "{{ env_var('LOCAL_POSTGRES_PASSWORD') }}"
      dbname: analytics_dev
      schema: public

  target: dev


# project.visivo.yml - Integrated with dbt
name: Local Analytics Development
cli_version: "1.0.74"

# Integration with dbt
dbt:
  profile: analytics_project
  target: dev
  project_dir: .

defaults:
  source_name: duckdb_local

sources:
  - name: duckdb_local
    type: duckdb
    database: ./dev.duckdb

includes:
  - path: visivo/**/*.yml
  - path: visivo/**/*.yaml

Integrated Development Workflow

Sample Data Setup

Create realistic sample data for local development:

-- data/setup_sample_data.sql
-- Create sample tables that mirror production structure

CREATE OR REPLACE TABLE raw_orders AS
SELECT *
FROM read_csv_auto('data/sample_orders.csv');

CREATE OR REPLACE TABLE raw_customers AS
SELECT *
FROM read_csv_auto('data/sample_customers.csv');

-- Verify data loaded correctly
SELECT COUNT(*) as order_count FROM raw_orders;
SELECT COUNT(*) as customer_count FROM raw_customers;

# Load sample data
duckdb dev.duckdb < data/setup_sample_data.sql

dbt™ Model Development

Create dbt™ models that work with your local data:

-- models/staging/stg_orders.sql
{{ config(materialized='view') }}

SELECT
    order_id,
    customer_id,
    order_date::date as order_date,
    order_amount::decimal(10,2) as order_amount,
    status,
    created_at::timestamp as created_at
FROM {{ source('raw', 'orders') }}
WHERE order_date >= '2023-01-01'

-- models/staging/stg_customers.sql
{{ config(materialized='view') }}

SELECT
    customer_id,
    customer_name,
    email,
    registration_date::date as registration_date,
    customer_segment,
    created_at::timestamp as created_at
FROM {{ source('raw', 'customers') }}
WHERE customer_segment IS NOT NULL

-- models/marts/customer_analytics.sql
{{ config(materialized='table') }}

WITH customer_orders AS (
    SELECT
        c.customer_id,
        c.customer_name,
        c.customer_segment,
        c.registration_date,
        COUNT(o.order_id) as total_orders,
        SUM(CASE WHEN o.status = 'completed' THEN o.order_amount ELSE 0 END) as total_revenue,
        MAX(o.order_date) as last_order_date,
        MIN(o.order_date) as first_order_date
    FROM {{ ref('stg_customers') }} c
    LEFT JOIN {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name, c.customer_segment, c.registration_date
)

SELECT
    *,
    CASE
        WHEN total_orders = 0 THEN 'inactive'
        WHEN total_orders = 1 THEN 'one_time'
        WHEN total_orders < 5 THEN 'low_frequency'
        WHEN total_orders < 10 THEN 'medium_frequency'
        ELSE 'high_frequency'
    END as frequency_segment,

    CASE
        WHEN total_revenue = 0 THEN 'no_value'
        WHEN total_revenue < 100 THEN 'low_value'
        WHEN total_revenue < 500 THEN 'medium_value'
        ELSE 'high_value'
    END as value_segment,

    CASE
        WHEN total_orders > 0 THEN total_revenue / total_orders
        ELSE 0
    END as avg_order_value,

    CASE
        WHEN last_order_date >= CURRENT_DATE - 90 THEN 'active'
        WHEN last_order_date >= CURRENT_DATE - 180 THEN 'at_risk'
        ELSE 'churned'
    END as lifecycle_stage

FROM customer_orders

Visivo Integration with dbt™ Models

Configure Visivo to use your dbt™ models directly. This integration demonstrates the power of BI-as-code scalable systems and dbt™ BI integration:

name: trace_example

# visivo/traces/customer_traces.yml
traces:
  - name: customer_value_distribution
    model: ${ref(customer_analytics)}}  # Reference dbt model
    columns:
      x: total_orders
      y: total_revenue
      color: customer_segment
      size: avg_order_value
    props:
      type: scatter
      mode: markers
      x: column(x)
      y: column(y)
      marker:
        color: column(color)
        size: column(size)
        sizemode: diameter
        sizeref: 2
        colorscale: 'Viridis'
      text: column(customer_name)
      hovertemplate: |
        Customer: %{text}<br>
        Orders: %{x}<br>
        Revenue: $%{y:,.2f}<br>
        Segment: %{marker.color}<br>
        AOV: $%{marker.size:,.2f}<br>
        <extra></extra>

  - name: customer_lifecycle_funnel
    model: ${ref(customer_analytics)}}
    columns:
      stage: lifecycle_stage
      count: customer_id
    filters:
      - ?{COUNT(*) as count}  # Aggregate by lifecycle stage
    group_by:
      - stage
    props:
      type: funnel
      x: column(count)
      y: column(stage)
      text: column(count)
      texttemplate: "%{text} customers"

  - name: segment_performance_heatmap
    model: ${ref(customer_analytics)}}
    columns:
      frequency: frequency_segment
      value: value_segment
      revenue: total_revenue
    filters:
      - ?{SUM(total_revenue) as revenue}
    group_by:
      - frequency, value
    props:
      type: heatmap
      x: column(frequency)
      y: column(value)
      z: column(revenue)
      colorscale: 'Blues'
      text: column(revenue)
      texttemplate: "$%{text:,.0f}"


# visivo/dashboards/customer_analytics.yml
dashboards:
  - name: Customer Analytics Dashboard
    description: "Comprehensive customer behavior and segmentation analysis"

    rows:
      - height: compact
        items:
          - markdown: |
              # Customer Analytics Dashboard

              Real-time insights into customer behavior, segmentation, and lifetime value.

              **Data Source:** Local development database
              **Last Updated:** {{ current_timestamp }}
              **Models Used:** {{ ref('customer_analytics') }}

      # KPI Row
      - height: small
        items:
          - width: 1
            chart:
              traces:
                - model: ${ref(customer_analytics)}
                  columns:
                    value: customer_id
                  filters:
                    - ?{COUNT(DISTINCT customer_id) as total_customers}
                  props:
                    type: indicator
                    mode: number
                    value: column(value)
                    title:
                      text: "Total Customers"
                    number:
                      font:
                        size: 24

          - width: 1
            chart:
              traces:
                - model: ${ref(customer_analytics)}
                  columns:
                    value: total_revenue
                  filters:
                    - ?{SUM(total_revenue) as total_revenue}
                  props:
                    type: indicator
                    mode: number
                    value: column(value)
                    title:
                      text: "Total Revenue"
                    number:
                      prefix: "$"
                      font:
                        size: 24

          - width: 1
            chart:
              traces:
                - model: ${ref(customer_analytics)}
                  columns:
                    value: avg_order_value
                  filters:
                    - ?{AVG(avg_order_value) as avg_aov}
                  props:
                    type: indicator
                    mode: number
                    value: column(value)
                    title:
                      text: "Average AOV"
                    number:
                      prefix: "$"
                      font:
                        size: 24

      # Main Analysis
      - height: large
        items:
          - width: 2
            chart:
              traces: [${ref(customer-value-distribution)}]
              layout:
                title: "Customer Value Distribution"
                xaxis:
                  title: "Total Orders"
                yaxis:
                  title: "Total Revenue ($)"

          - width: 1
            chart:
              traces: [${ref(customer-lifecycle-funnel)}]
              layout:
                title: "Customer Lifecycle"

      # Detailed Analysis
      - height: medium
        items:
          - width: 3
            chart:
              traces: [${ref(segment-performance-heatmap)}]
              layout:
                title: "Segment Performance Matrix"
                xaxis:
                  title: "Frequency Segment"
                yaxis:
                  title: "Value Segment"

Fast Development Loop

The 30-Second Development Cycle

Here's how fast local development becomes:

#!/bin/bash
# fast_dev_loop.sh - Complete development and testing cycle

echo "Starting fast development cycle..."

# Step 1: Run dbt models (5-10 seconds)
echo "Running dbt models..."
time dbt run --select customer_analytics

# Step 2: Test dbt models (2-3 seconds)
echo "Testing dbt models..."
time dbt test --select customer_analytics

# Step 3: Start Visivo server (5 seconds)
echo "Starting Visivo dashboard server..."
visivo serve --port 8080 &
SERVER_PID=$!

# Step 4: Open browser to dashboard
sleep 2
echo "Opening dashboard in browser..."
open http://localhost:8080/dashboards/customer_analytics

echo "Total cycle time: ~30 seconds"
echo "Dashboard server running at http://localhost:8080"
echo "Press Ctrl+C to stop server"

# Wait for user to stop
wait $SERVER_PID

Real-Time Development with Watch Mode

Set up automatic rebuilding during development:

# Terminal 1: Watch dbt models for changes
watchfiles 'dbt run --select customer_analytics' models/

# Terminal 2: Run Visivo in development mode
visivo serve --port 8080

# Terminal 3: Your editor for making changes
code models/marts/customer_analytics.sql

When you save changes to any dbt™ model:

  1. watchfiles detects the change and runs dbt run
  2. Visivo automatically reloads with fresh data
  3. Browser shows updated dashboard in < 5 seconds

This rapid iteration cycle is essential for modern analytics, as detailed in our faster feedback cycles guide.

Advanced Local Development Patterns

Environment-Specific Development

Support multiple local environments:

name: visivo_project

# profiles.yml - Multiple local environments
analytics_project:
  outputs:
    # Fast development with DuckDB
    dev:
      type: duckdb
      path: ./dev.duckdb
      threads: 4

    # Local PostgreSQL for production simulation
    local_prod:
      type: postgres
      host: localhost
      port: 5432
      user: postgres
      password: "{{ env_var('LOCAL_POSTGRES_PASSWORD') }}"
      dbname: analytics_local_prod

    # Sample data environment
    sample:
      type: duckdb
      path: ./sample.duckdb
      threads: 2

  target: "{{ env_var('DBT_TARGET', 'dev') }}"


# Switch between environments easily
export DBT_TARGET=dev
dbt run  # Uses DuckDB

export DBT_TARGET=local_prod
dbt run  # Uses local PostgreSQL

export DBT_TARGET=sample
dbt run  # Uses sample data

Integration Testing with Production Data Samples

Create realistic local datasets from production:

-- scripts/create_sample_data.sql
-- Run this against production to create sample datasets

-- Sample orders (last 90 days, anonymized)
COPY (
    SELECT
        order_id,
        'cust_' || ROW_NUMBER() OVER () as customer_id,  -- Anonymize
        order_date,
        order_amount,
        status,
        created_at
    FROM orders
    WHERE order_date >= CURRENT_DATE - 90
    ORDER BY RANDOM()
    LIMIT 10000
) TO 'sample_orders.csv' (HEADER, DELIMITER ',');

-- Sample customers (anonymized)
COPY (
    SELECT
        'cust_' || ROW_NUMBER() OVER () as customer_id,  -- Anonymize
        'Customer ' || ROW_NUMBER() OVER () as customer_name,
        'user' || ROW_NUMBER() OVER () || '@example.com' as email,
        registration_date,
        customer_segment,
        created_at
    FROM customers
    WHERE created_at >= CURRENT_DATE - 365
    ORDER BY RANDOM()
    LIMIT 5000
) TO 'sample_customers.csv' (HEADER, DELIMITER ',');

Performance Testing and Optimization

Profile your models locally before deploying:

# Profile dbt model performance
dbt run --select customer_analytics --profiles-dir . --profile analytics_project --target dev

# Use DuckDB's EXPLAIN for query optimization
duckdb dev.duckdb -c "EXPLAIN SELECT * FROM customer_analytics LIMIT 100;"

# Test with larger datasets
dbt run --select customer_analytics --vars '{"start_date": "2020-01-01"}'

Collaborative Development with Shared Samples

Share sample datasets across the team:

name: example_project

Production Deployment Integration

Seamless Local-to-Production Workflow

Move from local development to production with confidence. This workflow demonstrates proper managing staging and production environments:

#!/bin/bash
# deploy_pipeline.sh

# Step 1: Final local testing
echo "Running final local tests..."
dbt test --target dev
visivo test --stage local

# Step 2: Deploy to staging with production data structure
echo "Deploying to staging..."
dbt run --target staging
visivo deploy --stage staging

# Step 3: Run integration tests
echo "Running staging tests..."
dbt test --target staging
visivo test --stage staging

# Step 4: Deploy to production
echo "Deploying to production..."
dbt run --target prod
visivo deploy --stage production

echo "Deployment complete!"

Configuration Management

Manage configurations across environments:

# project.visivo.yml
name: Analytics Project
cli_version: "1.0.74"

# Environment-specific settings
defaults:
  source_name: "{{ env_var('VISIVO_SOURCE', 'duckdb_local') }}"

sources:
  # Local development
  - name: duckdb_local
    type: duckdb
    database: ./dev.duckdb

  # Staging environment
  - name: postgres_staging
    type: postgresql
    host: "{{ env_var('STAGING_DB_HOST') }}"
    database: analytics_staging
    username: "{{ env_var('STAGING_DB_USER') }}"
    password: "{{ env_var('STAGING_DB_PASSWORD') }}"

  # Production environment
  - name: postgres_prod
    type: postgresql
    host: "{{ env_var('PROD_DB_HOST') }}"
    database: analytics_prod
    username: "{{ env_var('PROD_DB_USER') }}"
    password: "{{ env_var('PROD_DB_PASSWORD') }}"

# Environment-specific includes
includes:
  - path: "visivo/{{ env_var('ENVIRONMENT', 'local') }}/**/*.yml"

Results: The Speed Advantage

Development Velocity Metrics

Teams using local dbt™ + Visivo development report dramatic improvements, supporting Grand View Research's finding that "The global data analytics market will reach $684.12 billion by 2030" as organizations invest in efficient development workflows:

Model Development Speed:

  • Local DuckDB: 2-5 seconds per model run
  • Cloud warehouse: 30-60 seconds per model run
  • Speedup: Significantly faster development cycles

Dashboard Development Speed:

  • Local development: Instant feedback on chart changes
  • Cloud deployment: 2-5 minutes per deployment
  • Speedup: Real-time development vs. batch deployment

Cost Reduction:

  • Development costs: Substantial reduction (no cloud compute during development)
  • Experimentation: Unlimited without cost concerns
  • Resource efficiency: No shared environment contention

Real-World Success Story

Before Local Development:

  • Data team of 8 analysts
  • Average 4-5 iterations per model due to cloud delays
  • 45 minutes per analysis cycle (model + dashboard changes)
  • $2,000/month in development compute costs
  • Limited experimentation due to cost and time concerns

After Local dbt™ + Visivo Setup:

  • Same team, same complexity
  • 15-20 iterations per model with instant feedback
  • 3 minutes per analysis cycle (model + dashboard changes)
  • $200/month in development compute costs
  • Unlimited experimentation enabling innovative analytics

Business Impact:

  • Significantly faster development cycles
  • Substantial cost reduction for development
  • More analysis iterations
  • More experimental projects completed
  • Faster time-to-market for new dashboards

Getting Started with Local dbt™ + Visivo Development

Quick Start Guide

# 1. Set up project structure
mkdir analytics-local-dev && cd analytics-local-dev

# 2. Initialize dbt project
dbt init . --skip-profile-setup

# 3. Initialize Visivo project
visivo init

# 4. Install DuckDB
pip install dbt-duckdb

# 5. Configure for local development
cat > profiles.yml << EOF
analytics_local_dev:
  outputs:
    dev:
      type: duckdb
      path: ./dev.duckdb
      threads: 4
  target: dev
EOF

# 6. Create sample data
mkdir data
echo "order_id,customer_id,order_date,order_amount,status" > data/sample_orders.csv
echo "1,cust_1,2024-01-15,99.99,completed" >> data/sample_orders.csv

# 7. Run first model
dbt run

# 8. Start Visivo development server
visivo serve --reload

Best Practices for Team Adoption

  1. Standardize local environments: Use Docker or conda for consistent setups
  2. Share sample datasets: Maintain realistic, anonymized sample data
  3. Document workflows: Create team runbooks for local development
  4. Gradual migration: Start with new projects, migrate existing ones incrementally
  5. Training and support: Invest in team education on local development tools

Local dbt™ development combined with Visivo transforms analytics engineering from a slow, expensive process to a fast, efficient workflow that scales with your team. The ability to iterate rapidly, experiment freely, and develop offline creates a development experience that rivals modern software engineering practices.

For related topics, explore our guides on reproducible BI environments, CI/CD analytics implementation, and visualizations as code.

Start your local development journey today and experience the speed advantage that will transform how your team builds analytics.