0

I have an advanced background in SQL (Postgres,Presto,MySQL,etc...) but am trying to teach myself technologies like BigQuery for a job and want to gauge my preparedness. My question is simply when querying in BigQuery or Snowflake do you write in the same syntax as traditional SQL or is it an entirely different language?

Griffin Weinhold
  • 9
  • 1
  • The SQL is essentially the same, but there are definitely differences to using it effectively -- from the lack of indexes to using arrays, from partitioning tables to understanding the timestamp logic. – Gordon Linoff Jul 13 '21 at 16:17

3 Answers3

2

"My question is simply when querying in BigQuery or Snowflake do you write in the same syntax as traditional SQL or is it an entirely different language?"

Queries

Snowflake supports standard SQL, including a subset of ANSI SQL:1999 and the SQL:2003 analytic extensions. Snowflake also supports common variations for a number of commands where those variations do not conflict with each other.

In general Snowflake SQL is just SQL and it is compliant with ANSI SQL. But to truly utilize its power it will require investing time and understanding concepts like micropartitions/virtual warehouses/TASKS/STREAMS/Time Travel/Data Sharing/Java(Script) UDFs/Stages to name a few.

It also does not support B-Tree indexes, constraints except NOT NULL are not enforced and there are other minor differences.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
1

Adding to the technical differences Lukasz clearly described above I also think there's a huge change to the data modelling approach. Although the SQL interface remains ANSI standard there's quite a big difference behind the covers.

Hybrid columnar databases solve performance where traditional DBMS's heavily leaned on Edgar Codd's FK/PK technique outlined in A relational Model for Large Shared Databanks.

The problem is there's a generation of BI/DW professionals wed to techniques forged around the PK/FK ... namely Kimball/Inmon star schema's and the likes.

Columnar Databases with semi structured (Nesting in BigQuery and Arrays/Variants in Snowflake) give us a magical time in this space to design new models/approaches far superior to that of the past.

Tables with 2-3K columns won't make many cloud based DBMS's blink while also setting us up simpler pathways to Data Science algorithms. Nesting allows us to build tables where our consumers need only write 'Select * from table' to gain access to in-depth models. No need to join and predicates are simple. The only thing I think letting the side down in the Front End Toolsets ... with no native support for Arrays/Variants/Nesting they reduce the options to just Data Studio, Snowsight, Google Sheets and I thought Looker but not 100% sure.

Adrian White
  • 1,720
  • 12
  • 14
  • Okay interesting, so doesn’t follow the traditional relation database structure on the backend in regards to clustering on keys etc? But end all be all the syntax is akin to SQL just more optimizable? – Griffin Weinhold Jul 14 '21 at 06:06
  • @GriffinWeinhold Snowflake does support clustering but they recommend to do so only on large tables >1TB. The main difference is the lack of enforcing of primary keys (although you can define one) and no indices. Largely it is ANSI SQL based but to utilise the full power, you should look into all the other features that the other users have mentioned – Dean Flinter Jul 14 '21 at 08:37
  • @GriffinWeinhold I’d not throw out the baby with the bath water … rather solve the actual problem in the best possible way. Sometimes the traditional star schema isn’t the best way. In general I’ve found I/O (any joins) to be worse than larger tables. Remember each column is it’s own file here. Nesting/Arrays saves circa 40% disk space in large wide tables. Do what’s best for ur users in modular patterns that can be reused easily. And have fun with it - creation on a platform where performance is essentially solved really let’s us focus on the data within. – Adrian White Jul 14 '21 at 10:31
0

If you look through the docs (e.g. https://docs.snowflake.com/en/sql-reference/functions-all.html) you will see that Snowflake is pretty close to ANSI SQL.

Nat Taylor
  • 1,122
  • 7
  • 9