# How to Insert Into PostgreSQL
Here's the shortest and easiest way to insert in PostgreSQL. You only have to specify the values, but you have to pass all values, and they have to be in order. So if you have 10 columns, you have to specify 10 values.

```sql
-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
insert into users values ('John', 'Doe', 'john@doe.com');
```

If you have many columns, but only want to specify some:

```sql
insert into users (first_name) values ('John');
```

If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
insert into users (preferences) values ('{ "beta": true }');
```

If inserting a row would violate a unique constraint, you can use Postgres' `on conflict` clause to specify what to do when that happens. For example, imagine you have a webhook system and you want to gracefully handle duplicate webhooks:

```sql
-- If we already recorded this webhook, do nothing
insert into stripe_webhooks (event_id)
values ('evt_123')
on conflict do nothing;
```

You can also do "upserts" (update or insert) in Postgres:

```sql
-- Assuming you have a unique index on email
insert into users (email, name)
values ('john@doe.com', 'Jane Doe')
on conflict (email) do update set name = excluded.name; -- excluded.name refers to the 'Jane Doe' value
```

How to Insert

# How to Update in PostgreSQL
```sql
-- All rows
update users set updated_at = now();

-- Some rows
update users set updated_at = now() where id = 1;
```

How to Update

# How to Delete in PostgreSQL

```sql
delete from users where id = 1;
```

How to Delete

# How to Trim Strings in PostgreSQL
The `trim()` function removes specified characters or spaces from a string. You can specify to trim from only the start or end of the string, or trim from both. This is best explained using examples.

You can trim "1" from start of the string:

```sql
select trim(leading '1' from '111hello111');

 ltrim   
----------
 hello111
```

You can trim "1" from the end of the string:

```sql
select trim(trailing '1' from '111hello111211');

 rtrim     
--------------
 111hello1112
```

_Note: the 1's before the 2 were not trimmed, as they are not the end of the string._

You can specify multiple characters to trim. PostgreSQL will remove any combination of those characters in succession:

```sql
select trim(both 'abc' from 'abcbabccchellocbaabc');

 btrim
-------
 hello
```

PostgreSQL also supports the non-standard syntax of specifying which characters to remove as the last parameter, separated by a comma:

```sql
select trim(both 'abcbabccchellocbaabc','abc');

 btrim
-------
 hello
```

Not specifying where to trim from has the same result as "both":

```sql
 select trim('1' from '111hello111');

 btrim
-------
 hello
```

Not specifying what character to trim will result in trimming spaces:

```sql
select trim('    remove spaces from both sides    ');

 btrim             
-------------------------------
 remove spaces from both sides
```

`rtrim()` and `ltrim()` are special versions that only remove trailing and leading characters, respectively. These functions only accept parameters separated by commas:

```sql
select ltrim('zzzyclean this up', 'xyz');

 ltrim     
---------------
 clean this up
```

```sql
select rtrim('again not specifying what to trim removes spaces   ');

 rtrim                       
--------------------------------------------------
 again not specifying what to trim removes spaces
```

How to Trim Strings

# How to Use substring() in PostgreSQL
In PostgreSQL, the function `substring()` has many uses. The simplest one extracts a number of characters from the supplied string. Let's look at its syntax and parameters:

**Syntax:**

```sql
substring(original_string [from <starting_position>] [for <number_of_characters>])
```

**Parameters:**

* original\_string (required) - the string or column name to extract from
* `from` starting\_position (optional) - the position of the first character to be extracted. If not specified, the extraction will be from the first character.
* `for` number\_of\_characters - the number of characters to be extracted. If not specified, the extraction will be up to the last character of the string.

Note that while the last two parameters are optional, you must use one of them. Here are some examples:

```sql
# select substring('Learning SQL is essential.' from 10);
 substring 
-------------------
 SQL is essential.
```

```sql
# select substring('Learning SQL is essential.' from 10 for 3);
 substring
-----------
 SQL
```

```sql
# select substring('Learning SQL is essential.' for 13);
 substring 
---------------
 Learning SQL
```

`starting_position` can also be a negative number. On its own it is ignored but when used with `number_of_characters` it causes the function to count "invisible" characters to the left of the string.

```sql
# select substring('Learning SQL is essential.', -4, 10);
 substring
-----------
 Learn
```

There is also a version of this function that uses commas to separate the parameters instead of using the keywords `from` and `for`. In this case only the number of characters parameter is optional.

```sql
# select substring('Learning SQL is essential.', 10, 3);
 substring
-----------
 SQL

# select substring('Learning SQL is essential.', 10);
 substring 
-------------------
 SQL is essential.
```

How to Use substring()

# How to Use PostgreSQL substring() with RegEx to Extract a String
You can use regular expressions in the `substring()` function to extract a string that matches a specified pattern:

**Syntax:**

```sql
substring(string from pattern) -- using POSIX regular expressions
substring(string from pattern for escape_char)  -- using SQL regular expressions
```

Here is one example that uses POSIX regular expressions to extract any word that has 'ss' among its letters:

```sql
# select substring('Learning SQL is essential.' from '\w*ss\w*');
 substring
-----------
 essential
```

`substring()` with SQL regular expressions involves three parameters: the string to search, the pattern to match, and a delimiter defined after the `for` keyword. In the following example we look for a three and then seven letter words that starts with an 'S' and ends with an 'L':

```sql
# select substring('Learning SQL is essential.' from '%#"S_L#"%' for '#');
 substring
-----------
 SQL

# select substring('Do you pronounce it as SQL or SEQUEL?' from '%#"S____L#"%' for '#');
 substring
-----------
 SEQUEL
```

You may refer to the [PostgreSQL documentation](https://www.postgresql.org/docs/12/functions-matching.html) for more information on regular expression pattern matching.

How to Use substring() with RegEx to Extract a String

# How to Replace Substrings in PostgreSQL
The `replace()` function is used to change all occurrences of a certain substring to a new string. It accepts three parameters - the main string, the substring to be replaced, and the new string to be used.

```sql
select replace('This is old, really old', 'old', 'new');
```

```sql
 replace         
-------------------------
 This is new, really new
```

How to Replace Substrings

# How to Modify Arrays in PostgreSQL
## Overwriting an Array

The most basic way to modify an array column are to overwrite all values by assigning it a new array, or to specify an element to change.

Say you start off with these data:

```sql
player_number  |  round_scores   
---------------+------------------
         10002 | {91,92,93,95,99}
         10001 | {95,92,96,97,98}
```

```sql
-- overwrite all scores for a player
update player_scores set round_scores='{92,93,94,96,98}' where player_number=10002;

-- change only the score for the second round for player 10001
update player_scores set round_scores[2]=94 where player_number=10001;
```

After performing these commands, the updated data are:

```sql
 player_number |   round_scores   
---------------+------------------
         10002 | {92,93,94,96,98}
         10001 | {95,94,96,97,98}
```

## Prepend and Append to an Array

PostgreSQL has functions that offer more ways to modify arrays. First, use `array_prepend()` and `array_append()` to add one element to the start and to the end of an array, respectively:

```sql
update player_scores set round_scores = array_prepend(0, round_scores);

update player_scores set round_scores = array_append(round_scores, 100);
```

```sql
 player_number |      round_scores      
---------------+------------------------
         10002 | {0,92,93,94,96,98,100}
         10001 | {0,95,94,96,97,98,100}
```

## Concatenate Multiple Arrays

To add an array to another array, use `array_cat()`.

```sql
select array_cat('{1, 2}', ARRAY[3, 4]) as concatenated_arrays;
```

```sql
 concatenated_arrays
---------------------
 {1,2,3,4}
```

The `||` operator can be used as a much simpler alternative to `array_prepend()`, `array_append()` and `array_cat()`:

```sql
select 1 || array[2, 3, 4] as element_prepend;
```

```sql
 element_prepend
-----------------
 {1,2,3,4}
```

```sql
select array[1, 2, 3] || 4 as element_append;
```

```sql
 element_append
----------------
 {1,2,3,4}
```

```sql
select array['a', 'b', 'c'] || array['d', 'e', 'f'] as concat_array;
```

```sql
 concat_array  
---------------
 {a,b,c,d,e,f}
```

You can even add an array to a 2-dimensional array:

```sql
select array[1, 2] || array[[4, 5],[6, 7]] as concat_2d_array;
```

```sql
   concat_2d_array   
---------------------
 {{1,2},{4,5},{6,7}}
```

## Removal from an Array

`array_remove()` removes all elements that matches the second parameter.

```sql
select array_remove(round_scores,94) as removed_94 from player_scores;
```

```sql
     removed_94      
---------------------
 {0,92,93,96,98,100}
 {0,95,96,97,98,100}
```

Note: `array_remove()` removes ALL occurences of the matching values

```sql
select array_remove(ARRAY[1,2,3,2,5], 2) as removed_2s;
```

```sql
 removed_2s
------------
 {1,3,5}
```

## Replace Elements in an Array

`array_replace()` replaces all elements that matches the second parameter with the third parameter.

```sql
select array_replace(ARRAY[1,2,3,2,5], 2, 10) as two_becomes_ten;
```

```sql
 two_becomes_ten
-----------------
 {1,10,3,10,5}
```

## Fill an Array

`array_fill()` takes three parametes. `array_fill()` returns an array pre-filled by the value of the first parameter. The second parameter defines how many elements to initialize with the given value. The optional third parameter defines the starting position of the array (defaults to 1).

```sql
insert into player_scores (player_number, round_scores) values
	(10003, array_fill(95,array[5]));
```

_In other words, update the_ `_player_scores_` _table with a new record for player\_number 10003. All 5 of her scores will be 95._

```sql
insert into player_scores (player_number, round_scores) values
	(10004, array_fill(90,array[5],array[3]));
```

_Similarly, you update the_ `_player_scores_` _table with a new record for player\_number 10004. However, his 5 scores of 90, will begin in position 3 in the array._

```sql
 player_number |      round_scores      
---------------+------------------------
         10003 | {95,95,95,95,95}
         10004 | [3:7]={90,90,90,90,90}
```

To shows that the scores array for player 10004 started with element position 3, simply query:

```sql
select
  round_scores[1],
  round_scores[2],
  round_scores[3]
from player_scores
where player_number in (10003, 10004);
```

```sql
 player_number | round_scores | round_scores | round_scores
---------------+--------------+--------------+--------------
         10003 |           95 |           95 |           95
         10004 |              |              |           90
```

How to Modify Arrays

# How to Compare Arrays in PostgreSQL
The **equality operators** (`=`, `<>`) do an exact element-by-element comparison.

```sql
select
array[1,2,3] = array[1,2,4] as compare1, -- arrays are equal
array[1,2,3] <> array[1,2,4] as compare2; -- arrays are not equal
```

```sql
 compare1 | compare2
----------+----------
 f | t
```

The **ordering operators** (`>`, `<`, `>=`, `<=`) also compare each element in an array in order. Results are based on the first different pair of elements, not the sizes of the arrays.

```sql
select
array[1,2,5] >= array[1,2,4] as compare1,
array[1,2,5] <= array[1,2,4,5] as compare2;
```

```sql
 compare1 | compare2
----------+----------
 t | f
```

Then there are the **containment operators** (`@>`, `<@`). They are casually called "bird operators", well, because `@>` looks like a bird. An array is said to be contained in another array if each of its unique elements is also present in the other array.

```sql
-- This reads as array['a', 'b', 'c'] contains array['a', 'b', 'b', 'a']
select array['a', 'b', 'c'] @> array['a', 'b', 'b', 'a'] as contains;
```

```sql
 contains
----------
 t
```

```sql
-- this reads as array[1, 1, 4] is contained by array[4, 3, 2, 1]
select array[1, 1, 4] <@ array[4, 3, 2, 1] as is_contained_by;
```

```sql
 is_contained_by
-----------------
 t
```

Lastly, there is the **overlap operator** (`&&`). Arrays that have elements in common are called overlapping arrays. To check if two arrays overlap, use the `&&` operator:

```sql
select
array[1, 2] && array[2, 3] as overlap1,
array[1, 2] && array[3, 4] as overlap2;
```

```sql
 overlap1 | overlap2
----------+----------
 t | f
```

How to Compare Arrays

# How to Concatenate Strings in PostgreSQL
PostgreSQL offers two ways to concatenate strings. The first uses the `||` operator:

```sql
select 'Join these ' || 'strings with a number ' || 23;
```

```sql
 result               
-------------------------------------
 Join these strings with a number 23
```

You can see from above that PostgreSQL took care of transforming the number to a string to attach it to the rest. Note that you also need to consciously add spaces to make the string readable. For example, in the **customer** table of the Sakila database, to join the first and last names of the customers you have to add a single space in the concatenation:

```sql
select first_name||' '||last_name as customer_name from customer limit 5;
```

```sql
 customer_name   
------------------
 MARY SMITH
 PATRICIA JOHNSON
 LINDA WILLIAMS
 BARBARA JONES
 ELIZABETH BROWN
```

One disadvantage of using the `||` operator is a null value in any of the columns being joined together will result in a null value.

```sql
select 'Null with ||' || 'will make ' || 'everything disappear' || null;
```

```sql
 result
----------
```

Using `concat()` will transform the nulls into empty strings when concatenating:

```sql
select concat('Concat() handles', null, ' nulls better', null);
```

```sql
 concat            
-------------------------------
 Concat() handles nulls better
```

`concat()`accepts multiple parameters, separated by commas.

How to Concatenate Strings

# How to Convert the Case of a String in PostgreSQL
The most basic case conversion functions are `lower()` and `upper()`. Usage is pretty straightforward:

```sql
select lower('Turn this into lOweRCAse');
```

```sql
 lower           
--------------------------
 turn this into lowercase
```

```sql
select upper('capiTalize THis');
```

```sql
 upper      
-----------------
 CAPITALIZE THIS
```

Another useful case conversion function is `initcap()`, which capitalizes the first character of each word and lowers the case of everything else. This is very useful when printing proper nouns and titles and making names stored in uppercase more pleasing to read:

```sql
select
  first_name,
  last_name,
  initcap(concat(first_name, ' ', last_name)) as name
from customer
limit 5;
```

```sql
 first_name | last_name |       name       
------------+-----------+------------------
 MARY       | SMITH     | Mary Smith
 PATRICIA   | JOHNSON   | Patricia Johnson
 LINDA      | WILLIAMS  | Linda Williams
 BARBARA    | JONES     | Barbara Jones
 ELIZABETH  | BROWN     | Elizabeth Brown
```

How to Convert the Case of a String

# How to Create an Array in PostgreSQL
An array is a single data object that holds multiple values.

In PostgreSQL, you can create an array for any built-in or user-defined data type. However, an array can only contain one data type. This means you can have an array of strings, an array of integers, and the like, but you cannot have an array that has both integer and string types.

To create a column of an array type, the `[]` symbol is used. The following examples illustrate this:

```sql
create table contacts (
	first_name varchar,
	last_name varchar,
	phone_numbers varchar[]
);

create table player_scores (
	player_number integer,
	round_scores integer[]
);
```

PostgreSQL also allows multi-dimensional arrays by using multiple pairs of square brackets in the column definition. One requirement here, as you will see later, is that the inner dimensions must have the same array lengths. Here we create a two-dimensional array for the student scores:

```sql
create table student_scores (
	student_number integer,
	test_scores decimal[][]
);
```

To conform to the SQL standard, PostgreSQL also accepts the `ARRAY` keyword for declaring one-dimensional arrays. Shown below is an alternate way to create the contacts and player\_scores tables:

```sql
create table contacts (
	first_name varchar,
	last_name varchar,
	phone_numbers varchar array
);

create table player_scores (
	player_number integer,
	round_scores integer array[10]
);
```

Note the `array[10]` in player\_scores above. PostgreSQL allows you to specify an array size limit, whether you use the `datatype[]` or `datatype array[]` pattern. However, this is just to conform to the SQL standard. As of PostgreSQL 12.2, this is quietly ignored and is not enforced.

Arrays are typically used when a field can have multiple values for an entity, and the values are on their own "complete" and do not merit that they be put to another table for a one-to-many relationship.

How to Create an Array

# How to Insert Data Into an Array in PostgreSQL
There are two accepted syntaxes for inserting data to an array column. The first uses `ARRAY [value1, value2, etc]`:

```sql
insert into contacts (first_name, last_name, phone_numbers)
values ('John', 'Doe', ARRAY ['999-876-5432','999-123-4567']);

insert into player_scores (player_number, round_scores)
values (10001, ARRAY [95, 92, 96, 97, 98] );

-- multi-dimension arrays must have same array lengths for the inner dimensions
insert into student_scores (student_number, test_scores)
values (20001, ARRAY [[1, 95], [2, 94], [3, 98]]);
```

The second one uses single quotes and curly braces.

```sql
insert into contacts (first_name, last_name, phone_numbers)
values ('Bob', 'Parr', '{"555-INC-RDBL"}');

insert into player_scores (player_number, round_scores)
values (10002, '{91, 92, 93, 95, 99}' );

insert into student_scores (student_number, test_scores)
values (20002, '{{1, 96}, {2, 93}, {4, 97}}');
```

Note above that Bob Parr's phone number is now enclosed in double quotations, since we are using the single quotes to wrap the array. Also note that even if there is only one phone number, it still has to be inserted in array form.

How to Insert Data Into an Array

# How to Create a Table in PostgreSQL
Here's an example of creating a `users` table in PostgreSQL:

```sql
create table users (
  id serial primary key, -- Auto incrementing IDs
  name character varying, -- String column without specifying a length
  preferences jsonb, -- JSON columns are great for storing unstructured data
  created_at timestamp without time zone -- Always store time in UTC
);
```

This is also a chance to specify [not null constraints](https://popsql.com/learn-sql/postgresql/how-to-add-a-not-null-constraint-in-postgresql/) and [default values](https://popsql.com/learn-sql/postgresql/how-to-add-a-default-value-to-a-column-in-postgresql/):

```sql
create table users (
  id serial primary key,
  name character varying not null,
  active boolean default true
);
```

You can also create temporary tables that will stick around for the duration of your session. This is helpful to break down your analysis into smaller pieces.

```sql
-- Create a temporary table called `scratch_users` with just an `id` column
create temporary table scratch_users (id integer);

-- Or create a temporary table based on the output of a select
create temp table active_users
as
select * from users where active is true;
```

How to Create a Table

# How to Drop a Table in PostgreSQL
```sql
drop table funky_users;
```

How to Drop a Table

# How to Rename a Table in PostgreSQL
```sql
alter table events rename to events_backup;
```

How to Rename a Table

# How to Truncate a Table in PostgreSQL
Be very careful with this command. It will empty the contents of your PostgreSQL table. This is useful in development, but you'll rarely want to do this in production.

```sql
truncate my_table
```

If you have a serial ID column and you'd like to [restart its sequence](https://popsql.com/learn-sql/postgresql/how-to-alter-sequence-in-postgresql/) (ie restart IDs from `1`):

```sql
truncate my_table restart identity
```

How to Truncate a Table

# How to Duplicate a Table in PostgreSQL
Sometimes it's useful to duplicate a table:

```sql
create table dupe_users as (select * from users);

-- The `with no data` here means structure only, no actual rows
create table dupe_users as (select * from users) with no data;
```

How to Duplicate a Table

# How to Add a Column in PostgreSQL
Here's an example of adding a `created_at` timestamp column to your `users` table in PostgreSQL.

```sql
alter table users add column created_at timestamp without time zone;
```

Adding a string (varchar) column with a not null constraint:

```sql
alter table users add column bio character varying not null;
```

Adding a boolean column with a default value:

```sql
alter table users add column active boolean default true;
```

For reference, check out the [full list of Postgres data types](https://www.postgresql.org/docs/current/static/datatype.html#DATATYPE-TABLE).

How to Add a Column

# PostgreSQL: Change a Column Name
Here's an example of renaming a column in PostgreSQL:

```sql
alter table users rename column registered_at to created_at;
```

How to Change a Column Name

# How to Add a Default Value to a Column in PostgreSQL
```sql
-- Example: Orders have a default total of 0 cents
alter table orders alter column total_cents set default 0;

-- Example: Items are available by default
alter table items alter column available set default true;
```

How to Add a Default Value

# How to Remove a Default Value From a Column in PostgreSQL
Assuming `orders.total_cents` had a default value, this will drop the default for future inserts.

```sql
alter table orders alter column total_cents drop default;
```

How to Remove a Default Value From a Column

# How to Add a Not Null Constraint in PostgreSQL
Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc. If you're validating nulls on the database layer as well, you're protected 💪

```sql
alter table users alter column email set not null;
```

How to Add a Not Null Constraint

# How to Remove a Not Null Constraint in PostgreSQL
```sql
alter table users alter column email drop not null;
```

How to Remove a Not Null Constraint

# PostgreSQL: Add an Index
Having the right indexes are critical to making your queries performant, especially when you have large amounts of data. Here's an example of how to create an index in PostgreSQL:

```sql
create index concurrently "index_created_at_on_users"
on users using btree (created_at);
```

If you want to index multiple columns:

```sql
create index concurrently "index_user_id_and_time_on_events"
on events using btree (user_id, time);
```

Unique indexes to prevent duplicate data:

```sql
create unique index concurrently "index_stripe_event_id_on_stripe_events"
on stripe_events using btree(stripe_event_id);
```

Partial indexes to only index rows where a certain condition is met:

```sql
create index concurrently "index_active_users"
on users using btree(created_at) where active is true;
```

You can also have a _unique_ partial index. For example, imagine if each user can only have one active credit card:

```sql
-- This will prevent any user from having more than one active credit card
create unique index concurrently "index_active_credit_cards"
on credit_cards using btree(user_id) where active is true;
```

How to Add an Index

# How to Drop an Index in PostgreSQL
```sql
drop index index_created_at_on_users;
```

How to Drop an Index

# How to Create a View in PostgreSQL
```sql
create or replace view enriched_users as (
  select *
  from users
  inner join enrichments on enrichments.user_id = users.id
);
```

How to Create a View

# How to Drop a View in PostgreSQL
```sql
drop view enriched_users;
```

How to Drop a View

# PostgreSQL: Reset Sequence Command
If you have a serial ID column (ie auto incrementing ID), they'll start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" and have their own designated table.

If you have a `users.id` column, you'll have a `users_id_seq` table. Some helpful columns in there are `start_value`, which will usually be `1`, and `last_value`, which _could_ be a fast way to see how many rows are in your table if you haven't altered your sequence or deleted any rows.

```sql
select * from users_id_seq;
```

```sql
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 users_id_seq  |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)
```

To alter the sequence so that IDs start a different number, you can't just do an `update`, you have to use the `alter sequence` command.

```sql
alter sequence users_id_seq restart with 1000;
```

How to Reset Sequence

# How to Exclude Current or Partial Weeks in PostgreSQL
Let's say you have a simple query that [groups by week](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/) and looks back at the last 4 weeks:

```sql
select
  date_trunc('week', created_at), -- or hour, day, month, year
  count(1)
from users
where created_at > now() - interval '4 weeks'
group by 1;
```

If you ran this query midweek, say on a Wednesday, the current week would only return data from Monday through Wednesday (~3 days). Given the incomplete week, the last data point would look artificially low:

![query with dip](//images.ctfassets.net/iv1sg9nibjwl/2ESGPfVAGarGbPzzOf0pWd/9395c5666129f966596dd0702769a852/query_with_dip.png)

To avoid this dip (and the inevitable questions from your manager), use the `date_trunc()` function in the `where` clause:

```sql
select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > now() - interval '4 weeks'
group by 1;
```

You now omit any data from the current incomplete week, there's no more dip:

![query without dip](//images.ctfassets.net/iv1sg9nibjwl/58OFuSoEfNEcKNlBo94rEZ/2c6736c87246f44af155aecc2ea4e230/query_without_dip.png)

There's one more problem. If you ran this query mid-week, the starting point of your "look back period" would be in the middle of the week 4 weeks ago. To guard against incomplete weeks in the _beginning_ of your time range, `date_trunc()` can help again:

```sql
select
  date_trunc('week', created_at),
  count(1)
from users
where date_trunc('week', created_at) != date_trunc('week', now())
and created_at > date_trunc('week',now()) - interval '4 weeks'
group by 1;
```
Instead of looking back 4 weeks from `now()`, your query look backs 4 weeks from the _beginning of current week_. See the difference in below:

```sql
select now(); -- Result: 2020-02-05 19:38:26.423589+00
select date_trunc('week',now()); -- Result: 2020-02-03 00:00:00+00
```

How to Exclude Current or Partial Weeks

# How to Use BETWEEN Correctly in PostgreSQL
Be careful when using `BETWEEN` with timestamps. You might end up chopping off a whole day of data 😬

Imagine you were chief safety inspector at a local trampoline park (bonus points if that is your job in real life). You might write a query like this to get a report of accidents in December:

```sql
SELECT *
FROM accidents
WHERE created_at BETWEEN '2019-12-01' AND '2019-12-31'
```

Looks good, right? Nope.

This query would omit any mishaps the whole day of December 31. Why? **Your query only looks from midnight** on Dec 1 to midnight on Dec 31. Any bump, abrasion, or mid-air collision that occurred after midnight on the 31st won't be in your results. The query above is the same as:

```sql
SELECT *
FROM accidents
WHERE created_at >= '2019-12-01 00:00:00.000000'
AND created_at <= '2019-12-31 00:00:00.000000'
```

You can avoid this problem by writing the query:

```sql
SELECT *
FROM accidents
WHERE created_at >= '2019-12-01'
AND created_at < '2020-01-01'
```

The lesson: save `BETWEEN` for discrete quantities like integers. And stay away from trampoline parks. We’ve seen the data and it doesn't look pretty.

How to Use BETWEEN Correctly

# How to Query Date and Time in PostgreSQL
Get the date and time time right now:

```sql
select now(); -- date and time
select current_date; -- date
select current_time; -- time
```

Find rows between two absolute timestamps:

```sql
select count(1)
from events
where time between '2018-01-01' and '2018-01-31'
```

Find rows created within the last week:

```sql
select count(1)
from events
where time > now() - interval '1 week'; -- or '1 week'::interval, as you like
```

Find rows created between one and two weeks ago:

```sql
select count(1)
from events
where time between (now() - '1 week'::interval) and (now() - '2 weeks'::interval);
```

Extracting part of a timestamp:

```sql
select date_part('minute', now()); -- or hour, day, month
```

Get the day of the week from a timestamp:

```sql
-- returns 0-6 (integer), where 0 is Sunday and 6 is Saturday
select date_part('dow', now());

-- returns a string like monday, tuesday, etc
select to_char(now(), 'day');
```

Converting a timestamp to a unix timestamp (integer seconds):

```sql
select date_part('epoch', now());
```

Calculate the difference between two timesetamps:

```sql
-- Difference in seconds
select date_part('epoch', delivered_at) - date_part('epoch', shipped_at); -- or minute, hour, week, day, etc

-- Alternatively, you can do this with `extract`
select extract(epoch from delivered_at) - extract(epoch from shipped_at);
```

How to Query Date and Time

# How to Group by Time in PostgreSQL
When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The PostgreSQL function you need here is `date_trunc`.

```sql
select
  date_trunc('minute', created_at), -- or hour, day, week, month, year
  count(1)
from users
group by 1
```

If you don't have new users every minute, you're going to have gaps in your data. To have one row per minute, even when there's no data, you'll want to use [generate\_series](https://popsql.com/learn-sql/postgresql/how-to-use-generate-series-to-avoid-gaps-in-data-in-postgresql/).

How to Group by Time

# How to Round Timestamps in PostgreSQL
Rounding/truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/). The function you need here is `date_trunc`:

```sql
select date_trunc('second', now()) -- or minute, hour, day, month
```

How to Round Timestamps

How to Convert UTC to Local Time Zone

# How to Use nullif() in PostgreSQL
The `nullif()` function returns a null value, if a the value of the field/column defined by the first parameter equals that of the second. Otherwise, it will return the original value. Here's an example below:

```sql
select
  name,
  platform,
  nullif(platform,'Did not specify') as platform_mod
from users;
```

```sql
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             | Mac
 Bill      | Windows         | Windows
 Linus     | Linux           | Linux
 Beth      | Did not specify |
```

Note that `nullif()` is only capable of replacing one value with null. If you need to replace multiple values, you can use the [CASE function](https://popsql.com/learn-sql/postgresql/how-to-write-a-case-statement-in-postgresql/).

```sql
select
  name,
  platform,
  case
    when platform = 'Mac' then null
    when platform = 'Windows' then null
    when platform = 'Linux' then null
    else platform
  end as platform_mod
from users;
```

```sql
   name    |    platform     | platform_mod
-----------+-----------------+------------
 Steve     | Mac             |
 Bill      | Windows         |
 Linus     | Linux           |
 Beth      | Did not specify | Did not specify
```

How to Use nullif()

# How to Use Lateral Joins in PostgreSQL
Once upon a time, my queries were a mess. I didn’t know how to use lateral joins, so I would copy-and-paste the same calculations over and over again in my queries.

_Co-workers were starting to talk._

Lateral joins allow you to reuse calculations, making your queries neat and legible. Let's learn about lateral joins by rewriting an atrocious query together.

## Data Set

We'll use a [cool sample dataset](https://popsql.com/blog/cool-sample-data) of real Kickstarter projects, if you'd like to follow along.

Relevant columns:

![data table](//images.ctfassets.net/iv1sg9nibjwl/3F0Cn2OhTutbvN29thKDRS/6462da6f00b96d6a7e2e213eac6566d8/data_table.png)

For each Kickstarter project, we want to calculate:

*   total pledged in USD
*   average pledge in USD
*   USD over or under goal
*   duration of the project in days
*   daily shortfall / surplus, _the extra USD needed daily to hit goal_

## Queries, Before and After

**Before:**

```sql
select
    (pledged / fx_rate) as pledged_usd,
    (pledged / fx_rate) / backers_count as avg_pledge_usd,
    (goal / fx_rate) - (pledged / fx_rate) as amt_from_goal,
    (deadline - launched_at) / 86400.00 as duration,
    ((goal / fx_rate) - (pledged / fx_rate)) / ((deadline - launched_at) / 86400.00) as usd_needed_daily
from kickstarter_data;
```

Without lateral joins, see how often I reuse the same calculations:

![repetitive computations](//images.ctfassets.net/iv1sg9nibjwl/5tiBwCsD5VfW8oB0woKuEO/92b5175fb600e9cf83e233286ef29022/repetitive_computations.png)

Yuck. Not only does this make the query difficult to read, it introduces risk of typos or other errors if I ever need to make an update.

**After:**

```sql
select
    pledged_usd,
    avg_pledge_usd,
    amt_from_goal,
    duration,
    (usd_from_goal / duration) as usd_needed_daily
from kickstarter_data,
    lateral (select pledged / fx_rate as pledged_usd) pu
    lateral (select pledged_usd / backers_count as avg_pledge_usd) apu
    lateral (select goal / fx_rate as goal_usd) gu
    lateral (select goal_usd - pledged_usd as usd_from_goal) ufg
    lateral (select (deadline - launched_at)/86400.00 as duration) dr;
```

With lateral joins, I can define the calculation just once. I can then reference those calculations in other parts of my query.

## What's happening?

The `lateral` keyword allows us to access columns after the `FROM` statement, and reference these columns "earlier" in the query ("earlier" meaning "written higher in the query").

SQL queries run in a different order than you might expect. In fact, `FROM` and `JOIN` are the first statements run. Therefore it's no problem to reference columns after the `FROM` statement.

![query order](//images.ctfassets.net/iv1sg9nibjwl/1Ll3rLDmU4J36jRbTlQfxg/eea51ce12cf5cb5ece843886a89e18fa/query_order.png)

Image Credit: [Julia Evans](https://twitter.com/b0rk/status/1179449535938076673)

Word of warning: stick to simple mathematical operations when writing lateral joins for calculations. Aggregate functions like `COUNT()`, `AVG()`, or `SUM()` are not supported.

Happy querying! 🍭

How to Use Lateral Joins

# How to Calculate Percentiles in PostgreSQL
Let's say we want to look at the percentiles for query durations. We can use PostgreSQL's `percentile_cont` function to do that:

```sql
select
  percentile_cont(0.25) within group (order by duration asc) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) as percentile_95
from query_durations
```

If we want to view those percentiles by day:

```sql
select
  day,
  percentile_cont(0.25) within group (order by duration asc) over (partition by day) as percentile_25,
  percentile_cont(0.50) within group (order by duration asc) over (partition by day) as percentile_50,
  percentile_cont(0.75) within group (order by duration asc) over (partition by day) as percentile_75,
  percentile_cont(0.95) within group (order by duration asc) over (partition by day) as percentile_95
from query_durations
group by 1
order by 1 asc
```

How to Calculate Percentiles

# How to Get the First Row per Group in PostgreSQL
Let's say we have an `events` table that belongs to a `user_id`, and we want to see the first event for each user for that day. The function we need here is `row_number`. It's got a tricky syntax that I always forget. Here's an example PostgreSQL query:

```sql
select
  *,
  row_number() over (partition by user_id order by created_at desc) as row_number
from events
where day = '2018-01-01'::date
```

This gives us all the event IDs for the day, plus their `row_number`. Since we only want the first event for the day, we only want rows that have `row_number: 1`. To do that, we can use a [common table expression](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/):

```sql
with _events as (
  select
    *,
    row_number() over (partition by user_id order by created_at desc) as row_number
  from events
  where day = '2018-01-01'::date
)

select *
from _events
where row_number = 1
```

How to Get the First Row per Group

# How to Use generate_series to Avoid Gaps In Data in PostgreSQL
If you're [grouping by time](https://popsql.com/learn-sql/postgresql/how-to-group-by-time-in-postgresql/) and you don't want any gaps in your data, PostgreSQL's `generate_series` can help. The function wants three arguments: `start`, `stop`, and `interval`:

```sql
select generate_series(
  date_trunc('hour', now()) - '1 day'::interval, -- start at one day ago, rounded to the hour
  date_trunc('hour', now()), -- stop at now, rounded to the hour
  '1 hour'::interval -- one hour intervals
) as hour
```

```sql
          hour
------------------------
 2017-12-22 13:00:00-08
 2017-12-22 14:00:00-08
 2017-12-22 15:00:00-08
 2017-12-22 16:00:00-08
 2017-12-22 17:00:00-08
 ...
```

Now you can use a [common table expression](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/) to create a table that has a row for each interval (ie each hour of the day), and then left join that with your time series data (ie new user sign ups per hour).

```sql
with hours as (
  select generate_series(
    date_trunc('hour', now()) - '1 day'::interval,
    date_trunc('hour', now()),
    '1 hour'::interval
  ) as hour
)

select
  hours.hour,
  count(users.id)
from hours
left join users on date_trunc('hour', users.created_at) = hours.hour
group by 1
```

How to Use generate_series to Avoid Gaps In Data

# How to Do Type Casting in PostgreSQL
Here are some examples of common types in PostgreSQL:

```sql
-- Cast text to boolean
select 'true'::boolean;

-- Cast float to integer
select 1.0::integer;

-- Cast integer to float
select '3.33'::float;
select 10/3.0; -- This will return a float too

-- Cast text to integer
select '1'::integer;

-- Cast text to timestamp
select '2018-01-01 09:00:00'::timestamp;

-- Cast text to date
select '2018-01-01'::date;

-- Cast text to interval
select '1 minute'::interval;
select '1 hour'::interval;
select '1 day'::interval;
select '1 week'::interval;
select '1 month'::interval;
```

How to Do Type Casting

# How to Write a Common Table Expression in PostgreSQL
Common table expressions (CTEs) are a great way to break up complex PostgreSQL queries. Here's a simple query to illustrate how to write a CTE:

```sql
with beta_users as (
  select *
  from users
  where beta is true
)

select events.*
from events
inner join beta_users on beta_users.id = events.user_id
```

You can find a real world example of a CTE in [How to Calculate Cumulative Sum/Running Total in PostgreSQL](https://popsql.com/learn-sql/postgresql/how-to-calculate-cumulative-sum-running-total-in-postgresql).

How to Write a Common Table Expression

# How to Import a CSV into PostgreSQL using Copy
Importing a CSV into PostgreSQL requires you to [create a table](https://popsql.com/learn-sql/postgresql/how-to-create-a-table-in-postgresql/) first. [Duplicating an existing table's structure](https://popsql.com/learn-sql/postgresql/how-to-duplicate-a-table-in-postgresql/) might be helpful here too.

The commands you need here are `copy` (executed server side) or `\copy` (executed client side). The former requires your database to be able to access the CSV file, which is rarely going to work for you in a production environment like Amazon RDS because you're not going to be uploading random CSV files to your database server. However, if you use `psql` on your local machine and you're connected to your remote database, you can use the latter `\copy` command, assuming your machine can access the CSV file.

In your terminal, let's open `psql`:

```sql
psql your_database_name # or postgres://username:password@amazonaws.com
```

Now it's time to use the `\copy` command:

```sql
-- Assuming you have already created an imported_users table
-- Assuming your CSV has no headers
\copy imported_users from 'imported_users.csv' csv;

-- If your CSV does have headers, they need to match the columns in your table
\copy imported_users from 'imported_users.csv' csv header;

-- If you want to only import certain columns
\copy imported_users (id, email) from 'imported_users.csv' csv header;
```

How to Import a CSV using Copy

# How to Compare Two Values When One Is Null in PostgreSQL
Imagine you're comparing two PostgreSQL columns and you want to know how many rows are different. No problem, you think:

```sql
select count(1)
from items
where width != height;
```

Not so fast. If some of the widths or heights are null, they won't be counted! Surely that wasn't your intention. That's where `is distinct from` comes into play:

```sql
select count(1)
from items
where width is distinct from height;
```

Now, your count will be "null aware" and you'll get the result you want 💥

How to Compare Two Values When One Is Null

# How to Use Coalesce in PostgreSQL
Say you're looking at a PostgreSQL integer column where some rows are null:

```sql
select
  day,
  tickets
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |   null
 2018-01-03 |     3
```

Instead of having that null, you might want that row to be `0`. To do that, use the `coalesce` function, which returns the first non-null argument it's passed:

```sql
select
  day,
  coalesce(tickets, 0)
from stats;
```

```sql
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |     0
 2018-01-03 |     3
```

How to Use Coalesce

# How to Write a Case Statement in PostgreSQL
Case statements are useful when you're reaching for an if statement in your select clause.

```sql
select
 case
 when precipitation = 0 then 'none'
 when precipitation <= 5 then 'little'
 when precipitation > 5 then 'lots'
 else 'unknown'
 end as amount_of_rain
from weather_data;
```

How to Write a Case Statement

# How to Use Filter to Have Multiple Counts in PostgreSQL
Using `filter` is useful when you want to do multiple counts on a table:

```sql
select
  count(1), -- Count all users
  count(1) filter (where gender = 'male'), -- Count male users
  count(1) filter (where beta is true) -- Count beta users
  count(1) filter (where active is true and beta is false) -- Count active non-beta users
from users
```

How to Use Filter to Have Multiple Counts

# How to Calculate Cumulative Sum-Running Total in PostgreSQL
Let's say we want to see a hockey stick graph of our cumulative user sign ups by day in PostgreSQL. First, we'll need a table with a day column and a count column:

```sql
select
  date_trunc('day', created_at) as day,
  count(1)
from users
group by 1
```

```sql
         day         | count
---------------------+-------
 2018-01-01 00:00:00 |     10
 2018-01-02 00:00:00 |     10
 2018-01-03 00:00:00 |     10
```

Next, we'll write a [PostgreSQL common table expression (CTE)](https://popsql.com/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql/) and use a window function to keep track of the cumulative sum/running total:

```sql
with data as (
  select
    date_trunc('day', created_at) as day,
    count(1)
  from users
  group by 1
)

select
  day,
  sum(count) over (order by day asc rows between unbounded preceding and current row)
from data
```

How to Calculate Cumulative Sum-Running Total

# How to query a JSON column in PostgreSQL

## Querying JSON in PostgreSQL

PostgreSQL is a powerful relational database management system. One of its standout features is its ability to handle unstructured data by allowing you to store it in a JSON column. This means you can enjoy the benefits of a structured relational database while leveraging the flexibility of JSON for certain data types. Below are some common ways to 

## Using PostgreSQL JSON to query a column

### Retrieving a Specific JSON Key as Text

If you have a table named **<code>events</code> and you want to retrieve the value associated with the key <code>name</code> from the JSON column <code>params</code>, you can use the following query:

```sql
SELECT params->>'name' FROM events;
```

This will return the value of `params.name` as text from the `events` table.

### Filtering rows based on a specific JSON key value

If you want to find all events with a specific name, for instance, 'Click Button', you can use:

```sql
SELECT * FROM events WHERE params->>'name' = 'Click Button';
```

This will return all rows from the `events` table where the `name` key in the `params` JSON column has the value 'Click Button'.

### Accessing an element from a JSON array

If your JSON column contains arrays and you want to retrieve the first element (index 0) of the array associated with the key `ids` from the `params` column, you can use:

```sql
SELECT params->'ids'->0 FROM events;
```

This will return the first element of the `ids` array from the `params` column in the `events` table.

### Filtering rows based on a nested JSON key

Sometimes, your JSON might have nested structures. For instance, if you have a table named `users` with a JSON column `preferences` and you want to find users where the nested key `beta` is set to true, you can use:

```sql
SELECT preferences->'beta' FROM users WHERE (preferences->>'beta')::boolean IS TRUE;
```

This query first type casts the value of `preferences.beta` from JSON to boolean and then filters the rows where it's true.

### Querying a JSONb column in PostgreSQL

In PostgreSQL, `jsonb` is a data type used to store JSON (JavaScript Object Notation) data in a more efficient and optimized binary format. It is an extension of the json data type. jsonb stands for **JSON binary**. It provides several advantages over the standard json type, especially when it comes to querying and indexing JSON data.

You can query` jsonb` columns using various JSON functions and operators provided by PostgreSQL. Some commonly used functions and operators include:

* `->`: Extracts a JSON element by key or array index.
* `->>`: Extracts a JSON element as text.
* `#>`: Extracts a JSON sub-object at a specified path.
* `#>>`: Extracts a JSON sub-object as text.
* `@>`: Checks if a JSON document contains another JSON document.
* `&lt;@`: Checks if a JSON document is contained within another JSON document.
* `jsonb_array_elements()`: Expands a JSON array into a set of rows.
* `jsonb_each()`: Expands a JSON object into key-value pairs.

Suppose you have a table called employees with a `jsonb` column named` employee_data`.

```sql
-- Extract the employee's name
SELECT employee_data->>'name' AS employee_name
FROM employees;

-- Check if the employee has a skill in "Sales"
SELECT *
FROM employees
WHERE employee_data->'skills' @> '["Sales"]';

-- Find employees in the "Marketing" department
SELECT *
FROM employees
WHERE employee_data->>'department' = 'Marketing';
```

`jsonb` is a powerful tool for working with JSON data in PostgreSQL, especially when you need to query and manipulate complex JSON structures.

## Fixing issues in querying JSON columns

Troubleshooting JSON column querying in PostgreSQL can involve identifying and addressing issues related to data integrity, query performance, and syntax errors. Troubleshooting JSON column querying in PostgreSQL often requires a combination of SQL knowledge, understanding of JSON data structures, and careful query optimization. By addressing these common issues and best practices, you can improve your ability to work effectively with JSON data in PostgreSQL. Here are some common troubleshooting steps and potential issues to watch out for when querying JSON columns.

### Nested JSON structures

As shown in the tutorial, querying JSON columns is fairly straightforward. However, it can get a bit difficult to query nested JSON structures. It is important to use appropriate JSON operators and functions to navigate and query nested JSON objects and arrays. Functions like` ->`, `->>`, `#>`, and` #>>` can help access nested elements. The` ->` operator returns a JSON object, and` ->>` returns the value as text. By chaining these operators, you can navigate through nested JSON structures to retrieve the desired information.

### Incorrect JSON path

While it seems obvious, more often than you would want, specifying the wrong JSON path in your queries results in incorrect output or failed queries. Examples of such error messages are - `cannot extract elements from a scalar` or `JSON path not found`. Double-check the JSON path you're using in your queries, especially when dealing with nested structures. Use tools like JSON viewers to visualize the JSON structure.

### Error handling

Data quality is an industry wide problem. While we fight this issue daily, lack of error handling in queries can cause errors. Poor quality data results in random missing keys. A single missing key in a large query can disrupt query execution and raise an error. To ensure your queries don’t fail entirely for a few missing keys, implement error handling in your queries. To handle such situations more gracefully, you can use the `COALESCE` function or conditional logic to provide a default value when a JSON key is missing. Instead of complete failure, the query will then return `"Uncategorized"` and you still get the values for which keys are present.

### Database version

Some JSON functions and operators may not be available in older PostgreSQL versions.

Ensure that you're using a PostgreSQL version that supports the JSON functionality you need. Consider upgrading, if necessary.

### Performance bottlenecks

The reason for slow query performance can be inefficient JSON queries. To resolve this, profile your queries using tools like `EXPLAIN` to identify potential bottlenecks. Consider optimizing queries by creating appropriate indexes, rewriting queries, or denormalizing data where necessary. 

## Manage unstructured data like a pro

PostgreSQL's ability to seamlessly integrate structured relational data with the flexibility of unstructured JSON data offers developers a unique advantage. By understanding how to query JSON columns effectively, one can harness the full potential of Postgres, making it easier to manage, retrieve, and analyze diverse datasets. Whether you're accessing specific JSON keys, filtering based on specific values, or diving into nested structures, PostgreSQL provides the tools to do so with precision and efficiency. As data continues to evolve and become more complex, mastering these techniques will be invaluable for any database professional.

## Going beyond JSON querying

Not that you have queried JSON columns successfully, it is time to use the results to gain further insights. Filtering this data is one of the ways to refine the results of JSON querying. Take a look at the tutorial [How to Use Filter to Have Multiple Counts in PostgreSQL](https://popsql.com/learn-sql/postgresql/how-to-use-filter-to-have-multiple-counts-in-postgresql).

## FAQs

### 1. How do I extract a specific JSON key from a JSON column?

 You can use the ``->`` or` `->>`` operators to extract JSON keys.` `->`` returns the value as JSON, while` `->>`` returns the value as text.

### 2. Can I query nested JSON structures in PostgreSQL?
Yes, PostgreSQL allows you to query and extract data from nested JSON structures using nested `->` or `->>` operators.

### 3. How can I filter rows based on JSON data criteria?

 Use the `WHERE` clause to filter rows based on JSON data criteria. For example,` `WHERE json_column->>'key' = 'value'``.

### 4. What's the difference between the `json` and `jsonb` data types for querying JSON in PostgreSQL?

``jsonb`` is a binary JSON data type optimized for querying and indexing, while ``json`` is a plain text JSON data type. ``jsonb`` is recommended for querying JSON data.

### 5. How do I handle missing JSON keys or errors when querying JSON columns?

You can use the` `COALESCE`` function or conditional logic to provide default values or handle missing keys when querying JSON columns.

How to Query a JSON Column

# How to drop a column in PostgreSQL

## Dropping or deleting a column in PosgreSQL

Dropping or deleting a column is typically performed as part of a deliberate schema management process, data cleanup, or in response to changing business requirements or compliance needs. This is a significant and irreversible operation that should be carefully considered.

When a column is no longer needed or contains redundant information but consumes storage and affects query performance, it can be a good idea to drop it. Dropping a column simplifies the table structure, optimizes storage, and aids schema evaluation. If a column contains sensitive or **Personally Identifiable Information (PII)** and you need to comply with data privacy regulations like GDPR, you might need to drop the column to ensure data privacy and security.

## Instructions

Dropping a column is a fairly straightforward operation.

1. To drop a column in PostgreSQL, you can use the `ALTER TABLE` statement with the `DROP COLUMN` clause. Here's the basic syntax for dropping a column:

    ```sql
    ALTER TABLE table_name
    DROP COLUMN column_name;
    ```

2. You need to replace `table_name` with the name of the table that contains the column you want to drop, and `column_name` with the name of the column you wish to remove.

    Here's an example of how to drop a column from a table. Suppose you have a table named `employees` and you want to drop the `phone_number` column.

```sql ALTER TABLE employees DROP COLUMN phone_number; ```
## Errors while dropping a table

When attempting to drop a table in PostgreSQL, several errors can occur, especially if there are dependencies or issues related to permissions, active connections, or other factors. Here are common errors you might encounter when trying to drop a table in PostgreSQL:

### Table Does Not Exist

If the table you're trying to drop does not exist in the database, you'll receive an error like "table does not exist."

 ```sql
ERROR:  table "table_name" does not exist
```

### Dependency Exists

If there are dependencies on the table, such as foreign keys, views, or indexes, PostgreSQL won't allow you to drop the table until you've addressed these dependencies. You might see an error like:

 ```sql
ERROR:  cannot drop table "table_name" because other objects depend on it
DETAIL:  constraint "constraint_name" of table "referencing_table_name" depends on table "table_name"
```

In this case, you need to drop the dependent objects first, or you may use the `CASCADE` option with the `DROP TABLE` command to automatically remove dependent objects. However, be cautious when using `CASCADE` as it will remove all dependent objects without confirmation.

### Access Privileges

If you do not have the required permissions to drop the table, you'll receive an error like:

 ```sql
ERROR:  permission denied for table "table_name"
 ```

You should have the `DROP` privilege on the table to delete it.

### Active Connections

If there are active sessions or open transactions that are using the table, you won't be able to drop it until those sessions are closed or the transactions are completed. You might see an error indicating that there are active connections.

 ```sql
ERROR:  table "table_name" is being used by active queries
```

Ensure that there are no active transactions or connections using the table before attempting to drop it.

### Table in Use by Views or Rules

If the table is being used by views or rules, you'll encounter an error indicating that the table is in use.

 ```sql
ERROR:  cannot drop table "table_name" because it is being used by object "view_name"
```

You must first drop or modify the dependent views or rules before deleting the table.

### Table Contains Data

If the table contains data, PostgreSQL will require you to specify whether you want to delete the data (and the table) or just the table structure. You may receive an error like:

 ```sql
ERROR:  cannot drop table "table_name" because other objects depend on it

DETAIL:  constraint "constraint_name" of table "referencing_table_name" depends on table "table_name"

HINT:  Use DROP ... CASCADE to drop the dependent objects too.
```

In this case, you can either drop the data and table or use the `CASCADE` option to remove dependent objects.

### Recovery Mode

If PostgreSQL is in recovery mode, you won't be able to drop a table. You'll receive an error like:

 ```sql
ERROR:  cannot execute DELETE in a read-only transaction
```

Ensure that you're not in recovery mode when attempting to drop the table.

### Reserved Keywords

If your table name is a reserved keyword in PostgreSQL, you might encounter issues. It's generally a good practice to avoid using reserved keywords as table names.

 ```sql
ERROR:  syntax error at or near "select"
```

To successfully drop a table in PostgreSQL, make sure to address these potential errors by removing dependencies, granting necessary permissions, and ensuring there are no active connections or transactions using the table. Always exercise caution and back up your data before attempting to drop a table, as it is an irreversible operation.

## Dropping a table is permanent

It's important to emphasize that dropping a column is a potentially destructive operation. You should always perform a thorough analysis of the impact before proceeding as this operation cannot be undone. Consider the following:

* **Data Dependencies**: Check for any dependencies on the column, such as in views, indexes, triggers, or stored procedures. You may need to update or remove these objects.
* **Backup**: Ensure you have a recent backup of the data in case you need to restore it.
* **Application Impact**: Be aware of how dropping a column may affect the applications that rely on the table. You may need to modify your application code or queries.
* **Testing**: Consider testing the column drop in a development or staging environment to verify that it won't cause unexpected issues.

## Further table manipulation

Now that you know how to drop a column in PostgreSQL, let us explore how to add a column with this [next tutorial](https://popsql.com/learn-sql/postgresql/how-to-add-a-column-in-postgresql).

## FAQs

### 1. What happens to the data in the column when I delete it?

Deleting a column in PostgreSQL also removes all the data stored in that column. It is an irreversible operation, so make sure to back up your data before proceeding if you need to keep the information.

### 2. What if there are constraints or dependencies on the column I want to delete?

If there are constraints (e.g., foreign keys) or dependencies (e.g., views) that reference the column, you won't be able to delete it directly. You'll need to address these dependencies first, either by dropping or modifying the dependent objects.

### 3. Do I need specific privileges to delete a column?

Yes, you need the appropriate privileges to modify the table. Typically, you must have the `ALTER` privilege on the table to delete a column.

### 4. What is the impact of deleting a column on my application or queries?

Removing a column can impact any SQL queries, views, or applications that reference the deleted column. You will need to update your code and queries to accommodate the change, ensuring they no longer reference the deleted column.


How to drop a column

# How to Query Arrays in PostgreSQL
You can retrieve the contents of an array by specifying it in the `select` clause like any other column:

```sql
select
 first_name,
 last_name,
 phone_numbers
from contacts;
```

```sql
 first_name | last_name | phone_numbers 
------------+-----------+-----------------------------
 John | Doe | {999-876-5432,999-123-4567}
 Bob | Parr | {555-INC-RDBL}
```

You can also specify which element of an array to retrieve by specifying its position inside the square brackets. By default PostgreSQL uses 1 as the first position, though this can be overridden as shown in the `array_fill()` example. The example below shows the first round scores of a player:

```sql
select
 player_number,
 round_scores[1]
from player_scores;
```

```sql
 player_number | round_scores
---------------+--------------
 10001 | 95
 10002 | 91
```

You can also check against the value of a specific element in the `where` clause:

```sql
select *
from player_scores
where round_scores[1] >= 95;
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98}
```

To compare all elements of an array to a value, you can use `ANY/SOME` and `ALL` . `ANY` and its synonym `SOME` will return a row if at least one element satisfies the condition. `ALL` requires all elements to satisfy the condition for a row to be returned. See the examples below:

_Show records where there's at least one score above 95. This is best read as "where 95 is lower than ANY of the scores":_

```sql
select *
from player_scores
where 95 < any (round_scores);
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98}
 10002 | {91,92,93,95,99}
```

_Only show records where 92 is lower or equal to ALL the scores:_

```sql
select *
from player_scores
where 92 <= all (round_scores);
```

```sql
 player_number | round_scores 
---------------+------------------
 10001 | {95,92,96,97,98} 
```

Using `unnest()` expands an array to multiple rows. The non-array columns get repeated for each row.

```sql
select
 first_name,
 last_name,
 unnest(phone_numbers)
from contacts;
```

```sql
 first_name | last_name | unnest 
------------+-----------+--------------
 John | Doe | 999-876-5432
 John | Doe | 999-123-4567
 Bob | Parr | 555-INC-RDBL
```

How to Query Arrays

# How to Use string_agg() in PostgreSQL
`string_agg()` combines non-null values into one string, separated by the delimiter character that you specify in the second parameter. For example, in the Sakila database there's a city and a country table. If you want to show the available cities per country in one line, separated by commas:

```sql
select
  country,
  string_agg(city, ',') as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Songkhla,Nakhon Sawan,Pak Kret
 Faroe Islands        | Trshavn
 Bangladesh           | Jamalpur,Tangail,Dhaka
 United States        | Springfield, Springfield
```

Note that PostgreSQL does not assume a default delimiter. You need to specify it as the second parameter. Also note that like other aggregate functions, you must use the `group by` clause with `string_agg()`.

## Removing duplicates in our output string

Notice that in our results, there are multiple cities in the United States named "Springfield". If you want to omit any duplicates, simply add `distinct` in the first parameter:

```sql
select
  country,
  string_agg(distinct city, ',') as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Songkhla,Nakhon Sawan,Pak Kret
 Faroe Islands        | Trshavn
 Bangladesh           | Jamalpur,Tangail,Dhaka
 United States        | Springfield
```

## Ordering the contents within the output string

Perhaps you also want the output of the `string_agg()` function to be ordered alphabetically. You can specify the order using `order by` after the second parameter:

```sql
select
  country,
  string_agg(distinct city, ',' order by city asc) as cities
from country
join city using (country_id)
group by country
limit 4;
```

```sql
       country        |             cities             
----------------------+--------------------------------
 Thailand             | Nakhon Sawan,Pak Kret,Songkhla
 Faroe Islands        | Trshavn
 Bangladesh           | Dhaka,Jamalpur,Tangail
 United States        | Springfield
```

This can be helpful for particular long outputs, but the `order by` clause is optional.

How to Use string_agg()

# How to Round Timestamps in MySQL
Rounding or truncating timestamps are especially useful when you're [grouping by time](https://popsql.com/learn-sql/mysql/how-to-group-by-time-in-mysql/). If you are rounding by year or date, you can use the corresponding functions:

```sql
SELECT YEAR(now());  -- or DATE();
```

However, care must be done if you are grouping by months. Using `MONTH()` will, for example, make November 2018 and November 2017 both just translate to "11". If that is what you want, then you can use MONTH(). However, if you want to distinguish between months of different years, you need to use `DATE_FORMAT()`:

```sql
SELECT date_format(now(),'%Y-%m'); -- round to the month
SELECT date_format(now(),'%Y-%m-%d'); -- round to the day
SELECT date_format(now(),'%Y-%m-%d %H'); -- round to the hour
SELECT date_format(now(),'%Y-%m-%d %H:%i'); -- round to the minute
```

# dbt build Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data engineers and analysts to transform data in their warehouses more effectively. One of the essential commands in dbt is **`dbt build`**, which allows you to run models, test tests, snapshot snapshots, and seed seeds in your dbt project. This tutorial will guide you through understanding and using the **`dbt build`** command effectively.

## Understanding the dbt build Command

The **`dbt build`** command operates in Directed Acyclic Graph (DAG) order. This means it runs commands based on the dependencies between your models, tests, seeds, and snapshots. When you run **`dbt build`**, it creates two important artifacts: a single manifest and a single run results artifact. The manifest is a file that contains representations of all the resources in your dbt project, while the run results artifact contains detailed information about the output of the **`dbt build`** command, including executed models and tests, the time to run the models, test failure rates, and more.

## Setting Up dbt build

To use **`dbt build`**, you first need to have a dbt project set up. If you don't have one yet, you can create one using the **`dbt init`** command. Once your project is set up, you can use **`dbt build`** with various flags and options to control its behavior. For example, you can use the **`--select`** option to specify which models to run, or the **`--exclude`** option to specify which models to exclude. The **`--resource-type`** flag allows you to filter the resources that **`dbt build`** will operate on.

## Running dbt build

Let's say you have a dbt project with two models: **`orders`** and **`customers`**. The **`orders`** model depends on the **`customers`** model. Here's how you can run **`dbt build`** to build these models:

```bash
dbt build --select orders,customers

```

When you run this command, dbt will first build the **`customers`** model because the **`orders`** model depends on it. If the build for **`customers`** succeeds, dbt will then build the **`orders`** model. If the build for **`customers`** fails, dbt will skip the **`orders`** model.

## Understanding the Output of dbt build

The output of **`dbt build`** provides detailed information about the build process. For each model, test, seed, or snapshot that **`dbt build`** runs, it will show whether the operation was successful or not, and how long it took. If there are any errors, **`dbt build`** will also provide error messages to help you troubleshoot the issues.

For example, if you run **`dbt build`** on the **`orders`** and **`customers`** models, you might see output like this:

```bash
$ dbt build --select orders,customers

Running with dbt=0.21.0
Found 2 models, 0 tests, 0 snapshots, 0 analyses, 341 macros, 0 operations, 0 seed files, 0 sources

14:49:43 | Concurrency: 1 threads (target='dev')
14:49:43 |
14:49:43 | 1 of 2 START table model my_project.customers..................... [RUN]
14:49:43 | 1 of 2 OK created table model my_project.customers................ [SELECT 100 in 0.12s]
14:49:43 | 2 of 2 START table model my_project.orders........................ [RUN]
14:49:43 | 2 of 2 OK created table model my_project.orders................... [SELECT 500 in 0.15s]
14:49:43 |
14:49:43 | Finished running 2 table models in 0.45s.

Completed successfully

Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

```

This output tells you that **`dbt build`** successfully created the **`customers`** and **`orders`** models, selecting 100 and 500 rows respectively.

## Comparing dbt build vs run

While **`dbt build`** and **`dbt run`** might seem similar, they have significant differences. The most noticeable difference is in their operating principles. **`dbt build`** validates tests and then runs the downstream models, while **`dbt run`** runs prior to testing. This means that **`dbt build`** is optimized to obtain better data quality, while **`dbt run`** can lead to data quality issues if not used carefully.

For example, if you have a unique test on a column in the **`customers`** model and you use **`dbt run`** to build the models, **`dbt run`** will not check the unique test before building the **`orders`** model. This could lead to data quality issues in the **`orders`** model if there are duplicate values in the column in the **`customers`** model.

## Best Practices for Using dbt build

When using __`dbt build`__, it's important to follow best practices to ensure that your data transformations are reliable and efficient. Here are some tips:

- __`dbt build`__ is the preferred way to run models in production environments. This ensures that your data transformations are tested before they are run, which can help prevent data quality issues.
- Use the __`--full-refresh`__ flag when there is a change in the schema of the incremental model or if you want to reprocess the logic changes in the models.
- Use [graph operators](https://docs.getdbt.com/reference/node-selection/graph-operators) (__`+`__ before or after model names) or __`--select`__ and __`--exclude`__ to control which models, tests, seeds, and snapshots `dbt build` operates on. This can be useful for testing changes to specific parts of your dbt project.
- Regularly check the output of `dbt build` for errors and warnings. This can help you catch and fix issues early.

## Conclusion

The **`dbt build`** command is a powerful tool for data transformation in dbt. By understanding how it works and following best practices, you can use **`dbt build`** to create reliable and efficient data transformations in your dbt projects. Whether you're a data engineer or a data analyst, mastering **`dbt build`** is a valuable skill that can help you get the most out of dbt.

dbt build

# dbt compile Command: Usage & Examples

## Introduction

Welcome to this tutorial on the **`dbt compile`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. The **`dbt compile`** command is a crucial part of this tool, and it’s used to generate executable SQL from source model, test, and analysis files.

## Understanding the dbt compile Command

The **`dbt compile`** command takes your dbt project’s source files and generates executable SQL code. This code is stored in the **`target/`** directory of your dbt project.

For example, if you have a model file **`orders.sql`** with the following content:

```sql
{{ config(materialized='view') }}

select * 
from raw_data.orders
where order_date >= '2023-01-01'

```

Running **`dbt compile`** will generate a SQL file in the **`target/`** directory that might look something like this:

```sql
CREATE OR REPLACE VIEW analytics.orders AS

select * 
from raw_data.orders
where order_date >= '2023-01-01'

```

## Use Cases for dbt compile

### Inspecting Compiled Output

The **`dbt compile`** command is useful for visually inspecting the compiled output of model files. This is particularly useful for validating complex jinja logic or macro usage.

For example, if you have a model file **`customers.sql`** that uses a macro to calculate the lifetime value of a customer:

```sql
{{ config(materialized='table') }}

select    
  customer_id,    
  {{ lifetime_value('order_total') }} as  lifetime_value

from raw_data.orders

```

Running **`dbt compile`** will allow you to see the actual SQL that gets generated and run against your database.

### Manually Running Compiled SQL

While debugging a model or schema test, it’s often useful to execute the underlying select statement to find the source of the bug. **`dbt compile`** allows you to do this.

### Compiling Analysis Files

**`dbt compile`** can also be used to compile analysis files. These are SQL files that allow you to perform ad-hoc analyses and explorations.

## Common Misconceptions about dbt compile

### Not a Prerequisite for dbt run

A common misconception is that **`dbt compile`** is a prerequisite for **`dbt run`** or other building commands. This is not the case. Those commands will handle compilation themselves.

### Use dbt parse for Validation

If you just want dbt to read and validate your project code, without connecting to the data warehouse, use **`dbt parse`** instead of **`dbt compile`**.

## Advanced Usage of dbt compile

The **`dbt compile`** command accesses the data platform to cache-related metadata and to run introspective queries.

You can use the **`--no-populate-cache`** flag to disable the initial cache population. If metadata is needed, it will be a cache miss, requiring dbt to run the metadata query.

You can also use the **`--no-introspect`** flag to disable introspective queries. If a model’s definition requires running an introspective query and this flag is used, dbt will raise an error.

## Conclusion

In this tutorial, we’ve covered the basics of the **`dbt compile`** command, including what it does, how to use it, and some common misconceptions about it. We’ve also looked at some advanced usage options. With this knowledge, you should be able to use **`dbt compile`** effectively in your dbt projects.

dbt compile

# dbt Debug Command: Usage & Examples

## Introduction

Welcome to this tutorial on the **`dbt debug`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data professionals to transform data in their warehouses more effectively. Debugging is a crucial part of any data transformation process, and dbt provides a built-in command for this purpose: **`dbt debug`**.

## Understanding the dbt debug Command

The **`dbt debug`** command is a utility function that tests the database connection and displays information for debugging purposes. It checks the validity of your project file and your installation of any requisite dependencies (like git). It’s important to distinguish this from the **`--debug`** option, which increases logging verbosity but doesn’t perform the same checks as **`dbt debug`**.

## Using the dbt debug Command

To use **`dbt debug`**, navigate to your dbt project directory and run the command:

```bash
dbt debug

```

This will output information about your dbt installation, your project, and your database connection. If there are any issues with your setup, **`dbt debug`** will highlight them.

## Example: Finding the Location of the profiles.yml File

The **`profiles.yml`** file is where dbt stores database connection information. You can use **`dbt debug`** to find its location:

```bash
dbt debug --config-dir

```

This will output the directory where **`profiles.yml`** is located. To view the file, you can use the **`open`** command:

```bash
open /path/to/profiles.yml

```

Replace **`/path/to/`** with the output from the previous command.

## Common Issues and How to Debug Them

Let’s say you’re working on a business analytics project and you’ve set up a dbt project to transform your sales data. You run your transformations, but the output isn’t what you expected. This is where **`dbt debug`** comes in.

Run **`dbt debug`** and check the output. If there’s an issue with your database connection, **`dbt debug`** will tell you. If your project file is invalid, **`dbt debug`** will tell you. By using **`dbt debug`**, you can quickly identify and fix issues with your dbt setup.

## Conclusion

The **`dbt debug`** command is a powerful tool for identifying and fixing issues with your dbt setup. By using **`dbt debug`**, you can ensure that your data transformations run smoothly and produce the expected results.

dbt debug

# dbt Cheat Sheet: 11 dbt Commands You Need to Know

Welcome to this comprehensive tutorial on dbt commands. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool used by data analysts and engineers to transform data in their warehouses. It follows the principles of transformations as code, version control, modularity, and testing. In this tutorial, we will walk you through the essential dbt commands, provide descriptions for each, and offer syntax examples to help you understand their usage. Let’s dive in!

## Understanding dbt commands

dbt commands are the primary way you’ll interact with dbt. Each command performs a specific action in your dbt project, from creating a new project to running transformations and tests. Understanding these commands and their functions is crucial to effectively using dbt.

## Basic dbt commands

### `dbt init`

This command creates a new dbt project. It sets up the necessary directory structure and provides some example models to get you started. Here’s how you use it:

```bash
dbt init my_new_project
```

### `dbt debug`

This command checks your dbt project setup. It verifies that your profiles.yml file is set up correctly, that dbt can connect to your data warehouse, and that all dependencies are installed. Here’s how you use it:

```bash
dbt debug
```

### `dbt deps`

This command is used to download and manage the dbt packages that your project depends on. It reads your packages.yml file and installs all the packages listed there. Here’s how you use it:

```bash
dbt deps
```

### `dbt clean`

This command cleans up your dbt project by deleting the dbt_modules and target directories. It’s useful when you want to ensure that you’re working with the most recent versions of your dbt packages and models. Here’s how you use it:

```bash
dbt clean
```

## Data Loading and Transformation

### `dbt seed`

This command loads CSV files into your data warehouse. It’s useful when you have small data files that you want to use in your transformations. Here’s how you use it:

```bash
dbt seed
```

### `dbt run`

This command runs transformations in your dbt project. It uses the model SQL files in your project to transform your raw data into analysis-ready tables. Here’s how you use it:

```bash
dbt run
```

### `dbt snapshot`

This command is used to track changes in your data over time. It’s useful when you have slowly changing dimensions and you want to keep a history of changes. Here’s how you use it:

```bash
dbt snapshot
```

## Testing in dbt

### `dbt test`

This command runs tests in your dbt project. It’s used to ensure that your transformations are working as expected and that your data meets the defined quality standards. Here’s how you use it:

```bash
dbt test
```

### `dbt compile`

This command compiles your dbt project to check for errors. It doesn’t run the transformations, but it does generate the SQL that would be run, which can be useful for debugging. Here’s how you use it:

```bash
dbt compile
```

## Documentation and Metadata

### `dbt docs generate`

This command generates documentation for your dbt project. It reads your model, test, and schema files and creates a set of documentation that describes your project. Here’s how you use it:

```bash
dbt docs generate
```

### `dbt docs serve`

This command serves your dbt project documentation locally, allowing you to view it in your web browser. Here’s how you use it:

```bash
dbt docs serve
```

## Conclusion

By now, you should have a good understanding of the essential dbt commands and how to use them in your data transformation projects. Remember, practice is key when it comes to mastering these commands. So, don’t hesitate to create your own dbt project and start transforming data!

dbt commands

# dbt run Command: Usage & Examples

## Introduction

Welcome to this comprehensive tutorial on the **`dbt run`** command. [dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool for data professionals who need to manage and transform data in SQL. The **`dbt run`** command is a core function of dbt, enabling the execution of compiled SQL model files against a specified target database. This is particularly beneficial in business scenarios where raw data needs to be transformed into a more digestible format for analysis and decision-making.

## Understanding the dbt run Command

The **`dbt run`** command is designed to execute your compiled SQL model files against the current target database. It connects to your database and executes the necessary SQL to materialize all data models using the strategies you’ve outlined.

Let’s consider a business scenario where you have a raw sales data table and you want to create a model that aggregates this data by product category. We'll assume you already created a staging model for that table, so we can reference the staging model here. Your SQL model file might look like this:

```sql
-- sales_by_category.sql

{{
  config(
    materialized='table'
  )
}}

select 
  product_category,
  sum(sales_amount) as total_sales 

from {{ref('stg_sales_data')}}

group by product_category

```

To run this model, you would use the **`dbt run`** command in your terminal:

```bash
dbt run --model sales_by_category
```

The command will execute the SQL in the model file against your target database, creating a new table with the aggregated sales data.

## dbt run Model Deployment

When deploying new models, **`dbt run`** handles the process smoothly. It first builds each model with a temporary name, then drops the existing model, and finally renames the new model to its correct name. This process is handled within a single database transaction for database adapters that support transactions, ensuring data integrity.

For instance, if you update the **`sales_by_category.sql`** model to also include the average sales amount per category, **`dbt run`** will create a new version of the **`sales_by_category`** table with the additional data, and then replace the old table with the new one.

## Using the full-refresh Flag

The **`--full-refresh`** flag is a powerful feature of **`dbt run`**. When used, dbt treats [incremental models](https://docs.getdbt.com/docs/build/incremental-models) as table models. This is especially useful when the schema of an incremental model changes and you need to recreate it, or when you want to reprocess the entirety of the incremental model due to new logic in the model code.

For example, if we had a daily version of our **`sales_by_category`** model from earlier and it only added new rows each day, it'd look like this:

```sql
--daily_sales_by_category.sql

select 
  sales_date,
  product_category, 
  sum(sales_amount) as total_sales, 
  avg(sales_amount) as average_sales, 
  count(*) as number_of_transactions

from {{ref('stg_sales_data')}} 

{% if is_incremental() %}

  where sales_date > (select max(sales_date) from {{ this }})

{% endif %}

group by sales_date, product_category

```

In this case if you need to run the model with all your raw data, and not just the days not yet added to the model, you can use the **`--full-refresh`** flag to recreate the table with the new data.

```bash
dbt run --model sales_by_category --full-refresh

```

This will ignore the incremental logic in the `where` statement of the model and run for all days in the source data set.

## Selecting Specific Models to Materialize

**`dbt run`** also allows you to select which specific models you’d like to materialize. This can be useful in scenarios where you may prefer running a different set of models at various intervals, or when you want to limit the tables materialized while you develop and test new models.

For example, if you have a large number of models but you’re currently only working on a few of them, you can use the **`m`** option with **`dbt run`** to only run those models. This can save time and resources during the development and testing process.

```bash
dbt run -m sales_by_category,daily_sales_by_category

```

## Conclusion

We’ve covered a lot in this tutorial, from understanding the **`dbt run`** command and its intelligent multi-threading, to deploying `dbt run` models and using the **`--full-refresh`** flag. Remember, the best way to get comfortable with these concepts is to practice. So, go ahead and start using the **`dbt run`** command in your data transformation projects.

dbt run command

# dbt seed Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the powerful features of dbt is the **`seed`** command, which allows you to load CSV files (referred to as “seeds”) into your data warehouse. This tutorial will guide you through the process of using the **`dbt seed`** command.

## Understanding dbt Seeds

In dbt, seeds are CSV files that you can load into your data warehouse. They are particularly useful for handling static data that changes infrequently. For instance, you might have a CSV file containing a list of country codes and their corresponding country names. This data can be loaded into your warehouse as a seed and referenced in your dbt models.

## dbt seed Use Cases

### Mapping Codes to Descriptive Names

As we’ve seen in the previous example, you can use a seed to map product codes to product names. This can be extended to any scenario where you have codes that need to be mapped to more descriptive names. For example, you might have a seed that maps error codes to error descriptions, or abbreviations to their full forms.

### Excluding Certain Data from Analysis

Suppose you have a list of test email addresses or user IDs that you want to exclude from your analysis. You can create a seed with these email addresses or user IDs, and then use the **`dbt seed`** command to load this data into your warehouse. In your dbt models, you can then exclude these test email addresses or user IDs from your analysis.

### Loading Small Reference Datasets

If you have small reference datasets that don’t change often, it can be more efficient to load them into your warehouse as seeds rather than storing them as tables in your source databases. For example, you might have a small dataset of exchange rates that you want to use in your dbt models.

### Data Validation

You can use seeds to validate the data in your warehouse. For example, you might have a seed that contains the expected results of a certain calculation. You can then create a dbt test that compares the actual results in your warehouse to the expected results in your seed.

### Machine Learning Model Testing

If you’re a data scientist or machine learning engineer, you can use seeds to load test data into your warehouse. You can then use this test data to evaluate the performance of your machine learning models.

## Creating Your First dbt Seed

Let’s create a CSV file to use as a seed. For this tutorial, we’ll use a simple CSV file containing product codes and their corresponding product names. Save the following data in a file named `**product_codes.csv**`:

```
product_code,product_name
PRD01,Apple iPhone 13
PRD02,Samsung Galaxy S21
PRD03,Google Pixel 6

```

Place this file in the **`seeds`** directory of your dbt project.

## Loading dbt Seeds into Your Data Warehouse

To load the seed into your data warehouse, run the following command in your terminal:

```bash
$ dbt seed

```

This command will load all CSV files in the **`seeds`** directory into your data warehouse. If you want to load a specific seed, you can use the **`--select`** flag followed by the name of the seed (without the **`.csv`** extension). For example:

```bash
$ dbt seed --select product_codes

```

## Referencing dbt Seeds in Your Models

Once your seed is loaded into your data warehouse, you can reference it in your dbt models using the **`ref`** function. For example, if you have a model that needs to join with the `**product_codes**` seed, you could do something like this:

```sql
select    
  orders.*,    
  product_codes.product_name

from {{ ref('orders') }} as orders

  left join {{ ref('product_codes') }} as product_codes
    on orders.product_code = product_codes.product_code

```

## Configuring and Testing dbt Seeds

You can configure your seeds in the **`dbt_project.yml`** file. For example, you can specify the schema where the seed data should be loaded. You can also document and test your seeds by declaring properties in YAML. For more information, check out the **[dbt documentation](https://docs.getdbt.com/docs/building-a-dbt-project/seeds)**.

## Conclusion

The **`dbt seed`** command is a powerful tool for loading static data into your data warehouse. By using seeds, you can keep your transformation logic within dbt, ensuring that your data transformations are consistent, version controlled, and code reviewable.

dbt seed

# dbt deps Command: Usage & Examples

## Introduction

Welcome to this tutorial on managing dependencies in [dbt](https://github.com/dbt-labs/dbt-core) using the **`dbt deps`** command. dbt is a powerful tool for transforming raw data into a format that’s useful for analysis. One of its key features is its ability to manage dependencies, ensuring that your data transformations run in the correct order. The **`dbt deps`** command is an integral part of this process.

## Understanding the dbt deps Command

The **`dbt deps`** command is used to manage the dependencies of your dbt project. It pulls the most recent version of the dependencies listed in your **`packages.yml`** file from git. This ensures that your project is always using the most up-to-date versions of its dependencies.

## Using the dbt deps Command

To use the **`dbt deps`** command, simply navigate to your dbt project directory in your terminal and run **`dbt deps`**. Here’s what the output might look like:

```bash
$ dbt deps

Installing fishtown-analytics/dbt_utils@0.7.1  
Installed from version 0.7.1  
Up to date!
Installing dbt-labs/codegen@0.4.0  
Installed from version 0.4.0  
Up to date!
```

This output tells you that dbt has installed the specified versions of the **`dbt_utils`** and **`codegen`** packages, and that these versions are up to date.

## Updating Packages

If you want to update a package to a newer version, you can do so by changing the version number in your **`packages.yml`** file. For example, if you wanted to update **`dbt_utils`** to version **`0.7.2`**, you would change your **`packages.yml`** file to look like this:

```yaml
packages:  
  - package: fishtown-analytics/dbt_utils    
  version: 0.7.2  

  - package: dbt-labs/codegen    
  version: 0.4.0

```

Then, you can run **`dbt deps`** again to install the updated version:

```bash
$ dbt deps

Installing fishtown-analytics/dbt_utils@0.7.2  
Installed from version 0.7.2  
Up to date!
Installing dbt-labs/codegen@0.4.0  
Installed from version 0.4.0  
Up to date!

```

## Troubleshooting Common Issues

If you run into any issues while using the **`dbt deps`** command, the first thing you should do is check the error message. It will often tell you what the problem is. Common issues include not having a **`packages.yml`** file, having a syntax error in your **`packages.yml`** file, or trying to install a version of a package that doesn’t exist.

## Conclusion

The **`dbt deps`** command is a powerful tool for managing your dbt project’s dependencies. By keeping your dependencies up to date, you can ensure that your project is always using the latest and greatest tools and techniques.

dbt deps

# dbt init Command: Creating a New dbt Project

## **Introduction**

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key commands in dbt is **`dbt init`**, which helps you create a new dbt project. This tutorial will guide you through the process of installing dbt, creating a new project, setting up a connection profile, and more.

## **Installation**

Before we start, make sure you have Python and pip installed on your machine. If not, you can download Python from the official website and pip is included in the package.

To install dbt and dbt-init, open your terminal and type:

```bash
pip install dbt dbt-init
```

## **Creating a New Project**

To create a new dbt project, you can use the **`dbt init`** command. For example, if you want to create a new project for a client named “acme_corp” using Snowflake as the warehouse, and you want the project to be located in the **`~/clients/`** directory, you would run:

```bash
dbt-init --client acme_corp --warehouse snowflake --target-dir ~/clients/

```

This command will create a new project in the **`~/clients/`** directory.

## **Setting Up the Connection Profile**

After creating a new project, you need to set up a connection profile. This is done in the **`profiles.yml`** file, which is located in the **`~/.dbt/`** directory.

Open the **`sample.profile.yml`** file in your project directory and update it with the correct details for your client. Replace the placeholders below with your actual details:

```yaml
acme_corp:
 target: dev 
 outputs: 
 dev: 
 type: snowflake 
 account: <account> 
 user: <user>
 role: <role>
 database: <database> 
 warehouse: <warehouse> 
 schema: <schema> 
 threads: <threads> 
 private_key: <private_key>

```

## **Understanding the Project Structure**

After running the **`dbt init`** command, you’ll notice several files and directories in your project directory. The most important one is **`dbt_project.yml`**, which is the main configuration file for your dbt project.

## **Building Out the Starter Project**

In your starter project, there are several variables you can use to customize your project. For example, **`{{ project.name }}`** refers to the name of your project, **`{{ project.warehouse }}`** refers to the warehouse that your client is using, and so on. You can use these variables in your **`dbt_project.yml`** file to customize your project.

## **Testing the Changes**

To test the changes you’ve made to your project, you can run the **`dbt run`** command. This command will execute all models in your project. Here’s an example:

```bash
dbt run
```

## **Conclusion**

Congratulations! You’ve just created a new dbt project, set up a connection profile, and learned how to customize your project. As next steps, you can start creating models in your project and transforming your data.

dbt init

# dbt snapshot Command: Strategies & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a powerful data transformation tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key features of dbt is the ability to create snapshots, which are a way of tracking changes in your data over time. This tutorial will guide you through the process of creating and using dbt snapshots.

## Understanding Slowly Changing Dimensions

Slowly Changing Dimensions (SCDs) are a concept in data warehousing that refers to the ways in which data changes over time. They are called “slowly changing” because the changes, such as a customer’s address or a product’s price, usually occur infrequently, but the impact of these changes can be significant for data analysis.

SCDs are typically categorized into three types:

- **Type 1**: This approach overwrites old data with new data, and therefore, no history is preserved.
- **Type 2**: This approach adds a new record with the new data and preserves the old record as historical data. This is the most common type of SCD and the one that dbt snapshots implement.
- **Type 3**: This approach adds a new column for the new data and preserves the old data in the original column. This type is less common and is used when it’s important to see the progression of changes.

dbt snapshots are directly connected to the concept of SCDs, particularly Type 2 SCDs. A snapshot in dbt is a way of tracking changes in your data over time, effectively creating a version history of each row of data. When you create a snapshot, dbt adds metadata columns to your data, including **`dbt_valid_from`** and **`dbt_valid_to`**, which indicate the time range during which a particular version of a record was valid.

This mechanism allows dbt snapshots to implement Type 2 SCDs. When a change occurs in the source data, instead of overwriting the existing record (Type 1) or adding a new column (Type 3), dbt adds a new record with the new data (Type 2). The **`dbt_valid_from`** and **`dbt_valid_to`** columns in the snapshot table indicate when each version of the record was valid, allowing you to track the full history of changes over time.

## Creating a dbt Snapshot Model

A snapshot model in dbt is a special kind of model that tracks changes in your data over time. To create a snapshot model, you need to create a new file in the **`snapshots`** directory of your dbt project. This file should contain a **`snapshot`** block, which defines the configuration for your snapshot.

Here’s an example of a snapshot model that tracks changes in a **`users`** table:

```sql
{% snapshot users_snapshot %}  

{{
  config(      
    target_schema='snapshots',      
    strategy='timestamp',      
    unique_key='id',      
    updated_at='updated_at'    
  )  
}}  

  select * 
  from raw.users

{% endsnapshot %}

```

In this example, the **`strategy`** is set to **`'timestamp'`**, which means dbt will track changes based on the **`updated_at`** column. The **`unique_key`** is set to **`'id'`**, which is the unique identifier for each row in the **`users`** table.

## Understanding dbt Snapshot Strategies

In dbt, there are two main strategies for creating snapshots: the timestamp strategy and the check strategy.

### Timestamp strategy

The **timestamp strategy** is used when your source data includes a timestamp column that gets updated whenever a record changes. In this strategy, dbt will create a new snapshot record whenever the timestamp column is more recent than the last time the snapshot was run. This strategy is useful when you want to track changes based on when they occurred.

You can see an example of a timestamp strategy in the previous paragraph.

### Check strategy

The **check strategy**, on the other hand, is used when you want to track changes based on the values of specific columns. In this strategy, dbt will create a new snapshot record whenever the values in the specified columns are different from the last time the snapshot was run. This strategy is useful when you want to track changes based on what changed, rather than when it changed.

Here’s an example of a snapshot configuration using the check strategy:

```sql
{% snapshot users_snapshot %}  

{{    
  config(      
    target_schema='snapshots',      
    strategy='check',      
    unique_key='id',      
    check_cols=['status']    
  )  
}}  

select * 
from raw.users

{% endsnapshot %}

```

In this example, dbt will create a new snapshot record whenever the **`status`** column changes for a user.

Choosing the right strategy for your snapshots depends on the nature of your source data and the specific requirements of your use case.

## Running Your Snapshot Models

Once you’ve created your snapshot model, you can run it using the **`dbt snapshot`** command. This command will execute all snapshot models in your dbt project and create a new snapshot table in your data warehouse.

Here’s how you can run your snapshot models:

```bash
dbt snapshot

```

After running this command, you should see a new table in your data warehouse under the **`snapshots`** schema (or whatever schema you specified in your snapshot configuration).

## Working with dbt Snapshot Data

The snapshot table created by dbt contains a full history of your data, with each row representing a different version of a record. You can query this table to see how your data has changed over time.

Here’s an example of a query that shows all changes to the **`status`** column of the **`users`** table:

```sql

select 
  id, 
  status, 
  dbt_valid_from, 
  dbt_valid_to

from snapshots.users_snapshot
order by id, dbt_valid_from;

```

This query will return a result set that shows when each **`status`** value was valid for each user.

## Advanced Snapshot Techniques

In addition to the basic snapshot functionality, dbt also provides several advanced features that can help you manage your snapshots more effectively. For example, you can use the **`invalidate_hard_deletes`** configuration option to track records that have been deleted from your source data.

Here’s how you can modify the previous snapshot model to track hard deletes:

```sql
{% snapshot users_snapshot %}  

{{    
  config(      
    target_schema='snapshots',      
    strategy='timestamp',      
    unique_key='id',      
    updated_at='updated_at',      
    invalidate_hard_deletes=True    
  )  
}}  

select * 
from raw.users

{% endsnapshot %}

```

With this configuration, dbt will create a new row in the snapshot table for each record that is deleted from the **`users`** table, with the **`dbt_valid_to`** column set to the timestamp of the deletion.

## Best Practices and Guidelines

When working with dbt snapshots, there are a few best practices you should follow:

- Keep your snapshots simple: Avoid adding complex logic or joins to your snapshot models. You can always apply this logic downstream after the snapshot is created.
- Use a separate schema for snapshots: This makes it easier to distinguish between snapshot tables and other types of tables in your data warehouse.
- Ensure your unique key is unique: The unique key you specify in your snapshot configuration should uniquely identify each record in your source data.

## Conclusion

dbt snapshots are a powerful tool for tracking changes in your data over time. By following this tutorial, you should now have a good understanding of how to create and use dbt snapshots in your own projects.

dbt snapshot

# `dbt_utils` for Surrogate Key Generation

## Introduction

Welcome to this tutorial on surrogate key generation using dbt's utility package. [dbt](https://github.com/dbt-labs/dbt-core) is a powerful tool for transforming data in the data warehouse. One of its many utilities is the generation of surrogate keys, which are essential for data modeling and analytics.

## Understanding Primary and Surrogate Keys

- **Primary Keys**: These are unique identifiers for records in a table. They ensure no duplicate rows, establish relationships with other tables, and help identify the grain of the table.
- **Surrogate Keys**: When your data doesn't come with a unique primary key, surrogate keys come to the rescue. They are primary keys derived in the analytics layer, ensuring each record has a unique identifier.

## The Basics of `dbt_utils.generate_surrogate_key`

**`dbt_utils`** is a [package for dbt](https://github.com/dbt-labs/dbt-utils) that offers a collection of macros and materializations to simplify common tasks. One such macro is **`generate_surrogate_key`**, which aids in creating surrogate keys.

## Generating Surrogate Keys: A Step-by-Step Guide

- **When to Use**: Whenever your table lacks a unique primary key or when combining data from multiple sources.
- **How to Use**: This macro concatenates the fields and applies a [cryptographic hash](https://en.wikipedia.org/wiki/Cryptographic_hash_function) (using MD5 by default) to produce a unique ID.

```
{{ dbt_utils.generate_surrogate_key('field_a', 'field_b') }}

```

## Handling Null Values in Surrogate Keys

Null values can be tricky when generating surrogate keys. If any value is null, the entire concatenated string might return as null.

- **Solution**: Use the **`coalesce`** function to manage null values. This function replaces null values with a default value, ensuring the concatenated string remains unique.
- **Separator Importance**: To ensure uniqueness, add separators between fields, especially when handling null values.

```
select
  {{ dbt_utils.generate_surrogate_key(
    coalesce(activity_date,'NO_ACTIVITY'), 
    '---', 
    user_id) 
  }} as primary_key, 

  activity_date,
  user_id,
  total_actions

from {{ ref('int__user_daily_activity') }}

```

## Customizing Hashing: Switching from MD5 to SHA

While MD5 is the default hashing function, you might prefer using SHA for its cryptographic advantages.

**How to Switch**: Override the default hashing macro in dbt by adding a macro called **`hash.sql`** to your dbt project's macros directory. The file should include this file:

```
{% macro default__hash(field) -%}
  sha(cast({{ field }} as {{ api.Column.translate_type('string') }}))
{%- endmacro %}

```

## Best Practices for Surrogate Key Generation

- **Consistency**: Ensure you use the same method for surrogate key generation across your project.
- **Data Integrity**: Regularly test your surrogate keys in dbt to ensure data completeness and uniqueness.
- **Avoid Pitfalls**: Be wary of null values and ensure you're using separators to maintain uniqueness.

## Conclusion

Surrogate keys are invaluable in data modeling, especially when dealing with data from diverse sources. With **`dbt_utils`**, the process becomes streamlined, allowing you to focus on analytics rather than data preparation.

## **Additional Resources**

[Official `dbt_utils` Documentation](https://docs.getdbt.com/docs/build/packages)

dbt utils surrogate keys

# dbt run-operation Command: Usage & Examples

## Introduction

### What is dbt?

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative software in analytics and engineering. It bridges the gap between raw data in your warehouse and actionable insights by allowing for sophisticated transformations and modeling.

### The Power of `run-operation`

Among dbt's arsenal of commands, **`run-operation`** stands out. It's not just about invoking macros; it's about automating repetitive tasks, ensuring data integrity, and customizing operations to suit specific business needs.

## `run-operation` Meaning and Usage

At its core, the command is a bridge to your macros, enabling you to execute them with specific arguments.

```bash
$ dbt run-operation {macro} --args '{args}'
```

**Deep Dive**:

- **`{macro}`**: This is your macro's name, which should be descriptive of its function.
- **`-args`**: These are the specific details or parameters for your action. A YAML formatted string. YAML, or "YAML Ain't Markup Language", is a human-readable data serialization format. It's crucial to get the formatting right to avoid errors.

## Practical Examples

The examples below assume that you’ve already created the mentioned macros, and you’re using `run-operation` to execute them either manually from a command line, or through a scheduled action. 

### Example 1: Granting Select Permissions

**Business Scenario**: Imagine a growing company with new hires in the data department. Instead of manually granting permissions, automate the process using this macro.

**Command**: 

```bash
$ dbt run-operation grant_select --args '{role: reporter}'

```

### Example 2: Cleaning Stale Models

**Business Scenario**: As data accumulates, old models can clutter your warehouse. Regularly cleaning them optimizes performance and storage costs.

**Command**: 

```bash
$ dbt run-operation clean_stale_models --args '{days: 7, dry_run: True}'

```

### Example 3: Updating Sales Metrics

**Business Scenario**: Monthly sales reviews are common in businesses. Automate the data gathering process to ensure timely and accurate reports.

**Command**: 

```bash
$ dbt run-operation update_sales_metrics --args '{month: "July", year: 2023}'

```

### Example 4: Archiving Old Customer Data

**Business Scenario**: Compliance, like GDPR, mandates certain data handling practices. Use this operation to ensure you're not retaining customer data longer than necessary.

**Command**: 

```bash 
$ dbt run-operation archive_old_customers --args '{inactive_days: 365}'

```

### Example 5: Generating Monthly Financial Reports

**Business Scenario**: Financial reviews are pivotal for business strategy. Generate tailored financial reports for specific departments to aid in decision-making.

**Command**: 

```
$ dbt run-operation generate_financial_report --args '{month: "August", year: 2023, department: "Sales"}'

```

## Tips and Best Practices

- **Validation**: Always validate your macros and arguments before executing to ensure data integrity.
- **Documentation**: A well-documented macro is easier to use and reduces the risk of errors.
- **Regular Updates**: As your business evolves, so will your data needs. Regularly review and update your macros to stay relevant.

## Troubleshooting Common Issues

- **YAML Formatting**: Common errors include missing spaces or incorrect indentation. Use online YAML validators for assistance.
- **Macro Errors**: Ensure your macro is correctly defined in your dbt project.
- **Argument Issues**: Missing or incorrectly formatted arguments can cause failures. Always refer to your macro's documentation.

## Conclusion

The **`dbt run-operation`** command, when mastered, can be a game-changer. It streamlines processes, ensures data accuracy, and allows for a high degree of customization. As with any tool, the key lies in understanding its potential and applying it effectively.

## Additional Resources

- [dbt macros blog post]

dbt run-operation

# dbt ls (list) Command: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool in the world of analytics, allowing data teams to transform, test, and document their data workflows. One of its powerful commands is **`dbt ls`**, which provides a way to list resources in your dbt project. This tutorial will guide you through its usage, key arguments, and practical examples.

## dbt list (ls) Basic Usage

The **`dbt ls`** command, also known as **`dbt list`**, is designed to list resources in your dbt project. Unlike some other commands, it doesn't connect to the database or run any queries. It only reads your your connection profile to resolve target-specific logic.

```bash
dbt ls

```

## Key Arguments

### Resource Type (`--resource-type`)

This argument allows you to specify the type of resource you want to list, such as models, sources, seeds, or snapshots.

```bash
dbt ls --resource-type model

```

### Selection (`--select` and `--models`)

Use these arguments to filter the nodes or models you want to list. The shorthand for `--select` is `-s` and the shorthand for `--models` is `-m`.

For instance, if you have a sales model and want to list only those, you'd use:

```bash
dbt ls --select intermediate.*

```

### Exclusion (`--exclude`)

If you want to list all models except for a specific model or group of models, you can use the **`--exclude`** argument. The command below will return all models except for the ones that have the `v2` tag.

```bash
dbt ls --resource-type model --exclude tag:v2

```

### Named Selectors (`--selector`)

For complex projects, you might have predefined selectors in a **`selectors.yml`** file. Use this argument to list resources based on those selectors.

```bash
dbt ls --selector my_selector

```

## Advanced Output Options

### Output Format (`--output`)

By default, the output is in a readable format. However, for integration with other tools, you might need a JSON output:

```bash
dbt ls --output json

```

### Output Keys (`--output-keys`)

When using JSON output, you can specify which keys you want to include:

```bash
dbt ls --output json --output-keys name resource_type

```

## Practical dbt ls Examples

### Listing Specific Models for a Retail Business

Suppose you have models related to inventory and want to list all of them:

```bash
dbt ls --select inventory.*

```

### Listing Tests for Financial Reporting

For businesses in the finance sector, ensuring data integrity is crucial. List all tests related to financial data by filtering on the tag `financial` that we’ve applied on all tests in that category:

```bash
dbt ls --select tag:financial --resource-type test

```

### Listing Incremental Models in E-commerce

In e-commerce, data like user activity might be stored incrementally. To list such models:

```bash
dbt ls --select config.materialized:incremental

```

### Output for Integration with Business Dashboards

If you're integrating with a business dashboard tool and need a JSON output:

```bash
$ dbt ls --select sales.* --output json

```

## Common Pitfalls and Tips

- Always ensure that the models you're trying to list are not disabled.
- Familiarize yourself with the connection profile and target-specific logic to avoid unexpected results.

## Conclusion

The **`dbt ls`** command is a versatile tool in the dbt suite, allowing for efficient resource listing and management. By mastering its arguments and understanding its use cases, you can streamline your data workflow and improve your project's organization.


dbt ls

# dbt DAG: Definition, Usage, and Examples

**Overview of dbt**: [dbt](https://github.com/dbt-labs/dbt-core) is a transformation tool that allows data analysts and engineers to transform and model data in the data warehouse. It's a popular tool in the modern analytics engineering workflow.

**DAG in Analytics**: A [Directed Acyclic Graph](https://en.wikipedia.org/wiki/Directed_acyclic_graph) (DAG) is a type of graph where nodes represent tasks and edges represent dependencies between these tasks. In the context of dbt, these tasks are often data models, which are saved as SQL statements and result in data transformations. 

## Basics of DAG

**What is a DAG?**: When people refer to "the DAG" in the context of dbt, they usually mean the graph with directionally related nodes. The nodes can flow left to right and even back in some cases, as long as they don't form a closed loop. A closed loop would mean there's a circular dependency and the system will not work.

**Application**: In businesses, DAGs ensure that data processes run in the correct sequence. For instance, sales data might need to be cleaned and transformed before it's merged with inventory data.

## DAGs in dbt

**dbt's Lineage Graph**: This is a visual representation of your dbt project's DAG. It showcases how different data models relate to each other.

**DAGs and Data Models**: In dbt, each model represents a SQL transformation. The DAG ensures models run in the correct order based on their dependencies.

## Unpacking Relationships with dbt dag

**Upstream and Downstream Models**:

- **Upstream models** are the ones your current model depends on.
- **Downstream models** are those that depend on your current model.

**dbt Lineage Graph**: In this graph, upstream models are to the left, and downstream models are to the right. The arrows indicate the flow of data.

**Example**: Consider a business with sales and inventory data. The **`sales_cleaned`** model (which cleans raw sales data) might be upstream, and a **`sales_inventory_merged`** model (which merges sales and inventory data) would be downstream.

## Auditing Projects with a dbt DAG

- **Unique DAGs**: Every business has unique data needs, leading to unique DAGs.
- **Diagnosing Inefficiencies**: If a model takes too long to run, it might be due to expensive joins or complex logic. The DAG can help identify such bottlenecks.

## Modular Data Modeling in dbt

**Traditional vs. Modular**: Traditional modeling often pulls directly from raw sources. Modular modeling, recommended for scalability, uses intermediary stages.

**Best Practices**:

- **Staging Layers**: Start with a staging model to clean raw data. For instance, **`stg_sales`** for raw sales data.
- **Intermediate Layers**: Transform staged data further, like calculating total sales in **`int_total_sales`**.
- **Mart Layers**: Final transformations, like merging sales and inventory in **`mart_sales_inventory`**.

## Advanced Features in dbt related to DAG

- **[dbt Project Evaluator Package](https://github.com/dbt-labs/dbt-project-evaluator)**: This tool audits your dbt project, suggesting improvements.
- **[dbt Docs](/learn-dbt/dbt-docs)**: A feature that visualizes your DAG, helping you understand data flow and dependencies.
- **[Exposures in dbt](/learn-dbt/dbt-exposures)**: This newer feature lets you define downstream uses of your data models, like dashboards or machine learning pipelines, enhancing transparency.

## Practical Exercise: Building Your Own dbt DAG

**Setting up**: Install dbt and [initiate a new project](/learn-dbt/dbt-init).

### Creating Models

Below is the model code for three models that are all connected in the DAG. 

__Raw sales staging model:__

```sql
-- stg__sales.sql

select 
  date(created_at) as date,
  to_double(amount/100.00) as amount

from {{ source('raw', 'sales') }};

```

__Total sales intermediate model:__

```sql
-- int__total_sales.sql

select 
  date, 
  sum(amount) as total_sales 

from {{ ref('stg__sales') }} 
group by date;

```

__Sales inventory mart:__

```sql
-- mart__sales_inventory.sql

select 
  s.date, 
  s.total_sales, 
  i.total_inventory

from  {{ ref('int__total_sales') }}  s

  inner join {{ ref('stg__inventory') }} i 
    on s.date = i.date;

```

- **Visualizing the DAG**: Run **`dbt run`** to execute the models, then **`dbt docs serve`** to view the Lineage Graph on the hosted docs page. For this example, the DAG would look like this:

![simiple dbt DAG](//images.ctfassets.net/iv1sg9nibjwl/7uioLyGPUUipEoN5h7N0Bm/f68d04a6aa85fea5eefa398aeec0beb2/Screenshot_2023-09-15_at_9.10.12_AM.png)

## Conclusion

- **Recap**: DAGs in dbt ensure data transformations run in the correct order, providing a visual representation of data flow and dependencies.
- **Next Steps**: Explore more [advanced dbt features](https://popsql.com/learn-dbt) and keep refining your DAG for efficiency.

## Additional Resources

- [Learn about dbt docs](/learn-dbt/dbt-docs)
- [How to write a dbt model](/blog/dbt-models)

dbt DAG

# dbt CLI Overview & Install

## Why dbt CLI?

dbt CLI (Command Line Interface) is the heart of dbt. It allows users to run and manage their dbt projects directly from the command line, offering flexibility and integration capabilities.

## Setting Up the dbt CLI

### Requirements

- You'll need a dbt project, so if you don't already have one, you can [use this template](https://github.com/popsql/dbt-template) to get things started. 
- Install [dbt Core](https://github.com/dbt-labs/dbt-core) locally on your computer, or [sign up for PopSQL](https://popsql.com/users/sign_up) and connect your dbt project to skip this step.
- If developing locally, you'll also need a code editor. Popular choices include VSCode and Sublime for writing and managing dbt models.

### Installation for local development

- Download and install dbt Core from the official website or use package managers like **`pip`**.
- Verify the installation by running **`dbt --version`** in your terminal.

### Navigating the CLI

- Use **`pwd`** to check your current directory.
- Navigate using **`cd`** and list files with **`ls`**.
- For assistance, use the **`-help`** flag, e.g., **`dbt --help`** to view available commands.

These navigation issues aren't really necessary if you're doing your dbt development in PopSQL since you can easily navigate your files using the file explorer. You'll only need the CLI for reading logs and triggering advanced commands that aren't available using PopSQL's dbt action button.

![dbt CLI in PopSQL](//images.ctfassets.net/iv1sg9nibjwl/1oDxhzTQuYHew7osrTG2XJ/e2474a9cf534af63d6ee8b7e5fb5ee01/Screenshot_2023-09-15_at_9.59.11_AM.png)

## Creating and Managing a dbt Project using the dbt CLI

### Starting a New Project

- Use [dbt init](/learn-dbt/dbt-init) to create a new dbt project.
- This command generates a directory with essential files and structures for your dbt models, tests, and documentation.

### Setting the Working Directory

Ensure you're in the dbt project directory before running any dbt commands. This is crucial for dbt to recognize and manage your project files. If you're using PopSQL, we'll handle this step automatically.

### Using the `--help` Flag

Whenever in doubt, use **`dbt [command] --help`** to get a detailed explanation and available options for the command. You can also read through [more dbt tutorials here](/learn-dbt).

### Best Practices and Tips

- Always ensure you're in the right directory before running dbt commands.
- Regularly update your dbt CLI to benefit from the latest features and security patches.
- Integrate dbt with CI/CD pipelines for automated testing and deployment, ensuring data quality and consistency.

### Conclusion

- The dbt CLI is a powerful tool for modern data teams, allowing for efficient data transformation and modeling directly from the command line.
- With integrations like Spectacles and Keboola, dbt becomes even more versatile, catering to a wide range of data workflows.
- Try out a hosted dbt option like PopSQL to make setup and management easier, especially if you're not familiar with the local software development processes. 

### Additional Resources

- [PopSQL dbt Documentation](https://docs.popsql.com/docs/dbt)
- [Template dbt project](https://github.com/popsql/dbt-template)


dbt CLI

# dbt Artifacts Package: semantic_manifest, manifest, catalog, run_results, sources

[dbt](https://github.com/dbt-labs/dbt-core) is a transformative tool in the world of data analytics, enabling data professionals to transform and model data in the warehouse. One of its powerful features is the generation of **dbt artifacts**—structured outputs from dbt runs that provide insights into the dbt project and its operations.

## Basics of dbt Artifacts

dbt artifacts are JSON files generated every time dbt runs. They include:

- **`semantic_manifest.json`**: Contains the compiled SQL code for each model.
- **`manifest.json`**: Offers a comprehensive view of your dbt project at the time of the last run.
- **`catalog.json`**: Provides details about the database schema, including column data types and descriptions.
- **`run_results.json`**: Contains the results of the last dbt run, including success or failure status.
- **`sources.json`**: Details about the source data tables used in the project.

These artifacts are essential for documentation, understanding the state of your dbt project, and visualizing source freshness.

## Generating and Accessing Artifacts

Every time you invoke dbt, it generates artifacts. For instance, when you run:

```bash
dbt run

```

dbt will produce artifacts in the **`target/`** directory of your dbt project. You can access these JSON files directly and use tools like dbt's built-in documentation site to visualize their content.

## Practical Application: dbt artifacts Package

Brooklyn Data's **[dbt_artifacts package](https://hub.getdbt.com/brooklyn-data/dbt_artifacts/latest/)** is a powerful tool that models a dbt project and its run metadata. To utilize it:

### Installation

Add the package to your **`packages.yml`**:

```
packages:
  - package: brooklyn-data/dbt_artifacts
    version: 2.5.0

```

### Configuration

Adjust your **`dbt_project.yml`** to specify where data is uploaded:

```
models:
  dbt_artifacts:
    +database: your_destination_database
    +schema: your_destination_schema

```

### Usage

After setting up, run:

```bash
dbt run --select dbt_artifacts

```

## Advanced Usage with Elementary dbt Package

Another package that can be useful when using dbt artifacts is the **[Elementary dbt package](https://hub.getdbt.com/elementary-data/elementary/latest/).** It offers advanced artifact modeling capabilities:

### Uploading Artifacts

Elementary uses macros to extract fields from artifacts and insert them into tables, such as **`dbt_run_results`** and **`dbt_models`**.

### Model Execution

When you make changes to your dbt projects, run:

```bash
dbt run --models dbt_models

```

---

## Practical Examples

### Generate Artifacts for a Business Sales dbt Project

1. Create a dbt project focused on sales data.
2. Run the project using **`dbt run`**.
3. Explore the generated artifacts in the **`target/`** directory.

### Use the dbt Artifacts Package

1. Install the package as described above.
2. Configure it to upload data to a **`sales_artifacts`** schema.
3. Run the package and explore the generated tables.

## Best Practices and Tips

- **Consistency**: Always ensure that your dbt models are consistent in naming and structure. This ensures that your artifacts are reliable.
- **Optimization**: Use the **`run_results.json`** artifact to identify slow-running models and optimize them.
- **Collaboration**: Share your artifacts with team members to ensure everyone is aligned.

## Conclusion and Further Resources

dbt artifacts are a powerful feature that provides deep insights into your dbt projects. By understanding and utilizing them effectively, you can optimize your data transformation processes and ensure data reliability.

- [dbt artifacts package](https://hub.getdbt.com/brooklyn-data/dbt_artifacts/latest/)
- [elementary package](https://hub.getdbt.com/elementary-data/elementary/latest/) for loading artifacts into warehouse tables

dbt artifacts

# dbt clean & clean-targets Commands: Usage & Examples

## Introduction

[dbt](https://github.com/dbt-labs/dbt-core) is an essential tool for data professionals, enabling them to transform, test, and document data in the modern analytics engineering workflow. As with any project, maintaining a clean environment is crucial for efficiency and accuracy. This tutorial will guide you through the **`dbt clean`** command, ensuring your dbt projects remain clutter-free.

## What is the `dbt clean` Command?

The **`dbt clean`** command is a utility function designed to help you manage your dbt project's environment. Its primary purpose is to delete specified folders, ensuring that your project remains organized and free from unnecessary files.

## The Role of `clean-targets` in `dbt_project.yml`

The **`clean-targets`** configuration in the **`dbt_project.yml`** file plays a pivotal role. It provides a list of directories and files that the **`dbt clean`** command should remove, offering a tailored cleaning experience for your project.

## Setting Up `clean-targets`

### Syntax and Structure

To specify which files or directories the **`dbt clean`** command should target, you'll use the **`clean-targets`** configuration. For instance:

```yaml
clean-targets:
  - "target/*"
  - "dbt_modules/*"
  - "sales_data_temp.log"

```

### Placement in the Project

Ensure that the **`clean-targets`** configuration is placed within the **`dbt_project.yml`** file, which should be located in your dbt project's root directory.

### Using Wildcards

Wildcards, represented by the `*****` character, can be incredibly useful. For example, **`target/*`** will match all files and directories within the **`target`** directory, ensuring a thorough cleanup.

## Types of Files and Directories to Clean

In a business context, you might have directories related to specific departments or projects, such as:

- **`finance_reports/`**
- **`marketing_data/`**
- **`temp_files/`**

You might also have log files or temporary data files from various business operations:

- **`Q1_sales_temp.csv`**
- **`employee_data_log.log`**

To ensure these are cleaned, you'd add them to your **`clean-targets`** configuration.

## Dependencies and the `dbt clean` Command

When specifying directories or files in **`clean-targets`**, be aware of dependencies. If a file or directory is crucial for another part of your dbt project, removing it might cause issues. Always review dependencies before running the **`dbt clean`** command.

## Safety Precautions

Before executing the **`dbt clean`** command:

- **Backup**: Ensure you have backups of essential data or files.
- **Review**: Double-check the **`clean-targets`** configuration to avoid unintentional deletions.
- **Test**: Consider running the command in a test environment first.

## Limitations of the `dbt clean` Command

The **`dbt clean`** command doesn't work when interfacing with the RPC server powering the dbt Cloud IDE. In such cases, use the **`dbt deps`** command, which cleans before installing packages. If you're using dbt Cloud, you can manually delete the **`target`** folder from the sidebar file tree.

## Practical Exercise

Let's simulate a business scenario:

Imagine you're handling data for a retail company. Over time, you've accumulated temporary sales data, logs from marketing campaigns, and old finance reports.

1. **Setup**: Create a sample DBT project with directories: **`sales_data/`**, **`marketing_logs/`**, and **`finance_reports/`**.
2. **Populate**: Add some dummy data and logs to these directories.
3. **Configure `clean-targets`**:

```yaml
clean-targets:
  - "sales_data/temp_sales*"
  - "marketing_logs/campaign_2022.log"
  - "finance_reports/Q1_2022_report_temp.csv"

```

4. **Run**: Execute the **`dbt clean`** command and observe the specified files and directories being removed.

## Conclusion

The **`dbt clean`** command is a powerful tool in your dbt toolkit. By understanding its functionality and best practices, you can maintain a streamlined and efficient dbt project environment, especially crucial in dynamic business settings.

## Additional Resources

- [dbt clean documentation](https://docs.getdbt.com/reference/commands/clean)
- [clean-targets documentation](https://docs.getdbt.com/reference/project-configs/clean-targets)

dbt clean

# dbt Exposures: Usage & Examples

[dbt](https://github.com/dbt-labs/dbt-core) has revolutionized the way data teams view and handle transformations. Within this ecosystem, dbt exposures stand out as a powerful feature that bridges the gap between raw data models and their downstream applications. In essence, exposures allow you to define and describe how your dbt project is utilized downstream, be it in a dashboard, application, or data science pipeline.

## Why Use dbt Exposures?

Imagine making a change to a core data model and not knowing how it might affect the monthly sales dashboard the CEO looks at. This is where exposures come in. They provide:

- **Downstream Impact Analysis**: Understand potential impacts of changes made to data models.
- **Time-saving**: No need to reverse engineer data sources or analytics assets.
- **Enhanced Documentation**: Every exposure gets its dedicated page in the autogenerated documentation site, providing clarity to data consumers.

## Defining Exposures: A Step-by-Step Guide

### Understanding Downstream Usage

Before defining an exposure, identify what downstream tools or reports rely on your dbt models. For instance, a sales dashboard in Looker might be built on top of a **`monthly_sales`** dbt model.

### Creating the `.yml` File

Navigate to your dbt project directory. Create or open an existing **`.yml`** file where you'll define your exposures.

### Defining the Exposure Properties

Here's a business-related example:

```yaml
version: 2

exposures:
  - name: sales_dashboard_exposure
    type: dashboard
    owner:
      name: Marty McFly
      email: marty@FrisbiePieCompany.com
    depends_on:
      - ref('monthly_sales')
    description: Monthly sales dashboard used by the executive team.
    url: https://looker.company.com/dashboards/123
    maturity: high

```

### Linking Exposures to dbt Models

The **`depends_on`** property links the exposure to its upstream dbt models. In our example, the Looker dashboard depends on the **`monthly_sales`** dbt model.

## Working with Exposures in dbt

After defining exposures, you can reference them in the [dbt CLI](/learn-dbt/dbt-cli).

__Run models feeding into an exposure:__

```bash
dbt run -s +exposure:sales_dashboard_exposure

```

__Test these models:__ 

```bash 
dbt test -s +exposure:sales_dashboard_exposure

```

## Automating Exposures

While defining exposures manually is powerful, automation tools like AirOps can generate dbt exposures automatically after a data model is built, ensuring exposures are always up-to-date.

## Visualizing Exposures in the dbt Docs Site

Generate your dbt docs with **`dbt docs generate`** and view them with **`dbt docs serve`**. Navigate to the exposures section, and you'll see a dedicated page for each exposure, complete with all the details you provided.

## Best Practices for Using dbt Exposures

- **Regularly Update Exposures**: As your data models evolve, ensure your exposures reflect these changes.
- **Maintain Clear Descriptions**: This aids data consumers in understanding the purpose and context of each exposure.
- **Monitor Maturity Levels**: As a dashboard or report moves from development to production, update its maturity level in the exposure.

## Conclusion

dbt exposures offer a structured way to understand and document the downstream usage of your dbt models. By effectively using exposures, data teams can ensure transparency, traceability, and better communication with business stakeholders.

## Additional Resources

- [dbt documentation on exposures](https://docs.getdbt.com/docs/build/exposures)

dbt Exposures

# dbt Full Refresh: Usage and Examples

[dbt](https://github.com/dbt-labs/dbt-core) has revolutionized the way we handle data transformations in modern data stacks. It allows data analysts and engineers to transform raw data in the warehouse into meaningful datasets. One crucial aspect of dbt is the **full refresh** capability. This tutorial delves deep into this feature, helping you understand and implement it effectively.

## Why dbt Full Refresh?

Imagine a scenario where your company has a model containing all user actions that gets materialized into a table. There are millions of actions in that table, so you set it up to load data incrementally. That means each day, only the actions from the past day get added to the table.

After a few weeks you notice there are some edge cases that cause your source data to drift and become slightly different from the dbt user actions table due to some important manual updates being made to the source table. To ensure your model reflects a perfect copy of the source data, you'd need a mechanism to refresh it entirely. This is where dbt's full refresh comes into play.

## Configuring Full Refresh in dbt

To initiate a full refresh in dbt, you can use the **`--full-refresh`** flag during your dbt run or build. 

```bash
dbt run --full-refresh --models user_actions

```

However, if you want certain models always or never to undergo a full refresh, you can set the **`full_refresh`** configuration in your model's configuration (this can either be set at the top of your model file or in the projects yaml file.

```yaml
{{ config(full_refresh = true) }}

```

## dbt Incremental Models vs. Full Refresh

While full refreshes are powerful, they might not always be the most efficient choice, especially for large datasets. This is where incremental models come in. They allow dbt to process only the new or updated rows, saving time and computational resources.

In some cases, like the example from the introduction about millions of user actions, you might want to trigger incremental refreshes daily, then a full refresh once a week or once a month to ensure all historical data is accurate. 

Another scenario where you might want a full refresh is if the logic of your incremental model changes.

## Trigger Full Refresh on Schema Changes

The **`on_schema_change`** configuration in dbt offers granular control when columns in your incremental model change. For instance, if you add a new column, you can decide whether to:

- Ignore it (**`ignore`**).
- Fail the run (**`fail`**).
- Append the new column (**`append_new_columns`**).
- Sync all columns, including adding new ones and removing missing ones (**`sync_all_columns`**).

Example:

```yaml
{{ config(
  materialized='incremental', 
  on_schema_change='append_new_columns'
) }}

```

## Working with Full Refresh Models

**Scenario**: Your e-commerce company wants to maintain a model of customer profiles. Over time, customers update their profiles, and you want to ensure the model is always up-to-date.

**Step 1**: Set up your dbt environment and create a new model named **`customer_profiles`**.

**Step 2**: Define your model using the **`table`** materialization type:

```sql
--customer_profiles.sql

{{ config(materialized='table') }}

select
    customer_id,
    first_name,
    last_name,
    email,
    last_updated_date

from {{ source('shopify','customers') }}

```

**Step 3**: Run your model:

```sql
dbt run --models customer_profiles
```

**Step 4**: To simulate profile updates, modify some records in your **`shopify.customers`** source.

**Step 5**: Run a full refresh to update the **`customer_profiles`** model:

```sql
dbt run --full-refresh --models customer_profiles
```

**Step 6**: Validate that the **`customer_profiles`** model has the updated records.

## Best Practices

- **Large Datasets**: For models with especially large datasets, consider setting **`full_refresh: false`** to prevent dbt from dropping and recreating them unnecessarily.
- **Schema Changes**: If your source data schema changes frequently, consider using the **`on_schema_change`** configuration to handle these changes gracefully.
- **Optimization**: Regularly monitor the performance of your dbt runs and adjust configurations as needed.

## Conclusion

The ability to perform a full refresh is a powerful feature in dbt, ensuring data accuracy and consistency. By understanding when and how to use it, you can optimize your data transformation processes and maintain high-quality datasets.

## Additional Resources

- [Official dbt documentation](https://docs.getdbt.com/reference/resource-configs/full_refresh)

dbt Full Refresh

# dbt sources & dbt source freshness Commands: Usage & Examples

## Introduction

Data transformation is the backbone of modern analytics, and [dbt](https://github.com/dbt-labs/dbt-core) has emerged as a pivotal player in this space. The **`dbt source`** command, a cornerstone of dbt, allows users to manage and reference raw data tables seamlessly. This tutorial delves into the intricacies of this command, ensuring you can harness its full potential.

## dbt sources Overview

dbt sources represent raw data tables in your data warehouse, serving as the foundation for dbt transformations. Instead of directly referencing these tables, dbt sources provide an abstraction, enhancing maintainability and clarity. By declaring a table as a source, you're marking it as a trusted base for transformations, enabling functionalities like data freshness checks. In business contexts, sources could represent raw sales data, customer interactions, or inventory details.

## Understanding dbt sources

dbt sources are designed to manage source data within dbt. Its primary subcommand, **`dbt source freshness`**, allows you to query the "freshness" of your source tables, ensuring data is up-to-date and reliable.

## Working with Source Data

In analytics, source data is the raw, unprocessed information directly from data stores. For instance, if you're an e-commerce business, your source data might be raw sales transactions, a raw list of customers, and raw historical inventory data. With dbt sources, you can create references to these tables, streamlining the transformation process.

## Using the `dbt source freshness` Command

### Add `loaded_at_field` to your sources yaml file

Before you can use `dbt source freshness` you need to define your sources and a `loaded_at_field` in your sources yaml file(s). In this example we two tables from our Stripe data: `subbscriptions` and `customers` that both have a field called `_synced_at` that tells us when each row was pulled from Stripe.  We’re also including a `freshness` warning if data is more than 2 days old and an error if it’s more than 7 days old. 

```sql
sources:
  - name: stripe
    database: analytics_db
    freshness:
      warn_after: {count: 2, period: day}
      error_after: {count: 7, period: day}
    loaded_at_field: _synced_at

    tables:
      - name: subscriptions
      - name: customers
```

### Use the dbt source freshness

Imagine you want to ensure that your sales transactions data is updated at least once a day. Using **`dbt source freshness`**, you can run:

```bash
dbt source freshness

```

If any source table hasn't been updated as per the specified freshness configuration, dbt will raise a warning or error. In our example above, if the data is 3 days old, we’ll get a warning, since the `warn_after` threshold was set to 2 days. 

## Configuring Source Freshness Output

By default, the freshness information is saved to **`target/sources.json`**. To customize this:

```bash
dbt source freshness --output target/my_custom_path.json

```

## Advanced Usage of dbt Source

### Specifying Sources to Snapshot

If you only want to check the freshness of specific tables, use selectors like `--select`, `--exclude` , etc. This command will check source freshness for only the Stripe Customers source. 

```bash
dbt source freshness --select source:stripe.customers

```

### Understanding Data Lineage

For a business, understanding where data comes from is crucial. Using the **`{{ source() }}`** function, you can define the lineage. For instance, to reference our Stripe customers source in a model:

```sql
select 
  customer_id, 
  customer_name, 
  created_at

from {{ source('stripe', 'customers') }}

```

Most of the time you’ll select fields you need out of each source in a staging model, so each source has its own staging model file. That way you can choose which fields you need from the table and handle renaming and recasting data in one place.

### Testing Your Sources

To ensure data quality, you can write tests for your sources. For instance, to ensure that each Stripe customer has a unique ID:

```yaml
version: 2

sources:
  - name: stripe
    tables:
      - name: customers
        tests:
          - unique:
              column_name: customer_id
```

## Benefits of Using dbt Sources

For businesses, dbt sources offer:

- **Data Consistency**: Ensure that your analytics are based on reliable, consistent data.
- **Efficiency**: Avoid rewriting transformation logic by referencing standardized data.
- **Freshness Monitoring**: Ensure that your business decisions are based on the latest data.

## Common Pitfalls and Best Practices

- **Stay Updated**: Source data can change. Regularly run **`dbt source freshness`** to avoid disruptions.
- **Regular Checks**: Schedule the freshness command to run at regular intervals, ensuring data quality.

## Conclusion

dbt sources are a powerful tool in the dbt arsenal, especially for businesses that rely on timely and accurate data. By mastering the `dbt source freshness` command and setting up sources correctly, you ensure a robust foundation for your analytics.

dbt sources

# dbt docs generate & serve: Command Usage and Examples

## **Introduction**

[dbt](https://github.com/dbt-labs/dbt-core) is a command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively. One of the key features of dbt is its ability to generate documentation for your data models, which is where the **`dbt docs`** command comes into play. This tutorial will guide you through the process of generating and serving your project documentation using dbt.

## **Understanding the dbt docs Command**

The **`dbt docs`** command has two subcommands: **`generate`** and **`serve`**. The **`generate`** command is used to create your project’s documentation, while the **`serve`** command is used to view this documentation in a web browser.

## **Generating Documentation with dbt docs generate**

To generate documentation for your dbt project, navigate to the root of your dbt project in your terminal and run the following command:

```bash
dbt docs generate

```

This command will create a static site with documentation for your project. The site includes information about your models, tests, sources, and more.

### **Serving Documentation Locally with**

After generating the documentation, you can view it locally using the **`serve`** command. Run the following command in your terminal:

```bash
dbt docs serve

```

This will start a web server and open the documentation in your default web browser. You can navigate through the documentation to view information about your dbt project.

### **Exploring the Documentation**

The generated documentation provides a wealth of information about your dbt project. You can switch between a view of your project’s folder hierarchy and a database-focused collection of tables and views using the Project/Database toggle. You can also use the search bar to find specific models in your project.

### **Visualizing Data Lineage**

One of the powerful features of dbt docs is its ability to visualize the relationships between your models. You can access this feature by clicking on the “Lineage” tab in the model’s page. This will show a graph of all the models that are upstream or downstream of the selected model, providing a clear view of your data’s lineage.

### **Uploading Documentation to re_cloud**

If you want to share your documentation with others, you can upload it to re_cloud. To do this, you need to [set up re_cloud](https://docs.getre.io/latest/docs/re_cloud/whatis_cloud) and run the following command in your dbt project directory:

```bash
re_cloud upload dbt-docs --name your_project_name

```

This will upload your generated documentation to re_cloud, where it can be accessed by others in your organization.

## dbt docs Example

Assume we have a dbt project with a simple model that transforms raw sales data into a more useful format. The model is defined in a file called **`sales.sql`**:

```sql
-- models/sales.sql
{{ config(materialized='table') }}

select    
  order_id,    
  product_id,    
  customer_id,    
  quantity,    
  price,    
  quantity * price as total_price,    
  order_date

from raw.sales

```

We also have a **`schema.yml`** file that describes this model:

```yaml
# models/schema.yml
version: 2

models:  
  - name: sales    
  description: This table contains transformed sales data.    

  columns:      
    - name: order_id        
    description: The unique identifier for each order.      

    - name: product_id        
    description: The unique identifier for each product.      

    - name: customer_id        
    description: The unique identifier for each customer.      

    - name: quantity        
    description: The quantity of the product sold in the order.      

    - name: price        
    description: The price of the product.      

    - name: total_price        
    description: The total price of the order, calculated as quantity * price.      

    - name: order_date        
    description: The date the order was placed.

```

Now, we can generate documentation for this model using **`dbt docs generate`**. In your terminal, navigate to the root of your dbt project and run:

```bash
dbt docs generate

```

This command will generate a **`target`** directory in your dbt project. Inside this directory, you’ll find a **`manifest.json`** file and a **`catalog.json`** file. These files contain metadata about your dbt project and are used to generate the documentation website.

Next, you can serve the documentation locally using **`dbt docs serve`**:

```bash
dbt docs serve

```

This command will start a web server and open your default web browser to the documentation site. Here, you’ll see a page for the **`sales`** model with all the column descriptions and other information you defined in the **`schema.yml`** file.

## **Conclusion**

The **`dbt docs`** command is a powerful tool for generating and serving documentation for your dbt projects. By using this command, you can ensure that your team and other stakeholders have access to up-to-date, accurate information about your data models.

dbt docs

dbt Snowflake

# Data Types in BigQuery

BigQuery supports all common data types found in Standard SQL. Google Cloud has [verbose documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types), but here it is presented short and sweet:

## Most Common

| **Name** | **Description** | **Storage Size** | **Note** |
| --- | --- | --- | --- |
| INT64 (Integer) | A whole number that is not a fraction. | 8 bytes | Range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| NUMERIC | A number with 38 digits of precision, 9 of which can be after the decimal point. | 16 bytes | Range from -99999999999999999999999999999.999999999 to 99999999999999999999999999999.999999999 |
| FLOAT64 (Float) | Double precision (approximate) decimal values. | 8 bytes | [Wikipedia will take this one.](https://en.wikipedia.org/wiki/Double-precision_floating-point_format) |
| BOOL (Boolean) | Represented by keywords TRUE and FALSE |  | Case insensitive so `TRUE` or `false` will work. |
| STRING | Variable-length character (Unicode) data. | | Must be quoted with either single (') or double (") quotation marks. Alternatively, can be triple-quoted with groups of three single (''') or three double (""") quotation marks. |
| BYTES | Variable-length binary data. | | Not to be used interchangeably with STRING.  
| BYTES operates on raw bytes rather than Unicode characters. | | | |
| ARRAY | Ordered list of zero or more elements of any non-ARRAY type. |  |  |

### Time Data Types

| **Name** | **Description** | **Canonical Format** | **Note** |
| --- | --- | --- | --- |
| DATE | Represents a logical calendar date, without time. | `YYYY-[M]M-[D]D` |BYTES operates on raw bytes rather than Unicode characters. Range from 0001-01-01 to 9999-12-31 |
| TIME | Represents a time, independent of a specific date. | `[H]H:[M]M:[S]S[.DDDDDD]` | Range from 00:00:00 to 23:59:59.999999 |
| DATETIME | Represents a year, month, day, hour, minute, second, and subsecond _without a timezone_. † | `YYYY-[M]M-[D]D[(|T)[H]H:[M]M:[S]S[.DDDDDD]]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |
| TIMESTAMP | Represents an absolute point in time, with microsecond precision _with a timezone_. | `YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]` | Range from 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 |

† `DATETIME` is seldom used, as it's rare to wish to omit the timezone.

**How to read Canonical Format:**

*   `YYYY`: Four-digit year
*   `[M]M`: One or two digit month
*   `[D]D`: One or two digit day
*   `( |T)`: A space or a `T` separator
*   `[H]H`: One or two digit hour (valid values from 00 to 23)
*   `[M]M`: One or two digit minutes (valid values from 00 to 59)
*   `[S]S`: One or two digit seconds (valid values from 00 to 59)
*   `[.DDDDDD]`: Up to six fractional digits (microsecond precision)
*   `[time zone]`: String representing the time zone, with two canonical formats:
    *   [Time zone name](https://cloud.google.com/dataprep/docs/html/Supported-Time-Zone-Values_66194188) per the tz database
    *   Offset from Coordinated Universal Time (UTC), or the letter Z for UTC

## Less Common

| **Name** | **Description** | **Note** |
| --- | --- | --- |
| STRUCT | A container of ordered fields, each with a type (required) and field name (optional). | [Learn more](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#struct-type) about STRUCT |
| GEOGRAPHY | A collection of points, lines, and polygons, which is represented as a point set, or a subset of the surface of the Earth. | Based on the Open Geospatial Consortium’s (OGC) [Simple Features specification (SFS)](https://www.opengeospatial.org/standards/sfs#downloads) |

Data Types

# Get Started with BigQuery and PopSQL in 5 Minutes

Grab your ⏱ and see if you can beat our 5 min record:

1. Log into your [existing Google Cloud account](https://console.cloud.google.com/home/dashboard) or create a [Google Cloud account for free](https://cloud.google.com/). Just be sure to use the Google Account you'll also use to create a PopSQL account.

2. Create a project within your Google Cloud account. Name it whatever you'd like.

  ![create test project bigquery google cloud console](//images.ctfassets.net/iv1sg9nibjwl/582GaqdASEJVe6WOC55UCX/e1d5c0a0bbc46e63691f54e9882092cf/create_test_project_bigquery_google_cloud_console.png)

3. [Download PopSQL](https://get.popsql.com/).

  > _You can technically use the Google BigQuery web UI, but PopSQL is a modern SQL editor built for teams that works on every platform, for all major databases._

4. Create a [new database connection in PopSQL for BigQuery.](https://popsql.com/docs/getting-started/connecting-to-bigquery/)

    * We recommend connecting via OAuth.
    * Use the same Google Account you used to make your Google Cloud Account.
    * Enter the `Project Name` you created in step 2 as your `BigQuery Project`

5. Want to start querying right away? In PopSQL you can reference any of BigQuery's [surprisingly cool free, public sample datasets](https://popsql.com/learn-sql/bigquery/accessing-bigquery-public-datasets/) 😎.

Get Started with BigQuery and PopSQL in 5 Minutes

# How to add index in MySQL

## Enhancing MySQL query performance with indexing

Indexes are essential tools in databases, ensuring efficient data retrieval. Indexing in MySQL plays a pivotal role in optimizing query performance, especially as your dataset grows. Without the appropriate indexes, you might experience high CPU usage on your database server, slow response times, and ultimately, dissatisfied users. Indexes are data structures that store a subset of the data in a table, organized in a way that allows the database management system to quickly locate and retrieve rows that match specific criteria.

## Getting started with indexing in MySQL

### Using the CREATE INDEX command

1. To add an index to a table in MySQL or create a table index, you can use the `CREATE INDEX` command. The basic syntax is:

        CREATE INDEX index_name ON table_name (column_name);

2. For instance, if you want to add an index to the `email` column in the `users` table, the command would be:

        CREATE INDEX email_idx ON users (email);

### Multiple-column index
1. Sometimes, you might need to add an index that spans multiple columns, especially if those columns are frequently used together in queries. A multiple-column index often performs better than several single-column indexes. The syntax is:

        CREATE INDEX index_name ON table_name (column1, column2);

2. If your `column1` and `column2` contain user IDs and Organization ID, this is how your query would look:

        CREATE INDEX user_id_and_org_id_idx ON users (user_id, org_id);

### Unique index
1. A unique index ensures that the indexed columns do not have duplicate values. This can be particularly useful for columns like email addresses, where uniqueness is crucial. The standard syntax for creating unique index is:

        CREATE UNIQUE INDEX index_name ON table_name (column_name);

2. To create unique index for the `email` column, with the index name `users_email_uq`, here’s the query:

        CREATE UNIQUE INDEX users_email_uq ON users (email);

### Partial index or filtered index

In some cases, you might want to index only a specific portion of a string column instead of an entire table. This type of indexing is particularly useful for indexing historical data, rare or extreme values, or indexing based on status.  For instance, you can create an index on the first 20 characters of a the `name` column that holds company names:
```sql
CREATE INDEX company_part_name_idx ON companies (name(20));
```

### Storage order in index

Starting with MySQL version 8.x, you can specify the storage order of a column in an index. This can be beneficial if you also need to display the column in a particular order. By default, the order is ascending. In the following example, we are changing the order to descending using `DESC`:
```sql
CREATE INDEX reverse_name_idx ON companies (name DESC);
```

### Functional key parts

1. MySQL versions 8.0.13 and above support functional key parts. Functional key parts allow you to create an index on a function of one or more columns rather than on the columns themselves. This feature can be particularly useful in scenarios where you want to index computed values, apply functions to columns, or use expressions in your queries:

        CREATE INDEX index_name ON table_name 
        (expression_function(column_name));

2. In the following example, the `idx_full_name` index is created on the result of the `CONCAT` function applied to the `first_name` and `last_name` columns.

        CREATE INDEX idx_full_name ON employees((CONCAT(first_name, ' ', last_name)));

## Identifying and resolve indexing issues in MySQL

Troubleshooting MySQL indexing issues can help improve the responsiveness and performance of your database queries. Remember to carefully plan and test any index changes in a development or staging environment before applying them to a production database. Indexing decisions should be based on your specific query patterns and use cases, and regular monitoring and maintenance are essential for maintaining optimal performance.

### Missing index

Missing indexes, refer to indexes that have not been created on columns frequently used in query conditions (e.g., in the `WHERE` clause) or in join conditions (e.g., in` JOIN` operations). When these indexes are absent, queries can become inefficient, leading to slower data retrieval and decreased overall database performance.
```sql
SELECT * FROM orders WHERE customer_id = 123;
```
To identify missing indexes, you can use `EXPLAIN` or `EXPLAIN ANALYZE`. For the above example, let us create an index on the `customer_id` column.
```sql
CREATE INDEX idx_customer_id ON orders(customer_id);
```

### Redundant index

Redundant indexes in MySQL refer to indexes that are created on the same set of columns as other existing indexes, providing little to no additional benefit in terms of query performance. These redundant indexes can lead to increased storage requirements, slower data modification operations, and additional maintenance overhead without improving the efficiency of database queries. Let us look at the following example where there are two indexes for the same column `column1`:
```sql
CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column1_column2 ON table1(column1, column2);
```
To resolve the above indexing issue, let us combine redundant indexes into a single, more efficient composite index (single index for `table1`):
```sql
DROP INDEX idx_column1 ON table1;
```

### Composite index order

Sometimes, the order of columns in a composite (multi-column) index does not match the query conditions or the order of columns used in a` JOIN` operation. This can lead to suboptimal query performance.
```sql
SELECT * FROM products WHERE category_id = 1 AND brand_id = 2;
```
Let us create a composite index with columns in the correct order to match the existing index order. Note that this may not always be feasible, especially if you have many queries using different column orders.
```sql
CREATE INDEX idx_category_brand ON products(category_id, brand_id);
```

### Low cardinality index

Low cardinality index is an index on a column that has a relatively small number of unique values compared to the total number of rows in a table. In other words, a low cardinality column has few distinct values, and many rows share the same value. Indexing a column with low cardinality may not improve query performance.
```sql
CREATE INDEX idx_status ON orders(status);
```
Consider whether indexing such a column is necessary or beneficial. In some cases, indexing a low cardinality column may not provide significant benefits, and it may be more efficient to focus on indexing columns with higher selectivity (i.e., columns with many distinct values) or optimizing query design in other ways. Careful consideration should be given to the specific use cases and query patterns in your database to determine whether indexing a low cardinality column is appropriate.

### Index fragmentation

Index fragmentation refers to a condition where the physical storage of index data becomes disorganized or inefficient over time due to data modifications such as` INSERT`,` UPDATE`, and `DELETE` operations. As data in a table changes, the corresponding indexes may become less efficient, leading to performance degradation in query execution.
```sql
OPTIMIZE TABLE your_table;
```
Regularly optimize tables to rebuild indexes and regain performance.

### Large index

Large indexes consume significant storage space and can slow down data modification operations. Evaluate whether such an index is necessary and consider the trade-offs.
```sql
CREATE INDEX idx_large_column ON table1(large_column);
```

### Over-indexing

Having too many indexes can increase maintenance overhead and slow down data modifications.
```sql
CREATE INDEX idx_column1 ON table1(column1);
CREATE INDEX idx_column2 ON table1(column2);
CREATE INDEX idx_column3 ON table1(column3);
```
Review the necessity of each index and remove redundant or unused ones.

### Covering index or index-only query

Covering index is a type of database index that includes all the columns required to fulfill a specific query without the need to access the actual data rows in the table. In other words, a covering index "covers" a query by including all the information needed in the index itself, allowing the database engine to satisfy the query directly from the index structure. This can significantly improve query performance.
```sql
SELECT name, email FROM customers WHERE registration_date >= '2023-01-01';
```
Create a covering index that includes all the columns required by the query.
```sql
CREATE INDEX idx_registration_date ON customers(registration_date, name, email);
```

## MySQL indexes for database optimization

Creating and managing indexes in MySQL is a crucial aspect of database optimization. By understanding and utilizing the various index types and options available, you can ensure efficient data retrieval and optimal database performance. Always remember to monitor and adjust your indexes as your data and query patterns evolve. With this tutorial, you can now create index in MySQL easily and troubleshoot any indexing errors you may encounter.

## Towards next steps

Now that you know how to create indexes, if you want to drop an index, go to the next tutorial, [How to Drop an Index in MySQL using Drop Index and Alter Table Commands](https://popsql.com/learn-sql/mysql/how-to-drop-an-index-in-mysql)

## FAQs

### What is an index in MySQL?

An index in MySQL is a database structure that provides a quick and efficient way to look up rows in a table based on the values in one or more columns. Indexes enhance query performance by reducing the need for full table scans when filtering, sorting, or joining data.

### What types of indexes are available in MySQL?

MySQL supports various types of indexes, including single-column indexes, composite (multi-column) indexes, unique indexes, full-text indexes for text-based searches, and spatial indexes for geospatial data. The choice of index type depends on your specific use case and query patterns.

### When should I use indexes in MySQL?

You should use indexes in MySQL when you frequently query a table based on specific columns in `WHERE` clauses, `JOIN` conditions, or `ORDER BY` clauses. Indexes are most beneficial for read-heavy workloads but should be carefully selected to avoid over-indexing and index maintenance overhead.

### What are some common mistakes to avoid when using indexes in MySQL?

Common mistakes when using indexes include over-indexing (creating too many indexes), not monitoring index usage, neglecting index maintenance, using low cardinality columns for indexing, and not considering the order of columns in composite indexes. Proper indexing strategy and regular monitoring are crucial to avoid these pitfalls.

### How do I check if my queries are using indexes in MySQL?

You can use the `EXPLAIN` statement before your query to view the query execution plan, which provides information about which indexes are used (or not used) in the query. For example:
```sql
EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';
```
Additionally, MySQL provides tools and commands for monitoring index usage, such as the MySQL Performance Schema and the `SHOW INDEX` statement.

How to use SQL Pivot

# How to Insert in MySQL
## Basic

The simplest way to insert a row in MySQL is to use the `INSERT INTO` command and specify values for all columns. If you have 10 columns, you have to specify 10 values and they have to be in order how the table was defined:

```sql
-- Assuming the users table has only three columns: first_name, last_name, and email, and in that order
INSERT INTO users VALUES ('John', 'Doe', 'john@doe.com');
```

## Specifying a Column List

It's optional, but specifying a column list before the `VALUES` keyword is highly recommended:

```sql
INSERT INTO users (first_name, last_name, email, birth_date, city, state)
VALUES ('John', 'Doe', 'john@doe.com','2000-01-01','Los Angeles','CA');
```

Having a column list has the following advantages:

*   You don't have to remember the column order as defined in the table.
*   You don't have to specify a value for all columns, just the required ones.
*   In case there are many columns, it is easier to match a value to the column it's intended for when you see it in the statement, rather than having to look at the table definition.
*   `INSERT` statements without a column lists are invalidated once a column is added or removed from the table. You need to modify your query to reflect the new or deleted column in order for them to work again.

## Using the SET Keyword

Although it is not frequently used, MySQL also allows the `SET` keyword in the `INSERT` statement. This is useful when there are many columns to be inserted because it's easier to read:

```sql
INSERT INTO users SET
  first_name='Vincent',
  last_name='Aviles',
  birth_date='1973-08-14',
  profession='Artist',
  educational_attainment='College Degree',
  city='Daly City',
  current_points=543,
  date_registred='2018-11-30';
```

The limitation of using the `SET` keyword is it can only be used to insert one row at a time.

## Inserting Multiple Rows

You can insert multiple rows in one `INSERT` statement by having multiple sets of values enclosed in parentheses:

```sql
INSERT INTO users (first_name, last_name)
VALUES
  ('John','Lennon'),
  ('Paul','McCartney'),
  ('George','Harrison'),
  ('Ringo','Starr');
```

You can also use `INSERT` with a `SELECT` command to copy data from an existing table. Note the `VALUES` keyword is omitted:

```sql
INSERT INTO beta_users (first_name, last_name)
SELECT first_name, last_name
FROM users
where beta = 1;
```

## Inserting JSON Values

Starting with version 5.7.8, MySQL supports JSON data types. If you want to insert into a JSON column, just wrap the valid JSON in a single quoted string:

```sql
INSERT INTO test_json
VALUES ('{"beta": true, "status": "for review", "test_count": 1}');
```

We also have a tutorial on [querying JSON columns](https://popsql.com/learn-sql/mysql/how-to-query-a-json-column-in-mysql/).

## Handling Conflicts/Duplicates

If inserting a row would violate a unique constraint, there are different ways to handle it, depending on your requirements.

Using `INSERT IGNORE` will quietly discard the row to be inserted:

```sql
INSERT IGNORE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 50);
```

See it in action:

```sql
mysql> create table products
    -> (product_id int not null primary key, product_name varchar(100), stocks int);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 50);
Query OK, 1 row affected (0.01 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 40);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     50 |
+------------+---------------+--------+
1 row in set (0.00 sec)
```

Note that the stocks value did not change.

If you want to do an "upsert" (update the row it exists, otherwise insert), you can use `INSERT... ON DUPLICATE KEY UPDATE`:

```sql
INSERT INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);
```

In action:

```sql
mysql> INSERT INTO products (product_id, product_name, stocks)
    -> VALUES (1, 'VPN Product 1', 45)
    -> ON DUPLICATE KEY UPDATE stocks=VALUES(stocks);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from products;
+------------+---------------+--------+
| product_id | product_name  | stocks |
+------------+---------------+--------+
|          1 | VPN Product 1 |     45 |
+------------+---------------+--------+
1 row in set (0.00 sec)
```

MySQL also has the `REPLACE` keyword. When it detects a duplicate, it will delete the conflicting row and insert the new one.

```sql
REPLACE INTO products (product_id, product_name, stocks)
VALUES (1, 'VPN Product 1', 45)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);
```

Be careful when using `REPLACE` vs `INSERT ON DUPLICATE KEY` because `REPLACE` deletes the entire row, so it affects foreign keys and can produce unexpected results if the table uses `AUTO_INCREMENT` to assign a value.

# How to Update in MySQL
To update all rows in a MySQL table, just use the `UPDATE` statement without a `WHERE` clause:

```sql
UPDATE products SET stocks=100;
```

You can also update multiple columns at a time:

```sql
UPDATE products SET stocks=100, available=true;
```

Usually you only want to update rows that match a certain condition. You do this by specifying a `WHERE` clause:

```sql
--This will update only one row that matches product_id=1
UPDATE products SET stocks=100, available=true
WHERE product_id=1;

--This will update multiple rows that match Category='Electronics'
UPDATE products SET stocks=50, available=true
WHERE category='Electronics';
```

# How to Delete in MySQL
To delete rows in a MySQL table, use the `DELETE FROM` statement:

```sql
DELETE FROM products WHERE product_id=1;
```

The `WHERE` clause is optional, but you'll usually want it, unless you really want to delete every row from the table.

# How to Create a Table in MySQL
Here's an example of creating a `users` table in MySQL:

```sql
CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, -- Auto incrementing IDs
  name VARCHAR(100), -- String column of up to 100 characters
  preferences JSON, -- JSON columns are great for storing unstructured data and are supported starting MySQL version 5.7.8
  created_at TIMESTAMP -- Always store time in UTC
);
```

Within the parentheses are called column definitions separated by commas. The minimum required fields for a column definition are column name and data type, which is what is shown above for columns `name`, `preferences`, and `created_at`. The id column has extra fields to identify it as the primary key column and use an auto-incrementing feature to assign it values.

This is also a chance to specify [not null constraints](https://popsql.com/learn-sql/mysql/how-to-add-a-not-null-constraint-in-mysql/), [default values](https://popsql.com/learn-sql/mysql/how-to-add-a-default-value-to-a-column-in-mysql/), and an optional `ENGINE` keyword:

```sql
CREATE TABLE users (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) not null,
  active boolean default true
) ENGINE=INNODB;
```

Storage engines are MySQL components that handle the SQL operations for different table types. This allows developers to extend capabilities of MySQL. When not specified, the default engine used is INNODB.

# How to Delete a Table in MySQL
Dropping a table in MySQL is simple:

```sql
DROP TABLE inactive_users;
```

Be careful - there's no "undo" for this!

How to Delete a Table

# MySQL: Rename a Table with Alter Table or Rename Table Command
MySQL offers two ways to rename tables. The first one uses the `ALTER TABLE` syntax:

```sql
ALTER TABLE old_table_name RENAME new_table_name;
```

The second way is to use `RENAME TABLE`:

```sql
RENAME TABLE old_table_name TO new_table_name;
```

`RENAME TABLE` offers more flexibility. It allows renaming multiple tables in one statement. This can be useful when replacing a table with a new pre-populated version:

```sql
RENAME TABLE products TO products_old, products_new TO products;
```

The above statement is executed left to right, so there's no conflict naming `products_new` to `products` since the existing table has already been renamed to `products_old`. Furthermore, this is done atomically.

# How to Truncate a Table in MySQL
Be very careful with this command. It will empty the contents of your MySQL table and there is no undo. This is useful in development, but you'll rarely want to do this in production.

```sql
TRUNCATE TABLE table_name;

--the TABLE keyword is actually optional:
TRUNCATE table_name;
```

If the table contains an `AUTO_INCREMENT` column, the counter for it will also get reset. Note that this behavior is different vs other databases where you need to reset the counter yourself.

# How to Duplicate a Table in MySQL
You can duplicate or "clone" a table's contents by executing a `CREATE TABLE ... AS SELECT` statement:

```sql
CREATE TABLE new_table AS SELECT * FROM original_table;
```

Please be careful when using this to clone big tables. This can take a lot of time and server resources.

Note also that `new_table` inherits ONLY the basic column definitions, null settings and default values of the `original_table`. It does not inherit indexes and auto\_increment definitions.

To inherit all table definitions, use the `CREATE TABLE... LIKE` syntax:

```sql
CREATE TABLE new_table LIKE original_table;
```

This makes the structure of `new_table` exactly like that of `original_table`, but DOES NOT copy the data. To copy the data, you'll need `INSERT ... SELECT`:

```sql
INSERT INTO new_table SELECT * FROM original_table;
```

Again, be careful when doing this to big tables.

# How to Add a Column to MySQL Table
Adding a column in MySQL involves using the `ALTER TABLE` command.

Here's an example of adding a `created_at` datetime column to your `users` table:

```sql
ALTER TABLE users ADD created_at DATETIME;
```

Adding a string (varchar) column with a not null constraint:

```sql
ALTER TABLE users ADD bio VARCHAR(100) NOT NULL;
```

Adding a boolean column with a default value:

```sql
ALTER TABLE users ADD active BOOLEAN DEFAULT TRUE;
```

MySQL offers extensive documentation on supported datatypes in their [documentation](https://dev.mysql.com/doc/refman/8.0/en/data-types.html).

# How to Remove a Column in MySQL
Dropping a column in MySQL involves using the `ALTER TABLE` command. The typical syntax is as follows:

```sql
ALTER TABLE table_name DROP COLUMN column_name;

--The COLUMN keyword is actually optional
ALTER TABLE table_name DROP column_name;
```

How to Remove a Column

# How to Change a Column Name in MySQL
## MySQL 5.6.x and 5.7.x

Renaming a column in MySQL involves using the `ALTER TABLE` command. For MySQL version 5.6 .x and 5.7.x, the typical syntax is as follows:

```sql
ALTER TABLE table_name CHANGE old_column_name new_column_name <column definition>;

ALTER TABLE products CHANGE product_name product_full_name VARCHAR(100) NOT NULL;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default. For example, not stating `NOT NULL` will result in the column allowing NULLS.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
 Table: products
Create Table: CREATE TABLE `products` (
 `product_id` bigint(20) NOT NULL,
 `product_name` varchar(100) NOT NULL,

(The rest of the output is truncated for brevity)
```

Then use that as basis for the `ALTER TABLE` command.

## MySQL 8.0

While MySQL 8.0 accepts the above syntax, it also support an easier way:

```sql
ALTER TABLE products RENAME COLUMN product_name TO product_full_name;
```

This is a lot easier since there's no longer a need to restate the full column definition. But if you need to change both the column name and something in the definition, you can use the `ALTER TABLE ... CHANGE` command to do it in one go.

# How to Set a Column with Default Value in MySQL with Alter Table Command
To add a default value to a column in MySQL, use the `ALTER TABLE ... ALTER ... SET DEFAULT` command:

```sql
--Example: Products have a default stock of 0
ALTER TABLE products ALTER COLUMN stocks integer SET DEFAULT 0;

--Example: Products are available by default (removed optional COLUMN keyword)
ALTER TABLE products ALTER available SET DEFAULT true;
```

How to Set a Column with Default Value

# How to Remove a Default Value to a Column in MySQL
To remove a default value to a column in MySQL, use the `ALTER TABLE ... ALTER ... DROP DEFAULT` command:

```sql
--Example: Products have a default stock of 0
ALTER TABLE products ALTER COLUMN stocks integer DROP DEFAULT;

--Example: Products are available by default (removed optional COLUMN keyword)
ALTER TABLE products ALTER available DROP DEFAULT;
```

How to Remove a Default Value to a Column

# How to Add a Not Null Constraint in MySQL
Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc. The only way to really be sure is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected 💪

To enforce `NOT NULL` for a column in MySQL, you use the `ALTER TABLE .... MODIFY` command and restate the column definition, adding the `NOT NULL` attribute.

```sql
--Example: Products have a default stock of 0
ALTER TABLE products MODIFY stocks INT NOT NULL;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) DEFAULT '',
  `stocks` int(11) DEFAULT '0',

(The rest of the output is truncated for brevity)
```

Use the current definition and add `NOT NULL` for the correct modification:

```sql
ALTER TABLE products MODIFY stocks INT NOT NULL DEFAULT 0;
```

# How to Remove a Not Null Constraint in MySQL
To remove a `NOT NULL` constraint for a column in MySQL, you use the `ALTER TABLE .... MODIFY` command and restate the column definition, removing the `NOT NULL` attribute.

```sql
--Example: Products have a default stock of 0
ALTER TABLE products MODIFY stocks INT;
```

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value.

To ensure that you do not miss anything, you can use the `SHOW CREATE TABLE` command to see the full column definition:

```sql
mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) DEFAULT '',
  `stocks` int(11) NOT NULL DEFAULT '0',

(The rest of the output is truncated for brevity)
```

Use the current definition and remove the NOT NULL for the correct modification:

```sql
ALTER TABLE products MODIFY stocks INT DEFAULT 0;
```

# How to Drop an Index in MySQL using Drop Index and Alter Table Commands
To drop a non-primary key index, use the `DROP INDEX` command:

```sql
DROP INDEX index_name ON table_name;
```

The syntax requires the table name to be specified because MySQL allows index names to be reused on multiple tables.

Primary keys in MySQL are always named `PRIMARY` (not case sensitive). But because `PRIMARY` is a reserved keyword, backticks are required when you use it in the `DROP INDEX` command.

```sql
--Enclose PRIMARY in backticks to refer to the name, not the reserved word
DROP INDEX `PRIMARY` ON table_name;
```

Alternatively, MySQL also allows to drop indexes using the `ALTER TABLE` command:

```sql
-- Drop a non-primary key index
ALTER TABLE table_name DROP INDEX index_name;

-- Drop the primary key. This time we are using the reserved words so no backticks
ALTER TABLE table_name DROP PRIMARY KEY;
```

# How to Create a View in MySQL
Views allow to encapsulate or "hide" complexities, or allow limited read access to part of the data.

To create a view, use the `CREATE VIEW` command:

```sql
CREATE OR REPLACE VIEW view_name AS <select statement>;
```

While optional, the `OR REPLACE` part is frequently used so the the view is updated if already exists.

Some examples:

```sql
-- A view to show only beta users
CREATE VIEW beta_users_vw AS
SELECT * FROM users WHERE beta = 1;

-- A view to limit read access to only certain columns
CREATE VIEW users_basic_vw AS
SELECT first_name, last_name, telephone_number
FROM users;

-- A view for management so they only need to do a "SELECT * FROM top_20_customers_vw" instead of learning a complex SQL
CREATE OR REPLACE VIEW top_20_customers_vw AS
SELECT c.customer_name, sum(p.price*od.quantity) order_total
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_details od USING (order_id)
JOIN products p USING (product_id)
GROUP BY c.customer_name
ORDER BY order_total DESC
LIMIT 20;
```

You may notice all view names above end in `_vw`. This is an example convention some developers adopt to easily distinguish views from tables.

# How to Drop a View in MySQL
To drop a view, use the `DROP VIEW` command:

```sql
DROP VIEW view_name;
```

# How to Alter Sequence in MySQL
Auto incrementing columns in tables start at 1 by default, but sometimes you may want them to start at a different number. These numbers are known as "sequences" in other databases but are implemented differently in MySQL. An auto incrementing column is part of table definition and is modified using the `ALTER TABLE` command.

First, you can check for the next value using the `SHOW CREATE TABLE COMMAND`:

```sql
mysql> SHOW CREATE TABLE users\G;
*************************** 1. row ***************************
 Table: users
Create Table: CREATE TABLE `users` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT,
 <<other lines omitted for brevity>>
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2
```

To change it:

```sql
ALTER TABLE users AUTO_INCREMENT=1000;
```

How to Alter Sequence

# How to Query Date and Time in MySQL

MySQL has the following functions to get the current date and time:

```sql
SELECT now();  -- date and time
SELECT curdate(); --date
SELECT curtime(); --time in 24-hour format
```

To find rows between two dates or timestamps:

```sql
SELECT *
FROM events
where event_date between '2018-01-01' and '2018-01-31';

-- Can include time by specifying in YYYY-MM-DD hh:mm:ss format:
SELECT *
FROM events
WHERE event_date BETWEEN '2018-01-01 12:00:00' AND '2018-01-01 23:30:00';
```

To find rows created within the last week:

```sql
SELECT *
FROM events
WHERE event_date > date_sub(now(), interval 1 week);
```

There's also `DATE_ADD()`. For example, to find events scheduled between one week ago and 3 days from now:

```sql
SELECT *
FROM events
WHERE event_date BETWEEN date_sub(now(), interval 1 week) AND date_add(now(), interval 3 day);
```

You can extract part of a timestamp by applying the corresponding function:

```sql
SELECT year(now()); -- or month(), day(), hour(), minute(), second()
```

To get a day of week from a timestamp, use the `DAYOFWEEK()` function:

```sql
-- returns 1-7 (integer), where 1 is Sunday and 7 is Saturday
SELECT dayofweek('2018-12-12');

-- returns the string day name like Monday, Tuesday, etc
SELECT dayname(now());
```

To convert a timestamp to a unix timestamp (integer seconds):

```sql
-- This will assume time to be 12am
SELECT unix_timestamp('2018-12-09');

-- You can specify an exact timestamp to be converted down to the second
SELECT unix_timestamp('2018-12-09 14:53:21');

-- calling unix_timestamp without a parameter will be like calling it for current timestamp
SELECT unix_timestamp(); -- same as SELECT unix_timestamp(now());
```

To calculate the difference between two timestamps, convert them to unix timestamps then perform the subtraction:

```sql
-- show seconds between delivery and shipping timestamps
SELECT unix_timestamp(delivered_at) - unix_timestamp(shipped_at);
FROM deliveries;

-- convert computed difference to hh:mm:ss format:
SELECT sec_to_time(unix_timestamp(delivered_at) - unix_timestamp(shipped_at))
FROM deliveries;
```

Note that MySQL also has `DATEDIFF()` and `TIMEDIFF()` functions, but they can only used with purely date values or purely time values, respectively.

# How to Group by Time in MySQL

When you want to group by minute, hour, day, week, etc., it's tempting to just group by your timestamp column, however, then you'll get one group per second, which is likely not what you want. Instead, you need to "truncate" your timestamp to the granularity you want, like minute, hour, day, week, etc. The function you need here is `DATE_FORMAT`:

```sql
SELECT
   date_format(created_at,'%Y-%m-%d %H-%i'), -- leave out -%i if you want to group by hour
   count(1)
FROM users
GROUP BY 1;
```

Grouping by date is easier as you can just use the \`DATE() function:

```sql
SELECT
   date(created_at),
   count(1)
FROM users
GROUP BY 1;
```

If you don't have new users for every minute/hour/day, you're going to have gaps in your data. To have one row per interval, even when there's no data, you'll want to [generate data](https://popsql.com/learn-sql/mysql/how-to-avoid-gaps-in-data-in-mysql/) for it.

# How to Convert UTC to Local Time Zone in MySQL

MySQL does not store time zone data when storing timestamps. It also uses the host server time as basis for generating the output of `NOW()`;

To convert a timestamp from one time zone to another, you can use the `CONVERT_TZ` function:

```sql
-- using named time zones
SELECT CONVERT_TZ('2018-01-01 12:00:00','UTC','MET');

-- using offset time zones
SELECT CONVERT_TZ('2018-01-01 12:00:00','+00:00','+10:00');
```

PostgreSQL

Ready for a modern SQL editor?