Denormalized as much as possible

Question

Denormalized as much as possible

yrstruly

Hall of Fame

Points: 3475
More actions
March 16, 2022 at 2:21 pm

#4000596

Please assist? I would like to model this data(https://docs.google.com/spreadsheets/d/1-9x4ean4QleYODvu8GsAsgm8OKVjABeM/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true) for Bigquery as much as possible in a denormalized data model. Bigquery favours denormalized data models. I am busing creating the data model of the data tab/table in the sheet.
The Data tab/table contains the data as it comes from CM360(data source). Journey and Mapping Tab/table is extracted from the Data Tab/table. See pdf file attached of a quick overview of current data model to be denomalized.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

yrstruly wrote:

Please assist? I would like to model this data(https://docs.google.com/spreadsheets/d/1-9x4ean4QleYODvu8GsAsgm8OKVjABeM/edit?usp=sharing&ouid=104129043494164133703&rtpof=true&sd=true) for Bigquery as much as possible in a denormalized data model. Bigquery favours denormalized data models. I am busing creating the data model of the data tab/table in the sheet.
The Data tab/table contains the data as it comes from CM360(data source). Journey and Mapping Tab/table is extracted from the Data Tab/table. See pdf file attached of a quick overview of current data model to be denomalized.

No pdf file attached that I can see.

Without seeing that, pick the "primary driver" table and Left Join the other tables to it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

See: https://drive.google.com/file/d/13NhT_wx0EQl2GBP1JhthqksjU0phgSFz/view?usp=sharing

Would like to denormalize this data model.

This reply was modified 2 years, 8 months ago by yrstruly.

Attachments:

You must be logged in to view attached files.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

You've got it pretty well mapped out. Like I said, pick the table that has the the primary unique information in it an left join to that to form your "de normalization". Also understand that I've never set foot into Google products like "BigQuery" and probably won't anytime in the near future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yrstruly Hall of Fame Points: 3475 More actions · Answer 4

I consider my CM Data table to be my fact table. What would my relationship cardinality be for Store detail and Zip code to Fact table?

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Like I said, a Left Outer join on the pertinent columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

yrstruly Hall of Fame Points: 3475 More actions · Answer 6

one -to-many or one-to-one?

Attachments:

You must be logged in to view attached files.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

Stop and think about what a LEFT OUTER JOIN is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 8

yrstruly wrote:

one -to-many or one-to-one?

Well that entirely depends on your data 🙂