BI, dashboards, data visualization
Created: 2026-10-12 Mon 15:56
Collecting data has one main purpose: gain insights about your business and make conscious data-driven decisions. This is why we need to create:
All this steps sit in a perpetual circle where each iteration is re-purposed and improved over time as business requirements or input data change.
Business intelligence sits in between of input data and insights. It is really crucial to understand:
Business intelligences keeps on improving and adapting to each moving component to optimize data processing and provide faster and more insightful results.
The process works at such:
To create an efficient business intelligence we have two
It is essential to create a proper data model to create structures where both atomistic and aggregated information are stored in a persistent way:
To efficiently access the data and understand what is the best way to create a pipeline or query the data we need to provide consistent metadata to describe the content of directories and tables. We should periodically review the content and understand whether we need to test different strategies. Metadata should contain the table description and how tables can be linked together, what is the best index to join tables and whether there is an aggregated version which will improve performance.
Dbt is “data build tool” focused for business intelligence and data transformation. It treats a warehouse as a computing engine:
#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 setupInfo Created .vscode/extensions.json with dbt extension recommendationSuccess Project created successfully!Info Project name: jaffle_shopInfo Project directory: jaffle_shopInfo 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
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
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\')') }}
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
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"}'
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 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
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
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') }}=
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"]
}
}
) }}
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 views2. 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 changes4. Collaboration- Write clear, documented SQL- Add tests for data quality- Generate and maintain documentation5. Performance- Materialize large models as tables- Use incremental updates where possible- Partition by time when appropriate
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.
Often we need to subset the data or run different types of queries
Use an LLM to filter the data