scapecast

One of his great talks at Stanford: “Target Big Markets” https://youtu.be/nKN-abRJMEw

I much agree with this response. The term that I've heard to summarize all of the above is "semantic layer". That layer will run in the warehouse, vs. some external dashboard tool.

Redshift is very predictable in terms of cost because of their pricing model. But the bottleneck becomes query performance. When you run a lot of concurrent queries, you may experience queue wait time. The recent launch of Concurrency Scaling mitigates that a fair bit though.

For Snowflake, it's the other way around. Because Snowflake separates compute and storage out of the box, Snowflake will spin up more resources to handle the peak load in queries. So you'll have to watch your $$$.

Snowflake comes with less knobs to turn ("zero admin"), and that's a convenience we've seen lots of data engineers appreciate. In short, you're trading convenience for cost predictability.

We currently support Amazon Redshift with intermix.io for performance monitoring. We are looking to build out support for Snowflake for query and cost monitoring. If there's anybody on this thread who is open for an exploratory call about Snowflake, please shoot me a note at lars at intermix dot io.

can you elaborate on the process of copying data into Redshift? How often do you run COPY commands? On a continuous basis? Asking since Redshift as an OLAP isn't really optimized for heavy writes, so if you've figured out a way to do it, please bottle that magic!

I'm the guy who wrote the reply that pops up first when you read that Quora thread.

Here's the short story (and I know all of this because the guy who invented the core engine for ParAccel's MPP columnar tech, that is the foundation for Redshift, is one of our early advisors).

- ParAccel developed the tech for a columnar storage database. I believe it was called "matrix"

- Amazon AWS bought the source code from ParAccel, limited for use as a cloud service, i.e. they couldn't create another on-premise version that would compete with ParAccel

- ParAccel then sold to Actian, and a few years ago Actian shelved the product as clearly the on-premise world had lost to cloud warehouses.

The reason AWS bought the source code was time-to-market. It would have taken too long to build a product from scratch, and customers were asking for a cloud warehouse. Back then, ParAccel had by far the best and fastest MPP / columnar tech, plus it's very attractive since it's based on Postgres.

So Actian and Amazon AWS essentially had the same tech, just different distribution models. One is on-premise (Actian), the other one a managed cloud service (AWS). We all know who won.

there's very interesting paper by the Amazon RDS team (where Redshift rolls up). It's not only about "faster, better, cheaper" - it really is about simplicity and that's what Redshift delivered on.

https://event.cwi.nl/lsde/papers/p1917-gupta.pdf

Spin up a cluster in less than 5 minutes and get results within 15 min. Keep in mind, this was all in late 2012, so what appears "normal" today was pure magic back then.

but ever since the "fork", i.e. when AWS purchased a snapshot in time of the code base, the products have obviously diverged. There's some 8 years of development now in Amazon Redshift.

What is it that makes you want to dump Redshift? Sounds like there's a specific frustration?

Co-founder here of intermix.io

We've compiled a write-up of the performance tuning techniques we've used in the past two years to get the most out of our own Redshift environment.

It covers the major pain points that people have when starting with Redshift and when scaling their workloads. By using the techniques, a lot of our customers stayed on Redshift vs. doing the switch to e.g. BiqQuery or Snowflake.

Would love to get opinions on this thread if you've successfully used any of the approaches we describe.

Lars here, the guy who gets the honorable mention at the end of the post "for brainstorming Redshift performance" with Austin (the author of the post) :-)

If you care to dig a little deeper into the things we discussed, we've written them up in a longer blog post:

https://www.intermix.io/blog/top-14-performance-tuning-techn...

So first off congratulations to creating a business that makes customers pay $200K per year. That's amazing. There are companies out there that raise many $M, hire a bunch of engineers, and still don't get there.

I may be able to add a perspective here since I've worked on both sides of the table, in Corp Dev (before) and as a founder (today).

If you want to find an exit for your company, then it's "just" another sales process. That means you need to define your process, set a timeline, and create urgency among potential buyers. That means filling your pipeline with lots of prospects. In a way, it's like fundraising - you're trying to sell (a part of) your company.

Key is that you have a large top of the funnel - more people will say "no" than "yes". And for the ones that say "yes", you want to make sure that you have leverage, i.e. more than one offer.

Here's the rough process that I would pursue if I were in your shoes:

==========

- create a long list of companies that may be a good fit / home for your product (20+ companies)

- find their CEOs and their emails (e.g. via a freelancer on Upwork)

- get a warm intro (best), otherwise reach out cold

- prepare a general pitch (market changes, need for your product, etc.)

- do you homework to understand how your product would fit into their portfolio

==============

The first contact should only be about the potential opportunity - "xyz thought what I'm doing may be a good fit for your company. If I sent you a short deck, would you spend a few minutes looking at it?" ----> if yes, send and propose a 30 min chat

Do this for as many good potential buyers as possible, all within a few days. Schedule the coffee / call for the week after. Prepare that deck that you can send if somebody is open to talk / meet.

in the deck:

=============

- describe a big change in the market that has created a need for YOUR product

- make sure you nail the "why now?"

- explain your product's secret sauce and why it's unique

- show your traction, customer logos

- explain your economics (e.g. "ASP $18K / year")

- show what the business can look like if you join forces

- put a price on it

============

Keep this deck small. 6-8 pages max.

Make sure you communicate that you're looking to find a buyer within the next 6-8 weeks (urgency), and that you're talking to a few good companies (fear of missing out).

If you feel there's a fit, and the other side believes that too, work with the CEO to identify & involve the stakeholders on their end (e.g. VP of Product, VP of Engineering). Make sure that happens as a follow-up within the next week of your chat / coffee with the CEO.

If they all decide it's a fit - agree on the process and timeline it will take to close. They'll need to get approval from their board, etc. to get this through. So much can go wrong here. People go on vacation. There's an important release. The quarter is ending, and they missed their quarter. That's why you need many potential parties in your deal.

So:

first contact --> call / meeting --> get ok from stakeholders --> come to decision "is this a fit"? --> agree on closing steps

Reg. the price - there are few well-known small PE buyers out there that will pay your 1x revenue regardless of what your business is. Take that as the bottom of the valuation. To get something that's higher - you'll need competing parties for the deal.

But I doubt that anybody will buy you for the revenue. $200K is too little for any company to matter, regardless of small / big. So the important part is to communicate the potential, and your value-add as an individual contributor for say the next 12-24 months. But you don't know until you try and ask :-)

Hope this helps. and then look at https://www.intermix.io to and let me know if we're a potential fit :-)

The post describes how you're using an OLAP database like Amazon Redshift in an OLTP use case. The magic is done via using dblink and Amazon RDS. Why would you do that? You can build an analytics app and productize it.

AWS acquired the underlying tech for Redshift from ParAccel. The former ParAccel CTO is actually on our team.

I wrote a long answer on Quora on that topic:

https://www.quora.com/Amazon-redshift-uses-Actians-ParaAccel...

For background, our customers use Redshift, and we are also a heavy Redshift user for our own back-end. The ones that have done a bake-off with BigQuery (and Snowflake too, btw) come all back with the conclusion "too expensive". And if you're the BigQuery PM, I'd love to talk to you because we're planning to build for BigQuery what we've built for Amazon Redshift. lars at intermix dot io

I think the difference here is in the use case, and I think it boils down to "ETL" vs. "ELT". If you do ETL, and your analyst team runs a few ad-hoc queries, then the pay-per-query approach makes a lot of sense. It would agree that it's "ultra-efficient".

If you do "ELT", where you ingest fairly raw data on a continuous basis, and then run complex transformations within your data warehouse, then the "buy a cluster" pricing will win. The data loads (we've seen load frequency up to every 2 minutes) require resources, and then so do the transformations.

===================

side note:

When it comes to utilization, I always have to think of this great research paper by Andrew Odlyzko:

http://www.dtc.umn.edu/~odlyzko/doc/network.utilization.pdf

"Data networks are lightly utilized, and will stay that way"

===============================

Agreed that most warehouses sit at 30% utilization. The customers we work with have more utilization than 30% though. That's because they're continuously ingesting data, transforming it within Redshift, and then lots of ad-hoc queries by analyst teams and data services that feed other applications. If you have a business that runs on a two or more continents, then you don't even have downtime during US nighttime, as Europe, Asia, etc. are running. And so in those cases, we've seen that customers don't want any surprises and would rather pay for the cluster. Predictability becomes more important than paying the lowest amount possible per query.

---------

I'm a co-founder at https://www.intermix.io - we provide monitoring for data infrastructure

what scandal are you referring to?

dashboard tools like Chartio and Looker make self-service analytics "easy" so you don't need to write 10 different flavors of the same query. We see this happening every day - our biggest customer has 1,200 analysts on Looker.

The key is how you set up your data warehouse, e.g. Amazon Redshift. What we see happening that data engineering teams provide "data services" to their company, via a set of common schemas / tables.

At the very high level:

- set up two different schemas. A (1) raw schema in which you dump all your event level data. only data engineers are allowed to access that schema. and then (2) an ad-hoc schema that analysts can use to run their queries.

- You move data from the raw to the ad-hoc schema with scheduled transformations / aggregations. Airflow, Luigi, Pinball, dbt are popular tools for that purpose. The tables in the ad-hoc schema need to be well documented so analysts can understand what data is available.

- Give every analysts a dashboard seat and access to the ad-hoc schema. Give them access to "SQL playbooks" that analysts can re-use. If you're adventurous, allow them to create their own tables.

This approach scales from a few GBs to TBs and more.

I think I can answer that, given we're in the business of Redshift performance.

Redshift is incredibly powerful, but can be a pain to work with if you're a data engineer. To get the most out of it, you need to turn a lot of knobs - and knowing how to tune it is really complex. But if you get it right - it blows everything else out of the water. Including Snowflake and BigQuery.

The key to success is using Redshift's Workload Management, and using best practices for your schema, table and query design. Many people who start using Redshift come from a mySQL world and are not familiar with a columnar storage db. Sort / dist keys are not concepts that people are necessarily familiar with.

cost for both DynamoDB and Amazon Redshift can both get out of hands real quickly. I've seen it first hand. With Redshift, the problem is that data teams do not adjust the default configuration of 5 query slots. So when things slow down, instead of increasing concurrency for a given node, they add more nodes. I've seen monthly invoices of $100K+ for a workload that could have been done with less than a $2,000 / month.

What’s the PIA you’re referring to?

If you are a start-up, I second the approach of dumping logs into Redshift. Lots of start-ups are doing exactly that (including yours truly).

Few things to keep in mind when you do that:

Create separate schemas for your raw data and your analysis. Dump the logs into a "raw schema", run your aggregations, and write the results to a "data schema". Once your data grows and you're running more reports, it will make your life much easier.

Separate your users from the start. Create a user each for your data loads, for your aggregations and for your ad-hoce analysis. As you ramp up query volume, the separation of concerns will make it easier to use the Redshift workload manager and keep concurrency high.

Ping me if you have more questions about the set-up. lars @ intermix dot io

Yeah, true. Even though Redshift is based on Postgres, it does not leverage the automatic vacuuming facility of postgres.

So the vacuuming of tables to recover disk space from deleted rows needs to be done manually.

There are 3 types of vacuuming in Redshift (DELETE ONLY, SORT, REINDEX). The vacuum SORT operation is done on tables that have a sort key. To the extent that a vacuum SORT is an expensive (high IO) operation, we recommend when possible, to avoid the need to vacuum by loading the rows in sort order. If you do that, you will not need to vacuum the table, and this is the optimal solution for very long tables.

When dealing with a very long table (>10b rows) that has a sort key, it’s useful to partition the table into multiple tables where the table names are modified to indicate the partition. This is useful when pruning the size of the table, you don’t have to run VACUUM DELETE ONLY.

The key is to vacuum on a regular basis, and keep your stats off below 10%. If you wait too long, you might end up in a place where you have to resize your cluster or do a deep copy.

One more thing: Run your vacuum jobs in the queue with the most memory.

I can confirm that Redshift is based on Postgres. And I know that straight from the source: Dave Steinhoff, one of the founders of ParAccel, is on our team.

https://www.intermix.io/about-us/

Matt, thanks - can you elaborate on your hack of the airflow script? What type of events do you set alerts on?

I actually have no doubt that the benchmarks are correct. However, what they are probably doing is using the Amazon Redshift default configuration.

For Redshift to be fast, you need to use a feature called "workload management". Redshift operates in a queuing model, and the default option is one queue with five concurrent queries.

If you throw an Oracle workload at Redshift WITHOUT configuring it, then yes, Oracle will be 15 times faster because Redshift is only running the default concurrency of 5 queries.

However, if you do configure Redshift for your workloads, to allow for higher concurrency, Redshift will blow Oracle out of the water, at 1/10 of the cost.

I know this because at our own company we're running multiple large Redshift instances. And then also our own customers use Redshift, and once we've helpd them fine-tune their cluster, they've never looked back.

But I have to give it to Larry - he's an excellent marketer...

------ I'm the co-founder of https://www.intermix.io/ - we help data engineers who use Amazon Redshift to be more productive with data

happy to help, and no rush.

Lars here, co-founder of a start-up that specializes in optimizing Amazon Redshift performance. We work with a lot of Fivetran customers who we help maximize their load speeds.

My hunch is that your data science team can solve both issues by using the Workload Management (WLM) feature in Redshift.

Redshift operates in a queueing model. As a user, you can define queues for your different workloads, and then assign them a specific concurrency / memory configuration. The default configuration for Redshift is one queue with a concurrency of 5. If you run more than 5 concurrent queries, then your queries wait in the queue. That's when the "takes too long" goes into effect.

The available amount of memory is distributed evenly across each concurrency slot. Say that you have a total of 1GB, then with a default configuration, each of the 5 concurrency slot gets 200MB memory. If you run a query that needs more than 200MB, then it falls back to disk. Disk-based queries are really bad for the cluster, because they consume a lot of I/O which slows down the entire cluster, not just a specific queue.

You can fix both by configuring Redshift specific to your workloads. As a general set-up, we recommend defining 4 queues (load, transform, ad_hoc, catch_all) to isolate your workloads from each other.

-------------

1) data loading takes too long

Create a dedicated "load" queue in the WLM that is only responsible for loading data into Redshift. These are all COPY statements. By separating your data loads from everything else, you make sure that your data loads are protected from e.g. some big ad-hoc queries that some not-so-skilled SQL user writes. The next trick is to figure out how many concurrent slots you need for all your loads.

2) queries frequently overflow to disk

I assume this is true for some large aggregations / roll-ups you're running, or some massive ad-hoc queries. By giving them their own queue, with sufficient memory, you start eliminating the volume of disk-based queries.

--------

The problem is that it's not straightforward to figure out how to set the right concurrent / memory configuration. The information is hidden in the log files (which Redshift deletes on a rolling basis), and sifting through those log files requires writing really complex queries. That just put more load on the cluster...

However, if you do get it right, you can run blazing fast data loads and queries.

We struggled with this problem ourselves at our previous company, and so with https://www.intermix.io we built a service to solve that problem. If you ping me at lars at intermix dot io - I can give you an extended free trial.

I'll pay you dinner (in SF, or maybe in Vegas at Reinvent), if we can't solve both issues for you. How is that?

three worthwhile reads on that topic:

- https://blog.fishtownanalytics.com/what-are-the-steps-tools-...

- http://blog.clearbit.com/enterprise-grade-analytics-for-star...

- https://www.simple.com/engineering/building-analytics-at-sim...

Three choices for your data warehouse:

- Amazon Redshift

- Google BigQuery

- Snowflake

Redshift is by far the dominating product in this market. I'd say they have around 60-70% market share. It also has the richest ecosystem of ETL and BI tools. We saw Google BigQuery coming in aggressively earlier this year, but now I'd say the major competitor that keeps coming up is Snowflake. I hear people are happy with Snwoflake and BigQuery. The major FUD you'll hear Google and Snowflake spread about Redshift is that it's (1) expensive because compute & storage are bundled and (2) that it doesn't scale. Both statements are false.

Then you also have a choice of fully managed offerings like:

- holistics.io

- panoply.io

I only learned about holistics.io in this thread. I have to say that I really like their value proposition and features.

For your ETL - I have a whole spreadsheet full of tools that I'm happy to share with you. You will have two major use cases, one to get your production data into your dw, another one to unify it with all your SaaS data.

with intermix.io we've built a performance analytics service for Amazon Redshift. And so we see all the different ETL tools that companies use. The ones we see used most frequently are:

- Fivetran

- Stich Data

- Segment

- Alooma

All are very good products and I can only recommend using them.

Hope that helps!

I will print and frame your last sentence.

Amazon built Redshift by using technology from Paraccel. Paraccel was built on Postgres.

See a Quora answer I wrote:

https://www.quora.com/Amazon-redshift-uses-Actians-ParaAccel...

For a small dataset like that, Redshift is overkill. Especially if it's a one time thing. Redshift excels when you have (at least) TBs of data from many different sources.

Having said that - if you expect to query your data on an ongoing basis, with fresh (and growing) data coming in every day, then it's worth considering. You can run your transformations on top of raw data in Redshift. It's certainly more expensive than S3. But with just a few GBs, you'll stay underneath $150 / months.

Reg. Spark vs. Redshift - see my post on Quora:

https://www.quora.com/Spark-vs-Redshift-Should-I-be-using-bo...

most companies just use

https://docs.companyname.com

But then you have companies like Slack, which picked a whole new top level domain.

https://get.slack.help/

What's the benefit of going with the .help domain?

so I hear a few things from you, also based on your comments in the thread so far. You're using Redshift as your DW, and you want to:

- find a (cheap) alternative to Tableau for data viz

- allow basic self-service analytics for your team

- embed charts into applications

- provide a professional growth platform for data engineers

It sounds a bit like you're trying to build what the folks at Clearbit covered in a blog post:

http://blog.clearbit.com/enterprise-grade-analytics-for-star...

Some suggestions (a few of these tools have been mentioned already):

Data viz:

- Metabase

- AirBnB's Superset http://airbnb.io/projects/superset/

those two are open source products; not that the PM on superset used to work at Tableau... just sayin'...

for data viz & embedding charts:

- Mode Analytics

- Looker

- Periscope Data

- grow.com

- reflect.io (highly recommended, built just for that purpose, but I think they want $60K / year too...)

shoot me a note at lars at intermix dot io - we have a spreadsheet with all the data viz tools out there, a list of some 40+ tools....