Scenario

You work for a social media analytics company and have been given a table containing information about social media posts across multiple platforms.

Task

Write a Snowflake SQL query that:

1. Selects all columns from {{ ref("social_media") }}
2. Replaces every occurrence of "Python" in the text column with "PySpark"
3. Returns the same 7 columns as the input (preserving the original column names)

Schema

social_media

Example

social_media:

Expected Output:

Note: Every occurrence of "Python" in the text column is replaced with "PySpark". All other columns remain unchanged.

SELECT
    id,
    REPLACE(text, 'Python', 'PySpark') AS text,
    date,
    likes,
    comments,
    shares,
    platform
FROM {{ ref("social_media") }}

Explanation

Step 1: Using REPLACE for string substitution

REPLACE(text, 'Python', 'PySpark') AS text

The REPLACE function takes three arguments: the source string, the substring to find, and the replacement string. It replaces every occurrence of the target substring within the source. In Snowflake, REPLACE is case-sensitive, so 'Python' will only match that exact casing.

Step 2: Listing all columns explicitly

SELECT
    id,
    REPLACE(text, 'Python', 'PySpark') AS text,
    date,
    likes,
    comments,
    shares,
    platform

Because we need to transform the text column while keeping the same column name, we list all 7 columns explicitly and alias the replaced column as text. Using SELECT * with an additional REPLACE(...) expression would produce a duplicate text column, which is why the explicit column list is necessary.

Step 3: Alternative with CTE

You can also wrap the query in a CTE for readability:

WITH corrected AS (
    SELECT
        id,
        REPLACE(text, 'Python', 'PySpark') AS text,
        date,
        likes,
        comments,
        shares,
        platform
    FROM {{ ref("social_media") }}
)
SELECT * FROM corrected

Both produce identical results. The CTE version can be easier to read when you add more transformation steps later.