This guide explains how to build the dbt-nexus catalogue pipeline for ad-platform product catalogues (Meta Catalog, TikTok Catalog, Reddit Catalog, and others).

A catalogue is the door through which product and grouping metadata reaches an ad platform. It is the counterpart to the Marketing Conversions pipeline: conversions carry the event (someone bought item X); the catalogue describes what item X is and which group it rolls up to. Dynamic product ads (DPA / Dynamic Showcase Ads), product-set targeting, and catalogue-based audiences all read the catalogue, not the event payload.

The pattern is the same two-layer, composable shape used by marketing conversions:

• Define the catalogue once in a canonical model (product_catalogue_canonical)
• Project thin, platform-specific feed tables (product_catalogue_meta, …)
• Deliver with the transport each platform supports (API push or hosted feed)

Why a warehouse-derived catalogue

Ad platforms treat the catalogue as a separate optimisation surface from the pixel/CAPI event. Meta, for example, is three machines: matching (identity), optimisation (conversion signal + catalogue attributes), and audiences. There is no door for stuffing high-cardinality product metadata onto the event and hoping the optimiser reads it — that metadata belongs in the catalogue, and targeting rides on product sets (a saved filter over catalogue fields), never on a raw pixel parameter. TikTok and Reddit follow the same model.

This makes the catalogue a data-modelling problem, exactly like conversions:

• Single source of truth — item identity, grouping, price, and availability come from the same warehouse models operations and finance already trust.
• Coverage contract — the catalogue must be a superset of every item id that can appear on an event (see below). Deriving it from the warehouse is the only way to guarantee that.
• Version control — every change to the feed shape or grouping logic is a git commit with review and tests, not a click in an ads-manager UI.
• Destination-agnostic — add a platform with a thin Layer 2 projection; grouping and metadata rules stay in Layer 1 once.

The item-id ↔ conversion join

The single most important contract: the catalogue's item id must exactly match the product id sent on the corresponding marketing event. Platforms call this field different names — Meta content_ids, TikTok id ("must match your pixel events"), Reddit Product ID — but it is the same value, and it is the join key that makes DPA and product-set logic work.

In practice this means the catalogue and the Marketing Conversions pipeline agree on the same id. The conversion event emits it as the product/content id; the catalogue lists it as a row. If an event fires an id the catalogue does not yet know, that item goes silently untargetable — no error, just no matching.

Architecture

flowchart LR
  src[Source Systems] --> nexusEnt[nexus_entities / nexus_relationships]
  nexusEnt --> layer1[product_catalogue_canonical]
  layer1 --> meta[product_catalogue_meta]
  layer1 --> tiktok[product_catalogue_tiktok]
  layer1 --> reddit[product_catalogue_reddit]
  meta --> push[API push / reverse ETL]
  tiktok --> push
  reddit --> feed[Hosted feed file]

The pipeline has two layers, mirroring the conversions pipeline:

Layer	Model	Materialization	Purpose
1	product_catalogue_canonical	table	One row per catalogue item: identity, grouping, descriptive fields, custom labels
2	product_catalogue_meta, product_catalogue_tiktok, product_catalogue_reddit	view	Thin platform projections with feed-native column names and per-platform rules

All grouping and metadata logic belongs in Layer 1. Layer 2 models are filtered views that rename columns to feed-native names and apply platform-specific requirements (required-field minimums, item-type vocab). They do not re-implement business logic.

Directory structure

models/
  outputs/
    product-catalogues/
      product_catalogue_canonical.sql   -- Layer 1
      product_catalogue_meta.sql        -- Layer 2: Meta Catalog
      product_catalogue_tiktok.sql      -- Layer 2: TikTok Catalog
      product_catalogue_reddit.sql      -- Layer 2: Reddit Catalog

The shared product_catalogue_ prefix groups the models together in the warehouse table listing, the same way marketing_conversions_ does.

---

Layer 1: product_catalogue_canonical

This is the canonical catalogue model. One row per catalogue item. It owns:

• Item identity (item_id) — the value that must match the conversion product/content id.
• Grouping (group_id) — the parent the item rolls up to for targeting (e.g. a title, a campaign, a collection). Emitted to every platform as custom_label_0.
• Descriptive fields — title, description, price, currency, availability, condition, image, link, brand.
• Item type — the vertical/kind of the row (used to drive eligibility and the descriptive defaults).
• Custom labels — custom_label_0 = group_id, plus optional custom_label_1..4 for genre, season, margin tier, etc.

The grouping item is itself a catalogue row

The most important modelling decision: the group is also a catalogue item.

Many engageable things have no sellable SKU — a trailer view, a landing-page view, a title with no per-ticket SKU. For those, the event's product id is the group id itself, so the group must exist as a catalogue row (item_id = group_id) or those events go unmatched.

This gives a single uniform id rule, identical to the conversion side:

item_id = asset_id  (when the item has its own SKU)
        ?? group_id  (otherwise — the group row stands in)

A per-group product set then unites the group row and all of its child item rows. (A24 example: the movie is a catalogue item with item_id = project_id; merch, tickets, VOD, and trailers are child rows with custom_label_0 = project_id. The "Robin Hood" product set is everything where custom_label_0 = mov_123.)

Coverage and freshness contract

The catalogue must be a superset of every item_id that can appear on an event, refreshed ahead of go-live.

This is a hard contract with no fallback. If an event fires an item_id the catalogue does not know yet — a new SKU, a new title on sale before the feed runs — it is silently untargetable. New-item onboarding therefore means "register in the catalogue before it is purchasable / engageable." Build Layer 1 from the union of every sellable/engageable asset plus every group, not from historical orders.

Source data

Unlike conversions (which read nexus_events), the catalogue is built from entities and their relationships — the products/assets and their parent groups — not the event log:

Model	Purpose
nexus_entities	Product / asset / title rows and their descriptive traits
nexus_relationships	Asset → group membership (never join on trait columns)
source product tables	Native catalogue rows (e.g. Shopify products) and SKU details

Never join on trait columns of nexus_entities (e.g. a *_product_id trait) to resolve grouping. Identity resolution collapses traits via max(), so a trait can point at the wrong source row. Use nexus_relationships to connect an item to its group. See the schema reference for why.

Model shape

{{ config(
    materialized='table',
    tags=['catalogue', 'outputs']
) }}

WITH items AS (
    -- 1. Every sellable / engageable asset, one row each.
    SELECT
        a.asset_id                       as item_id,
        a.group_id,
        a.item_type,                     -- 'merch' | 'ticket' | 'vod' | 'trailer' | ...
        a.title,
        a.description,
        a.price,
        a.currency,
        a.availability,                  -- 'in stock' | 'out of stock' | 'preorder'
        a.condition,                     -- 'new' | 'used' | 'refurbished'
        a.image_link,
        a.link,
        a.brand
    FROM {{ ref('your_assets') }} a
),

groups AS (
    -- 2. The group itself as a catalogue item, so SKU-less events resolve.
    SELECT
        g.group_id                       as item_id,
        g.group_id,
        'group'                          as item_type,
        g.group_title                    as title,
        g.group_description              as description,
        0                                as price,        -- not directly sold
        'USD'                            as currency,
        'in stock'                       as availability,
        'new'                            as condition,
        g.group_image_link               as image_link,
        g.group_link                     as link,
        g.brand
    FROM {{ ref('your_groups') }} g
),

unioned AS (
    SELECT * FROM items
    UNION ALL
    SELECT * FROM groups
),

catalogue_mapped AS (
    -- 3. Custom labels + descriptive defaults. One row per item_id.
    SELECT
        item_id,
        group_id,
        item_type,
        title,
        description,
        price,
        currency,
        availability,
        condition,
        image_link,
        link,
        brand,

        -- Custom labels (identical names across platforms)
        group_id                         as custom_label_0,   -- the rollup
        item_type                        as custom_label_1,   -- e.g. vertical
        CAST(NULL AS VARCHAR)            as custom_label_2,
        CAST(NULL AS VARCHAR)            as custom_label_3,
        CAST(NULL AS VARCHAR)            as custom_label_4,

        ROW_NUMBER() OVER (
            PARTITION BY item_id ORDER BY price DESC
        ) as rn
    FROM unioned
)

SELECT * FROM catalogue_mapped WHERE rn = 1

Design decisions

• item_id is the cross-platform / cross-pipeline key. It is what every platform's feed id and every conversion event's product id resolve to. Keep it stable; never regenerate it.
• custom_label_0 = group_id is the targeting hook. Product sets are saved filters over custom labels. Putting the group on custom_label_0 everywhere means a per-group product set is one filter on one field — portable across Meta, TikTok, and Reddit.
• Descriptive fields are real columns, unhashed and queryable. No PII is involved (unlike conversions), so nothing is hashed.
• One row per item_id. Dedupe in Layer 1 (the ROW_NUMBER guard) so every Layer 2 feed inherits a clean superset.

Materialization

table for Layer 1 (read by every Layer 2 feed). view for Layer 2 (thin projections that should always reflect the latest Layer 1).

---

Layer 2: Platform projections

Each platform gets a thin model that reads product_catalogue_canonical and renames to that platform's feed field names. The three platforms are close cousins of the Meta feed spec, so the projections are mostly renames plus a few platform rules. See the per-platform guides:

• Meta Catalog — content_ids join, custom_label_0 product sets, Shopify-native merch dedup, Catalog Batch API
• TikTok Catalog — required-field minimums, custom_label_0..4, Catalog Batch API or scheduled feed URL, e-commerce-vertical-only wrinkle
• Reddit Catalog — required fields, product groups, hosted feed file transport (no robust catalog API), DPA newer (GA May 2025)

Transport differs by platform

This is the main mechanical difference from the conversions pipeline, where every destination is an API push:

Platform	Transport	Layer 2 output is…
Meta	Catalog Batch API (or hosted feed)	a table reverse ETL pushes to the API
TikTok	Catalog Batch API or scheduled feed URL (hourly–daily)	a table reverse ETL pushes, or a published feed
Reddit	Hosted feed file only (CSV / Sheets / RSS 2.0 XML / TSV) over HTTPS / SFTP	a feed file published to a URL Reddit fetches

So Meta and TikTok can be driven by the same reverse-ETL-to-API pattern as conversions. Reddit instead requires publishing the Layer 2 view as a hosted feed file at an HTTPS/SFTP URL on a schedule. Plan for one feed-publishing job in addition to the API push adapters.

Adding a platform

1. Add any new custom-label or item-type mapping to Layer 1.
2. Create a Layer 2 model selecting from product_catalogue_canonical with feed-native names and the platform's required-field rules.
3. Wire transport: reverse ETL → catalog API, or a feed-publishing job.
4. Add schema tests (item_id not_null + unique; required fields not_null).

---

Data quality

models:
  - name: product_catalogue_canonical
    columns:
      - name: item_id
        description: >
          Catalogue item id. Must match the product/content id sent on the
          corresponding marketing conversion event.
        tests:
          - not_null
          - unique
      - name: group_id
        tests:
          - not_null
      - name: custom_label_0
        description: Rollup group; the product-set targeting key on every platform.
        tests:
          - not_null

Beyond schema tests, add a coverage check: every product/content id that appears in marketing_conversions_canonical should exist as an item_id in product_catalogue_canonical. A failing row is an item that is silently untargetable.

---

Reference

• Marketing Conversions — the event-side counterpart; shares the item_id / product-id contract
• Meta Product Catalog
• TikTok Catalog
• Reddit Catalog Requirements