Power BI vs Excel for Data Engineers: A Practical Comparison

-- PostgreSQL example: a read-only role for BI tools
CREATE ROLE bi_reader LOGIN PASSWORD 'set-from-secret-store';
GRANT CONNECT ON DATABASE analytics TO bi_reader;
GRANT USAGE ON SCHEMA mart TO bi_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA mart TO bi_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA mart GRANT SELECT ON TABLES TO bi_reader;

-- Pipeline writes one row per dataset after each successful load
CREATE TABLE IF NOT EXISTS mart.dataset_freshness (
  dataset     TEXT PRIMARY KEY,
  loaded_at   TIMESTAMP NOT NULL,
  row_count   BIGINT NOT NULL
);

INSERT INTO mart.dataset_freshness (dataset, loaded_at, row_count)
SELECT 'fact_sales', now(), COUNT(*) FROM mart.fact_sales
ON CONFLICT (dataset) DO UPDATE
  SET loaded_at = EXCLUDED.loaded_at, row_count = EXCLUDED.row_count;

CREATE TABLE mart.dim_product (
  product_key   INT PRIMARY KEY,
  product_name  TEXT NOT NULL,
  category      TEXT NOT NULL
);

CREATE TABLE mart.dim_date (
  date_key      INT PRIMARY KEY,        -- e.g. 20261106
  full_date     DATE NOT NULL UNIQUE,
  month_name    TEXT NOT NULL,
  year_num      INT NOT NULL
);

CREATE TABLE mart.fact_sales (
  order_id      TEXT NOT NULL,
  date_key      INT NOT NULL REFERENCES mart.dim_date(date_key),
  product_key   INT NOT NULL REFERENCES mart.dim_product(product_key),
  quantity      INT NOT NULL,
  net_amount    NUMERIC(12, 2) NOT NULL
);

-- A flat summary view for Excel users (small, readable columns)
CREATE OR REPLACE VIEW mart.v_monthly_category_sales AS
SELECT d.year_num,
       d.month_name,
       p.category,
       SUM(f.quantity)   AS units,
       SUM(f.net_amount) AS revenue
FROM mart.fact_sales f
JOIN mart.dim_date d    ON d.date_key = f.date_key
JOIN mart.dim_product p ON p.product_key = f.product_key
GROUP BY d.year_num, d.month_name, p.category;

import duckdb

con = duckdb.connect()
con.execute("""CREATE TABLE fact_sales AS SELECT * FROM (VALUES
  ('o1', 'COMPLETED', 500.0), ('o2', 'CANCELLED', 300.0), ('o3', 'COMPLETED', 200.0)
) t(order_id, status, net_amount)""")
con.execute("""CREATE VIEW v_revenue AS
  SELECT SUM(net_amount) AS revenue FROM fact_sales WHERE status = 'COMPLETED'""")

expected = 700.0          # agreed definition: completed orders only
actual = con.execute("SELECT revenue FROM v_revenue").fetchone()[0]
assert actual == expected, f"revenue {actual} != {expected}"
print("revenue definition test passed")

Subject: mart.fact_sales change on <date>

What changes: new column 'channel' added; nothing removed
Who is affected: Power BI sales dataset, Excel users of v_monthly_category_sales
Action needed: refresh; no report changes required
Fallback: previous view kept as v_monthly_category_sales_v1 for 2 weeks
Questions: <team channel>

Dataset: mart.fact_sales (+ dim_date, dim_product)
Grain: one row per order line
Key measures: quantity, net_amount (completed orders only)
Refresh: pipeline at 02:00; Power BI refresh scheduled 03:00
Freshness: mart.dataset_freshness
Access: bi_reader role; row-level security by region in Power BI
Excel users: use mart.v_monthly_category_sales
Owner: data engineering (contact channel)

02:00  Pipeline loads raw -> staging -> marts
02:40  Quality checks; freshness table updated
03:00  Power BI dataset refresh (scheduled)
03:15  Refresh failure alert to BI owner
08:00  Users see yesterday's complete data

If the pipeline is late, delay refresh instead of refreshing stale data.

[ ] Star schema with clean keys
[ ] Shared date dimension
[ ] Flat summary views for Excel
[ ] Metric definitions documented and tested
[ ] Read-only role created
[ ] Refresh scheduled after load
[ ] Freshness table exposed
[ ] Schema change process agreed