Config-driven Union Model

Role

Analytics Engineer

Stack

Original: GCS, BigQuery, dbt, Omni
Demo: dbt, DuckDB

Context

Funnel metrics for analysts, rebuilt on Sentinel-2 catalog data

Outcome

14 metrics from 11 source models, combined into one Monster model.

View the dbt documentation for model lineage and detailed technical information.

Much like Frankenstein’s monster, the demonstrated model brings together many objects into one “Monster Model”. At its inception, the analysts I worked with needed an easy way to make visualizations across key milestones of the business user’s workflow. More concretely, the analysts needed a way to count the different types of transactions that a user could do, and drill into each of those transactions within one funnel visual. For the data model, that meant I needed a model that contained transactions of different object types in a way that was scalable.

Problem

Monster was built with the following concerns in mind:

• I needed an easy way to add (or remove) new workflow metrics, as they were often in flux
• I needed a way to govern the added metrics and ensure the addition did not break the model contract
• I needed a method to filter each metric in its own way, without impacting any of the other metrics’ subsets
• The reported date columns needed to be consistent types across the metrics
• A funnel in the BI tool needed a full spine: every event from every stage, unioned into one table. Without it, each new stage meant another join in the BI tool onto a base that didn’t share its grain.
• Needed to be queryable by the conversational analytics platform and user friendly for the business.

The Approach

The metrics were things like “how many [objects] were created by User this week?”

And for sequential metrics, the analysts needed to demonstrate that:

User created:

• 50 Tofu
• 25 Mofu
• 10 Bofu

Each individual metric shared a few properties, as each one had:

• An internal object ID
• A business facing “pretty” ID
• A user associated to the transaction
• Each represented one logical object that could not be split into parts.
  • By business definition, you could not have “half a Bofu”
  • This parameter makes the Monster Model possible because it enabled the grain to be: [one object] x [one transaction date] x [for one user]

Part of the challenge was that each object needed its own set of filters, posing a challenge for any templater. The solution was to create a macro that uses the values of a dbt seed when templating the Monster Model. Each row of the seed represents a metric (dbt model) that turns into a section of code in the Monster Model.

Each seed row contains:

• the dbt model name stg__orders
• the metric alias name (the funnel stage) orders
• the filters for the metric not like xyz
• the date filter that should be used by the metric

Seed Example

Object Name	Alias	Filters	Event Date
stg__orders	orders	where some_column not like ‘xyz’	this_date
int__tofu	tofu		that_date
stg__mofu	mofu	where a_diff_column not like ‘abc’	another_date

Between the macro and the seed, the following pattern was written once, then templated into the Monster Model for each metric. In this case, the metrics are Orders, Tofu, and Mofu.

Compiled SQL Example

with (...), 

orders as (
    select * from schema.stg__orders
), 

count_orders as (
    select 
        orders_internal_id, 
        orders_pretty_id,
        transformation_1, 
        transformation_2, 
        event_date,
        1 as num_events
    from orders
    where 
        some_column not like 'xyz'
), 

tofu as (
    select * from schema.int__tofu
), 

count_tofu as (
    ...
),

mofu as (
    ...
), 

count_mofu as (
    ...
), 

...

select * from count_orders
union all
select * from count_tofu
union all
select * from count_mofu

The Solution

Monster’s grain is:

• one object (which represents one milestone or metric)
• one event date
• one user (or other defining category of the transaction)

I enforced the model contract with data tests rather than dbt’s native contracts, which kept the barrier low for a team still adopting dbt.

Results

The team was successfully able to use the output of the Monster model in their analyses, and the business users were able to easily conversationally query for their own funnel metrics with a minimal data platform learning curve.

Showcase

Obviously I will not – and cannot – showcase the original Monster, so I had fun with creating a new one sourced by more interesting data.

I chose to play with metadata from satellite images by combining 12 different sources into one transactional fact table.

Data

Sentinel-2 L2A scene metadata from the Copernicus Data Space STAC API, 2022-01-01 → 2026-09-30. 12 locations, each loaded as its own source table (locations.<location>) to stand in for 12 separately ingested sources. 8,431 rows. Fetched by fetch_scenes.py, loaded by load_raw.py.

Lineage

flowchart LR
  src["locations.<location> ×12"] --> stg["stg__<location> ×12"]
  stg --> fct[fct_location_daily_metrics]
  seed[(locations seed)] --> fct
  regions[(regions seed)] --> exp([fetch + load scripts])

Staging

stg__<location> ×12: unpack the statistics JSON, deduplicate to one row per capture. 8,431 → 8,178 rows.

Monster model

fct_location_daily_metrics, driven by the locations seed. Grain: one image × one event date × one location.

Tests

119 data tests: ids, accepted values, one row per capture, 0–100 ranges.

Analysis

Usable = cloud_cover_pct < 10 and nodata_pct < 5.

select
    location_name,
    sum(num_images) as images,
    sum(num_images) filter (where cloud_cover_pct < 10 and nodata_pct < 5) as usable_images
from fct_location_daily_metrics
group by 1

location	images	usable	% usable
oklahoma_city	374	151	40.4
mexico_city	376	138	36.7
phoenix	755	211	27.9
monterrey	738	134	18.2
seattle	752	129	17.2
los_angeles	745	118	15.8
yellowstone	709	94	13.3
boston	747	76	10.2
chicago	784	55	7.0
new_river_gorge	737	50	6.8
miami	740	37	5.0
tuxtla_gutierrez	721	0	0.0

Tuxtla Gutierrez: nodata always at least 33%; the tile sits on the edge of the satellite’s swath.