Midterm 2, Fall 2022: Capturing Data Changes for Slowly Changing Dimensions¶

Version 1.0.0

Version History

1.0.0

• Initial release.

All of the header information is important. Please read it..

Topics, number of exercises: This problem builds on your knowledge of working with tabular data. It has 9 exercises, numbered 0 to 8. There are 19 available points. However, to earn 100% the threshold is 12 points. (Therefore, once you hit 12 points, you can stop. There is no extra credit for exceeding this threshold.)

Exercise ordering: Each exercise builds logically on previous exercises, but you may solve them in any order. That is, if you can't solve an exercise, you can still move on and try the next one. Use this to your advantage, as the exercises are not necessarily ordered in terms of difficulty. Higher point values generally indicate more difficult exercises.

Demo cells: Code cells starting with the comment ### define demo inputs load results from prior exercises applied to the entire data set and use those to build demo inputs. These must be run for subsequent demos to work properly, but they do not affect the test cells. The data loaded in these cells may be rather large (at least in terms of human readability). You are free to print or otherwise use Python to explore them, but we did not print them in the starter code.

Debugging your code: Right before each exercise test cell, there is a block of text explaining the variables available to you for debugging. You may use these to test your code and can print/display them as needed (careful when printing large objects, you may want to print the head or chunks of rows at a time).

Exercise point breakdown:

• Exercise 0: 3 point(s)
• Exercise 1: 2 point(s)
• Exercise 2: 1 point(s)
• Exercise 3: 2 point(s)
• Exercise 4: 3 point(s)
• Exercise 5: 3 point(s)
• Exercise 6: 1 point(s)
• Exercise 7: 2 point(s)
• Exercise 8: 2 point(s)

Final reminders:

• Submit after every exercise
• Review the generated grade report after you submit to see what errors were returned
• Stay calm, skip problems as needed, and take short breaks at your leisure

Scenario (don't dwell on this)¶

You have just been hired by the hot new startup Spot-i-flix-ify (this is a fictional company which will offer video and audio streaming services) as a Data Scientist. This is a small startup so you have to "wear many different hats," so to speak. Your first task on the job is to set up their data warehousing so that they can capture a historical record of their operations for analysis later. The operational database (which someone else has already set up) only contains the current state of the operation to maintain maximum efficiency while performing tasks like adding new customers, changing services, applying promotions, etc. It will not contain any history and is not intended have complex queries run against it.

While this is a fictional company and simulation data, there is a real-world use case for the processes developed in this notebook.

Data (Don't dwell on this. The structures you are working with will be explained in each exercise.)¶

You are working with four tables:

• customers - Center of the "star" schema. Primary Key: id.
  • customers.id - a unique identifier for an individual customer.
  • customers.paid - ('True'|'False') - indicates whether a customer has paid their bill for their upcoming month of service.
• prices - The prices of the services offered by Spot-i-flix-ify. Primary Key: service, tier, promo
  • prices.service - Name of the sevice
  • prices.tier - Tier of the service. A service can be offered in several tiers. Higher tiers give customers more features.
  • prices.promo - Promotion which can be applied to a service/tier combination to offer a discount to customers.
  • prices.price - The price of a particular service/tier/promo combination.
• services - Services which each customer is subscribed. Primary Key: cust_id, service; Foreign Keys: cust_id references customers.id, (service, tier) references (prices.service, prices.tier)
  • services.cust_id - id of the customer associated with this subscription.
  • services.service - name of service associated with this subscription.
  • services.tier - tier of service associated with a subscription.
• promos - All promos which a customer has ever used for any service. This historical information is required to prevent customers from using the same promo twice.
  • cust_id - id of a customer associated with a particular record.
  • service - service which a customer used a particular promo on.
  • promo - name of the promo associated with a particular record.
  • time_left - number of remaining months for which the promo price is applied to a service for the customer. If all promos associated with a cust_id/service pair have 0 months left. The "base" promo is applied to the customer for that service.

On data types¶

These tables are made available to you in a staging environment as Pandas DataFrame objects. All columns in all of the DataFrames are strings (even the columns where you would expect other data types).

On SQL¶

We used Pandas exclusively in developing this exam, however some exercise are solvable using SQL. In the cell below we have included the function dfs_to_conn which can be used to create in-memory database connections. If you pass in a dictionary mapping table names to DataFrames, dfs_to_conn will return a sqlite 3 connection with all of the data in the DataFrames available under the names given as keys. You are also free to write to the in-memory database by creating tables, inserting/deleting/updating records, etc. Anything that SQLite allows should work!

Example:

my_df = pd.DataFrame({'A':[1,2,3], 'B': [4,5,6], 'C':['x', 'y', 'z']})
print(my_df)
#    A  B  C
# 0  1  4  x
# 1  2  5  y
# 2  3  6  z
conn = dfs_to_conn({'my_table': my_df})
cur = conn.cursor()
cur.execute('select A, B, C from my_table')
result = cur.fetchall()
conn.close() 
print(result) # list of tuples, each tuple is a row
#[(1, 4, 'x'), (2, 5, 'y'), (3, 6, 'z')]

Exercise 0 - (3 Points):¶

Motivation (Don't dwell on this):
The business logic behind the database requires all promos which a customer has ever participated in be stored in the "live" business data in order to prevent a customer from using the same promotion twice. However, for keeping the historical record, the data consumers (i.e. your bosses) are only interested in seeing which promotion is actually being applied to a customer's bill. We need to extract this information from the promos table.

Requirements:
Define get_active_promos(promos). The input promos is a DataFrame with columns as described in the promos table above. These are the columns/descriptions:

• cust_id - id of a single customer.
• service - a service where the customer has participated in a promo.
• promo - name of a promo in which a customer has participated for the associated service.
• time_left - the time the customer has left on the promo.

Note: There may be many records in promos with the same cust_id/service combination. However, at most one such record will have a time_left value other than '0'.

Your function should return a new DataFrame, active_promos derived from promos with the schema outlined below. There should be exactly 1 record in active_promos for each unique combination of cust_id/service found in promos.

active_promos - the promotion which is actually applied to each customer for a particular service

• 'cust_id' - identifies an individual customer.
• 'service' - identifies a service for which the customer has an active promotion. The customer may not actually be subscribed to the service!
• 'promo' - the active promo for the cust_id/service pair.
  • If 'time_left' is '0' for all records associated with the cust_id/service pair in promos, this column should have a value of 'base'.
  • If there is a record associated with a non-zero 'time_left', this column should have the 'promo' from that record.

The demo included in the solution cell below should display the following output: ``` cust_id promo service 0 0 promo_1 audio 1 0 base video 2 1 base audio 3 1 promo_1 video ```

The cell below will test your solution for Exercise 0. The testing variables will be available for debugging under the following names in a dictionary format.

• input_vars - Input variables for your solution.
• original_input_vars - Copy of input variables from prior to running your solution. These should be the same as input_vars - otherwise the inputs were modified by your solution.
• returned_output_vars - Outputs returned by your solution.
• true_output_vars - The expected output. This should "match" returned_output_vars based on the question requirements - otherwise, your solution is not returning the correct output.