Writing

Articles on analytics, SQL, R, and practical data work

Writing

Articles on analytics, SQL, R, DuckDB, and practical data work

The archive leans toward SQL patterns, analytics engineering details, DuckDB, and the implementation edge cases that tend to matter more than they first appear.

Some posts are compact implementation notes. Others are longer walkthroughs focused on the reasoning, not just the final query.

Manager Notes on one on ones

Data Ramblings 2025 (part 1)

Over time, my head fills up with ideas about the data world. These ideas are usually ramblings about how the current tooling is lacking capabilities. On the one hand…

Micro-improvement for window functions

SQL

A very small change leads to a very small improvement

Qualifying Events Using Recursive CTEs

SQL

And the joys of writing trash SQL code

dbt anti-pattern: using {{ this }} directly in incremental refreshes

If you’re using dbt, the recommended default way to build incremental models looks like this:

dbt anti-pattern: dbt docs runs queries with pivot macros

I can’t for the life of me find this documented but apparently queries that use the pivot or unpivot macros are run so that their compiled code becomes available in the…

Grouping Non-Overlapping Date Ranges

SQL

If you’re a data analyst at a company, it’s VERY likely your company stores some data as a Type 2 slowly-changing dimension. It’s also likely that the way it’s stored may…

Grouping Overlapping Date Ranges

SQL

It’s pretty straightforward to group rows in SQL if their date ranges are consecutive or have fixed gaps.¹ It’s much harder if you want to group overlapping date ranges. I…

Connect to Fabric Lakehouse using R and Python

R

odbc::dbConnect(odbc::odbc(), driver = “SQL Server 17”, Authentication = “ActiveDirectoryServicePrincipal”)

Convert seconds to hh:mm:ss in DAX

pbi

Solutions to convert an integer into a time format in DAX are not great. The default should be FORMAT([seconds], "hh:mm:ss") but it doesn’t work on a hierachical two-way…

Create a Date Dimension

data warehouse

I have been searching for a clean solution to building a date dimension so here I am writing my own. In R!

Dimension reduction with Wingspan data

r

data science

Using data from the board game Wingspan to present dimension reduction techniques.

My Favourite Things Books

books

This is not a comprehensive guide but more of a compendium of links and sources for future self-reference. I suspect some of these might become deprecated or some of the…

3 Ways to Turbocharge Your Excel Team

excel

data

A lot of companies still have Excel teams working with data. It may seem like folks working in the Microsoft garden are missing out on the bees’ knees of R, Python or Julia…

Visualising ESS data using ggplot2 and tmap

r

I stumbled across rOpenSci’s package essurvey that contains data from the European Social Survey (ESS) for European countries, including Lithuania! Also, tmap has a lot of…

log(x+1) and log(1+x)

r

statistics

I’ll be honest, my math skills are not great and I haven’t studied maths formally since high school. However, Lior Pachter’s preprint brings a methodological issue in…

Assumptions of the Chi-squared test

r

statistics

Having written the post about the Chi-squared test, I thought I would list the assumptions of the Chi-square test and null hypothesis testing in general.

How to Chi-squared test

r

statistics

As I am working through Statistical Rethinking and picking up new tools, I decided to rework my knowledge of null hypothesis testing. This is definitely not the worst…

Student reported p values

r

statistics

education

I have been delaying this post for 2 years but I am glad to finally publish it. This post is an example of incentives driving the students to publish more abstracts with…

Analysing LSMU publications

r

lsmu

Hello! I have recently finished my master’s degree and finished my summer projects! With spare time on my hands, I have finally gotten around to analysing the full list of…