> For the complete documentation index, see [llms.txt](https://gchandra.gitbook.io/data-warehousing/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gchandra.gitbook.io/data-warehousing/fundamentals/thoughts-on-some-data.md).

# Thoughts on some data

Don't remove NULL columns or Bad data from the Source. Let's learn and handle that in Spark.

**Sample 1**

| Serial Number | List Year |                             |
| ------------- | --------- | --------------------------- |
| 20093         | 2020      | 10 - A Will                 |
| 200192        | 2020      | 14 - Foreclosure            |
| 190871        | 2019      | 18 - In Lieu Of Foreclosure |
|               |           |                             |

**dim\_nonusecode**

id

code

code\_name

**Sample 2**

<table><thead><tr><th>TripID</th><th width="160">Source Lat</th><th>Souce Long</th><th>Des Lat</th><th>Des Long</th></tr></thead><tbody><tr><td>1</td><td>-73.9903717</td><td>40.73469543</td><td>-73.98184204</td><td>40.73240662</td></tr><tr><td>2</td><td>-73.98078156</td><td>40.7299118</td><td>-73.94447327</td><td>40.71667862</td></tr><tr><td>3</td><td>-73.98455048</td><td>40.67956543</td><td>-73.95027161</td><td>40.78892517</td></tr></tbody></table>

| dim\_location              |
| -------------------------- |
| location\_id               |
| lat                        |
| long                       |
| location\_name (If exists) |

| fact                      |
| ------------------------- |
| trip\_id                  |
| source\_location\_id      |
| destination\_location\_id |

**Another Variation**

| Location                   |
| -------------------------- |
|                            |
| POINT (-72.98492 41.64753) |
| POINT (-72.96445 41.25722) |

**Notes:**

**DRY Principle**: You're not repeating the lat-long info, adhering to the "Don't Repeat Yourself" principle.&#x20;

**Ease of Update**: If you need to update a location's details, you do it in one place.&#x20;

**Flexibility**: Easier to add more attributes to locations in the future.

**5 decimal places:** Accurate to \~1.1 meters, usually good enough for most applications including vehicle navigation.&#x20;

**4 decimal places**: Accurate to \~11 meters, may be suitable for some applications but not ideal for vehicle-level precision.&#x20;

**3 decimal places**: Accurate to \~111 meters, generally too coarse for vehicle navigation but might be okay for city-level analytics.

**Sample 3**

<table><thead><tr><th>Fiscal Year</th><th width="178">disbursement Date</th><th>Vendor Invoice Date</th><th>Vendor Invoice Week</th><th>Check Clearance Date</th></tr></thead><tbody><tr><td>2023</td><td>06-Oct-23</td><td>08-Aug-23</td><td>08-06-2023</td><td></td></tr><tr><td>2023</td><td>06-Oct-23</td><td>16-Aug-23</td><td>08/13/2023</td><td></td></tr><tr><td>2023</td><td>06-Oct-23</td><td>22-Sep-23</td><td>09/17/2023</td><td>10-08-2023</td></tr></tbody></table>

Create a single Date Dimension and map all these dates to that table.&#x20;

See the reference diagram given in canvas

**Sample 4**

**Another DateTime**&#x20;

| date\_attending     | ip\_location                     |
| ------------------- | -------------------------------- |
| 2017-12-23 12:00:00 | Reseda, CA, United States        |
| 2017-12-23 12:00:00 | Los Angeles, CA, United States   |
| 2018-01-05 14:00:00 | Mission Viejo, CA, United States |

you can create a date time dimension like this

<table><thead><tr><th width="107">DateTimeID</th><th>FullDateTime</th><th width="80">Year</th><th width="78">Month</th><th width="59">Day</th><th width="60">Hour</th><th width="78">Minute</th><th width="84">Weekday</th><th>IsWeekend</th><th>IsHoliday</th></tr></thead><tbody><tr><td>1</td><td>2017-12-23 12:00:00</td><td>2017</td><td>12</td><td>23</td><td>12</td><td>0</td><td>6</td><td>False</td><td>False</td></tr><tr><td>2</td><td>2018-01-05 14:00:00</td><td>2018</td><td>1</td><td>5</td><td>14</td><td>0</td><td>5</td><td>False</td><td>False</td></tr></tbody></table>

**Sample 5**

<table><thead><tr><th>Job Title</th><th>Experience</th><th width="130">Qualifications</th><th>Salary Range</th><th>Age_Group</th></tr></thead><tbody><tr><td>Digital Marketing Specialist</td><td>5 to 15 Years</td><td>M.Tech</td><td>$59K-$99K</td><td>Youth (&#x3C;25)</td></tr><tr><td>Web Developer</td><td>2 to 12 Years</td><td>BCA</td><td>$56K-$116K</td><td>Adults (35-64)</td></tr><tr><td>Operations Manager</td><td>0 to 12 Years</td><td>PhD</td><td>$61K-$104K</td><td>Young Adults (25-34)</td></tr><tr><td>Network Engineer</td><td>4 to 11 Years</td><td>PhD</td><td>$65K-$91K</td><td>Young Adults (25-34)</td></tr><tr><td>Event Manager</td><td>1 to 12 Years</td><td>MBA</td><td>$64K-$87K</td><td>Adults (35-64)</td></tr></tbody></table>

Dimension will look like this

#### Experience Dimension Table

| ExperienceID | ExperienceRange | MinExperience | MaxExperience |
| ------------ | --------------- | ------------- | ------------- |
| 1            | 5 to 15 Years   | 5             | 15            |
| 2            | 2 to 12 Years   | 2             | 12            |
| 3            | 0 to 12 Years   | 0             | 12            |
| 4            | 4 to 11 Years   | 4             | 11            |
| 5            | 1 to 12 Years   | 1             | 12            |

#### Salary Range Dimension Table

<table><thead><tr><th>SalaryID</th><th width="172">SalaryRange</th><th>MinSalary</th><th>MaxSalary</th></tr></thead><tbody><tr><td>1</td><td>$59K-$99K</td><td>$59K</td><td>$99K</td></tr><tr><td>2</td><td>$56K-$116K</td><td>$56K</td><td>$116K</td></tr><tr><td>3</td><td>$61K-$104K</td><td>$61K</td><td>$104K</td></tr><tr><td>4</td><td>$65K-$91K</td><td>$65K</td><td>$91K</td></tr><tr><td>5</td><td>$64K-$87K</td><td>$64K</td><td>$87K</td></tr></tbody></table>

#### Age Group Dimension Table

<table><thead><tr><th>AgeGroupID</th><th>AgeGroupLabel</th><th width="190">AgeGroupRange</th><th>MinAge</th><th>MaxAge</th></tr></thead><tbody><tr><td>1</td><td>Youth (&#x3C;25)</td><td>&#x3C;25</td><td>NULL</td><td>24</td></tr><tr><td>2</td><td>Adults (35-64)</td><td>35-64</td><td>35</td><td>64</td></tr><tr><td>3</td><td>Young Adults (25-34)</td><td>25-34</td><td>25</td><td>34</td></tr></tbody></table>

**Sample 6**

| ITEM CODE | ITEM DESCRIPTION                     |
| --------- | ------------------------------------ |
| 100293    | SANTORINI GAVALA WHITE - 750ML       |
| 100641    | CORTENOVA VENETO P/GRIG - 750ML      |
| 100749    | SANTA MARGHERITA P/GRIG ALTO - 375ML |

The Dimension will turn out to be like this

| ItemID | ItemCode | ItemDescription              | Quantity |
| ------ | -------- | ---------------------------- | -------- |
| 1      | 100293   | SANTORINI GAVALA WHITE       | 750ML    |
| 2      | 100641   | CORTENOVA VENETO P/GRIG      | 750ML    |
| 3      | 100749   | SANTA MARGHERITA P/GRIG ALTO | 375ML    |

**Sample 7**

| production\_countries                    | spoken\_languages                  |
| ---------------------------------------- | ---------------------------------- |
| United Kingdom, United States of America | English, French, Japanese, Swahili |
| United Kingdom, United States of America | English                            |
| United Kingdom, United States of America | English, Mandarin                  |
|                                          |                                    |

\
Let's create the Dimension table first

| CountryID | CountryName              |
| --------- | ------------------------ |
| 1         | United Kingdom           |
| 2         | United States of America |

| LanguageID | LanguageName |
| ---------- | ------------ |
| 1          | English      |
| 2          | French       |
| 3          | Japanese     |
| 4          | Swahili      |
| 5          | Mandarin     |

There are two approaches

\
**Creating Many to Many**

| FactID | CountryID | LanguageID |
| ------ | --------- | ---------- |
| 1      | 1         | 1          |
| 1      | 2         | 1          |
| 1      | 1         | 2          |
| 1      | 2         | 2          |

Pros

**Normalization**: Easier to update and maintain data.

Cons

**Storage**: May require more storage for the additional tables and keys.

#### Fact Table with Array Datatypes

| FactID | ProductionCountryIDs | SpokenLanguageIDs |
| ------ | -------------------- | ----------------- |
| 1      | \[1, 2]              | \[1, 2, 3, 4]     |
| 2      | \[1, 2]              | \[1]              |
| 3      | \[1, 2]              | \[1, 5]           |

With newer Systems like Spark SQL which supports Arrays

* **Simplicity**: Easier to understand and less complex to set up.
* **Performance**: Could be faster for certain types of queries, especially those that don't require unpacking the array.