Modeling different events for an Order

Data Vault 2.0
1

Hi All,

Sorry if this is something very basic but I’m new to DV 2.0 and would like to understand modelling approach for my use case.

We need to ingest order event data into DV layer. The order has multiple events and each event type has a different structure. The data is published by kafka and each are JSON messaged.

For an example consider below events

  1. Order Created - Order ID, Customer ID, Timestamp, Items, Total Amount, Shipping Address, etc.
  2. Order Payment Received - Order ID, Payment Method, Amount, Timestamp, etc
  3. Order Fulfilment Stated - Order ID, Location ID, Handling user, Timestamp, etc.
  4. Order Fulfilment Finished - Order ID, Location ID, Handling user, Timestamp, etc.

Between 3 & 4 there could be another 70-80 odd events.

Question:

  • Would you create a single LINK table storing all these events associated with an order

  • Would you create a LINK table for each event - possibly 80+ storing relevant event data

3

Is it just one Kafka topic?

5

Yes,

All the events are published within a single Kafka topic with different schema structure for each event. We plan to land the data in Stage layer in a single tables with variant ( we are using snowflake)

6

In this case I would prefer one non-historized link with only few basic common attributes parsed (order ID, timestamp, … ?) and whole message in JSON.
Then, in the business vault, you can have couple business satellites, business hubs and business links based on the JSONs. Also, the Snowflake has methods for hybrid solutions which are suitable for this use case. But I have no more info, I’m not a SnowPro. Try to ask someone skilled.

1 Like
7

Thank you ! I am unable to think of other solution than storing the payload as JSON, else will have to create flat LINK object covering all attributes for all events.

8

Kafka topics may evolve, building a static structure to serve structured columns would be painful

See: https://www.snowflake.com/blog/handling-semi-structured-data/