Fun with Loops and Lists in dbt (and Jinja2)

James Hunt |

One thing I really like about dbt is Jinja. The fact that there is a built-in templating language, complete with modern control structures like if-then-else and for loops, makes writing SQL more meta than ever before. Today, I want to talk about bridging the divide between dbt variables defined in your project YAML file, and the (SQL) model files where you want to use them. (By the way, this blog post was inspired by a question on the dbt Slack org.

Read on →

Exploring Data With dbt: Renormalizing Data

James Hunt |

Sometimes, we get data in its natural, normalized form. More frequently, however, we get denormalized data, where some attribute or identity that exists in its own right has been embedded in related data. When modeling for enterprise concepts (nouns, not verbs) we will often want to renormalize. In our case, the Scryfall data has been denormalized, and we need to renormalize it. That’s what we’re going to do today, now that we have more or less finished modeling our card data.

 Read on →

Exploring Data With dbt: Anticipating the Future

James Hunt |

So far, we’ve spun up a lab environment, landed and de-duplicated our data, used Postgres’ JSONB capabilities to extract fields, and staged all of it into an emerging model with dbt. Our understanding of the current Scryfall data schema is taking shape, and we’re fairly confident in it. Today, we’re going to talk about the future. In particular, we’re going to discuss how that schema might change, and what we might do about it with dbt.

 Read on →

Exploring Data With dbt: Extracting JSON Fields

James Hunt |

In the last essay, we got our lab environment spun up (thanks to the wonders of Docker Compose), set up our dbt project, and wrote our first model to de-duplicate landed JSON blobs. Today, we’re going to roll up our sleeves and dive right into the JSON objects we’re getting out of Scryfall. Welcome! In this series on exploring data, we use dbt to plumb the depths of a semi-structured JSON data set with the help of PostgreSQL’s JSONB datatype and related functions and operators.

 Read on →

Exploring Data With dbt: Loading & Landing

James Hunt |

dbt is fantastic. It’s mostly SQL, with enough convention and some templating thrown in to cover the gaps for things like dynamic column families, open-ended data-driven joins, etc. To show off the sheer power and flexibility that dbt brings the data modeler, I want to explore some semi-structured data, and build up the dbt models and pipeline we need to flatten it into a relational, queryable model. The data in question is from Scryfall, a search service built on top of the collectible trading card game Magic: the Gathering.

 Read on →

Playing with Arrays in PostgreSQL

James Hunt |

I’m going to come clean: for a large chunk of my adult software-writing life, I’ve actively ignored databases. That’s not to say I didn’t use them! Given the choice, I would implement application logic in the code I was writing; treating the relational database as a dumb collection of keys and their values. In part this grew from a culture of ORM use. Rather than using icky SQL and getting tied to one platform or another, object relational mappers insulate us from underlying database engines and their — ahem — idiosyncrasies.

 Read on →

Trust is the true measure of technology consulting

Lachlan James |

Firsts can be calmingly poignant: From life milestones like your first paycheck or firstborn, to your first sip of coffee in the morning. Likewise, it’s quite something to join a fresh new business venture, on the ground floor, and see the possibilities; what could happen, what could be. Those initial stages of a start-up often deliver that sense of calmness through clarity of purpose. But, like most firsts of significance, the tyranny of the blank slate also weighs heavy — a conscious acknowledgement of the complexities and important decisions that lie ahead.

 Read on →

Building a new kind of consultancy

James Hunt |

There is a quiet serenity to being early. Picking up a new technology that hasn’t yet seen widespread adoption, taking-up a niche hobby, or – bear with me here – even joining a startup. There is no tradition. There is no “that’s how it’s always been.” There is no “we’ve always done things this way.” The sense of possibility and the openness of it all is refreshing. The future is, quite deliberately, wide open.

 Read on →

Cloud consulting isn’t a profession — but it should be

Tony Nicol |

The cloud and data industry is continuously and rapidly evolving. In fact, the global cloud computing market is forecast to grow from USD 445.3 billion in 2021 to USD 947.3 billion by 2026 — a Compound Annual Growth Rate of 16.3%. It’s an exciting space that’s both fed and driven my passion for technology throughout my career. Cloud computing, during the 2010’s in particular, has redefined how organizations work. We’ve seen the rise of serverless computing; containers and microservices; the explosion of SaaS, PaaS and IaaS; multi-cloud strategies; edge computing; big data and IoT; open-source services and more.

 Read on →