I know Meltano suggests we use Postgres as the sys...
# best-practicess
sterling_paramore
08/31/2022, 5:43 PMI know Meltano suggests we use Postgres as the system database in production. I was wondering if anyone had tried using Snowflake and if there are any gotchas I should be aware of before attempting this?
t
taylor
08/31/2022, 5:48 PMI don’t know if anyone has tried that! We do support MSSQL thanks to a community contribution and we’re considering MySQL as well. https://docs.meltano.com/concepts/project#support-for-other-database-types
s
sterling_paramore
08/31/2022, 5:52 PMI’m mostly trying to get away from having to set up and manage another database
sterling_paramore
08/31/2022, 5:53 PMI already have Snowflake set up and configured and hoped I could use that. Otherwise, I’m going to have to involve my DevOps team to set up dev and prod cloud resources.
Looks like Snowflake doesn’t work right now.
a
aaronsteers
08/31/2022, 6:58 PMUsing the 'vanilla' snowflake would not be advised because of the way Snowflake handles updates (it doesn't) and the fact that it isn't really designed to be an OLTP system.
aaronsteers
08/31/2022, 6:58 PMI heard they're working on some new OLTP-friendly backends though. I don't know status or timing on that, but someone else might be able to chime in if they know.
aaronsteers
08/31/2022, 8:12 PMrelated to: Snowflake OLTP
aaronsteers
08/31/2022, 8:13 PMThe most recent comment there from Snowflake themselves addresses this:
Regarding my (soon-to-be-obsolete) statement 4 years ago:
"Snowflake is NOT an appropriate platform for OLTP workloads. No question about that."
I am delighted to report that with the introduction of Hybrid Tables (to be formally announced at our Snowflake Summit in a couple of weeks), Snowflake will be able to handle OLTP workloads.
aaronsteers
08/31/2022, 8:13 PMSo, perhaps the answer is becoming "yes, when using the new Hybrid Tables feature"?
s
sterling_paramore
08/31/2022, 8:36 PMI get that snowflake is not meant to be an OLTP database, but isn’t that only for performance reasons? The number of updates that meltano needs to do in the database seem pretty minimal (based on what I’ve seen investigating the sqlite db), so I wouldn’t expect performance to be much a concern here.
When you say it doesn’t handle updates, what do you mean? It’s ACID compliant as far as I understand.
sterling_paramore
08/31/2022, 8:39 PMPeople build these kinds of cool transactional applications on top of Snowflake
c
christoph
08/31/2022, 9:51 PMa
aaronsteers
08/31/2022, 11:38 PMI get that snowflake is not meant to be an OLTP database, but isn’t that only for performance reasons?My answer was that it "would not be advised". But that doesn't mean it can't work. The gap would likely be in alembic/sqlalchemy compatibility for the way in which Meltano calls those libraries. In theory, it could work - and to my other point, I think now that this feature released, there won't even be the same "it's not OLTP" objection.
aaronsteers
08/31/2022, 11:40 PMIf there are tweaks we can make on our side to improve compatibility, I do think we would accept a PR to add snowflake support - very similar to how we recently added MS SQL support.
aaronsteers
08/31/2022, 11:42 PMOn looking more closely, it looks like that Snowflake feature hasn't launched yet officially but there's a signup here and when that releases, in theory it would unblock the OLTP use case.
5 Views