DEV Community: Trevor Fox

Beginner's Guide to Content Management Systems and Templating Engines

Trevor Fox — Tue, 25 Feb 2020 17:04:02 +0000

If you're new to web development—especially if you are coming from adjacent territory like marketing or product management, you've probably begun to understand the basics of variables and for loops but there's a big gap between where you are and how things get done in the wild. The goal of this post is to introduce you to the wild world of content management and tame it at the same time. Hope you enjoy!

Redefining the Content Management System Category

The term “Content Management System” describes an actively expanding category of software that can be used to manage the creation and modification of digital content. You may think that definition seems broad but that’s because the category is really broad! In fact, **Wikipedia doesn't even include serving content as a requirement for this definition!

On top of the content creation-and-modification functionality, most CMS’s provide a much wider range of offerings. This starts, of course, with actually serving the content as HTML pages (rather than just text or image files) and includes common services like handling ecommerce transactions, managing user credentials and web analytics.

Here are a few members of the Venn diagram that is the CMS category:

Wordpress: The most popular CMS started as a blog platform but now is capable of supporting any type of app from ecommerce shops to user-review websites.
Shopify: The popular ecommerce platform is essentially a CMS that focuses on managing product content and handling monetary transactions.
Dropbox: You might not consider the **digital asset management software **a CMS, the app allows you to upload images and serve them publicly on the web.
Hubspot: The customer relationship management (CRM) system, also offers a blog and landing page CMS with the personalization benefits that only a CRM could.
Ghost: One of the most popular in a category of “headless CMS,” Ghost serves content via an API which is perfect Javascript-based single-page apps (SPAs).
Webflow: A codeless CMS, it affords 80% of the content and design capabilities of a code-based CMS like Wordpress, without needing to write custom code to use them.
Pelican: On the far corner of the CMS world is this static site generator written in Python. Pelican simply translates a collection of text files (Markdown is commonly used) into a website that can be hosted on services like.

From headless to full-stack, codeless to code-only, feature-rich to barely-a-CMS, you get the idea: the space is huge.

There is a CMS for almost any use case: If you don’t want to use a database, use a static site generator like Pelican or Jekyll. If you want to build a front end but don’t want to worry about the back end, use a headless CMS. If you want to use a CMS with a ton of community support and familiar developers, use Wordpress. The list goes on.

No matter your use case, there are some general principles that apply to most CMS that are good to understand. That’s what we’ll get into next.

Beyond Content: Themes and Templates

If you are working on SEO but aren’t really familiar with web development, you might have heard some of these terms like “theme,” “templating,” and “rendering,” and wonder what they’re all about. Let’s fix that.

CMS Themes

Most CMS, including Wordpress, Drupal, and Shopify employ a concept of themes (even if they call them something else). Themes are the CSS, Javascript, and HTML template files that package CMS content into a complete website experience.

The combination of these files in a browser creates the “look-and-feel” of a website: CSS files define visual characteristics like background colors, typography, and iconography while JS files create animations and interactivity. HTML templates determine the layout of the content on a page whether that’s in a phone, tablet, or computer screen.

**Themes offer one massive benefit: separation of concerns. **This means that the content is separated from the presentation of the content. And while you might take that for granted, this is what makes it so easy to modify the layout of all the pages on a site without having to change the HTML of each page. (This was a game-changer in the early Web 2.0 days!)

For example, if you want to change a Wordpress theme or Pelican theme, all you have to do is add the theme files to your project and change some site settings and voilà, your site has a hot new look!

HTML Templating

At the core of every theme is the way that content is transformed and merged into HTML to create a webpage. This is called templating. Typically template files look like regular HTML files except, instead of containing the page’s content within the HTML tags, there are variable placeholders and other “templating logic” that will be replaced with the page’s content when the page is “rendered” by the CMS code.

It really helps to see an example. Below is a very minimal HTML page with variables (denoted by the {{variable}} syntax) embedded in the HTML.

After the variables are replaced with the page’s content, the output HTML file would look something like this:

Let’s step way back for a moment and discuss what this all means. Consider how much easier it is to scale up a website with a lot of content. Without templating, the best way to create new pages would be to copy a similar existing HTML file and replace the copied page’s with new content. It sounds like a nightmare! And what if you wanted to change something about a page you copied from?? Yeah… you get the idea.

This generic approach to generating HTML files was a game-changer. Now nearly every single site on the web employs the concept of templating and it’s a foundational piece of SEO.

Let’s consider the popular marketplace website, craigslist.com. The site has millions (billions?) of live pages at any moment but it likely has fewer than 100 different templates. There are pages for every geographic locale that list all the categories for that locale, there are several types of category pages that list all the event, service, and for-sale item listing in that locale, and there are pages with all the details for each event, service, or item in that category. The pages add up quickly, but thanks to templates, the site’s content scales beautifully.

This is a great example for SEO too. Consider for a moment that all of Craigslist’s category pages are just templates with content from other pages combined dynamically to generate content for these new pages. It’s like every time they have content for a few listing pages they get a category page for free—and all the organic traffic that comes with it.

Templating Engines

Most CMSs employ a templating engine (or you might hear them called “templating languages”) on top of whatever programming language the CMS was built on. templating engines make it easy to render HTML files from template files. Here are a few examples and the CMS’ and web frameworks that use them.

Liquid: Ruby’s most popular templating engine is used by Shopify, Jekyll, Zendesk, and many others
Jinja2: Python’s most popular templating engine is used by Pelican and can be used with the web frameworks Flask and Django and others
HubL: Hubspot’s proprietary templating engine for web and email is an extension of on Jinja offering more Hubspot-specific functionality
Handlebars: The Javascript templating engine is used by Ghost, Enduro.js CMS, and web frameworks such as Ember.js and Metor.js.
PHP?: Ok, PHP is a scripting language but it’s still worth mentioning here because the language was built for constructing web pages. As part of the language’s design, you write logic into .php files that output content into the HTML.

This is only a small sample of templating engines. Most popular programming languages have several popular options but all of these templating engines have a few things in common: they assemble and render HTML files from templates that look like HTML but can contain variables, conditional logic, and other inserted or inherited templates.

In the future, I hope to break down how website templating works, especially within the context of SEO.

Trevor Fox

@realtrevorfaux

Would you be interested in an online course: "Intro to Technical SEO and Web Analytics" featuring Python and Javascript?

Stay tuned: mailchi.mp/17a84f9ff204/t…

#technicalseo #webanalytics #webmarketing

06:56 AM - 25 Feb 2020

0 0

But for now, this is the end of this primer on content management systems, themes, and templates. Hopefully, this sets the foundation for future posts about hands-on technical SEO with the static-site generator, Pelican as code examples.

Intro to SQL User Defined Functions (UDFs) in Redshift

Trevor Fox — Sat, 01 Feb 2020 22:50:13 +0000

As a data analyst, your credibility is as valuable as your analytical skills. And to maintain your credibility, it’s important to be able to answer questions correctly and consistently. That’s why you must be careful to integrate reproducibility into your SQL analyses. This post is going to show you how you can use Redshift User Defined Functions to do just that.

Reproducibility in SQL Analysis

There are two broad factors to reproducibility. The first is the data—different data for the same analysis is going to produce different results. A good example would be a court case: if you ask two witnesses the same question, each one will probably tell you something similar but likely slightly different.

The second factor is the analytical methods. If we use the court case example again, this would be like the prosecution and the defense asking a witness the same question in two different ways. The lawyers would do this with the intent_ _to get two different answers.

This post is more concerned with the second factor of reproducibility, the analytical method. Whenever you have to write complex SQL queries to get an answer, your analytical method (the SQL query) becomes a big variable. SQL is iterative by nature! Think about it, just be adding and removing “WHEN” conditions, you’re liable to drastically change your results.

As you iterate on a numerical calculation or classification in a CASE expression you are likely to change your query results. And what happens when you have to perform the same analysis weeks later? You better hope you use the same iteration of your SQL query the second time as the first!

And that is exactly where User-Defined Functions become so valuable!

User-Defined Functions (UDFs) are simply a way of saving one or more calculations or expressions with a name so that you can refer to it as a SQL function for further use.

What are User Defined Functions?

User-Defined Functions can be used just like any other function in SQL like SUBSTRING or ROUND except you get to define what the output of the function is, given the input.

User-Defined Functions (UDFs) are simply a way of saving one or more calculations or expressions with a name so that you can refer to it as a SQL function for further use.

They are a great way to simplify your SQL queries and make them more reproducible at the same time. You can basically take several lines of code that produce one value from your SELECT statement, give it a name, and keep it for future use. Using UDFs, you can ensure that, given the same data, your calculations will always produce the same result.

UDF Functions are Scalar Functions. What does scalar mean?

As you learn about UDFs, you’ll see references to the word “scalar.” Scalar just means that the function is defined with one or more parameters and returns a single result. Just like the ROUND function has one parameter (the number) and an optional second parameter (the number of decimal places for rounding) and returns the rounded number. The function is applied to every value in a column, but it only returns one value for each row in that column.

A Hello World! SQL UDF Example

If you are familiar with any kind of programming language, this should be pretty simple. The CREATE FUNCTION syntax only requires a function name and a return data type. That’s it.

A function called hello_world that returns ‘HELLO WORLD!’ every time would look like this:

create function hello_world ( )
  returns varchar
stable
as $$
  select 'HELLO WORLD!'
$$ language sql;

In that case, the input data type and the output data type are both varchar because “HELLO WORLD!” is a text output. You could use your function like this:

select hello_world() as my_first_function;

And you’d get an output that looks like this:

my_first_function
HELLO WORLD!

But that wouldn’t be very interesting. You’ll generally want to modify the input(s) of your functions. Let’s take apart a more interesting UDF example.

How to Write SQL UDF Functions

This example function, called url_category takes a varchar as an input (a URL) and returns a varchar output (the category of the URL). To do this, the function compares the input (shown as $1 because it is the first parameter) to the conditions of a case expression.

You could also write this function with two parameters. Here’s an example if you were using Google Analytics data. You could take in the parameters, hostname and a page_path to get more granular with your URL categorization.

SQL UDF Functions with Multiple Arguments

This is Redshift’s example from their docs. It takes two parameters (both specified as float) and returns the value that is greater of the two.

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

To refer to the different parameters in the function, you just use the dollar sign ($) and the order of the parameter in the function definition. As long as you follow that convention, you could go wild with your input parameters!

Redshift UDF Limitations

UDFs are basically restricted to anything that you can normally do inside a SELECT clause. The only exception would be subqueries—you cannot use subqueries in a UDF. This means you’re limited to constant or literal values, compound expressions, comparison conditions, CASE expressions, and any other scalar function. But that’s quite a lot!

Common UDF Errors and their Causes

Once you start writing UDFs, you’ll find that it’s pretty easy going but there are two especially common “gotchas”

ERROR: return type mismatch in function declared to return {data type}

DETAIL: Actual return type is {data type}.

This just means that you’ve created a function where the output value has a different data type than you said it would. Check that the return data type that you specified is the same as the function is actually returning. This can be tricky if your function is using a CASE expression because a CASE could accidentally return two different data types.

ERROR: The select expression can not have subqueries.

CONTEXT: Create SQL function "try_this" body

This means you tried to write a SELECT statement in your function that includes a subquery. You can’t do that.

ERROR: function function_name({data type}) does not exist

HINT: No function matches the given name and argument types. You may need to add explicit type casts.

There is one especially odd thing about Redshift UDFs. You can have several functions with the same name as long as they take different arguments or argument types. This can get confusing. The error here means that you’ve called a function with the wrong type of argument. Check the input data type of your function and make sure it’s the same as you input data.

Scaling your SQL Analysis with Confidence!

User-Defined Functions make it really easy to repeat your analytical method across team members and across time. All you have to do is define a function once and let everyone know that they can use it. On top of that, if you want to change the logic of your function you only have to do it in one place and then that logic will be changed for each user in every workbench, notebook, or dashboard!

Take advantage of this clever tool. Your team will thank you, and you will thank you later!

8 New SQL Tools That Will Change How You Work in 2020

Trevor Fox — Sun, 05 Jan 2020 22:45:45 +0000

When it comes to working with SQL, we all want the same things: speed and scale. And on top of that we, want to be easy—maybe even fun. From data collection to processing, to analysis, to putting it into action; we work with data because we want to reach a goal, not because we love parsing JSON, type checking, inspecting logs, and writing documentation. That’s why we need good tools—especially ones that evolve with our changing pace and work styles. That’s why I wanted to share these tools with you today.

I’ve been working in SQL in a few roles, for a few years. It’s been long enough to understand the work, but not long enough to be tied to antiquated methodologies or systems. To me, these tools reflect a shift towards a modern SQL workflow to fit the pace and flexibility of modern work. I appreciate both the innovation of these tools and ingenuity used to solve laborious SQL tasks.

These tools cover everything from getting data into your database, to transforming your data, to analyzing your data, and even sharing your findings. Ok, enough with the intro, let’s get to the SQL tools!

Data Collection

Without data in your database, SQL is entirely useless. These tools make it easy to cross the bridge from messy data to a SQL database.

Numidian Convert

Everybody has run into the problem of having a database full of interesting data and some other file that would be a lot more interesting if it were in the database. Numidian Convert makes it incredibly easy to get data from JSON and CSV files into databases. All you have to do is input a file and define your transformations then it gives you the CREATE TABLE and INSERT statements.

Numidian convert supports Postgres, MySQL, and SQLite. They have a free offering that allows you to convert files up to 10MB, and they also offer a paid API when you need to convert a lot of data for $20/mo.

Panoply

You know the problem. All your data comes from a bunch of APIs, spreadsheets, and file systems and you just want it in one place where you can connect your SQL tools. Panoply is a cloud data warehouse with a ton of native data pipelines from including MySQL, MongoDB, Salesforce, Stripe, Google Analytics, Google Sheets and a ton of other integrations.

Panoply is built on AWS infrastructure and can be used with Azure too. It is basically the power of Redshift except 10x easier to setup, manage and scale. They offer a full-featured free trial that allows you to start querying all your data in less time than it takes to finish a cup of coffee.

Data Exploration

If you’ve ever had to wrap your head around a new DB, you’ve probably had to spend a lot of time querying system tables just to figure you where the good data lives and how much data there is.

Schema Explorer

Schema Explorer, recently open-sourced, is a friendly UI wrapper around all the queries that you would make to get familiar with a new database. What is especially nice about the tool is, if you have foreign key constraints in place, Schema Explorer will draw out a diagram of your database for you!

The software is pretty lean (it appears to be a one-man show) but it supports both Mac and Windows and is 100% free and open source on Github.

Data Transformations

When it comes to scaling, especially scaling out the complexity and application of data across an organization, you have to have a good process. These two tools are really impressive in how they’ve made complex data transformation workflows simple.

Dataform

Consistent with the, no/low maintenance infrastructure trend, Dataform wraps up the entire Transform segment of the ELT process into a SaaS application. This makes it easier than ever to democratize trustworthy tables for end-user analysis. The best part: everything is managed in SQL or in a well-designed UI.

Dataform works with Redshift, BigQuery, Panoply, Azure SQL DW, and Snowflake data warehouses. It also seamlessly handles version control via Github within the UI. And again, all this power comes without maintaining any infrastructure!

DBT (Data Build Tool)

For those of you who like total control of your data pipelines (and don’t mind maintaining the infrastructure that comes along with it) DBT is the transformation tool for you. DBT is among my favorite Github repos because they have really thought about all the challenges of data preparation and transformation down to generating documentation on the fly! DBT predates Dataform so the DAG-ish workflows and templating feel similar except you can control every detail of your deployment.

DBT is a command-line tool that supports Postgres, Redshift, Bigquery, Panoply, and Snowflake, community/partial support for Microsoft SQL Server, Presto, and Spark. Also, their community is really impressive. With “customers” like Hubspot and Seatgeek, you know you're amongst some solid data company.

Data Access and Sharing

This is probably the most solved problem in the SQL space (if not over-solved). But as our workflows evolve, so should our tools. These two tools stand out to me because they are natural extensions from modern collaboration tools, Slack and Google Sheets.

SQLBot

If you’re like me, you’ve integrated every app you use with Slack. I’m not kidding, I have a slash command for Chuck Norris jokes. It should come as no surprise that I was really excited when I learned about SQLBot. I had always thought it would be cool, security aside, to write SQL into Slack and get results back. SQLBot is the next best thing. It allows you to set up slash commands that return query results. You can get your reports without leaving your conversation!

SQLBot is a newer app that is fresh from IndieHackers so while the functionality is amazing the Bootstrap interface keeps things pretty simple. It supports Postgres, MySQL, SQLServer, Amazon Redshift, and Panoply and allows you to drop variables in to your queries which is pretty slick.

SeekWell

As much as I believe that everybody should learn SQL, there are, and probably always will be those who love their spreadsheets too much to let them go. Lucky for us, SeekWell has built a bridge between your database and your co-workers spreadsheets. The tool allows you to write SQL, or Python via a Jupyter notebook and import the data directly into Google Spreadsheets and Slack. You can even schedule queries from both your database and Python notebooks.

SeekWell supports MySQL, Postgres, Redshift, Panoply, Microsoft SQL Server, and Snowflake and I have been pretty impressed by their development speed. It seems like every time I check back with their product, the tool’s experience is becoming more seamless.

Documentation

At the intersection between nobody’s-got-time-for-that and this-is-so-useful lives our old friend documentation. Here is a new tool that will do the job nicely and is pretty painless.

dbdiagram.io

ER diagrams and data dictionaries are a data analyst’s best friends as they get acquainted with a new database or data warehouse but building these from scratch with Google Sheets or Google Drawings is lame and boring and doesn’t take advantage of code to take care of the repetitive tasks that we love to automate. dbiagram.io took a novel approach to this problem and translates a markup language that describes a database and it’s relationships into interactive diagrams that show the database tables.

The free tool is open source, as is the DBML, their markup language that the tool uses, and is maintained by the good people at Holistics. If you have a big DB that you need to diagram, I’d recommend looking into dbdiagram.io to see how you can programmatically generate your ER diagram for free!

I hope some of these tools will bring joy to your SQL work in 2020!