Connect

 View Only
Back to discussions
Expand all | Collapse all

Using Join Tables in Connect CDC for real time enrichment and filtering

  • 1.  Using Join Tables in Connect CDC for real time enrichment and filtering

    Employee
    Posted 20 days ago
    After spending more time with Connect CDC, I ran into a feature called Join Tables that quietly removes a common need for an extra ETL step. It lets you enrich rows during replication itself, instead of pushing raw data and fixing it later.
    How it works
    In a basic model, CDC sends the source row as-is. Join Tables change that. When a source row changes, CDC can look up a related table, pull additional columns, and include those values in the same outbound row.
    You end up sending a joined, enriched record to the target without building a separate transformation layer.
     
    Simple example
    I tested this with an Employee table joined to an Address table. When an employee updates, CDC pulls the address fields and uses them in expressions:
    • ADDRESS_1 + ADDRESS_2 into a single ADDRESS column
    • CITY, STATE, PROVINCE combined into LOCATION
    • ZIP_CODE mapped directly
    The target receives a fully denormalized employee record in one step.
    Inner vs outer join behavior
    This part stood out.
    • Outer join: always replicates the source row, even if no match exists
    • Inner join: only replicates when a match is found
    This effectively becomes a gate condition. If there is no matching row in the join table, replication simply does not happen. It is a clean way to filter rows without writing separate conditions.
    Things that matter in practice
    • You can define multiple join tables for a single source
    • Joins are one-directional from trigger to join table
    • Changes in join tables do not trigger replication
    • All tables must be on the same server
    • If a join returns more than one row, only the first is used and a warning is logged
    Where I found it useful
    • Sending denormalized, analytics-ready data
    • Avoiding extra ETL pipelines
    • Filtering rows using inner join logic
    • Enriching data for downstream APIs or reporting layers
    Why it felt different
    Most of us treat CDC as copy A to B. This turns it into something closer to copy A, enrich with C, send to B. That shift makes CDC much more flexible than it initially appears, especially for lightweight transformation use cases.
     
    Have you used Join Tables for enrichment or as a filter in your models, or are you still handling this outside CDC?


    ------------------------------
    Adhitya Maya
    *Precisely Software Inc.
    ------------------------------