Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Dimension City
|   City Key |   WWI City ID | City         | State Province   | Country       | Continent     | Sales Territory   | Region   | Subregion        | Location                       |   Latest Recorded Population | Valid From              | Valid To                |   Lineage Key |
|-----------:|--------------:|:-------------|:-----------------|:--------------|:--------------|:------------------|:---------|:-----------------|:-------------------------------|-----------------------------:|:------------------------|:------------------------|--------------:|
|      98656 |         11945 | Fort Douglas | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-93.2418434 35.6842479) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98662 |         12112 | Fox          | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-92.2976558 35.7914653) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98667 |         12518 | Gainesville  | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-90.5106623 36.1647882) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98668 |         12581 | Gamaliel     | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-92.2332128 36.4567317) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98674 |         12834 | Genoa        | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-93.909628 33.3837368)  |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98678 |         12957 | Gifford      | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-92.7443327 34.3756489) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98683 |         13162 | Glencoe      | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-91.7473681 36.2942329) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98684 |         13179 | Glendale     | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-91.9593041 33.9628786) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98686 |         13419 | Goodwin      | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-91.0209529 34.9381496) |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |
|      98691 |         13577 | Grand Glaise | Arkansas         | United States | North America | Southeast         | Americas | Northern America | POINT (-91.409853 35.4750816)  |                            0 | 2014-07-01 16:00:00.000 | 9999-12-31 23:59:59.996 |             1 |


Table: Dimension Customer
|   Customer Key |   WWI Customer ID | Customer                           | Bill To Customer            | Category     | Buying Group   | Primary Contact    |   Postal Code | Valid From              | Valid To                |   Lineage Key |   No of sales |
|---------------:|------------------:|:-----------------------------------|:----------------------------|:-------------|:---------------|:-------------------|--------------:|:------------------------|:------------------------|--------------:|--------------:|
|              1 |                 1 | Tailspin Toys (Head Office)        | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Waldemar Fisar     |         90410 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           406 |
|              2 |                 2 | Tailspin Toys (Sylvanite, MT)      | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Lorena Cindric     |         90216 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           361 |
|              3 |                 3 | Tailspin Toys (Peeples Valley, AZ) | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Bhaargav Rambhatla |         90205 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           437 |
|              4 |                 4 | Tailspin Toys (Medicine Lodge, KS) | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Daniel Roman       |         90152 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           316 |
|              5 |                 5 | Tailspin Toys (Gasport, NY)        | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Johanna Huiting    |         90261 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           384 |
|              6 |                 6 | Tailspin Toys (Jessie, ND)         | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Biswajeet Thakur   |         90298 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           363 |
|              7 |                 7 | Tailspin Toys (Frankewing, TN)     | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Kalidas Nadar      |         90761 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           412 |
|              8 |                 8 | Tailspin Toys (Bow Mar, CO)        | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Kanti Kotadia      |         90484 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           315 |
|              9 |                 9 | Tailspin Toys (Netcong, NJ)        | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Sointu Aalto       |         90129 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           346 |
|             10 |                10 | Tailspin Toys (Wimbledon, ND)      | Tailspin Toys (Head Office) | Novelty Shop | Tailspin Toys  | Siddhartha Parkar  |         90061 | 2013-01-01 00:00:00.000 | 9999-12-31 23:59:59.996 |             2 |           385 |


Table: Dimension Date
| Date                    |   Day Number |   Day | Month   | Short Month   |   Calendar Month Number | Calendar Month Label   |   Calendar Year | Calendar Year Label   |   Fiscal Month Number | Fiscal Month Label   |   Fiscal Year | Fiscal Year Label   |   ISO Week Number |
|:------------------------|-------------:|------:|:--------|:--------------|------------------------:|:-----------------------|----------------:|:----------------------|----------------------:|:---------------------|--------------:|:--------------------|------------------:|
| 2016-01-01 00:00:00.000 |            1 |     1 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                53 |
| 2016-01-02 00:00:00.000 |            2 |     2 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                53 |
| 2016-01-03 00:00:00.000 |            3 |     3 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                53 |
| 2016-01-04 00:00:00.000 |            4 |     4 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-05 00:00:00.000 |            5 |     5 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-06 00:00:00.000 |            6 |     6 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-07 00:00:00.000 |            7 |     7 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-08 00:00:00.000 |            8 |     8 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-09 00:00:00.000 |            9 |     9 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |
| 2016-01-10 00:00:00.000 |           10 |    10 | January | Jan           |                       1 | CY2016-Jan             |            2016 | CY2016                |                     3 | FY2016-Jan           |          2016 | FY2016              |                 1 |


Table: Dimension Employee
|   Employee Key |   WWI Employee ID | Employee           | Preferred Name   | Is Salesperson   | Valid From              | Valid To                |   Lineage Key |
|---------------:|------------------:|:-------------------|:-----------------|:-----------------|:------------------------|:------------------------|--------------:|
|              1 |                14 | Lily Code          | Lily             | True             | 2013-01-01 00:00:00.000 | 2013-01-01 08:00:00.000 |             3 |
|              2 |                 4 | Isabella Rupp      | Isabella         | False            | 2013-01-01 00:00:00.000 | 2013-01-01 08:00:00.000 |             3 |
|              3 |                11 | Ethan Onslow       | Ethan            | False            | 2013-01-01 00:00:00.000 | 2013-01-04 08:00:00.000 |             3 |
|              4 |                 7 | Amy Trefl          | Amy              | True             | 2013-01-01 00:00:00.000 | 2013-01-05 08:00:00.000 |             3 |
|              5 |                19 | Jai Shand          | Jai              | False            | 2013-01-01 00:00:00.000 | 2013-01-06 08:00:00.000 |             3 |
|              6 |                 8 | Anthony Grosse     | Anthony          | True             | 2013-01-01 00:00:00.000 | 2013-01-09 08:00:00.000 |             3 |
|              7 |                15 | Taj Shand          | Taj              | True             | 2013-01-01 00:00:00.000 | 2013-01-11 08:00:00.000 |             3 |
|              8 |                13 | Hudson Hollinworth | Hudson           | True             | 2013-01-01 00:00:00.000 | 2013-01-11 08:00:00.000 |             3 |
|              9 |                20 | Jack Potter        | Jack             | True             | 2013-01-01 00:00:00.000 | 2013-01-14 08:00:00.000 |             3 |
|             10 |                17 | Piper Koch         | Piper            | False            | 2013-01-01 00:00:00.000 | 2013-01-19 08:00:00.000 |             3 |


Table: Dimension EmployeeFirstname
|   Employee Key | FirstName   |
|---------------:|:------------|
|              0 | Unknown     |
|              1 | Lily        |
|              2 | Rupp        |
|              3 | Onslow      |
|              4 | Trefl       |
|              5 | Shand       |
|              6 | Grosse      |
|              7 | Taj         |
|              8 | Hudson      |
|              9 | Potter      |


Table: Dimension Stock Item
|   Stock Item Key |   WWI Stock Item ID | Stock Item                                   | Color   | Selling Package   | Buying Package   | Brand   | Size   |   Lead Time Days |   Quantity Per Outer | Is Chiller Stock   | Barcode   |   Tax Rate |   Unit Price |   Recommended Retail Price |   Typical Weight Per Unit | Valid From              | Valid To                |   Lineage Key |
|-----------------:|--------------------:|:---------------------------------------------|:--------|:------------------|:-----------------|:--------|:-------|-----------------:|---------------------:|:-------------------|:----------|-----------:|-------------:|---------------------------:|--------------------------:|:------------------------|:------------------------|--------------:|
|               83 |                 137 | Animal with big feet slippers (Brown) XL     | N/A     | Each              | Each             | N/A     | XL     |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.4  | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               84 |                 136 | Animal with big feet slippers (Brown) L      | N/A     | Each              | Each             | N/A     | L      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               85 |                 135 | Animal with big feet slippers (Brown) M      | N/A     | Each              | Each             | N/A     | M      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               86 |                 134 | Animal with big feet slippers (Brown) S      | N/A     | Each              | Each             | N/A     | S      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               95 |                 125 | Ogre battery-powered slippers (Green) XL     | N/A     | Each              | Each             | N/A     | XL     |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.4  | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               96 |                 124 | Ogre battery-powered slippers (Green) L      | N/A     | Each              | Each             | N/A     | L      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               97 |                 123 | Ogre battery-powered slippers (Green) M      | N/A     | Each              | Each             | N/A     | M      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               98 |                 122 | Ogre battery-powered slippers (Green) S      | N/A     | Each              | Each             | N/A     | S      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|               99 |                 121 | Dinosaur battery-powered slippers (Green) XL | N/A     | Each              | Each             | N/A     | XL     |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.4  | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |
|              100 |                 120 | Dinosaur battery-powered slippers (Green) L  | N/A     | Each              | Each             | N/A     | L      |               12 |                    1 | False              | N/A       |         12 |           32 |                      47.84 |                      0.35 | 2013-01-01 00:00:00.000 | 2016-05-31 23:00:00.000 |             5 |


Table: Fact Sale
|   Sale Key |   City Key |   Customer Key |   Bill To Customer Key |   Stock Item Key | Invoice Date Key        | Delivery Date Key       |   Salesperson Key |   WWI Invoice ID | Description                                                                           | Package   |   Quantity |   Unit Price |   Tax Rate |   Total Excluding Tax |   Tax Amount |   Profit |   Total Including Tax |   Total Dry Items |   Total Chiller Items |   Lineage Key |
|-----------:|-----------:|---------------:|-----------------------:|-----------------:|:------------------------|:------------------------|------------------:|-----------------:|:--------------------------------------------------------------------------------------|:----------|-----------:|-------------:|-----------:|----------------------:|-------------:|---------:|----------------------:|------------------:|----------------------:|--------------:|
|      49374 |      69332 |              0 |                      0 |              186 | 2013-10-22 00:00:00.000 | 2013-10-23 00:00:00.000 |                19 |            15219 | Developer joke mug - fun was unexpected at this time (White)                          | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      56882 |      56111 |              0 |                      0 |              198 | 2013-12-05 00:00:00.000 | 2013-12-06 00:00:00.000 |                19 |            17512 | DBA joke mug - it depends (White)                                                     | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      57068 |      82826 |              0 |                      0 |              167 | 2013-12-06 00:00:00.000 | 2013-12-07 00:00:00.000 |                19 |            17572 | IT joke mug - keyboard not found … press F1 to continue (Black)                       | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      57429 |      42598 |              0 |                      0 |              172 | 2013-12-09 00:00:00.000 | 2013-12-10 00:00:00.000 |                19 |            17684 | Developer joke mug - this code was generated by a tool (White)                        | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      57669 |      84888 |              0 |                      0 |              173 | 2013-12-10 00:00:00.000 | 2013-12-11 00:00:00.000 |                19 |            17757 | Developer joke mug - a foo walks into a bar (Black)                                   | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      58705 |      42573 |              0 |                      0 |              201 | 2013-12-18 00:00:00.000 | 2013-12-19 00:00:00.000 |                19 |            18085 | DBA joke mug - daaaaaa-ta (Black)                                                     | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|      59359 |      43854 |              0 |                      0 |              165 | 2013-12-23 00:00:00.000 | 2013-12-24 00:00:00.000 |                19 |            18288 | IT joke mug - that behavior is by design (Black)                                      | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|       1022 |      50625 |              0 |                      0 |              178 | 2013-01-07 00:00:00.000 | 2013-01-08 00:00:00.000 |                19 |              394 | Developer joke mug - understanding recursion requires understanding recursion (White) | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|       2974 |      43529 |              0 |                      0 |              177 | 2013-01-18 00:00:00.000 | 2013-01-19 00:00:00.000 |                19 |              971 | Developer joke mug - understanding recursion requires understanding recursion (Black) | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |
|       3661 |      49849 |              0 |                      0 |              177 | 2013-01-23 00:00:00.000 | 2013-01-24 00:00:00.000 |                19 |             1178 | Developer joke mug - understanding recursion requires understanding recursion (Black) | Each      |          1 |           13 |         15 |                    13 |         1.95 |      8.5 |                 14.95 |                 1 |                     0 |            11 |

 


tables: name: Dimension City
data: Dimension City.csv
name: Dimension Customer
data: Dimension Customer.csv
name: Dimension Date
data: Dimension Date.csv
name: Dimension Employee
data: Dimension Employee.csv
name: Dimension EmployeeFirstname
data: Dimension EmployeeFirstname.csv
name: Dimension Stock Item
data: Dimension Stock Item.csv
name: Fact Sale
data: Fact Sale.csv
label: Fact Sale
City Key
Dimension City
City Key
Fact Sale
Customer Key
Dimension Customer
Customer Key
Fact Sale
Invoice Date Key
Dimension Date
Date
Fact Sale
Salesperson Key
Dimension Employee
Employee Key
Fact Sale
Stock Item Key
Dimension Stock Item
Stock Item Key
Dimension EmployeeFirstname
Employee Key
Dimension Employee
Employee Key
output: {
    "joins": [
        {
            "from_table": "Fact Sale",
            "from_column": "City Key",
            "to_table": "Dimension City",
            "to_column": "City Key"
        },
        {
            "from_table": "Fact Sale",
            "from_column": "Customer Key",
            "to_table": "Dimension Customer",
            "to_column": "Customer Key"
        },
        {
            "from_table": "Fact Sale",
            "from_column": "Invoice Date Key",
            "to_table": "Dimension Date",
            "to_column": "Date"
        },
        {
            "from_table": "Fact Sale",
            "from_column": "Salesperson Key",
            "to_table": "Dimension Employee",
            "to_column": "Employee Key"
        },
        {
            "from_table": "Fact Sale",
            "from_column": "Stock Item Key",
            "to_table": "Dimension Stock Item",
            "to_column": "Stock Item Key"
        },
        {
            "from_table": "Dimension EmployeeFirstname",
            "from_column": "Employee Key",
            "to_table": "Dimension Employee",
            "to_column": "Employee Key"
        }
    ]
}