Tableau vs Excel for Data Engineers: A Practical Comparison

-- A stable view for both Excel and Tableau users
CREATE OR REPLACE VIEW reporting.daily_sales AS
SELECT
  order_date,
  region,
  product_category,
  COUNT(DISTINCT order_id) AS orders,
  SUM(net_amount)          AS revenue
FROM mart.fact_orders
WHERE order_status = 'COMPLETED'
GROUP BY order_date, region, product_category;

-- Read-only access for the BI role
GRANT SELECT ON reporting.daily_sales TO bi_reader;

import duckdb

# Export a ready-made extract for Excel users
con = duckdb.connect("warehouse.duckdb")
con.execute("""
  COPY (
    SELECT * FROM reporting.daily_sales
    WHERE order_date >= current_date - INTERVAL 90 DAY
    ORDER BY order_date
  ) TO 'daily_sales_last_90_days.csv' (HEADER, DELIMITER ',')
""")
print("extract written")

import duckdb

EXCEL_ROW_LIMIT = 1_048_576
con = duckdb.connect("warehouse.duckdb")

for table in ["reporting.daily_sales", "mart.fact_orders"]:
    rows = con.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    status = "OK for Excel" if rows < EXCEL_ROW_LIMIT * 0.5 else "Too big: aggregate or filter"
    print(f"{table:24} {rows:>10,} rows  {status}")

import duckdb

con = duckdb.connect("warehouse.duckdb")
con.execute("""
  CREATE OR REPLACE TABLE reporting.monthly_sales AS
  SELECT date_trunc('month', order_date) AS month,
         region,
         SUM(revenue) AS revenue,
         SUM(orders)  AS orders
  FROM reporting.daily_sales
  GROUP BY 1, 2
""")
print(con.execute("SELECT COUNT(*) FROM reporting.monthly_sales").fetchone()[0], "rows")

-- Add load metadata so every tool can show freshness
CREATE OR REPLACE TABLE reporting.daily_sales_tbl AS
SELECT *, current_timestamp AS loaded_at
FROM reporting.daily_sales;

-- A tiny freshness table both Excel and Tableau can display
CREATE OR REPLACE VIEW reporting.data_freshness AS
SELECT 'daily_sales' AS dataset, MAX(loaded_at) AS last_loaded
FROM reporting.daily_sales_tbl;

from datetime import datetime, timedelta
import duckdb

con = duckdb.connect("warehouse.duckdb")
last = con.execute("SELECT last_loaded FROM reporting.data_freshness").fetchone()[0]
age = datetime.now() - last
if age > timedelta(hours=26):
    print(f"ALERT: daily_sales is {age} old; dashboards may be stale")
else:
    print("daily_sales is fresh:", last)

Dataset: reporting.daily_sales
Grain: one row per date x region x product_category
Columns: order_date, region, product_category, orders, revenue
Filters applied: completed orders only
Refresh: daily after 02:00 pipeline run
Freshness: see reporting.data_freshness
Owner: data engineering team (contact channel)
Best for: Tableau (detail), Excel (use monthly_sales)

Subject: Change to reporting.daily_sales on <date>

What changes: new column 'channel' added
What stays: all existing columns and names unchanged
Action needed: none; refresh to see the new column
Rollback: previous view kept as reporting.daily_sales_v1
Questions: <contact channel>

[ ] Grain documented
[ ] Business filters documented
[ ] Column names final and stable
[ ] Read-only role granted
[ ] Excel-sized summary available
[ ] Refresh scheduled after load
[ ] loaded_at / freshness exposed
[ ] Owner and contact listed