I do some ETL stuff with redshift but it's mostly been exploratory. We have a few legit Data Scientists who do a lot of the heavy lifting, and then that will get integrated into our (non-redshift, online) databases in some other way. Like something I've been working on in my so called spare time is identifying and dealing with forms of "cheating" (it's not always malicious, like some people have multiple devices and when they work out it might get recorded 2 or even 3 times)

Redshift is an offline database - we push data to it nightly. I do use it for some reporting for in-house stuff. It's one of those weird ironies. If you're used to normal database stuff, it is Very Slow if you have a limited query. Like if I want to do a query on my own user's workouts for the last week, it takes like 30-60 seconds. Intolerable. Our online database can get me results back in under 50ms.

But I can run the same query for 10,000 users and the whole result STILL comes back in 30-60s. When it's warmed up, maybe I can do the same query for a MILLION users in 30-60 seconds. It really turns your expectations on their head.

Ha I JUST had that experience:

We have a regular table with ~400k rows and a JSON field in Django. The old data is horrendous since the input was from PHP and had 0 validation so we need to escape all the text values.

My initial plan was to use redshift to find row PKs w/ <> (expected <10k actual updates), thinking that was going to be faster than Django which has 0 JSON filtering. However I barely know SQL so experimenting with json_extract_path_text was a nightmare. I finally figured out the query, but our data team put in 30 min query limits (apparently we all share the same resources in redshift?), so it wouldn't even run.

Ended up just using python regex which took around 20 min.

Using awk would probably be even easier and probably faster than python (python solution is still good idea). In doing big data stuff I've found the old school unix tools are surprisingly efficient.

What are your first thoughts if you find a query that is joining on up to 7 tables (including a couple self-joins).?

So I don't really wanna derail this thread, as its obvious you have not really studied the subject (I'd be happy to talk you through this in BFI or via PM, but I doubt you have any intention to educate yourself).

Your fundamental assumption that many investors don't lose massive sums is incorrect. There is a thread about raptor buying houses in BFI where everyone told him they were massive money losers with a negative real ROI, which he promptly ignored and purchased them. When you have landlords who are essentially paying people to live in rentals, it becomes pretty easy to offset a variety of supplements you mentioned.

NB: I'm speaking about the vast swath of middle class SFH. Crack shacks almost always spin off mad cash flow. Along with well managed luxury rentals.

Nah. Pretty much done on the subject. But it seems relevant since this is a pretty large financial decision for most people and misleading someone into a five figure error is kinda lol.

What are you using to get your data into Redshift? I'm playing with Alooma to grab CSV files from an S3 bucket for storing our raw input data.

That rent on average is set such that it loses tons of money for landlords sounds implausible and you're generally not likely to have good experience with landlords who are losing money because they are probably equally awful at managing properties and you may be forced to move when they come to their senses. This sounds like someone being excited to be at a restaurant that loses money and talking about how eating out saves money compared to cooking because there are so many bad restaurantiers who lose moeny. It's unlikely that landlords are losing money because being a landlord in a general is a losing proposition - it's most likely because they are operationally inefficient or getting bad deals on the supply side. I'm not sure if you're providing anything other than basic truism that someone somewhere is losing money - if you're getting a rental, what are the odds that somehow you managed to pick one where you get an otherwise good landlord who does a good job of managing the properties, is willing to let you stay indefinitely but is so bad at finance as to be willing to lose money indefinitely as well? You're at best talking about a temporary dislocation of the sort I'd already pointed out and most likely just anecdotes. Btw I've worked in this field and I've never seen market rent stay low enough to make renting profitable in the long run.

SF rentals are generally fairly rare and from my experience are disproportionately concentrated in bad neighborhoods with bad school districts. Either way, living in a rental SF is subpar experience because part of the point of living in SF is being able to choose exactly the type of house you want and then being able to remodel it to your liking. SF rentals are neither common enough for you to find the exact house you like, nor does it offer you the ability to remodel, which means you're almost always compromising on several dimensions, not to mention the potential need to move as your situation changes. Apartments vs Condos are a much closer like-for-like comparison and if you're adding "but for SF" stipulation, you're adding yet another source of transaction cost.

Btw, mods, feel free to nuke these into a separate thread or BFI or whatever.

An in-house thing built with python. I haven't dealt with it very much but I think it's mostly doing queries against our online database and pushing the results into redshift. As in, it runs once a day and copies any rows added or changed in the last day. Also, some of our mongo data is copied over, which is nice.

What's interesting about LA is, if there is no traffic, the freeways are designed so that everything is 20 minutes away. Unfortunately, with traffic, getting from Hollywood to Santa Monica, which is only 10 miles away, would take over one hour.

The buses in LA are an utter joke, but somewhat functional. Many of the "frequent" and "express" buses are supposed to run every 15 minutes, but it isn't uncommon to wait for over an hour for a bus to come. If you are going from downtown to Santa Monica (15 miles), forget about it. Looking at Google Maps right now, the expected travel time is 49 minutes in car!

If you are on the train lines, it isn't so bad. In fact, many people living in downtown LA use the trains and Uber to get around, which is what I was doing.

I'm not sure how DC got included in this, even a maybe. Search Google for "DC Metro" and filter for news articles. You won't find any positive reports.

If you think my last one was harsh...

Okay, some parts of LA aren't like this, but...

Everyone is staring at their phones all day and no one talks to each other. If you go to a restaurant, it is solid crickets and everyone blinding themselves with pixels. You go to a concert, phones raised up, no one talking or really having fun. LA was definitely different 10 years ago, but it changed for the the worst. Don't even get me started on Holllywood. The image conscience is over the top. Downtown would be a terrible place to raise a child, of course, but at least people are normal, and it isn't unseemly to go to a $250 per plate restaurant wearing a t-shirts and jeans.

People complain about not being able to get a date or a girlfriend. There was an article a few years back, in the local rag, about sex and how hard it is to get going with it. Some girl was complaining that she didn't have sex for over 5 years. Another guy, 8 years. She is a very good looking woman, by the way.

If I'm walking down the street and a woman is walking toward me, she will cross the street to be sure we aren't on the same sidewalk. I'm a white guy, rail thin, and mostly harmless.

Finally, especially in the "industry," there is a ton of con artists and scammers. I met people from New York who found the people outright appalling at how sophisticated and good they are.

The fact is, people are highly disconnected from each other, and it is very hard to get around and very difficult to talk to anyone. There are some exceptions to this rule, but you really have to dig, or just stay in downtown, the Arts District, or Pasadena. Be ready to move once the idiots move next door.

I wouldn't want my kids growing up thinking that people are scary.

SF is a whole never level of don't talk to me. If you go to a coffee shop, or you are walking down the street, people will actually snap their head away from you if you attempt to talk to them or make eye contact. This is horrible.

But what I find interesting about SF is that, once you break through, you can't get anyone to shut up. These people are just desparate to talk to anyone at all, and they are so needy to talk to anyone who isn't an aloof *******.

When I think of SF, I think of one particular interaction I had. Unfortunately, I had a concert to attend and was leaving the next day.

I walked into a coffee shop in the Mission District. For context, the coffee shop was dead silent, despite being SRO. Everyone one on their iPads and Macs. I simply said "good afternoon" to the girl working the counter, and she was taken aback. She saw that I asked because I meant to. I gave a small smile and she just poured out to me, right there. Despite having a line behind me, she was going on about her week, her weekend, just chatting me up as if no one else was in the world. I didn't really say much back to her, but you could just tell how incredibly shocked she was that someone actually spoke to her. Believe it or not, this girl was absolutely gorgeous and there is no doubt, if I had a couple more days, I'd have asked for her number. And this sort of thing happened multiple times. How can there be any single and desperate woman in a town that is 4 women to each 6 men?

I'm only using this one example, but this situation was far from unique. I'm hardly an extrovert and this situation included both men and women.

Compare this to Austin. If you go to a bar, no one is looking at their phones. People are talking to each other, and if you strike up a random conversation (or someone strikes one with you), you talk to each other.

One day, I was chatting up some dude for abut 10 minutes in front of a bar, and we started joking about Austin. At first, you are like "why the **** are you talking to me... hey wait... this is how life is supposed to be."

One of my friends came and visited me during Christmas and he just shocked at how friendly everyone was. His review: "Is this Pleasant Town? Everyone is so damn friendly and approachable... What is going on?" (Then we went to South Lamar and he had to adjust his thinking a bit)

So, sure. Raise a kid and buy him a skateboard. In LA and SF, he'll take it to the skate part and stare at his phone and not make any friends and be a 40y.o. virgin. You just bought him $150 seat with wheels and terrible ergonomics.

With all that said, you can see now why I'm so appalled at any rudeness I see here in Austin. It just ain't right. The friendliness and slower pace is quite disconcerting at first, but if I was to chose to raise a kid (never going to happen, but that's another topic), I'd chose any place but California any day of the week. I'm not one to judge what is "normal," but I would want my kid to have the perspective to know what LA and SF are like if he or she ever decided to go there. Of course, things may well be much different in another 15 years, but the paranoia, the overkill child-coddling, the image-over-substance culture, and stupid slew of laws in California is a perfect representative of the insanity well the state has become.

Finally, with the state being more and more expensive, how old would your kid have to be before he or she can get their own place? What 21 year old can afford to pay $3k / month on rent?

Interesting, yeah I don't have extensive experience or anything but the couple times I visited, the metro seemed usable, but I guess it could be different for people who use it more often.

daveT, that was a pretty good write up on Austin. I spent time in the bay area, NY, Virginia and Florida, and I had no doubt in my mind that whole time away that I'd move back to Austin and spend the rest of my life here.

I'll respond more later, but in case anyone who isn't familiar with California is reading that post (probably not many in a forum of programmers): no, just no.

I lived in CA for 10 years... which was about 5 years too long

Is that mostly Southern California? I thought you grew up in Cali - you moved there in your 20's or so? What brought you there? Where else have you lived?

Been all over the country as well. I didn't like New Orleans the city, but I thought the people were just wonderful, so I was always drawn to come back to the South. Plus I met so many people from Texas in LA and was just like "these are the coolest people ever."

I think of Austin as a collision of old-town and new-town, but it somehow works. I like how they have managed to keep nature so well mixed together.

how is this even possible?

Went somewhere other than Parkway for po boys.

Yeah, mostly southern. I was in San Deigo and Los Angeles. Sort of bounced away from LA and then went back a few times. Considered moving to SF until I visited. To be fair to the SF denizens, Richmond is a wonderful area -- I'd never be able to get sick of the ocean walk from the golf course to Sutra Baths.

I was born in Cleveland Ohio, but moved about once a year, and grew up around eastern Ohio and western Pennsylvania, ultimately finishing my education in Cleveland. I sort of wish I grew up somewhere else, but since I was on my own at 18, I think it worked out much better for me that I was there, as I was able to fade the rent on my minimum wage job. (This is another big reason I wouldn't raise a kid in California. If something happens to you...)

I also lived in Miami, Key West, New Orleans, and Las Vegas. Of course, I have a different perspective on each place since my priorities in life were very different in those days than they are now. I made a bunch of week-to-month stops to a bunch of other cities, like Orlando, Tampa, Atlanta, Louisville, Mobile, etc, basically seeing if these were areas I'd like to plant some flimsy roots and try for some work in a pizza shop or whatever. Spent a bit of time in Mexico and considered moving to Tijuana, but after seeing what it'd be like to live there, took a pass on that. Mostly financed my moving being a street performer, doing odd jobs, and cleaning in hostels.

I have no idea what brought me to California. I just wanted to see as much of the US and the world as I could at the time. I saw the Atlantic Ocean and wanted to see the Pacific Ocean. I decided that I liked LA and planned to stick around a bit. I settled in LA at about 27, but that was after first showing up around 25 and leaving. Basically got rolled to the bones, but can't say I didn't manage to experience many things there that I otherwise would not be able to experience and do anywhere else. Never did the surfing thing, sorry.

Ironically, the more you do things that aren't city-like in SF and LA, the more likely you are to find interesting situations and people. The hiking is truly incredible. The small speakeasy's, if you can find them, are really cool. You can even figure out how to be a fire performer at Burning Man and learn how to do trapeze.

I think one thing that fell apart in LA is the live music scene. All the bars started charging to play and all the talent went elsewhere, and it fell into amateur hour pretty quickly. Of course, Beats Antique is from Oakland.

Before or after Katrina? Back then, it was well-known to be extremely violent and dangerous. I heard more stories about crazy robberies and other crap from NO than any other city.

I got my bike stolen there. Saw too many fights. Saw too many police beatings on the French Quarter.

The mosquito and other wtf bug bites left scars that didn't heal for about 5 years. The heat, the humidity.

As I said, loved the people. Probably fell in love once a day there.

Dave, ultimately people need to find a place that fits them well, and I don't doubt that California is a poor fit for you for various reasons, but you need to be able to sort out which of those reasons are personal to you and which of those are actually reflective of the place.

You seem to have a hypersensitivity to being disconnected from people, so things like people using phones, people not making eye contact and smiling on the street, people not striking up random conversations particularly bother you. For millions of people, these things don't bother them that much, and they can exchange small talk with baristas without incident or find someone in a city of 5 million to date.

I grew up 10 miles from SF and contrary to the vision you laid out for kids who grow up here (granted, there weren't smartphones then, but you seem to think they're just a manifestation of the problem and not the cause of it), I had a normal childhood and played with other kids and ran and biked around the neighborhood without helicoptering parents and then went off to school and learned things and became a pretty successful person.

Also, you brought up politics yet you moved to a state that's still fighting the good fight through legislation to make sure women are forced to have your kid and to deny voting rights to minorities. I wouldn't want my kids growing up in an environment promoting those views of women or other races, but that's the point, that's just me and my personal reasons.

Good summary.

Just want to chime in that New Orleans is awesome. I would move there, but it would shorten my life considerably.