Databricks series : setting up a connection to Databricks from local IDE (Part II)

Introduction

This is the second blogpost in a series about setting up an environment and perform a small project in Databricks. The first blogpost was about the installation of Databricks local IDE and this is the second blogpost in the series. In this blogpost w'll continue setting up the environment and make a connection to the workspace in Databricks.

This is the first blogpost in the series : Databricks series : Starting a new python project in Visual Studio Code (Part I)

These are the steps I've taken to setup the connection :

• Open the Databricks sidebar in Visual Studio Code and configure a new connection.
• Select your Databricks Workspace.
• Configure authentication, for example, using OAuth.
• Sign in through your browser.
• Select the desired compute resource.
• Setup the Python environment.

 

Create a configuration

First you need to setup a configuration in Visual Studio Code.

Fill the URL to the Databricks environment.

And select OAUTH (in my case)

And give the profile a proper name. I called it DBProfile.

Sign in with your account to Databricks.

A verification code is necessary to enter and if all is verified then you are authenticated.

And in Visual Studio Code you see the following configuration settings:

And there is more information about my Databricks Workspace there.

Next step is to select the compute. The option is to select Serverless and this is fine for now.

And I selected v5 as the version for the serverless compute.

Next there is the set up for the Python environment.

But I ran into an error. uv was not installed yet. 

I tried to install with Winget but that didn't succeed.

winget install --id=astral-sh.uv -e

But ran in an error because uv was not recognized as a valid command. 

I think it has something to do with the PATH variable in Windows. On Stackoverflow I found a script that I used to install in a terminal in Visual Studio Code: https://stackoverflow.com/questions/79474930/uv-command-not-found-in-visual-studio-code-on-windows

powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/install.ps1 | iex"

And in the output tab of Visual Studio it states now that the full intstallation of Python for Databricks  Connect is succeeded. 

Once configured, we now can work with our Databricks environment directly from Visual Studio Code.

Final thoughts

Quite some steps to setup the local environment for connecting to Databricks. Up to the next blogpost!

Hennie

Databricks series : Starting a new python project in Visual Studio Code (Part I)

Introduction

From local Python development to Databricks Jobs! In these series we w''ll start building  a Flexible Data Transfer Solution with Visual Studio Code and Databricks Asset Bundles.

Databricks offers extensive capabilities for processing data, but did you know that you don't have to develop all your Python code directly inside a Databricks Notebook?

You can also work locally with Visual Studio Code, structure your Python project, and deploy your code to Databricks. By using Databricks Asset Bundles (DAB), you can also manage your Databricks Job configurations as code.

In these series of blogposts, we will build a small end-to-end example. We will develop a local Python application that reads CSV files and writes the data to Delta tables in Databricks.

The interesting part is that we will use a single Python script to process multiple datasets. Which dataset gets processed is determined by parameters passed from a Databricks Job.

We will cover in these series the following  topics :

• Setting up Visual Studio Code (part I)
• Installing the Databricks Extension (part I)
• Creating a local Python project (part I)
• Preparing sample data 
• Developing the Python application
• Configuring Databricks Asset Bundles
• Deploying and running a Databricks Job

Lets first start with setting up the local environment :

• Visual Studio Code : Local development environment
• Python : Developing the application logic
• Databricks Extension : Integration between VS Code and Databricks
• Databricks CLI : Deployment and management
• Databricks Asset Bundles: Managing Jobs and resources as code

Visual Studio Code

The first part is installing Visual Studion Code. You can download the code from https://code.visualstudio.com/ and install visual studio code. It's quite straight forward!

Install python

Download Python from https://www.python.org/downloads/And also install the official Python extension from Microsoft in Visual Studio Code. 

Verify the installation using the terminal with python --version.

Installing the Databricks Extension

Open Visual Studio Code and navigate to Extensions (Ctrl + Shift + X). Search for the official Databricks extension and install it. The extension provides several capabilities, including:

• Connecting to a Databricks Workspace
• Creating Databricks projects.
• Managing Asset Bundles.
• Synchronizing and deploying Python code.
• Running and managing Databricks Jobs.

Nowadays you're also asked for installing the databricks AI tools :

Installing the Databricks CLI

We use the Databricks CLI to validate and deploy our Asset Bundle. Installation instructions:

https://docs.databricks.com/aws/en/dev-tools/cli/install

The instalation of Databricks CLI accroding to the instructions was not quite straight forward as you see below. Instead of upgrade I used install in the command :

Verify the installation of the Databricks CLI with Databricks version.

Final thoughts

This was a small blogpost about the installation of Databricks local development environment.

Hennie

Certified DBT Developer certification

Wow, I finally managed to pass the exam of DBT Developer certification. I failed two times first because I didn't had much of handson experience with DBT. That will change in the future. So, I had to learn everything from the documentation and from a lot of Udemy tests. Although the tests available on the internet doesn't completely cover the questions on the real exam of DBT, it helped a lot to understand DBT. I did that by researching the answers, googling and reading the documentation of DBT. Last but not least I practiced a bit with DBT on a small mock-up project that I've created. 

I also created a summary of all the things I learned in PowerPoint slides. I do this now for a couple of years now and I have an extensive list of PowerPoints created on subjects like DevOps, Snowflake, Microsoft, Azure, Data modelling, Datavault, Git, Scrum, etc and now DBT. I helps me to understand and organize the information.

Here is my list of powerpoints of DBT. 

Here are interesting courses and tests on Udemy

And I really enjoyed following this course on Udemy (also because it's for Snowflake).

Qanalabs also has a good list of questions (and explanantions) that helps.

Hennie

Ephemeral tables in DBT (Part XI)

Introduction

In this blogpost we're going to take a look at a specific type of a materialization, known as ephemeral. According to DBT : materializations are strategies for persisting dbt models in a warehouse. There are five types of materializations built into dbt. They are:

• table
• view
• incremental
• ephemeral
• materialized view

In this blogpost we'll focus on the ephemeral materialization. Now one thing that pops up out out of my head right away is the continuing discussing between a dimensional model as Kimball has defined and the Snowflake (not the data platform) datamodel. Sometimes there are in multiple dimensions the same aggregations used. In the example of AdventureWorksLT think about ProductCategory. Suppose that a ProductCategory is used in multiple dimensions and you want adopt the DRY principle. Then, you can decide to ProductCategory as Ephemeral materialization. Lets try this out!

This blogpost is a blogpost in a serie of blogposts :

• Build a data warehouse with DBT and Snowflake on AdventureWorksLT (PART I)
• Build Staging and Dimensions in DBT for Snowflake (PART II)
• Sources in DBT for Snowflake (PART III)
• Data freshness for DBT in snowflake (Part IV)
• Snapshots in DBT for Snowflake (Part V)
• Generic tests in DBT for Snowflake (PART VI)
• Singular tests in DBT for Snowflake (Part VII)
• Add packages to your DBT project (Part VIII)
• Adding documentation to your DBT project (Part IX)
• Working with hooks in DBT  (Part X)
• Ephemeral tables in DBT (Part XI)

Ephemeral example

Suppose that you have a dimension ProductCategory that is cleaned up and has quite some transformations. You don't want to apply the same logic again and again for different (other) dimensions. Ok, now lets go the Dim_ProductCategory.sql file and make it an ephemeral materialization:

Next, we need to reference this ephemeral materialization in the Product dimension.

The next step is to run the model 

Now what has DBT made from the code? Lets check this in the target folder :

An extra CTE is added to the Dim_Product and is around the CTE_DIM_PRODUCTCATEGORY. The ephemeral model is incorporated in another model.

Final thoughts

In this bogpost I've investigated the ephemeral materialization of a model. Interesting to see that it is possible to reuse logic of a model in other models.

Hennie

dbt Analytics Engineering Certification Exam cheatsheet

Introduction

I'm preparing for the DBT exam and I used the  "dbt Analytics Engineering Certification Exam Study Guide" for searching for more information about the topics covered in the exam. I stored the links that I used in this blogpost, just helping you too. I hope you enjoy it.

I'll frequently update this blogpost. I just started with a couple of topics.

Topic 1: Developing dbt models

• Identifying and verifying any raw object dependencies
• Pro-tips for workflows
• Understanding core dbt materializations
• Materializations
• Conceptualizing modularity and how to incorporate DRY principles
• DRY
• Converting business logic into performant SQL queries
• Business logic in DBT
• Refactoring legacy SQL to dbt
• How dbt Can Help Solve 4 Common Data Engineering Pain Points
• Using commands such as run, test, docs and seed
• dbt Command reference
• Creating a logical flow of models and building clean DAGs
• Data modeling techniques for more modularity
• How we structure our dbt projects
• dbt DAG: Definition, Usage, and Examples
• Defining configurations in dbt_project.yml
• Model configurations
• Configs, properties, what are they?
• Configuring sources in dbt
• Source configurations
• Add sources to your DAG
• Source properties
• Using dbt Packages
• Packages
• Building dbt packages
• dbt packages

Topic 2: Debugging data modeling errors

• Understanding logged error messages
• Debug errors
• The definitive guide to debugging dbt
• Troubleshooting using compiled code
• Debug errors
• The definitive guide to debugging dbt
• Troubleshooting .yml compilation errors
• Debug errors
• The definitive guide to debugging dbt
• Distinguishing between a pure SQL and a dbt issue that presents itself as a SQL issue
• Developing and implementing a fix and testing it prior to merging

Topic 3: Monitoring data pipelines

• Understanding and testing the warehouse-level implications of a model run failing at different points in the DAG
• Understanding DAGs in dbt: Erring Models and Failing Tests
• DAG
• On DAGs, Hierarchies, and IDEs
• Understanding the general landscape of tooling

Topic 4: Implementing dbt tests

• Using generic, singular and custom tests on a wide variety of models and sources
• Add data tests to your DAG
• Writing custom generic data tests
• Understanding assumptions specific to the datasets being generated in models and to the raw data in the warehouse
• Best practices for workflows
• Add sources to your DAG
• Implementing various testing steps in the workflow.
• Add data tests to your DAG
• Data testing
• Data transformation process: 6 steps in an ELT workflow
• Ensuring data is being piped into the warehouse and validating accuracy against baselines

Topic 5: Deploying dbt jobs

• Understanding the differences between deployment and development environments
• Configuring development and deployment environments
• Configuring the appropriate tasks, settings and triggers for the job
• Understanding how a dbt job utilizes an environment in order to build database objects and artifacts
• Using dbt commands to execute specific models

Topic 6: Creating and Maintaining dbt documentation

• Updating dbt docs
• Implementing source, table, and column descriptions in .yml files
• Using dbt commands to generate a documentation site
• Using macros to show model and data lineage on the DAG

Topic 7: Promoting code through version control

• Understanding concepts and working with Git branches and functionalities
• Creating clean commits and pull requests
• Merging code to the main branch

Topic 8: Establishing environments in data warehouse for dbt

• Understanding environment’s connections
• Understanding the differences between production data, development data, and raw data