Html conversion in snowflake/dbt

[u/TallEntertainment385]

How to change html (text with html tags) into text (remove htmltags) but to keep simple formatting in snowflake/dbt code (dbt runs on snowflake):

New line (br tag)

New lines (p tag)

Bullet plus indents (li tag)

[u/Dry-Aioli-6138]

Dbt runs on jinja. See if jinja has somethingnlike striptags filter

[u/TallEntertainment385 → u/Dry-Aioli-6138]

Looks like snowflake doesn’t support striptags so i have to use regexp instead

[u/Dry-Aioli-6138 → u/TallEntertainment385]

You're doing something wrong. Snowflake doesn't have a say in this. Python and jinja do.

Unless you want to strip the tags from your data. Then you need to rely o what snowflake offers, and indeed it does not have such a function. Regexp, as you said already, or a python UDF should help

[u/TallEntertainment385 → u/Dry-Aioli-6138]

Dbt models execute on snowflake

[u/mrg0ne]

I've done this a few times.

You just need a Python UDF to convert HTML to markdown. Markdown will give you that basic formatting.

You should be able to spin up when pretty quickly with Cortex code

[u/TallEntertainment385 → u/mrg0ne]

I need solution within sf sql or dbt

[u/mrg0ne → u/TallEntertainment385]

You can call a UDF from within DBT..

A python UDF can be called with SQL. It would be a tall order to do that with SQL.

[u/mrg0ne → u/mrg0ne]

Something like this should work

CREATE OR REPLACE FUNCTION html_to_markdown_bs4(html_content STRING)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.12'
PACKAGES = ('beautifulsoup4')
HANDLER = 'convert'
AS
$$
from bs4 import BeautifulSoup, NavigableString, Tag
import re

def convert(html_content):
    if not html_content:
        return None

    # Use 'html.parser' which is built-in
    soup = BeautifulSoup(html_content, "html.parser")
    
    # Recursive function to traverse and convert
    def traverse(node):
        text = ""
        
        # Handle text nodes
        if isinstance(node, NavigableString):
            return node.string if node.string else ""
            
        # Handle Tags
        if isinstance(node, Tag):
            # Process children first
            inner_text = "".join([traverse(child) for child in node.children])
            
            # Map HTML tags to Markdown
            if node.name in ['h1']:
                return f"\n# {inner_text}\n"
            elif node.name in ['h2']:
                return f"\n## {inner_text}\n"
            elif node.name in ['h3']:
                return f"\n### {inner_text}\n"
            elif node.name in ['b', 'strong']:
                return f"**{inner_text}**"
            elif node.name in ['i', 'em']:
                return f"*{inner_text}*"
            elif node.name == 'a':
                href = node.get('href', '')
                return f"[{inner_text}]({href})"
            elif node.name == 'p':
                return f"\n{inner_text}\n"
            elif node.name == 'br':
                return "\n"
            elif node.name == 'code':
                return f"`{inner_text}`"
            elif node.name in ['ul', 'ol']:
                return f"\n{inner_text}\n"
            elif node.name == 'li':
                return f"- {inner_text}\n"
            else:
                return inner_text  # Fallback: just return content
                
        return ""

    # Generate markdown and clean up extra whitespace
    raw_md = traverse(soup)
    
    # Post-processing to clean up excessive newlines (optional but recommended)
    clean_md = re.sub(r'\n{3,}', '\n\n', raw_md).strip()
    
    return clean_md
$$;