5 ms·

Redundancy across providers. BigQuery storage costs very little. It was implemented first and we introduced Redshift later to deal with data issues we didn't wa

Redundancy across providers. BigQuery storage costs very little. It was implemented first and we introduced Redshift later to deal with data issues we didn't want to handle long-term in ETL. It didn't make sense to kill off the bigquery integration and I've decided for $20/mo or so that the backup storage on a separate provider is worth it. Redshift and BigQuery are both solid services. And BigQuery has gotten better with time. But I still have some reservations with it as a pure Redshift replacement.

scrollaway 9y ago
Thanks for the details! Could I ask you about your query patterns and the kind of data loads you're pushing into those?
- sturgill 9y ago
  Most of our use case is storing raw event data: user clicked on X; interacted with feature Y; was bucketed into test group Z; audit logs for subscription data points (joined, upgraded, downgraded, paid, payment denied, etc). Basically everything we care about, but that doesn't matter as a core concern of providing the service. The main app needs to know if a user has access to entitlement A, but the history of that process is outside the concern of the app itself. We throw everything into BQ/Redshift. Then when we find a need for it, we bring it into a separate reporting dataset in Redshift via ETL. The nice part about this is you can join your sanitized ETL data against raw event data if you need to run a once off query like "for everyone put into variant A of experiment B, how many converted from a free trial to a paid subscription?" I haven't found a great way to programmatically recreate ETL data into BQ without recreating a new dataset every day (because of the history of immutable data). It's not that it's impossible, it's just not as great (imho) as an upsert type workflow. Occasionally we'll have a once off query we access by hand, but that's relatively rare at this point. Jobs run around 1am and everything is ready to go when the team gets in in the morning. You have the odd job where you have to manage distkeys, and that's rarely fun, but it's a trade off we're comfortable making. And, while I'm pimping other people's services, Redshift + Looker is incredible.

scrollaway 9y ago

Thanks for the details! Could I ask you about your query patterns and the kind of data loads you're pushing into those?

sturgill 9y ago
Most of our use case is storing raw event data: user clicked on X; interacted with feature Y; was bucketed into test group Z; audit logs for subscription data points (joined, upgraded, downgraded, paid, payment denied, etc). Basically everything we care about, but that doesn't matter as a core concern of providing the service. The main app needs to know if a user has access to entitlement A, but the history of that process is outside the concern of the app itself. We throw everything into BQ/Redshift. Then when we find a need for it, we bring it into a separate reporting dataset in Redshift via ETL. The nice part about this is you can join your sanitized ETL data against raw event data if you need to run a once off query like "for everyone put into variant A of experiment B, how many converted from a free trial to a paid subscription?" I haven't found a great way to programmatically recreate ETL data into BQ without recreating a new dataset every day (because of the history of immutable data). It's not that it's impossible, it's just not as great (imho) as an upsert type workflow. Occasionally we'll have a once off query we access by hand, but that's relatively rare at this point. Jobs run around 1am and everything is ready to go when the team gets in in the morning. You have the odd job where you have to manage distkeys, and that's rarely fun, but it's a trade off we're comfortable making. And, while I'm pimping other people's services, Redshift + Looker is incredible.