Slowly-changing Dimensions Type 2 Data

A quick tidbit about slowly-changing dimensions, a.k.a. Type 2 data, in Knarr. I ran across this today as I was trying to link together some marketing campaign data with website traffic data and I thought it could be useful for others.

Caveat - this could add a ton of records to your table if you’re not careful, so be sure you’re not trying to do this on a huge dataset or it may get very slow.

I had a dataset of 67 campaigns that had start and end dates, but I wanted to link those to specific dates in our website activity data from Google Analytics. So what I did was create a calendar table in Google Sheets (just start from your min date and drag the column down to your max date) and did a FULL OUTER JOIN on my campaigns table using the Start and End dates to link the records properly.

This resulted in a ~7k-record table but accomplished the functionality I needed.

See below for the code snippet:

SELECT
	ca."id" AS "Campaign ID",
	ca."Campaign Name",
	ca."Campaign Type",
	ca."Campaign Start",
	ca."Campaign End",
	ca."Target Audience",
	ca."Opens",
	ca."Clicks",
	ca."Cost",
	cal."Date"
FROM "Marketing Mock Data_Campaigns" ca 
FULL OUTER JOIN "Marketing Mock Data_Calendar" cal 
   ON ca."Campaign Start" <= cal."Date" AND ca."Campaign End" >= cal."Date";