PedroMartinSteenstrup’s gists

PedroMartinSteenstrup / keybase.md

Created February 22, 2024 10:55

I hereby claim:

To claim this, I am signing this object:

PedroMartinSteenstrup / Ansible Let's Encrypt Nginx setup

Created November 20, 2022 13:15 — forked from mattiaslundberg/Ansible Let's Encrypt Nginx setup

Let's Encrypt Nginx setup with Ansible

	Ansible playbook to setup HTTPS using Let's encrypt on nginx.

	The Ansible playbook installs everything needed to serve static files from a nginx server over HTTPS.
	The server pass A rating on [SSL Labs](https://www.ssllabs.com/).

	To use:
	1. Install [Ansible](https://www.ansible.com/)
	2. Setup an Ubuntu 16.04 server accessible over ssh
	3. Create `/etc/ansible/hosts` according to template below and change example.com to your domain
	4. Copy the rest of the files to an empty directory (`playbook.yml` in the root of that folder and the rest in the `templates` subfolder)

PedroMartinSteenstrup / EMR

Last active August 12, 2022 12:06

emr stuff

	import boto3

	from analytics_utils.config import config_2


	class EMR:

	def __init__(self, **kwargs):

	self.config_object = getattr(config_2, 'EMRUtils')(**kwargs)

PedroMartinSteenstrup / HomeToGo setup

Last active August 1, 2022 14:28

Bunch of commands to set yourself up with HomeToGo tooling, piggybacking on https://gitlab.com/gitlab-data/analytics/blob/master/admin/onboarding_script.zsh

	########## HomeBrew ##########
	## Check if exists

	command -v brew >/dev/null 2>&1 \|\| { echo "Installing Homebrew.."
	/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
	} >&2;
	echo "Homebrew successfully installed"

	########## SSH ##########

PedroMartinSteenstrup / docker-superset_superset_config.py

Created October 23, 2021 02:48

Basic implementation of Snowflake pass-through authentication in Superset

	# --------------------------------------
	# Snowflake OAuth
	# --------------------------------------

	SNOWFLAKE_ACCOUNT = os.getenv('SNOWFLAKE_ACCOUNT') or None

	if SNOWFLAKE_ACCOUNT:
	SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_ID = os.getenv('SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_ID')
	SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_SECRET = os.getenv('SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_SECRET')
	SNOWFLAKE_OAUTH_AUTHORIZATION_ENDPOINT = f'https://{SNOWFLAKE_ACCOUNT}.snowflakecomputing.com/oauth/authorize'

PedroMartinSteenstrup / snowflake_roles.sql

Created August 12, 2020 16:08

Get users and their role in snowflake

	CREATE OR REPLACE TABLE DBUSERS (
	NAME VARCHAR,
	CREATED_ON TIMESTAMP_LTZ,
	LOGIN_NAME VARCHAR,
	DISPLAY_NAME VARCHAR,
	FIRST_NAME VARCHAR,
	LAST_NAME VARCHAR,
	EMAIL VARCHAR,
	MINS_TO_UNLOCK VARCHAR,
	DAYS_TO_EXPIRY VARCHAR,

PedroMartinSteenstrup / savings_year.sql

Last active July 2, 2020 15:08

get the savings of a specific year for an estimation of a TransferWise saving

	use role "[email protected]";

	CREATE OR REPLACE table ANALYST_SANDBOX.year_savings AS (
	WITH comparison_data AS (
	SELECT lookup_countries.CODE_2CHAR AS comparison_country,
	avg(((FEES / AMOUNT) + ((MIDMARKET_RATE - RATE) / MIDMARKET_RATE))) AS comparison_total_pct
	FROM reports.LOOKUP_COUNTRIES AS lookup_countries
	INNER JOIN comparison.rates AS rates ON rates.source_country = lookup_countries.code_3char
	LEFT JOIN COMPARISON.PROVIDERS On providers.ID = rates.PROVIDER_ID
	AND rates.date_collected::DATE BETWEEN '2019-04-01' AND '2020-03-31'

PedroMartinSteenstrup / Looker usage

Created June 11, 2020 14:37

	USE ROLE AP_TOOLS;

	-- CAVEAT Nr 1: Snowflake only gets the history_id from Looker passed as context, so we can link to a user efficiently.
	-- -- -- -- -- -- This is then linked to workday's data
	-- CAVEAT Nr 2: Looker's history_id can have several Snowflake queries linked to, who each have a cost associated to.
	-- -- -- -- -- -- therefore there is a danger of double counting. History is augmented by Looker data.
	-- CAVEAT Nr 3: Workday's data seems to not systematically have the cost center input, though it makes little sense
	-- -- -- -- -- -- some departments might be under-represented until we figure out why it's missing
	-- CAVEAT Nr 4: Snowflake credits seem to be off compared to the rest of the figures
	-- -- -- -- -- -- might have to tweak the calculation

PedroMartinSteenstrup / virality_nps

Last active February 23, 2021 09:44

updated version of virality as a factor of NPS score

	WITH viralit AS (
	Select host_id,
	count(distinct IFF(DATE_SIGNED_UP is not null, GUEST_ID, NULL)) count_signups,
	count(distinct IFF(DATE_TRANSFERRED is not null, GUEST_ID, NULL)) count_mnus
	from virality.referral
	GROUP BY 1),
	nps AS (SELECT bnps.user_id,
	bnps.nps_score AS nps_score,
	bnps.respond_date,
	rank() over (partition by bnps.USER_ID order by bnps.RESPOND_DATE desc) rank

Pedro Martin-Steenstrup PedroMartinSteenstrup