Kickstart Your Analytics Engineering Career

A highly scalable, serverless, and cost-effective cloud data warehouse. We'll leverage its public datasets, which are free to query up to a certain limit, making it ideal for learning.

dbt enables data analysts and engineers to transform data in their warehouse by simply writing SQL. It brings software engineering best practices like version control, testing, and documentation to the data transformation workflow.

Essential for any collaborative or structured development, Git allows you to track changes in your code, while GitHub provides a platform for hosting your repositories and collaborating with others.

Implement continuous integration and continuous delivery directly into your GitHub repositories. This will be crucial for automating your dbt project's testing and deployment.

SQL is fundamental for many job titles, including Analytics Engineers, as it the primary language used to query data stored in databases and also to transform that data. Familiarity with CTEs (common table expressions), JOINs and window functions is expected. If you are not familiar with these areas, you can sign up for online courses (DataCamp, Udemy, etc.) or attend one of Xebia's SQL training courses.

Version control is a system that records changes to a file or set of files over time so that you can recall specific versions later. For aspiring analytics engineers, understanding the basics of version control (most commonly Git) is fundamental as it enables collaborative development on SQL queries, dbt models, and Python scripts and more, allowing teams to track every modification, revert to previous states and merge contributions seamlessly. In modern data transformation pipelines, version control ensures data quality, reproducibility, auditability, and efficient teamwork. There are several commonly used version control tools including GitHub, GitLab and Azure DevOps.

Create a Google Cloud account (it's free!). Create a new project and enable the BigQuery API. In the BigQuery UI, ensure you can see the bigquery-public-data project in the Explorer pane.

In the "IAM and admin" > "Service accounts" section, create a service account and a JSON key for this service account. Note that this key is sensitive so please store it safely!

Create a GitHub account (it's free!). Create a repository, preferably public so others can see your skills. Note that it is possible to change the visibility of your repository between public and private as often as you like.

Create a dbt Cloud account (it's free for individuals!). Create a new project selecting "BigQuery" as the warehouse and uploading the service account JSON file created earlier. Connect your GitHub repository to dbt Cloud by following this guide. You should be able to run dbt debug successfully from the Studio in dbt Cloud.

Model Organisation

Using the bigquery-public-data BigQuery project, select tables to use as sources. Here are some interesting options:

1. crypto_bitcoin.transactions: A large, constantly updating table containing Bitcoin transactions. This source gives you the opportunity to create an incremental model.

2. github_repos.commits: Analyse the commit activity in a GitHub repo(s) of your choice.

3. wikipedia.pageviews_2025: Take a look at what Wikipedia articles are trending.

Structure your dbt project with staging, intermediate, and marts layers according the dbt's recommendations.

Configure your dbt_project.yml, carefully considering which materialisation (e.g., table, view, incremental) makes the most sense for each layer of your models.

Add both singular and generic tests to ensure your marts layer contains high quality data.

Explore adding dbt exposures to define downstream consumption of your dbt models, particularly for your BI reports.

Make use of version control best practices by creating feature branches and pull requests. Ensure your commits contain related work and a clear commit message.

Add a CI job and configure "slim CI". This will automate testing of your changes on every pull request, ensuring data quality and preventing breaking changes.

Add a branch protection rule to your main branch so that pull requests cannot be merged unless the CI job succeeds.

With your data transformed and validated by dbt, the next step is to connect your chosen BI tool (e.g. Tableau Public, Power BI Desktop – many offer free versions or trials) to your dbt-generated marts tables. Building dashboards demonstrates that the data available in your marts layer can answer business questions.

As an alternative to dbt Cloud you can use dbt Core, the open-source version of dbt. This guide provides a quickstart. This allows you to demonstrate your knowledge of setting up a python virtual environment and comfort working with a command line interface (CLI). VSCode is a great integrated development environment (IDE) to help you get started.

Using GitHub Actions it is possible to create your own CI workflow that compiles and runs your dbt project. In addition, you can create a continuous delivery (CD) workflow that publishes dbt Docs as a GitHub Page.

Use a SQL formatter on your code to ensure a standardised approach to formatting. SQLFluff and sqlfmt are two widely used options.

Is your background in other areas of engineering, do you have Docker knowledge? One way to demonstrate this is by using dbt Core with a dev container, a complete development environment that includes all the essential packages, tools, dependencies, and configurations needed to develop with dbt.

Orchestration refers to the regular running of your dbt project so the underlying dbt models contain up to date data. Generally this is performed using dbt Cloud or a tool like Airflow or Dagster. You can also use GitHub Actions to schedule your dbt project. When designing your orchestration, keep in mind topics like how you will handle retries, will alerts be sent upon failures and what logs are outputted so you can debug what actions the pipeline performed.

Already have a dbt repo you are proud of?! Don't forget to include a link on your CV so recruiters and hiring managers can take a look!

Books

For those interested in upskilling on other aspects of Analytics Engineering, there are many books available such as:

1. Designing Data-Intensive Applications: A resource for understanding and building data-intensive applications, covering data modeling, storage systems, and distributed systems.

2. Fundamentals of Analytics Engineering: This book guides you through designing and implementing data pipelines, data modeling, schema design, data quality, governance, observability, collaborative coding practices (Git), and automating workflows with CI/CD pipelines and orchestrators.

3. The Data Warehouse Toolkit: The classic guide to data modeling in data warehouses, focused on the Kimball dimensional modeling methodology.

Community

Inspiration for learnings can also come from others. There are a number of communities where can you ask questions, get inspired or see the work of others:

1. dbt Slack: A Slack workspace for all questions related to dbt, frequented by over 20k users.

2. r/dataengineering: For more technical questions, the data engineering subreddit is an active space for questions as well as project showcases.

3. Newsletters: There are a number of newsletters/blogs that are worth signing up for such as Data Products from Chad Sanderson, The Analytics Engineering Roundup from dbt Labs and Benn Stancil's Substack.