The LIKE operator helps you easily match, find, and filter out string values of a specified pattern by using SQL wildcards. Important to note that the pattern passed into the LIKE operator is case-sensitive, unlike its case-insensitive cousin, ILIKE.
The LIKE operator has a simple syntax, with the ability to have it utilized in WHERE clauses or case statements:
where <field_name> like '<pattern>' or case when <field_name> like '<pattern>'
Some notes on this operator’s syntax and functionality:
- The
<pattern> can use two SQL wildcards (% and _); the underscore will match any single character and the % matches zero or more characters- Ex. '%J' = any string that ends with a capital J
- Ex. 'J%' = any string that starts with a capital J
- Ex. 'J%L' = any string that starts with a capital J and ends with a capital L
- Ex. '_J%' = any string that has a capital J in the second position
- Majority of use cases for the LIKE operator will likely involve the
% wildcard - The LIKE operator is case-sensitive, meaning that the casing in the
<pattern> you want to filter for should match the same-case in your column values; for columns with varied casing, leverage the case-insensitive ILIKE operator - The LIKE operator can be paired with the NOT operator, to filter on rows that are not like a specified pattern
Let’s dive into a practical example using the LIKE operator now.
SQL LIKE example
select
user_id,
first_name
from {{ ref('customers') }}
where first_name like 'J%'
order by 1
This simple query using the Jaffle Shop’s customers table would return all of the customers whose first name starts with the uppercase letter J:
| customer_id | first_name |
|---|
| 1 | Julia |
| 4 | Jeremy |
Because LIKE is case-sensitive, it would not return results in this query for customers with lowercase J-names. If you have a mix of uppercase and lowercase strings in your data, consider standardizing casing for strings using the UPPER and LOWER functions or use the more flexible ILIKE operator.
LIKE syntax in Snowflake, Databricks, BigQuery, and Redshift
Most, if not all, modern data warehouses support the LIKE operator and the syntax is also the same across them. Some data warehouses, such as Snowflake and Databricks, additionally support similar or more flexible operators such as ILIKE, the case-insensitive version of LIKE, or LIKE ANY, which allows you to pass in multiple pattern options to scan for.
Use the table below to read more on the documentation for the LIKE operator in your data warehouse.
| Data warehouse | LIKE support? |
|---|
| Snowflake | ✅ |
| Databricks | ✅ |
| Amazon Redshift | ✅ |
| Google BigQuery | ✅ |
You may see the LIKE operator used in analytics engineering work to:
- Bucket column values together based on general requirements using case statements and the LIKE operator (ex.
case when page_path like '/product%' then 'product_page' else 'non_product_page') - Filter out employee email records based on a similar email address pattern (ex.
where email_address not like '%@dbtlabs.com')
This isn’t an extensive list of where your team may be using the LIKE operator throughout your dbt models, but contains some common scenarios analytics engineers face day-to-day.
FAQs
dbt provides more reliable analysis
No longer copy and paste SQL, which can lead to errors when logic changes. Instead, build reusable data models that get pulled into subsequent models and analysis. Change a model once and that change will propagate to all its dependencies.
Is dbt an ETL tool? ›
DBT can be used in ETL (Extract, Transform, Load) workflows, but its main focus is data transformation and modeling, making it more suited for ELT workflows where data transformation is a crucial stage in data integration.
Is dbt free to use? ›
dbt is free forever for solo developers, and teams can try it out for 14 days. Create an account to escape tedious, disordered workflows and take control of the data you deliver.
Can you use dbt with SQL Server? ›
Activating the virtual environment is the first step in setting up Microsoft SQL Server with dbt Developer Hub. This involves creating an isolated environment where you can install the necessary packages and dependencies without affecting your system's Python environment.
What are the disadvantages of dbt tool? ›
DBT's Lack of Real-Time Processing
DBT (data build tool) is designed to transform data in a batch processing manner, which inherently means it does not cater to real-time data processing needs. This limitation is crucial for organizations that require immediate insights from their data streams.
Is dbt worth learning? ›
dbt is an easier way to write and manage SQL, that's it. It's free, open-source and simple. Sure you can do everything dbt does in Python and a workflow orchestration tool like Airflow, Dagster or Prefect to handle dependencies between your transformations and triggering jobs.
Does dbt use Python? ›
In a dbt Python model, all Python code is executed remotely on the platform. None of it is run by dbt locally. We believe in clearly separating model definition from model execution. In this and many other ways, you'll find that dbt's approach to Python models mirrors its longstanding approach to modeling data in SQL.
Why use dbt with Snowflake? ›
dbt Cloud is the fastest way to build trusted, governed data and AI products on Snowflake. Find out why thousands of organizations rely on the Data Control Plane to accelerate pipelines, improve collaboration, and get more data work done at enterprise scale. Now available as a Native App!
When to not use dbt? ›
While DBT is an effective, evidence-based treatment for BPD, it may not work as well for certain mental health conditions and preferences. For example, there's conflicting research about DBT's effectiveness for obsessive-compulsive disorder (OCD), panic disorder, and post-traumatic stress disorder.
Why is dbt criticized? ›
Criticisms of Dialectical Behavior Therapy
- DBT requires a significant time commitment (from the consumer and the clinician). - There are many skills in DBT, which may be overwhelming. As a result, consumers who may benefit from it may find it overly complex and unwilling to try.
Here are a few benefits users of both dbt and Databricks get: Easy way to incorporate data analysts into the data infrastructure development process – dbt provides a streamlined process from development to production, enabling users who may be new to Git and software development standards to easily use the lakehouse.
Who is dbt not recommended for? ›
When Does DBT Not Work? DBT is not recommended for people with intellectual disabilities. DBT is also not targeted to treat panic disorder/panic disorder with agoraphobia, post-traumatic stress disorder, or psychotic disorders.
Can dbt connect to MySQL? ›
Configuring your profile
A dbt profile can be configured to run against MySQL using configuration example below. Use type: mysql for MySQL 8. x, type: mysql5 for MySQL 5. x, and type: mariadb for MariaDB.
Can dbt connect to Oracle? ›
dbt can connect to Oracle Autonomous Database (ADB-S) in Oracle Cloud using either TLS (Transport Layer Security) or mutual TLS (mTLS).
Which platform is best for SQL practice? ›
Table of contents
- 10 Best Platforms to Practice SQL.
- Codecademy.
- Analytics Vidhya.
- SQLZoo.
- LeetCode.
- HackerRank.
- Mode Analytics.
- StrataScratch.
Advantages of Direct Benefit Transfer
DBT ensures accurate targeting of beneficiaries. Beneficiaries can link only one bank by seeding the fund deposits to their Aadhaar details to avoid duplication of subsidies. It enables the Government to simultaneously reach out to both citizens and beneficiaries of the scheme.
What advantages does dbt core have over traditional SQL? ›
dbt enables data analysts to custom-write transformations through SQL SELECT statements. There is no need to write boilerplate code. This makes data transformation accessible for analysts that don't have extensive experience in other programming languages.
Why use dbt over stored procedures? ›
The major advantage of dbt lies in the modularity of its models. Each transformation step is encapsulated in a specific model, thus facilitating its documentation and independent testing.
Why use dbt therapy? ›
Dialectical behavior therapy (DBT) is especially effective for people who have difficulty managing and regulating their emotions. DBT has proven to be effective for treating and managing a wide range of mental health conditions, including: Borderline personality disorder (BPD). Self-harm.
