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 lets a data engineer build and test transformations on their own machine, with a feedback loop measured in seconds. According to VentureBeat, "87% of data science projects never make it to production," and a slow development cycle with no immediate feedback is a common reason.

No waiting on a cloud warehouse, no shared development schema. Add Visivo and the same loop covers the dashboards too: model, chart and test all run on the laptop.

Why local development matters

Developing against a shared cloud warehouse has a cost at every step:

  • Cloud delays: 30-60 seconds for every model run in a cloud warehouse
  • Resource contention: shared development environments become a bottleneck
  • Cost: every test run spends compute credits
  • Less experimentation: nobody wants to break a shared resource
  • No offline work: no connection, no development

MIT research finds that "Companies using data-driven strategies have 5-6% higher productivity." That gain depends on a workflow that lets people iterate quickly.

Local development removes that friction without giving up fidelity to production, as long as the tooling and the workflow are set up for it.

The local stack: dbt™, Visivo and DuckDB

Four tools cover the whole loop on one laptop:

  • dbt™: transformations and data models
  • Visivo: dashboards, visualizations and deployment
  • DuckDB: a fast, embedded analytical database
  • Git: version control and collaboration

This is the workflow behind developer-first BI and faster feedback cycles.

Local development setup

1. Install the 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

One repository holds the dbt™ project and the Visivo configuration:

# 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
    ├── insights/
    │   ├── customer_insights.yml
    │   └── revenue_insights.yml
    └── dashboards/
        ├── executive.yml
        └── analytics.yml

3. Database configuration

Configure DuckDB as your local development database:

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

  target: dev
# project.visivo.yml - Integrated with dbt
name: Local Analytics Development

# Integration with dbt: reads dbt_project.yml, profiles.yml and
# target/manifest.json from the working directory
dbt:
  enabled: true

defaults:
  source_name: duckdb_local

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

includes:
  - path: visivo/

With dbt: enabled: true, Visivo reads the dbt™ manifest on every visivo run and visivo serve. It turns each target in profiles.yml into a source named <profile>_<target> (here analytics_project_dev) and each dbt™ model into a Visivo model with the same name, so an Insight can reference ${ref(customer_analytics)} directly. Visivo uses the default target from profiles.yml; pass --dbt-target to pick another one.

Development workflow

Sample data

Load sample data that mirrors the production schema:

-- 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™ models

The models read the sample tables like any other source:

-- 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

Insights on dbt™ models

The Insights reference the dbt™ model customer_analytics by name. For the wider pattern, see BI-as-code scalable systems and dbt™ BI integration:

# visivo/insights/customer_insights.yml
insights:
  - name: customer_value_distribution
    props:
      type: scatter
      mode: markers
      x: ?{ ${ref(customer_analytics).total_orders} }
      y: ?{ ${ref(customer_analytics).total_revenue} }
      marker:
        size: ?{ ${ref(customer_analytics).avg_order_value} }
        sizemode: diameter
        sizeref: 8
        opacity: 0.7
      text: ?{ ${ref(customer_analytics).customer_name} }
      hovertemplate: |
        Customer: %{text}<br>
        Orders: %{x}<br>
        Revenue: $%{y:,.2f}<br>
        AOV: $%{marker.size:,.2f}<br>
        <extra>%{fullData.name}</extra>
    interactions:
      - split: ?{ ${ref(customer_analytics).customer_segment} }

  - name: customer_lifecycle_funnel
    props:
      type: funnel
      x: ?{ COUNT(${ref(customer_analytics).customer_id}) }
      y: ?{ ${ref(customer_analytics).lifecycle_stage} }
      text: ?{ COUNT(${ref(customer_analytics).customer_id}) }
      texttemplate: "%{text} customers"
    interactions:
      - split: ?{ ${ref(customer_analytics).lifecycle_stage} }

  - name: segment_performance_heatmap
    props:
      type: heatmap
      x: ?{ ${ref(customer_analytics).frequency_segment} }
      y: ?{ ${ref(customer_analytics).value_segment} }
      z: ?{ SUM(${ref(customer_analytics).total_revenue}) }
      colorscale: "Blues"
      text: ?{ SUM(${ref(customer_analytics).total_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
              **Model Used:** customer_analytics (dbt)

      # KPI Row
      - height: small
        items:
          - width: 1
            chart:
              name: total-customers-kpi
              insights:
                - name: total-customers
                  props:
                    type: indicator
                    mode: number
                    value: ?{ COUNT(DISTINCT ${ref(customer_analytics).customer_id}) }[0]
                    title:
                      text: "Total Customers"
                    number:
                      font:
                        size: 24

          - width: 1
            chart:
              name: total-revenue-kpi
              insights:
                - name: total-revenue
                  props:
                    type: indicator
                    mode: number
                    value: ?{ SUM(${ref(customer_analytics).total_revenue}) }[0]
                    title:
                      text: "Total Revenue"
                    number:
                      prefix: "$"
                      font:
                        size: 24

          - width: 1
            chart:
              name: average-aov-kpi
              insights:
                - name: average-aov
                  props:
                    type: indicator
                    mode: number
                    value: ?{ AVG(${ref(customer_analytics).avg_order_value}) }[0]
                    title:
                      text: "Average AOV"
                    number:
                      prefix: "$"
                      font:
                        size: 24

      # Main Analysis
      - height: large
        items:
          - width: 2
            chart:
              name: customer-value-chart
              insights:
                - ${ref(customer_value_distribution)}
              layout:
                title:
                  text: "Customer Value Distribution"
                xaxis:
                  title:
                    text: "Total Orders"
                yaxis:
                  title:
                    text: "Total Revenue ($)"

          - width: 1
            chart:
              name: customer-lifecycle-chart
              insights:
                - ${ref(customer_lifecycle_funnel)}
              layout:
                title:
                  text: "Customer Lifecycle"

      # Detailed Analysis
      - height: medium
        items:
          - width: 3
            chart:
              name: segment-performance-chart
              insights:
                - ${ref(segment_performance_heatmap)}
              layout:
                title:
                  text: "Segment Performance Matrix"
                xaxis:
                  title:
                    text: "Frequency Segment"
                yaxis:
                  title:
                    text: "Value Segment"

A Visivo dashboard built on the dbt model customer_analytics, with KPI indicators, a customer value scatter split by segment and a lifecycle funnel Customer Analytics Dashboard reading the dbt™-built customer_analytics model through Visivo's dbt™ integration, with synthetic sample data.

The fast loop

The 30-second cycle

The whole cycle, model to dashboard, fits in one script:

#!/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

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

Watch mode

Rebuild on save with a file watcher:

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

# Terminal 2: Run the Visivo development server
visivo serve --port 8080

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

When you save a dbt™ model:

  1. watchfiles detects the change and runs dbt run
  2. Visivo rebuilds and reruns the project when a Visivo YAML file changes, so save a Visivo YAML file (or restart visivo serve) to pull in the new dbt™ output
  3. Browser shows the updated dashboard

Our faster feedback cycles guide goes deeper on why this loop matters.

More local patterns

Several local environments

dbt™ targets give you more than one local database:

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

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

  target: dev
# Switch between environments easily
dbt run --target dev            # Uses DuckDB
visivo serve --dbt-target dev

dbt run --target sample         # Uses sample data
visivo serve --dbt-target sample

Keep profiles.yml free of templated values that Visivo needs to read. Visivo parses profiles.yml as plain YAML and does not render dbt™ Jinja, so a templated target: is not found. Visivo also turns every target in profiles.yml into a source, and its dbt™ integration does not accept a target of type postgres, so keep Postgres targets out of the profiles.yml that Visivo reads.

Sample data from production

Pull an anonymized sample from production so local results look like real ones:

-- 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

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"}'

From local to production

The deploy pipeline

The commands that run locally run the same way against staging and production targets. See managing staging and production environments for the full pattern:

#!/bin/bash
# deploy_pipeline.sh

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

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

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

echo "Deployment complete!"

Configuration across environments

One project.visivo.yml declares every source, and environment variables fill in the credentials:

# project.visivo.yml
name: Analytics Project

# The default source for models that do not come from dbt
defaults:
  source_name: duckdb_local

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

  # Staging environment
  - name: postgres_staging
    type: postgresql
    host: ${env.STAGING_DB_HOST}
    database: analytics_staging
    username: ${env.STAGING_DB_USER}
    password: ${env.STAGING_DB_PASSWORD}

  # Production environment
  - name: postgres_prod
    type: postgresql
    host: ${env.PROD_DB_HOST}
    database: analytics_prod
    username: ${env.PROD_DB_USER}
    password: ${env.PROD_DB_PASSWORD}

includes:
  - path: visivo/

${env.VAR_NAME} works on source connection fields, and Visivo loads a .env file from the project root. To use a different default source in one environment, pass --source (for example visivo run --source postgres_staging). Models that come from dbt™ use the dbt™ target you pick with --dbt-target.

The speed difference

Development speed

Local dbt™ + Visivo development can bring large improvements. Grand View Research finds that "The global data analytics market will reach $684.12 billion by 2030" as organizations invest in efficient development workflows. The numbers that matter day to day:

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

Getting started

Quick start

# 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 (--bare writes the files without starting the server)
visivo init --bare

# 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. Turn on the dbt integration in project.visivo.yml
cat >> project.visivo.yml << EOF
dbt:
  enabled: true
EOF

# 9. Start the Visivo development server
visivo serve

Team adoption

  1. Standardize local environments: Use Docker or conda so every machine matches
  2. Share sample datasets: Keep one realistic, anonymized sample in the repository
  3. Document workflows: Write a team runbook for local development
  4. Migrate gradually: Start with new projects, then move existing ones
  5. Train the team: Budget time for people to learn the local tools

Local dbt™ plus Visivo gives analytics the development loop software engineers already have: edit, run, see the result, in seconds, on your own machine, offline if you need to.

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

The quick start above takes a few minutes. /get-started covers installing Visivo, and the Visivo documentation has the full reference for the dbt™ integration.

Install command copied