Here's a scenario that plays out in nearly every data-mature organization: your analytics engineers have built a beautiful dbt project — carefully modeled dimensions, well-tested facts, documented sources, the works. Business users are excited. Then someone opens Tableau, connects directly to the raw schema, drags in a field called rev_adj_final_v3, and publishes a dashboard that gets cited in a board meeting. Now you have a governance crisis, three conflicting definitions of "revenue," and a data team that's one bad quarter away from losing all credibility.
The antidote isn't locking everything down so hard that analysts have to file a Jira ticket to count rows. It's building a genuinely self-serve platform — one where business users can explore freely within a governed perimeter. That means exposing the right models (not all models), defining metrics in a single place (not per-workbook), and making Tableau or Looker a well-lit, well-signed room rather than an endless warehouse with the lights off.
By the end of this lesson, you'll have the full architecture in place: dbt Exposures that document exactly which BI tools consume which models, a semantic layer that centralizes metric definitions so numbers never diverge, and a connection pattern for Tableau or Looker that respects row-level security, hides internal models, and gives business users a curated, trustworthy surface to explore. This is expert-level content — we'll go into the internals, the edge cases, and the architectural trade-offs that matter at scale.
What you'll learn:
Before diving in, you should be comfortable with:
ref() and the DAG.The mistake most teams make is building these components independently and hoping they'll integrate cleanly later. They won't. Let's establish the target architecture upfront so every decision has context.
The platform has four layers:
1. The Warehouse Layer — Snowflake (or equivalent) with multiple schemas: raw, staging, intermediate, marts. Business users should never touch raw or staging. They get read-only access to marts schema objects, and even within marts, only specific tables and views.
2. The Transformation Layer — dbt. This is where your business logic lives. Models in marts/ are the governed output. dbt Exposures document the downstream consumers of those models.
3. The Semantic Layer — This sits between dbt's output and the BI tool. It's where revenue becomes a defined, tested, unambiguous metric rather than SUM(amount_usd) written slightly differently in 40 workbooks.
4. The BI Layer — Tableau or Looker. Business users interact here. Their experience is curated: they see a clean set of dimensions and metrics, not raw tables. Looker's LookML is actually a semantic layer itself, which creates interesting architectural choices we'll explore. Tableau with dbt's semantic layer is a different but equally valid path.
The governance mechanism runs through all four: dbt tests validate the data, Exposures track who uses what, the semantic layer enforces consistent definitions, and the BI tool enforces who can see what data.
An Exposure in dbt is a YAML block that declares a downstream consumer of your models. Here's what most tutorials show you:
exposures:
- name: executive_revenue_dashboard
type: dashboard
maturity: high
url: https://tableau.company.com/views/ExecutiveRevenue
description: "Revenue dashboard for executive team"
owner:
name: Jane Smith
email: jane@company.com
depends_on:
- ref('fct_orders')
- ref('dim_customers')
This is fine as documentation. It's not fine as governance. The problem is that it's manually maintained, which means it rots. Six months after this dashboard is published, fct_orders gets refactored into fct_orders_v2, the Exposure never gets updated, and you've lost lineage.
Here's how to use Exposures with teeth:
Treat Exposures as contracts. Every model that has an Exposure depending on it gets tagged and protected. In your dbt_project.yml:
models:
my_project:
marts:
+tags: ['marts']
+meta:
governance_tier: 'production'
finance:
+tags: ['finance', 'governed']
+meta:
governance_tier: 'critical'
sla_freshness_hours: 4
Then write a CI check that prevents renaming or deprecating any model that has an active Exposure. This is a shell script that runs in your CI pipeline:
#!/bin/bash
# check_exposure_dependencies.sh
# Fails if any model referenced in an exposure has been deleted or renamed
EXPOSED_MODELS=$(python3 -c "
import yaml, glob, sys
exposed = set()
for f in glob.glob('models/**/*.yml', recursive=True):
with open(f) as fh:
data = yaml.safe_load(fh) or {}
for exp in data.get('exposures', []):
for dep in exp.get('depends_on', []):
if dep.startswith('ref('):
model_name = dep[4:-1].strip(\"'\")
exposed.add(model_name)
print('\n'.join(exposed))
")
MISSING=0
for model in $EXPOSED_MODELS; do
if ! find models/ -name "${model}.sql" | grep -q .; then
echo "ERROR: Model '${model}' is referenced in an exposure but does not exist"
MISSING=1
fi
done
exit $MISSING
This runs before dbt run in CI. If someone deleted fct_orders and forgot to update the Exposure, the build fails with a clear error.
The other place teams underuse Exposures is stakeholder metadata. dbt's meta field is freeform — use it:
exposures:
- name: finance_monthly_close_pack
type: dashboard
maturity: high
url: https://tableau.company.com/views/FinanceMonthlyClose/Summary
description: >
Monthly close reporting package used by Finance during the close process.
This dashboard drives the Board pack and feeds the CFO narrative.
Changes to any dependent model require a 5-day notice to Finance.
owner:
name: Revenue Analytics Team
email: revenue-analytics@company.com
meta:
business_criticality: P0
change_notification_days: 5
stakeholders:
- name: CFO Office
slack_channel: '#finance-close'
- name: VP Revenue
slack_channel: '#revenue-ops'
data_classification: confidential
refresh_schedule: "daily at 06:00 UTC"
governance_approved: true
approval_date: "2024-01-15"
depends_on:
- ref('fct_monthly_revenue')
- ref('fct_arr_movements')
- ref('dim_accounts')
- ref('dim_products')
This metadata is queryable via dbt ls and the dbt manifest JSON. You can write alerting pipelines that, when a model in the DAG upstream of a P0 Exposure fails, immediately pages the on-call engineer and sends a Slack message to the listed stakeholder channels. That's real governance — not a YAML comment nobody reads.
The manifest.json that dbt generates after dbt compile contains the full DAG including Exposures. This is how you build automated workflows:
import json
from pathlib import Path
from collections import defaultdict
def get_exposure_impact(manifest_path: str, changed_model: str) -> dict:
"""
Given a changed model name, return all Exposures that depend on it
(directly or transitively) and their metadata.
"""
with open(manifest_path) as f:
manifest = json.load(f)
nodes = manifest['nodes']
exposures = manifest['exposures']
# Build reverse dependency map
dependents = defaultdict(set)
for node_id, node in nodes.items():
for dep in node.get('depends_on', {}).get('nodes', []):
dependents[dep].add(node_id)
# Add exposure dependencies
exposure_deps = {}
for exp_id, exp in exposures.items():
for dep in exp.get('depends_on', {}).get('nodes', []):
dependents[dep].add(exp_id)
exposure_deps[exp_id] = exp
# BFS from changed model
model_node_id = f"model.my_project.{changed_model}"
visited = set()
queue = [model_node_id]
affected_exposures = []
while queue:
current = queue.pop(0)
if current in visited:
continue
visited.add(current)
for dep in dependents.get(current, set()):
if dep.startswith('exposure.'):
exp = exposure_deps[dep]
affected_exposures.append({
'name': exp['name'],
'url': exp.get('url'),
'criticality': exp.get('meta', {}).get('business_criticality'),
'slack_channels': [
s['slack_channel']
for s in exp.get('meta', {}).get('stakeholders', [])
]
})
else:
queue.append(dep)
return affected_exposures
Run this in your CI pipeline when a PR modifies a model, and post the results as a PR comment: "This change affects 3 downstream dashboards, including 1 P0 exposure used by the CFO Office." That changes the conversation engineers have with product managers about model changes.
A semantic layer is the single place where revenue is defined. Without it, you have:
SUM(amount_usd)SUM(amount_usd) - SUM(refunds)SUM(amount_usd) * exchange_rateThe semantic layer says: revenue is SUM(amount_usd) - SUM(refunds), always, for everyone, no exceptions. The BI tool's job becomes visualization, not calculation.
As of dbt 1.6+, the dbt Semantic Layer uses MetricFlow as its engine. If you're on dbt Cloud, this is available via the Semantic Layer API. If you're on dbt Core, you can use MetricFlow CLI directly or integrate Cube.dev as an alternative (we'll cover that below).
Here's how to define semantic models and metrics properly. The starting point is always your marts models:
-- models/marts/finance/fct_monthly_revenue.sql
SELECT
order_id,
customer_id,
account_id,
product_id,
order_date,
close_date,
amount_usd,
refund_amount_usd,
currency_code,
is_new_business,
revenue_category,
fiscal_quarter,
fiscal_year
FROM {{ ref('int_orders_enriched') }}
WHERE order_status = 'completed'
Now define the semantic model over this fact table:
# models/marts/finance/_semantic_models.yml
semantic_models:
- name: monthly_revenue
description: >
Revenue facts at the order grain. This is the authoritative source
for all revenue reporting. Do not use fct_orders directly in BI tools.
model: ref('fct_monthly_revenue')
defaults:
agg_time_dimension: order_date
entities:
- name: order
type: primary
expr: order_id
- name: customer
type: foreign
expr: customer_id
- name: account
type: foreign
expr: account_id
- name: product
type: foreign
expr: product_id
dimensions:
- name: order_date
type: time
type_params:
time_granularity: day
- name: close_date
type: time
type_params:
time_granularity: day
- name: is_new_business
type: categorical
- name: revenue_category
type: categorical
- name: fiscal_quarter
type: categorical
- name: fiscal_year
type: categorical
measures:
- name: order_amount_usd
description: "Gross order value in USD before refunds"
agg: sum
expr: amount_usd
- name: refund_amount_usd
description: "Total refunds in USD"
agg: sum
expr: refund_amount_usd
- name: order_count
description: "Count of completed orders"
agg: count_distinct
expr: order_id
- name: new_business_order_count
description: "Count of new business orders (first purchase by account)"
agg: count_distinct
expr: "CASE WHEN is_new_business THEN order_id END"
Now define metrics over those measures. This is the critical step — measures are building blocks, metrics are what business users actually ask for:
metrics:
- name: revenue
label: "Revenue (Net)"
description: >
Net revenue after refunds, in USD. This is the company's primary
revenue metric and should be used in all board-level reporting.
type: derived
type_params:
expr: order_amount_usd - refund_amount_usd
metrics:
- name: order_amount_usd
- name: refund_amount_usd
meta:
owner: "Revenue Analytics"
certified: true
certification_date: "2024-01-15"
- name: gross_revenue
label: "Revenue (Gross)"
description: "Gross revenue before refunds. Use for volume analysis only."
type: simple
type_params:
measure: order_amount_usd
meta:
owner: "Revenue Analytics"
certified: true
- name: new_business_revenue
label: "New Business Revenue"
description: >
Revenue from first-time purchases only. Filters to is_new_business = true.
type: simple
type_params:
measure: order_amount_usd
filter: "{{ Dimension('monthly_revenue__is_new_business') }} = true"
meta:
owner: "Sales Analytics"
certified: true
- name: refund_rate
label: "Refund Rate"
description: "Refunds as a percentage of gross revenue. Target: < 2%."
type: ratio
type_params:
numerator: refund_amount_usd
denominator: order_amount_usd
meta:
owner: "Revenue Analytics"
certified: true
Critical architectural point: Notice that
revenueis defined asorder_amount_usd - refund_amount_usd, not as a measure directly. This means when MetricFlow generates SQL, it will join the underlying measures correctly regardless of the grain of the query. If someone asks for revenue by account, MetricFlow handles the fanout problem automatically. This is the core value proposition that raw SQL in Tableau can never provide.
MetricFlow has a CLI for validation. Run this before any deployment:
# Validate all semantic models parse correctly
dbt sl validate
# Query a metric to verify the SQL generated is correct
dbt sl query --metrics revenue --group-by metric_time__month --limit 10
# Check for ambiguous joins (critical — MetricFlow will error at runtime otherwise)
dbt sl list metrics
dbt sl list dimensions --metrics revenue
The most common failure at this stage is ambiguous join paths — when two semantic models can be joined via multiple entity paths and MetricFlow doesn't know which to use. Resolve this by being explicit about join types in your semantic model entities.
If you're not on dbt Cloud or need a semantic layer that works across multiple dbt projects and other data sources, Cube.dev is the mature alternative. It compiles to optimized SQL, has its own caching layer (Cube Store), and exposes a REST/GraphQL API that both Tableau and Looker can consume.
A Cube schema for the same revenue model:
// cubes/revenue.js
cube('Revenue', {
sql_table: `analytics.fct_monthly_revenue`,
measures: {
gross_revenue: {
sql: `amount_usd`,
type: `sum`,
title: 'Gross Revenue (USD)',
description: 'Revenue before refunds',
},
refunds: {
sql: `refund_amount_usd`,
type: `sum`,
title: 'Refund Amount (USD)',
},
net_revenue: {
sql: `${gross_revenue} - ${refunds}`,
type: `number`,
title: 'Net Revenue (USD)',
description: 'Authoritative revenue metric for all reporting',
format: 'currency',
},
refund_rate: {
sql: `${refunds} / NULLIF(${gross_revenue}, 0)`,
type: `number`,
title: 'Refund Rate',
format: 'percent',
},
},
dimensions: {
order_id: {
sql: `order_id`,
type: `string`,
primary_key: true,
},
order_date: {
sql: `order_date`,
type: `time`,
},
revenue_category: {
sql: `revenue_category`,
type: `string`,
},
is_new_business: {
sql: `is_new_business`,
type: `boolean`,
},
},
// Pre-aggregations dramatically reduce query time at scale
pre_aggregations: {
monthly_revenue_by_category: {
measures: [Revenue.gross_revenue, Revenue.refunds, Revenue.net_revenue],
dimensions: [Revenue.revenue_category],
time_dimension: Revenue.order_date,
granularity: `month`,
refresh_key: {
every: `1 hour`,
},
},
},
});
The architectural trade-off: Cube adds a network hop and operational overhead (you're running another service), but it gives you pre-aggregation caching (huge for query performance), multi-source federation, and a richer API surface. The dbt Semantic Layer is more tightly integrated with your dbt lineage but requires dbt Cloud for production use.
Most Tableau governance failures happen at the connection layer. Business users connect to Snowflake using their own credentials or, worse, a shared admin credential. Every query runs with full warehouse access. There's no row-level security, no schema restriction, nothing.
The governed architecture uses Published Data Sources on Tableau Server, not live direct connections. Here's why: a Published Data Source is a single, IT-controlled artifact. It has its own connection credentials (a service account with restricted permissions), embedded row-level security, and a defined set of fields that users can see. Business users connect to the Published Data Source — they never see Snowflake credentials or schema names.
The Snowflake side of this requires careful role design:
-- Snowflake RBAC setup for governed Tableau access
-- Run this as ACCOUNTADMIN
-- Create a dedicated role for Tableau service account
CREATE ROLE TABLEAU_READER;
-- Grant usage on the warehouse (not the raw one — a separate, sized-for-reads warehouse)
GRANT USAGE ON WAREHOUSE ANALYTICS_WH TO ROLE TABLEAU_READER;
-- Grant access to marts schema ONLY
GRANT USAGE ON DATABASE ANALYTICS_DB TO ROLE TABLEAU_READER;
GRANT USAGE ON SCHEMA ANALYTICS_DB.MARTS TO ROLE TABLEAU_READER;
-- Grant SELECT only on specific tables/views, not all objects
-- Do NOT use GRANT SELECT ON ALL TABLES — be explicit
GRANT SELECT ON TABLE ANALYTICS_DB.MARTS.FCT_MONTHLY_REVENUE TO ROLE TABLEAU_READER;
GRANT SELECT ON TABLE ANALYTICS_DB.MARTS.DIM_CUSTOMERS TO ROLE TABLEAU_READER;
GRANT SELECT ON TABLE ANALYTICS_DB.MARTS.DIM_ACCOUNTS TO ROLE TABLEAU_READER;
GRANT SELECT ON TABLE ANALYTICS_DB.MARTS.DIM_PRODUCTS TO ROLE TABLEAU_READER;
-- Create the service account
CREATE USER TABLEAU_SVC_USER
PASSWORD = '<strong_rotated_password>'
DEFAULT_ROLE = TABLEAU_READER
DEFAULT_WAREHOUSE = ANALYTICS_WH
MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE TABLEAU_READER TO USER TABLEAU_SVC_USER;
Security note: Use Snowflake key-pair authentication for the service account rather than password-based auth. Passwords rotate and break dashboards at 2 AM. Key pairs can be rotated without changing the connection string in Tableau Server.
If you're not using a semantic layer (or your BI tool doesn't support it), implement RLS directly in your dbt model via a security view:
-- models/marts/access/fct_monthly_revenue_secured.sql
-- This view enforces row-level security using Tableau's USERNAME() function
-- The Tableau Published Data Source connects to this view, not the base table
{{
config(
materialized='view',
tags=['governed', 'rls'],
meta={
'exposure_surface': 'tableau',
'contains_rls': true
}
)
}}
WITH user_regions AS (
-- This table maps Tableau usernames to the regions they're allowed to see
-- It's maintained by the data platform team and updated via HR feed
SELECT
tableau_username,
allowed_region
FROM {{ ref('dim_tableau_user_access') }}
),
secured_revenue AS (
SELECT
r.*
FROM {{ ref('fct_monthly_revenue') }} r
INNER JOIN user_regions ur
ON r.region = ur.allowed_region
-- Snowflake's CURRENT_USER() returns the session user
-- When Tableau uses a service account, CURRENT_USER() returns the service account
-- So we embed the Tableau username via an INITIAL_PARAMETER or use VPD
AND ur.tableau_username = CURRENT_USER()
)
SELECT * FROM secured_revenue
This approach has a significant limitation: CURRENT_USER() returns the service account, not the individual Tableau user. For true per-user RLS with a service account, you need one of:
Snowflake Row Access Policies — define a policy that checks a mapping table based on CURRENT_USER(), then map the service account to a specific user via session parameters set by Tableau's initial SQL.
Tableau's Initial SQL with Snowflake session parameters — configure the Published Data Source to set a session variable on connection: ALTER SESSION SET TABLEAU_USER = '{{tableau_user}}', then query that variable in your Snowflake view.
The Initial SQL approach in Tableau:
-- Set in the Published Data Source's "Initial SQL" field
-- This runs when each session is established
ALTER SESSION SET TABLEAU_USER = TABLEAU_USERNAME();
Wait — TABLEAU_USERNAME() is a Tableau function, not SQL. The actual pattern is:
Configure the Initial SQL in the data source connection to run:
ALTER SESSION SET TABLEAU_USER = '<tableau_username_parameter>';
Where the parameter is injected by Tableau Server using the User Filter capability. Then in your Snowflake view:
-- The view reads the session variable set by Initial SQL
WHERE ur.tableau_username = CURRENT_SESSION():TABLEAU_USER
This is architecturally messy, which is exactly why a proper semantic layer with native RLS support is preferable at scale.
Manual publishing via Tableau Desktop is not sustainable. Use the Tableau REST API or Tableau's tabcmd / newer tableau-api-lib to publish data sources as part of your dbt deployment pipeline:
import tableauserverclient as TSC
import json
from pathlib import Path
def publish_governed_datasource(
server_url: str,
token_name: str,
token_value: str,
project_name: str,
datasource_path: str,
overwrite: bool = True
) -> str:
"""
Publish a Tableau data source to a governed project.
Returns the LUID of the published data source.
"""
tableau_auth = TSC.PersonalAccessTokenAuth(token_name, token_value)
server = TSC.Server(server_url, use_server_version=True)
with server.auth.sign_in(tableau_auth):
# Find the target project
all_projects, _ = server.projects.get()
project = next(
(p for p in all_projects if p.name == project_name),
None
)
if not project:
raise ValueError(f"Project '{project_name}' not found on Tableau Server")
# Configure the new data source
new_datasource = TSC.DatasourceItem(project_id=project.id)
new_datasource.name = Path(datasource_path).stem
# Publish
publish_mode = (
TSC.Server.PublishMode.Overwrite if overwrite
else TSC.Server.PublishMode.CreateNew
)
result = server.datasources.publish(
new_datasource,
datasource_path,
publish_mode
)
print(f"Published '{result.name}' with LUID: {result.id}")
return result.id
Wire this into your CI/CD: after dbt run succeeds on main, the deployment pipeline calls this function to update the Published Data Source. Business users picking up Tableau the next morning see fresh, tested data with no manual intervention.
Looker's LookML is both a semantic layer and a BI configuration language. It's more tightly integrated than Tableau because Looker compiles LookML to SQL rather than letting users write SQL directly. Every query a business user runs goes through a LookML model, which means every calculation is defined centrally.
The architectural question you need to answer: if you're using dbt's Semantic Layer, do you also need LookML?
The honest answer: it depends on whether you're using Looker as your primary BI tool. Looker can connect to the dbt Semantic Layer via its JDBC connector, treating it as a data source. But Looker's exploration and dashboard capabilities are built around LookML. If you want Looker's full feature set (Explores, drill-downs, dashboard linking, scheduled deliveries), you define your surface area in LookML.
The recommended pattern for dbt + Looker:
Here's a LookML model that properly references dbt marts:
# views/revenue.view.lkml
view: fct_monthly_revenue {
sql_table_name: analytics_db.marts.fct_monthly_revenue ;;
# Never expose raw IDs to business users
# They only see measures and labeled dimensions
dimension: order_id {
hidden: yes
primary_key: yes
type: string
sql: ${TABLE}.order_id ;;
}
dimension_group: order {
type: time
timeframes: [date, week, month, quarter, year, fiscal_quarter, fiscal_year]
datatype: date
sql: ${TABLE}.order_date ;;
label: "Order"
}
dimension: revenue_category {
type: string
sql: ${TABLE}.revenue_category ;;
label: "Revenue Category"
description: "Product category driving this revenue. Set by Product team."
}
dimension: is_new_business {
type: yesno
sql: ${TABLE}.is_new_business ;;
label: "New Business?"
description: "True if this is the account's first purchase."
}
# The measures — single definitions, always consistent
measure: gross_revenue {
type: sum
sql: ${TABLE}.amount_usd ;;
label: "Gross Revenue"
value_format_name: usd
description: "Revenue before refunds. Use Net Revenue for board reporting."
}
measure: refunds {
type: sum
sql: ${TABLE}.refund_amount_usd ;;
label: "Refunds"
value_format_name: usd
}
measure: net_revenue {
type: number
sql: ${gross_revenue} - ${refunds} ;;
label: "Net Revenue"
value_format_name: usd
description: "Primary revenue metric. Gross revenue minus refunds."
}
measure: refund_rate {
type: number
sql: ${refunds} / NULLIF(${gross_revenue}, 0) ;;
label: "Refund Rate"
value_format_name: percent_2
description: "Refunds as % of gross revenue. Target: < 2%."
}
measure: order_count {
type: count_distinct
sql: ${order_id} ;;
label: "Order Count"
}
}
Looker has a rich permission model. At the model level, you control which LookML models users can access:
# model: finance_analytics.model.lkml
connection: "snowflake_prod"
# Only include the views that finance users should access
include: "/views/finance/*.view.lkml"
include: "/views/shared/*.view.lkml"
# Do NOT include staging or intermediate views
# include: "/views/staging/*.view.lkml" -- NEVER do this
explore: monthly_revenue {
label: "Monthly Revenue"
description: "Revenue analysis at monthly grain. Certified by Revenue Analytics team."
join: dim_accounts {
type: left_outer
sql_on: ${fct_monthly_revenue.account_id} = ${dim_accounts.account_id} ;;
relationship: many_to_one
}
join: dim_products {
type: left_outer
sql_on: ${fct_monthly_revenue.product_id} = ${dim_products.product_id} ;;
relationship: many_to_one
}
# Restrict time range to prevent runaway queries on large tables
sql_always_where: ${order_date} >= DATE_TRUNC('year', DATEADD('year', -2, CURRENT_DATE())) ;;
tags: ["finance", "certified"]
}
For row-level security in Looker, use User Attributes:
# In the view, reference a Looker user attribute
dimension: region_filter {
type: string
sql: ${TABLE}.region ;;
hidden: yes
}
# In the explore or view, add a required access filter
explore: monthly_revenue {
access_filter: {
field: fct_monthly_revenue.region_filter
user_attribute: allowed_region
}
}
Set the allowed_region user attribute in Looker's admin panel or via the Looker API when provisioning users. When a user runs any query in this Explore, Looker automatically appends WHERE region IN ('EMEA') (or whatever their allowed values are). The user cannot override this — it's compiled into the SQL by Looker's query engine.
Warning: Looker User Attributes for RLS are powerful but have edge cases with
ORconditions, null handling, and aggregate queries. Always test your RLS implementation by logging in as a test user with restricted attributes and verifying the generated SQL in Looker's SQL Runner. Never assume it works from the configuration alone.
Here's the deployment pipeline that ties all of this together. This assumes GitHub Actions, dbt Cloud, and Snowflake, but the logic translates to any CI system:
# .github/workflows/deploy_analytics_platform.yml
name: Deploy Analytics Platform
on:
push:
branches: [main]
jobs:
dbt_deploy:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- name: Validate Exposure Dependencies
run: |
pip install pyyaml
bash scripts/check_exposure_dependencies.sh
- name: Run dbt in Production
env:
DBT_CLOUD_TOKEN: ${{ secrets.DBT_CLOUD_TOKEN }}
run: |
# Trigger dbt Cloud job via API
python scripts/trigger_dbt_cloud_job.py \
--job-id ${{ vars.DBT_PROD_JOB_ID }} \
--wait-for-completion
- name: Validate Semantic Layer
run: |
pip install dbt-metricflow
dbt sl validate --project-dir . --profiles-dir .
- name: Check Exposure Freshness
run: |
python scripts/check_model_freshness.py \
--manifest target/manifest.json \
--alert-slack ${{ secrets.SLACK_WEBHOOK }}
- name: Publish Tableau Data Sources
env:
TABLEAU_TOKEN: ${{ secrets.TABLEAU_TOKEN }}
run: |
python scripts/publish_tableau_datasources.py \
--project "Governed Analytics" \
--datasources-dir tableau/published_datasources/
- name: Notify Stakeholders
run: |
python scripts/notify_exposure_owners.py \
--manifest target/manifest.json \
--slack-token ${{ secrets.SLACK_TOKEN }}
The final piece is alerting. dbt's source freshness checks are well-known, but fewer teams wire model freshness to Exposure criticality. Here's the logic:
# scripts/check_model_freshness.py
import json
import requests
from datetime import datetime, timezone
def check_freshness_for_critical_exposures(
manifest_path: str,
run_results_path: str,
slack_webhook: str
) -> None:
with open(manifest_path) as f:
manifest = json.load(f)
with open(run_results_path) as f:
run_results = json.load(f)
# Build model -> last run time map
model_run_times = {}
for result in run_results.get('results', []):
node_id = result['unique_id']
if result['status'] == 'success':
model_run_times[node_id] = result['timing'][-1]['completed_at']
# Check each P0 exposure
exposures = manifest.get('exposures', {})
alerts = []
for exp_id, exp in exposures.items():
criticality = exp.get('meta', {}).get('business_criticality')
sla_hours = None
# Walk upstream nodes and check SLA
for dep_id in exp.get('depends_on', {}).get('nodes', []):
node = manifest['nodes'].get(dep_id, {})
sla_hours = node.get('meta', {}).get('sla_freshness_hours')
if sla_hours and dep_id in model_run_times:
last_run = datetime.fromisoformat(
model_run_times[dep_id].replace('Z', '+00:00')
)
age_hours = (
datetime.now(timezone.utc) - last_run
).total_seconds() / 3600
if age_hours > sla_hours:
alerts.append({
'exposure': exp['name'],
'model': dep_id.split('.')[-1],
'age_hours': round(age_hours, 1),
'sla_hours': sla_hours,
'channels': [
s['slack_channel']
for s in exp.get('meta', {}).get('stakeholders', [])
]
})
for alert in alerts:
message = (
f":warning: *Data Freshness Alert*\n"
f"Exposure `{alert['exposure']}` depends on model "
f"`{alert['model']}` which is {alert['age_hours']} hours old "
f"(SLA: {alert['sla_hours']} hours)."
)
requests.post(slack_webhook, json={"text": message})
Build the following in a sandbox environment (dbt Cloud free tier + Snowflake trial work well):
Scenario: You work at a SaaS company. Finance wants a self-serve dashboard for ARR analysis. Sales wants pipeline metrics. Neither team should see the other's data.
Step 1 — Build two marts models:
Create fct_arr_movements (with columns: account_id, movement_date, arr_movement_usd, movement_type) and fct_pipeline (with columns: opportunity_id, account_id, owner_id, stage, expected_close_date, expected_arr).
Step 2 — Define semantic models:
Write the MetricFlow semantic model YAML for both. Define at minimum: net_arr_added, churn_arr, net_new_arr (derived: net_arr_added - churn_arr) for the ARR model, and pipeline_value, weighted_pipeline for the pipeline model.
Step 3 — Create Exposures:
Define two Exposures: finance_arr_dashboard depending on fct_arr_movements, and sales_pipeline_dashboard depending on fct_pipeline. Set business_criticality: P0 on the finance one, add stakeholder metadata.
Step 4 — Validate:
Run dbt sl validate. Run dbt sl query --metrics net_new_arr --group-by metric_time__month. Confirm the generated SQL looks correct — specifically that the derived metric handles NULL correctly (it should use COALESCE or your metric definitions should handle it).
Step 5 — Simulate governance:
Write and run the check_exposure_dependencies.sh script. Then rename fct_arr_movements to fct_arr_movements_v2 and confirm the script catches the broken reference before running dbt run.
Step 6 — Connect to a BI tool:
If you have Tableau Desktop, create a Published Data Source connected to analytics_db.marts using a restricted user. Add three fields: movement_date, movement_type, and net_arr_added. Hide all other fields. Save the data source and note how the field surface area compares to a direct connection to the full schema.
Mistake 1: Exposures that reference non-existent model aliases
If you rename a model using the alias config, ref('original_name') still works in dbt, but the Exposure depends_on: ref('original_name') also still works — it's the DAG name, not the SQL alias. The error shows up when you switch to the warehouse name. Always use the dbt model name in Exposures.
Mistake 2: MetricFlow fanout on many-to-many joins
If you join two semantic models via a shared entity where the relationship isn't many-to-one, MetricFlow can generate a Cartesian product at certain granularities. The symptom is metrics that appear inflated by a consistent factor. Diagnose by running dbt sl query and examining the generated SQL. Resolve by adding explicit join_type: many_to_one annotations and restructuring your semantic models to respect the grain.
Mistake 3: Looker RLS bypassed by SQL Runner access
If you grant users SQL Runner access in Looker, they can bypass all LookML access controls. SQL Runner is direct SQL access to the connection. Reserve it for data team members only. Business users should never have SQL Runner permissions.
Mistake 4: Service account with excessive privileges
Teams often use a single Snowflake service account for Tableau with GRANT ALL ON SCHEMA MARTS. Then a business user creates a custom SQL data source directly in Tableau Desktop, connects using embedded credentials, and queries INFORMATION_SCHEMA to discover every table. Lock down the service account to specific table-level grants only.
Mistake 5: dbt Semantic Layer queries timing out in production
MetricFlow generates complex SQL that works fine in development against small tables but times out against 500M-row fact tables. Solutions in order of preference: (1) add clustering/partitioning on your time dimension columns in the warehouse, (2) define pre-aggregations in your semantic layer (Cube.dev excels here), (3) add sql_always_where constraints in Looker Explores to limit default query scope.
Mistake 6: Publishing Tableau data sources without testing embedded credentials
The Tableau publish API doesn't validate that the embedded credentials actually work at publish time. It validates at query time. Build a post-publish validation step that runs a simple query against the published data source via the Tableau REST API's query endpoint.
You now have the full architecture for a governed, self-serve analytics platform. Let's recap the key decisions and why they matter:
dbt Exposures aren't documentation artifacts — they're governance contracts. When you treat them as code (CI validation, programmatic parsing, stakeholder metadata) they become the connective tissue between your transformation layer and your BI tools. Impact analysis, change notifications, and SLA alerting all flow from a well-maintained Exposure graph.
The Semantic Layer is the most architecturally significant component. Whether you choose dbt's native MetricFlow implementation or Cube.dev depends on whether you need multi-source federation and aggressive pre-aggregation caching. Either way, the principle is the same: metric definitions live in one place, and every BI tool reads from that place. The alternative — metric definitions in each workbook — is a governance debt that compounds with every new dashboard.
Tableau and Looker governance operates at three levels: connection credentials (service accounts with minimal privilege), schema-level access (marts only, specific tables), and row-level security (via Snowflake Row Access Policies or Looker User Attributes). Don't conflate these — you need all three, and each one defends against a different failure mode.
Next steps to build on this foundation:
v1, v2) for models with active Exposures. This gives you a migration path when you need to restructure a model without breaking live dashboards.The goal of all of this is a platform where business users feel trusted and empowered, data engineers feel confident that changes won't cause ungoverned chaos, and the numbers in every dashboard mean exactly what they say they mean. That's not a small thing. It's the difference between a data team that's consulted and one that's ignored.
Learning Path: Modern Data Stack