Stories

Comment by DeGi

Comment

Comment by DeGi

We have ~80TB of (compressed) data in Snowflake at Celtra and I'm working with Snowflake on a daily basis. We've been using it for the last ~1 year in production. Overall maintenance is minimal and the product is very stable.

Pros:

  - Support for semi-structured nested data (think json, avro, parquet) and querying this in-database with
  custom operators
  - Separation of compute from storage. Since S3 is used for storage, you can just spawn as many compute
  clusters as needed - no congestion for resources.
  - CLONE capability. Basically, Snowflake allows you to do a zero-copy CLONE, which copies just the metadata,
  but not the actual data (you can clone a whole database, a particular schema or a particular table). This is
  particularly useful for QA scenarios, because you don't need to retain/backup/copy over a large table - you
  just CLONE and can run some ALTERs on the clone of the data. Truth be told, there are some privilege bugs
  there, but I've already reported those and Snowflake is working on them.
  - Support for UDFs and Javascript UDFs. We've had to do a full ~80TB table rewrite and being able to do this
  without copying data outside of Snowflake was a massive gain.
  - Pricing model. We did not like query-based model of BigQuery a lot, because it's harder to control the costs.
  Storage on Snowflake costs the same as S3 ($27/TB compressed), BigQuery charges for scans of uncompressed data.
  - Database-level atomicity and transactions (instead of table-level on BigQuery)
  - Seamless S3 integration. With BigQuery, we'd have to copy all data over to GCS first.
  - JDBC/ODBC connectivity. At the time we were evaluating Snowflake vs. BigQuery (1.5 years ago, BigQuery didn't
  support JDBC)
  - You can define separate ACLs for storage and compute
  - Snowflake was faster when the data size scanned was smaller (GBs)
  - Concurrent DML (insert into the same table from multiple processes - locking happens on a partition level)
  - Vendor support
  - ADD COLUMN, DROP COLUMN, RENAME all work as you would expect from a columnar database
  - Some cool in-database analytics functions, like HyperLogLog objects (that are aggregatable)
Cons:

  - Nested data is not first-class. It's supported by semi-structured VARIANT data type, but there is no schema
  if you use this. So you can't have nested data + define a schema both at the same time, you have to pick just
  one.
  - Snowflake uses a proprietary data storage format and you can't access data directly (even though it sits on
  S3). For example when using Snowflake-Spark connector, there is a lot of copying of data going on: S3 ->
  Snowflake -> S3 -> Spark cluster, instead of just S3 -> Spark cluster.
  - BigQuery was faster for full table scans (TBs)
  - Does not release locks if connection drops. It's pain to handle that yourself, especially if you can't
  control the clients which are killed.
  - No indexes. Also no materialized views. Snowflake allows you to define Clustering keys, which will retain
  sort order (not global!), but it has certain bugs and we've not been using it seriously yet. Particularly,
  it doesn't seem to be suited for small tables, or tables with frequent small inserts, as it doesn't do file
  compaction (number of files just grows, which hits performance).
  - Which brings me to the next point. If your use case is more streaming in nature (more frequent inserts, but
  smaller ones), I don't think Snowflake would handle this well. For one use case, we're inserting every minute,
  and we're having problems with number of files. For another use case, we're ingesting once per hour, and this
  works okay.
Some (non-obvious) limitations:

  - 50 concurrent queries/user
  - 150 concurrent queries/account
  - streaming use cases (look above)
  - 1s connection times on ODBC driver (JDBC seems to be better)
If you decide for Snowflake or have some more questions, I can help with more specific questions/use cases.
Replies DeGi · 2017-03-21
Open on HN
Loading the discussion…

Domain filters

Stories from these domains are hidden from every list. Subdomains match too: blocking substack.com also hides danluu.substack.com.

    New collection

    Delete this collection?

    About YAVCHN

    YAVCHN is a reader for Hacker News and Lobsters, with articles and discussions in separate windows or Classic pages.

    Created by Paul Parks and built with PUDL.

    YAVCHN source code on GitHub

    Privacy policy · Terms of use

    Help

    Keyboard

    j / k
    Move down and up the story list. The arrow keys scroll whatever has focus.
    Enter
    Read the marked story in the article reader.
    ]
    Read the next story in the same article-reader applet. Back returns to the previous story.
    p
    Pin or unpin the marked story, which keeps it in Pinned.
    n / N
    Move to the next or previous top-level comment in the window in front.
    c
    Collapse or expand that comment.
    f
    Hide or show the story list.
    Esc
    Close a menu or this help.
    Access key m
    Go to the menu bar. Most browsers take it with Alt on Windows and Linux, and Safari with Control and Option.
    ?
    Show this help.

    Windows

    Each story opens in a window holding its article above its discussion; drag the bar between them to share the room differently. A window can be moved by its title bar, resized from any edge, snapped to a half or a corner by dragging it there, maximised, or minimised to the bar at the foot of the page. Use Window > New reader window to open an empty reader, or Story > Open in new reader window to open another reader for the current article. Docked readers keep their articles when you select another story from the sidebar. Minimized readers can be restored and reused for their site. A window's Next story link reads on down the list in the same window.

    A link in a comment or an article to another Hacker News or Lobsters thread opens that thread in a window too. A link to a single HN comment opens the comment above its replies.

    While a story's window is in front, the Story and Discussion menus in the menu bar hold its commands: pinning, Next story, sorting, collapsing every thread, jumping to the first new comment. Each window also remembers where you were in its article and discussion, so a reload, or Back to a story that Next took you past, finds your place again. Closing a window forgets it.

    The whole arrangement lives in the address, so a bookmark or a shared link brings it back, and Back undoes the last change. Moving between Hacker News, Lobsters, their lists, Pinned and Find changes only the list, and leaves the windows open.

    The list

    The pin at the start of a row keeps the story in Pinned, and the cross at its end hides it. Pinned can be narrowed by words in the title, site or author, by source, and to the stories you haven't opened yet, and ordered by when you pinned them, by points or by comments; the filters are part of the address, so a filtered view can be bookmarked. Scroll past the end of the list to load more. Domain filters, in the View menu, hide every story from a site.

    Collections are named lists of stories. Story > Add to collection files the story in front into one or more of them, and the Collections feed shows them all or one at a time; the menu that chooses collections also creates, renames, and deletes them. A note is your own text on a story. Choose Add note in a story's toolbar to write one; it saves as you type. Rows with a note carry the note mark, and the Notes feed lists every noted story and searches the text of your notes.

    Browsing view

    View > Windowed and View > Classic select the browsing view and save your default in this browser. Window view reuses a reader for each feed. Classic view opens stories and applets as pages. Open as a page is a one-off action that does not change your saved default. Use the Windowed selector to return an article to a window. Direct page links always open as pages.

    Applets

    The Applets menu in the menu bar holds three tools, each a window of its own. Replies to me takes your Hacker News user name and lists the replies to your last thirty comments and stories, checking again every three minutes while it is open, and marking what is new since you last marked them read. Look up a user opens a profile on Hacker News or Lobsters, with their submissions and recent comments, as a commenter's name in any discussion does; the bar at the top of a profile looks up someone else in the same window, and Back returns to the one before. Who is hiring? filters the posts of HN's monthly hiring threads by the words you type.

    They read only what the sites publish to everyone, so none of them asks for a login, and your user name stays in this browser anonymously and is included in retained applet state when signed in.

    Find

    Find takes any link and lists every time it was submitted to Hacker News and Lobsters, so you can read each discussion of it.

    About

    YAVCHN never sees your Hacker News or Lobsters login. The discussion is fetched from each site's public API; to vote or reply, follow the link above the discussion, or the arrow beside a comment, to the source's own site. Without a YAVCHN account, your data stays in this browser. When signed in, pins, collections, notes, blocked domains, and retained reading state are stored with your account and synchronized across devices. Hidden stories and layout stay in this browser. The privacy policy has the details.

    Open source: github.com/paulmooreparks/yavchn. Built with PUDL.