Constantin Lungudatawise.dev·May 9, 2024The order in which you ROUND matters in SQLRounding numbers in SQL is one of the simplest operation, but it's important to we pay attention to how we apply it. 1) Consider the sequence in which you apply rounding and aggregation functions. When you ROUND a value and then aggregate it usin...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·May 9, 2024Order of precedence in SQL: WHERE vs HAVINGIf you're just getting started with SQL, this post is for you. So, it's worth looking at the order of precedence of SQL operators. One particular case is WHERE vs HAVING, especially if you bind the aggregated column to the same column alias as in the...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·May 9, 2024Transactions in BigQueryFor a long time I didn't even know transactions existed in BigQuery. How are they useful? Say you are performing a number of operations that you would like to succeed in full (so all steps are successful) or be aborted entirely (reverting to the prev...DiscussPractical BigQuerybigquery
Moe Abbasmoeabbas.hashnode.dev·May 9, 2024Taming the Wiggles: Unveiling Trends with Simple Moving Averages in BigQuery & TableauEver felt like you're wrestling with a data kraken – a tangled mess of daily sales figures bouncing all over the place? Fear not, fellow business intelligence warriors, for we have a trusty tool in our arsenal: the simple moving average (SMA). Forget...Discussanalytics
Kaushik IskaforPeerDB Blogblog.peerdb.io·May 7, 2024PeerDB Cloud is Now in Public Beta!🚀 Today, we're excited to announce that PeerDB Cloud is officially entering public beta. If you're a data engineer or an organization looking for a fast, simple, and cost-effective way to replicate data from Postgres to data warehouses such as Snowf...Discuss·1 like·92 readsdata-movement
Constantin Lungudatawise.dev·May 7, 2024Where does QUALIFY fit in the order of execution in BigQuery?Here's an example of how QUALIFY fits into the order of execution in SQL. In the BigQuery example below, we want to compute the second-to-last order_updated event for each order. To do this, we filter to keep only the rows WHERE order_status = 'o...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·May 7, 2024Using GAPS_FILL in BigQueryAnother new time series function in BigQuery in addition to the ones previously presented is the GAPS_FILL table-valued function. It allows us to fill in a time series (DATE, DATETIME, TIMESTAMP) with missing rows to a desired time grain. Previously,...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·May 7, 2024Time Series functions in BigQueryI've posted some time ago about "rounding" timestamps and datetime values, but in the past few months BigQuery has added Time Series functions (in preview for now), making for a cleaner and simpler approach of this problem. We now have DATE_BUCKET,...DiscussPractical BigQuerybigquery
Constantin Lungudatawise.dev·May 7, 2024JSON datatype vs JSON-like STRING in BigQueryIn one my previous posts, we've briefly introduced the JSON datatype in BigQuery. But did anyone notice how most of the JSON functions have signatures for both a JSON-type input and a json-formatted string input? What is the difference between the tw...DiscussPractical BigQuerySQL
Constantin Lungudatawise.dev·May 2, 2024A simple data validation scenario using FULL OUTER JOIN & ORDER BYData Engineers do a lot of Data Analysis work, too. For example: we need to understand why is there a difference between two approaches or data in two source systems. I've previously shown how a FULL OUTER JOIN in SQL with simple validation and h...DiscussPractical BigQuerybigquery