Why Data Dreamer Exists

Opening paragraph. Every analytics team eventually inherits a dashboard that technically works, but only because a few people know which buttons not to press, which filters not to touch, and which spreadsheet version is secretly the real source of truth.

This post is a practical walkthrough of how I think about turning those fragile dashboard workflows into durable decision systems: systems that have ownership, versioning, scenario logic, validation, auditability, and a clear path from raw data to leadership-ready decisions.

The Problem With “Working” Dashboards

A dashboard can be live, used, and business-critical while still being structurally weak.

The problem usually does not show up as one giant failure. It shows up as small daily friction:

• Someone exports the data to Excel because the dashboard cannot answer the next question.
• Someone changes a target manually because the model does not support exceptions.
• Someone screenshots a chart because leadership wants a static view for a meeting.
• Someone updates logic in SQL but forgets to update the documentation.
• Someone asks, “Why did this number change from yesterday?” and nobody can answer with confidence.

What Makes This Worse

The dashboard often becomes the visible symptom of a deeper operating model issue.

The real problem is not usually Tableau, Power BI, Looker, Snowflake, Python, or whatever tool is being used. The real problem is that the business process has outgrown the data workflow.

A Common Pattern

Here is the pattern I keep seeing:

What a Decision System Needs

A decision system is different from a reporting dashboard.

A reporting dashboard answers:

• What happened?
• Where are we trending?
• What changed since last period?

A decision system answers those questions too, but it also handles:

• Which assumptions were used?
• Who changed the assumptions?
• Which scenario is currently active?
• What was the previous version?
• What was the business rationale?
• What happens if leadership changes one input?
• Which number became the final approved value?

The Core Capabilities

At minimum, I want the system to support five capabilities:

1. Versioning
   Every major change should create a traceable version.

2. Scenario management
   Leadership should be able to compare options before selecting a final view.

3. Input governance
   Manual inputs should be stored, validated, timestamped, and owned.

4. Reproducibility
   The team should be able to recreate yesterday’s number.

5. Narrative context
   Numbers should have explanations, not just values.

A Better Architecture

The goal is not to over-engineer. The goal is to move the fragile parts out of people’s heads and into a system.

Before

A typical fragile workflow looks like this:

Raw tables
  ↓
Long SQL query
  ↓
Dashboard extract
  ↓
Manual export
  ↓
Spreadsheet adjustment
  ↓
Leadership screenshot
  ↓
Final number in slide deck

The problem is that the final business number may no longer be directly traceable to the raw source.

After

A stronger workflow looks like this:

Raw source tables
  ↓
Staging models
  ↓
Business logic layer
  ↓
Scenario input tables
  ↓
Versioned calculation tables
  ↓
Validation checks
  ↓
Dashboard + decision log
  ↓
Approved final scenario

The Data Model

A decision system needs tables that represent the business process, not just the source data.

Example Tables

Scenario Table

create table analytics.scenario (
    scenario_id varchar primary key,
    scenario_name varchar not null,
    scenario_status varchar not null,
    planning_year varchar not null,
    created_by varchar not null,
    created_at timestamp_ntz default current_timestamp(),
    approved_by varchar,
    approved_at timestamp_ntz,
    scenario_description varchar
);

Scenario Input Table

create table analytics.scenario_input (
    input_id varchar primary key,
    scenario_id varchar not null,
    input_category varchar not null,
    input_name varchar not null,
    input_value number(18,4),
    input_text varchar,
    effective_start_date date,
    effective_end_date date,
    created_by varchar not null,
    created_at timestamp_ntz default current_timestamp()
);

Turning Business Logic Into Reusable Layers

One of the biggest improvements is moving repeated dashboard logic into reusable SQL models or views.

Example Business Logic View

create or replace view analytics.vw_base_targets as
select
    account_id,
    region,
    segment,
    fiscal_year,
    historical_revenue,
    renewal_base,
    attrition_rate,
    renewal_base * (1 - attrition_rate) as modeled_target
from analytics.fact_renewal_base
where fiscal_year = (
    select active_fiscal_year
    from analytics.ref_policy
    where is_active = true
);

This creates a stable base layer. Scenarios can then modify the base output without rewriting the whole calculation.

Applying Scenario Inputs

create or replace view analytics.vw_scenario_targets as
select
    b.account_id,
    b.region,
    b.segment,
    b.fiscal_year,
    s.scenario_id,
    s.scenario_name,
    b.modeled_target,
    coalesce(i.input_value, 0) as leadership_adjustment,
    b.modeled_target + coalesce(i.input_value, 0) as scenario_target
from analytics.vw_base_targets b
cross join analytics.scenario s
left join analytics.scenario_input i
    on s.scenario_id = i.scenario_id
    and b.region = i.input_name
    and i.input_category = 'REGION_ADJUSTMENT'
where s.scenario_status in ('draft', 'active', 'approved');

The Role of the Dashboard

Once the logic is versioned and scenario-aware, the dashboard becomes much cleaner.

The dashboard no longer needs to be the place where every calculation happens. It becomes the interface for:

• Reviewing scenario output
• Comparing scenarios
• Finding drivers of change
• Surfacing validation warnings
• Showing final approved values
• Explaining the decision history

Dashboard Sections

A strong decision dashboard might have these sections:

Handling Leadership Inputs

Leadership inputs are not the enemy. They are often necessary.

The problem is when those inputs are informal, invisible, or impossible to audit.

Bad Input Pattern

"Can we increase the East region target by 3%?"
"Sure, I’ll adjust it in the spreadsheet."

Better Input Pattern

Input category: REGION_ADJUSTMENT
Input name: East
Value: +3%
Scenario: FY27 Leadership Case
Requested by: VP Sales
Reason: Higher expected enterprise renewal motion
Effective period: FY27

Example Input Record

{
  "scenario_id": "FY27_LEADERSHIP_CASE",
  "input_category": "REGION_ADJUSTMENT",
  "input_name": "East",
  "input_value": 0.03,
  "input_text": "Increase East target by 3% based on leadership guidance.",
  "created_by": "analytics_team",
  "effective_start_date": "2026-02-01",
  "effective_end_date": "2027-01-31"
}

Validation Rules

Every decision system needs validation rules.

Validation is what prevents the system from becoming a polished way to publish bad numbers.

Example Validation Checks

SQL Validation Example

create or replace table analytics.validation_result as
select
    scenario_id,
    account_id,
    'NEGATIVE_TARGET' as validation_code,
    'error' as severity,
    'Scenario target is below zero.' as message,
    current_timestamp() as checked_at
from analytics.vw_scenario_targets
where scenario_target < 0

union all

select
    scenario_id,
    account_id,
    'LARGE_TARGET_MOVEMENT' as validation_code,
    'warning' as severity,
    'Scenario target changed by more than 25% from modeled target.' as message,
    current_timestamp() as checked_at
from analytics.vw_scenario_targets
where abs(scenario_target - modeled_target) / nullif(modeled_target, 0) > 0.25;

Versioning the Process

Versioning is where the workflow starts to feel mature.

Instead of constantly asking “which number is right?”, the team can ask “which version are we looking at?”

Version States

Example Version Flow

Draft scenario
  ↓
Analytics review
  ↓
Leadership review
  ↓
Revision requested
  ↓
Scenario updated
  ↓
Validation passed
  ↓
Approved scenario
  ↓
Final dashboard view

Change Analysis

A good system does not only show the final number. It explains how the number moved.

Movement Table

Change Query Example

select
    current.scenario_id,
    current.region,
    previous.scenario_target as previous_target,
    current.scenario_target as current_target,
    current.scenario_target - previous.scenario_target as movement
from analytics.scenario_snapshot current
join analytics.scenario_snapshot previous
    on current.region = previous.region
    and current.account_id = previous.account_id
where current.scenario_id = 'FY27_FINAL_REVIEW'
  and previous.scenario_id = 'FY27_BASELINE';

Example Python Check

SQL is often enough, but Python can help for more advanced checks, summaries, and exports.

import pandas as pd

def flag_outliers(df: pd.DataFrame, value_col: str, group_col: str) -> pd.DataFrame:
    results = []

    for group, group_df in df.groupby(group_col):
        mean = group_df[value_col].mean()
        std = group_df[value_col].std()

        group_df = group_df.copy()
        group_df["z_score"] = (group_df[value_col] - mean) / std
        group_df["is_outlier"] = group_df["z_score"].abs() > 3

        results.append(group_df)

    return pd.concat(results, ignore_index=True)

Why Python Helps

Python is useful when the validation logic becomes more statistical or when the team wants to generate files, summaries, or model diagnostics.

However, Python should not become a hidden second system.

What the Blog Cover Could Show

Use a cover image that visually communicates the transformation from chaos to clarity.

Optional Screenshot Grid

Use an image grid when you want to compare before and after states.

Operational Checklist

Before calling the system production-ready, I would check these items.

Data Layer

• Source tables are documented.
• Business logic is separated from raw extraction.
• Scenario inputs are stored in tables.
• Manual adjustments have owners and timestamps.
• Snapshots are preserved.

Dashboard Layer

• The dashboard clearly identifies the selected scenario.
• Draft and approved scenarios are visually distinct.
• Validation warnings are visible.
• Filters do not accidentally change the meaning of executive numbers.
• The dashboard supports the actual meeting flow.

Governance Layer

• Final scenarios require approval.
• Changes are logged.
• Historical versions are preserved.
• Documentation explains the calculation flow.
• Known limitations are visible to users.

[ ] Source tables identified
[ ] Refresh cadence documented
[ ] Data owners assigned
[ ] Scenario table created
[ ] Scenario input table created
[ ] Snapshot table created
[ ] Validation result table created
[ ] Dashboard connected to governed views
[ ] Final scenario logic documented
[ ] Approval process documented
[ ] Rollback process documented
[ ] Known risks documented
[ ] Access permissions reviewed
[ ] Sample output tested
[ ] Leadership review completed

Where Teams Usually Overcomplicate This

The first instinct is often to buy another tool, rebuild the dashboard, or create a complicated app.

That may eventually be useful, but the first version can usually be much simpler.

Start With the Workflow

Ask these questions first:

1. What decisions are being made?
2. Which numbers influence those decisions?
3. Who can change those numbers?
4. Which changes need approval?
5. What history needs to be preserved?
6. What needs to be explained in a meeting?
7. What needs to be reproducible six months later?

A Practical MVP

A strong MVP does not need to include every feature.

MVP Scope

MVP Flow

Create scenario
  ↓
Load baseline data
  ↓
Add leadership inputs
  ↓
Run calculation
  ↓
Validate output
  ↓
Review dashboard
  ↓
Approve scenario
  ↓
Lock snapshot

Lessons Learned

The biggest lesson is that analytics maturity is not just about better visuals.

It is about moving from isolated reporting assets to reusable decision infrastructure.

Lesson 1: Store Assumptions

If an assumption affects the final number, it belongs in the system.

Lesson 2: Preserve Snapshots

If a number was used in a leadership meeting, preserve the version that produced it.

Lesson 3: Make Validation Visible

Validation should not be hidden in the backend. Users need to see whether they are looking at clean, warning-level, or failed data.

Lesson 4: Design for Explanation

The system should explain movement, not just display totals.

Lesson 5: Keep the Interface Calm

A premium analytics experience is not about adding more charts. It is about reducing confusion.

Final Architecture Summary

Here is the simplified architecture I would aim for:

Snowflake source data
  ↓
Staging views
  ↓
Modeled business logic
  ↓
Scenario + input tables
  ↓
Calculation snapshots
  ↓
Validation results
  ↓
Dashboard experience
  ↓
Decision log and approved scenario

What This Gives You

• Cleaner dashboards
• Fewer mystery numbers
• Better leadership conversations
• Reproducible outputs
• More confidence in final decisions
• Less dependency on tribal knowledge
• A path toward automation later

Next Steps

The next step is to build a small working version.

Start with one planning process, one set of metrics, and one decision cycle. Do not try to solve every analytics workflow at once.

A useful first build would include:

1. A scenario table.
2. A scenario input table.
3. A calculation snapshot table.
4. A validation result table.
5. A dashboard page that compares baseline, draft, and approved scenarios.

Once that is stable, the system can grow into a more complete planning platform.