CODEWITHSUNDEEP
data-modelingdata-warehousedimensional-modeling
arcee123
arcee123

Reputation: 243

how to handle address dimension with 75M locations

I am creating an address dimension for a Snowflake Schema Data Warehouse. I have 75M locations on a source that I want to convert to said schema. I know how to handle Zip->City->County->State dimensions, but if I add street addresses to the location dimension I would have an equal number dimension rows as fact rows.

What I need to know, is where should the street addresses go (123 anywhere St.)? Should it go in the fact table? How do I handle street addresses?

Thanks.

Upvotes: 0

Views: 802

Answers (1)

mal-wan
mal-wan

Reputation: 4476

The street address itself should go in a Fact. If it's a Real Estate app I'd imagine there'd be some kind of "Sale Contract Fact" or "Rental Contract Fact" or something similar - the street address would be an attribute of that fact.

In your instance the instance of the address is definitely tied to a single transaction. As you said, the same street address could appear multiple times, but it would be on different Sales Contracts and thus different Fact instances.

Other elements of the address (zipcode, city, state etc) would be dimensionalised as it makes sense to group them for classification.

Upvotes: 1

Related Questions