Snowflake SQL query resources for analysts used to Redshift

I’ve spent the last seven years using Amazon Redshift, and during that time Snowflake became the standard modern data warehouse. (I did a survey of relevant companies’ data stacks when we evaluated Looker, and Snowflake was the overwhelming favorite warehouse.) Odds are pretty good that I’ll use Snowflake in my next job. So how will I go from training colleagues about efficient Redshift queries (and Introduction to SQL for interns) to writing Snowflake queries in the future?

I started with fairly obvious Google queries, “snowflake sql tutorial” and “snowflake sql query examples”, then added “query from redshift to snowflake” to find people’s comments about migrating. I knew what types of differences I was looking for:

  1. Structural differences in how data is stored/accessed
  2. Syntax differences
    • Timestamps and dates – always painful
  3. Special capabilities / functions

Data engineering posts about migrating your warehouse are good for the first two; analytics and analytics engineering posts about advanced query tips are good for the third. Snowflake documentation and writing is good for filling in syntax details and expert advice once you know what to look for.

Continue reading
Posted in Analysis, Development, Tools | Tagged , , | Leave a comment

Why my alumni giving goes to Howard University, a school I never went to

I made my alumni giving contributions today – to my high school, college, and MBA program, and also to the Howard University Gift a Senior campaign.

I’m not a Howard alum. But I am from Washington DC, and in particular I grew up when DC was the murder capital, when just about every local story was bad. Our mayor was convicted on drug charges, and Congress took away financial home rule.

But Howard was admired. One of the top historically black colleges & universities, it had taught a miles-long list of notables, from public servants like Thurgood Marshall to artists like Toni Morrison. Howard was a demonstration that DC could lead, that its citizens weren’t just the owners of Taxation Without Representation license plates and the victims of those murders.

I’ve been sad over the last decade to read about Howard’s multiple crises of leadership and finances, and not sure how to lend support. Then in 2015 I read about the Gift a Senior campaign.

Gift a Senior grants money to students who have fulfilled their graduation requirements but can’t graduate because they owe money to the university. It might be a few hundred or a few thousand dollars. The problem isn’t getting smaller – in 2015 “180 prospective graduates owed about $380,000,” and this year the campaign page talks about 224 seniors with balances totalling $1.35 million.

And so I’ve given Gift a Senior the same amount I’ll pay on my student loans this month.

I have student loans because I invested in my future career by going back to school. I’m grateful I can afford to pay them, and sorry I can’t make it to either my high school (20th) or business school (5th) reunions this year. I’ve been fortunate, and I remember the DC I grew up with. Gift a Senior is my investment in the future of DC, and of Howard University, and of a Bison whose story I might not hear but who will write a better one because they can graduate next weekend.

I hope to wipe out more of my loans this year, so I’ll have to find a new metric for how much to give next year. But my alumni giving will again go to my own schools, plus to Gift a Senior.

Posted in Education, Nonprofit, Personal | Leave a comment

Minimum Viable Project Management

Posted in Beyond Websites, Design, Development, Information Architecture, Management, Tools | Tagged , | Leave a comment

Tours of duty vs old-school promotions: everything old is new again

Ben Casnocha and Chris Yeh spoke a while ago on NPR’s OnPoint about their HBR article with Reid Hoffman, Tours of Duty: The New Employer/Employee Compact. People calling in seemed to have two main concerns: either they didn’t want the tour-of-duty life, or they worried that this was creating two classes of employees.

And they’re right: it does mean there are different kinds of employees. There are employees who place a higher value on security and building on existing knowledge, and those who place a higher value on innovation and building new kinds of knowledge. But that isn’t different from in the past.

A high-potential employee in a very traditional company of lifetime employees had the same kind of tour-of-duty experience as Casnocha, Yeh, and Hoffman are suggesting, except that they may have had a little more help picking their next tour. They’d be in a role for a few years, and then they moved on to the next role, horizontally or vertically, because they’d achieved the goals set for them. The company’s talent management and succession planning programs would suggest what they needed to learn next.

Casnocha, Yeh, and Hoffman are trying to reinstitute help in picking a next role for new-economy jobs. If (like many people) you know you’re likely to switch companies within five years, they say your manager should help plan what you’ll achieve and learn at this company and, if you complete that plan, support finding your next project even if it’s somewhere else. That intentional progression of responsibility is harder across organizations, but losing it does an injustice to great employees.

Since we know that knowledge spillovers (partly due to employee mobility) are a driver of innovation clusters, employees and companies who think this way are also good for whole groups of companies. Some people will stay and become mainstays of their companies, building firm-specific human capital; some people will move and drive innovation for a whole region or industry.

If someone’s done great work for me, I’d rather be part of that conversation than have the employee decide they aren’t learning enough and leave unexpectedly. I’d want the chance to define a new role that they might choose to stay for. And if they have an amazing opportunity elsewhere, what goes around comes around – they may refer my next great employee from their new company, when that person wants to move in turn.

Posted in Management | Tagged , , , , , , | Leave a comment

We think our phones are private, while law enforcement gets our data

Two individually interesting studies released this week combine to demonstrate how mismatched our privacy expectations and our data uses are.

From the Berkeley Center for Law and Technology‘s Berkeley Consumer Privacy Survey, we learn that most Americans think their mobile phone data is at least as private as their home computer (especially if they’re young):

A plurality of Americans think their mobile carrier shouldn’t keep their location data at all:

From Rep. Ed Markey (D-MA) (via GigaOM and the New York Times, other coverage by MSNBC, TPM, and emptywheel), we learn that cell carriers received at least one million requests (via court orders and subpoenas) for subscriber information in 2011: AT&T (PDF) and Verizon (PDF) each received approximately 260,000 requests each, while Sprint (PDF) estimates it received 500,000 subpoenas (does not include court orders). Consumers might be surprised to know that if each AT&T request were for a different customer, one in every 400 AT&T customers would have had their mobile information requested by law enforcement in 2011.

It’s easy to assume a mobile device is private, and more than 75% of Americans wouldn’t let an acquaintance or work colleague borrow their phone (Table 3, Berkeley study). One paper on Social Implications of Mobile Technology (PDF) says “personal communication technologies are distinctive from other network technologies (e.g. the computer) in that they are often worn on body, highly individualized, and regarded as extensions of the self.” Consumers haven’t internalized the fact that this extension of themselves is also an extension of technological tracking. That’s not always negative (consider 911 calls from mobile phones), but ignoring it while law enforcement requests increase and include “cell tower ‘dump[s]’ for data on subscribers who were near a tower during a certain period of time” means unaware consumers will have their information disclosed more and more often.

Consumers might also want to know the law enforcement request data when they choose a network provider: T-Mobile “does not disclose the number of requests we receive from law enforcement annually” (PDF), so don’t choose them if you’re curious about these numbers. Sprint appears to receive disproportionately more subpoenas and far more subpoenas overall: AT&T, Verizon, and T-Mobile all report half the request are subpoenas – so AT&T and Verizon each have 130,000 per year – while Sprint reports 325,000 court orders over five years and an estimate of 500,000 subpoenas in 2011. This may be because of Sprint’s pay-as-you-go sub-brands.

All data from 2011 unless otherwise noted:

  Size rank Subscribers (millions) Law enforcement requests Subpoenas Other (court orders, emergency requests)
Verizon 1 108 260,000 50% 50%
AT&T 2 101 260,400 131,400 129
Sprint 3 53 500,000+ 500,000 325,982 over five years
T-Mobile 4 34 No response 50% 50%
TracFone 5 19 No response    
Clearwire 6 10 Not asked for information    
MetroPCS 7 9 Fewer than 144,000    
Leap/Cricket 8 6 42,500    
U.S. Cellular 9 6 19,734 10,511 9223
C Spire 10? 1 12,500 over five years    
Posted in Mobile, Privacy | Leave a comment

Introduction to SQL

Presented to the Chicago Booth technology club in spring 2012. Back-dating this post now I’ve finally uploaded the presentation in 2014….

Posted in Development, Education, Tools | 1 Comment

Millennial recession poetry

Every so often you start reading generational sociology (I’m one of the very-tail-end-of-Gen-X people) and find a poem instead.

the Greek ideal of “thumos”, which is the lust not for money or success (in the conventional sense) but the lust for glory
we want glory through our ideas-we want to know we matter
(10:33) the cold truth is that not all of us are brilliant
we are not all big thinkers. Not everyone’s TED talks will change the world
some of us will just dissipate into the ether
(10:34) but it is the digital connectivity, that proximity to these people, that makes us think that perhaps we will succeed as well
(10:35) ok, i’m done
(10:36) no i’m not
here’s why the recession is so devastating to us
we grew up, all the way through college, with everything seeming so ripe and possible
(10:37) we had a PC education—people tried to hide from us as long as possible that not everyone is equal
we were told we all have a fair chance of making it
that’s just not so
and we’re starting to realize that
(10:39) are you even listening to me anymore?
(10:41) ME: hi sorry
(10:42) i was writing an email
i am filing your comments
in my file.
(10:43) i think
your cover letter is good!
CLARE: i thought it was ok
(10:47) but I am, to be honest, expecting a rejection.

Posted in Uncategorized | Leave a comment

How to Learn About the Financial Crisis

Considering I went to business school to focus on marketing, this is a weird term. I’m taking Investments (the first finance course), Central Banking (an advanced macroeconomics course, taught by the former head of the German central bank, Axel Weber), and Probability (PhD level, taught by a finance-focused Bayesian statistician). Oh, plus I’m a TA for microeconomics. But in the current economic climate, this is where I’ll learn the most.

For Central Banking in particular, I feel like I’m running as fast as I can to keep up with what I should understand. I wasn’t an investment banker before school, and my economics classes from ten years ago were long-forgotten. So what’s going to let me survive the class?

  1. Historical backbone: I read the Washington Post (which bothers to cover international news) daily and have for 20 years, plus lots of other online news. I’m still trying to get in the habit of reading the Wall Street Journal consistently.
  2. Economic policy: I read Ezra Klein. If you believe in data and graphs, and that government and business interact in unpredictable ways, Wonkblog is your friend. The daily Wonkbook post/email links to the most thoughtful economic discussion from papers, blogs, and think tanks. This year Ezra has a team of reporters to expand their coverage – very different from when I started reading his blog while he was still in college.
  3. Macroeconomic theory: I read my old Macroeconomics textbook from college during the month before classes started. Its preoccupations are Japan’s Lost Decade and the Clinton budget surpluses rather than the current financial crisis, but that means I can see how much of the discussion sounds the same.

And then there are my current reference posts, the best explanations I’ve found of what’s happened and intervention options. I’m planning to add to this list, so I don’t just keep these tabs open in Firefox all term.

And the people like to publish the next reference post:

Let me know if you have a favorite reference to add….

Posted in International, Journalism | Leave a comment

Why Squid?

Because cephalopods are amazing.

Posted in Uncategorized | 2 Comments

Selling Attention – the New York Times paywall, Lincoln, Chuck, Subway, and the Washington Post redesign

This week I’m actively selling attention. Others have sold it for me in the past, every time I read an article with an (ignored) ad next to it, but now the purchasers are making their requests explicit and obvious. And I like this way better, at least while there aren’t too many buyers.

  • Lincoln, an existing advertiser with The New York Times, has targeted 200,000 heavy readers of the newspaper’s website with an offer to sponsor their digital subscription for 2011. – I almost didn’t read this offer. It came up as an interstitial, between the homepage and the article I wanted to read, so the first two times I clicked by without actually seeing it. Then I belatedly noticed it had said something about the NYTimes paywall, went back and looked at the banner version on the NYTimes homepage, and clicked through to accept a gift from a brand that is basically irrelevant to me.

    It’s not that I’m not a good target demographically – MBA students fit “‘thought leaders’ in a younger-age bracket” and “the kind of consumers who are interested in the newer Lincolns” pretty much perfectly, I’d guess – but personally I don’t expect to buy a car in the next five years, and if I did it would be chosen for function not luxury. I feel a little weird about accepting the offer, and now feel some obligation to find out something about Lincoln to be sure I’m not dismissing it unreasonably. Reciprocity is a powerful trick (“techniques used in advertising and other propaganda whereby a small gift of some kind is proffered with the expectation of producing a desire on the part of the recipient to reciprocate in some way”).

  • Chuck vs the delicious Subway sandwich – Chuck has been my favorite TV show for the past several years (funny, geeky, and the geeky stuff sometimes saves the day). It has repeatedly been almost-canceled, and one of the things that saved it was a relationship with Subway – aggressive product placement led to a fan campaign in which the show’s star participated (demonstrating to Subway that its sponsorship led to sales), which then led to more aggressive product placement.

    But they deliberately make the product placement stand out (not like the “oh, hey, it’s Windows Phone 7 and we’re going to linger on the phone for a really long time for no reason” placement I saw elsewhere this week), and they make it a joke all the fans are in on. One review commented, “I don’t know who is ever against product placement because every time the people at Chuck mention the greatness of Subway, I find it hilarious.” I’m aware I’m being sold, and yes, the next time I see a Subway around breakfast time I’ll try to go buy the relevant product, because I appreciate their sponsoring content I love.

  • The Washington Post redesign – I’ve been reading the Post almost every day for 19 years, 13 of those online. I’m not a fan of the redesign. The links are black, making it harder for me to scan for things I can click on to get more information. They used all-caps for a long headline. It took me several days to pick out where they’d put the sections of news I care about and what parts of the page I could safely ignore. But most of all, that first day made it very clear what one thing I was supposed to do when I arrived on the homepage: click on the ads. This on a day when the main headline was about nuclear catastrophe.

    washingtonpost.com screenshot

    They’ve since added back photos to the top content area, so it’s not quite so much “there’s some boring black and white stuff and – ooh, shiny, a brightly colored ad! and another!” but it still looks visually like the right column, with ads and links, is the dark-colored important area. I’ve taught myself to be pretty ad-blind (see above about not reading the Lincoln offer), and the design makes it harder to ignore the ads in favor of the content that brought me to the site. The ads aren’t helping provide the content I want: they’re getting in the way of it. I’m the product being sold, and the Post isn’t even winking at me as they sell me. This isn’t the way I want to see sponsorship go.

There are plenty of issues with patronage models. Editorial independence is a big deal. But while selling lots of little ads preserves independence better, it’s much more demanding for the reader/watcher in terms of cognitive load. I’d rather have one sponsor; then I know whose influence I might have to discount – I’m used to that model from white papers, stadium names, and opinion columnists. With one sponsor, I know who I’m selling my attention to, and I can decide without too much difficulty how much weight I want to give their coin.

Posted in Advertising, Business models, Design, Journalism, Marketing, Personalization Design, Publishing | Tagged , , , , , , , , | Leave a comment