Interface investigation

[drewbanin]

Investigating interfaces

I wrote a hacky python script as a proof of concept. There is much to expand upon and clean up, so try your best to ignore the code itself. Right now, the script doesn't actually create/drop schemas/views, but that's a one-line change.

The script sorts the queries by filename, so there's 1_base.sql, 2_filter.sql, etc to resolve dependencies. I think we should be able to actually generate and walk a DAG instead (as @cmerrick mentioned) when the time comes.

Interface hints are given as view comments:

COMMENT ON VIEW pardot_analysis.visitoractivity_transformed IS 'timeseries,funnel,cohort';

I think this needs some more thought. Namely, I suspect that visitoractivity_transformed should expose the interface event_stream, and event_stream should expose timeseries,funnel,cohort. But again, we might not be there yet...

Outstanding questions on my end:

• are we enforcing the base/filter/transform paradigm? Or dropping it? Or ambivalent towards it?
• do we have plans to use interface hinting for anything besides eventual validation? I have some ideas but I'm keen to hear your (analyst) collective thoughts.

Respond!

[jthandy]

Haven't looked at this yet, but wanted to respond to your specific questions first. Here's my take.

• My understanding was that we already decided to drop the base/filtered/transformed view of the world. We had a conversation last week where we said we were going to be less opinionated about how people found their way to the interface, favoring instead to define the interface between the modeling and analysis layer very clearly. We should provide a template of how to "get from here to there" (raw data to interface) but ultimately it's up to the individual user to implement the necessary SQL to meet the needs of the interface. This is a key part of the "top down" approach--we're not trying to model the world, we're creating analyses, and then creating a space for users to map data to those analyses in whatever way they need. @drewbanin can you just confirm that this makes sense and that you agree with it? I want to make sure we're on the same page here.
• I have instincts about things like you're asking, but I'm explicitly attempting to ignore them for the moment. I can imagine the interface hints as performing a vital function in later analytical layers since they can be read by any number of tools, either ones built by the community or commercial ones. Really, they could be a key to the entire project, because what you need for plug-and-play analysis is hints to the analytical layer as to how to treat certain datasets. I don't want to think beyond that for the moment because I'm nervous about constructing castles in the sky :)

Looking through the code now.

[jthandy]

Ok, overall comments on this PR:

• There is way more code than required for the top-down approach. Top-down approach should essentially have only the modeling required to get you to the interface. Which means any tables that are not related to implementing the interface don't have models, also means you don't need to alias columns in tables unless it's required. Also means Pardot doesn't need multiple tiers of models (does still need the metadata and transform logic).
• In top-down, we have to start with the analysis. So, I think that this PR should actually include the analysis for the three event stream interfaces. It's not really "working" without them.

Please let me know if you want to discuss this today. We've spent a bunch of time talking about this, but I think we're still not getting detailed enough in determining what exactly next steps look like before you go to write code. I'm going to really focus on fixing this communication problem so that we can iterate more tightly.

[drewbanin]

@jthandy please take another look! cc @cmerrick

[cmerrick]

This looks like a step in the right direction. We need to figure out how to allow the source schema.table for a model to be set at runtime too so these can be reusable for other data sets. We can tackle that next.

[cmerrick]

I just ran into a problem that we're going to have to get around: dropping the schema drops all permissions granted to it, so they would need to be recreated every time. For now switching from a drop if exists+create to a create if not exists will help us avoid this, but in that scenario I believe the create or replace view... commands are going to fail if the schema of the view changes.

[jthandy]

I'm good on this PR once the existing comments have been addressed.

[drewbanin]

@cmerrick @jthandy great feedback, thanks. Will address your collective comments ASAP!

[cmerrick]

@drewbanin I have a few other PRs ready to merge that depend on this branch, lmk if there's anything I can do to help get this merged

[drewbanin]

Don't have much bandwidth this week -- I'll take a crack at it tonight and let you know where I leave off! Worst case, I'll finish and merge Tuesday afternoon

Sent from my iPhone

On Mar 7, 2016, at 4:22 PM, Christopher Merrick notifications@github.com wrote:

@drewbanin I have a few other PRs ready to merge that depend on this branch, lmk if there's anything I can do to help get this merged

— Reply to this email directly or view it on GitHub.

[drewbanin]

@cmerrick @jthandy addressed the changes the two of you mentioned. There are some issues raised w/ no clear solution yet. Let's punt on those for the moment. I'll create any necessary issues as appropriate

merging