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";