Constantin Lungudatawise.dev·Apr 22, 2024Using INSTR in BigQueryIf you ever need to do something different based on the existence of a particular substring in hashtag#BigQuery, take a look at the INSTR function. It returns the 1-based index of the first occurrence of a substring (1 or more characters) in another ...Discuss·42 readsPractical BigQuerybigquery
Moe Abbasmoeabbas.hashnode.dev·Apr 22, 2024Simplifying the Complex: BigQuery Recursive CTEs for Data TransformationBusiness Intelligence Analysts juggle a diverse range of datasets. Some reside at the granular unit level, while others are pre-aggregated at the item or order level. Aggregating data upwards (unit -> item -> order) is a breeze. But when it comes to ...Discussbigquery
Moe Abbasmoeabbas.hashnode.dev·Apr 21, 2024The Secret Weapon of BI Ninjas: Mastering the Art of Dummy DataIn today's data-driven world, BI analysts are crucial for organizational success. Building a strong portfolio is essential to showcase your skills, but finding relevant data can be challenging. Public datasets may not align with your interests, and c...Discussbigquery
Moe Abbasmoeabbas.hashnode.dev·Apr 19, 2024LAG()ing Behind on Sales Growth? This Analytical Function Will Catch You UpYou're tasked with producing a monthly sales growth report for a new market. Traditionally, the SQL required to produce this report would require complex joins or subqueries. Fortunately, modern RDBMS like Snowflake and BigQuery offer analytical func...DiscussSQL
Constantin Lungudatawise.dev·Apr 19, 2024Cross-dataset Foreign Key referencing in BigQueryIn one of my previous posts I've written about the then-newly-added Primary Key/Foreign Key constraints in BigQuery. While they are not enforced like traditional RDBMS, they can still provide an improvement to query performance. They have one importa...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·Apr 19, 2024Pay attention to cardinality & grain when UNNESTING in BigQuery!Whenever you're UNNESing an ARRAY, you're getting a Cartesian product between the row and the array contents. If you were to unnest another array, you'll get another Cartesian product between the output of the previous unnest and the elements in the ...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·Apr 19, 2024Constructing STRUCTS in BigQueryAfter my previous STRUCTS in BigQuery post, I could not have skipped to mention number of options when it comes to construct one. You can choose whether to provide field type, field name or both. There's 3 main ways:- via tuple ('a', 1) - BQ creates ...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·Apr 19, 2024Using STRUCTS for quick analysis in BigQueryI've posted earlier about STRUCTS in BigQuery, here's how I use it from time to time to help me debug and analyze data a bit faster. Since changing filter values for different test cases / observations you are interested about can be a headache (espe...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·Apr 19, 2024Understanding STRUCTS in BigQueryI've previously did a short intro post about ARRAYS in BigQuery, but I do see from time to time people that are just getting started become confused about how are they different from STRUCTS and when should we use them. Let's clarify this. STRUCT = a...Discuss·55 readsPractical BigQuerybigquery
Constantin Lungudatawise.dev·Apr 15, 2024Sharded tables in BigQueryHave you ever worked with sharded tables in BigQuery? I've encountered them in a project long time ago and haven't seen them much around since. Does the name not ring a bell? Well, think of it as pseudo-partitioning, a way to store data split between...Discuss·46 readsPractical BigQuerybigquery